AI Hints
Homeโ€บSQL Tutorialโ€บPractice Gym: Groups and Joins
SQL ยท Lesson 118 of 126

Practice Gym: Groups and Joins

The middle of the course.

โฑ 2 min readโ–ถ Video at 2:48:01๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Round two of the gym. Grouping and joins. These are the skills you'll use most at work, so they're worth extra practice. Same rules. Pause, write, run, then compare.

Practice Gym: Groups and Joins
From the video: Practice Gym: Groups and Joins
  • Grouping and joins
  • Used most at work
  • Pause, write, run

Pricey genres

Which genres have an average price above twenty dollars? Show the number of books and the average price. Pause and try. Here's a solution. Group by genre, then having on the average. Four genres, led by technology.

โœ๏ธ Exercise: Pricey genres

Genres with an average price above $20

Show hint

GROUP BY genre HAVING AVG(price) > 20

Show solution
solution.sqlsql
SELECT genre,
       COUNT(*)             AS books,
       ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY genre
HAVING AVG(price) > 20
ORDER BY avg_price DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   genre    โ”‚ books โ”‚ avg_price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Technology โ”‚ 3     โ”‚ 32.32     โ”‚
โ”‚ Cooking    โ”‚ 3     โ”‚ 24.82     โ”‚
โ”‚ Business   โ”‚ 3     โ”‚ 21.99     โ”‚
โ”‚ History    โ”‚ 3     โ”‚ 20.66     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Canadian sign-ups

How many Canadian customers joined in each year? Pause and try. Here's a solution. Where for Canada, then group by the year from strftime. Only fourteen joined in twenty twenty five, so far.

โœ๏ธ Exercise: Canadian sign-ups

New Canadian customers per year

Show hint

WHERE country = 'Canada' GROUP BY strftime('%Y', joined)

Show solution
solution.sqlsql
SELECT strftime('%Y', joined) AS year,
       COUNT(*) AS new_customers
FROM customers
WHERE country = 'Canada'
GROUP BY year;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ year โ”‚ new_customers โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2023 โ”‚ 31            โ”‚
โ”‚ 2024 โ”‚ 32            โ”‚
โ”‚ 2025 โ”‚ 14            โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Quick check

Quick check. You want genres with more than three books, but only counting books over ten dollars. Where does the price test go? Pause and choose. The answer is A. The price test is about single rows, so it goes in where. The count test goes in having.

Quick check
Count only books over $10, keep genres with > 3

Summary

Group by and having, with where in front. Next, joins in the gym.

Key takeaways
  • GROUP BY + HAVING
  • WHERE first
  • strftime for years
Next up: Gym: Join Workout โ€” three join challenges