Creating and Querying Tables

So far every SELECT computed values out of thin air. By the end of this lesson you can define a table, fill it with rows, and read columns back out of it -- a complete database session in three statements.

CREATE TABLE: defining the shape

A table is a named set of rows, and every row has the same columns. CREATE TABLE is your first piece of DDL (data definition): it declares the table's name and, in parentheses, each column as column_name TYPE, separated by commas.

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    pages  INTEGER
);

The four SQLite types:

Two constraints appear here and get a full lesson later. NOT NULL means the column must always hold a value. PRIMARY KEY means the column uniquely identifies a row; an INTEGER PRIMARY KEY in SQLite also fills itself in (1, 2, 3...) when you leave it out of an insert.

One SQLite honesty note: types are only suggestions unless you ask for STRICT tables, so 'abc' goes into an INTEGER column without complaint. Postgres would reject it. Declare types as if they were enforced.

INSERT: adding rows

INSERT INTO books (title, pages) VALUES
    ('Dune', 412),
    ('Emma', 474);
INSERT INTO books (title, pages) VALUES ('Ulysses', 730);

The list after the table name says which columns the values go into, in that order. Each parenthesized group in VALUES is one row; several groups separated by commas insert several rows at once. Text takes single quotes; numbers take none. id is not listed, so SQLite numbers the rows 1, 2, 3.

SELECT ... FROM: reading columns back

FROM names the table to read; the column list names what to print from each row.

SELECT pages, title FROM books;

412|Dune
474|Emma
730|Ulysses

Columns print in the order you list them, not the order the table declared them -- here pages comes first because it was asked for first. The runner joins the columns of a row with |. The rows come back in insert order for a simple table like this one; that is not guaranteed in general, and the next lessons add ORDER BY for when order matters.

A script runs top to bottom, one statement at a time. A SELECT placed above the INSERT reads an empty table and prints nothing.

Common mistakes

• Naming the table where a column belongs. SELECT books FROM books; fails with no such column: books -- the table is books, its columns are id, title and pages.
• Columns in the wrong order. SELECT pages, title prints 412|Dune; SELECT title, pages prints Dune|412. The grader compares every character, so swapped columns fail every line.
• A value count that does not match the column list. INSERT INTO books (title, pages) VALUES ('Dune'); is refused with 1 values for 2 columns. Each row group needs one value per listed column.
• Rows inserted out of order. With no ORDER BY, the output follows insert order, so inserting the rows in a different sequence prints them in a different sequence.

Your exercise

Write the whole session yourself for a different table: create it, insert the rows given in the exercise, and select two columns from it. The starter contains only a placeholder SELECT so it runs; replace it. The likeliest mistake is listing the selected columns in the wrong order.