Databases and SQL
- Explain the operation of a query that joins two tables.
- Explain how to restrict the output of a query containing a join to only include meaningful combinations of values.
- Write queries that join tables on equal keys.
- Explain what primary and foreign keys are, and why they are useful.
In order to submit her data to a web site that aggregates historical meteorological data, Gina needs to format it as latitude, longitude, date, quantity, and reading. However, her latitudes and longitudes are in the
Site table, while the dates of measurements are in the
Visited table and the readings themselves are in the
Survey table. She needs to combine these tables somehow.
The SQL command to do this is
JOIN. To see how it works, let’s start by joining the
SELECT * FROM Site JOIN Visited;
JOIN creates the cross product of two tables, i.e., it joins each record of one with each record of the other to give all possible combinations. Since there are three records in
Site and eight in
Visited, the join’s output has 24 records (3 * 8 = 24) . And since each table has three fields, the output has six fields (3 + 3 = 6).
What the join hasn’t done is figure out if the records being joined have anything to do with each other. It has no way of knowing whether they do or not until we tell it how. To do that, we add a clause specifying that we’re only interested in combinations that have the same site name, thus we need to use a filter:
SELECT * FROM Site JOIN Visited ON Site.name=Visited.site;
ON is very similar to
WHERE, and for all the queries in this lesson you can use them interchangeably. There are differences in how they affect outer joins, but that’s beyond the scope of this lesson. Once we add this to our query, the database manager throws away records that combined information about two different sites, leaving us with just the ones we want.
Notice that we used
Table.field to specify field names in the output of the join. We do this because tables can have fields with the same name, and we need to be specific which ones we’re talking about. For example, if we joined the
Visited tables, the result would inherit a field called
ident from each of the original tables.
We can now use the same dotted notation to select the three columns we actually want out of our join:
SELECT Site.lat, Site.long, Visited.dated FROM Site JOIN Visited ON Site.name=Visited.site;
If joining two tables is good, joining many tables must be better. In fact, we can join any number of tables simply by adding more
JOIN clauses to our query, and more
ON tests to filter out combinations of records that don’t make sense:
SELECT Site.lat, Site.long, Visited.dated, Survey.quant, Survey.reading FROM Site JOIN Visited ON Site.name=Visited.site JOIN Survey ON Visited.ident=Survey.taken WHERE Visited.dated IS NOT NULL;
We can tell which records from
Survey correspond with each other because those tables contain primary keys and foreign keys. A primary key is a value, or combination of values, that uniquely identifies each record in a table. A foreign key is a value (or combination of values) from one table that identifies a unique record in another table. Another way of saying this is that a foreign key is the primary key of one table that appears in some other table. In our database,
Person.ident is the primary key in the
Person table, while
Survey.person is a foreign key relating the
Survey table’s entries to entries in
Most database designers believe that every table should have a well-defined primary key. They also believe that this key should be separate from the data itself, so that if we ever need to change the data, we only need to make one change in one place. One easy way to do this is to create an arbitrary, unique ID for each record as we add it to the database. This is actually very common: those IDs have names like “student numbers” and “patient numbers”, and they almost always turn out to have originally been a unique record identifier in some database system or other. As the query below demonstrates, SQLite automatically numbers records as they’re added to tables, and we can use those record numbers in queries:
SELECT rowid, * FROM Person;
Listing Radiation Readings
Write a query that lists all radiation readings from the DR-1 site.
Write a query that lists all sites visited by people named “Frank”.
Describe in your own words what the following query produces:
SELECT Site.name FROM Site JOIN Visited ON Site.lat<-49.0 AND Site.name=Visited.site AND Visited.dated>='1932-01-01';
Who has been where?
Write a query that shows each site with exact location (lat, long) ordered by visited date, followed by personal name and family name of the person who visited the site and the type of measurement taken and its reading. Please avoid all null values. Tip: you should get 15 records with 8 fields.