Instructor's Guide for Databases and SQL

A relational database is a way to store and manipulate information that is arranged as tables. Each table has columns (also known as fields) which describe the data, and rows (also known as records) which contain the data.

When we are using a spreadsheet, we put formulas into cells to calculate new values based on old ones. When we are using a database, we send commands (usually called queries) to a database manager: a program that manipulates the database for us. The database manager does whatever lookups and calculations the query specifies, returning the results in a tabular form that we can then use as a starting point for further queries.

Queries are written in a language called SQL, which stands for "Structured Query Language". SQL provides hundreds of different ways to analyze and recombine data; we will only look at a handful, but that handful accounts for most of what scientists do.

Figure 1 shows a simple database that stores some of the data Gina extracted from the logs of those long-ago expeditions. It contains four tables:

Notice that three entries—one in the Visited table, and two in the Survey table—are shown as NULL. We'll return to these values later. For now, let's write an SQL query that displays scientists' names. We do this using the SQL command select, giving it the names of the columns we want and the table we want them from. Our query and its output look like this:

sqlite> select family, personal from Person;

The semi-colon at the end of the query tells the database manager that the query is complete and ready to run. If we enter the query without the semi-colon, or press 'enter' part-way through the query, the SQLite interpreter will give us a different prompt to show us that it's waiting for more input:

sqlite> select family, personal
   ...> from Person
   ...> ;

From now on, we won't bother to display the prompt(s) with our commands.

SeLeCt famILY, PERSonal frOM PERson;

Displaying Results

Exactly how the database displays the query's results depends on what kind of interface we are using. If we are running SQLite directly from the shell, its default output looks like this:

Dyer|William Pabodie|Frank Lake|Anderson Roerich|Valentina Danforth|Frank

If we are using a graphical interface, such as the SQLite Manager plugin for Firefox or the database extension for the IPython Notebook, our output will be displayed graphically (Figure 2 and Figure 3). We'll use a simple table-based display in these notes.

Going back to our query, it's important to understand that the rows and columns in a database table aren't actually stored in any particular order. They will always be displayed in some order, but we can control that in various ways. For example, we could swap the columns in the output by writing our query as:

select personal, family from Person;

or even repeat columns:

select ident, ident, ident from Person;

We will see ways to rearrange the rows later.

As a shortcut, we can select all of the columns in a table using the wildcard *:

select * from Person;

Data is often redundant, so queries often return redundant information. For example, if we select the quantitites that have been measured from the survey table, we get this:

select quant from Survey;

We can eliminate the redundant output to make the result more readable by adding the distinct keyword to our query:

select distinct quant from Survey;

If we select more than one column—for example, both the survey site ID and the quantity measured—then the distinct pairs of values are returned:

select distinct taken, quant from Survey;

Notice in both cases that duplicates are removed even if they didn't appear to be adjacent in the database. Again, it's important to remember that rows aren't actually ordered: they're just displayed that way.

One of the most powerful features of a database is the ability to filter data, i.e., to select only those records that match certain criteria. For example, suppose we want to see when a particular site was visited. We can select these records from the Visited table by using a where clause in our query:

select * from Visited where site='DR-1';

The database manager executes this query in two stages (Figure 4). First, it checks at each row in the Visited table to see which ones satisfy the where. It then uses the column names following the select keyword to determine what columns to display.

This processing order means that we can filter records using where based on values in columns that aren't then displayed:

select ident from Visited where site='DR-1';

We can use many other Boolean operators to filter our data. For example, we can ask for all information from the DR-1 site collected since 1930:

select * from Visited where (site='DR-1') and (dated>='1930-00-00');

