Exercices SQL corrigés : CASE WHEN

Notions à utiliser : CASE.

Structure de la requête :

SELECT …, CASE WHEN … >= … THEN … ELSE … END AS …
FROM …

SELECT name, CASE WHEN salary >= 40000 THEN 'high' ELSE 'low' END AS level
FROM employees;

Résultat attendu (8 lignes) :

Notions à utiliser : CASE, NULL, COALESCE.

Structure de la requête :

SELECT …, CASE WHEN … IS NULL THEN … ELSE … END AS …
FROM …

SELECT id, CASE WHEN paid_on IS NULL THEN 'unpaid' ELSE 'paid' END AS status
FROM invoices;

Résultat attendu (6 lignes) :

Notions à utiliser : CASE.

Structure de la requête :

SELECT …, CASE WHEN … < … THEN … WHEN … <= … THEN … ELSE … END AS …
FROM …

SELECT title, CASE WHEN duration < 100 THEN 'short' WHEN duration <= 120 THEN 'medium' ELSE 'long' END AS length
FROM movies;

Résultat attendu (10 lignes) :

Notions à utiliser : CASE.

Structure de la requête :

SELECT …, CASE WHEN … > … THEN … WHEN … < … THEN … ELSE … END AS …
FROM …

SELECT played_on, CASE WHEN home_goals > away_goals THEN 'home' WHEN home_goals < away_goals THEN 'away' ELSE 'draw' END AS result
FROM matches;

Résultat attendu (10 lignes) :

Notions à utiliser : CASE, Texte et dates.

Structure de la requête :

SELECT …, CASE WHEN STRFTIME(…, …) < … THEN … WHEN STRFTIME(…, …) < … THEN … ELSE … END AS …
FROM …

SELECT id, CASE WHEN strftime('%H:%M', departs) < '12:00' THEN 'morning' WHEN strftime('%H:%M', departs) < '18:00' THEN 'afternoon' ELSE 'evening' END AS slot
FROM flights;

Résultat attendu (12 lignes) :

Notions à utiliser : CASE.

Structure de la requête :

SELECT …, …, CASE WHEN … = … THEN … WHEN … >= … THEN … ELSE … END AS …
FROM …

SELECT name, city, CASE WHEN stars = 5 THEN 'luxury' WHEN stars >= 3 THEN 'comfort' ELSE 'budget' END AS category
FROM hotels;

Résultat attendu (5 lignes) :

Notions à utiliser : CASE.

Structure de la requête :

SELECT …, CASE WHEN … < … THEN … WHEN … < … THEN … ELSE … END AS …
FROM …

SELECT name, CASE WHEN price < 10 THEN 'small' WHEN price < 50 THEN 'medium' ELSE 'large' END AS size
FROM products;

Résultat attendu (7 lignes) :

Notions à utiliser : CASE.

Structure de la requête :

SELECT …, …, CASE WHEN … > … THEN … ELSE … END AS …
FROM …

SELECT id, amount, CASE WHEN amount > 0 THEN 'credit' ELSE 'debit' END AS direction
FROM transactions;

Résultat attendu (14 lignes) :

Notions à utiliser : COUNT, SUM, AVG, MIN, MAX, GROUP BY, CASE.

Structure de la requête :

SELECT …, SUM(… >= …) AS …, SUM(… < …) AS …
FROM …
GROUP BY …

SELECT department, SUM(salary >= 45000) AS high, SUM(salary < 45000) AS low
FROM staff
GROUP BY department;

Résultat attendu (3 lignes) :

Notions à utiliser : COUNT, SUM, AVG, MIN, MAX, GROUP BY, CASE.

Structure de la requête :

SELECT (… / …) * … AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …, SUM(CASE WHEN … <> … THEN … ELSE … END) AS …
FROM …
GROUP BY …

SELECT (year / 10) * 10 AS decade, SUM(CASE WHEN genre = 'Drama' THEN 1 ELSE 0 END) AS drama, SUM(CASE WHEN genre <> 'Drama' THEN 1 ELSE 0 END) AS other
FROM movies
GROUP BY decade;

