Using UNION, INTERSECT, and EXCEPT in PostgreSQL

Variant 1: Online Bookstore - Genre Analysis

Database Schema:

CREATE TABLE genre_books_set1 (
    book_id INTEGER,
    book_title VARCHAR(255),
    genre VARCHAR(50)
);

CREATE TABLE genre_books_set2 (
    book_id INTEGER,
    book_title VARCHAR(255),
    genre VARCHAR(50)
);

INSERT INTO genre_books_set1 (book_id, book_title, genre) VALUES
(1, 'The Girl with the Dragon Tattoo', 'Mystery'),
(2, 'Gone Girl', 'Mystery'),
(3, 'The Lord of the Rings', 'Fantasy'),
(4, 'Pride and Prejudice', 'Romance'),
(5, 'The Da Vinci Code', 'Thriller'),
(6, 'The Hobbit', 'Fantasy');

INSERT INTO genre_books_set2 (book_id, book_title, genre) VALUES
(1, 'The Girl with the Dragon Tattoo', 'Thriller'),
(2, 'Gone Girl', 'Thriller'),
(7, 'To Kill a Mockingbird', 'Drama'),
(8, '1984', 'Science Fiction'),
(9, 'Animal Farm', 'Satire'),
(3, 'The Lord of the Rings', 'Adventure');

Tasks:

  1. Find all book titles that appear in either genre_books_set1 or genre_books_set2.
  2. Find all book titles that appear in both genre_books_set1 and genre_books_set2.
  3. Find book titles that are in genre_books_set1 but not in genre_books_set2.
  4. Find book titles that are in genre_books_set2 but not in genre_books_set1.
  5. List all genres present in either genre_books_set1 or genre_books_set2.
  6. List genres that are common to both genre_books_set1 and genre_books_set2.
  7. List genres that are present in genre_books_set1 but not in genre_books_set2.
  8. Find all unique combinations of (genre, book_title) from both tables.
  9. Find all book titles that are of the ‘Mystery’ genre from either genre_books_set1 or genre_books_set2.
  10. Find all book titles that are of the ‘Fantasy’ genre and are present in both genre_books_set1 and genre_books_set2.
Variant 2: University Courses - Department Enrollments

Database Schema:

CREATE TABLE cs_courses_enrollments (
    student_id INTEGER,
    student_name VARCHAR(255),
    course_name VARCHAR(255)
);

CREATE TABLE engineering_courses_enrollments (
    student_id INTEGER,
    student_name VARCHAR(255),
    course_name VARCHAR(255)
);

INSERT INTO cs_courses_enrollments (student_id, student_name, course_name) VALUES
(1, 'Alice Smith', 'Introduction to Algorithms'),
(4, 'David Lee', 'Database Systems'),
(2, 'Bob Johnson', 'Data Structures'),
(5, 'Eve White', 'Computer Networks'),
(6, 'Frank Green', 'Operating Systems');

INSERT INTO engineering_courses_enrollments (student_id, student_name, course_name) VALUES
(2, 'Bob Johnson', 'Thermodynamics'),
(1, 'Alice Smith', 'Circuit Analysis'),
(3, 'Charlie Brown', 'Mechanics'),
(7, 'Grace Black', 'Fluid Dynamics'),
(5, 'Eve White', 'Engineering Math');

Tasks:

  1. Find all student names enrolled in either CS courses or Engineering courses.
  2. Find student names enrolled in both CS courses and Engineering courses.
  3. Find student names enrolled in CS courses but not in Engineering courses.
  4. Find student names enrolled in Engineering courses but not in CS courses.
  5. List all course names offered by either CS or Engineering departments.
  6. List course names offered by both CS and Engineering departments.
  7. List course names offered by CS but not Engineering departments.
  8. Find all unique combinations of (student_name, course_name) from both departments.
  9. Find all student names enrolled in ‘Database Systems’ course or ‘Thermodynamics’ course from their respective tables.
  10. Find student names who are enrolled in ‘Data Structures’ course in cs_courses_enrollments AND also enrolled in ‘Thermodynamics’ course in engineering_courses_enrollments.
Variant 3: Music Streaming Service - Artist Popularity

Database Schema:

CREATE TABLE pop_artists_popularity (
    artist_name VARCHAR(255),
    country VARCHAR(100),
    monthly_listeners INTEGER
);

CREATE TABLE rock_artists_popularity (
    artist_name VARCHAR(255),
    country VARCHAR(100),
    monthly_listeners INTEGER
);

INSERT INTO pop_artists_popularity (artist_name, country, monthly_listeners) VALUES
('Taylor Swift', 'USA', 85000000),
('Ed Sheeran', 'UK', 70000000),
('Maroon 5', 'USA', 55000000),
('Justin Bieber', 'Canada', 60000000),
('Ariana Grande', 'USA', 75000000);

INSERT INTO rock_artists_popularity (artist_name, country, monthly_listeners) VALUES
('Queen', 'UK', 45000000),
('The Beatles', 'UK', 50000000),
('Coldplay', 'UK', 65000000),
('Imagine Dragons', 'USA', 70000000),
('Maroon 5', 'USA', 55000000);

Tasks:

  1. Find all artist names that are listed as either Pop or Rock artists.
  2. Find artist names that are listed as both Pop and Rock artists.
  3. Find artist names that are listed as Pop artists but not Rock artists.
  4. Find artist names that are listed as Rock artists but not Pop artists.
  5. List all countries of origin for either Pop or Rock artists.
  6. List countries that have artists in both Pop and Rock categories.
  7. List countries that have Pop artists but no Rock artists listed.
  8. Find all unique combinations of (artist_name, country) from both tables.
  9. Find all artist names from ‘USA’ in either pop_artists_popularity or rock_artists_popularity table.
  10. Find artist names from ‘UK’ that are present in both pop_artists_popularity and rock_artists_popularity tables.
