SELECT Examples by Jay A. Kreibich

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

O'Reilly logo

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required