Using SQLite

SELECT Examples

The SELECT command is very complex, and it can be difficult to see how these different clauses can be fit together into something useful. Some of this will become more obvious in the next chapter, when we look at standard database design practices, but to get you started, we’re going to look at several examples.

All of these examples will use this data:

CREATE TABLE x ( a, b );
INSERT INTO x VALUES ( 1, 'Alice' );
INSERT INTO x VALUES ( 2, 'Bob' );
INSERT INTO x VALUES ( 3, 'Charlie' );

CREATE TABLE y ( c, d );
INSERT INTO y VALUES ( 1, 3.14159 );
INSERT INTO y VALUES ( 1, 2.71828 );
INSERT INTO y VALUES ( 2, 1.61803 );

CREATE TABLE z ( a, e );
INSERT INTO z VALUES ( 1, 100 );
INSERT INTO z VALUES ( 1, 150 );
INSERT INTO z VALUES ( 3, 300 );
INSERT INTO z VALUES ( 9, 900 );

These examples show the sqlite3 command-line tool. The following dot-commands were issued to make the output easier to understand. The last command will cause sqlite3 to print the string [NULL] whenever a NULL is encountered. Normally, a NULL will produce a blank output that is indistinguishable from an empty string:

.headers on
.mode column
.nullvalue [NULL]

This dataset is available on the book’s download page on the O’Reilly website, as both an SQL file and an SQLite database. I suggest you sit down with a copy of sqlite3 and try these commands out. Try experimenting with different variations.

If one of these examples doesn’t quite make sense, just break the SELECT statement down into its individual ...