Reference · module 1
SELECT
Everything this module explains, on one page.
SELECT and FROM
Ask a table for the columns you need.
- A database keeps data in tables: rows are records, columns are their fields. SELECT title FROM books; asks the table books for the title column of every row.
- List several columns with commas: SELECT title, year FROM books;. The result is a new table with exactly those columns, in the order you named them.
- SELECT * FROM books; returns every column. It is handy for a first look at a table, but real code names its columns, so a new column cannot silently change the result.
The mistake you are about to make
SELECT title year FROM books;
SELECT title, year FROM books;
A space between columns feels natural, like between words. Without the comma SQL reads year as a new name for title: you get one column labelled year.
Calculations and names
Calculate new columns, name them, and handle text values.
- A column can be calculated: SELECT name, price * 2 FROM products; returns each price doubled. The table itself does not change; only the result shows the new values.
- AS names a result column: SELECT price * 2 AS double_price FROM products;. Without AS the column is labelled with the expression itself, which is hard to read and hard to use.
- Text values go in single quotes: 'Oslo'. Double quotes mean a column or table name in standard SQL, and || joins text: SELECT first || ' ' || last FROM people;.
The mistake you are about to make
SELECT first + ' ' + last FROM people;
SELECT first || ' ' || last FROM people;
In many languages + joins text. In SQL + always adds numbers: SQLite tries to turn the names into numbers and returns 0 instead of a full name.
DISTINCT and LIMIT
Remove repeats, take only a few rows, and leave notes in a query.
- SELECT DISTINCT city FROM customers; returns each city once, however many customers live there. DISTINCT applies to the whole row of chosen columns, not to one column.
- LIMIT 5 at the end returns at most five rows: SELECT name FROM customers LIMIT 5;. Without ORDER BY, which five you get is up to the database.
- Two dashes start a comment: everything after -- on that line is ignored. Comments explain why a query is written this way; the query itself already says what it does.
The mistake you are about to make
SELECT DISTINCT city, name FROM customers;
SELECT DISTINCT city FROM customers;
DISTINCT sits next to city, so it seems to remove repeated cities only. The whole row must be unique: two customers from the same city bring the city back twice.