SQL, lesson 1
SQL is the language you use to talk to a database. A database stores data in tables: each
table has named columns (like title or price) and holds any number of rows, one per
record. In this course you use SQLite, a small database engine that runs your whole file as a
script, top to bottom.
The statement you will write most is SELECT. It asks for data and prints the answer.
Every statement ends with a semicolon. SQL keywords are not case-sensitive (select works too),
but writing them in capitals makes queries easier to read.
Name the columns you want after SELECT, and the table after FROM:
Use * to get every column. It is handy while exploring, but naming the columns you need is
clearer and keeps your output stable if someone adds a column later.
2 exercises to try what you just read.
SQLite prints one line per row. Values in a row are separated by a vertical bar |, and no
column names are printed. A missing value (NULL) shows as nothing at all.
A column in a SELECT can be any expression: arithmetic, text, or a function call. AS gives
the result a name, which matters later when you sort or reuse it.
Watch out for one SQLite rule: dividing two whole numbers gives a whole number. 7 / 2 is 3,
but 7.0 / 2 is 3.5. Text values go in single quotes: 'Dune'.
Two dashes start a comment that runs to the end of the line. SQLite ignores it.
In each exercise the tables are already created and filled for you. You only write the query. The prompt shows what the table holds so you know which columns exist.
The table books has the columns title, author, year and pages:
Write a query that prints only the title and author of every book, in the order they are
stored. Expected output:
The table menu lists drinks and their prices before tax:
Print each item with its price including 20% tax, rounded to 2 decimal places. Multiply by
1.2 and use round(value, 2). Expected output:
SELECT 'Hello, World!';
SELECT 6 * 7;SELECT title, author FROM books;SELECT * FROM books;Dune|Frank Herbert
Emma|Jane AustenSELECT title, pages / 30 AS days_to_read FROM books;
SELECT 'Book: ' || title FROM books; -- || joins two pieces of text
SELECT round(9.99 * 1.2, 2); -- 11.99-- List every book
SELECT title FROM books; -- a comment can follow code tootitle | author | year | pages
The Hobbit | J.R.R. Tolkien | 1937 | 310
Dune | Frank Herbert | 1965 | 412
Matilda | Roald Dahl | 1988 | 240
The Martian | Andy Weir | 2011 | 369The Hobbit|J.R.R. Tolkien
Dune|Frank Herbert
Matilda|Roald Dahl
The Martian|Andy Weiritem | price
Espresso | 2.5
Latte | 3.8
Hot chocolate| 3.2
Tea | 1.9Espresso|3.0
Latte|4.56
Hot chocolate|3.84
Tea|2.28