Why set operations are needed in SQL - See It in Action

Understanding Why Set Operations Are Needed in SQL

📖 Scenario: You work at a bookstore that has two separate tables: one for books currently in stock and another for books on order. You want to find combined lists of books for different purposes.

🎯 Goal: Build SQL queries using set operations to combine and compare book lists from two tables.

📋 What You'll Learn

Create two tables named in_stock and on_order with book titles.

Add a configuration variable to limit results to books starting with the letter 'A'.

Use the UNION operation to combine book lists without duplicates.

Use the INTERSECT operation to find books present in both tables.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses often have data in separate tables and need to combine or compare lists, like current stock and incoming orders.

💼 Career

Understanding set operations in SQL is essential for data analysts and database developers to write efficient queries that merge or compare data from different sources.

1

Create the in_stock and on_order tables

Write SQL statements to create two tables called in_stock and on_order. Each table should have a single column title of type VARCHAR(100). Insert these exact book titles into in_stock: 'Alice in Wonderland', 'Brave New World', 'Catch-22'. Insert these exact book titles into on_order: 'Animal Farm', 'Brave New World', 'Don Quixote'.

SQL

-- Create tables and insert data for in_stock and on_order
-- Your code here

Need a hint?

Use CREATE TABLE to make tables and INSERT INTO to add rows.

2

Add a filter for book titles starting with 'A'

Create a variable or placeholder named filter_letter and set it to the string 'A'. This will be used to filter book titles starting with this letter in later queries.

SQL

CREATE TABLE in_stock (title VARCHAR(100));
CREATE TABLE on_order (title VARCHAR(100));

INSERT INTO in_stock (title) VALUES
('Alice in Wonderland'),
('Brave New World'),
('Catch-22');

INSERT INTO on_order (title) VALUES
('Animal Farm'),
('Brave New World'),
('Don Quixote');

-- Set filter_letter to 'A'
-- Your code here

Need a hint?

Use a variable or session variable to hold the filter letter.

3

Use UNION to combine book lists without duplicates

Write a SQL query that selects title from in_stock where title starts with the value in @filter_letter. Use UNION to combine this with a selection of title from on_order where title also starts with @filter_letter. This query should return all unique book titles starting with 'A' from both tables.

SQL

CREATE TABLE in_stock (title VARCHAR(100));
CREATE TABLE on_order (title VARCHAR(100));

INSERT INTO in_stock (title) VALUES
('Alice in Wonderland'),
('Brave New World'),
('Catch-22');

INSERT INTO on_order (title) VALUES
('Animal Farm'),
('Brave New World'),
('Don Quixote');

-- Configuration variable for filtering
SET @filter_letter = 'A';

-- Query using UNION to combine titles starting with @filter_letter
-- Your code here

Need a hint?

Use LIKE CONCAT(@filter_letter, '%') to filter titles starting with the letter.

4

Use INTERSECT to find books in both tables

Write a SQL query that selects title from in_stock where title starts with @filter_letter and use INTERSECT to find titles also present in on_order starting with @filter_letter. This query returns book titles starting with 'A' that are in both tables.

SQL

CREATE TABLE in_stock (title VARCHAR(100));
CREATE TABLE on_order (title VARCHAR(100));

INSERT INTO in_stock (title) VALUES
('Alice in Wonderland'),
('Brave New World'),
('Catch-22');

INSERT INTO on_order (title) VALUES
('Animal Farm'),
('Brave New World'),
('Don Quixote');

-- Configuration variable for filtering
SET @filter_letter = 'A';

SELECT title FROM in_stock WHERE title LIKE CONCAT(@filter_letter, '%')
UNION
SELECT title FROM on_order WHERE title LIKE CONCAT(@filter_letter, '%');

-- Query using INTERSECT to find common titles starting with @filter_letter
-- Your code here

Need a hint?

Use INTERSECT to find common rows between two queries.