It is accessible at sqlzoo.net.
We will use parts of this database as time goes.
SELECT [DISTINCT] select-list FROM from-list WHERE qualification GROUP BY grouping-list HAVING group-qualification ORDER BY order-list
SELECT name,population FROM CIA WHERE population > 100000000
SELECT C.name, C.population FROM CIA C WHERE C.population > 100000000
SELECT C.name FROM CIA C WHERE C.area > (SELECT area FROM cia WHERE name='United States')
However, we consider the following goal: For each region, report the country with the largest area in that region. One way to structure this query is as follows:
SELECT C.region,C.name FROM CIA C WHERE C.area >= ALL (SELECT D.area FROM cia D WHERE C.region = D.region)
To explore joins, we will start by considering another database from sqlzoo.net, modeling the Internet Movie Database (imdb.com) We explored the following tutorials from sqlzoo:
SELECT C.name, D.name FROM CIA C, CIA D WHERE C.region = D.region AND C.name != D.name AND C.area > 10000 AND C.area between D.area and D.area+1000