Combining Multiple Tables with JOINs in PostgreSQL.

Variant 1: E-commerce Products and Categories

Scenario: A simplified e-commerce system needs to categorize products.

Database Schema:

Tables: products, categories

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    category_id INTEGER REFERENCES categories(category_id)
);

Sample Data:

INSERT INTO categories (category_name, description) VALUES
('Electronics', 'Electronic devices and gadgets'),
('Books', 'Literary works and publications'),
('Clothing', 'Apparel and fashion items'),
('Home Goods', 'Items for household use');

INSERT INTO products (product_name, price, category_id) VALUES
('Laptop', 1200.00, 1),
('Smartphone', 800.00, 1),
('T-Shirt', 25.00, 3),
('Novel - Mystery', 15.00, 2),
('Cookbook', 20.00, 2),
('Jeans', 60.00, 3),
('Coffee Maker', 45.00, NULL),
('Tablet', 300.00, 1),
('Pillow', 25.00, NULL);

Tasks:

  1. Using INNER JOIN, list product names and their corresponding category names.
  2. Using LEFT JOIN, show all categories and the product names within each category.
  3. Using RIGHT JOIN, list all products and their category names.
  4. Using FULL OUTER JOIN, combine categories and products.
  5. Using CROSS JOIN, generate all combinations of categories and products.
  6. Using INNER JOIN with a WHERE clause, find product names and prices for products in the ‘Electronics’ category.
  7. Using LEFT JOIN and a WHERE clause, show all categories and product names but only for products priced over $50.
  8. Using INNER JOIN, find product names and category names, ordering the result by product price in descending order.
  9. Using LEFT JOIN, count the number of products in each category. Display category name and the count.
  10. Using RIGHT JOIN, list all products and their category names, including products that might not be assigned to any category. If a product has no category, show category name as ‘Uncategorized’.
Variant 2: Library Patrons and Books

Scenario: A library system tracks patrons and the books they are interested in (wishlist).

Database Schema:

Tables: patrons, wishlist_books

CREATE TABLE patrons (
    patron_id SERIAL PRIMARY KEY,
    patron_name VARCHAR(100) NOT NULL,
    library_card_number VARCHAR(20) UNIQUE,
    city VARCHAR(50)
);

CREATE TABLE wishlist_books (
    wishlist_id SERIAL PRIMARY KEY,
    patron_id INTEGER REFERENCES patrons(patron_id),
    book_title VARCHAR(150) NOT NULL,
    author VARCHAR(100)
);

Sample Data:

INSERT INTO patrons (patron_name, library_card_number, city) VALUES
('Alice Reader', 'LC123', 'New York'),
('Bob PageTurner', 'LC456', 'Los Angeles'),
('Charlie Bookworm', 'LC789', 'Chicago'),
('Diana LibraryFan', 'LC101', 'Houston'),
('Eve Novelist', 'LC112', 'New York'),
('Frank Fictional', 'LC131', 'Los Angeles');

INSERT INTO wishlist_books (patron_id, book_title, author) VALUES
(1, 'The Secret Garden', 'Frances Hodgson Burnett'),
(NULL, 'Pride and Prejudice', 'Jane Austen'),
(1, 'To Kill a Mockingbird', 'Harper Lee'),
(3, '1984', 'George Orwell'),
(4, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(NULL, 'Jane Eyre', 'Charlotte Brontë'),
(5, 'Moby Dick', 'Herman Melville'),
(6, 'Little Women', 'Louisa May Alcott');

Tasks:

  1. Using INNER JOIN, retrieve a list of patron names and the titles of books on their wishlists.
  2. Using LEFT JOIN, show all patrons and the book titles on their wishlists.
  3. Using RIGHT JOIN, list all wishlist books and the names of the patrons who wishlisted them.
  4. Using FULL OUTER JOIN, combine patrons and wishlist books.
  5. Using CROSS JOIN, generate all combinations of patrons and wishlist books.
  6. Using INNER JOIN with a WHERE clause, find patron names and book titles for wishlisted books authored by ‘Jane Austen’.
  7. Using LEFT JOIN and a WHERE clause, show all patrons and book titles but only for books titled starting with ‘The’.
  8. Using INNER JOIN, find patron names and wishlist book titles, ordering the result by patron name alphabetically.
  9. Using LEFT JOIN, count how many books are on each patron’s wishlist. Display patron name and the count.
  10. Using RIGHT JOIN, list all wishlist books and the patron names, including wishlist entries that might not have a corresponding patron. If a wishlist book has no patron, show patron name as ‘Unknown Patron’.
Variant 3: Social Media Users and Activities

Scenario: A simplified social media platform tracks users and their activities (likes).

Database Schema:

Tables: users, activities

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    city VARCHAR(50)
);

