Creating and Querying Tables

Time to make the data itself. CREATE TABLE is your first DDL — you're defining the shape that every future row must fit — and the decisions you make in it (names, types, what's required) are the cheapest bug-prevention you'll ever buy.

CREATE TABLE

CREATE TABLE cities (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    country    TEXT NOT NULL,
    population INTEGER
);

Anatomy: table name, then a parenthesized list of column_name TYPE constraints. The types you need in SQLite:

• INTEGER — whole numbers
• REAL — floating point
• TEXT — strings
• BLOB — raw bytes (rare in this course)

Two constraints preview here (chapter 4 goes deep): PRIMARY KEY — this column uniquely identifies each row; NOT NULL — this column must always hold a value. Even before you know the theory, the instinct is right: make the database refuse bad data, don't rely on every future program remembering the rules.

One honest SQLite footnote: unlike stricter databases, SQLite historically treats types as suggestions (you can insert 'abc' into an INTEGER column and it will shrug). Postgres would reject it. Write your schemas as if types are enforced — they are everywhere else, and modern SQLite offers STRICT tables that enforce them too.

INSERT

INSERT INTO cities (name, country, population) VALUES
    ('Tokyo',  'JP', 13960000),
    ('Osaka',  'JP', 2691000),
    ('Berlin', 'DE', 3645000);

Name the columns explicitly (the (name, country, …) list) — inserts that rely on column order break silently when the table gains a column. Note id is omitted: an INTEGER PRIMARY KEY in SQLite auto-assigns itself (1, 2, 3…). Multiple rows go in one statement with comma-separated VALUES groups.

And back out again

SELECT name, population
FROM cities;

FROM is the clause that was missing in the first two lessons — which table to read. The full pipeline you've now assembled: CREATE the shape, INSERT the rows, SELECT them back out. That's a complete, working database session, and it's exactly what your exercise asks for.

Your exercise: List Cities

The schema and the rows are already written for you in the editor; your job is the last line. Two details decide pass/fail: select the column, not the table — SELECT cities FROM cities is the most common failure here, because cities names the table and not a column; and leave the given INSERT order alone, because with no ORDER BY (next lesson) SQLite hands back the rows in insert order for a simple table like this one, and the expected output was produced that way. When you do select more than one column, the runner joins them with | on each line — worth knowing before the later exercises ask for exactly that. Schema, data, query: you now speak all three.