--- /dev/null
+% Databases and SQL
+% Greg Wilson
+% June 2013
+
+In the late 1920s and early 1930s,
+William Dyer,
+Frank Pabodie,
+and Valentina Roerich led expeditions to the
+[Pole of Inaccessibility](http://en.wikipedia.org/wiki/Pole_of_inaccessibility)
+in the South Pacific,
+and then onward to Antarctica.
+Two years ago,
+Gina Geographer discovered their expedition journals
+in a storage locker at Miskatonic University.
+She has scanned and OCR'd the data they contain,
+and wants to store that information
+in a way that will make search and analysis easy.
+
+Gina basically has three options:
+text files,
+a spreadsheet,
+or a database.
+Text files are easiest to create,
+and work well with version control,
+but she would then have to build all her search and analysis herself.
+Spreadsheets are good for doing simple analysis,
+but as she found in her last project,
+they don't handle large or complex data sets very well.
+She would therefore like to put her data in a database,
+and this chapter will show her how.
+
+As many scientists have found out the hard way,
+if collecting data is the first 90% of the work,
+managing it is the other 90%.
+In this chapter,
+we'll see how to use a database to store and analyze field observations.
+The techniques we will explore apply directly to other kinds of databases as well,
+and as we'll see,
+knowing how to get information *out* of a database is essential to
+figuring out how to put data *in*.
+
+## For Instructors {.guide}
+
+Relational databases are not as widely used in science as in business,
+but they are still a common way to store large data sets with complex structure.
+Even when the data itself isn't in a database,
+the metadata could be:
+for example,
+meteorological data might be stored in files on disk,
+but data about when and where observations were made,
+data ranges,
+and so on could be in a database
+to make it easier for scientists to find what they want to.
+
+The first few sections
+(up to [Ordering Results](#s:sort))
+usually go very quickly.
+The pace usually slows down a bit when null values and aggregation are discussed,
+mostly because learners have a lot of details to keep straight by this point.
+Things *really* slow down during the discussion of joins,
+but this is the key idea in the whole lesson:
+important ideas like primary keys and referential integrity only make sense
+once learners have seen how they're used in joins.
+It's worth going over things a couple of times if necessary
+(with lots of examples).
+
+The final three sections are independent of each other,
+and can be dropped if time is short.
+Of the three,
+people seem to care most about how to add data
+(which only takes a few minutes to demonstrate),
+and how to use databases from inside "real" programs.
+The material on transactions is more abstract than the rest,
+and should be omitted if [web programming](web.html)
+isn't being taught.
+Overall,
+this material takes three hours to present
+assuming that a short exercise is done with each topic.
+
+### Prerequisites {.prereq}
+
+Everything up to the [final section](#s:programming)
+only requires some understanding of Boolean operators,
+data types,
+and pipelines,
+and what's needed can actually be introduced on the fly.
+That [final section](#s:programming),
+which shows how to use databases from within programs,
+depends on most of the [basic Python material](python.html).
+
+### Teaching Notes {.notes}
+
+* It isn't necessary to cover [sets and dictionaries](setdict.html)
+ before this material,
+ but if that has been discussed,
+ it's helpful to point out that a relational table is a generalized dictionary.
+* Simple calculations are actually easier to do in a spreadsheet,
+ the advantages of using a database become clear
+ as soon as filtering and joins are needed.
+ Instructors may therefore want to show a spreadsheet
+ with the information from the four database tables
+ consolidated into a single sheet,
+ and demonstrate what's needed in both systems to answer questions like,
+ "What was the average radiation reading in 1931?"
+* Some learners may have heard that NoSQL databases
+ (i.e., ones that don't use the relational model)
+ are the next big thing,
+ and ask why we're not teaching those.
+ The answers are:
+ * Relational databases are far more widely used than NoSQL databases.
+ * We have far more experience with relational databases
+ than with any other kind,
+ so we have a better idea of what to teach
+ and how to teach it.
+ * NoSQL databases are as different from each other
+ as they are from relational databases.
+ Until a leader emerges,
+ it isn't clear *which* NoSQL database we should teach.
+* This discussion is a useful companion to that of vectorization
+ in the lesson on [numerical computing](numpy.html):
+ in both cases,
+ the key point is to describe *what* to do,
+ and let the computer figure out *how* to do it.
+
+## Selecting {#s:select}
+
+### Learning Objectives {.objectives}
+
+* Explain the difference between a table, a database, and a database manager.
+* Explain the difference between a field and a record.
+* Select specific fields from specific tables, and display them in a specific order.
+
+Duration: 15 minutes (not including time required to download database file and connect to it)
+
+### Lesson
+
+A [relational database](../gloss.html#relational-database)
+is a way to store and manipulate information
+that is arranged as [tables](../gloss.html#table).
+Each table has columns (also known as [fields](../gloss.html#field-database)) which describe the data,
+and rows (also known as [records](../gloss.html#record-database)) which contain the data.
+
+<a id="a:dbms"></a>
+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](../gloss.html#query))
+to a [database manager](../gloss.html#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.
+
+> ### Under the Hood {.box}
+>
+> Every database manager—Oracle,
+> IBM DB2, PostgreSQL, MySQL, Microsoft Access, and SQLite—stores
+> data in a different way,
+> so a database created with one cannot be used directly by another.
+> However,
+> every database manager can import and export data in a variety of formats,
+> so it *is* possible to move information from one to another.
+
+Queries are written in a language called [SQL](../gloss.html#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](#f:survey_db) shows
+a simple database that stores some of the data
+Gina extracted from the logs of those long-ago expeditions.
+It contains four tables:
+
+<div class="db">
+
+Table Purpose
+-------------------- --------------------
+`Person` People who took readings.
+`Site` Locations of observation sites.
+`Visited` When readings were taken at specific sites.
+`Survey` The actual measurement values.
+
+</div>
+
+**Person**
+
+<div class="db">
+
+ident personal family
+-------------------- -------------------- --------------------
+dyer William Dyer
+pb Frank Pabodie
+lake Anderson Lake
+roe Valentina Roerich
+danforth Frank Danforth
+
+</div>
+
+**Survey**
+
+<div class="db">
+
+taken person quant reading
+-------------------- -------------------- -------------------- --------------------
+619 dyer rad 9.82
+619 dyer sal 0.13
+622 dyer rad 7.8
+622 dyer sal 0.09
+734 pb rad 8.41
+734 lake sal 0.05
+734 pb temp -21.5
+735 pb rad 7.22
+735 NULL sal 0.06
+735 NULL temp -26.0
+751 pb rad 4.35
+751 pb temp -18.5
+751 lake sal 0.1
+752 lake rad 2.19
+752 lake sal 0.09
+752 lake temp -16.0
+752 roe sal 41.6
+837 lake rad 1.46
+837 lake sal 0.21
+837 roe sal 22.5
+844 roe rad 11.25
+
+</div>
+
+**Site**
+
+<div class="db">
+
+name lat long
+-------------------- -------------------- --------------------
+DR-1 -49.85 -128.57
+DR-3 -47.15 -126.72
+MSK-4 -48.87 -123.4
+
+</div>
+
+**Visited**
+
+<div class="db">
+
+ident site dated
+-------------------- -------------------- --------------------
+619 DR-1 1927-02-08
+622 DR-1 1927-02-10
+734 DR-3 1939-01-07
+735 DR-3 1930-01-12
+751 DR-3 1930-02-26
+752 DR-3 NULL
+837 MSK-4 1932-01-14
+844 DR-1 1932-03-22
+
+</div>
+
+<figcaption>Figure 1: Survey Database</figcaption>
+
+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](#s:null).
+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;
+
+<div class="db">
+
+-------------------- --------------------
+Dyer William
+Pabodie Frank
+Lake Anderson
+Roerich Valentina
+Danforth Frank
+-------------------- --------------------
+
+</div>
+
+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
+ ...> ;
+
+<div class="db">
+
+-------------------- --------------------
+Dyer William
+Pabodie Frank
+Lake Anderson
+Roerich Valentina
+Danforth Frank
+-------------------- --------------------
+
+</div>
+
+From now on,
+we won't bother to display the prompt(s) with our commands.
+
+> ### Case and Consistency {.box}
+>
+> We have written our command and the column names in lower case,
+> and the table name in title case,
+> but we could use any mix:
+> SQL is [case insensitive](../gloss.html#case-insensitive),
+> so we could write them all in upper case,
+> or even like this:
+>
+> ``` {.sql}
+> SeLeCt famILY, PERSonal frOM PERson;
+> ```
+>
+> But please don't:
+> large SQL queries are hard enough to read
+> without the extra cognitive load of random capitalization.
+
+> ### Displaying Results {.box}
+>
+> 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](https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/) plugin for Firefox
+> or the [database extension](https://github.com/catherinedevlin/ipython-sql) for the IPython Notebook,
+> our output will be displayed graphically
+> ([Figure 2](#f:firefox_output)
+> and [Figure 3](#f:notebook_output)).
+> We'll use a simple table-based display in these notes.
+>
+> <figure id="f:firefox_output">
+> <img src="db/firefox_output.png" alt="Firefox SQLite Manager Output" />
+> <figcaption>Figure 2: Firefox SQLite Manager Output</figcaption>
+> </figure>
+>
+> <figure id="f:notebook_output">
+> <img src="db/notebook_output.png" alt="IPython Notebook Database Extension Output" />
+> <figcaption>Figure 3: IPython Notebook Database Extension Output</figcaption>
+> </figure>
+
+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:
+
+``` {.sql}
+select personal, family from Person;
+```
+
+<div class="db">
+
+-------------------- --------------------
+William Dyer
+Frank Pabodie
+Anderson Lake
+Valentina Roerich
+Frank Danforth
+-------------------- --------------------
+
+</div>
+
+or even repeat columns:
+
+``` {.sql}
+select ident, ident, ident from Person;
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+dyer dyer dyer
+pb pb pb
+lake lake lake
+roe roe roe
+danforth danforth danforth
+-------------------- -------------------- --------------------
+
+</div>
+
+We will see ways to rearrange the rows [later](#s:sort).
+
+As a shortcut, we can select all of the columns in a table
+using the wildcard `*`:
+
+``` {.sql}
+select * from Person;
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+dyer William Dyer
+pb Frank Pabodie
+lake Anderson Lake
+roe Valentina Roerich
+danforth Frank Danforth
+-------------------- -------------------- --------------------
+
+</div>
+
+### Summary {.keypoints}
+
+* A relational database stores information in tables with fields and records.
+* A database manager is a program that manipulates a database.
+* The commands or queries given to a database manager are usually written in a specialized language called SQL.
+* SQL is case insensitive.
+* The rows and columns of a database table aren't stored in any particular order.
+* Use `select *fields* from *table*` to get all the values for specific fields from a single table.
+* Use `select * from *table*` to select everything from a table.
+
+### Challenges {.challenges}
+
+* Write a query that selects only site names from the `Site` table.
+
+* Many people format queries as:
+
+ ```
+ SELECT personal, family FROM person;
+ ```
+
+ or as:
+
+ ```
+ select Personal, Family from PERSON;
+ ```
+
+ What style do you find easiest to read, and why?
+
+## Removing Duplicates {#s:distinct}
+
+### Learning Objectives {.objectives}
+
+* Write queries that only display distinct results once.
+
+Duration: 5 minutes.
+
+### Lesson
+
+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:
+
+``` {.sql}
+select quant from Survey;
+```
+
+<div class="db">
+
+--------------------
+rad
+sal
+rad
+sal
+rad
+sal
+temp
+rad
+sal
+temp
+rad
+temp
+sal
+rad
+sal
+temp
+sal
+rad
+sal
+sal
+rad
+--------------------
+
+</div>
+
+We can eliminate the redundant output
+to make the result more readable
+by adding the `distinct` keyword
+to our query:
+
+``` {.sql}
+select distinct quant from Survey;
+```
+
+<div class="db">
+
+--------------------
+rad
+sal
+temp
+--------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select distinct taken, quant from Survey;
+```
+
+<div class="db">
+
+-------------------- --------------------
+619 rad
+619 sal
+622 rad
+622 sal
+734 rad
+734 sal
+734 temp
+735 rad
+735 sal
+735 temp
+751 rad
+751 temp
+751 sal
+752 rad
+752 sal
+752 temp
+837 rad
+837 sal
+844 rad
+-------------------- --------------------
+
+</div>
+
+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.
+
+### Summary {.keypoints}
+
+* Use `distinct` to eliminate duplicates from a query's output.
+
+### Challenges {.challenges}
+
+* Write a query that selects distinct dates from the `Site` table.
+
+* If you are using SQLite from the command line,
+ you can run a single query by passing it to the interpreter
+ right after the path to the database file:
+
+ ```
+ $ sqlite3 survey.db 'select * from Person;'
+ ```
+
+ <div class="db">
+
+ -------------------- -------------------- --------------------
+ dyer William Dyer
+ pb Frank Pabodie
+ lake Anderson Lake
+ roe Valentina Roerich
+ danforth Frank Danforth
+ -------------------- -------------------- --------------------
+
+ </div>
+
+ Fill in the missing commands in the pipeline below
+ so that the output contains no redundant values.
+
+ ```
+ $ sqlite3 survey.db 'select person, quant from Survey;' | ____ | ____
+ ```
+
+ Do you think this is less efficient, just as efficient, or more efficient
+ that using `distinct` for large data?
+
+## Filtering {#s:filter}
+
+### Learning Objectives {.objectives}
+
+* Write queries that select records based on the values of their fields.
+* Write queries that select records using combinations of several tests on their fields' values.
+* Build up complex filtering criteria incrementally.
+* Explain the logical order in which filtering by field value and displaying fields takes place.
+
+Duration: 5-10 minutes.
+
+### Lesson
+
+One of the most powerful features of a database is
+the ability to [filter](../gloss.html#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:
+
+``` {.sql}
+select * from Visited where site='DR-1';
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+619 DR-1 1927-02-08
+622 DR-1 1927-02-10
+844 DR-1 1932-03-22
+-------------------- -------------------- --------------------
+
+</div>
+
+The database manager executes this query in two stages
+([Figure 4](#f:pipeline_where)).
+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.
+
+ <figure id="f:pipeline_where">
+ <img src="db/pipeline_where.png" alt="Two-Stage Query Processing Pipeline" />
+ <figcaption>Figure 4: Two-Stage Query Processing Pipeline</figcaption>
+ </figure>
+
+This processing order means that
+we can filter records using `where`
+based on values in columns that aren't then displayed:
+
+``` {.sql}
+select ident from Visited where site='DR-1';
+```
+
+<div class="db">
+
+--------------------
+619
+622
+844
+--------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select * from Visited where (site='DR-1') and (dated>='1930-00-00');
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+844 DR-1 1932-03-22
+-------------------- -------------------- --------------------
+
+</div>
+
+(The parentheses around the individual tests aren't strictly required,
+but they help make the query easier to read.)
+
+> ### Working With Dates {#a:dates .box}
+>
+> Most database managers have a special data type for dates.
+> In fact, many have two:
+> one for dates,
+> such as "May 31, 1971",
+> and one for durations,
+> such as "31 days".
+> SQLite doesn't:
+> instead,
+> it stores dates as either text
+> (in the ISO-8601 standard format "YYYY-MM-DD HH:MM:SS.SSSS"),
+> real numbers
+> (the number of days since November 24, 4714 BCE),
+> or integers
+> (the number of seconds since midnight, January 1, 1970).
+> If this sounds complicated,
+> it is,
+> but not nearly as complicated as figuring out
+> [historical dates in Sweden](http://en.wikipedia.org/wiki/Swedish_calendar).
+
+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`:
+
+``` {.sql}
+select * from Survey where person='lake' or person='roe';
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- --------------------
+734 lake sal 0.05
+751 lake sal 0.1
+752 lake rad 2.19
+752 lake sal 0.09
+752 lake temp -16.0
+752 roe sal 41.6
+837 lake rad 1.46
+837 lake sal 0.21
+837 roe sal 22.5
+844 roe rad 11.25
+-------------------- -------------------- -------------------- --------------------
+
+</div>
+
+Alternatively,
+we can use `in` to see if a value is in a specific set:
+
+``` {.sql}
+select * from Survey where person in ('lake', 'roe');
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- --------------------
+734 lake sal 0.05
+751 lake sal 0.1
+752 lake rad 2.19
+752 lake sal 0.09
+752 lake temp -16.0
+752 roe sal 41.6
+837 lake rad 1.46
+837 lake sal 0.21
+837 roe sal 22.5
+844 roe rad 11.25
+-------------------- -------------------- -------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select * from Survey where quant='sal' and person='lake' or person='roe';
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- --------------------
+734 lake sal 0.05
+751 lake sal 0.1
+752 lake sal 0.09
+752 roe sal 41.6
+837 lake sal 0.21
+837 roe sal 22.5
+844 roe rad 11.25
+-------------------- -------------------- -------------------- --------------------
+
+</div>
+
+which is salinity measurements by Lake,
+and *any* measurement by Roerich.
+We probably want this instead:
+
+``` {.sql}
+select * from Survey where quant='sal' and (person='lake' or person='roe');
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- --------------------
+734 lake sal 0.05
+751 lake sal 0.1
+752 lake sal 0.09
+752 roe sal 41.6
+837 lake sal 0.21
+837 roe sal 22.5
+-------------------- -------------------- -------------------- --------------------
+
+</div>
+
+Finally,
+we can use `distinct` with `where`
+to give a second level of filtering:
+
+``` {.sql}
+select distinct person, quant from Survey where person='lake' or person='roe';
+```
+
+<div class="db">
+
+-------------------- --------------------
+lake sal
+lake rad
+lake temp
+roe sal
+roe rad
+-------------------- --------------------
+
+</div>
+
+But remember:
+`distinct` is applied to the values displayed in the chosen columns,
+not to the entire rows as they are being processed.
+
+> ### Growing Queries {.box}
+>
+> What we have just done is how most people "grow" their SQL queries.
+> We started with something simple that did part of what we wanted,
+> then added more clauses one by one,
+> testing their effects as we went.
+> This is a good strategy—in fact,
+> for complex queries it's often the *only* strategy—but
+> it depends on quick turnaround,
+> and on us recognizing the right answer when we get it.
+>
+> The best way to achieve quick turnaround is often
+> to put a subset of data in a temporary database
+> and run our queries against that,
+> or to fill a small database with synthesized records.
+> For example,
+> instead of trying our queries against an actual database of 20 million Australians,
+> we could run it against a sample of ten thousand,
+> or write a small program to generate ten thousand random (but plausible) records
+> and use that.
+
+### Summary {.keypoints}
+
+* Use `where *test*` in a query to filter records based on Boolean tests.
+* Use `and` and `or` to combine tests.
+* Use `in` to check if a value is in a set.
+* Build up queries a bit at a time, and test them against small data sets.
+
+### Challenges {.challenges}
+
+* Gina wants to select all sites that lie within 30° of the equator.
+ Her query is:
+
+ ``` {.sql}
+ select * from Site where (lat > -30) or (lat < 30);
+ ``` {.sql}
+
+ Explain why this is wrong,
+ and rewrite the query so that it is correct.
+
+* Normalized salinity readings are supposed to be between 0.0 and 1.0.
+ Write a query that selects all records from `Survey`
+ with salinity values outside this range.
+
+* The SQL test `*column-name* like *pattern*`
+ is true if the value in the named column
+ matches the pattern given;
+ the character '%' can be used any number of times in the pattern
+ to mean "match zero or more characters".
+
+ Expression Value
+ -------------------- --------------------
+ `'a' like 'a'` `True`
+ `'a' like '%a'` `True`
+ `'b' like '%a'` `False`
+ `'alpha' like 'a%'` `True`
+ `'alpha' like 'a%p%'` `True`
+ `'beta' like 'a%p%'` `False`
+
+ The expression `*column-name* not like *pattern*`
+ inverts the test.
+ Using `like`,
+ write a query that finds all the records in `Visited`
+ that *aren't* from sites labelled 'DR-something'.
+
+## Calculating New Values {#s:calc}
+
+### Learning Objectives {.objectives}
+
+* Write queries that do arithmetic using the values in individual records.
+
+Duration: 5 minutes.
+
+### Lesson
+
+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:
+
+``` {.sql}
+select 1.05 * reading from Survey where quant='rad';
+```
+
+<div class="db">
+
+--------------------
+10.311
+8.19
+8.8305
+7.581
+4.5675
+2.2995
+1.533
+11.8125
+--------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select taken, round(5*(reading-32)/9, 2) from Survey where quant='temp';
+```
+
+<div class="db">
+
+-------------------- --------------------
+734 -29.72
+735 -32.22
+751 -28.06
+752 -26.67
+-------------------- --------------------
+
+</div>
+
+We can also combine values from different fields,
+for example by using the string concatenation operator `||`:
+
+``` {.sql}
+select personal || ' ' || family from Person;
+```
+
+<div class="db">
+
+--------------------
+William Dyer
+Frank Pabodie
+Anderson Lake
+Valentina Roerich
+Frank Danforth
+--------------------
+
+</div>
+
+> ### A Note on Names {.box}
+>
+> It may seem strange to use `personal` and `family` as field names
+> instead of `first` and `last`,
+> but it's a necessary first step toward handling cultural differences.
+> For example,
+> consider the following rules:
+>
+> Full Name Alphabetized Under Reason
+> -------------------- -------------------- --------------------
+> Liu Xiaobo Liu Chinese family names come first
+> Leonardo da Vinci Leonardo "da Vinci" just means "from Vinci"
+> Catherine de Medici Medici family name
+> Jean de La Fontaine La Fontaine family name is "La Fontaine"
+> Juan Ponce de Leon Ponce de Leon full family name is "Ponce de Leon"
+> Gabriel Garcia Marquez Garcia Marquez double-barrelled Spanish surnames
+> Wernher von Braun von *or* Braun depending on whether he was in Germany or the US
+> Elizabeth Alexandra May Windsor Elizabeth monarchs alphabetize by the name under which they reigned
+> Thomas a Beckett Thomas and saints according to the names by which they were canonized
+>
+> Clearly,
+> even a two-part division into "personal" and "family"
+> isn't enough...
+
+### Summary {.keypoints}
+
+* Use expressions as fields to calculate per-record values.</li>
+
+### Challenges {.challenges}
+
+* After further reading,
+ Gina realizes that Valentina Roerich
+ was reporting salinity as percentages.
+ Write a query that returns all of her salinity measurements
+ from the `Survey` table
+ with the values divided by 100.
+
+* The `union` operator combines the results of two queries:
+
+ ``` {.sql}
+ select * from Person where ident='dyer' union select * from Person where ident='roe';
+ ```
+
+ <div class="db">
+
+ -------------------- -------------------- --------------------
+ dyer William Dyer
+ roe Valentina Roerich
+ -------------------- -------------------- --------------------
+
+ </div>
+
+ Use `union` to create a consolidated list of salinity measurements
+ in which Roerich's, and only Roerich's,
+ have been corrected as described in the previous challenge.
+ The output should be something like:
+
+ <div class="db">
+
+ -------------------- --------------------
+ 619 0.13
+ 622 0.09
+ 734 0.05
+ 751 0.1
+ 752 0.09
+ 752 0.416
+ 837 0.21
+ 837 0.225
+ -------------------- --------------------
+
+ </div>
+
+* The site identifiers in the `Visited` table have two parts
+ separated by a '-':
+
+ ``` {.sql}
+ select distinct site from Visited;
+ ```
+
+ <div class="db">
+
+ --------------------
+ DR-1
+ DR-3
+ MSK-4
+ --------------------
+
+ </div>
+
+ Some major site identifiers are two letters long and some are three.
+ The "in string" function `instr(X, Y)`
+ returns the 1-based index of the first occurrence of string Y in string X,
+ or 0 if Y does not exist in X.
+ The substring function `substr(X, I)`
+ returns the substring of X starting at index I.
+ Use these two functions to produce a list of unique major site identifiers.
+ (For this data,
+ the list should contain only "DR" and "MSK").
+
+* Pabodie's journal notes that all his temperature measurements
+ are in °F,
+ but Lake's journal does not report whether he used °F or °C.
+ How should Gina treat his measurements,
+ and why?
+
+## Ordering Results {#s:sort}
+
+### Learning Objectives {.objectives}
+
+* Write queries that order results according to fields' values.
+* Write queries that order results according to calculated values.
+* Explain why it is possible to sort records using the values of fields that are not displayed.
+
+Duration: 5 minutes.
+
+### Lesson
+
+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:
+
+``` {.sql}
+select reading from Survey where quant='rad' order by reading;
+```
+
+<div class="db">
+
+--------------------
+1.46
+2.19
+4.35
+7.22
+7.8
+8.41
+9.82
+11.25
+--------------------
+
+</div>
+
+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"):
+
+``` {.sql}
+select reading from Survey where quant='rad' order by reading desc;
+```
+
+<div class="db">
+
+--------------------
+11.25
+9.82
+8.41
+7.8
+7.22
+4.35
+2.19
+1.46
+--------------------
+
+</div>
+
+(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:
+
+``` {.sql}
+select taken, person from Survey order by taken asc, person desc;
+```
+
+<div class="db">
+
+-------------------- --------------------
+619 dyer
+619 dyer
+622 dyer
+622 dyer
+734 pb
+734 pb
+734 lake
+735 pb
+735
+735
+751 pb
+751 pb
+751 lake
+752 roe
+752 lake
+752 lake
+752 lake
+837 roe
+837 lake
+837 lake
+844 roe
+-------------------- --------------------
+
+</div>
+
+This is easier to understand if we also remove duplicates:
+
+``` {.sql}
+select distinct taken, person from Survey order by taken asc, person desc;
+```
+
+<div class="db">
+
+-------------------- --------------------
+619 dyer
+622 dyer
+734 pb
+734 lake
+735 pb
+735
+751 pb
+751 lake
+752 roe
+752 lake
+837 roe
+837 lake
+844 roe
+-------------------- --------------------
+
+</div>
+
+Since sorting happens before columns are filtered,
+we can sort by a field that isn't actually displayed:
+
+``` {.sql}
+select reading from Survey where quant='rad' order by taken;
+```
+
+<div class="db">
+
+--------------------
+9.82
+7.8
+8.41
+7.22
+4.35
+2.19
+1.46
+11.25
+--------------------
+
+</div>
+
+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):
+
+``` {.sql}
+select random(), ident from Person;
+```
+
+<div class="db">
+
+-------------------- --------------------
+-6309766557809954936 dyer
+-2098461436941487136 pb
+-2248225962969032314 lake
+6062184424509295966 roe
+-1268956870222271271 danforth
+-------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select ident from Person order by random();
+```
+
+<div class="db">
+
+--------------------
+danforth
+pb
+dyer
+lake
+roe
+--------------------
+
+</div>
+
+``` {.sql}
+select ident from Person order by random();
+```
+
+<div class="db">
+
+--------------------
+roe
+dyer
+pb
+lake
+danforth
+--------------------
+
+</div>
+
+Our query pipeline now has four stages
+([Figure 5](#f:pipeline_sort_distinct)):
+
+* Select the rows that pass the `where` criteria.
+* Sort them if required.
+* Filter the columns according to the `select` criteria.
+* Remove duplicates if required.
+
+ <figure id="f:pipeline_sort_distinct">
+ <img src="db/pipeline_sort_distinct.png" alt="Four-Stage Query Processing Pipeline" />
+ <figcaption>Figure 5: Four-Stage Query Processing Pipeline</figcaption>
+ </figure>
+
+### Summary {.keypoints}
+
+* Use `order by` (with `asc` or `desc`) to order a query's results.
+* Use `random` to generate pseudo-random numbers.
+
+### Challenges {.challenges}
+
+* Create a list of sites identifiers
+ and their distance from the equator in kilometers,
+ sorted from furthest to closest.
+ (A degree of latitude corresponds to 111.12 km.)
+
+* Gina needs a list of radiation measurements from all sites
+ sorted by when they were taken.
+ The query:
+
+ ``` {.sql}
+ select * from Survey where quant='rad' order by taken;
+ ```
+
+ produces the correct answer for the data used in our examples.
+ Explain when and why it might produce the wrong answer.
+
+## Missing Data {#s:null}
+
+### Learning Objectives {.objectives}
+
+* Explain what databases use the special value `NULL` to represent.
+* Explain why databases should *not* uses their own special values (like 9999 or "N/A") to represent missing or unknown data.
+* Explain what atomic and aggregate calculations involving `NULL` produce, and why.
+* Write queries that include or exclude records containing `NULL`.
+
+Duration: 10-20 minutes
+(depending on whether or not the instructor includes an anecdote about
+what happens when you *don't* take missing data into account).
+
+### Lesson
+
+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:
+
+``` {.sql}
+select * from Visited;
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+619 DR-1 1927-02-08
+622 DR-1 1927-02-10
+734 DR-3 1939-01-07
+735 DR-3 1930-01-12
+751 DR-3 1930-02-26
+752 DR-3
+837 MS-4 1932-01-14
+844 DR-1 1932-03-22
+-------------------- -------------------- --------------------
+
+</div>
+
+> ### Displaying Nulls {.box}
+>
+> Different databases display nulls differently.
+> Unfortunately,
+> SQLite's default is to print nothing at all,
+> which makes nulls easy to overlook
+> (particularly if they're in the middle of a long row).
+
+Null doesn't behave like other values.
+If we select the records that come before 1930:
+
+``` {.sql}
+select * from Visited where dated<'1930-00-00';
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+619 DR-1 1927-02-08
+622 DR-1 1927-02-10
+-------------------- -------------------- --------------------
+
+</div>
+
+we get two results,
+and if we select the ones that come during or after 1930:
+
+``` {.sql}
+select * from Visited where dated>='1930-00-00';
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+734 DR-3 1939-01-07
+735 DR-3 1930-01-12
+751 DR-3 1930-02-26
+837 MS-4 1932-01-14
+844 DR-1 1932-03-22
+-------------------- -------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select * from Visited where dated=NULL;
+```
+
+``` {.sql}
+select * from Visited where dated!=NULL;
+```
+
+To check whether a value is `null` or not,
+we must use a special test `is null`:
+
+``` {.sql}
+select * from Visited where dated is NULL;
+```
+
+<div class="db">
+
+-------------------- --------------------
+752 DR-3
+-------------------- --------------------
+
+</div>
+
+or its inverse `is not null`:
+
+``` {.sql}
+select * from Visited where dated is not NULL;
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+619 DR-1 1927-02-08
+622 DR-1 1927-02-10
+734 DR-3 1939-01-07
+735 DR-3 1930-01-12
+751 DR-3 1930-02-26
+837 MS-4 1932-01-14
+844 DR-1 1932-03-22
+-------------------- -------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select * from Survey where quant='sal' and person!='lake';
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- --------------------
+619 dyer sal 0.13
+622 dyer sal 0.09
+752 roe sal 41.6
+837 roe sal 22.5
+-------------------- -------------------- -------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select * from Survey where quant='sal' and (person!='lake' or person is null);
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- --------------------
+619 dyer sal 0.13
+622 dyer sal 0.09
+735 sal 0.06
+752 roe sal 41.6
+837 roe sal 22.5
+-------------------- -------------------- -------------------- --------------------
+
+</div>
+
+
+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.
+
+> ### What Happens When You Forget {.box}
+>
+> Several years ago,
+> I was helping a group who were looking at
+> the spread of drug-resistant tuberculosis (DRTB)
+> in industrialized countries.
+> In particular,
+> they wanted to know if it was spreading faster among less affluent people.
+>
+> We tackled the problem by combining two data sets.
+> The first gave us skin and blood test results for DRTB
+> along with patients' postal codes
+> (the only identifying information we were allowed---we didn't even have gender).
+> The second was Canadian census data that gave us
+> median income per postal code.
+> Since a PC is about 300-800 people,
+> we felt justified in joining the first with the second
+> to estimate incomes for people with positive and negative test results.
+>
+> To our surprise,
+> we didn't find a correlation between income and infection.
+> We were just about to publish when someone spotted the mistake I'd made.
+>
+> Question: Who *doesn't* have a postal code?
+>
+> Answer: Homeless people.
+>
+> When I did the join,
+> I was throwing away homeless people,
+> which introduced a statistically significant error in my results.
+> But I couldn't just set the income of anyone without a postal code to zero,
+> because our sample included another set of people without postal codes:
+> 16-21 year olds whose addresses were suppressed
+> because they had tested positive for sexually-transmitted diseases.
+>
+> At this point the problem is no longer a database issue,
+> but rather a question of statistics.
+> The takeaway is,
+> checking your queries when you're programming is as important as
+> checking your samples when you're doing chemistry.
+
+### Summary {.keypoints}
+
+* Use `null` in place of missing information.
+* Almost every operation involving `null` produces `null` as a result.
+* Test for nulls using `is null` and `is not null`.
+
+### Challenges {.challenges}
+
+* Write a query that sorts the records in `Visited` by date,
+ omitting entries for which the date is not known
+ (i.e., is null).
+
+* What do you expect the query:
+
+ ``` {.sql}
+ select * from Visited where dated in ('1927-02-08', null);
+ ```
+
+ to produce?
+ What does it actually produce?
+
+* Some database designers prefer to use
+ a [sentinel value](../gloss.html#sentinel-value)
+ to mark missing data rather than `null`.
+ For example,
+ they will use the date "0000-00-00" to mark a missing date,
+ or -1.0 to mark a missing salinity or radiation reading
+ (since actual readings cannot be negative).
+ What does this simplify?
+ What burdens or risks does it introduce?
+
+## Aggregation {#s:aggregate}
+
+### Learning Objectives {.objectives}
+
+* Write queries that combine values from many records to create a single aggregate value.
+* Write queries that put records into groups based on their values.
+* Write queries that combine values group by group.
+* Explain what is displayed for *unaggregated* fields when some fields are aggregated.
+
+Duration: 10 minutes.
+
+### Lesson
+
+Gina now wants to calculate ranges and averages for her data.
+She knows how to select all of the dates from the `Visited` table:
+
+``` {.sql}
+select dated from Visited;
+```
+
+<div class="db">
+
+--------------------
+1927-02-08
+1927-02-10
+1939-01-07
+1930-01-12
+1930-02-26
+
+1932-01-14
+1932-03-22
+--------------------
+
+</div>
+
+but to combine them,
+she must use an [aggregation function](../gloss.html#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:
+
+``` {.sql}
+select min(dated) from Visited;
+```
+
+<div class="db">
+
+--------------------
+1927-02-08
+--------------------
+
+</div>
+
+``` {.sql}
+select max(dated) from Visited;
+```
+
+<div class="db">
+
+--------------------
+1939-01-07
+--------------------
+
+</div>
+
+`min` and `max` are just two of
+the aggregation functions built into SQL.
+Three others are `avg`,
+`count`,
+and `sum`:
+
+``` {.sql}
+select avg(reading) from Survey where quant='sal';
+```
+
+<div class="db">
+
+--------------------
+7.20333333333
+--------------------
+
+</div>
+
+``` {.sql}
+select count(reading) from Survey where quant='sal';
+```
+
+<div class="db">
+
+--------------------
+9
+--------------------
+
+</div>
+
+``` {.sql}
+select sum(reading) from Survey where quant='sal';
+```
+
+<div class="db">
+
+--------------------
+64.83
+--------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select min(reading), max(reading) from Survey where quant='sal' and reading<=1.0;
+```
+
+<div class="db">
+
+-------------------- --------------------
+0.05 0.21
+-------------------- --------------------
+
+</div>
+
+We can also combine aggregated results with raw results,
+although the output might surprise you:
+
+``` {.sql}
+select person, count(*) from Survey where quant='sal' and reading<=1.0;
+```
+
+<div class="db">
+
+-------------------- --------------------
+lake 7
+-------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+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:
+
+``` {.sql}
+select min(dated) from Visited;
+```
+
+<div class="db">
+
+--------------------
+1927-02-08
+--------------------
+
+</div>
+
+instead of always having to filter explicitly:
+
+``` {.sql}
+select min(dated) from Visited where dated is not null;
+```
+
+<div class="db">
+
+--------------------
+1927-02-08
+--------------------
+
+</div>
+
+### Summary {.keypoints}
+
+* Use aggregation functions like `sum` and `max` to combine query results.
+* Use `count` function to count the number of results.
+* If some fields are aggregated and others are not, the database manager chooses an arbitrary result for the unaggregated field.
+* Most aggregation functions skip nulls when combining values.
+
+### Challenges {.challenges}
+
+* How many temperature readings did Frank Pabodie record,
+ and what was their average value?
+
+* The average of a set of values is the sum of the values
+ divided by the number of values.
+ Does this mean that the `avg` function returns 2.0 or 3.0
+ when given the values 1.0, `null`, and 5.0?
+
+* Gina wants to calculate the difference between
+ each individual radiation reading
+ and the average of all the radiation readings.
+ She writes the query:
+
+ ``` {.sql}
+ select reading-avg(reading) from Survey where quant='rad';
+ ```
+
+ What does this actually produce, and why?
+
+* The function `group_concat(field, separator)`
+ concatenates all the values in a field
+ using the specified separator character
+ (or ',' if the separator isn't specified).
+ Use this to produce a one-line list of scientists' names,
+ such as:
+
+ ``` {.sql}
+ William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth
+ ```
+
+ Can you find a way to order the list by surname?
+
+## Grouping {#s:grouping}
+
+### Learning Objectives {.objectives}
+
+* Group results to be aggregated separately.
+* Explain when grouping occurs in the processing pipeline.
+
+Duration: 5 minutes.
+
+### Lesson
+
+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:
+
+``` {.sql}
+select person, count(reading), round(avg(reading), 2)
+from Survey
+where quant='rad';
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+roe 8 6.56
+-------------------- -------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select person, count(reading), round(avg(reading), 2)
+from Survey
+where quant='rad'
+and person='dyer';
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+dyer 2 8.81
+-------------------- -------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select person, count(reading), round(avg(reading), 2)
+from Survey
+where quant='rad'
+group by person;
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+dyer 2 8.81
+lake 2 1.82
+pb 3 6.66
+roe 1 11.25
+-------------------- -------------------- --------------------
+
+</div>
+
+`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](#f:grouped_aggregation)).
+
+ <figure id="f:grouped_aggregation">
+ <img src="db/grouped_aggregation.png" alt="Grouped Aggregation" />
+ <figcaption>Figure 6: Grouped Aggregation</figcaption>
+ </figure>
+
+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:
+
+``` {.sql}
+select person, quant, count(reading), round(avg(reading), 2)
+from Survey
+group by person, quant;
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- --------------------
+ sal 1 0.06
+ temp 1 -26.0
+dyer rad 2 8.81
+dyer sal 2 0.11
+lake rad 2 1.82
+lake sal 4 0.11
+lake temp 1 -16.0
+pb rad 3 6.66
+pb temp 2 -20.0
+roe rad 1 11.25
+roe sal 2 32.05
+-------------------- -------------------- -------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+select person, quant, count(reading), round(avg(reading), 2)
+from Survey
+where person is not null
+group by person, quant
+order by person, quant;
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- --------------------
+dyer rad 2 8.81
+dyer sal 2 0.11
+lake rad 2 1.82
+lake sal 4 0.11
+lake temp 1 -16.0
+pb rad 3 6.66
+pb temp 2 -20.0
+roe rad 1 11.25
+roe sal 2 32.05
+-------------------- -------------------- -------------------- --------------------
+
+</div>
+
+Looking more closely,
+this query:
+
+* selected records from the `Survey` table
+ where the `person` field was not null;
+
+* grouped those records into subsets
+ so that the `person` and `quant` values in each subset
+ were the same;
+
+* ordered those subsets first by `person`,
+ and then within each sub-group by `quant`;
+ and
+
+* 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](#f:pipeline_grouping).
+
+ <figure id="f:pipeline_grouping">
+ <img src="db/pipeline_grouping.png" alt="Query Processing Pipeline With Grouping" />
+ <figcaption>Figure 7: Query Processing Pipeline With Grouping</figcaption>
+ </figure>
+
+### Summary {.keypoints}
+
+* Use `group by` to group values for separate aggregation.
+
+### Challenges {.challenges}
+
+* Write a single query that finds the earliest and latest date
+ that each site was visited.
+
+* Show the records produced by each stage of
+ [Figure 7](#f:pipeline_grouping)
+ for the following query:
+
+ ``` {.sql}
+ select min(reading), max(reading) from Survey
+ where taken in (734, 735)
+ and quant='temp'
+ group by taken, quant;
+ ```
+
+* How can the query in the previous challenge be simplified
+ without changing its result?
+
+## Combining Data {#s:join}
+
+### Learning Objectives {.objectives}
+
+* Explain what primary keys and foreign keys are.
+* Write queries that combine information from two or more tables by matching keys.
+* Write queries using aliases for table names.
+* Explain why the `tablename.fieldname` notation is needed when tables are joined.
+* Explain the logical sequence of operations that occurs when two or more tables are joined.
+
+Duration: 20 minutes (and expect to have to walk through an example step-by-step).
+
+### Lesson
+
+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 `Site` and `Visited` tables:
+
+``` {.sql}
+select * from Site join Visited;
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
+DR-1 -49.85 -128.57 619 DR-1 1927-02-08
+DR-1 -49.85 -128.57 622 DR-1 1927-02-10
+DR-1 -49.85 -128.57 734 DR-3 1939-01-07
+DR-1 -49.85 -128.57 735 DR-3 1930-01-12
+DR-1 -49.85 -128.57 751 DR-3 1930-02-26
+DR-1 -49.85 -128.57 752 DR-3
+DR-1 -49.85 -128.57 837 MS-4 1932-01-14
+DR-1 -49.85 -128.57 844 DR-1 1932-03-22
+DR-3 -47.15 -126.72 619 DR-1 1927-02-08
+DR-3 -47.15 -126.72 622 DR-1 1927-02-10
+DR-3 -47.15 -126.72 734 DR-3 1939-01-07
+DR-3 -47.15 -126.72 735 DR-3 1930-01-12
+DR-3 -47.15 -126.72 751 DR-3 1930-02-26
+DR-3 -47.15 -126.72 752 DR-3
+DR-3 -47.15 -126.72 837 MS-4 1932-01-14
+DR-3 -47.15 -126.72 844 DR-1 1932-03-22
+MS-4 -48.87 -123.4 619 DR-1 1927-02-08
+MS-4 -48.87 -123.4 622 DR-1 1927-02-10
+MS-4 -48.87 -123.4 734 DR-3 1939-01-07
+MS-4 -48.87 -123.4 735 DR-3 1930-01-12
+MS-4 -48.87 -123.4 751 DR-3 1930-02-26
+MS-4 -48.87 -123.4 752 DR-3
+MS-4 -48.87 -123.4 837 MS-4 1932-01-14
+MS-4 -48.87 -123.4 844 DR-1 1932-03-22
+-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
+
+</div>
+
+`join` creates
+the [cross product](../gloss.html#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:
+
+``` {.sql}
+select * from Site join Visited on Site.name=Visited.site;
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
+DR-1 -49.85 -128.57 619 DR-1 1927-02-08
+DR-1 -49.85 -128.57 622 DR-1 1927-02-10
+DR-1 -49.85 -128.57 844 DR-1 1932-03-22
+DR-3 -47.15 -126.72 734 DR-3 1939-01-07
+DR-3 -47.15 -126.72 735 DR-3 1930-01-12
+DR-3 -47.15 -126.72 751 DR-3 1930-02-26
+DR-3 -47.15 -126.72 752 DR-3
+MS-4 -48.87 -123.4 837 MS-4 1932-01-14
+-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
+
+</div>
+
+`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:
+
+``` {.sql}
+select Site.lat, Site.long, Visited.dated
+from Site join Visited
+on Site.name=Visited.site;
+```
+
+<div class="db">
+
+-------------------- -------------------- --------------------
+-49.85 -128.57 1927-02-08
+-49.85 -128.57 1927-02-10
+-49.85 -128.57 1932-03-22
+-47.15 -126.72
+-47.15 -126.72 1930-01-12
+-47.15 -126.72 1930-02-26
+-47.15 -126.72 1939-01-07
+-48.87 -123.4 1932-01-14
+-------------------- -------------------- --------------------
+
+</div>
+
+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:
+
+``` {.sql}
+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;
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- -------------------- --------------------
+-49.85 -128.57 1927-02-08 rad 9.82
+-49.85 -128.57 1927-02-08 sal 0.13
+-49.85 -128.57 1927-02-10 rad 7.8
+-49.85 -128.57 1927-02-10 sal 0.09
+-47.15 -126.72 1939-01-07 rad 8.41
+-47.15 -126.72 1939-01-07 sal 0.05
+-47.15 -126.72 1939-01-07 temp -21.5
+-47.15 -126.72 1930-01-12 rad 7.22
+-47.15 -126.72 1930-01-12 sal 0.06
+-47.15 -126.72 1930-01-12 temp -26.0
+-47.15 -126.72 1930-02-26 rad 4.35
+-47.15 -126.72 1930-02-26 sal 0.1
+-47.15 -126.72 1930-02-26 temp -18.5
+-48.87 -123.4 1932-01-14 rad 1.46
+-48.87 -123.4 1932-01-14 sal 0.21
+-48.87 -123.4 1932-01-14 sal 22.5
+-49.85 -128.57 1932-03-22 rad 11.25
+-------------------- -------------------- -------------------- -------------------- --------------------
+
+</div>
+
+<a id="a:keys"></a>
+We can tell which records from `Site`, `Visited`, and `Survey`
+correspond with each other
+because those tables contain
+[primary keys](../gloss.html#primary-key)
+and [foreign keys](../gloss.html#foreign-key).
+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:
+
+``` {.sql}
+select rowid, * from Person;
+```
+
+<div class="db">
+
+-------------------- -------------------- -------------------- --------------------
+1 dyer William Dyer
+2 pb Frank Pabodie
+3 lake Anderson Lake
+4 roe Valentina Roerich
+5 danforth Frank Danforth
+-------------------- -------------------- -------------------- --------------------
+
+</div>
+
+### Summary {.keypoints}
+
+* Use `join` to create all possible combinations of records from two or more tables.
+* Use `join *tables* on *test*` to keep only those combinations that pass some test.
+* Use `*table*.*field*` to specify a particular field of a particular table.
+* Every record in a table should be uniquely identified by the value of its primary key.
+
+### Challenges {.challenges}
+
+* 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:
+
+ ``` {.sql}
+ select Site.name from Site join Visited
+ on Site.lat<-49.0 and Site.name=Visited.site and Visited.dated>='1932-00-00';
+ ```
+
+* Why does the `Person` table have an `ident` field?
+ Why do we not just use scientists' names in the `Survey` table?
+
+* Why does the table `Site` exist?
+ Why didn't Gina just record latitudes and longitudes
+ directly in the `Visited` and `Survey` tables?
+
+## Creating and Modifying Tables {#s:create}
+
+### Learning Objectives {.box}
+
+* Write queries that create database tables with fields of common types.
+* Write queries that specify the primary and foreign key relationships of tables.
+* Write queries that specify whether field values must be unique and/or are allowed to be `null`.
+* Write queries that erase database tables.
+* Write queries that add records to database tables.
+* Write queries that delete specific records from tables.
+* Explain what referential integrity is, and how a database can become inconsistent as data is changed.
+
+Duration: 10 minutes.
+
+### Lesson
+
+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:
+
+<div class="db">
+
+-------------------- --------------------
+`integer` A signed integer.
+`real` A floating point value.
+`text` A string.
+`blob` Any "binary large object" such as an image or audio file.
+-------------------- --------------------
+
+</div>
+
+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](#a:dates).
+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:
+
+``` {.sql}
+select count(*) from Survey where person='lake';
+```
+
+<div class="db">
+
+--------------------
+7
+--------------------
+
+</div>
+
+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](../gloss.html#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](../gloss.html#cascading-delete).
+However,
+this technique is outside the scope of this chapter.
+
+> ### Other Ways to Do It {#a:hybrid .box}
+>
+> Many applications use a hybrid storage model
+> instead of putting everything into a database:
+> the actual data (such as astronomical images) is stored in files,
+> while the database stores the files' names,
+> their modification dates,
+> the region of the sky they cover,
+> their spectral characteristics,
+> and so on.
+> This is also how most music player software is built:
+> the database inside the application keeps track of the MP3 files,
+> but the files themselves live on disk.
+
+### Summary {.keypoints}
+
+* Use `create table *name*(...)` to create a table.
+* Use `drop table *name*` to erase a table.
+* Specify field names and types when creating tables.
+* Specify `primary key`, `foreign key`, `not null`, and other constraints when creating tables.
+* Use `insert into *table* values(...)` to add records to a table.
+* Use `delete from *table* where *test*` to erase records from a table.
+* Maintain referential integrity when creating or deleting information.
+
+### Challenges {.challenges}
+
+* Write an SQL statement to replace all uses of `null`
+ in `Survey.person`
+ with the string `'unknown'`.
+
+* One of Gina's colleagues has sent her a [CSV](../gloss.html#csv) file
+ containing temperature readings by Robert Olmstead,
+ which is formatted like this:
+
+ ```
+ Taken,Temp
+ 619,-21.5
+ 622,-15.5
+ ```
+
+ Write a small Python program that reads this file in
+ and prints out the SQL `insert` statements needed
+ to add these records to the survey database.
+ Note: you will need to add an entry for Olmstead
+ to the `Person` table.
+ If you are testing your program repeatedly,
+ you may want to investigate SQL's `insert or replace` command.
+
+* SQLite has several administrative commands that aren't part of the SQL standard.
+ One of them is `.dump`,
+ which prints the SQL commands needed to re-create the database.
+ Another is `.load`,
+ which reads a file created by `.dump` and restores the database.
+ A colleague of yours thinks that storing dump files (which are text) in version control
+ is a good way to track and manage changes to the database.
+ What are the pros and cons of this approach?
+
+<section id="s:transactions">
+
+## Transactions
+
+### Learning Objectives {.objectives}
+
+* Explain what a race condition is.
+* Explain why database operations sometimes have to be placed ina transaction to ensure correct behavior.
+* Explain what it means to commit a transaction.</li>
+
+Duration: 10 minutes.
+
+### Lesson
+
+Suppose we have another table in our database that shows
+which pieces of equipment have been borrowed by which scientists:
+
+``` {.sql}
+select * from Equipment;
+```
+
+<div class="db">
+
+-------------------- --------------------
+dyer CX-211 oscilloscope
+pb Greenworth balance
+lake Cavorite damping plates
+-------------------- --------------------
+
+</div>
+
+(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](../gloss.html#acid):
+[atomic](../gloss.html#atomic-operation) (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](../gloss.html#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](../gloss.html#commit) them at the end of the transaction.
+
+> ### Transactions and Commits {.box}
+>
+> We first used the term "transaction" in
+> [our discussion of version control](svn.html#b:basics:transaction).
+> That's not a coincidence:
+> behind the scenes,
+> tools like Subversion are using many of the same algorithms as database managers
+> to ensure that either everything happens consistently
+> or nothing happens at all.
+> We [use the term "commit"](svn.html#a:commit) for the same reason:
+> just as our changes to local files aren't written back to the version control repository
+> until we commit them,
+> our (apparent) changes to a database aren't written to disk
+> until we say so.
+
+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:
+
+``` {.sql}
+select * from Exposure;
+```
+
+<div class="db">
+
+-------------------- --------------------
+pb 4
+dyer 1
+lake 5
+-------------------- --------------------
+
+</div>
+
+After going through the journal entries for 1932,
+Gina wants to add two days to Lake's count:
+
+``` {.sql}
+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:
+
+``` {.sql}
+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:
+
+-------------------- --------------------
+`X = read Exposure('lake', __)` `Y = read Exposure('lake', __)`
+`write Exposure('lake', X+2)` `write Exposure('lake', Y+1)`
+-------------------- --------------------
+
+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:
+
+-------------------- --------------------
+`X = read Exposure('lake', __)` `X` is 5
+`write Exposure('lake', X+2)` database contains 7
+`Y = read Exposure('lake', __)` `Y` is 7
+`write Exposure('lake', Y+1)` database contains 8
+-------------------- --------------------
+
+then all is well.
+But what if it interleaves the operations like this:
+
+-------------------- --------------------
+`X = read Exposure('lake', __)` `X` is 5
+`Y = read Exposure('lake', __)` `Y` is 5
+`write Exposure('lake', X+2)` database contains 7
+`write Exposure('lake', Y+1)` database contains 6
+-------------------- --------------------
+
+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](../gloss.html#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.
+
+### Summary {.keypoints}
+
+* Place operations in a transaction to ensure that they appear to be atomic, consistent, isolated, and durable.
+
+### Challenges {.challenges}
+
+* A friend of yours manages a database of aerial photographs.
+ New records are added all the time,
+ but existing records are never modified or updated.
+ Your friend claims that because of this,
+ he doesn't need to put his queries in transactions.
+ Is he right or wrong, and why?
+
+## Programming With Databases {#s:programming}
+
+### Learning Objectives {.objectives}
+
+* Write a Python program that queries a database and processes the results.
+* Explain what an SQL injection attack is.
+* Write a program that safely interpolates values into queries.
+
+Duration: 20 minutes.
+
+### Lesson
+
+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`:
+
+``` {.python}
+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](../gloss.html#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.
+
+> ### What Are The u's For? {.box}
+>
+> You may have noticed that
+> each of the strings in our output has a lower-case 'u' in front of it.
+> That is Python's way of telling us that the string is stored in
+> [Unicode](../gloss.html#unicode).
+
+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:
+
+``` {.python}
+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:
+
+``` {.sql}
+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](../gloss.html#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](../gloss.html#prepared-statement)
+instead of formatting our statements as strings.
+Here's what our example program looks like if we do this:
+
+``` {.python}
+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.
+
+### Summary {.keypoints}
+
+* Most applications that use databases embed SQL in a general-purpose programming language.
+* Database libraries use connections and cursors to manage interactions.
+* Programs can fetch all results at once, or a few results at a time.
+* If queries are constructed dynamically using input from users, malicious users may be able to inject their own commands into the queries.
+* Dynamically-constructed queries can use SQL's native formatting to safeguard against such attacks.
+
+### Challenges {.challenges}
+
+* Write a Python program that creates a new database
+ in a file called `original.db`
+ containing a single table called `Pressure`,
+ with a single field called `reading`,
+ and inserts 100,000 random numbers between 10.0 and 25.0.
+ How long does it take this program to run?
+ How long does it take to run a program
+ that simply writes those random numbers to a file?
+
+* Write a Python program that creates a new database
+ called `backup.db`
+ with the same structure as `original.db`
+ and copies all the values greater than 20.0
+ from `original.db` to `backup.db`.
+ Which is faster:
+ filtering values in the query,
+ or reading everything into memory and filtering in Python?
+
+## Summing Up {#s:summary}
+
+There are many things databases can't do,
+or can't do well
+(which is why we have general-purpose programming languages like Python).
+However,
+they are still the best tool available
+for managing many kinds of complex, structured data.
+Thousands of programmer-years have gone into their design and implementation
+so that they can handle very large datasets—terabytes or more—quickly and reliably.
+Queries allow for great flexibility in how you are able to analyze your data,
+which makes databases a good choice when you are exploring data.
projects / swc-sql.git / commitdiff