10 NULL Exercises for Beginners

Looking to practice handing NULL values in your queries? Check out these 10 new NULL practice exercises!

Mastering SQL requires a lot of practice. While learning theory is important, hands-on practice is one of the best ways to keep your SQL skills up to date. In this article, we present 10 NULL exercises for you to analyze, complete with solutions and explanations.

Dealing with NULLs can catch SQL beginners off guard. NULL does not behave like regular data; it represents the total absence of information, which can make it hard to understand at first.

The following SQL exercises will help you sharpen your skills on the topic of NULLs. We will cover all the intricacies of working with NULLs and practice a wide range of use cases involving missing data.

And if you would like some more practice after this – not just with NULLs, but with the entirety of SQL – try our SQL Practice Track. It has 1250+ practice exercises to help you master SQL. And because everything happens in your browser, there’s nothing to set up or download.

Ready for some SQL NULL practice exercises? Let’s dive right in!

Get to Know the Data

For this exercise set, we will use two tables: employee and department. Take a look at some sample data from each table:

employee

In the employee table, some columns can have missing (NULL) values. For example, manager_id is NULL for all employees who do not have another employee overseeing them (i.e. the manager themselves). And salary and bonus information can be missing in case they are not applicable to that specific employee.

department

In the department table, only the region can be NULL. This indicates that the department operates online. We will come back to this in the exercises.

SQL NULL Exercises

1. Team Leads

Exercise: Display information on all team leads (i.e. employees who do not have a manager_id).

Solution:

SELECT *
FROM employee
WHERE manager_id IS NULL;

Explanation: To filter for the employees who do not have a manager_id, we need to use the IS NULL operator. Using regular operators like = or != with NULLs does not work as expected in SQL. If you use them, the result will always be NULL and the WHERE condition will fail.

If we wanted to show all non-team-lead employees, the filtering condition would be WHERE manager_id IS NOT NULL. You can read more about what NULLs are and how they work in NULLs and Handling Missing Data in SQL.

2. Established Departments

Exercise: Display all physical departments (i.e. those that have a region value). Departments without a region operate online.

Solution:

SELECT *
FROM department
WHERE region IS NOT NULL;

Explanation: Use the same principle for filtering that we discussed in the previous exercise. In this case, you will need to use the IS NOT NULL operator.

3. Missing Bonuses

Exercise: Display the number of employees that have a missing bonus.

Solution:

SELECT COUNT(*)
FROM employee
WHERE bonus IS NULL;

Explanation: Filter for the employees without a bonus using the IS NULL operator. Use the COUNT() aggregate function to count the number of employees without a bonus.

Note: Make sure to use the proper argument for the COUNT() function, as COUNT(bonus) WHERE bonus IS NULL will always return 0.

4. Small Bonuses

Exercise: Display all employees who received a bonus smaller than 300, including employees who don’t have a bonus recorded. Show employees with no recorded bonus first, then order employees from the smallest to the largest bonus.

Solution:

SELECT *
FROM employee
WHERE bonus < 300
  OR bonus IS NULL
ORDER BY bonus NULLS FIRST;

Explanation: Here, the condition is divided into two parts:

1. Bonuses smaller than 300, and
2. Missing bonuses.

We can use the < (less than) operator in the first part, but the second part will need the IS NULL operator.

Remember that NULL is not “bigger” or “smaller” than 300, so you need to use IS NULL specifically. Join the two conditions with the OR operator.

To sort the rows by bonus, use an ORDER BY clause. By default, the rows will be sorted in ascending order (i.e. smallest to largest bonus). To explicitly specify that the NULL rows should come first in the result, use the NULLS FIRST clause.

5. Online Employees

Exercise: Find all employees who work in remote departments (i.e. those where region is NULL). Display the employee’s name and the department’s name.

Solution:

SELECT 
  employee.name AS employee,
  department.name AS department
FROM employee
JOIN department
  ON employee.department_id = department.id
WHERE department.region IS NULL;

