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

Accepted Answer

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

How to write an "exclusive" query in SQL?

Solution

Problem