Which laptops are out of warranty?
The support desk of a firm is asked a simple question. Which laptops were bought before 2023, and who has them?
In a spreadsheet you would scroll and squint. A database answers in a moment.
You only have to ask the question in a way it understands.
By the end of this lesson
Note
I can ask a database for the rows I need.
Key words
| Word | What it means | Example |
|---|---|---|
| Query | a question you ask a database in SQL | |
| Clause | one part of a query, started by a keyword | WHERE |
| Condition | a test that a row passes or fails | price < 30 |
| Result | the rows that come back from a query | |
| Wildcard | a symbol in a pattern that stands for any text | % |
| NULL | the mark for a missing value |
The cells students fill in are left blank in this preview.
WHERE keeps the rows that pass
A query starts with SELECT, which names the columns. FROM names the table.
WHERE adds a condition. A row stays in the result only if it passes. Join conditions with AND and OR.
Text goes in single quotes. Numbers do not.
Run the two setup lines first. You did this in the last lesson, and it does no harm to repeat it.
Accept the server's certificate
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the shop
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Match a piece of text
sql "SELECT first_name, city FROM customers WHERE country = 'France' ORDER BY city, first_name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Two conditions together
sql "SELECT name, price FROM products WHERE category_id = 4 AND price < 30 ORDER BY price"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: the tests you can use
| Test | Means | Example |
|---|---|---|
| = <> | Equal, not equal | country = 'Spain' |
| < <= > >= | Smaller or larger | price < 30 |
| BETWEEN | Inside a range, ends included | stock BETWEEN 5 AND 10 |
| LIKE | Matches a pattern, % is any text | name LIKE '%Set%' |
| IS NULL | The value is missing | phone IS NULL |
The cells students fill in are left blank in this preview.
Find names with a pattern
sql "SELECT name FROM products WHERE name LIKE '%Set%' ORDER BY name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Stock from 5 to 10
sql "SELECT name, stock FROM products WHERE stock BETWEEN 5 AND 10 ORDER BY stock, name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
NULL is missing, not zero
Some customers have no phone number. The column holds NULL: a mark that says nothing is known.
NULL is not equal to anything, not even to NULL. So = NULL never matches a row.
Ask with IS NULL instead. Run both and compare.
Count customers with no phone
sql "SELECT COUNT(*) AS no_phone FROM customers WHERE phone IS NULL"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The same question, written wrongly
sql "SELECT COUNT(*) AS no_phone FROM customers WHERE phone = NULL"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think phone = NULL finds the missing phones. It finds none, and it does not warn you. A missing value is not equal to anything. Always write IS NULL or IS NOT NULL.
ORDER BY and LIMIT shape the result
A table has no fixed order. If the order matters, say so with ORDER BY. DESC puts the largest first.
LIMIT 3 keeps the first three rows of the result. OFFSET 2 skips two rows first.
Use LIMIT with ORDER BY. Without an order, "the first three" means nothing.
The three cheapest products
sql "SELECT name, price FROM products ORDER BY price LIMIT 3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Skip two, then take two
sql "SELECT name, price FROM products ORDER BY price DESC LIMIT 2 OFFSET 2"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: how a query is read
| Step | Clause | What the database does |
|---|---|---|
| 1 | FROM | Picks the table |
| 2 | WHERE | Throws away rows that fail the test |
| 3 | SELECT | Keeps only the columns you named |
| 4 | ORDER BY | Puts the rows in order |
| 5 | LIMIT | Cuts the list to the size you asked |
The cells students fill in are left blank in this preview.
Try it: laptops by department
-- Laptops in Legal or Sales.
SELECT tag, model, dept FROM laptops
WHERE dept = 'Legal' OR dept = 'Sales';
-- Change it to find laptops bought in 2024 or later.
Try it: the dearest laptops
-- The three dearest laptops, dearest first.
SELECT tag, price FROM laptops
ORDER BY price DESC
LIMIT 3;
-- Change DESC to ASC and run it again.
Going further
A shop's website lists products five to a page, cheapest first. Write the query for page three with LIMIT and OFFSET.
Two products in the shop cost 19.99. Explain why such a pair could swap places between two runs of the query. Then add a second column to the ORDER BY so the order is always the same.