Explanation: Join the employee and department tables to get access to both the employee’s and department’s name. Rename the columns with the AS keyword to avoid duplicate names. Then filter the result by the department’s region, keeping only the rows where the region IS NULL.

6. Employees and Regions

Exercise: For each employee, display their name, the name of their assigned department, and the region in which their department is based. For employees in departments with a missing region, display ‘Online’ as the region.

Solution:

SELECT 
  employee.name AS employee,
  department.name AS department,
  COALESCE(region, ‘Online’) AS region
FROM employee
JOIN department
  ON employee.department_id = department.id;

Explanation: To display both the employee’s name and the department’s name and region, join the tables.

Employees in departments without a region will have NULL as the value of that column. To replace this, we can use the COALESCE() function: it will keep the non-null values, but replace the NULLs in the result with a new value that we provide (‘Online’). If you would like to know more about how this function works, check out our article on The COALESCE() Function.

7. Regional Headcount

Exercise: Display every region in the database together with the number of employees who work at that region. Show ‘Online’ as the region for employees who do not have one.

Solution:

SELECT 
  COALESCE(region, ‘Online’) AS region,
  COUNT(*)
FROM employee
JOIN department
  ON employee.department_id = department.id
GROUP BY region;

Explanation: Join the employee and department tables. Just like in the last exercise, use the COALESCE() function to replace missing regions with ‘Online’.

Add the COUNT() aggregate function to get the number of employees in each region, then group the result by the region column.

Note: There is no need to use COALESCE() in the GROUP BY clause. COALESCE() replaces all NULLs with the same value, so the number of groups and the rows in them remain unchanged.

8. Bonus-to-Salary Ratio

Exercise: For each employee, display their name and the ratio between their bonus and their salary. If the bonus is 0, display NULL instead.

Solution:

SELECT 
  name,
  NULLIF(bonus, 0) / salary AS ratio 
FROM employee;

Explanation: When calculating the ratio between salary and bonus, remember that the bonus could be 0 and we don’t want to display 0 in the final result. To avoid that, we can use the NULLIF() function with the bonus column. This function checks if the bonus is 0; if it is, it  replaces the 0 with NULL. Any arithmetic operations that include NULL will result in NULL being returned. So if the bonus is NULL, the ratio will also be NULL.

9. Departmental Headcount

Exercise: For each department, count the number of employees working there. Include all departments, even those that do not have employees assigned to them. Ignore employees without a department.

Solution:

SELECT 
  department.name AS department,
  COUNT(employee.id) AS employees
FROM department
LEFT JOIN employee
  ON department.id = employee.department_id
GROUP BY 
  department.name, 
  department.id;

Explanation: Join the department and employee tables. To make sure that departments without employees are shown in the result, use LEFT JOIN and place the department table first. Count the employees’ IDs to avoid counting NULL rows, and group the result by the department’s name and id (in case two departments have the same name).

10. Roles per Department

Exercise: Display each department’s name, region, and the number of different roles within it. Make sure that there is an ‘unassigned’ section for employees without an assigned department.

Solution:

SELECT
  COALESCE(department.name, ‘unassigned’) AS department,
  region,
  COUNT(DISTINCT position) AS different_positions
FROM department
FULL OUTER JOIN employee
  ON employee.department_id = department.id
GROUP BY
  department.region, 
  department.name, 
  department.id

Explanation: Join the department and employee tables using a FULL OUTER JOIN to make sure that departments without employees and employees without departments are shown. The order of the tables does not matter here.

To handle employees without a department, use the COALESCE() function to replace the department name with ‘unassigned’. Count the number of different positions in each department using COUNT(DISTINCT position). You can learn more about using the DISTINCT keyword in our cookbook article.

Group the result by the region, name, and id of the department. Note that ‘unassigned’ is one of the resulting groups.

Thirsty for More SQL NULL Practice Exercises?

Nicely done! You have learned quite a bit about handling NULLs in SQL! If you want some more practice exercises, check out our extensive SQL Practice Track. In addition to NULLs, it covers every topic there is to help you keep your SQL skills sharp.

Happy practicing!