(The parentheses around the individual tests aren't strictly required, but they help make the query easier to read.)

If we want to find out what measurements were taken by either Lake or Roerich, we can combine the tests on their names using or:

select * from Survey where person='lake' or person='roe';

Alternatively, we can use in to see if a value is in a specific set:

select * from Survey where person in ('lake', 'roe');

We can combine and with or, but we need to be careful about which operator is executed first. If we don't use parentheses, we get this:

select * from Survey where quant='sal' and person='lake' or person='roe';

which is salinity measurements by Lake, and any measurement by Roerich. We probably want this instead:

select * from Survey where quant='sal' and (person='lake' or person='roe');

Finally, we can use distinct with where to give a second level of filtering:

select distinct person, quant from Survey where person='lake' or person='roe';

But remember: distinct is applied to the values displayed in the chosen columns, not to the entire rows as they are being processed.

After carefully reading the expedition logs, Gina realizes that the radiation measurements they report may need to be corrected upward by 5%. Rather than modifying the stored data, she can do this calculation on the fly as part of her query:

select 1.05 * reading from Survey where quant='rad';

When we run the query, the expression 1.05 * reading is evaluated for each row. Expressions can use any of the fields, all of usual arithmetic operators, and a variety of common functions. (Exactly which ones depends on which database manager is being used.) For example, we can convert temperature readings from Fahrenheit to Celsius and round to two decimal places as follows:

select taken, round(5*(reading-32)/9, 2) from Survey where quant='temp';

We can also combine values from different fields, for example by using the string concatenation operator ||:

select personal || ' ' || family from Person;

As we mentioned earlier, database records are not stored in any particular order. This means that query results aren't necessarily sorted, and even if they are, we often want to sort them in a different way, e.g., by the name of the project instead of by the name of the scientist. We can do this in SQL by adding an order by clause to our query:

select reading from Survey where quant='rad' order by reading;

By default, results are sorted in ascending order (i.e., from least to greatest). We can sort in the opposite order using desc (for "descending"):

select reading from Survey where quant='rad' order by reading desc;

(And if we want to make it clear that we're sorting in ascending order, we can use asc instead of desc.)

We can also sort on several fields at once. For example, this query sorts results first in ascending order by taken, and then in descending order by person within each group of equal taken values:

select taken, person from Survey order by taken asc, person desc;

This is easier to understand if we also remove duplicates:

select distinct taken, person from Survey order by taken asc, person desc;

Since sorting happens before columns are filtered, we can sort by a field that isn't actually displayed:

select reading from Survey where quant='rad' order by taken;

We can also sort results by the value of an expression. In SQLite, for example, the random function returns a pseudo-random integer each time it is called (i.e., once per record):

select random(), ident from Person;

So to randomize the order of our query results, e.g., when doing clinical trials, we can sort them by the value of this function:

select ident from Person order by random();

select ident from Person order by random();

Our query pipeline now has four stages (Figure 5):

1. Select the rows that pass the where criteria.
2. Sort them if required.
3. Filter the columns according to the select criteria.
4. Remove duplicates if required.

Real-world data is never complete—there are always holes. Databases represent these holes using special value called null. null is not zero, False, or the empty string; it is a one-of-a-kind value that means "nothing here". Dealing with null requires a few special tricks and some careful thinking.

To start, let's have a look at the Visited table. There are eight records, but #752 doesn't have a date—or rather, its date is null:

select * from Visited;

Null doesn't behave like other values. If we select the records that come before 1930:

select * from Visited where dated<'1930-00-00';

we get two results, and if we select the ones that come during or after 1930:

select * from Visited where dated>='1930-00-00';

we get five, but record #752 isn't in either set of results. The reason is that null<'1930-00-00' is neither true nor false: null means, "We don't know," and if we don't know the value on the left side of a comparison, we don't know whether the comparison is true or false. Since databases represent "don't know" as null, the value of null<'1930-00-00' is actually null. null>='1930-00-00' is also null because we can't answer to that question either. And since the only records kept by a where are those for which the test is true, record #752 isn't included in either set of results.

Comparisons aren't the only operations that behave this way with nulls. 1+null is null, 5*null is null, log(null) is null, and so on. In particular, comparing things to null with = and != produces null:

select * from Visited where dated=NULL;

select * from Visited where dated!=NULL;

To check whether a value is null or not, we must use a special test is null:

select * from Visited where dated is NULL;

or its inverse is not null:

select * from Visited where dated is not NULL;

Null values cause headaches wherever they appear. For example, suppose we want to find the all of salinity measurements that weren't taken by Dyer. It's natural to write the query like this:

select * from Survey where quant='sal' and person!='lake';

but this query filters omits the records where we don't know who took the measurement. Once again, the reason is that when person is null, the != comparison produces null, so the record isn't kept in our results. If we want to keep these records we need to add an explicit check:

select * from Survey where quant='sal' and (person!='lake' or person is null);

We still have to decide whether this is the right thing to do or not. If we want to be absolutely sure that we aren't including any measurements by Lake in our results, we need to exclude all the records for which we don't know who did the work.

Gina now wants to calculate ranges and averages for her data. She knows how to select all of the dates from the Visited table:

select dated from Visited;

but to combine them, she must use an aggregation function such as min or max. Each of these functions takes a set of records as input, and produces a single record as output:

select min(dated) from Visited;

select max(dated) from Visited;

min and max are just two of the aggregation functions built into SQL. Three others are avg, count, and sum:

select avg(reading) from Survey where quant='sal';

select count(reading) from Survey where quant='sal';

select sum(reading) from Survey where quant='sal';

We used count(reading) here, but we could just as easily have counted quant or any other field in the table, or even used count(*), since the function doesn't care about the values themselves, just how many values there are.

SQL lets us do several aggregations at once. We can, for example, find the range of sensible salinity measurements:

select min(reading), max(reading) from Survey where quant='sal' and reading<=1.0;

We can also combine aggregated results with raw results, although the output might surprise you:

select person, count(*) from Survey where quant='sal' and reading<=1.0;

Why does Lake's name appear rather than Roerich's or Dyer's? The answer is that when it has to aggregate a field, but isn't told how to, the database manager chooses an actual value from the input set. It might use the first one processed, the last one, or something else entirely.

Another important fact is that when there are no values to aggregate, aggregation's result is "don't know" rather than zero or some other arbitrary value:

select person, max(reading), sum(reading) from Survey where quant='missing';

One final important feature of aggregation functions is that they are inconsistent with the rest of SQL in a very useful way. If we add two values, and one of them is null, the result is null. By extension, if we use sum to add all the values in a set, and any of those values are null, the result should also be null. It's much more useful, though, for aggregation functions to ignore null values and only combine those that are non-null. This behavior lets us write our queries as:

select min(dated) from Visited;

instead of always having to filter explicitly:

select min(dated) from Visited where dated is not null;

Aggregating all records at once doesn't always make sense. For example, suppose Gina suspects that there is a systematic bias in her data, and that some scientists' radiation readings are higher than others. We know that this doesn't work:

select person, count(reading), round(avg(reading), 2)
from  Survey
where quant='rad';

because the database manager selects a single arbitrary scientist's name rather than aggregating separately for each scientist. Since there are only five scientists, she could write five queries of the form:

select person, count(reading), round(avg(reading), 2)
from  Survey
where quant='rad'
and   person='dyer';

but this would be tedious, and if she ever had a data set with fifty or five hundred scientists, the chances of her getting all of those queries right is small.

What we need to do is tell the database manager to aggregate the hours for each scientist separately using a group by clause:

select   person, count(reading), round(avg(reading), 2)
from     Survey
where    quant='rad'
group by person;

group by does exactly what its name implies: groups all the records with the same value for the specified field together so that aggregation can process each batch separately. Since all the records in each batch have the same value for person, it no longer matters that the database manager is picking an arbitrary one to display alongside the aggregated reading values (Figure 6).

Just as we can sort by multiple criteria at once, we can also group by multiple criteria. To get the average reading by scientist and quantity measured, for example, we just add another field to the group by clause:

select   person, quant, count(reading), round(avg(reading), 2)
from     Survey
group by person, quant;

Note that we have added person to the list of fields displayed, since the results wouldn't make much sense otherwise.

Let's go one step further and remove all the entries where we don't know who took the measurement:

select   person, quant, count(reading), round(avg(reading), 2)
from     Survey
where    person is not null
group by person, quant
order by person, quant;

Looking more closely, this query:

1. selected records from the Survey table where the person field was not null;
2. grouped those records into subsets so that the person and quant values in each subset were the same;
3. ordered those subsets first by person, and then within each sub-group by quant; and
4. counted the number of records in each subset, calculated the average reading in each, and chose a person and quant value from each (it doesn't matter which ones, since they're all equal).

Our query processing pipeline now looks like Figure 7.

In order to submit her data to a web site that aggregates historical meteorological data, Gina needs to format it as:

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 Site and Visited tables:

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. And since each table has three fields, the output has six fields.

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:

select * from Site join Visited on Site.name=Visited.site;

on does the same job as where: it only keeps records that pass some test. (The difference between the two is that on filters records as they're being created, while where waits until the join is done and then does the filtering.) 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 person and 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 join Survey
on     Site.name=Visited.site
and    Visited.ident=Survey.taken
and    Visited.dated is not null;

We can tell which records from Site, Visited, and 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 Person.

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;

So far we have only looked at how to get information out of a database, both because that is more frequent than adding information, and because most other operations only make sense once queries are understood. If we want to create and modify data, we need to know two other pairs of commands.

The first pair are create table and drop table. While they are written as two words, they are actually single commands. The first one creates a new table; its arguments are the names and types of the table's columns. For example, the following statements create the four tables in our survey database:

create table Person(ident text, personal text, family text);
create table Site(name text, lat real, long real);
create table Visited(ident integer, site text, dated text);
create table Survey(taken integer, person text, quant real, reading real);

We can get rid of one of our tables using:

drop table Survey;

Be very careful when doing this: most databases have some support for undoing changes, but it's better not to have to rely on it.

Different database systems support different data types for table columns, but most provide the following:

most databases also support Booleans and date/time values; SQLite uses the integers 0 and 1 for the former, and represents the latter as discussed earlier. An increasing number of databases also support geographic data types, such as latitude and longitude. Keeping track of what particular systems do or do not offer, and what names they give different data types, is an unending portability headache.

When we create a table, we can specify several kinds of constraints on its columns. For example, a better definition for the Survey table would be:

create table Survey(
        taken   integer not null, -- where reading taken
        person  text,             -- may not know who took it
        quant   real not null,    -- the quantity measured
        reading real not null,    -- the actual reading
        primary key(taken, quant),
        foreign key(taken) references Visited(ident),
        foreign key(person) references Person(ident)
);

Once again, exactly what constraints are avialable and what they're called depends on which database manager we are using.

Once tables have been created, we can add and remove records using our other pair of commands, insert and delete. The simplest form of insert statement lists values in order:

insert into Site values('DR-1', -49.85, -128.57);
insert into Site values('DR-3', -47.15, -126.72);
insert into Site values('MSK-4', -48.87, -123.40);

We can also insert values into one table directly from another:

create table JustLatLong(lat text, long TEXT);
insert into JustLatLong select lat, long from site;

Deleting records can be a bit trickier, because we have to ensure that the database remains internally consistent. If all we care about is a single table, we can use the DELETE command with a WHERE clause that matches the records we want to discard. For example, once we realize that Frank Danforth didn't take any measurements, we can remove him from the Person table like this:

delete from Person where ident = "danforth";

But what if we removed Anderson Lake instead? Our Survey table would still contain seven records of measurements he'd taken:

select count(*) from Survey where person='lake';

That's never supposed to happen: Survey.person is a foreign key into the Person table, and all our queries assume there will be a row in the latter matching every value in the former.

This problem is called referential integrity: we need to ensure that all references between tables can always be resolved correctly. One way to do this is to delete all the records that use 'lake' as a foreign key before deleting the record that uses it as a primary key. If our database manager supports it, we can automate this using cascading delete. However, this technique is outside the scope of this chapter.

Suppose we have another table in our database that shows which pieces of equipment have been borrowed by which scientists:

select * from Equipment;

(We should actually give each piece of equipment a unique ID, and use that ID here instead of the full name, just as we created a separate table for scientists earlier in this chapter, but we will bend the rules for now.) If William Dyer gives the oscilloscope to Valentina Roerich, we need to execute two statements to update this table:

delete from Equipment where person="dyer" and thing="CX-211 oscilloscope";
insert into Equipment values("roe", "CX-211 oscilloscope");

This is all fine—unless our program happens to crash between the first statement and the second. If that happens, the Equipment table won't have a record for the oscilloscope at all. Such a crash may seem unlikely, but remember: if a computer can do two billion operations per second, that means there are two billion opportunities every second for something to go wrong. And if our operations take a long time to complete—as they will when we are working with large datasets, or when the database is being heavily used—the odds of failure increase.

What we really want is a way to ensure that every operation is ACID: atomic (i.e. indivisible), consistent, isolated, and durable. The precise meanings of these terms doesn't matter; what does is the notion that every logical operation on the database should either run to completion as if nothing else was going on at the same time, or fail without having any effect at all.

The tool we use to ensure that this happens is called a transaction. Here's how we should actually write the statements to move the oscilloscope from one person to another:

begin transaction;
delete from Equipment where person="dyer" and thing="CX-211 oscilloscope";
insert into Equipment values("roe", "CX-211 oscilloscope");
end transaction;

The database manager treats everything in the transaction as one large statement. If anything goes wrong inside, then none of the changes made in the transaction will actually be written to the database—it will be as if the transaction had never happened. Changes are only stored permanently when we commit them at the end of the transaction.

Transactions serve another purpose as well. Suppose there is another table in the database called Exposure that records the number of days each scientist was exposed to higher-than-normal levels of radiation:

select * from Exposure;

After going through the journal entries for 1932, Gina wants to add two days to Lake's count:

update Exposure set days = days + 2 where person='lake';

However, her labmate has been doing through the journal entries for 1933 to help Gina meet a paper deadline. At the same moment as Gina runs her command, her labmate runs this to add one more day to Lake's exposure:

update Exposure set days = days + 1 where person='lake';

After both operations have completed, the database should show that Lake was exposed for eight days (the original five, plus two from Gina, plus one from her labmate). However, there is a small chance that it won't. To see why, let's break the two queries into their respective read and write steps and place them side by side:

The database can only actually do one thing at once, so it must put these four operations into some sequential order. That order has to respect the original order within each column, but the database can interleave the two columns any way it wants. If it orders them like this:

then all is well. But what if it interleaves the operations like this:

This ordering puts the initial value, 5, into both X and Y. It then writes 7 back to the database (the third statement), and then overwrites that with 6, since Y holds 5.

This is called a race condition, since the final result depends on a race between the two operations. Race conditions are part of what makes programming large systems with many components a nightmare: they are difficult to spot in advance (since they are caused by the interactions between components, rather than by anything in any one of those components), and can be almost impossible to debug (since they usually occur intermittently and infrequently).

Transactions come to our rescue once again. If Gina and her labmate put their statements in transactions, the database will act as if it executed all of one and then all of the other. Whether or not it actually does this is up to whoever wrote the database manager: modern databases use very sophisticated algorithms to determine which operations actually have to be run sequentially, and which can safely be run in parallel to improve performance. The key thing is that every transaction will appear to have had the entire database to itself.

To end this chapter, let's have a look at how to access a database from a general-purpose programming language like Python. Other languages use almost exactly the same model: library and function names may differ, but the concepts are the same.

Here's a short Python program that selects latitudes and longitudes from an SQLite database stored in a file called survey.db:

import sqlite3
connection = sqlite3.connect("survey.db")
cursor = connection.cursor()
cursor.execute("select site.lat, site.long from site;")
results = cursor.fetchall()
for r in results:
    print r
cursor.close()
connection.close()

The program starts by importing the sqlite3 library. If we were connecting to MySQL, DB2, or some other database, we would import a different library, but all of them provide the same functions, so that the rest of our program does not have to change (at least, not much) if we switch from one database to another.

Line 2 establishes a connection to the database. Since we're using SQLite, all we need to specify is the name of the database file. Other systems may require us to provide a username and password as well. Line 3 then uses this connection to create a cursor; just like the cursor in an editor, its role is to keep track of where we are in the database.

On line 4, we use that cursor to ask the database to execute a query for us. The query is written in SQL, and passed to cursor.execute as a string. It's our job to make sure that SQL is properly formatted; if it isn't, or if something goes wrong when it is being executed, the database will report an error.

The database returns the results of the query to us in response to the cursor.fetchall call on line 5. This result is a list with one entry for each record in the result set; if we loop over that list (line 6) and print those list entries (line 7), we can see that each one is a tuple with one element for each field we asked for.

Finally, lines 8 and 9 close our cursor and our connection, since the database can only keep a limited number of these open at one time. Since establishing a connection takes time, though, we shouldn't open a connection, do one operation, then close the connection, only to reopen it a few microseconds later to do another operation. Instead, it's normal to create one connection that stays open for the lifetime of the program.

Queries in real applications will often depend on values provided by users. For example, a program might take a user ID as a command-line parameter and display the user's full name:

import sys
import sqlite3

query = "select personal, family from Person where ident='%s';"
user_id = sys.argv[1]

connection = sqlite3.connect("survey.db")
cursor = connection.cursor()

cursor.execute(query % user_id)
results = cursor.fetchall()
print results[0][0], results[0][1]

cursor.close()
connection.close()

The variable query holds the statement we want to execute with a %s format string where we want to insert the ID of the person we're looking up. It seems simple enough, but what happens if someone gives the program this input?

dyer"; drop table Survey; select "

It looks like there's garbage after the name of the project, but it is very carefully chosen garbage. If we insert this string into our query, the result is:

select personal, family from Person where ident='dyer'; drop table Survey; select '';

Whoops: if we execute this, it will erase one of the tables in our database.

This technique is called SQL injection, and it has been used to attack thousands of programs over the years. In particular, many web sites that take data from users insert values directly into queries without checking them carefully first.

Since a villain might try to smuggle commands into our queries in many different ways, the safest way to deal with this threat is to replace characters like quotes with their escaped equivalents, so that we can safely put whatever the user gives us inside a string. We can do this by using a prepared statement instead of formatting our statements as strings. Here's what our example program looks like if we do this:

import sys
import sqlite3

query = "select personal, family from Person where ident=?;"
user_id = sys.argv[1]

connection = sqlite3.connect("survey.db")
cursor = connection.cursor()

cursor.execute(query, [user_id])
results = cursor.fetchall()
print results[0][0], results[0][1]

cursor.close()
connection.close()

The key changes are in the query string and the execute call. Instead of formatting the query ourselves, we put question marks in the query template where we want to insert values. When we call execute, we provide a list that contains as many values as there are question marks in the query. The library matches values to question marks in order, and translates any special characters in the values into their escaped equivalents so that they are safe to use.