Practice and reinforce the concepts from Lesson 4
Breaking News: BookVerse University is merging its East and West campus libraries into a unified system. The challenge? Two separate databases that need to work as one-and you have 48 hours before the fall semester begins.
The Stakes:
Your Mission: Master SQL JOINs to unify inventory, track cross-campus loans, identify data gaps, and ensure no book or member falls through the cracks during the migration.
Learning Objectives: By the end of this activity, you will be able to:
-- Authors table (shared across both campuses)
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
nationality VARCHAR(50),
birth_year INT
);
-- Books catalog (unified book records)
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INT REFERENCES authors(id),
isbn VARCHAR(13) UNIQUE,
publication_year INT
);
-- Campus locations
CREATE TABLE campuses (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
location VARCHAR(100),
capacity INT
);
-- Inventory: which books are at which campus
CREATE TABLE book_inventory (
id SERIAL PRIMARY KEY,
book_id INT REFERENCES books(id),
campus_id INT REFERENCES campuses(id),
copies_available INT DEFAULT 0,
shelf_location VARCHAR(20)
);
-- Library members (students and faculty)
CREATE TABLE members (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
campus_id INT REFERENCES campuses(id),
membership_type VARCHAR(20) -- 'student', 'faculty', 'staff'
);
-- Loan records (who borrowed what)
CREATE TABLE loans (
id SERIAL PRIMARY KEY,
book_id INT REFERENCES books(id),
member_id INT REFERENCES members(id),
loan_date DATE NOT NULL,
due_date DATE NOT NULL,
return_date DATE,
campus_id INT REFERENCES campuses(id)
);
-- Insert authors
INSERT INTO authors (name, nationality, birth_year) VALUES
('J.K. Rowling', 'British', 1965),
('Stephen King', 'American', 1947),
('Agatha Christie', 'British', 1890),
('George Orwell', 'British', 1903),
('Haruki Murakami', 'Japanese', 1949),
('Chimamanda Ngozi Adichie', 'Nigerian', 1977),
('Gabriel García Márquez', 'Colombian', 1927),
('Toni Morrison', 'American', 1931),
('Isaac Asimov', 'American', 1920),
('Ursula K. Le Guin', 'American', 1929),
('Margaret Atwood', 'Canadian', 1939),
('Kazuo Ishiguro', 'British', 1954);
-- Insert books
INSERT INTO books (title, author_id, isbn, publication_year) VALUES
('Harry Potter and the Philosopher''s Stone', 1, '9780439708180', 1997),
('Harry Potter and the Chamber of Secrets', 1, '9780439064873', 1998),
('The Shining', 2, '9780385121675', 1977),
('Pet Sematary', 2, '9780385182225', 1983),
('Murder on the Orient Express', 3, '9780062693662', 1934),
('And Then There Were None', 3, '9780062073488', 1939),
('1984', 4, '9780451524935', 1949),
('Animal Farm', 4, '9780452284241', 1945),
('Norwegian Wood', 5, '9780375704024', 1987),
('Kafka on the Shore', 5, '9781400079278', 2002),
('Half of a Yellow Sun', 6, '9781400095209', 2006),
('Americanah', 6, '9780307455925', 2013),
('One Hundred Years of Solitude', 7, '9780060883287', 1967),
('Love in the Time of Cholera', 7, '9780307389732', 1985),
('Beloved', 8, '9781400033416', 1987),
('Song of Solomon', 8, '9781400033423', 1977),
('Foundation', 9, '9780553293357', 1951),
('I, Robot', 9, '9780553382563', 1950),
('The Left Hand of Darkness', 10, '9780441478125', 1969),
('The Dispossessed', 10, '9780061054884', 1974),
('The Handmaid''s Tale', 11, '9780385490818', 1985),
('Oryx and Crake', 11, '9780385721677', 2003),
('Never Let Me Go', 12, '9781400078776', 2005),
('The Remains of the Day', 12, '9780679731726', 1989),
('It', 2, '9781501142970', 1986),
('The Stand', 2, '9780307743688', 1978),
('Death on the Nile', 3, '9780062073556', 1937),
('The ABC Murders', 3, '9780062073549', 1936),
('1Q84', 5, '9780307476463', 2009),
('The Wind-Up Bird Chronicle', 5, '9780679775430', 1994);
-- Insert campuses
INSERT INTO campuses (name, location, capacity) VALUES
('East Campus', '1200 University Ave', 5000),
('West Campus', '3400 College Blvd', 4500);
-- Insert inventory (books distributed across campuses)
INSERT INTO book_inventory (book_id, campus_id, copies_available, shelf_location) VALUES
-- East Campus inventory
(1, 1, 5, 'A-101'), (2, 1, 3, 'A-102'), (3, 1, 2, 'B-205'),
(4, 1, 1, 'B-206'), (7, 1, 4, 'C-301'), (8, 1, 2, 'C-302'),
(11, 1, 3, 'D-401'), (12, 1, 2, 'D-402'), (15, 1, 2, 'E-501'),
(17, 1, 4, 'F-601'), (18, 1, 3, 'F-602'), (21, 1, 2, 'G-701'),
(23, 1, 1, 'H-801'), (25, 1, 2, 'I-901'), (27, 1, 1, 'J-1001'),
-- West Campus inventory
(1, 2, 3, 'W-A10'), (3, 2, 4, 'W-B20'), (5, 2, 2, 'W-C30'),
(6, 2, 3, 'W-C31'), (9, 2, 2, 'W-D40'), (10, 2, 1, 'W-D41'),
(13, 2, 3, 'W-E50'), (14, 2, 2, 'W-E51'), (16, 2, 2, 'W-F60'),
(19, 2, 1, 'W-G70'), (20, 2, 2, 'W-G71'), (22, 2, 3, 'W-H80'),
(24, 2, 2, 'W-I90'), (26, 2, 1, 'W-J100'), (28, 2, 2, 'W-K110'),
-- Books at both campuses
(29, 1, 2, 'A-103'), (29, 2, 3, 'W-A11'),
(30, 1, 1, 'A-104'), (30, 2, 2, 'W-A12');
-- Insert members
INSERT INTO members (name, email, campus_id, membership_type) VALUES
-- East Campus members
('Alice Johnson', 'alice.j@bookverse.edu', 1, 'student'),
('Bob Smith', 'bob.s@bookverse.edu', 1, 'faculty'),
('Carol White', 'carol.w@bookverse.edu', 1, 'student'),
('David Brown', 'david.b@bookverse.edu', 1, 'student'),
('Emma Davis', 'emma.d@bookverse.edu', 1, 'staff'),
('Frank Miller', 'frank.m@bookverse.edu', 1, 'student'),
('Grace Lee', 'grace.l@bookverse.edu', 1, 'faculty'),
('Henry Wilson', 'henry.w@bookverse.edu', 1, 'student'),
-- West Campus members
('Iris Martinez', 'iris.m@bookverse.edu', 2, 'student'),
('Jack Taylor', 'jack.t@bookverse.edu', 2, 'faculty'),
('Kelly Anderson', 'kelly.a@bookverse.edu', 2, 'student'),
('Liam Thomas', 'liam.t@bookverse.edu', 2, 'student'),
('Maya Jackson', 'maya.j@bookverse.edu', 2, 'staff'),
('Noah Harris', 'noah.h@bookverse.edu', 2, 'student'),
('Olivia Clark', 'olivia.c@bookverse.edu', 2, 'faculty'),
('Peter Lewis', 'peter.l@bookverse.edu', 2, 'student'),
-- Members who never borrowed
('Quinn Roberts', 'quinn.r@bookverse.edu', 1, 'student'),
('Rachel Green', 'rachel.g@bookverse.edu', 2, 'student'),
('Sam Walker', 'sam.w@bookverse.edu', 1, 'student'),
('Tina Hall', 'tina.h@bookverse.edu', 2, 'student');
-- Insert loan records
INSERT INTO loans (book_id, member_id, loan_date, due_date, return_date, campus_id) VALUES
-- East Campus loans
(1, 1, '2024-09-01', '2024-09-15', '2024-09-14', 1),
(2, 1, '2024-09-15', '2024-09-29', NULL, 1),
(3, 2, '2024-09-05', '2024-09-19', '2024-09-18', 1),
(7, 3, '2024-09-10', '2024-09-24', NULL, 1),
(8, 4, '2024-09-12', '2024-09-26', '2024-09-25', 1),
(11, 5, '2024-09-03', '2024-09-17', '2024-09-16', 1),
(15, 6, '2024-09-08', '2024-09-22', NULL, 1),
(17, 7, '2024-09-11', '2024-09-25', '2024-09-24', 1),
(21, 8, '2024-09-06', '2024-09-20', NULL, 1),
(23, 1, '2024-09-20', '2024-10-04', NULL, 1),
-- West Campus loans
(1, 9, '2024-09-02', '2024-09-16', '2024-09-15', 2),
(3, 10, '2024-09-07', '2024-09-21', '2024-09-20', 2),
(5, 11, '2024-09-09', '2024-09-23', NULL, 2),
(9, 12, '2024-09-04', '2024-09-18', '2024-09-17', 2),
(13, 13, '2024-09-13', '2024-09-27', NULL, 2),
(19, 14, '2024-09-14', '2024-09-28', '2024-09-27', 2),
(22, 15, '2024-09-16', '2024-09-30', NULL, 2),
(24, 16, '2024-09-18', '2024-10-02', NULL, 2),
(29, 9, '2024-09-17', '2024-10-01', NULL, 2),
(30, 11, '2024-09-19', '2024-10-03', NULL, 2);
Use Case: Show books with their authors (only books that have an author assigned).
Concept: INNER JOIN returns only rows where there's a match in BOTH tables.
-- Basic INNER JOIN: Books with authors
SELECT
b.title,
a.name AS author_name,
a.nationality,
b.publication_year
FROM books b
INNER JOIN authors a ON b.author_id = a.id
ORDER BY b.publication_year;
Result: Returns 30 books (all have authors in our dataset).
Why This Matters: During the merger, you need to verify every book has proper author attribution before going live.
Use Case: Find members who have NEVER borrowed a book (potential inactive accounts).
Concept: LEFT JOIN returns ALL rows from the left table, with NULLs where there's no match.
-- Find members who never borrowed books
SELECT
m.name,
m.email,
m.membership_type,
c.name AS campus,
l.loan_date
FROM members m
LEFT JOIN loans l ON m.id = l.member_id
LEFT JOIN campuses c ON m.campus_id = c.id
WHERE l.id IS NULL
ORDER BY m.name;
Result: Returns 4 members (Quinn, Rachel, Sam, Tina) who never borrowed.
Why This Matters: Identify inactive accounts before the merger. Should they receive orientation emails?
Use Case: Find books in the catalog that have NO physical inventory at any campus.
Concept: RIGHT JOIN returns ALL rows from the right table, showing books without inventory.
-- Books without any inventory records
SELECT
b.title,
a.name AS author,
bi.copies_available,
bi.shelf_location
FROM book_inventory bi
RIGHT JOIN books b ON bi.book_id = b.id
LEFT JOIN authors a ON b.author_id = a.id
WHERE bi.id IS NULL
ORDER BY b.title;
Result: Shows books in the catalog but not physically present at either campus.
Why This Matters: These are "ghost entries"-catalog records without physical books. Critical to fix before students search for unavailable books.
Use Case: Generate a complete inventory report showing books at East, West, or both campuses.
Concept: FULL OUTER JOIN returns ALL rows from both tables, with NULLs where matches don't exist.
-- Complete cross-campus inventory comparison
SELECT
COALESCE(b.title, 'Unknown Book') AS book_title,
a.name AS author,
COALESCE(SUM(CASE WHEN c.name = 'East Campus' THEN bi.copies_available ELSE 0 END), 0) AS east_copies,
COALESCE(SUM(CASE WHEN c.name = 'West Campus' THEN bi.copies_available ELSE 0 END), 0) AS west_copies,
COALESCE(SUM(bi.copies_available), 0) AS total_copies
FROM books b
FULL OUTER JOIN book_inventory bi ON b.id = bi.book_id
LEFT JOIN campuses c ON bi.campus_id = c.id
LEFT JOIN authors a ON b.author_id = a.id
GROUP BY b.id, b.title, a.name
HAVING COALESCE(SUM(bi.copies_available), 0) > 0
ORDER BY total_copies DESC;
Result: Shows which books are campus-exclusive vs. shared across both locations.
Why This Matters: Students need to know if they must travel to the other campus or if books can be transferred.
Use Case: Generate "If you liked this book, try these others by the same author" recommendations.
Concept: Join a table to itself to find relationships within the same dataset.
-- Books by the same author (recommendations)
SELECT
b1.title AS book,
b2.title AS also_by_author,
a.name AS author
FROM books b1
JOIN books b2 ON b1.author_id = b2.author_id AND b1.id < b2.id
JOIN authors a ON b1.author_id = a.id
ORDER BY a.name, b1.title;
Result: Shows pairs of books by the same author (e.g., "Harry Potter 1" -> "Harry Potter 2").
Why This Matters: Enhances the student experience post-merger with automated recommendations.
Use Case: Generate a comprehensive loan report spanning members, books, authors, and campuses.
Concept: Chain multiple JOINs to connect 4+ tables in a single query.
-- Complete loan history with all details
SELECT
m.name AS member_name,
m.membership_type,
b.title AS book_title,
a.name AS author_name,
c.name AS campus,
l.loan_date,
l.due_date,
CASE
WHEN l.return_date IS NULL THEN 'Still Borrowed'
WHEN l.return_date <= l.due_date THEN 'Returned On Time'
ELSE 'Returned Late'
END AS return_status
FROM loans l
JOIN members m ON l.member_id = m.id
JOIN books b ON l.book_id = b.id
JOIN authors a ON b.author_id = a.id
JOIN campuses c ON l.campus_id = c.id
ORDER BY l.loan_date DESC;
Result: Returns 20 loan records with complete context (who, what, where, when, status).
Why This Matters: During the merger, you need unified reporting across both legacy systems.
Challenge: A student emails: "I need '1984' by George Orwell. Which campus has it?"
Working Query:
-- Find book availability across campuses
SELECT
b.title,
a.name AS author,
c.name AS campus,
bi.copies_available,
bi.shelf_location
FROM books b
JOIN authors a ON b.author_id = a.id
JOIN book_inventory bi ON b.id = bi.book_id
JOIN campuses c ON bi.campus_id = c.id
WHERE b.title = '1984'
ORDER BY c.name;
Expected Output:
title | author | campus | copies_available | shelf_location
------|----------------|--------------|------------------|---------------
1984 | George Orwell | East Campus | 4 | C-301
Analysis: The book is only at East Campus. West Campus students must request a transfer or visit East.
Challenge: Identify members who registered but never borrowed a book. Marketing wants to send them a "Welcome to the Library" email campaign.
Your Task: Write a LEFT JOIN query that shows:
Starter Code:
-- TODO: Complete this query
SELECT
m.name,
m.email,
-- Add campus name here
m.membership_type
FROM members m
-- Add LEFT JOIN for loans here
-- Add LEFT JOIN for campuses here
WHERE -- Add condition to filter members with no loans
ORDER BY m.name;
Hints:
LEFT JOIN loans l ON m.id = l.member_idWHERE l.id IS NULL to find missing loansChallenge: The library director wants a report showing how many books each author has in the collection, sorted by most prolific authors first.
Your Task: Write a query using GROUP BY + JOIN that shows:
Starter Code:
-- TODO: Complete this query
SELECT
-- Add author name
-- Add COUNT of books
FROM authors a
-- Add INNER JOIN for books
-- Add GROUP BY clause
-- Add ORDER BY clause (descending by count);
Hints:
COUNT(b.id) to count booksa.id, a.name to aggregate per authorTODO 1 - Cross-Campus Availability:
✓ Should return 1 row showing '1984' at East Campus with 4 copies
✓ Shelf location should be 'C-301'
TODO 2 - Members Without Loans:
✓ Should return exactly 4 members
✓ All should have NULL loan_date
✓ Members: Quinn Roberts, Rachel Green, Sam Walker, Tina Hall
TODO 3 - Author Productivity:
✓ Should return 12 authors (all in the database)
✓ Top authors should have 5 books each (Stephen King, Murakami, Christie)
✓ Some authors should have 2 books (J.K. Rowling, Orwell, etc.)
Run these validation queries to ensure your schema is correct:
-- 1. Count total books (should be 30)
SELECT COUNT(*) AS total_books FROM books;
-- 2. Count total inventory records (should be ~40+)
SELECT COUNT(*) AS total_inventory FROM book_inventory;
-- 3. Count total loans (should be 20)
SELECT COUNT(*) AS total_loans FROM loans;
-- 4. Verify no orphaned foreign keys
SELECT COUNT(*) AS orphaned_books
FROM books b
LEFT JOIN authors a ON b.author_id = a.id
WHERE a.id IS NULL;
-- Should return 0
Indexing Foreign Keys: Always index columns used in JOIN conditions
CREATE INDEX idx_books_author ON books(author_id);
CREATE INDEX idx_loans_member ON loans(member_id);
Join Order Matters: Start with the smallest table in multi-table JOINs
FROM loans JOIN books JOIN authors (large -> small)FROM campuses JOIN book_inventory JOIN books (small -> large)Avoid Cartesian Products: Always include ON clauses to prevent accidental cross joins
| JOIN Type | Use Case | Returns |
|---|---|---|
| INNER | Standard matching data | Only matching rows |
| LEFT | Find missing data | All left rows + matches |
| RIGHT | Rarely used (use LEFT instead) | All right rows + matches |
| FULL OUTER | Complete data audits | All rows from both tables |
| Self | Hierarchies, recommendations | Related rows in same table |
| CROSS | Generate combinations | Every possible pairing |
Pattern 1: Latest Loan Per Member
SELECT DISTINCT ON (m.id)
m.name,
b.title,
l.loan_date
FROM members m
JOIN loans l ON m.id = l.member_id
JOIN books b ON l.book_id = b.id
ORDER BY m.id, l.loan_date DESC;
Pattern 2: Books Never Borrowed
SELECT b.title, a.name AS author
FROM books b
LEFT JOIN loans l ON b.id = l.book_id
JOIN authors a ON b.author_id = a.id
WHERE l.id IS NULL;
Pattern 3: Overdue Books
SELECT m.name, b.title, l.due_date,
CURRENT_DATE - l.due_date AS days_overdue
FROM loans l
JOIN members m ON l.member_id = m.id
JOIN books b ON l.book_id = b.id
WHERE l.return_date IS NULL
AND l.due_date < CURRENT_DATE
ORDER BY days_overdue DESC;
Once you've completed all TODO queries:
Submission Checklist:
Next Up: In Activity 5, you'll use these JOIN skills with aggregations to analyze borrowing trends and generate library statistics reports.