A second round of LeetCode SQL problems! On the menu: joins, NULL, grouping, subqueries and a first look at IF and CASE.
1. Employee Bonus
Tables:
Employee:empId(primary key),name,supervisor,salaryBonus:empId(foreign key),bonus
Task: show the name and bonus of employees whose bonus is below 1000, including those with no bonus at all.
First idea: a LEFT JOIN to keep every employee, then a filter on the bonus.
SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b ON b.empId = e.empId
WHERE b.bonus < 1000;
That doesn’t work: we only get Dan (500). Brad and John, who have no bonus, are gone. Why? Their bonus is NULL, and NULL < 1000 is neither true nor false: the condition drops them.
The fix: include the NULLs explicitly with IS NULL.
SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b ON b.empId = e.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;
To test for
NULL, always writeIS NULLorIS NOT NULL, never= NULL.
2. Classes More Than 5 Students
Table: Courses: student, class
Task: show the classes that have at least 5 students.
Reasoning: group by class and count the rows in each group.
SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*) >= 5;
3. Not Boring Movies
Table: Cinema: id (primary key), movie, description, rating
Task: show the movies with an odd id whose description is not “boring”, sorted by rating, highest first.
SELECT *
FROM Cinema
WHERE description NOT LIKE 'boring'
AND id % 2 <> 0
ORDER BY rating DESC;
NOT LIKEcompares strings.description <> 'boring'works too.id % 2is the modulo: the remainder of the division by 2. If it’s not 0, theidis odd.
4. Customer Placing the Largest Number of Orders
Table: Orders: order_number (primary key), customer_number
Task: show the customer who placed the most orders. The data guarantees there’s only one.
Reasoning: group by customer, sort by number of orders, highest first, and keep the first.
SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;
Going further: what about ties?
LeetCode asks a bonus question: what if several customers shared the maximum number of orders? LIMIT 1 would keep only one.
The solution: keep every customer whose number of orders equals the maximum, computed by a subquery.
SELECT customer_number
FROM Orders
GROUP BY customer_number
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1
);
The subquery returns the maximum number of orders per customer, and HAVING keeps every customer who reaches it.
5. Swap Salary
Table: Salary: id (primary key), name, sex, salary. The sex column only holds 'm' or 'f'.
Task: swap every value ('f' becomes 'm' and vice versa) with a single UPDATE query, without a temporary table.
With IF: IF(condition, value_if_true, value_if_false).
UPDATE Salary
SET sex = IF(sex = 'f', 'm', 'f');
With CASE, more standard and available in every RDBMS:
UPDATE Salary
SET sex = CASE
WHEN sex = 'm' THEN 'f'
ELSE 'm'
END;
IF and CASE weren’t part of the course, but they’re very handy. I encourage you to explore other MySQL functions: many of them will make your life easier in specific cases.
Exercise · Bonus: rank the salaries
Using the Employee table from exercise 1, show each employee’s name and a level column that’s 'high' if their salary is above 3000, 'medium' if it’s between 1000 and 3000, and 'low' otherwise.
Show the solution
SELECT name,
CASE
WHEN salary > 3000 THEN 'high'
WHEN salary >= 1000 THEN 'medium'
ELSE 'low'
END AS level
FROM Employee;
MySQL checks the WHENs in order and stops at the first true one: no need to write salary <= 3000 in the second.
End of the course
Well done, you’ve finished SQL from Zero to Pro! You can now create databases and tables, link them, manipulate and query data, and write subqueries and joins. To keep improving, carry on with LeetCode’s Easy then Medium problems.
Found another solution to one of these problems? Share it in the comments under the video.
This is Chapter 9 of the SQL from Zero to Pro course. Follow along to learn SQL with MySQL, from the basics to joins.