Set operation column matching rules in SQL - Mini Project: Build & Apply

Set Operation Column Matching Rules in SQL

📖 Scenario: You work in a small bookstore database. You have two tables: FictionBooks and NonFictionBooks. Each table has columns for BookID, Title, and Author. You want to combine these tables to see all unique books available in the store.

🎯 Goal: Build a SQL query using a set operation that combines the Title and Author columns from both tables, showing all unique books without duplicates.

📋 What You'll Learn

Create two tables called FictionBooks and NonFictionBooks with columns BookID, Title, and Author.

Insert the exact rows specified into each table.

Write a SQL query that uses a set operation to combine the Title and Author columns from both tables.

Ensure the query returns unique rows only, with no duplicates.

💡 Why This Matters

🌍 Real World

Combining data from multiple sources or tables is common in reporting and data analysis in businesses.

💼 Career

Understanding set operations like UNION is essential for database querying roles, data analysts, and backend developers.

1

Create the FictionBooks table and insert data

Create a table called FictionBooks with columns BookID (integer), Title (text), and Author (text). Insert these rows exactly: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), (3, 'To Kill a Mockingbird', 'Harper Lee').

SQL

-- Create the FictionBooks table and insert the specified rows
-- Your code here

Need a hint?

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

2

Create the NonFictionBooks table and insert data

Create a table called NonFictionBooks with columns BookID (integer), Title (text), and Author (text). Insert these rows exactly: (1, 'Sapiens', 'Yuval Noah Harari'), (2, 'Educated', 'Tara Westover'), (3, '1984', 'George Orwell').

SQL

CREATE TABLE FictionBooks (
  BookID INTEGER,
  Title TEXT,
  Author TEXT
);

INSERT INTO FictionBooks (BookID, Title, Author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Create the NonFictionBooks table and insert the specified rows
-- Your code here

Need a hint?

Repeat the table creation and insertion steps for the NonFictionBooks table.

3

Write a query to combine Title and Author from both tables

Write a SQL query that selects the Title and Author columns from FictionBooks and NonFictionBooks and combines them using a set operation that removes duplicates.

SQL

CREATE TABLE FictionBooks (
  BookID INTEGER,
  Title TEXT,
  Author TEXT
);

INSERT INTO FictionBooks (BookID, Title, Author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE TABLE NonFictionBooks (
  BookID INTEGER,
  Title TEXT,
  Author TEXT
);

INSERT INTO NonFictionBooks (BookID, Title, Author) VALUES
  (1, 'Sapiens', 'Yuval Noah Harari'),
  (2, 'Educated', 'Tara Westover'),
  (3, '1984', 'George Orwell');

-- Write the SQL query combining Title and Author from both tables without duplicates
-- Your code here

Need a hint?

Use the UNION operator to combine the two SELECT statements and remove duplicates.

4

Complete the query with an ORDER BY clause

Add an ORDER BY clause to the query to sort the results by Title in ascending order.

SQL

CREATE TABLE FictionBooks (
  BookID INTEGER,
  Title TEXT,
  Author TEXT
);

INSERT INTO FictionBooks (BookID, Title, Author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE TABLE NonFictionBooks (
  BookID INTEGER,
  Title TEXT,
  Author TEXT
);

INSERT INTO NonFictionBooks (BookID, Title, Author) VALUES
  (1, 'Sapiens', 'Yuval Noah Harari'),
  (2, 'Educated', 'Tara Westover'),
  (3, '1984', 'George Orwell');

SELECT Title, Author FROM FictionBooks
UNION
SELECT Title, Author FROM NonFictionBooks

-- Add ORDER BY Title ascending
-- Your code here

Need a hint?

Use ORDER BY Title ASC at the end of the query to sort the results by title.