Skip to Main Content
Using SQLite
book

Using SQLite

by Jay A. Kreibich
August 2010
Intermediate to advanced content levelIntermediate to advanced
526 pages
23h 39m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL, 3rd Edition

Learning SQL, 3rd Edition

Alan Beaulieu
High Performance MySQL, 4th Edition

High Performance MySQL, 4th Edition

Silvia Botros, Jeremy Tinley

Publisher Resources

ISBN: 9781449394592Errata PageSupplemental Content