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 ...