Summarizing and grouping · Lesson 7 of 20

CASE, COALESCE and text functions

An if inside a query, a fallback for NULL, and shaping text and numbers.

9 minutes2 games

CASE is SQL's if: it tests conditions in order and returns the value of the first true one, or the ELSE value if none match.

SQL

SELECT name,
       xp,
       CASE
         WHEN xp >= 300 THEN 'gold'
         WHEN xp >= 150 THEN 'silver'
         WHEN xp IS NULL THEN 'new'
         ELSE 'bronze'
       END AS badge
FROM students;

COALESCE: a fallback for NULL

COALESCE(a, b, c) returns the first value that isn't NULL. Handy for display and maths: COALESCE(xp, 0) makes Youssef zero instead of NULL.

SQL

SELECT UPPER(name) AS name,
       LENGTH(name) AS letters,
       name || ' from ' || city AS intro,
       COALESCE(xp, 0) AS xp
FROM students
WHERE city = 'Alexandria';

Function	What it does
UPPER(s) / LOWER(s)	upper / lower case
LENGTH(s)	number of characters
a \|\| b	join text (MySQL: `CONCAT`)
SUBSTR(s, 1, 3)	part of the text
ROUND(x, 2)	round to 2 decimals
COALESCE(a, b)	first non-NULL value

What's the output?

SELECT name,
       CASE WHEN xp >= 300 THEN 'gold' ELSE 'other' END AS level
FROM students
WHERE id IN (1, 5);

What will this code print?

Exercise

For courses in the web track: show title and a column kind that is free when the price is zero and paid otherwise.

Expected result:

title | kind
HTML & CSS | free
JavaScript | paid
React | paid

SQL

SELECT title
FROM courses
WHERE track = 'web';

Learn more from

Played 0 of 2 games

SQL