Loading the lesson
Loading the lesson
AI & ML Engineering · Programming Foundations · Lesson 8 of 10
Select, filter, join and aggregate. Most real data lives in a table.
Read the short notes and pictures.Do everything inside a blue frame.
A database keeps data in tables. Every row is one thing, here one order. Every column is one fact about each of them.
A query describes the rows and columns you want. The database finds them. Pick a query and watch which parts of the table it keeps.
SELECT *
FROM ordersorders
| id | customer_id | city | amount |
|---|---|---|---|
| 1 | 1 | Pune | 1200 |
| 2 | 2 | Delhi | 450 |
| 3 | 1 | Pune | 300 |
| 4 | 3 | Kochi | 900 |
| 5 | 2 | Delhi | 750 |
| 6 | 3 | Kochi | 150 |
Result
| id | customer_id | city | amount |
|---|---|---|---|
| 1 | 1 | Pune | 1200 |
| 2 | 2 | Delhi | 450 |
| 3 | 1 | Pune | 300 |
| 4 | 3 | Kochi | 900 |
| 5 | 2 | Delhi | 750 |
| 6 | 3 | Kochi | 150 |
Which part of a query decides which rows come back?
SELECT id, city, amount
FROM orders
ORDER BY amount DESCorders
| id | customer_id | city | amount |
|---|---|---|---|
| 1 | 1 | Pune | 1200 |
| 2 | 2 | Delhi | 450 |
| 3 | 1 | Pune | 300 |
| 4 | 3 | Kochi | 900 |
| 5 | 2 | Delhi | 750 |
| 6 | 3 | Kochi | 150 |
Result
| id | city | amount |
|---|---|---|
| 1 | Pune | 1200 |
| 4 | Kochi | 900 |
| 5 | Delhi | 750 |
| 2 | Delhi | 450 |
| 3 | Pune | 300 |
| 6 | Kochi | 150 |
The orders table stores a customer_id, not a name. A join matches each order to its customer.
SELECT orders.id, customers.name, orders.amount
FROM orders
JOIN customers ON orders.customer_id = customers.idorders
| id | customer_id | city | amount |
|---|---|---|---|
| 1 | 1 | Pune | 1200 |
| 2 | 2 | Delhi | 450 |
| 3 | 1 | Pune | 300 |
| 4 | 3 | Kochi | 900 |
| 5 | 2 | Delhi | 750 |
| 6 | 3 | Kochi | 150 |
customers
| id | name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
| 3 | Meera |
Result
| id | name | amount |
|---|---|---|
| 1 | Asha | 1200 |
| 2 | Ravi | 450 |
| 3 | Asha | 300 |
| 4 | Meera | 900 |
| 5 | Ravi | 750 |
| 6 | Meera | 150 |
Total sales per city. Which clause do you need?
Now type the queries yourself. Each exercise checks your result against the right one, so any query that gives the right rows counts.
Your tables
orders
| id | customer_id | city | amount |
|---|---|---|---|
| 1 | 1 | Pune | 1200 |
| 2 | 2 | Delhi | 450 |
| 3 | 1 | Pune | 300 |
| 4 | 3 | Kochi | 900 |
| 5 | 2 | Delhi | 750 |
| 6 | 3 | Kochi | 150 |
customers
| id | name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
| 3 | Meera |
Show the city and amount of every order.
Run a query to see its result here.
Write a query for the id and amount of every order from Delhi, largest amount first.
Hint: You need SELECT, FROM, WHERE and ORDER BY, in that order.
Some orders have no discount recorded. Which finds them?
WHERE filters rows before they are grouped. HAVING filters the groups after. Use HAVING when the condition is about a total or a count.
WHERE: rows first
SELECT city, SUM(amount) FROM orders WHERE amount > 100 GROUP BY city;
HAVING: groups after
SELECT city, SUM(amount) FROM orders GROUP BY city HAVING SUM(amount) > 500;
Keep these