SQL

Calculate across many rows

1. 17Seeing only the kinds of valueStrip the repeats out with DISTINCT and list what values a column actually holds.5 slides · 13 exercisesGo to the exercises
2. 18Calculating as you pull data outMultiply and subtract columns inside SELECT, and show the answer as a new column.5 slides · 13 exercisesGo to the exercises
3. 19Adding it all upMeet aggregation — folding many rows into one value — and total things up with SUM.5 slides · 13 exercisesGo to the exercises
4. 20Working out an averageGet an average with AVG, and watch out — rows with no value are not counted either.4 slides · 13 exercisesGo to the exercises
5. 21Counting the rowsCount with COUNT. COUNT(*) takes every row; name a column and the empty ones are skipped.5 slides · 13 exercisesGo to the exercises
6. 22The biggest and the smallestPull out the largest and smallest with MAX and MIN. They work on text and dates too.4 slides · 13 exercisesGo to the exercises
7. 23Aggregating group by groupGather rows with the same value using GROUP BY, and get a total or a count for each group at once.5 slides · 13 exercisesGo to the exercises
8. 24Gathering by two thingsLine up columns in GROUP BY and you get a group per combination.4 slides · 13 exercisesGo to the exercises
9. 25Narrow first, then gatherWHERE bites before GROUP BY. Only the rows that survive get made into groups.5 slides · 13 exercisesGo to the exercises
10. 26Sieving the groupsNarrow an aggregated result with HAVING. The point is knowing it from WHERE, which narrows rows.5 slides · 13 exercisesGo to the exercises
11. 27Gather and count: putting it togetherThe round-up for the chapter. No basics this time — ten practice questions combining aggregation and grouping.3 slides · 10 exercisesGo to the exercises

Combine several tables

1. 28Reading the orders tableLook over the orders table you will use all chapter, and go back over the fetching and aggregating you know.5 slides · 13 exercisesGo to the exercises
2. 29A query inside a queryPut another SELECT inside brackets and use its result straight away as a condition.5 slides · 13 exercisesGo to the exercises
3. 30Naming things so they readGive a column or a calculation a name with AS so the headings of your result say something.4 slides · 13 exercisesGo to the exercises
4. 31The items live in another tableThe names and prices are in the items table. Have a look at how the data is split across tables.4 slides · 13 exercisesGo to the exercises
5. 32Finding the key that joins themThe item_id in orders and the id in items point at the same thing. Go and find the key between the tables.5 slides · 13 exercisesGo to the exercises
6. 33Joining two tablesUse JOIN and ON to bring orders and items together as one result.5 slides · 13 exercisesGo to the exercises
7. 34Join, then narrow and countLay WHERE, ORDER BY and GROUP BY over a joined result and read how things are selling.4 slides · 13 exercisesGo to the exercises
8. 35Adding the members tableBring in the members table and check on a sketch how the three tables hang together.4 slides · 13 exercisesGo to the exercises
9. 36Joining while keeping one side wholeKeep every row of the left table with LEFT JOIN. Where there is no partner you get NULL.5 slides · 13 exercisesGo to the exercises
10. 37Joining three tablesWrite two JOINs in a row and bring orders, items and members into one result.4 slides · 13 exercisesGo to the exercises
11. 38Joining tables: putting it togetherThe round-up for the chapter. No basics this time — ten practice questions combining subqueries, aliases and joins.3 slides · 10 exercisesGo to the exercises

Turn a goal into a query

1. 39Getting to know the shopRead the three tables of a made-up online shop and pick up how to break what you want to know into a query.5 slides · 13 exercisesGo to the exercises
2. 40Counting members by attributeWork out numbers and average ages per area and per plan, and get a sense of who the members are.4 slides · 13 exercisesGo to the exercises
3. 41Gathering the items by categoryWork out how many items, what average price and how much stock each category has, and see the shape of the range.4 slides · 13 exercisesGo to the exercises
4. 42Comparing with conditions attachedChoose between narrowing before counting and narrowing after, and compare how much each category earns.4 slides · 13 exercisesGo to the exercises
5. 43Analysing items: putting it togetherThe round-up for the items. No basics this time — ten practice questions layering narrowing and grouping.3 slides · 10 exercisesGo to the exercises
6. 44Counting the ordersKeep orders and units apart, and gather the purchase history per member and per item.4 slides · 13 exercisesGo to the exercises
7. 45Working sales and profit outMake the money that is in no table at all, with a join and a multiplication, and aggregate it.4 slides · 13 exercisesGo to the exercises
8. 46Laying it out by month to see changePull the month out of a date, gather on it, and read how sales and orders moved month to month.4 slides · 13 exercisesGo to the exercises
9. 47Seeing members through joins and subqueriesTie members to the purchase history, and use subqueries to bring out who has not bought and who sits above the average.5 slides · 13 exercisesGo to the exercises
10. 48Analysis: the final roundThe round-up for the chapter. No basics this time — ten practice questions calling on joins, aggregating and narrowing all at once.3 slides · 10 exercisesGo to the exercises

Change the data

1. 49Adding a rowAdd a new row with INSERT INTO. After writing, checking with a SELECT is the basic move.5 slides · 13 exercisesGo to the exercises
2. 50Changing a valueFix the values in a row with UPDATE. Deciding which rows it bites on, with WHERE, matters most of all.5 slides · 13 exercisesGo to the exercises
3. 51Deleting a rowDelete rows with DELETE. See in an actual result what happens when you leave the WHERE out.5 slides · 13 exercisesGo to the exercises
4. 52Insert, update and delete: putting it togetherThe round-up for the chapter. No basics this time — ten practice questions combining writing with the reading you know.3 slides · 10 exercisesGo to the exercises