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

Create a table, insert the given rows, select the requested columns. Three details decide pass/fail: column order in your SELECT must match the expected output (select name, country when it wants name-then-country); insert rows in the order given (without ORDER BY — next lesson — SQLite returns rows in insert order for simple tables, and the grader's expected output was generated that way); and match the data exactly — typos in inserted text are faithfully stored and faithfully wrong. Schema, data, query: you now speak all three.