SQL basics

SQL is the language you use to ask a relational database questions and to change what is in it. By the end of this page you can find, sort and count rows, and add, change and delete them without hitting the wrong ones.

Every example runs against the practice shop, shop.sqlite. The results shown are the real results from that file. To run them yourself, open the file in Database Manager and type them in the Query tab (the lab walks you through it).

The shop's tables

Lesson 1 showed customers. This lesson also uses products:

id	name	category	price_cents	stock
1	Spiral notebook A5	Paper	249	11
5	Gel pen, black	Pens	129	39
7	Fountain pen	Pens	2499	3
…	…	…	…	…

Prices are whole cents: 2499 is 24.99.

SELECT: read rows

SELECT picks columns, FROM names the table:

SELECT name, city FROM customers;

That returns the name and city of all 30 customers. SELECT * returns every column.

SQL keywords are not case-sensitive (select works too), but writing them in capitals makes queries easier to read. End each statement with a semicolon. Text values go in single quotes: 'Leeds'.

WHERE: only the rows you want

SELECT name, city FROM customers WHERE city = 'Leeds';

name	city
Ben Carter	Leeds
Hiro Okafor	Leeds
Noah Byrne	Leeds
Tom Raman	Leeds
Arjun Martin	Leeds

The comparisons you will use most:

Operator	Means	Example
=	Equals	city = 'Leeds'
<>	Does not equal	status <> 'cancelled'
< > <= >=	Less, more, at most, at least	stock < 10
LIKE	Matches a pattern; % is any text	name LIKE '%pen%'
IN	Is one of a list	id IN (1, 2, 3)
IS NULL	Has no value	email IS NULL
AND, OR, NOT	Combine conditions	category = 'Art' AND price_cents < 1000

Combining two conditions:

SELECT name, category, price_cents FROM products
WHERE category = 'Art' AND price_cents < 1000;

name	category	price_cents
Brush set	Art	849
Washi tape, 5 rolls	Art	549

LIKE can surprise you. name LIKE '%pen%' returns six products: the three pens ("Gel pen, black", "Gel pen set, 10 colours" and "Fountain pen"), and also "Mechanical pencil", "Pencil set, HB to 6B" and "Pencil sharpener", because pen is inside pencil. In SQLite, LIKE also ignores upper and lower case for English letters. Always look at what a pattern matched.

NULL is not equal to anything

email = NULL is never true, not even for an empty email. Write email IS NULL, or email IS NOT NULL.

ORDER BY and LIMIT: sort and cut

ORDER BY sorts, smallest first. Add DESC for largest first. LIMIT keeps only the first rows.

SELECT name, price_cents FROM products
ORDER BY price_cents DESC
LIMIT 3;

name	price_cents
Fountain pen	2499
Desk organiser	1899
Watercolour set	1599

The parts always come in this order: SELECT … FROM … WHERE … ORDER BY … LIMIT …. A low-stock report, smallest stock first:

SELECT name, stock FROM products WHERE stock < 10 ORDER BY stock;

name	stock
Eraser, pack of 3	0
Dot grid journal	3
Fountain pen	3
Stapler	6
Gel pen set, 10 colours	8

Without ORDER BY, rows come back in whatever order the database finds convenient. If the order matters, ask for it.

Learn from the app

In Database Manager, the find-rows panel on the Data tab builds a search like this from lists, and Show the query this runs shows the SQL behind it. See Browse and find rows.

INSERT: add a row

INSERT INTO products (name, category, price_cents, stock)
VALUES ('Index cards, pack of 100', 'Paper', 279, 40);

You list the columns, then the values in the same order. The id is left out: because it is an INTEGER PRIMARY KEY, SQLite gives the new row the next number, 21.

SELECT * FROM products WHERE id = 21;

id	name	category	price_cents	stock
21	Index cards, pack of 100	Paper	279	40

UPDATE: change rows

UPDATE products SET price_cents = 299 WHERE id = 21;

SET says what changes; WHERE says which rows. The database reports how many rows it changed: here, 1. You can set several columns at once: SET price_cents = 299, stock = 35.

DELETE: remove rows

DELETE FROM products WHERE id = 21;

That removes the one row, and the table is back to 20 products.

The most dangerous mistake in SQL

UPDATE and DELETE without a WHERE change every row in the table:

UPDATE products SET price_cents = 0;   -- every product is now free (20 rows)
DELETE FROM order_items;               -- every order is now empty (305 rows)

The database does not ask "are you sure?". Database Manager's Query tab runs the statement as soon as you select Run, and there is no undo. A missing WHERE, or a WHERE that matches more than you thought, is how most data disasters start.

The habit that prevents it: write the SELECT first.

1. Write the WHERE you mean as a SELECT, and run it:

   SELECT * FROM products WHERE category = 'Pens' AND stock < 10;

2. Check the rows that come back. Are they exactly the ones you want to change? Count them. Here there are 2: the fountain pen and the gel pen set.

3. Only then turn it into the change, keeping the WHERE word for word:

   UPDATE products SET stock = stock + 20 WHERE category = 'Pens' AND stock < 10;

4. Check that the reported number of changed rows matches your count.

Lesson 4 adds a safety net: a transaction you can roll back.

Counting and adding up

Aggregate functions turn many rows into one answer.

Function	Gives
COUNT(*)	The number of rows
SUM(x)	The total of x
AVG(x)	The average of x
MIN(x), MAX(x)	The smallest and largest x

SELECT COUNT(*) AS customers FROM customers;

customers
30

AS gives a result column a readable name.

You can do arithmetic inside an aggregate. Each row of order_items has a quantity and a unit_price_cents, so the total value of every item ever ordered is:

SELECT SUM(quantity * unit_price_cents) AS revenue_cents FROM order_items;

revenue_cents
474439

GROUP BY: one answer per group

GROUP BY splits the rows into groups and runs the aggregate once per group:

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY city;

city	customers
Bristol	5
Chennai	6
Glasgow	5
Kochi	5
Leeds	5
Pune	4

customers (30 rows)          GROUP BY city            COUNT(*)
Asha     Chennai  ─┐
Chloe    Chennai   ├──────►  Chennai: 6 rows   ─────►  6
Grace    Chennai  ─┘ …
Ben      Leeds    ─┐
Hiro     Leeds     ├──────►  Leeds: 5 rows     ─────►  5
…                 ─┘ …

Orders by status, most common first:

SELECT status, COUNT(*) AS orders FROM orders
GROUP BY status
ORDER BY orders DESC;

status	orders
delivered	74
shipped	18
placed	17
cancelled	11

WHERE filters rows before grouping. To filter the groups after, use HAVING:

SELECT city, COUNT(*) AS customers FROM customers
GROUP BY city
HAVING COUNT(*) > 5;

city	customers
Chennai	6

Stay safe

• Run SELECT with your WHERE before every UPDATE or DELETE, and compare the row counts.
• Add LIMIT when you look at a big table. The Query tab loads the whole result into one grid.
• Practise on a copy, such as the practice shop, never on a database people depend on.

Check yourself

QuizSQL basics

Question 1 of 6

Which query lists the three most expensive products, most expensive first?

Score: 0

What you learned

• SELECT … FROM … WHERE … ORDER BY … LIMIT … reads rows, in that order of parts.
• INSERT adds rows, UPDATE … SET … WHERE changes them, DELETE … WHERE removes them.
• Without WHERE, UPDATE and DELETE hit every row. Write the SELECT first.
• COUNT, SUM, AVG, MIN and MAX summarise rows; GROUP BY does it per group, and HAVING filters the groups.

Next steps

• Joins and keys: questions that need more than one table
• Run queries in Database Manager