ORDER BY with NULL values behavior in SQL - Mini Project: Build & Apply

ORDER BY with NULL values behavior

📖 Scenario: You work in a company database where some employees have not yet been assigned a department. You want to list all employees sorted by their department, but you want to control where the employees with no department (NULL) appear in the list.

🎯 Goal: Build a SQL query that orders employees by their department name, showing employees with NULL department either first or last using ORDER BY with NULLS FIRST and NULLS LAST.

📋 What You'll Learn

Create a table called employees with columns id (integer), name (text), and department (text, nullable).

Insert exactly these rows into employees: (1, 'Alice', 'Sales'), (2, 'Bob', NULL), (3, 'Charlie', 'HR'), (4, 'Diana', NULL), (5, 'Eve', 'IT').

Write a query to select all columns from employees ordered by department with NULL values appearing first.

Write a query to select all columns from employees ordered by department with NULL values appearing last.

💡 Why This Matters

🌍 Real World

Handling NULL values in sorting is common when working with real-world data where some information may be missing or optional.

💼 Career

Knowing how to control the position of NULLs in ordered query results is important for data analysis, reporting, and database management roles.

1

Create the employees table and insert data

Create a table called employees with columns id as integer, name as text, and department as text that can be NULL. Then insert these exact rows: (1, 'Alice', 'Sales'), (2, 'Bob', NULL), (3, 'Charlie', 'HR'), (4, 'Diana', NULL), (5, 'Eve', 'IT').

SQL

-- Create the employees table and insert the rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add data.

2

Write a query to order employees by department with NULLs first

Write a SQL query to select all columns from employees and order the results by department so that rows with NULL department appear first.

SQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', NULL),
(3, 'Charlie', 'HR'),
(4, 'Diana', NULL),
(5, 'Eve', 'IT');

-- Write a SELECT query ordering by department with NULLS FIRST
-- Your code here

Need a hint?

Use ORDER BY department NULLS FIRST to put NULL values at the top.

3

Write a query to order employees by department with NULLs last

Write a SQL query to select all columns from employees and order the results by department so that rows with NULL department appear last.

SQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', NULL),
(3, 'Charlie', 'HR'),
(4, 'Diana', NULL),
(5, 'Eve', 'IT');

SELECT * FROM employees
ORDER BY department NULLS FIRST;

-- Write a SELECT query ordering by department with NULLS LAST
-- Your code here

Need a hint?

Use ORDER BY department NULLS LAST to put NULL values at the bottom.

4

Complete the SQL script with both queries

Ensure your SQL script includes the CREATE TABLE statement, the INSERT INTO statement with the exact rows, and both SELECT queries: one ordering by department with NULLs first, and the other ordering by department with NULLs last.

SQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', NULL),
(3, 'Charlie', 'HR'),
(4, 'Diana', NULL),
(5, 'Eve', 'IT');

SELECT * FROM employees
ORDER BY department NULLS FIRST;

SELECT * FROM employees
ORDER BY department NULLS LAST;

-- Finalize your SQL script here
-- Your code here

Need a hint?

Make sure your script has all parts: table creation, data insertion, and both queries.