ORDER BY with NULLS FIRST and NULLS LAST in PostgreSQL - Mini Project: Build & Apply

Sorting Data with ORDER BY and NULLS FIRST / NULLS LAST in PostgreSQL

📖 Scenario: You are managing a small bookstore database. Some books have a known publication year, but others do not have this information yet, so their publication year is NULL. You want to list the books sorted by their publication year, but you want to control where the NULL values appear in the list.

🎯 Goal: Create a table called books with book titles and publication years. Then write two queries: one that sorts books by publication year with NULL values first, and another that sorts books by publication year with NULL values last.

📋 What You'll Learn

Create a table called books with columns title (text) and pub_year (integer).

Insert exactly these rows into books: ('The Hobbit', 1937), ('1984', 1949), ('Unknown Book', NULL), ('Brave New World', 1932), ('Future Book', NULL).

Write a query to select all columns from books ordered by pub_year with NULL values first.

Write a query to select all columns from books ordered by pub_year with NULL values last.

💡 Why This Matters

🌍 Real World

Sorting data with NULL values is common in real databases, such as when some information is missing or unknown. Controlling where NULLs appear helps make reports clearer.

💼 Career

Database developers and analysts often need to write queries that handle NULL values properly to ensure correct data presentation and analysis.

1

Create the books table and insert data

Create a table called books with columns title as text and pub_year as integer. Then insert these exact rows: ('The Hobbit', 1937), ('1984', 1949), ('Unknown Book', NULL), ('Brave New World', 1932), ('Future Book', NULL).

PostgreSQL

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

Need a hint?

Use CREATE TABLE to make the table, then INSERT INTO to add the rows exactly as given.

2

Write a query to order books with NULL publication years first

Write a SQL query to select all columns from books ordered by pub_year with NULL values appearing first. Use ORDER BY pub_year NULLS FIRST.

PostgreSQL

CREATE TABLE books (
    title TEXT,
    pub_year INTEGER
);

INSERT INTO books (title, pub_year) VALUES
('The Hobbit', 1937),
('1984', 1949),
('Unknown Book', NULL),
('Brave New World', 1932),
('Future Book', NULL);

-- Write a query to select all books ordered by pub_year with NULLS FIRST
-- Your code here

Need a hint?

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

3

Write a query to order books with NULL publication years last

Write a SQL query to select all columns from books ordered by pub_year with NULL values appearing last. Use ORDER BY pub_year NULLS LAST.

PostgreSQL

CREATE TABLE books (
    title TEXT,
    pub_year INTEGER
);

INSERT INTO books (title, pub_year) VALUES
('The Hobbit', 1937),
('1984', 1949),
('Unknown Book', NULL),
('Brave New World', 1932),
('Future Book', NULL);

SELECT * FROM books ORDER BY pub_year NULLS FIRST;

-- Write a query to select all books ordered by pub_year with NULLS LAST
-- Your code here

Need a hint?

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

4

Add a final query to show books ordered by title ascending

Write a SQL query to select all columns from books ordered by title in ascending alphabetical order.

PostgreSQL

CREATE TABLE books (
    title TEXT,
    pub_year INTEGER
);

INSERT INTO books (title, pub_year) VALUES
('The Hobbit', 1937),
('1984', 1949),
('Unknown Book', NULL),
('Brave New World', 1932),
('Future Book', NULL);

SELECT * FROM books ORDER BY pub_year NULLS FIRST;

SELECT * FROM books ORDER BY pub_year NULLS LAST;

-- Write a query to select all books ordered by title ascending
-- Your code here

Need a hint?

Use ORDER BY title ASC to sort books alphabetically by title.