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

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

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

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

Hint

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

Practice

1. What does the UNION operation do when combining results from two SELECT queries?

easy

A. Sorts the results in ascending order

B. Combines results and removes duplicate rows

C. Combines results and keeps all duplicate rows

D. Joins tables based on a common column

Solution

1. Step 1: Understand UNION operation

   The UNION operation combines results from two SELECT queries into one result set.

2. Step 2: Check duplicate handling

   UNION removes duplicate rows, unlike UNION ALL which keeps duplicates.

3. Final Answer:

   Combines results and removes duplicate rows -> Option B

4. Quick Check:

   UNION removes duplicates = C [OK]

Hint: UNION removes duplicates; UNION ALL keeps them [OK]

Common Mistakes:

• Confusing UNION with UNION ALL
• Thinking UNION sorts results automatically
• Mixing UNION with JOIN operations

2. Which of the following is the correct syntax to combine two SELECT queries with UNION and sort the final result by column name?

easy

A. SELECT name FROM table1 UNION ORDER BY name SELECT name FROM table2;

B. SELECT name FROM table1 ORDER BY name UNION SELECT name FROM table2;

C. SELECT name FROM table1 UNION SELECT name FROM table2 ORDER BY name;

D. SELECT name FROM table1 UNION SELECT name FROM table2 SORT BY name;

Solution

1. Step 1: Understand correct UNION syntax

   The UNION combines two SELECT queries; ORDER BY applies after the last SELECT.

2. Step 2: Check placement of ORDER BY

   ORDER BY must come after the entire UNION, not between or before queries.

3. Final Answer:

   SELECT name FROM table1 UNION SELECT name FROM table2 ORDER BY name; -> Option C

4. Quick Check:

   ORDER BY after UNION = A [OK]

Hint: Place ORDER BY after all UNION queries [OK]

Common Mistakes:

• Putting ORDER BY before UNION
• Using SORT BY instead of ORDER BY
• Placing ORDER BY between SELECTs

3. Given two tables:
table1 with values (1, 'Alice'), (2, 'Bob')
table2 with values (2, 'Bob'), (3, 'Charlie')
What is the result of this query?

SELECT id, name FROM table1 UNION SELECT id, name FROM table2 ORDER BY id;

medium

A. (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')

B. (1, 'Alice'), (2, 'Bob'), (2, 'Bob'), (3, 'Charlie')

C. (2, 'Bob'), (3, 'Charlie')

D. (1, 'Alice'), (3, 'Charlie')

Solution

1. Step 1: Combine rows with UNION

   UNION merges rows from both tables and removes duplicates, so (2, 'Bob') appears once.

2. Step 2: Sort combined results by id

   Ordering by id gives rows in order: 1, 2, 3.

3. Final Answer:

   (1, 'Alice'), (2, 'Bob'), (3, 'Charlie') -> Option A

4. Quick Check:

   UNION removes duplicates, ORDER BY sorts = B [OK]

Hint: UNION removes duplicates; ORDER BY sorts final list [OK]

Common Mistakes:

• Expecting duplicates with UNION
• Ignoring ORDER BY sorting
• Confusing UNION with UNION ALL

4. Identify the error in this SQL query:

SELECT name FROM table1 UNION ALL ORDER BY name SELECT name FROM table2;

medium

A. SELECT statements must have different column names

B. UNION ALL cannot be used with ORDER BY

C. Missing semicolon after first SELECT

D. ORDER BY is placed incorrectly between UNION ALL and second SELECT

Solution

1. Step 1: Check UNION ALL syntax

   UNION ALL combines two SELECT queries; ORDER BY must come after both queries, not between.

2. Step 2: Identify ORDER BY placement error

   ORDER BY is incorrectly placed between UNION ALL and second SELECT, causing syntax error.

3. Final Answer:

   ORDER BY is placed incorrectly between UNION ALL and second SELECT -> Option D

4. Quick Check:

   ORDER BY after UNION ALL queries = D [OK]

Hint: ORDER BY must follow all UNION ALL queries [OK]

Common Mistakes:

• Placing ORDER BY between UNION ALL and second SELECT
• Thinking UNION ALL disallows ORDER BY
• Assuming column names must differ

5. You have two tables:
employees with columns (id, name, department)
contractors with columns (id, name, department)
Write a query to list all unique names from both tables sorted alphabetically, but include duplicates if the same name appears in both tables.
Which query achieves this?

hard

A. SELECT name FROM employees UNION ALL SELECT name FROM contractors ORDER BY name;

B. SELECT name FROM employees UNION SELECT name FROM contractors ORDER BY name;

C. SELECT name FROM employees INTERSECT SELECT name FROM contractors ORDER BY name;

D. SELECT name FROM employees EXCEPT SELECT name FROM contractors ORDER BY name;

Solution

1. Step 1: Understand requirement for duplicates

   The query should include duplicates if the same name appears in both tables, so duplicates must be kept.

2. Step 2: Choose correct set operation

   UNION removes duplicates, so it is not suitable. UNION ALL keeps duplicates from both tables.

3. Step 3: Confirm sorting

   ORDER BY name sorts the combined result alphabetically.

4. Final Answer:

   SELECT name FROM employees UNION ALL SELECT name FROM contractors ORDER BY name; -> Option A

5. Quick Check:

   UNION ALL keeps duplicates, ORDER BY sorts = A [OK]

Hint: Use UNION ALL to keep duplicates, ORDER BY to sort [OK]

Common Mistakes:

• Using UNION which removes duplicates
• Confusing INTERSECT or EXCEPT with UNION
• Placing ORDER BY incorrectly