How to start learning SQL on a small database of your own

Why a small database you already know

SQL is the language almost every database understands for asking questions. Learning it on a sample with a million anonymous rows is harder than it needs to be. On your own shop’s products, or a club’s members, you know what the right answer looks like, so you can tell at once when a query is wrong.

The shape of a question

SELECT name, price FROM products WHERE category = 'Bakery' ORDER BY price DESC;

Read it aloud and it says what it does: give me the name and price from products, only the bakery ones, dearest first. Text goes in single quotes. SELECT * means every field, which is handy for looking and bad for anything you keep.

The next three things to learn

• Totals: SELECT category, COUNT(*), SUM(stock) FROM products GROUP BY category; gives one row per category.
• Two tables: SELECT p.name, SUM(l.quantity) FROM sale_lines l JOIN products p ON p.id = l.product_id GROUP BY p.name; adds up what each product sold, by following the link from each line to its product.
• Names with spaces: standard SQL, SQLite and PostgreSQL put such names in double quotes, "Reorder Level". Access uses square brackets, and MySQL backticks.

Practising without breaking anything

• Work on a copy. Copy the database file, or export a sample, before you start.
• SELECT reads; the rest writes. UPDATE, DELETE and INSERT change records. A DELETE with no WHERE removes every row in the table.
• Free places to practise: DB Browser for SQLite opens a .db or .sqlite file and has an Execute SQL tab. Access has SQL View on any query. LibreOffice Base has Create Query in SQL View.
• Read the error. A message such as “no such column: prise” is the database telling you exactly what it did not understand.