Which products have run out?
You work Saturdays in a shop. The manager asks which products have run out.
The shop sells hundreds of things. Reading every row would take all morning.
A database answers the question in under a second, if you ask it in the right words.
By the end of this lesson
Note
I can find the records I need with a query.
Key words
| Word | What it means | Example |
|---|---|---|
| Query | a question you ask a database | |
| SELECT | names the fields you want back | |
| WHERE | keeps only the records that pass a test | |
| Condition | a test each record passes or fails | stock = 0 |
| ORDER BY | sorts the records you get back |
The cells students fill in are left blank in this preview.
SELECT picks fields, WHERE picks records
SELECT names the fields you want. FROM names the table. WHERE gives a condition, and only the records that pass it come back.
Text in a condition goes inside single quotes, like 'Ireland'. Numbers do not.
Sign in with the first two Runs, then ask the shop two questions.
Let the terminal trust the server
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.
Customers who live in Ireland
sql "SELECT first_name, last_name, city FROM customers WHERE country = 'Ireland' ORDER BY first_name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Products that have run out
sql "SELECT name, stock FROM products WHERE stock = 0 ORDER BY name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Compare numbers and join tests together
A condition can compare numbers: > is more than, < is less than, >= is at least.
AND joins two tests, and a record must pass both. OR joins two tests, and passing either one is enough.
Worked example: reading a condition
| Condition | Keeps a record when | Example that passes |
|---|---|---|
| price > 40 | the price is more than 40 | Chef Knife, 64.00 |
| category_id = 8 AND stock > 10 | it is a toy and there are more than 10 | Kite, 13 in stock |
| city = 'Cardiff' OR city = 'Glasgow' | it is in either city | Isla, Glasgow |
The cells students fill in are left blank in this preview.
Products over 40 pounds
sql "SELECT name, price FROM products WHERE price > 40 ORDER BY price DESC"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Toys with more than ten in stock
sql "SELECT name, price, stock FROM products WHERE category_id = 8 AND stock > 10 ORDER BY name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Customers in Cardiff or Glasgow
sql "SELECT first_name, city FROM customers WHERE city = 'Cardiff' OR city = 'Glasgow' 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.
A common mistake
Warning
Many people think the database knows that Ireland is a word. Without quotes it reads Ireland as the name of a field, and there is no such field. Put text inside single quotes every time.
Forget the quotes on purpose
sql "SELECT first_name FROM customers WHERE country = Ireland"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
ORDER BY sorts and LIMIT cuts
A table keeps no fixed order. Without ORDER BY, two runs of one query may list records differently.
LIMIT 3 keeps only the first three records after sorting. Together they answer questions such as "what are our three dearest products?"
The three dearest products
sql "SELECT name, price FROM products ORDER BY price DESC LIMIT 3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Going further
LIKE finds text that starts or ends a certain way. The % stands for any letters.
Write a query for every product whose name starts with Wooden. Sort it by name. How many come back?