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

Gym: Nulls, Maths, and CASE

Three more warm-ups.

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

Three more. These use nulls, calculations, and case. Remember, pause first.

Gym: Nulls, Maths, and CASE
From the video: Gym: Nulls, Maths, and CASE
  • NULLs
  • Calculations
  • CASE

Missing details

How many customers have no email, and no city either? Pause and try. Here's a solution. Is null, twice, joined with and. Three customers. Remember, never write equals null.

โœ๏ธ Exercise: Missing details

Customers with no email AND no city

Show hint

email IS NULL AND city IS NULL

Show solution
solution.sqlsql
SELECT COUNT(*) AS no_email_no_city
FROM customers
WHERE email IS NULL
  AND city IS NULL;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ no_email_no_city โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 3                โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Most valuable shelves

Which three books have the most money sitting on the shelf? That's stock times price. Pause and try. Here's a solution. Multiply in the select, give it an alias, and sort by the alias. SQL in a Weekend has almost thirteen hundred dollars of stock.

โœ๏ธ Exercise: Most valuable shelves

Top 3 books by stock * price

Show hint

ROUND(stock * price, 2) AS shelf_value, ORDER BY it

Show solution
solution.sqlsql
SELECT title,
       ROUND(stock * price, 2) AS shelf_value
FROM books
ORDER BY shelf_value DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚ shelf_value โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ SQL in a Weekend         โ”‚ 1289.57     โ”‚
โ”‚ A Short History of Bread โ”‚ 1059.47     โ”‚
โ”‚ Numbers Don't Lie        โ”‚ 1033.53     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Salary bands

Last one in this round. Label each staff member senior if they earn fifty thousand or more, otherwise team. Highest salary first. Pause and try. Here's a solution. A case with one when, and an else. Helen, Priya, and Marcus are senior.

โœ๏ธ Exercise: Salary bands

Label staff 'Senior' (>= 50000) or 'Team'

Show hint

CASE WHEN salary >= 50000 THEN 'Senior' ELSE 'Team' END

Show solution
solution.sqlsql
SELECT name,
       salary,
       CASE WHEN salary >= 50000 THEN 'Senior'
            ELSE 'Team' END AS band
FROM staff
ORDER BY salary DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    name    โ”‚ salary โ”‚  band  โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Helen Ward โ”‚ 72000  โ”‚ Senior โ”‚
โ”‚ Priya Nair โ”‚ 56000  โ”‚ Senior โ”‚
โ”‚ Marcus Lee โ”‚ 54000  โ”‚ Senior โ”‚
โ”‚ Amy Chen   โ”‚ 33000  โ”‚ Team   โ”‚
โ”‚ Sara Odum  โ”‚ 32000  โ”‚ Team   โ”‚
โ”‚ Tom Baker  โ”‚ 31000  โ”‚ Team   โ”‚
โ”‚ Noah Price โ”‚ 30500  โ”‚ Team   โ”‚
โ”‚ Leo Grant  โ”‚ 29000  โ”‚ Team   โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Quick check

Quick check. You want books that cost between ten and twenty dollars, including both ends. Which condition works? Pause and choose. The answer is B. Between includes both ends.

Quick check
Price from $10 to $20, inclusive

Summary

Nulls, maths, and case are all second nature now. Next, a quick date challenge, then on to groups and joins.

Key takeaways
  • IS NULL
  • Maths with an alias
  • CASE labels
Next up: Gym: Dates โ€” one date challenge