How to write an "exclusive" query in SQL?

Question

I am going over a past paper for a database course I am taking and I am stuck on an SQL question

Here is the schema provided

• Country(name, capital, area), name is the key

• People(country, population, children, adult) where country refers to the name in Country, population is the total population, and children and adult is the percentage of the children and adult population.

• Language(country,language,percentage) – for each language spoken in the country, it lists the percentage of the population that speaks the language.

Here is the question:

Write the following query in SQL: Find languages that are only spoken in countries whose total population exceeds 10^7.

This is what I have so far:

SELECT l.language
FROM people p, language l
WHERE l.country = p.country AND
    p.population > 10^7

The bit I am confused about is how to check that there are no other countries which a language is spoken in but the population is less than 10^7.

Any advice? Thanks

Answer 1

Get all languages. From that set remove all languages spoken in countries with population <= 10^7. The reminder should be languages only spoken on countries with population > 10^7.

select language from languages
where language not in (
    select language from languages l
    join people p on l.country = p.country
    where p.population <= 10^7)

This works on the basis that your design has the restriction that every language must be spoken in at least one country ;)

Answer 2

WITH T 
     AS
     (
      SELECT l.language, 
             p.country, p.population
        FROM people p, language l
       WHERE l.country = p.country
     )
SELECT language
  FROM T
EXCEPT
SELECT language
  FROM T
 WHERE population <= 10000000;

Alternatively:

SELECT language
  FROM language AS l1
 WHERE 10000000 < ( SELECT MIN(p.population)
                      FROM people p, language l
                     WHERE l.country = p.country
                           AND l.language = l1.language );

Similarly:

SELECT language
  FROM language AS l1
 WHERE 10000000 < ALL ( SELECT p.population
                          FROM people p, language l
                         WHERE l.country = p.country
                               AND l.language = l1.language );