CREATE TABLE activities (
    activity_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    activity_type VARCHAR(50) NOT NULL,
    activity_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Sample Data:

INSERT INTO users (username, city) VALUES
('NetizenNick', 'London'),
('SocialSam', 'Paris'),
('OnlineOlivia', 'New York'),
('DigitalDan', 'London'),
('TechTina', 'San Francisco'),
('GlobalGreg', 'Tokyo');

INSERT INTO activities (user_id, activity_type) VALUES
(1, 'post_like'),
(NULL, 'comment'),
(1, 'follow'),
(3, 'post_like'),
(4, 'post_like'),
(NULL, 'follow'),
(5, 'comment'),
(6, 'post_like'),
(3, 'follow');

Tasks:

  1. Using INNER JOIN, retrieve usernames and their activity types.
  2. Using LEFT JOIN, show all users and their activity types.
  3. Using RIGHT JOIN, list all activities and the usernames of the users who performed them.
  4. Using FULL OUTER JOIN, combine users and activities.
  5. Using CROSS JOIN, generate all combinations of users and activities.
  6. Using INNER JOIN with a WHERE clause, find usernames and activity types for activities of type ‘post_like’.
  7. Using LEFT JOIN and a WHERE clause, show all users and activity types but only for activities that happened in the last 24 hours.
  8. Using INNER JOIN, find usernames and activity types, ordering the result by username alphabetically.
  9. Using LEFT JOIN, count the number of activities for each user. Display username and the count of activities.
  10. Using RIGHT JOIN, list all activities and usernames, including activities that might be linked to a non-existent user. If an activity has no user, show username as ‘Unknown User’.
Variant 4: Blogging Platform Authors and Articles

Scenario: A blogging platform needs to manage authors and their articles.

Database Schema:

Tables: authors, articles

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    author_id INTEGER REFERENCES authors(author_id),
    article_title VARCHAR(200) NOT NULL,
    publication_date DATE NOT NULL
);

Sample Data:

INSERT INTO authors (author_name, email) VALUES
('Jane Doe', 'jane.doe@example.com'),
('John Smith', 'john.smith@example.com'),
('Emily White', 'emily.white@example.com'),
('David Green', 'david.green@example.com'),
('Sophia Black', 'sophia.black@example.com'),
('Oliver Brown', 'oliver.brown@example.com');

INSERT INTO articles (author_id, article_title, publication_date) VALUES
(1, 'The Future of Technology', '2024-08-01'),
(NULL, 'Cooking at Home', '2024-08-05'),
(1, 'Travel in Europe', '2024-08-10'),
(3, 'Gardening Tips', '2024-08-15'),
(4, 'Financial Planning', '2024-08-20'),
(NULL, 'Baking Desserts', '2024-08-25'),
(5, 'Photography Basics', '2024-08-30'),
(6, 'Writing a Novel', '2024-09-05');

Tasks:

  1. Using INNER JOIN, retrieve a list of author names and their article titles.
  2. Using LEFT JOIN, show all authors and their article titles.
  3. Using RIGHT JOIN, list all articles and the names of their authors.
  4. Using FULL OUTER JOIN, combine authors and articles.
  5. Using CROSS JOIN, generate all combinations of authors and articles.
  6. Using INNER JOIN with a WHERE clause, find author names and article titles for articles published in August 2024.
  7. Using LEFT JOIN and a WHERE clause, show all authors and article titles but only for articles with titles containing the word ‘Cooking’.
  8. Using INNER JOIN, find author names and article titles, ordering the result by publication date in ascending order.
  9. Using LEFT JOIN, count the number of articles written by each author. Display author name and the count of articles.
  10. Using RIGHT JOIN, list all articles and author names, including articles that might be linked to a non-existent author. If an article has no author, show author name as ‘Unknown Author’.
Variant 5: Music Artists and Songs

Scenario: A music streaming service needs to manage artists and their songs.

