Steps to a complete understanding of SQL

What are the top 3 average government debts in percent of the GDP for those countries whose GDP per capita was over 40’000 dollars in every year in the last four years

Whew. Some (academic) business requirements.

In SQL (PostgreSQL dialect), we would write:

select code, avg(govt_debt)

from countries

where year > 2010

group by code

having min(gdp_per_capita) >= 40000

order by 2 desc

limit 3

Or, with inline comments

-- The average government debt

select code, avg(govt_debt)

-- for those countries

from countries

-- in the last four years

where year > 2010

-- yepp, for the countries

group by code

-- whose GDP p.c. was over 40'000 in every year

having min(gdp_per_capita) >= 40000

-- The top 3

order by 2 desc

limit 3

The result being:

code     avg
-----------------
JP    193.00
US     91.95

DE     56.00

Remember the 10 easy steps to a complete understanding of SQL:

1.    FROM generates the data set

2.    WHERE filters the generated data set

3.    GROUP BY aggregates the filtered data set

4.    HAVING filters the aggregated data set

5.    SELECT transforms the filters aggregated data set

6.    ORDER BY sorts the transformed data set

7.    LIMIT .. OFFSET frames the sorted data set