CODEWITHSUNDEEP
sql
user15006648
user15006648

Reputation:

Find all Asian cities whose population is greater than population of every single Nordic country (attribute region)

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?

Image

Upvotes: 0

Views: 103

Answers (3)

dogyog
dogyog

Reputation: 340

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).

Your WHERE does not match your question, and you accepted an answer that used your WHERE logic. BUT, if you are actually looking for Asian cities with a population greater than every single Nordic country, you need something like the below:

SELECT
    city.Name
FROM
    wwd.city
    JOIN wwd.country ON city.CountryCode = country.Code
WHERE
    country.Continent = 'Asia'
    AND city.population > (
        SELECT
            SUM(country.population)
        FROM
            wwd.country
        WHERE
            country.Region = 'Nordic'
    )

Upvotes: 0

psolomon
psolomon

Reputation: 173

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?

Upvotes: 0

Jakob Lovern
Jakob Lovern

Reputation: 1341

Try the following change:

SELECT
    city.Name
FROM
    wwd.city
    JOIN wwd.country ON city.CountryCode = country.Code
WHERE
    country.Continent = 'Asia'
    AND city.population > (
        SELECT
            max(city.population)
        FROM
            wwd.city
        WHERE
            country.Region = 'Nordic'
    )

Generally we want to compare in a 1:1 sort of way rather than directly comparing against a range.

Upvotes: 1

Related Questions