Database Schema:

Tables: artists, songs

CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    artist_name VARCHAR(100) NOT NULL UNIQUE,
    genre VARCHAR(50)
);

CREATE TABLE songs (
    song_id SERIAL PRIMARY KEY,
    artist_id INTEGER REFERENCES artists(artist_id),
    song_title VARCHAR(150) NOT NULL,
    duration_seconds INTEGER
);

Sample Data:

INSERT INTO artists (artist_name, genre) VALUES
('Rock Legends', 'Rock'),
('Pop Sensation', 'Pop'),
('Jazz Virtuosos', 'Jazz'),
('Blues Masters', 'Blues'),
('Classical Harmony', 'Classical'),
('Indie Vibes', 'Indie');

INSERT INTO songs (artist_id, song_title, duration_seconds) VALUES
(1, 'Stairway to Heaven', 480),
(NULL, 'Happy Tune', 240),
(1, 'Rock Anthem', 300),
(3, 'Smooth Jazz', 360),
(NULL, 'Pop Ballad', 270),
(4, 'Blues Solo', 390),
(5, 'Symphony No. 5', 600),
(6, 'Indie Track', 210);

Tasks:

  1. Using INNER JOIN, retrieve a list of artist names and their song titles.
  2. Using LEFT JOIN, show all artists and their song titles.
  3. Using RIGHT JOIN, list all songs and the names of the artists who performed them.
  4. Using FULL OUTER JOIN, combine artists and songs.
  5. Using CROSS JOIN, generate all combinations of artists and songs.
  6. Using INNER JOIN with a WHERE clause, find artist names and song titles for songs with a duration over 300 seconds.
  7. Using LEFT JOIN and a WHERE clause, show all artists and song titles but only for songs in the ‘Pop’ genre.
  8. Using INNER JOIN, find artist names and song titles, ordering the result by song duration in descending order.
  9. Using LEFT JOIN, count the number of songs for each artist. Display artist name and the count of songs.
  10. Using RIGHT JOIN, list all songs and artist names, including songs that might be linked to a non-existent artist. If a song has no artist, show artist name as ‘Unknown Artist’.
Variant 6: University Courses and Instructors

Scenario: A university database needs to manage courses and instructors who teach them.

Database Schema:

Tables: instructors, courses

CREATE TABLE instructors (
    instructor_id SERIAL PRIMARY KEY,
    instructor_name VARCHAR(100) NOT NULL,
    department VARCHAR(100)
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(150) NOT NULL,
    credits INTEGER,
    instructor_id INTEGER REFERENCES instructors(instructor_id)
);

Sample Data:

INSERT INTO instructors (instructor_name, department) VALUES
('Dr. Smith', 'Computer Science'),
('Prof. Jones', 'Mathematics'),
('Dr. Williams', 'Physics'),
('Prof. Brown', 'History'),
('Dr. Davis', 'English'),
('Prof. Miller', 'Biology');

INSERT INTO courses (course_name, credits, instructor_id) VALUES
('Intro to CS', 3, 1),
('Calculus II', 4, NULL),
('Quantum Physics', 3, 3),
('American History', 3, 4),
('Shakespearean Lit', 3, 5),
('Genetics 101', 4, 6),
('Data Structures', 4, 1),
('Linear Algebra', 3, NULL);

Tasks:

  1. Using INNER JOIN, retrieve a list of instructor names and the course names they teach.
  2. Using LEFT JOIN, show all instructors and the course names they teach.
  3. Using RIGHT JOIN, list all courses and the names of the instructors who teach them.
  4. Using FULL OUTER JOIN, combine instructors and courses.
  5. Using CROSS JOIN, generate all combinations of instructors and courses.
  6. Using INNER JOIN with a WHERE clause, find instructor names and course names for courses with 4 credits.
  7. Using LEFT JOIN and a WHERE clause, show all instructors and course names but only for courses offered in the ‘Mathematics’ department.
  8. Using INNER JOIN, find instructor names and course names, ordering the result by course credits in descending order.
  9. Using LEFT JOIN, count the number of courses taught by each instructor. Display instructor name and the count of courses.
  10. Using RIGHT JOIN, list all courses and instructor names, including courses that might be linked to a non-existent instructor. If a course has no instructor, show instructor name as ‘Unassigned Instructor’.

Submission Instructions

Playful GIF