I’m doing a task where I’m supposed to find all Asian cities whose population is greater than the population of every single Nordic country (attribute region). (The diagram is shown on the uploaded picture below). I’m pretty new to SQL and I just wanted to ask, what is the right way to connect tables with foreign keys (in this case country code and code) so that I can approach both tables and put my conditions.
So far, my approach is:
SELECT city.Name FROM wwd.city JOIN wwd.country ON city.CountryCode = country.Code WHERE country.Continent='Asia' AND city.population > (SELECT city.population FROM wwd.city WHERE country.Region = 'Nordic'
I’m not sure If I used command JOIN properly as well as is the command JOIN expected in the second SELECT part?
Advertisement
Answer
An alternative to the given answer is to use the ALL operator: https://www.w3schools.com/sql/sql_any_all.asp
SELECT city.Name FROM wwd.city JOIN wwd.country ON city.CountryCode = country.Code WHERE country.Continent = 'Asia' AND city.population > ALL ( SELECT city.population FROM wwd.city WHERE country.Region = 'Nordic' )
As an aside, can you reference the country table in the subquery without joining it first with the city table?