INTERSECT and EXCEPT in PostgreSQL - Mini Project: Build & Apply

Using INTERSECT and EXCEPT in PostgreSQL Queries

📖 Scenario: You work for a bookstore that wants to analyze its inventory and sales data. You have two tables: inventory and sales. You want to find which books are both in inventory and sold, and which books are in inventory but have not been sold yet.

🎯 Goal: Build SQL queries using INTERSECT and EXCEPT to find common and exclusive book titles between inventory and sales.

📋 What You'll Learn

Create two tables: inventory and sales with a title column

Insert specific book titles into both tables

Write a query using INTERSECT to find titles present in both tables

Write a query using EXCEPT to find titles in inventory but not in sales

💡 Why This Matters

🌍 Real World

Bookstores and inventory managers often need to compare lists of items to find overlaps or differences, such as which books are in stock and which have been sold.

💼 Career

Understanding INTERSECT and EXCEPT is useful for data analysts and database developers who write queries to compare datasets and generate reports.

1

Create the inventory and sales tables with book titles

Create two tables called inventory and sales. Each table should have one column called title of type VARCHAR. Then insert these exact titles into inventory: 'The Hobbit', '1984', 'Pride and Prejudice', 'To Kill a Mockingbird'. Insert these exact titles into sales: '1984', 'The Hobbit', 'The Catcher in the Rye'.

PostgreSQL

-- Create tables inventory and sales with title column
-- Insert specified book titles into both tables

Need a hint?

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

2

Set up a query to find common book titles using INTERSECT

Write a SQL query that selects the title from inventory and intersects it with the title from sales to find book titles present in both tables. Use the exact query structure: SELECT title FROM inventory INTERSECT SELECT title FROM sales;

PostgreSQL

CREATE TABLE inventory (title VARCHAR);
CREATE TABLE sales (title VARCHAR);

INSERT INTO inventory (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird');

INSERT INTO sales (title) VALUES
('1984'),
('The Hobbit'),
('The Catcher in the Rye');

-- Write a query using INTERSECT to find common titles
-- Your code here

Need a hint?

Use INTERSECT between two SELECT statements to find common rows.

3

Set up a query to find book titles in inventory but not sold using EXCEPT

Write a SQL query that selects the title from inventory and uses EXCEPT with the title from sales to find book titles that are in inventory but have not been sold. Use the exact query: SELECT title FROM inventory EXCEPT SELECT title FROM sales;

PostgreSQL

CREATE TABLE inventory (title VARCHAR);
CREATE TABLE sales (title VARCHAR);

INSERT INTO inventory (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird');

INSERT INTO sales (title) VALUES
('1984'),
('The Hobbit'),
('The Catcher in the Rye');

SELECT title FROM inventory
INTERSECT
SELECT title FROM sales;

-- Write a query using EXCEPT to find titles in inventory but not in sales
-- Your code here

Need a hint?

Use EXCEPT between two SELECT statements to find rows in the first but not in the second.

4

Complete the project by adding comments explaining the queries

Add comments above each query explaining what it does. For the INTERSECT query, add a comment: -- Find books present in both inventory and sales. For the EXCEPT query, add a comment: -- Find books in inventory not sold yet.

PostgreSQL

CREATE TABLE inventory (title VARCHAR);
CREATE TABLE sales (title VARCHAR);

INSERT INTO inventory (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird');

INSERT INTO sales (title) VALUES
('1984'),
('The Hobbit'),
('The Catcher in the Rye');

-- Add comments explaining the queries below
-- Your code here

Need a hint?

Use SQL comments starting with -- to explain each query.