Set operations with ORDER BY in SQL - Mini Project: Build & Apply

Set operations with ORDER BY in SQL

📖 Scenario: You work in a small bookstore that keeps two separate tables for new arrivals and best sellers. You want to create a combined list of book titles from both tables, sorted alphabetically.

🎯 Goal: Build an SQL query that combines book titles from two tables using a set operation and sorts the combined list alphabetically using ORDER BY.

📋 What You'll Learn

Create two tables named new_arrivals and best_sellers with a single column title.

Insert the exact book titles into each table as specified.

Write a query that uses UNION to combine titles from both tables.

Add ORDER BY title ASC to sort the combined list alphabetically.

💡 Why This Matters

🌍 Real World

Combining data from multiple sources and sorting it is common in reporting and data analysis for businesses.

💼 Career

Understanding set operations and sorting in SQL is essential for database querying roles, data analysts, and backend developers.

1

Create tables and insert data

Create two tables called new_arrivals and best_sellers. Each table should have one column named title of type VARCHAR(100). Insert these exact titles into new_arrivals: 'The Silent Patient', 'Where the Crawdads Sing', 'The Midnight Library'. Insert these exact titles into best_sellers: 'The Midnight Library', 'Becoming', 'Educated'.

SQL

-- Create tables new_arrivals and best_sellers with column title
-- Insert the specified book titles into each table
-- Your code here

Need a hint?

Use CREATE TABLE to make tables and INSERT INTO to add rows with the exact titles.

2

Set up the combined query

Write a query that selects the title column from new_arrivals and combines it with the title column from best_sellers using the UNION set operation.

SQL

CREATE TABLE new_arrivals (title VARCHAR(100));
CREATE TABLE best_sellers (title VARCHAR(100));

INSERT INTO new_arrivals (title) VALUES
('The Silent Patient'),
('Where the Crawdads Sing'),
('The Midnight Library');

INSERT INTO best_sellers (title) VALUES
('The Midnight Library'),
('Becoming'),
('Educated');

-- Write a query using UNION to combine titles from both tables
-- Your code here

Need a hint?

Use SELECT title FROM new_arrivals UNION SELECT title FROM best_sellers to combine the lists without duplicates.

3

Add ORDER BY to sort the combined list

Extend the previous query by adding ORDER BY title ASC at the end to sort the combined list of book titles alphabetically in ascending order.

SQL

CREATE TABLE new_arrivals (title VARCHAR(100));
CREATE TABLE best_sellers (title VARCHAR(100));

INSERT INTO new_arrivals (title) VALUES
('The Silent Patient'),
('Where the Crawdads Sing'),
('The Midnight Library');

INSERT INTO best_sellers (title) VALUES
('The Midnight Library'),
('Becoming'),
('Educated');

SELECT title FROM new_arrivals
UNION
SELECT title FROM best_sellers
-- Add ORDER BY title ASC here
-- Your code here

Need a hint?

Add ORDER BY title ASC after the UNION query to sort the results alphabetically.

4

Use UNION ALL and order the combined list

Modify the query to use UNION ALL instead of UNION to include duplicate titles, and keep the ORDER BY title ASC to sort the full list alphabetically.

SQL

CREATE TABLE new_arrivals (title VARCHAR(100));
CREATE TABLE best_sellers (title VARCHAR(100));

INSERT INTO new_arrivals (title) VALUES
('The Silent Patient'),
('Where the Crawdads Sing'),
('The Midnight Library');

INSERT INTO best_sellers (title) VALUES
('The Midnight Library'),
('Becoming'),
('Educated');

-- Change UNION to UNION ALL and keep ORDER BY
-- Your code here

Need a hint?

Replace UNION with UNION ALL to include duplicates, and keep the ORDER BY clause.