Four jobs at the till
At Harbour Sports (a made-up shop) a new customer joins. Another has moved and asks for their town to be changed. A third wants to know what they bought. A fourth asks to be removed from the list.
Every job is one of four things you do with data: add it, look at it, change it, or remove it.
By the end of this lesson
Note
I can use SQL (a language for working with tables) to create, read, update and delete records.
Key words
| Word | What it means | Example |
|---|---|---|
| Query | an instruction or question you give to a table of data | |
| CRUD | create, read, update and delete: the four jobs | |
| Data type | the kind of value a field holds, such as text or a whole number | |
| Criterion | a test a record must pass | price > 10 |
| Logical operator | AND, OR or NOT, used to join criteria |
The cells students fill in are left blank in this preview.
Starter: what you already know
Have a go
Starter: what you already know
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Four jobs, four SQL words
Programmers call the four jobs CRUD: create, read, update, delete. Each job has its own SQL word. You have met them before. Now you use them for a purpose: to keep the data right and to answer questions.
Every one of them works on a table and, most times, a criterion that says which records.
CRUD in SQL
| Job | SQL word | Example |
|---|---|---|
| Create | INSERT | INSERT INTO products (product_name, category, price) VALUES ('Cap', 'Kit', 7); |
| Read | SELECT | SELECT product_name FROM products WHERE price > 10; |
| Update | UPDATE | UPDATE products SET stock = 25 WHERE product_id = 5; |
| Delete | DELETE | DELETE FROM sales WHERE sale_id = 12; |
The cells students fill in are left blank in this preview.
Reading uses criteria and the right type
Reading is the job you do most. A criterion must match the data type. Text goes in quotes: category = 'Kit'. A whole number has no quotes: price > 10.
Join criteria with a logical operator. AND needs both. OR needs one. NOT turns it round. Use brackets when you mix AND with OR, so the query means what you think.
Try it: read with criteria
-- Products that cost more than 10 pounds and are in stock.
SELECT product_name, price, stock FROM products
WHERE price > 10 AND stock > 0;
-- Accessories or Kit. The brackets keep the meaning clear.
SELECT product_name, category FROM products
WHERE (category = 'Accessories' OR category = 'Kit') AND price < 10;
-- Change 'Kit' to 'Shoes' and run it again.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.