Lecture Notes: Databases and SQL

Reading: pp. 406-407 of text and the sqlzoo.net tutorials.

Though the text discusses use of SQL on paper, using the example databases from that chapter, we will base most of our exploration on a series of databases which we can query on the web at sqlzoo.net.

There are two separate databases that we will rely upon. One dataset involves information about countries, taken from the CIA Factbook. The other dataset involves movies, taken from the Internet Movie Datatbase (IMDB). In class, we handed out a brief summary of those database schemas.

Practice using the CIA country database from sqlzoo.net.

Note: Recently, the sqlzoo.net tutorials have been updated to use a new table, bbc, of country information provided by the BBC. However, we prefer to use the cia table as it has more complete information. When following their tutorials, please use cia rather than bbc as the source.

Here is a brief overview of the table's contents.
Here are some sample queries (answers are embedded)
Here is the tutorial
Here are sample answers to the tutorial.

Practice using the Movie database from sqlzoo.net.

Here is an overview of the table's contents.
Here are some sample queries involving joins (answers embedded).
Do queries 1a-1b, 2a-2c, and 3a-3e of the tutorial.
Here are sample answers to the tutorial.

Last modified: Monday, 27 November 2006