AI Hints
Homeโ€บSQL Tutorialโ€บPractice Gym: The Basics
SQL ยท Lesson 115 of 126

Practice Gym: The Basics

Quick warm-up challenges.

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

Welcome to the practice gym. Watching someone write SQL is useful, but writing it yourself is what makes it stick. Here come quick challenges, starting easy. For each one, pause the video, write your answer, and run it. Then compare with mine. It's fine if yours looks a little different. If the result is the same, you got it right.

Practice Gym: The Basics
From the video: Practice Gym: The Basics
  • Writing it yourself makes it stick
  • Pause, write, run
  • Same result = correct

Newer books

Warm up. How many of Helen's books were published in twenty twenty three or later? Pause and try. Here's a solution. Count star, with a where on the published year. Ten books.

โœ๏ธ Exercise: Newer books

How many books were published in 2023 or later?

Show hint

COUNT(*) with WHERE published >= 2023

Show solution
solution.sqlsql
SELECT COUNT(*) AS newer_books
FROM books
WHERE published >= 2023;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ newer_books โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 10          โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

First German customers

Next. Show the first three customers from Germany to join, oldest first. Show their names and join date. Pause and try. Here's a solution. Filter on the country, sort by joined, and limit three. Zane Okafor was the first.

โœ๏ธ Exercise: First German customers

First 3 customers from Germany to join (names, joined)

Show hint

WHERE country = 'Germany' ORDER BY joined LIMIT 3

Show solution
solution.sqlsql
SELECT first_name, last_name, joined
FROM customers
WHERE country = 'Germany'
ORDER BY joined
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚ last_name โ”‚   joined   โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Zane       โ”‚ Okafor    โ”‚ 2023-03-23 โ”‚
โ”‚ Hana       โ”‚ Novak     โ”‚ 2023-04-07 โ”‚
โ”‚ Nina       โ”‚ Lopez     โ”‚ 2023-04-10 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Cheap titles with The

Now find books with the word the in their title, that cost less than fifteen dollars, cheapest first. Pause and try. Here's a solution. Like with percent signs on both sides, and a price test. Five books, starting with The Painted Door.

โœ๏ธ Exercise: Cheap titles with The

Books with 'The' in the title, under $15, cheapest first

Show hint

title LIKE '%The%' AND price < 15

Show solution
solution.sqlsql
SELECT title, price
FROM books
WHERE title LIKE '%The%'
  AND price < 15
ORDER BY price;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title        โ”‚ price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Painted Door    โ”‚ 12.49 โ”‚
โ”‚ Code of the River   โ”‚ 12.99 โ”‚
โ”‚ The Last Lighthouse โ”‚ 13.99 โ”‚
โ”‚ The Calm Mind       โ”‚ 14.49 โ”‚
โ”‚ The Silent Orchard  โ”‚ 14.99 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Common beginner slips

Before the next round, here are the slips I see most often in these basic queries. Forgetting the quotes around text. Using double equals instead of one. Putting order by before where. And forgetting that like is case sensitive in some databases. If your query fails, check these four first.

Quotes'Germany', not Germany
One =not ==
OrderWHERE before ORDER BY
LIKEcase can matter

Summary

Where, order by, limit, and like. The basics are solid. More challenges next.

Key takeaways
  • WHERE and AND
  • ORDER BY, LIMIT
  • LIKE '%word%'
Next up: Gym: Nulls, Maths, and CASE โ€” three more warm-ups