Tables, rows, and output contracts
Select named columns while preserving the intended row grain.
Select named columns while preserving the intended row grain.
Audience and objective
You can read a small table and follow a programming example. No database server is needed. Select named columns while preserving the intended row grain.
Explanation
A relational table has named columns and rows. Decide what one output row represents before writing a query: one customer, one order, or one grouped summary. SELECT chooses columns; it does not remove repeated values unless DISTINCT is present. Give calculated or renamed fields explicit aliases so a downstream report can depend on their names.
The practice workspace builds synthetic tables before each test. Your query reads those tables; you do not create them in the editor. The complete example below includes setup so it can also run in a fresh SQLite session. Inspect the schema before assuming a column is present.
Runnable worked example
This complete SQLite example creates synthetic tables in a fresh in-memory database. In the practice editor, the tables are already supplied: submit only the final SELECT/WITH query.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT, email TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount_cents INTEGER, status TEXT, ordered_on TEXT);
CREATE TABLE returns (id INTEGER PRIMARY KEY, order_id INTEGER, amount_cents INTEGER);
INSERT INTO customers VALUES (1, 'Ada', 'Austin', 'a@example.test');
INSERT INTO customers VALUES (2, 'Bo', 'Dallas', NULL);
INSERT INTO customers VALUES (3, 'Cy', 'Austin', 'a@example.test');
INSERT INTO orders VALUES (1, 1, 1200, 'paid', '2026-01-02');
INSERT INTO orders VALUES (2, 1, 800, 'refunded', '2026-01-03');
INSERT INTO orders VALUES (3, 2, 1200, 'paid', '2026-02-01');
INSERT INTO orders VALUES (4, 99, 0, 'pending', '2026-02-02');
INSERT INTO returns VALUES (1, 1, 200);
INSERT INTO returns VALUES (2, 1, 100);
INSERT INTO returns VALUES (3, 2, 800);
SELECT name FROM customers ORDER BY id;
Expected columns and rows:
{"columns": ["name"], "values": [["Ada"], ["Bo"], ["Cy"]]}
Projection selects columns without deduplicating records.
Common mistakes
Do not assume storage order is a public ordering guarantee. SELECT * makes an output contract depend on every schema change.
Guided practice
Trace the worked query one stage at a time. State the source grain, any filtered rows, and the output grain. Predict the empty-table result before running it. Then change one source value at a boundary and explain exactly which output rows should change.
Independent practice
Recap
Select named columns while preserving the intended row grain. Keep aliases, missing-value behavior, duplicate handling and ordering part of the public contract. Use SQLite syntax here; test dialect-specific details before moving the query to another database.