Résultat attendu (2 lignes) :

Notions à utiliser : COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CASE.

Structure de la requête :

SELECT …, SUM(CASE WHEN … > … THEN … ELSE … END) AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …, SUM(CASE WHEN … < … THEN … ELSE … END) AS …
FROM … …
JOIN … … ON … = …
GROUP BY …

SELECT t.name, SUM(CASE WHEN m.home_goals > m.away_goals THEN 1 ELSE 0 END) AS wins, SUM(CASE WHEN m.home_goals = m.away_goals THEN 1 ELSE 0 END) AS draws, SUM(CASE WHEN m.home_goals < m.away_goals THEN 1 ELSE 0 END) AS losses
FROM teams t
JOIN matches m ON m.home_id = t.id
GROUP BY t.id;

Résultat attendu (5 lignes) :

Notions à utiliser : COUNT, SUM, AVG, MIN, MAX, GROUP BY, CASE, Texte et dates.

Structure de la requête :

SELECT DATE(…) AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …
FROM …
GROUP BY …

SELECT date(departs) AS day, SUM(CASE WHEN airline = 'SkyJet' THEN 1 ELSE 0 END) AS skyjet, SUM(CASE WHEN airline = 'AirNova' THEN 1 ELSE 0 END) AS airnova, SUM(CASE WHEN airline = 'BlueWing' THEN 1 ELSE 0 END) AS bluewing
FROM flights
GROUP BY day;

Résultat attendu (6 lignes) :

Notions à utiliser : COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CASE.

Structure de la requête :

SELECT …, SUM(CASE WHEN … = … THEN … ELSE … END), SUM(CASE WHEN … = … THEN … ELSE … END), SUM(CASE WHEN … = … THEN … ELSE … END)
FROM … …
LEFT JOIN … … ON … = …
GROUP BY …

SELECT h.name, SUM(CASE WHEN r.type = 'single' THEN 1 ELSE 0 END), SUM(CASE WHEN r.type = 'double' THEN 1 ELSE 0 END), SUM(CASE WHEN r.type = 'suite' THEN 1 ELSE 0 END)
FROM hotels h
LEFT JOIN rooms r ON r.hotel_id = h.id
GROUP BY h.id;

Résultat attendu (5 lignes) :

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CASE, Texte et dates.

Structure de la requête :

SELECT …, SUM(CASE WHEN … LIKE … THEN … * … ELSE … END), SUM(CASE WHEN … LIKE … THEN … * … ELSE … END), SUM(CASE WHEN … LIKE … THEN … * … ELSE … END)
FROM … …
JOIN … … ON … = …
JOIN … … ON … = …
JOIN … … ON … = …
WHERE … <> …
GROUP BY …

SELECT c.name, SUM(CASE WHEN o.order_date LIKE '2025-01%' THEN oi.qty * p.price ELSE 0 END), SUM(CASE WHEN o.order_date LIKE '2025-02%' THEN oi.qty * p.price ELSE 0 END), SUM(CASE WHEN o.order_date LIKE '2025-03%' THEN oi.qty * p.price ELSE 0 END)
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY c.id;

Résultat attendu (5 lignes) :

Notions à utiliser : COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CASE, NULL, COALESCE.

Structure de la requête :

SELECT …, …, COALESCE(SUM(CASE WHEN … > … THEN … END), …), COALESCE(-SUM(CASE WHEN … < … THEN … END), …), COALESCE(SUM(…), …)
FROM … …
LEFT JOIN … … ON … = …
GROUP BY …

SELECT a.id, a.owner, COALESCE(SUM(CASE WHEN t.amount > 0 THEN t.amount END), 0), COALESCE(-SUM(CASE WHEN t.amount < 0 THEN t.amount END), 0), COALESCE(SUM(t.amount), 0)
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.id
GROUP BY a.id;

Résultat attendu (6 lignes) :