UNION ALL with duplicates in SQL - Mini Project: Build & Apply

Using UNION ALL to Combine Tables with Duplicates

📖 Scenario: You work at a bookstore that has two separate tables for new arrivals and best sellers. Some books appear in both tables because they are both new and popular.You want to create a combined list of all books from both tables, including duplicates, to analyze total stock.

🎯 Goal: Build a SQL query that uses UNION ALL to combine the new_arrivals and best_sellers tables, keeping duplicate book entries.

📋 What You'll Learn

Create a table called new_arrivals with columns book_id and title and insert 3 specific books.

Create a table called best_sellers with columns book_id and title and insert 3 specific books, including one that duplicates a book from new_arrivals.

Write a SQL query using UNION ALL to combine all rows from both tables, including duplicates.

Ensure the final query selects book_id and title from the combined result.

💡 Why This Matters

🌍 Real World

Combining data from multiple sources like sales and inventory lists to get a full picture including duplicates.

💼 Career

Understanding <code>UNION ALL</code> is important for data analysts and database developers who merge datasets without losing repeated entries.

1

Create the new_arrivals table and insert data

Create a table called new_arrivals with columns book_id (integer) and title (text). Insert these exact rows: (1, 'The Silent Patient'), (2, 'Where the Crawdads Sing'), (3, 'Becoming').

SQL

-- Create new_arrivals table and insert data
-- Your code here

Need a hint?

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

2

Create the best_sellers table and insert data

Create a table called best_sellers with columns book_id (integer) and title (text). Insert these exact rows: (2, 'Where the Crawdads Sing'), (4, 'Educated'), (5, 'The Testaments').

SQL

CREATE TABLE new_arrivals (book_id INTEGER, title TEXT);
INSERT INTO new_arrivals (book_id, title) VALUES
(1, 'The Silent Patient'),
(2, 'Where the Crawdads Sing'),
(3, 'Becoming');

-- Create best_sellers table and insert data
-- Your code here

Need a hint?

Remember to use the same column names and data types as in new_arrivals.

3

Write the UNION ALL query to combine both tables

Write a SQL query that selects book_id and title from new_arrivals and combines it with all rows from best_sellers using UNION ALL to keep duplicates.

SQL

CREATE TABLE new_arrivals (book_id INTEGER, title TEXT);
INSERT INTO new_arrivals (book_id, title) VALUES
(1, 'The Silent Patient'),
(2, 'Where the Crawdads Sing'),
(3, 'Becoming');

CREATE TABLE best_sellers (book_id INTEGER, title TEXT);
INSERT INTO best_sellers (book_id, title) VALUES
(2, 'Where the Crawdads Sing'),
(4, 'Educated'),
(5, 'The Testaments');

-- Write UNION ALL query below
-- Your code here

Need a hint?

Use UNION ALL between two SELECT statements to keep duplicates.

4

Complete the query with ordering by book_id

Add an ORDER BY book_id clause at the end of your UNION ALL query to sort the combined results by book_id in ascending order.

SQL

CREATE TABLE new_arrivals (book_id INTEGER, title TEXT);
INSERT INTO new_arrivals (book_id, title) VALUES
(1, 'The Silent Patient'),
(2, 'Where the Crawdads Sing'),
(3, 'Becoming');

CREATE TABLE best_sellers (book_id INTEGER, title TEXT);
INSERT INTO best_sellers (book_id, title) VALUES
(2, 'Where the Crawdads Sing'),
(4, 'Educated'),
(5, 'The Testaments');

SELECT book_id, title FROM new_arrivals
UNION ALL
SELECT book_id, title FROM best_sellers
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Place ORDER BY book_id at the end of the combined query to sort results.