We have 2 tables Country and City as shown below:
Country (countryid, countryname )
City (countryid, cityname )
Q1. How do you get the countries that has no cities?
Q2. How do you get the countries that has less than 3 cities and also make sure the countries with no cities also show up?
Solution:
Ans 1. Use left outer join to get all countries
SELECT co.countryname FROM Country co LEFT OUTER JOIN City ci ON co.countryid = ci.countryid WHERE ci.countryid IS NULL
Ans 2. Use left outer join with group by and count function to get count of cities
SELECT co.countryname FROM Country co LEFT OUTER JOIN City ci ON co.countryid = ci.countryid GROUP BY co.countryId, ci.countryname HAVING COUNT(co.countryId) < 3
I think for the answer query where countryid is null clause is missing.