Some SQL examples

03 Nov 2011

Here are a few examples covering the syntax we used in class, using the un_examples.sqlite database.

The following query finds the countries with fewer than 1000 goats in 1999:

select country, goats
from goats
where year = 1999 and goats < 1000;

Note how the two conditions are written and combined. Each line is a separate part of the query:

  1. columns to include
  2. table to query
  3. which rows to keep

Results:

country goats
Aruba 0
Belize 130
Bermuda 300
Cayman Islands 269
French Guiana 800
Guam 650
Iceland 502
Liechtenstein 280
Saint Pierre and Miquelon 0
Singapore 500
The former Yugoslav Republic of Macedonia 0
Tuvalu 0

If we wanted to sort based on least goats, we could add a line to the end:

select country, goats
from goats
where year = 1999 and goats < 1000
order by goats asc;

And if we wanted only the 5 rows with the fewest goats, we could add one more line to that:

select country, goats
from goats
where year = 1999 and goats < 1000
order by goats asc
limit 5;

Results:

country goats
Aruba 0
Saint Pierre and Miquelon 0
The former Yugoslav Republic of Macedonia 0
Tuvalu 0
Belize 130

Poor Aruba.

The following query finds the 10 countries with the most goats on average:

select country, avg(goats) as avg_goats 
from goats 
group by country 
order by avg_goats desc
limit 10;

Note that each line shows a separate clause:

  1. columns to include
  2. table we're querying
  3. how to split into groups before averaging
  4. how to order the results
  5. the number to take from the top

Results:

country avg(goats)
World + 537499024.808511
Low Income Food Deficit Countries + 429140513.361702
Asia + 318799984.638298
Net Food Importing Developing Countries + 205906477.574468
Southern Asia + 175292810.340426
Africa + 164814905.851064
Least Developed Countries + 143035094.744681
India 95410936.5106383
Eastern Asia + 99310305.8510639
China 91652331.6595745

Here is another (complicated) version that shows every type of clause you'll use in the homework:

select country, avg(goats) as avg_goats, count(*) as num_years, male + female as total_pop
from goats natural join population
where male + female < 10000000
group by country 
having num_years >= 10
order by avg_goats desc
limit 10;

This selects the 10 countries with the most goats that have a total population under 10 million, and that have at least 10 years of data in the database. Each line does the following:

  1. columns and expressions to include in the result
  2. tables to query (note that they're joined by matching up on country and year
  3. rows to include
  4. how to group before averaging and counting
  5. which groups to keep
  6. how to order the results
  7. how many results to take from the top

Results:

country avg_goats num_years
Mongolia 7512938.0952381 21
Senegal 2958821.88235294 17
Botswana 1942176.47058824 17
Bolivia 1551015.23809524 21
Tunisia 1253703.07692308 13
Benin 1108691.0 11
Albania 1066985.71428571 14
Oman 966425.833333334 12
Lesotho 836798.833333334 12
Burundi 813864.818181818 11