Read a table and choose columns

The idea

A table stores rows with named columns. In a books table, each row can describe one book, with columns such as id and title. A column is a kind of information; a row is one stored record. A query is a request for a result table, not necessarily a request to change stored data. This course uses SQLite SQL.

SELECT title FROM books; asks for the title column of the books table. SELECT chooses columns or expressions, and FROM names the source. In the worked example, CREATE TABLE and INSERT provide a small practice fixture so you can run the entire example in a fresh SQLite database. Focus first on the final SELECT statement; later chapters explain schema constraints and data changes.

The output columns appear in the order written after SELECT. Row order is a separate rule: ORDER BY id makes this example’s row order predictable. A simple SELECT does not delete or edit the original rows. Build confidence by selecting one column, then two, and comparing the result with the source.

CREATE TABLE cq_intro_books (id INTEGER, title TEXT);
INSERT INTO cq_intro_books VALUES (2, 'Orbits'), (1, 'Gardens');
SELECT title, id FROM cq_intro_books ORDER BY id;

title   | id
Gardens | 1
Orbits  | 2

SELECT puts title before id in the result. ORDER BY sorts rows by id, so Gardens appears first even though it was inserted second.