Variant 4: E-commerce Platform - Product Catalog Comparison

Database Schema:

CREATE TABLE electronics_products_catalog (
    product_name VARCHAR(255),
    price DECIMAL(10, 2),
    brand VARCHAR(100)
);

CREATE TABLE durable_products_catalog (
    product_name VARCHAR(255),
    price DECIMAL(10, 2),
    brand VARCHAR(100)
);

INSERT INTO electronics_products_catalog (product_name, price, brand) VALUES
('Laptop', 1200.00, 'BrandA'),
('Smartphone', 900.00, 'BrandB'),
('Tablet', 500.00, 'BrandC'),
('Smartwatch', 300.00, 'BrandA'),
('Headphones', 150.00, 'BrandD');

INSERT INTO durable_products_catalog (product_name, price, brand) VALUES
('Chair', 150.00, 'BrandE'),
('Desk', 300.00, 'BrandF'),
('Table', 250.00, 'BrandE'),
('Laptop', 1200.00, 'BrandA'),
('Sofa', 500.00, 'BrandG');

Tasks:

  1. Find all product names that are listed in either Electronics or Durable product catalogs.
  2. Find product names that are listed in both Electronics and Durable product catalogs.
  3. Find product names that are in the Electronics catalog but not in the Durable catalog.
  4. Find product names that are in the Durable catalog but not in the Electronics catalog.
  5. List all brands present in either Electronics or Durable product catalogs.
  6. List brands that appear in both Electronics and Durable product catalogs.
  7. List brands that are in Electronics but not in Durable catalogs.
  8. Find all unique combinations of (product_name, price) from both catalogs.
  9. Find all product names from ‘BrandA’ in either electronics_products_catalog or durable_products_catalog.
  10. Find product names that are priced at $300.00 and are present in both electronics_products_catalog and durable_products_catalog.
Variant 5: Social Network - User Location Analysis

Database Schema:

CREATE TABLE sports_users_locations (
    username VARCHAR(255),
    location VARCHAR(100),
    activity VARCHAR(100)
);

CREATE TABLE music_users_locations (
    username VARCHAR(255),
    location VARCHAR(100),
    genre_preference VARCHAR(100)
);

INSERT INTO sports_users_locations (username, location, activity) VALUES
('john_doe', 'New York', 'Basketball'),
('jane_smith', 'London', 'Soccer'),
('peter_jones', 'Paris', 'Tennis'),
('lisa_brown', 'New York', 'Running'),
('mike_davis', 'Tokyo', 'Baseball');

INSERT INTO music_users_locations (username, location, genre_preference) VALUES
('jane_smith', 'London', 'Pop'),
('peter_jones', 'Paris', 'Jazz'),
('linda_williams', 'Los Angeles', 'Rock'),
('mike_davis', 'Tokyo', 'Classical'),
('susan_taylor', 'Chicago', 'Blues');

Tasks:

  1. Find all usernames that are listed as either Sports users or Music users.
  2. Find usernames that are listed as both Sports and Music users.
  3. Find usernames that are listed as Sports users but not Music users.
  4. Find usernames that are listed as Music users but not Sports users.
  5. List all locations of either Sports or Music users.
  6. List locations that have both Sports and Music users.
  7. List locations that have Sports users but no Music users.
  8. Find all unique combinations of (username, location) from both tables.
  9. Find all usernames located in ‘New York’ from either sports_users_locations or music_users_locations table.
  10. Find usernames who are located in ‘London’ in both sports_users_locations and music_users_locations tables.
Variant 6: Employee Skills - Department Skills Analysis

Database Schema:

CREATE TABLE programming_employees_skills (
    employee_name VARCHAR(255),
    department VARCHAR(100),
    skill VARCHAR(100)
);

CREATE TABLE design_employees_skills (
    employee_name VARCHAR(255),
    department VARCHAR(100),
    skill VARCHAR(100)
);

INSERT INTO programming_employees_skills (employee_name, department, skill) VALUES
('Ava Williams', 'IT', 'Python'),
('Jack Miller', 'Engineering', 'Java'),
('Chloe Davis', 'IT', 'JavaScript'),
('Ryan Moore', 'IT', 'C++'),
('Ella Robinson', 'Finance', 'R');

INSERT INTO design_employees_skills (employee_name, department, skill) VALUES
('Chloe Davis', 'Marketing', 'UI/UX'),
('Mia Taylor', 'Design', 'Graphic Design'),
('Jack Miller', 'Marketing', 'Branding'),
('Olivia Wilson', 'Design', 'Web Design'),
('Ava Williams', 'IT', 'Web Design');

Tasks:

  1. Find all employee names that are listed as either Programming or Design skilled.
  2. Find employee names that are listed as both Programming and Design skilled.
  3. Find employee names that are listed as Programming skilled but not Design skilled.
  4. Find employee names that are listed as Design skilled but not Programming skilled.
  5. List all departments that have either Programming or Design skilled employees.
  6. List departments that have both Programming and Design skilled employees.
  7. List departments that have Programming skilled but not Design skilled employees.
  8. Find all unique combinations of (employee_name, department) from both tables.
  9. Find all employee names from the ‘IT’ department in either programming_employees_skills or design_employees_skills table.
  10. Find employee names that are in the ‘IT’ department in both programming_employees_skills and design_employees_skills tables.

Submission Instructions

Playful GIF