Databases Tasks for Practical Class 10

Creating complex queries. Combining multiple tables and working with “Joins”.

Variant 1: Online Store Database

Scenario: You are managing the database for an online store. The database tracks customers, products, product categories, orders, and the specific items included in each order.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;

-- Create Categories table (with self-reference for subcategories)
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    parent_category_id INT REFERENCES categories(category_id) NULL -- For subcategories
);

-- Create Products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    category_id INT REFERENCES categories(category_id),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0)
);

-- Create Customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Create Orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) CHECK (status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled'))
);

-- Create OrderItems table (Junction table)
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT CHECK (quantity > 0),
    price_at_purchase DECIMAL(10, 2) -- Price when the order was placed
);

-- Insert sample data
INSERT INTO categories (category_name, parent_category_id) VALUES
('Electronics', NULL),
('Computers', 1),
('Mobile Phones', 1),
('Clothing', NULL),
('Books', NULL),
('Programming Books', 5);

INSERT INTO products (product_name, price, category_id, stock_quantity) VALUES
('Laptop Pro 15"', 1299.99, 2, 50),
('Smartphone X', 899.00, 3, 120),
('Wireless Mouse', 25.50, 2, 200),
('T-Shirt (Red)', 19.99, 4, 300),
('The SQL Enigma', 45.00, 6, 75),
('Advanced Python', 55.00, 6, 60),
('Generic USB Cable', 9.99, 1, 500),
('Monitor 27"', 299.00, 2, 30); -- Belongs to Computers category

INSERT INTO customers (customer_name, email, registration_date) VALUES
('Alice Wonderland', 'alice@example.com', '2023-01-15'),
('Bob The Builder', 'bob@example.com', '2023-02-20'),
('Charlie Chaplin', 'charlie@example.com', '2023-03-10'),
('Diana Prince', 'diana@example.com', '2023-04-05'); -- New customer, no orders yet

INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2023-10-01 10:00:00', 'Shipped'),
(2, '2023-10-05 14:30:00', 'Processing'),
(1, '2023-10-10 09:15:00', 'Pending'),
(3, '2023-10-12 11:00:00', 'Shipped');

INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase) VALUES
(1, 1, 1, 1299.99), -- Alice, Laptop
(1, 3, 1, 25.50),   -- Alice, Mouse
(2, 5, 2, 45.00),   -- Bob, SQL Book x2
(2, 7, 3, 9.99),    -- Bob, USB Cable x3
(3, 2, 1, 899.00),  -- Alice, Smartphone
(4, 4, 2, 19.99),   -- Charlie, T-Shirt x2
(4, 5, 1, 45.00);   -- Charlie, SQL Book
-- Note: Product 'Advanced Python' and 'Monitor 27"' not ordered yet.
-- Note: Customer 'Diana Prince' has no orders.
-- Note: Category 'Clothing' has T-shirt, 'Mobile Phones' has Smartphone X.

Tasks for Variant 1:

List the names of all customers who have placed at least one order, along with the order ID and order date. Order by customer name, then order date.
Show all products, their prices, and the name of their category. Include products that might not belong to any category (if possible according to schema - hint: check JOIN type).
List all categories and their parent category names. For top-level categories (those without a parent), the parent category name should appear as NULL or be handled appropriately. (Requires self-join).
Display the names of customers and the total number of distinct orders they have placed. Include customers who have placed zero orders. Order by the number of orders descending.
Find all products (product name) that have been ordered in quantities greater than 1 in any single order item. List the product name, order ID, and quantity.
List the names of products that belong to the ‘Electronics’ category or any of its subcategories. (Requires joining categories to itself and then to products).
Show the names of customers who ordered the product named ‘The SQL Enigma’.
Calculate the total quantity of each product sold across all orders. List the product name and the total quantity. Include products that have never been sold (show quantity 0). Order by product name.
List the categories that have more than 2 products associated with them. Show the category name and the count of products.
Display the details of all order items: include the order ID, customer name, product name, quantity ordered, and price at purchase. Order by Order ID, then Product Name.

Variant 2: Project Management Database

Scenario: You are managing a database for a company that tracks employees, departments, projects, tasks within those projects, and which employee is assigned to which task.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS assignments;
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

-- Create Departments table
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(100)
);

-- Create Employees table
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    job_title VARCHAR(100),
    dept_id INT REFERENCES departments(dept_id),
    hire_date DATE
);

-- Create Projects table
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(150) NOT NULL UNIQUE,
    start_date DATE,
    deadline DATE,
    budget DECIMAL(12, 2)
);

-- Create Tasks table (with self-reference for dependencies)
CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR(200) NOT NULL,
    project_id INT REFERENCES projects(project_id),
    status VARCHAR(20) CHECK (status IN ('Not Started', 'In Progress', 'Completed', 'Blocked')),
    depends_on_task_id INT REFERENCES tasks(task_id) NULL -- For task dependencies
);

-- Create Assignments table (Junction table)
CREATE TABLE assignments (
    assignment_id SERIAL PRIMARY KEY,
    employee_id INT REFERENCES employees(employee_id),
    task_id INT REFERENCES tasks(task_id),
    date_assigned DATE DEFAULT CURRENT_DATE,
    UNIQUE(employee_id, task_id) -- An employee is assigned to a specific task only once
);

-- Insert sample data
INSERT INTO departments (dept_name, location) VALUES
('Engineering', 'Building West'),
('Marketing', 'Building East'),
('Human Resources', 'Building Central'),
('Research', 'Building West');

INSERT INTO employees (employee_name, job_title, dept_id, hire_date) VALUES
('John Smith', 'Software Engineer', 1, '2022-03-15'),
('Jane Doe', 'Project Manager', 1, '2021-06-01'),
('Peter Jones', 'Marketing Specialist', 2, '2022-09-10'),
('Emily White', 'HR Manager', 3, '2020-11-01'),
('Michael Brown', 'Researcher', 4, '2023-01-20'),
('Sarah Green', 'Software Engineer', 1, '2023-05-01'),
('David Lee', 'UI/UX Designer', 1, NULL); -- Hired, but date missing

INSERT INTO projects (project_name, start_date, deadline, budget) VALUES
('Alpha Launch', '2023-01-15', '2023-12-31', 50000.00),
('Beta Marketing Campaign', '2023-06-01', '2023-11-30', 25000.00),
('Gamma Research Initiative', '2023-08-01', '2024-07-31', 75000.00),
('Internal Tools Upgrade', '2023-09-01', NULL, 15000.00); -- No deadline yet

INSERT INTO tasks (task_name, project_id, status, depends_on_task_id) VALUES
('Design Database Schema', 1, 'Completed', NULL),           -- Task 1
('Develop Backend API', 1, 'In Progress', 1),              -- Task 2 depends on Task 1
('Develop Frontend UI', 1, 'In Progress', 1),              -- Task 3 depends on Task 1
('Create Ad Copy', 2, 'Completed', NULL),                -- Task 4
('Launch Social Media Ads', 2, 'Not Started', 4),        -- Task 5 depends on Task 4
('Initial Research Phase', 3, 'In Progress', NULL),        -- Task 6
('Setup Lab Equipment', 3, 'Not Started', NULL),           -- Task 7
('Update Jenkins Server', 4, 'In Progress', NULL);          -- Task 8
-- Task without a project (e.g., internal admin task)
INSERT INTO tasks (task_name, project_id, status, depends_on_task_id) VALUES
('Review Annual Performance', NULL, 'Not Started', NULL); -- Task 9

INSERT INTO assignments (employee_id, task_id, date_assigned) VALUES
(1, 1, '2023-01-20'), -- John, Design Schema
(1, 2, '2023-03-01'), -- John, Backend API
(7, 3, '2023-04-15'), -- David, Frontend UI
(3, 4, '2023-06-10'), -- Peter, Ad Copy
(3, 5, '2023-09-01'), -- Peter, Social Media Ads
(5, 6, '2023-08-05'), -- Michael, Initial Research
(2, 1, '2023-01-18'), -- Jane, Design Schema (PM oversight)
(6, 2, '2023-05-10'); -- Sarah, Backend API
-- Note: Emily White (HR) not assigned to project tasks.
-- Note: Task 'Setup Lab Equipment' (Task 7) and 'Update Jenkins Server' (Task 8) not assigned yet.
-- Note: Task 'Review Annual Performance' (Task 9) not assigned and no project.

Tasks for Variant 2:

List all employees and the name of the department they belong to. Include employees who might not be assigned to a department yet. Order by department name, then employee name.
Show all projects and the tasks associated with each project. Include projects that currently have no tasks defined. List Project Name and Task Name.
For every task, list its name and the name of the task it depends on (if any). If a task has no dependency, show NULL for the dependency name. (Requires self-join).
Display the names of all employees who are assigned to at least one task. List the employee name, task name, and the name of the project the task belongs to.
Find all tasks that are currently ‘In Progress’ and list the names of the employees assigned to them.
List the names of all employees in the ‘Engineering’ department along with the names of the tasks they are assigned to. If an Engineering employee is not assigned to any task, they should still appear in the list (with NULL for task name).
Show the names of projects that have at least one task with the status ‘Not Started’. List only the unique project names.
Count the number of tasks assigned to each employee. List the employee name and the count of tasks. Include employees with zero assigned tasks. Order by the count descending.
List the departments that have more than 2 employees. Show the department name and the number of employees in that department.
Display the full details for tasks that depend on the task named ‘Design Database Schema’. Include the dependent task’s name, its status, the project name it belongs to, and the name of the prerequisite task (‘Design Database Schema’).

Variant 3: Library System Database

Scenario: You are managing the database for a public library. The system needs to track books, authors, genres, library members (borrowers), and loan records.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS book_authors;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS genres;

-- Create Genres table
CREATE TABLE genres (
    genre_id SERIAL PRIMARY KEY,
    genre_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

-- Create Authors table
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(150) NOT NULL,
    birth_year INT
);

-- Create Books table (with self-reference for series)
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    isbn VARCHAR(20) UNIQUE,
    publication_year INT,
    genre_id INT REFERENCES genres(genre_id),
    previous_book_in_series_id INT REFERENCES books(book_id) NULL -- For book series
);

-- Create BookAuthors table (Junction table for Many-to-Many between Books and Authors)
CREATE TABLE book_authors (
    book_id INT REFERENCES books(book_id),
    author_id INT REFERENCES authors(author_id),
    PRIMARY KEY (book_id, author_id) -- Composite primary key
);

-- Create Members table
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100) NOT NULL,
    join_date DATE DEFAULT CURRENT_DATE,
    email VARCHAR(100) UNIQUE
);

-- Create Loans table
CREATE TABLE loans (
    loan_id SERIAL PRIMARY KEY,
    book_id INT REFERENCES books(book_id),
    member_id INT REFERENCES members(member_id),
    loan_date DATE DEFAULT CURRENT_DATE,
    due_date DATE,
    return_date DATE NULL -- NULL if the book hasn't been returned yet
);

-- Insert sample data
INSERT INTO genres (genre_name, description) VALUES
('Science Fiction', 'Fiction dealing with imaginative concepts such as futuristic science and technology.'),
('Fantasy', 'Fiction genre set in a fictional universe, often inspired by real world myth and folklore.'),
('Mystery', 'Fiction involving a mysterious death or a crime to be solved.'),
('Programming', 'Books related to computer programming languages and techniques.'),
('History', 'Books detailing past events.');

INSERT INTO authors (author_name, birth_year) VALUES
('Isaac Asimov', 1920),
('J.R.R. Tolkien', 1892),
('Agatha Christie', 1890),
('Charles Petzold', 1953),
('Ada Lovelace', 1815), -- Historical figure, included for programming context
('Yuval Noah Harari', 1976);

INSERT INTO books (title, isbn, publication_year, genre_id, previous_book_in_series_id) VALUES
('Foundation', '978-0553293357', 1951, 1, NULL),                  -- Book 1
('Foundation and Empire', '978-0553293371', 1952, 1, 1),           -- Book 2 (depends on Book 1)
('The Hobbit', '978-0547928227', 1937, 2, NULL),                   -- Book 3
('Murder on the Orient Express', '978-0062073501', 1934, 3, NULL), -- Book 4
('Code: The Hidden Language', '978-0735611313', 1999, 4, NULL),   -- Book 5
('Notes on the Analytical Engine', NULL, 1843, 4, NULL),          -- Book 6 (No ISBN)
('Sapiens: A Brief History of Humankind', '978-0062316097', 2011, 5, NULL); -- Book 7

INSERT INTO book_authors (book_id, author_id) VALUES
(1, 1), (2, 1), -- Asimov books
(3, 2),         -- Tolkien book
(4, 3),         -- Christie book
(5, 4),         -- Petzold book
(6, 5),         -- Lovelace book (historical)
(7, 6);         -- Harari book

INSERT INTO members (member_name, join_date, email) VALUES
('Alice Smith', '2023-01-10', 'alice.s@email.com'),
('Bob Johnson', '2022-11-05', 'bob.j@email.com'),
('Charlie Davis', '2023-05-20', 'charlie.d@email.com'),
('Diana Miller', '2023-09-01', 'diana.m@email.com'); -- New member, no loans yet

INSERT INTO loans (book_id, member_id, loan_date, due_date, return_date) VALUES
(1, 1, '2023-08-01', '2023-08-15', '2023-08-14'), -- Foundation, Alice (Returned)
(3, 2, '2023-08-10', '2023-08-24', '2023-08-25'), -- The Hobbit, Bob (Returned late)
(4, 1, '2023-09-05', '2023-09-19', NULL),       -- Orient Express, Alice (Currently loaned)
(5, 3, '2023-09-10', '2023-09-24', NULL),       -- Code, Charlie (Currently loaned)
(1, 3, '2023-09-15', '2023-09-29', NULL);       -- Foundation, Charlie (Currently loaned)
-- Note: Book 'Foundation and Empire', 'Notes on the Analytical Engine', 'Sapiens' not loaned yet.
-- Note: Member 'Diana Miller' has no loans.
-- Note: Multiple authors per book not used in this sample, but schema supports it.

Tasks for Variant 3:

List all books and their genres. Include books that might not have a genre assigned. Show Book Title and Genre Name.
Show all authors and the titles of the books they have written. Use the book_authors table to link them. Include authors who may not have any books listed in the books table (if any).
For every book that is part of a series (i.e., has a previous_book_in_series_id), list the book’s title and the title of the previous book in the series. (Requires self-join).
Display the names of members who currently have at least one book on loan (i.e., return_date is NULL). List the member’s name and the title of the book(s) they have borrowed.
Find all books written by ‘Isaac Asimov’. List the book titles and their publication years.
List the names of all members and the titles of the books they have borrowed, including past loans (where return_date is not NULL). If a member has never borrowed a book, they should still appear in the list (with NULL for book title). Order by member name, then loan date.
Show the titles of books that belong to the ‘Science Fiction’ genre AND were published after 1950.
Calculate the total number of times each book has been loaned out. List the book title and the total loan count. Include books that have never been loaned (show count 0). Order by the loan count descending.
List the genres that have more than 1 book associated with them. Show the genre name and the count of books in that genre.
Display the full loan details for books currently on loan (return_date is NULL): include the loan ID, member name, book title, author name(s) (handle multiple authors if necessary), loan date, and due date.

Variant 4: University Course Scheduling Database

Scenario: You are designing a database for a university to manage its course schedule, including departments, instructors, courses, classrooms, and the specific time slots classes are offered. Students also enroll in these scheduled classes.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS schedule;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS instructors;
DROP TABLE IF EXISTS classrooms;
DROP TABLE IF EXISTS departments;

-- Create Departments table
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    office_location VARCHAR(100)
);

-- Create Instructors table
CREATE TABLE instructors (
    instructor_id SERIAL PRIMARY KEY,
    instructor_name VARCHAR(100) NOT NULL,
    dept_id INT REFERENCES departments(dept_id)
);

-- Create Classrooms table
CREATE TABLE classrooms (
    classroom_id SERIAL PRIMARY KEY,
    building VARCHAR(50) NOT NULL,
    room_number VARCHAR(10) NOT NULL,
    capacity INT CHECK (capacity > 0),
    UNIQUE(building, room_number)
);

-- Create Courses table
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_code VARCHAR(10) NOT NULL UNIQUE, -- e.g., CS101
    course_title VARCHAR(150) NOT NULL,
    credits INT CHECK (credits > 0),
    dept_id INT REFERENCES departments(dept_id)
);

-- Create Schedule table (Links Course, Instructor, Classroom, Time)
CREATE TABLE schedule (
    schedule_id SERIAL PRIMARY KEY,
    course_id INT REFERENCES courses(course_id),
    instructor_id INT REFERENCES instructors(instructor_id),
    classroom_id INT REFERENCES classrooms(classroom_id),
    semester VARCHAR(20) NOT NULL, -- e.g., 'Fall 2023'
    day_of_week VARCHAR(10) CHECK (day_of_week IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')),
    start_time TIME,
    end_time TIME,
    UNIQUE(classroom_id, semester, day_of_week, start_time) -- Prevent booking same room at same time
);

-- Create Students table
CREATE TABLE students (
	student_id SERIAL PRIMARY KEY,
	student_name VARCHAR(100) NOT NULL,
	major_dept_id INT REFERENCES departments(dept_id),
	enrollment_year INT
);

-- Create Enrollments table (Junction table for Students and Scheduled Classes)
CREATE TABLE enrollments (
	enrollment_id SERIAL PRIMARY KEY,
	student_id INT REFERENCES students(student_id),
	schedule_id INT REFERENCES schedule(schedule_id),
	grade CHAR(1) CHECK (grade IN ('A', 'B', 'C', 'D', 'F', NULL)), -- Allow NULL for ongoing classes
	UNIQUE(student_id, schedule_id) -- A student enrolls in a specific scheduled class instance only once
);

-- Insert sample data
INSERT INTO departments (dept_name, office_location) VALUES
('Computer Science', 'Tech Hall'),
('Mathematics', 'Math Tower'),
('Physics', 'Science Wing'),
('Literature', 'Arts Building');

INSERT INTO instructors (instructor_name, dept_id) VALUES
('Dr. Elara Vance', 1), ('Prof. Ben Carter', 1),
('Dr. Anya Sharma', 2), ('Dr. Kenji Tanaka', 3),
('Prof. Olivia Green', 4), ('Dr. Leo Maxwell', NULL); -- Not currently assigned

INSERT INTO classrooms (building, room_number, capacity) VALUES
('Tech Hall', '101', 50), ('Tech Hall', '205', 30),
('Math Tower', '314', 40), ('Science Wing', '110 Lab', 25),
('Arts Building', 'Auditorium', 150);

INSERT INTO courses (course_code, course_title, credits, dept_id) VALUES
('CS101', 'Intro to Computing', 3, 1), ('CS305', 'Databases', 4, 1),
('MA201', 'Calculus II', 4, 2), ('PH101', 'General Physics I', 4, 3),
('LT220', 'Shakespeare', 3, 4), ('MA101', 'Calculus I', 4, 2);

INSERT INTO schedule (course_id, instructor_id, classroom_id, semester, day_of_week, start_time, end_time) VALUES
(1, 1, 1, 'Fall 2023', 'Monday', '09:00:00', '10:15:00'),    -- CS101, Vance, Tech 101
(1, 1, 1, 'Fall 2023', 'Wednesday', '09:00:00', '10:15:00'), -- CS101, Vance, Tech 101
(2, 2, 2, 'Fall 2023', 'Tuesday', '10:30:00', '12:00:00'),   -- CS305, Carter, Tech 205
(2, 2, 2, 'Fall 2023', 'Thursday', '10:30:00', '12:00:00'),  -- CS305, Carter, Tech 205
(3, 3, 3, 'Fall 2023', 'Monday', '13:00:00', '14:30:00'),   -- MA201, Sharma, Math 314
(4, 4, 4, 'Fall 2023', 'Wednesday', '11:00:00', '12:30:00'),-- PH101, Tanaka, Sci 110
(5, 5, 5, 'Fall 2023', 'Friday', '14:00:00', '16:00:00');   -- LT220, Green, Auditorium

INSERT INTO students (student_name, major_dept_id, enrollment_year) VALUES
('Michael Lee', 1, 2022), ('Sophia Chen', 2, 2021),
('Ethan Garcia', 1, 2023), ('Isabella Rossi', 4, 2022),
('Noah Kim', NULL, 2023); -- Undecided

INSERT INTO enrollments (student_id, schedule_id, grade) VALUES
(1, 1, NULL), -- Michael, CS101 Mon
(1, 2, NULL), -- Michael, CS101 Wed
(1, 3, NULL), -- Michael, CS305 Tue
(2, 5, NULL), -- Sophia, MA201 Mon
(3, 1, NULL), -- Ethan, CS101 Mon
(4, 7, 'A'), -- Isabella, LT220 Fri (Assuming completed early or midterm grade)
(2, 3, NULL); -- Sophia, CS305 Tue (cross-major enrollment)

Tasks for Variant 4:

List all scheduled classes for the ‘Fall 2023’ semester. Show the course title, instructor name, building, room number, day of the week, start time, and end time. Order by course title, then day of the week.
Find all courses offered by the ‘Computer Science’ department. Include the course code, title, and credits.
List all instructors and the department they belong to. Include instructors who are not currently assigned to a department. Order by department name (NULLs last), then instructor name.
Show the names of students enrolled in the ‘CS305’ (Databases) course for the ‘Fall 2023’ semester. Hint: You’ll need to join students, enrollments, schedule, and courses.
List all classrooms in ‘Tech Hall’ and their capacities. Order by room number.
Find the names of instructors who are teaching more than one distinct course in the ‘Fall 2023’ semester. (Consider course_id, not schedule_id).
Display the schedule for ‘Dr. Elara Vance’ for ‘Fall 2023’. Show the course title, day, start time, end time, building, and room number.
Count the number of courses offered by each department. List the department name and the course count. Include departments that offer zero courses (if any exist). Order by count descending.
Find all students majoring in ‘Computer Science’ and list the titles of the courses they are currently enrolled in for ‘Fall 2023’ (where grade is NULL).
List all classrooms with a capacity greater than 35, along with the course title and instructor name for any classes scheduled in them during ‘Fall 2023’. Include large classrooms even if they have no classes scheduled. Order by building, room number.

Variant 5: E-commerce Product Reviews Database

Scenario: You are managing the database for an online store that allows users to review products. The system tracks users, products, categories (with subcategories), and the reviews submitted by users.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS users;

-- Create Users table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Create Categories table (with self-reference for subcategories)
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    parent_category_id INT REFERENCES categories(category_id) NULL -- For subcategories
);

-- Create Products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) CHECK (price > 0),
    category_id INT REFERENCES categories(category_id),
    added_date DATE DEFAULT CURRENT_DATE
);

-- Create Reviews table
CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id) ON DELETE CASCADE, -- If product is deleted, delete reviews
    user_id INT REFERENCES users(user_id) ON DELETE SET NULL, -- If user is deleted, keep review but remove user link
    rating INT CHECK (rating >= 1 AND rating <= 5),
    review_text TEXT,
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO users (username, email, registration_date) VALUES
('AliceR', 'alice.r@example.com', '2023-02-10'),
('BobS', 'bob.s@example.com', '2023-03-15'),
('CharlieT', 'charlie.t@example.com', '2023-04-20'),
('DianaP', 'diana.p@example.com', '2023-05-25'); -- User with no reviews yet

INSERT INTO categories (category_name, parent_category_id) VALUES
('Electronics', NULL), ('Books', NULL),
('Computers & Accessories', 1), ('Fiction Books', 2),
('Keyboards', 3), ('Science Fiction', 4);

INSERT INTO products (product_name, description, price, category_id) VALUES
('Mechanical Keyboard K7', 'RGB Backlit Mechanical Keyboard', 79.99, 5),
('Wireless Ergonomic Mouse', 'Comfortable mouse for long use', 45.50, 3),
('Dune by Frank Herbert', 'Classic Science Fiction Novel', 15.99, 6),
('The Three-Body Problem', 'Hard Sci-Fi by Cixin Liu', 18.00, 6),
('Laptop Stand', 'Adjustable aluminum laptop stand', 29.99, 3),
('Webcam HD 1080p', 'High definition webcam for streaming', 55.00, 1); -- Category 'Electronics'

INSERT INTO reviews (product_id, user_id, rating, review_text, review_date) VALUES
(1, 1, 5, 'Amazing keyboard, great feel!', '2023-06-01 10:00:00'),
(1, 2, 4, 'Very good, but a bit loud.', '2023-06-05 14:20:00'),
(2, 1, 4, 'Comfortable mouse, tracks well.', '2023-06-02 11:30:00'),
(3, 3, 5, 'A timeless classic!', '2023-07-10 09:00:00'),
(4, 1, 5, 'Mind-bending science fiction!', '2023-07-15 16:45:00'),
(4, 3, 4, 'Great story, complex ideas.', '2023-07-18 12:10:00'),
(5, 2, 3, 'Stand is okay, a bit wobbly.', '2023-08-01 15:00:00');
-- Note: Product 'Webcam HD 1080p' has no reviews yet.
-- Note: User 'DianaP' has not written any reviews.
-- Note: Category 'Fiction Books' has no direct products, only subcategory 'Science Fiction'.

Tasks for Variant 5:

List all products and their category names. Include products that might not be assigned to a category. Order by product name.
Show all reviews, including the product name, username of the reviewer, rating, and the review text. Order by review date descending.
List all categories and their parent category names. For top-level categories, the parent name should be NULL. (Requires self-join).
Find the average rating for each product that has at least one review. List the product name and its average rating. Order by average rating descending.
List the usernames of users who have given a rating of 5 stars to any product. Show the username and the name of the product they rated 5 stars.
Display all products belonging to the ‘Computers & Accessories’ category or any of its subcategories (e.g., ‘Keyboards’). Show the product name and its price. (Requires joining categories to itself and then to products).
Find all reviews written by the user ‘AliceR’. Include the review ID, product name, rating, and review text.
Count the number of reviews submitted by each user. List the username and the total number of reviews. Include users who have submitted zero reviews. Order by the count descending.
List the categories that contain products with an average rating higher than 4.0. Show the category name and the calculated average rating for products within that category. Hint: This might involve subqueries or joining aggregated results.
Show the names of products that have not received any reviews yet.

Variant 6: Hospital Patient Records Database

Scenario: You are managing a simplified database for a hospital or clinic. The system tracks patients, doctors, medical specialties, appointments, and diagnoses made during appointments.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS diagnoses;
DROP TABLE IF EXISTS appointments;
DROP TABLE IF EXISTS patients;
DROP TABLE IF EXISTS doctors;
DROP TABLE IF EXISTS specialties;

-- Create Specialties table (Medical Departments)
CREATE TABLE specialties (
    specialty_id SERIAL PRIMARY KEY,
    specialty_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

-- Create Doctors table
CREATE TABLE doctors (
    doctor_id SERIAL PRIMARY KEY,
    doctor_name VARCHAR(100) NOT NULL,
    specialty_id INT REFERENCES specialties(specialty_id),
    phone_number VARCHAR(20)
);

-- Create Patients table
CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    patient_name VARCHAR(100) NOT NULL,
    date_of_birth DATE,
    gender VARCHAR(10) CHECK (gender IN ('Male', 'Female', 'Other', 'Unknown'))
);

-- Create Appointments table
CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INT REFERENCES patients(patient_id),
    doctor_id INT REFERENCES doctors(doctor_id),
    appointment_date TIMESTAMP NOT NULL, -- Includes date and time
    room_number VARCHAR(10),
    reason_for_visit TEXT
);

-- Create Diagnoses table (Linking conditions to appointments)
CREATE TABLE diagnoses (
    diagnosis_id SERIAL PRIMARY KEY,
    appointment_id INT REFERENCES appointments(appointment_id) UNIQUE, -- Assume one primary diagnosis per appointment for simplicity
    condition_name VARCHAR(150) NOT NULL,
    diagnosis_notes TEXT
);

-- Insert sample data
INSERT INTO specialties (specialty_name, description) VALUES
('Cardiology', 'Deals with disorders of the heart.'),
('Neurology', 'Deals with disorders of the nervous system.'),
('Pediatrics', 'Branch of medicine dealing with children and their diseases.'),
('General Practice', 'Provides routine health care.');

INSERT INTO doctors (doctor_name, specialty_id, phone_number) VALUES
('Dr. Evelyn Reed', 1, '555-1111'),
('Dr. Marcus Chen', 2, '555-2222'),
('Dr. Sofia Ramirez', 3, '555-3333'),
('Dr. Ben Carter', 4, '555-4444'),
('Dr. Chloe Jenkins', 1, '555-1112'); -- Another Cardiologist

INSERT INTO patients (patient_name, date_of_birth, gender) VALUES
('John Doe', '1985-06-15', 'Male'),
('Jane Smith', '1992-11-20', 'Female'),
('Michael Lee', '2018-03-10', 'Male'),
('Emily White', '1970-09-01', 'Female'),
('David Brown', '1995-01-25', 'Male'); -- Patient with no appointments yet

INSERT INTO appointments (patient_id, doctor_id, appointment_date, room_number, reason_for_visit) VALUES
(1, 1, '2023-09-01 10:00:00', 'C101', 'Chest pain'),
(2, 4, '2023-09-05 11:30:00', 'G205', 'Annual check-up'),
(3, 3, '2023-09-10 09:15:00', 'P314', 'Fever and cough'),
(1, 2, '2023-09-12 14:00:00', 'N110', 'Headaches'),
(4, 1, '2023-09-15 10:30:00', 'C102', 'Follow-up appointment'),
(2, 4, '2023-10-02 11:00:00', 'G205', 'Flu shot'); -- Upcoming appointment

INSERT INTO diagnoses (appointment_id, condition_name, diagnosis_notes) VALUES
(1, 'Angina Pectoris', 'Prescribed medication, follow-up needed.'),
(2, 'Healthy', 'Routine check-up, all clear.'),
(3, 'Viral Infection', 'Rest and fluids recommended.'),
(4, 'Migraine', 'Advised stress reduction techniques.'),
(5, 'Stable Angina', 'Continue medication, monitor blood pressure.');
-- Note: Appointment 6 (Flu shot) has no diagnosis entry yet.
-- Note: Patient David Brown has no appointments.
-- Note: Dr. Chloe Jenkins has no appointments in this sample.

Tasks for Variant 6:

List all doctors and the name of their specialty. Include doctors who might not be assigned to a specialty (if possible according to schema). Order by specialty name, then doctor name.
Show all appointments, including the patient’s name, doctor’s name, appointment date/time, and reason for visit. Order by appointment date.
Find all patients born after January 1st, 1990. List their name and date of birth.
Display the details of all appointments scheduled with doctors in the ‘Cardiology’ specialty. Include patient name, doctor name, appointment date, and reason for visit.
List the names of all patients who had an appointment where the diagnosed condition was ‘Migraine’.
Show all patients and the date of their most recent appointment. Include patients who have never had an appointment (show NULL for the date). Hint: Requires a LEFT JOIN and aggregation (MAX) grouped by patient.
Find all appointments scheduled on or after ‘2023-09-10’. List the patient name, doctor name, and the full appointment date/time.
Count the number of appointments handled by each doctor. List the doctor’s name and the appointment count. Include doctors with zero appointments. Order by count descending.
List the specialties that have more than one doctor assigned to them. Show the specialty name and the count of doctors.
Display the full details for appointments that have resulted in a diagnosis. Include the patient’s name, doctor’s name, specialty name, appointment date, condition name, and diagnosis notes. Order by appointment date.

Variant 7: Flight Booking System Database

Scenario: You are managing the database for a flight booking system. The database tracks airlines, airports, flights between airports, passengers, and their bookings on specific flights.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS flights;
DROP TABLE IF EXISTS passengers;
DROP TABLE IF EXISTS airlines;
DROP TABLE IF EXISTS airports;

-- Create Airports table
CREATE TABLE airports (
    airport_code CHAR(3) PRIMARY KEY, -- e.g., 'JFK', 'LAX'
    airport_name VARCHAR(100) NOT NULL,
    city VARCHAR(100),
    country VARCHAR(100)
);

-- Create Airlines table
CREATE TABLE airlines (
    airline_id SERIAL PRIMARY KEY,
    airline_name VARCHAR(100) NOT NULL UNIQUE,
    headquarters_country VARCHAR(100)
);

-- Create Flights table (references Airports twice and Airlines)
CREATE TABLE flights (
    flight_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) NOT NULL,
    airline_id INT REFERENCES airlines(airline_id),
    departure_airport CHAR(3) REFERENCES airports(airport_code),
    arrival_airport CHAR(3) REFERENCES airports(airport_code),
    departure_time TIMESTAMP,
    arrival_time TIMESTAMP,
    base_price DECIMAL(10, 2) CHECK (base_price > 0)
);

-- Create Passengers table
CREATE TABLE passengers (
    passenger_id SERIAL PRIMARY KEY,
    passenger_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE
);

-- Create Bookings table (Junction table)
CREATE TABLE bookings (
    booking_id SERIAL PRIMARY KEY,
    flight_id INT REFERENCES flights(flight_id),
    passenger_id INT REFERENCES passengers(passenger_id),
    booking_date DATE DEFAULT CURRENT_DATE,
    seat_number VARCHAR(5), -- e.g., '12A', '3F'
    status VARCHAR(20) CHECK (status IN ('Confirmed', 'Cancelled', 'Pending')),
    UNIQUE(flight_id, passenger_id) -- Passenger booked once per flight
);

-- Insert sample data
INSERT INTO airports (airport_code, airport_name, city, country) VALUES
('JFK', 'John F. Kennedy International Airport', 'New York', 'USA'),
('LAX', 'Los Angeles International Airport', 'Los Angeles', 'USA'),
('LHR', 'London Heathrow Airport', 'London', 'UK'),
('CDG', 'Charles de Gaulle Airport', 'Paris', 'France'),
('HND', 'Tokyo Haneda Airport', 'Tokyo', 'Japan');

INSERT INTO airlines (airline_name, headquarters_country) VALUES
('Global Airways', 'USA'),
('EuroFly', 'France'),
('Pacific Wings', 'Japan'),
('Transatlantic Express', NULL); -- HQ country not specified

INSERT INTO flights (flight_number, airline_id, departure_airport, arrival_airport, departure_time, arrival_time, base_price) VALUES
('GA101', 1, 'JFK', 'LAX', '2024-01-15 08:00:00', '2024-01-15 11:30:00', 350.00),
('GA102', 1, 'LAX', 'JFK', '2024-01-15 13:00:00', '2024-01-15 21:30:00', 360.00),
('EF201', 2, 'CDG', 'LHR', '2024-01-16 10:00:00', '2024-01-16 10:30:00', 120.00),
('EF202', 2, 'LHR', 'CDG', '2024-01-16 14:00:00', '2024-01-16 16:30:00', 110.00),
('PW301', 3, 'HND', 'LAX', '2024-01-17 22:00:00', '2024-01-17 15:00:00', 850.00), -- Crosses date line
('GA105', 1, 'JFK', 'LHR', '2024-01-18 19:00:00', '2024-01-19 07:00:00', 620.00); -- Overnight flight
-- Flight without airline assigned (maybe code-share placeholder)
INSERT INTO flights (flight_number, airline_id, departure_airport, arrival_airport, departure_time, arrival_time, base_price) VALUES
('XX999', NULL, 'LAX', 'CDG', '2024-01-20 15:00:00', '2024-01-21 11:00:00', 700.00); -- Flight 7

INSERT INTO passengers (passenger_name, email, date_of_birth) VALUES
('Alice Green', 'alice.g@mail.com', '1990-05-15'),
('Bob White', 'bob.w@mail.com', '1985-11-22'),
('Charlie Black', 'charlie.b@mail.com', '1998-02-10'),
('Diana Blue', 'diana.b@mail.com', '2001-07-30'); -- New passenger, no bookings yet

INSERT INTO bookings (flight_id, passenger_id, booking_date, seat_number, status) VALUES
(1, 1, '2023-12-01', '10A', 'Confirmed'), -- Alice on GA101
(2, 1, '2023-12-01', '12B', 'Confirmed'), -- Alice on GA102
(3, 2, '2023-12-05', '5C', 'Confirmed'),  -- Bob on EF201
(5, 3, '2023-12-10', '22F', 'Confirmed'), -- Charlie on PW301
(1, 2, '2023-12-15', '11A', 'Pending');   -- Bob on GA101 (pending)
-- Note: Flight GA105 (JFK-LHR) has no bookings yet.
-- Note: Flight XX999 (LAX-CDG) has no bookings yet.
-- Note: Passenger Diana Blue has no bookings.
-- Note: Airline Transatlantic Express has no flights listed yet.

Tasks for Variant 7:

List all confirmed bookings, showing the passenger’s name, flight number, airline name, departure airport name, and arrival airport name.
Show all flights originating from ‘John F. Kennedy International Airport’ (JFK). Include the flight number, airline name, destination airport name, and departure time. Order by departure time.
List all airlines and the number of flights associated with each airline in the flights table. Include airlines that may have zero flights listed. Order by the number of flights descending.
Display the names of passengers who have booked flights on ‘Global Airways’. Ensure each passenger name appears only once.
Find all flights departing from the ‘USA’ and arriving in the ‘UK’. List the flight number, departure city, and arrival city. (Requires joining flights with airports twice).
Show all passengers and the total number of bookings they have (regardless of status). Include passengers who have made zero bookings. Order by the number of bookings descending.
List the flight number, departure airport name, and arrival airport name for all flights. Also include the name of the airline operating the flight. Include flights even if their airline information is missing (NULL).
Find airports that have more than one flight departing from them. List the airport name, city, and the count of departing flights. Order by the count descending.
List all passengers who have a ‘Pending’ booking status for any flight. Include the passenger’s name and the flight number of the pending booking.
Display the details of flights that have a base price greater than $500. Include the flight number, airline name, departure airport name, arrival airport name, and the base price.

Variant 8: Music Streaming Service Database

Scenario: You are designing the database for a music streaming service. It needs to track artists, albums, tracks within albums, users (listeners), playlists created by users, and which tracks are in which playlists.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS playlist_tracks;
DROP TABLE IF EXISTS playlists;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS tracks;
DROP TABLE IF EXISTS albums;
DROP TABLE IF EXISTS artists;

-- Create Artists table
CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    artist_name VARCHAR(150) NOT NULL UNIQUE,
    country VARCHAR(100)
);

-- Create Albums table
CREATE TABLE albums (
    album_id SERIAL PRIMARY KEY,
    album_title VARCHAR(200) NOT NULL,
    artist_id INT REFERENCES artists(artist_id),
    release_year INT,
    genre VARCHAR(50)
);

-- Create Tracks table
CREATE TABLE tracks (
    track_id SERIAL PRIMARY KEY,
    track_title VARCHAR(200) NOT NULL,
    album_id INT REFERENCES albums(album_id),
    duration_seconds INT CHECK (duration_seconds > 0),
    composer VARCHAR(150) NULL -- Allow NULL if composer unknown/not applicable
);

-- Create Users table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    join_date DATE DEFAULT CURRENT_DATE
);

-- Create Playlists table
CREATE TABLE playlists (
    playlist_id SERIAL PRIMARY KEY,
    playlist_name VARCHAR(100) NOT NULL,
    user_id INT REFERENCES users(user_id), -- Owner of the playlist
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_public BOOLEAN DEFAULT TRUE
);

-- Create PlaylistTracks table (Junction table for Many-to-Many between Playlists and Tracks)
CREATE TABLE playlist_tracks (
    playlist_id INT REFERENCES playlists(playlist_id),
    track_id INT REFERENCES tracks(track_id),
    date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (playlist_id, track_id) -- Composite primary key
);

-- Insert sample data
INSERT INTO artists (artist_name, country) VALUES
('The Quantizers', 'USA'),
('Syntax Sisters', 'UK'),
('Data Miners', 'Canada'),
('Algorithm Al', NULL); -- Country unknown

INSERT INTO albums (album_title, artist_id, release_year, genre) VALUES
('Boolean Boogie', 1, 2020, 'Electro-Rock'),
('Abstract Harmony', 2, 2021, 'Synth-Pop'),
('Data Streams', 3, 2022, 'Ambient Techno'),
('Recursive Rhythms', 1, 2023, 'Electro-Rock'),
('Solo Cipher', 4, 2022, 'Experimental'); -- Album by Algorithm Al
-- Album with no artist linked (maybe a compilation)
INSERT INTO albums (album_title, artist_id, release_year, genre) VALUES
('Various Vibes', NULL, 2023, 'Compilation'); -- Album 6

INSERT INTO tracks (track_title, album_id, duration_seconds, composer) VALUES
('Binary Beat', 1, 245, 'The Quantizers'),
('Logic Loop', 1, 180, 'The Quantizers'),
('Null Pointer Pop', 2, 210, 'Syntax Sisters'),
('K-Means Groove', 3, 360, 'Data Miners'),
('Forest Flow', 3, 420, 'Data Miners'),
('Callback Funk', 4, 200, 'The Quantizers'), -- From Recursive Rhythms
('Cipher Suite Swing', 5, 190, 'Algorithm Al'),
('Compilation Track 1', 6, 180, NULL), -- Track 8
('Compilation Track 2', 6, 220, NULL); -- Track 9
-- Track without album
INSERT INTO tracks (track_title, album_id, duration_seconds, composer) VALUES
('Standalone Single', NULL, 150, 'Syntax Sisters'); -- Track 10

INSERT INTO users (username, email, join_date) VALUES
('coder_chris', 'chris@example.com', '2022-01-15'),
('music_megan', 'megan@example.com', '2022-06-20'),
('listener_liam', 'liam@example.com', '2023-03-01'),
('sampler_sam', 'sam@example.com', '2023-08-10'); -- New user, no playlists yet

INSERT INTO playlists (playlist_name, user_id, is_public) VALUES
('Coding Focus', 1, true),
('Synth Waves', 2, true),
('Techno Chill', 1, false),
('Workout Mix', 2, true);

INSERT INTO playlist_tracks (playlist_id, track_id) VALUES
(1, 1), (1, 4), (1, 5), -- Coding Focus: Binary Beat, K-Means Groove, Forest Flow
(2, 3), (2, 7),         -- Synth Waves: Null Pointer Pop, Cipher Suite Swing
(3, 4), (3, 5),         -- Techno Chill: K-Means Groove, Forest Flow
(4, 1), (4, 6);         -- Workout Mix: Binary Beat, Callback Funk
-- Note: Track 'Logic Loop' (Track 2), 'Compilation Track 1/2' (8,9) and 'Standalone Single' (10) not in any playlists yet.
-- Note: User 'listener_liam' and 'sampler_sam' have no playlists.

Tasks for Variant 8:

List all tracks, including their title, the title of the album they belong to, and the name of the artist who performed the album. Include tracks that may not be linked to an album.
Show the names of all users and the names of the playlists they have created. Include users who have not created any playlists. Order by username, then playlist name.
List all artists and the titles of the albums they have released. Include artists who may not have any albums listed.
Display the track titles and their durations (in seconds) for all tracks found in the ‘Coding Focus’ playlist.
Find all albums released by ‘The Quantizers’. List the album title and release year.
List the names of all tracks that are part of the ‘Ambient Techno’ genre. Include the track title, album title, and artist name.
Show the names of users who have created playlists containing the track ‘K-Means Groove’. Ensure each username appears only once.
Calculate the total number of tracks on each album. List the album title and the track count. Include albums that might have zero tracks (if possible by schema). Order by album title.
List the artists who have released albums in more than one distinct genre. Show the artist name and the count of distinct genres.
Display the details of tracks included in playlists created by the user ‘coder_chris’. Show the playlist name, track title, album title, and artist name.

Variant 9: Real Estate Listing Service Database

Scenario: You are managing the database for a real estate listing service. The system tracks properties for sale, real estate agents, clients (potential buyers), property types, viewings of properties by clients, and offers made by clients on properties.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS offers;
DROP TABLE IF EXISTS viewings;
DROP TABLE IF EXISTS properties;
DROP TABLE IF EXISTS property_types;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS agents;


-- Create Agents table
CREATE TABLE agents (
    agent_id SERIAL PRIMARY KEY,
    agent_name VARCHAR(100) NOT NULL,
    agency_name VARCHAR(100),
    phone VARCHAR(20) UNIQUE
);

-- Create Clients table
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    client_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    max_budget DECIMAL(12, 2) NULL
);

-- Create PropertyTypes table
CREATE TABLE property_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL UNIQUE, -- e.g., 'Apartment', 'House', 'Condo', 'Land'
    description TEXT
);

-- Create Properties table
CREATE TABLE properties (
    property_id SERIAL PRIMARY KEY,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(100),
    zip_code VARCHAR(10),
    listing_price DECIMAL(12, 2) CHECK (listing_price > 0),
    bedrooms INT CHECK (bedrooms >= 0),
    bathrooms DECIMAL(3, 1) CHECK (bathrooms >= 0), -- e.g., 2.5 bathrooms
    square_feet INT CHECK (square_feet > 0),
    type_id INT REFERENCES property_types(type_id),
    agent_id INT REFERENCES agents(agent_id), -- Listing agent
    listing_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) CHECK (status IN ('For Sale', 'Pending', 'Sold', 'Withdrawn')) DEFAULT 'For Sale'
);

-- Create Viewings table (Junction table: Client views Property)
CREATE TABLE viewings (
    viewing_id SERIAL PRIMARY KEY,
    property_id INT REFERENCES properties(property_id),
    client_id INT REFERENCES clients(client_id),
    viewing_date TIMESTAMP,
    agent_present_id INT REFERENCES agents(agent_id) NULL, -- Agent who conducted the viewing
    feedback TEXT NULL
);

-- Create Offers table (Client makes an offer on Property)
CREATE TABLE offers (
    offer_id SERIAL PRIMARY KEY,
    property_id INT REFERENCES properties(property_id),
    client_id INT REFERENCES clients(client_id),
    offer_price DECIMAL(12, 2) CHECK (offer_price > 0),
    offer_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) CHECK (status IN ('Submitted', 'Accepted', 'Rejected', 'Withdrawn')) DEFAULT 'Submitted',
    UNIQUE(property_id, client_id, offer_date) -- Prevent identical offers on same day
);

-- Insert sample data
INSERT INTO agents (agent_name, agency_name, phone) VALUES
('Alice Realty', 'Premier Homes', '555-0101'),
('Bob Estates', 'City Properties', '555-0102'),
('Charlie Sales', 'Premier Homes', '555-0103'),
('Diana Agent', NULL, '555-0104'); -- Independent agent

INSERT INTO clients (client_name, email, max_budget) VALUES
('John Buyer', 'john.b@mail.net', 500000.00),
('Jane Seeker', 'jane.s@mail.net', 800000.00),
('Peter Looker', 'peter.l@mail.net', 350000.00),
('Mary Interested', 'mary.i@mail.net', NULL); -- Budget not specified

INSERT INTO property_types (type_name, description) VALUES
('House', 'Single-family detached home'),
('Apartment', 'Unit within a larger residential building'),
('Condo', 'Individually owned unit in a multi-unit complex with shared amenities'),
('Land', 'Undeveloped plot of land');

INSERT INTO properties (address, city, zip_code, listing_price, bedrooms, bathrooms, square_feet, type_id, agent_id, listing_date, status) VALUES
('123 Oak St', 'Maplewood', '12345', 450000.00, 3, 2.0, 1800, 1, 1, '2023-08-01', 'For Sale'), -- House by Alice
('456 Pine Ave, Unit 10', 'Maplewood', '12345', 320000.00, 2, 1.5, 1100, 3, 2, '2023-08-15', 'Pending'), -- Condo by Bob
('789 Elm Rd', 'Oakville', '67890', 750000.00, 4, 3.0, 2500, 1, 1, '2023-09-01', 'For Sale'), -- House by Alice
('101 Main St, Apt 5B', 'Maplewood', '12346', 280000.00, 1, 1.0, 750, 2, 3, '2023-09-10', 'For Sale'), -- Apartment by Charlie
('Lot 5, River View', 'Oakville', '67891', 150000.00, 0, 0.0, 43560, 4, 2, '2023-09-20', 'For Sale'); -- Land by Bob
-- Property without an agent (FSBO placeholder?)
INSERT INTO properties (address, city, zip_code, listing_price, bedrooms, bathrooms, square_feet, type_id, agent_id, listing_date, status) VALUES
('999 Owner Ln', 'Maplewood', '12347', 400000.00, 3, 2.0, 1600, 1, NULL, '2023-10-01', 'For Sale'); -- Property 6

INSERT INTO viewings (property_id, client_id, viewing_date, agent_present_id, feedback) VALUES
(1, 1, '2023-08-10 14:00:00', 1, 'Nice yard, kitchen needs update.'), -- John views 123 Oak, Alice present
(2, 1, '2023-08-20 11:00:00', 2, 'Good location, a bit small.'), -- John views 456 Pine, Bob present
(1, 2, '2023-08-12 16:00:00', 1, 'Loved the layout!'), -- Jane views 123 Oak, Alice present
(3, 2, '2023-09-05 10:00:00', 1, 'Spacious, great potential.'), -- Jane views 789 Elm, Alice present
(4, 3, '2023-09-15 15:00:00', 3, NULL); -- Peter views 101 Main, Charlie present
-- Note: Property 5 (Land) and Property 6 (Owner Ln) have no viewings yet.
-- Note: Client Mary Interested has no viewings.

INSERT INTO offers (property_id, client_id, offer_price, offer_date, status) VALUES
(1, 2, 445000.00, '2023-08-15', 'Submitted'), -- Jane offers on 123 Oak
(2, 1, 315000.00, '2023-08-22', 'Accepted'), -- John offers on 456 Pine (leading to Pending status)
(1, 1, 450000.00, '2023-08-16', 'Rejected'), -- John offers on 123 Oak (rejected)
(3, 2, 740000.00, '2023-09-10', 'Submitted'); -- Jane offers on 789 Elm
-- Note: No offers on properties 4, 5, 6 yet.
-- Note: Client Peter Looker and Mary Interested made no offers.

Tasks for Variant 9:

List all properties currently ‘For Sale’, showing the address, city, listing price, agent’s name, and the property type name.
Show the names of clients who have viewed properties listed by ‘Alice Realty’. Include the client’s name and the address of the property they viewed. Ensure client names are distinct.
List all agents and the number of properties they currently have listed (regardless of status). Include agents who may have zero properties listed. Order by the number of properties descending.
Display the details of all ‘Submitted’ offers. Include the property address, client name, offer price, and offer date.
Find all properties (address, city) that are of type ‘House’ and have a listing price below $500,000.
List all clients and the total number of viewings they have attended. Include clients who have attended zero viewings. Order by the number of viewings descending.
Show all properties and their listing agent’s name and agency. Include properties that might not have an agent assigned (listing agent is NULL).
Find agents who work for the ‘Premier Homes’ agency and list the addresses of the properties they are currently listing as ‘For Sale’.
List property types that have more than 1 property listed in the database (regardless of status). Show the property type name and the count of properties.
Display the viewing history for the property at ‘123 Oak St’. Show the client name, viewing date, and any feedback provided. Order by viewing date.

Variant 10: Restaurant Ordering System

Scenario: You are managing the database for a restaurant. The system tracks customers, menu items, item categories (like Appetizer, Main Course, Dessert, Drink), ingredients, orders placed by customers, and the specific items included in each order.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_item_ingredients;
DROP TABLE IF EXISTS ingredients;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;

-- Create Categories table (with self-reference for subcategories)
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    parent_category_id INT REFERENCES categories(category_id) NULL -- e.g., 'Hot Appetizers' under 'Appetizers'
);

-- Create MenuItems table
CREATE TABLE menu_items (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(150) NOT NULL,
    description TEXT,
    price DECIMAL(8, 2) CHECK (price >= 0),
    category_id INT REFERENCES categories(category_id),
    is_vegetarian BOOLEAN DEFAULT FALSE
);

-- Create Ingredients table
CREATE TABLE ingredients (
    ingredient_id SERIAL PRIMARY KEY,
    ingredient_name VARCHAR(100) NOT NULL UNIQUE,
    is_allergen BOOLEAN DEFAULT FALSE
);

-- Create MenuItemIngredients table (Junction table for Many-to-Many)
CREATE TABLE menu_item_ingredients (
    item_id INT REFERENCES menu_items(item_id),
    ingredient_id INT REFERENCES ingredients(ingredient_id),
    PRIMARY KEY (item_id, ingredient_id)
);

-- Create Customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) UNIQUE,
    first_visit_date DATE DEFAULT CURRENT_DATE
);

-- Create Orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id), -- Can be NULL for walk-in/non-registered
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    order_type VARCHAR(10) CHECK (order_type IN ('Dine-in', 'Takeout')),
    total_amount DECIMAL(10, 2) -- Calculated later or via trigger/application logic
);

-- Create OrderItems table (Junction table)
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    item_id INT REFERENCES menu_items(item_id),
    quantity INT CHECK (quantity > 0),
    price_per_item DECIMAL(8, 2) -- Price at the time of order
);

-- Insert sample data
INSERT INTO categories (category_name, parent_category_id) VALUES
('Appetizers', NULL), ('Main Courses', NULL), ('Desserts', NULL), ('Drinks', NULL),
('Hot Appetizers', 1), ('Salads', 1); -- Subcategories of Appetizers

INSERT INTO menu_items (item_name, description, price, category_id, is_vegetarian) VALUES
('Spring Rolls', 'Crispy fried rolls with vegetable filling', 6.50, 5, TRUE), -- Hot Appetizer
('Caesar Salad', 'Romaine lettuce, croutons, parmesan cheese, Caesar dressing', 8.00, 6, TRUE), -- Salad
('Grilled Salmon', 'Salmon fillet grilled with lemon butter sauce', 18.50, 2, FALSE), -- Main Course
('Spaghetti Carbonara', 'Pasta with eggs, cheese, pancetta, and pepper', 14.00, 2, FALSE), -- Main Course
('Cheesecake', 'Classic New York style cheesecake', 7.00, 3, TRUE), -- Dessert
('Mineral Water', 'Bottled sparkling water', 2.50, 4, TRUE), -- Drink
('Chicken Caesar Salad', 'Caesar salad with grilled chicken', 11.00, 6, FALSE); -- Salad

INSERT INTO ingredients (ingredient_name, is_allergen) VALUES
('Spring Roll Wrapper', FALSE), ('Cabbage', FALSE), ('Carrot', FALSE), ('Romaine Lettuce', FALSE), ('Croutons', TRUE), -- Gluten
('Parmesan Cheese', TRUE), -- Dairy
('Caesar Dressing', TRUE), -- Egg, Dairy
('Salmon Fillet', TRUE), -- Fish
('Lemon', FALSE), ('Butter', TRUE), -- Dairy
('Spaghetti Pasta', TRUE), -- Gluten
('Eggs', TRUE), -- Egg
('Pancetta', FALSE), ('Black Pepper', FALSE), ('Cream Cheese', TRUE), -- Dairy
('Sugar', FALSE), ('Graham Cracker Crust', TRUE), -- Gluten
('Chicken Breast', FALSE), ('Sparkling Water', FALSE);

INSERT INTO menu_item_ingredients (item_id, ingredient_id) VALUES
(1, 1), (1, 2), (1, 3), -- Spring Rolls: Wrapper, Cabbage, Carrot
(2, 4), (2, 5), (2, 6), (2, 7), -- Caesar Salad: Lettuce, Croutons, Parmesan, Dressing
(3, 8), (3, 9), (3, 10), -- Grilled Salmon: Salmon, Lemon, Butter
(4, 11), (4, 12), (4, 6), (4, 13), (4, 14), -- Carbonara: Pasta, Eggs, Parmesan, Pancetta, Pepper
(5, 15), (5, 16), (5, 17), -- Cheesecake: Cream Cheese, Sugar, Crust
(7, 4), (7, 5), (7, 6), (7, 7), (7, 18); -- Chicken Caesar: Lettuce, Croutons, Parmesan, Dressing, Chicken

INSERT INTO customers (customer_name, phone_number, first_visit_date) VALUES
('Alice Green', '555-1111', '2023-02-15'),
('Bob White', '555-2222', '2023-03-20'),
('Charlie Black', NULL, '2023-09-01'); -- Phone number optional

INSERT INTO orders (customer_id, order_time, order_type) VALUES
(1, '2023-10-20 19:30:00', 'Dine-in'),
(2, '2023-10-21 12:15:00', 'Takeout'),
(1, '2023-10-22 20:00:00', 'Dine-in'),
(NULL, '2023-10-22 13:00:00', 'Takeout'); -- Walk-in customer

INSERT INTO order_items (order_id, item_id, quantity, price_per_item) VALUES
(1, 1, 1, 6.50), (1, 3, 1, 18.50), (1, 6, 2, 2.50), -- Alice: Spring Rolls, Salmon, 2 Waters
(2, 4, 1, 14.00), (2, 6, 1, 2.50), -- Bob: Carbonara, Water
(3, 7, 1, 11.00), (3, 5, 1, 7.00), -- Alice: Chicken Caesar, Cheesecake
(4, 1, 2, 6.50); -- Walk-in: 2 Spring Rolls
-- Note: Spaghetti Carbonara (Item 4) was ordered, Caesar Salad (Item 2) was not.

Tasks for Variant 10:

List all menu items, their prices, and the name of their category. Include items that might not belong to any category (if possible). Order by category name, then item name.
Show all categories and their parent category names. For top-level categories, the parent name should be NULL. (Requires self-join on categories).
Display the names of customers who placed orders of type ‘Dine-in’. List the customer name, order ID, and order time.
List all menu items (name and price) that are vegetarian (is_vegetarian = TRUE).
Find all menu items (list their names) that contain the ingredient ‘Parmesan Cheese’. (Requires joining menu_items, menu_item_ingredients, and ingredients).
Show the names of menu items that belong to the ‘Appetizers’ category or any of its subcategories (e.g., ‘Hot Appetizers’). (Requires joining categories to itself and then to menu_items).
List the names of customers who ordered the ‘Grilled Salmon’.
Calculate the total quantity of each menu item sold across all orders. List the item name and the total quantity. Include items that have never been ordered (show quantity 0). Order by item name.
List the categories that contain menu items where the average price of items in that category is greater than $10. Show the category name and the average price.
Display the details of all items for Order ID = 1: include the order ID, customer name, item name, quantity ordered, and price per item at the time of order.

Variant 11: Blog/Content Management System

Scenario: You are managing the database for a blog platform. The system stores information about users (authors), blog posts, categories for posts, tags for fine-grained topics, comments on posts, and the relationships between them (e.g., which tags apply to which posts).

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS tags;

-- Create Users table (Authors)
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Categories table
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

-- Create Posts table
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INT REFERENCES users(user_id),
    category_id INT REFERENCES categories(category_id),
    publish_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(15) CHECK (status IN ('Draft', 'Published', 'Archived')) DEFAULT 'Draft'
);

-- Create Tags table
CREATE TABLE tags (
    tag_id SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) NOT NULL UNIQUE
);

-- Create PostTags table (Junction table for Many-to-Many between Posts and Tags)
CREATE TABLE post_tags (
    post_id INT REFERENCES posts(post_id),
    tag_id INT REFERENCES tags(tag_id),
    PRIMARY KEY (post_id, tag_id)
);

-- Create Comments table (with self-reference for replies)
CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(post_id),
    author_id INT REFERENCES users(user_id), -- Comment author (can be NULL for anonymous)
    comment_text TEXT NOT NULL,
    comment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reply_to_comment_id INT REFERENCES comments(comment_id) NULL -- For threaded comments
);

-- Insert sample data
INSERT INTO users (username, email, registration_date) VALUES
('john_doe', 'john@example.com', '2023-01-10 10:00:00'),
('jane_smith', 'jane@example.com', '2023-02-15 11:00:00'),
('guest_commenter', 'guest@anon.com', '2023-03-20 12:00:00'); -- User for commenting

INSERT INTO categories (category_name, description) VALUES
('Technology', 'Articles about gadgets, software, and the internet.'),
('Travel', 'Stories and tips about exploring the world.'),
('Cooking', 'Recipes and culinary adventures.');

INSERT INTO posts (title, content, author_id, category_id, publish_date, status) VALUES
('Introduction to SQL JOINs', 'Exploring INNER JOIN, LEFT JOIN...', 1, 1, '2023-10-01 09:00:00', 'Published'),
('My Trip to Italy', 'Visiting Rome, Florence, and Venice...', 2, 2, '2023-10-05 14:00:00', 'Published'),
('Making Perfect Pasta', 'A step-by-step guide to homemade pasta.', 2, 3, '2023-10-10 11:00:00', 'Published'),
('Advanced Git Techniques', 'Beyond commit, push, pull...', 1, 1, '2023-10-15 16:00:00', 'Draft'),
('Hidden Gems in Tokyo', 'Exploring lesser-known spots.', 2, 2, NULL, 'Draft'); -- Not published yet

INSERT INTO tags (tag_name) VALUES
('SQL'), ('Database'), ('Travel Tips'), ('Europe'), ('Italy'), ('Food'), ('Recipe'), ('Pasta'), ('Git'), ('Version Control'), ('Japan');

INSERT INTO post_tags (post_id, tag_id) VALUES
(1, 1), (1, 2), -- Post 1: SQL, Database
(2, 3), (2, 4), (2, 5), -- Post 2: Travel Tips, Europe, Italy
(3, 6), (3, 7), (3, 8), -- Post 3: Food, Recipe, Pasta
(4, 9), (4, 10); -- Post 4: Git, Version Control
-- Post 5 has no tags yet

INSERT INTO comments (post_id, author_id, comment_text, comment_date, reply_to_comment_id) VALUES
(1, 2, 'Great explanation of JOINs!', '2023-10-02 10:00:00', NULL), -- Comment 1
(1, 3, 'Thanks, this was helpful.', '2023-10-02 11:30:00', NULL), -- Comment 2
(2, 1, 'Italy looks amazing!', '2023-10-06 09:15:00', NULL),      -- Comment 3
(1, 1, 'Glad you found it useful!', '2023-10-03 08:00:00', 1);    -- Comment 4 (reply to Comment 1)
-- Note: Post 3, 4, 5 have no comments yet.

Tasks for Variant 11:

List all published posts (status = 'Published') along with the author’s username and the post’s category name. Order by publish date descending.
Show all users and the titles of the posts they have authored. Include users who haven’t authored any posts yet. Order by username.
For every comment, list its text, the username of the comment author, and the text of the comment it replies to (if any). If it’s not a reply, show NULL for the replied-to text. (Requires self-join on comments).
Display the titles of all posts that have the tag ‘SQL’. (Requires joining posts, post_tags, and tags).
Find all comments written by the user ‘jane_smith’. List the comment text and the title of the post the comment belongs to.
List the titles of all posts belonging to the ‘Technology’ category. Also include the tags associated with each of these posts (list all tags for each post).
Show the titles of posts that have received comments from ‘guest_commenter’. List only unique post titles.
Count the number of posts written by each author (user). List the username and the post count. Include users who have written zero posts. Order by post count descending.
List the categories that have more than 1 post published in them. Show the category name and the count of published posts.
Display the full details for comments on the post titled ‘Introduction to SQL JOINs’. Include the comment text, comment author’s username, comment date, and if it’s a reply, include the ID of the comment it replies to.

Variant 12: Gym Membership System

Scenario: You are managing the database for a fitness center. The system tracks members, their membership types, fitness classes offered, instructors teaching the classes, the class schedule (which class/instructor/time/location), and member attendance records.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS attendance;
DROP TABLE IF EXISTS schedule;
DROP TABLE IF EXISTS instructors;
DROP TABLE IF EXISTS fitness_classes;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS membership_types;


-- Create MembershipTypes table
CREATE TABLE membership_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL UNIQUE,
    monthly_fee DECIMAL(8, 2) CHECK (monthly_fee >= 0),
    access_level VARCHAR(50) -- e.g., 'Full', 'Off-Peak', 'Classes Only'
);

-- Create Members table
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100) NOT NULL,
    join_date DATE DEFAULT CURRENT_DATE,
    email VARCHAR(100) UNIQUE,
    membership_type_id INT REFERENCES membership_types(type_id)
);

-- Create Instructors table
CREATE TABLE instructors (
    instructor_id SERIAL PRIMARY KEY,
    instructor_name VARCHAR(100) NOT NULL,
    specialty VARCHAR(100), -- e.g., 'Yoga', 'Spin', 'Strength Training'
    hire_date DATE
);

-- Create FitnessClasses table (with self-reference for prerequisites)
CREATE TABLE fitness_classes (
    class_id SERIAL PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    duration_minutes INT CHECK (duration_minutes > 0),
    prerequisite_class_id INT REFERENCES fitness_classes(class_id) NULL -- e.g., 'Advanced Yoga' requires 'Beginner Yoga'
);

-- Create Schedule table (linking Class, Instructor, Time/Location)
CREATE TABLE schedule (
    schedule_id SERIAL PRIMARY KEY,
    class_id INT REFERENCES fitness_classes(class_id),
    instructor_id INT REFERENCES instructors(instructor_id),
    class_datetime TIMESTAMP NOT NULL, -- Specific date and time
    location VARCHAR(50), -- e.g., 'Studio A', 'Spin Room'
    max_capacity INT CHECK (max_capacity > 0)
);

-- Create Attendance table (Junction table)
CREATE TABLE attendance (
    attendance_id SERIAL PRIMARY KEY,
    member_id INT REFERENCES members(member_id),
    schedule_id INT REFERENCES schedule(schedule_id),
    check_in_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (member_id, schedule_id) -- Member can attend a specific scheduled class only once
);

-- Insert sample data
INSERT INTO membership_types (type_name, monthly_fee, access_level) VALUES
('Gold Full Access', 75.00, 'Full'),
('Silver Off-Peak', 50.00, 'Off-Peak'),
('Bronze Classes Only', 40.00, 'Classes Only');

INSERT INTO members (member_name, join_date, email, membership_type_id) VALUES
('Alice Young', '2023-01-15', 'alice.y@mail.com', 1),
('Bob Oldman', '2022-11-01', 'bob.o@mail.com', 2),
('Charlie Fast', '2023-03-10', 'charlie.f@mail.com', 1),
('Diana Strong', '2023-05-05', 'diana.s@mail.com', 3),
('Ethan Flexible', '2023-08-20', 'ethan.f@mail.com', 1); -- New member, no attendance yet

INSERT INTO instructors (instructor_name, specialty, hire_date) VALUES
('Instructor Ann', 'Yoga', '2022-05-01'),
('Instructor Ben', 'Spin', '2022-08-15'),
('Instructor Chloe', 'Strength Training', '2023-02-01'),
('Instructor Dave', 'Yoga', '2023-06-01'); -- Another Yoga instructor

INSERT INTO fitness_classes (class_name, description, duration_minutes, prerequisite_class_id) VALUES
('Beginner Yoga', 'Introduction to basic yoga poses and breathing.', 60, NULL), -- Class 1
('Spin Express', 'High-intensity interval cycling class.', 45, NULL),         -- Class 2
('Total Body Strength', 'Full body workout using weights and bodyweight.', 60, NULL), -- Class 3
('Advanced Yoga', 'Challenging poses and flow sequences.', 75, 1);           -- Class 4 (Requires Class 1)

INSERT INTO schedule (class_id, instructor_id, class_datetime, location, max_capacity) VALUES
(1, 1, '2023-10-23 09:00:00', 'Studio A', 20), -- Beginner Yoga with Ann
(2, 2, '2023-10-23 17:30:00', 'Spin Room', 15), -- Spin Express with Ben
(3, 3, '2023-10-24 18:00:00', 'Weight Room', 12), -- Strength with Chloe
(1, 4, '2023-10-25 10:00:00', 'Studio A', 20), -- Beginner Yoga with Dave
(4, 1, '2023-10-26 09:00:00', 'Studio A', 15); -- Advanced Yoga with Ann

INSERT INTO attendance (member_id, schedule_id, check_in_time) VALUES
(1, 1, '2023-10-23 08:55:00'), -- Alice attends Beginner Yoga (Ann)
(2, 1, '2023-10-23 08:58:00'), -- Bob attends Beginner Yoga (Ann)
(3, 2, '2023-10-23 17:25:00'), -- Charlie attends Spin (Ben)
(1, 2, '2023-10-23 17:28:00'), -- Alice attends Spin (Ben)
(4, 3, '2023-10-24 17:55:00'); -- Diana attends Strength (Chloe)
-- Note: Ethan hasn't attended yet. Schedule ID 4 (Beginner Yoga with Dave) and 5 (Advanced Yoga with Ann) have no attendees yet.

Tasks for Variant 12:

List all members and the name of their membership type. Include members who might not have a type assigned (if possible). Order by member name.
Show all scheduled classes: include the class name, instructor’s name, scheduled date/time, and location. Order by date/time.
For every fitness class that has a prerequisite, list the class name and the name of its prerequisite class. (Requires self-join on fitness_classes).
Display the names of members who have attended at least one class. List the member’s name, the name of the class they attended, and the date/time of the class. Order by member name, then class date/time.
Find all classes taught by ‘Instructor Ann’. List the class name, scheduled date/time, and location.
List the names of all members with a ‘Gold Full Access’ membership type, along with the names of the classes they have attended. If a Gold member hasn’t attended any classes, they should still appear in the list (with NULL for class name).
Show the names of fitness classes scheduled to take place in ‘Studio A’. List only the unique class names.
Count the number of classes attended by each member. List the member’s name and the count. Include members who have attended zero classes. Order by the count descending.
List the instructors who are scheduled to teach more than one class instance in the current schedule. Show the instructor’s name and the count of scheduled classes.
Display the full attendance details for the ‘Spin Express’ class scheduled on ‘2023-10-23 17:30:00’ (Schedule ID = 2). Include the member name, check-in time, class name, and instructor name.

Variant 13: Conference Management System

Scenario: You are managing the database for a company organizing technical conferences. The system tracks conferences, the venues where they are held, individual sessions within each conference, the speakers presenting at sessions, and attendees who register and attend sessions.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS attendee_sessions;
DROP TABLE IF EXISTS attendees;
DROP TABLE IF EXISTS session_speakers;
DROP TABLE IF EXISTS speakers;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS conferences;
DROP TABLE IF EXISTS venues;


-- Create Venues table
CREATE TABLE venues (
    venue_id SERIAL PRIMARY KEY,
    venue_name VARCHAR(150) NOT NULL UNIQUE,
    address VARCHAR(255),
    capacity INT CHECK (capacity > 0)
);

-- Create Conferences table
CREATE TABLE conferences (
    conf_id SERIAL PRIMARY KEY,
    conf_name VARCHAR(200) NOT NULL,
    start_date DATE,
    end_date DATE,
    venue_id INT REFERENCES venues(venue_id)
);

-- Create Sessions table (with self-reference for prerequisites)
CREATE TABLE sessions (
    session_id SERIAL PRIMARY KEY,
    session_title VARCHAR(200) NOT NULL,
    session_time TIMESTAMP,
    room VARCHAR(50),
    conf_id INT REFERENCES conferences(conf_id) NOT NULL,
    prerequisite_session_id INT REFERENCES sessions(session_id) NULL -- For sessions that build on others
);

-- Create Speakers table
CREATE TABLE speakers (
    speaker_id SERIAL PRIMARY KEY,
    speaker_name VARCHAR(100) NOT NULL,
    affiliation VARCHAR(150), -- Company or University
    bio TEXT
);

-- Create SessionSpeakers junction table (Many-to-Many: Sessions <-> Speakers)
CREATE TABLE session_speakers (
    session_id INT REFERENCES sessions(session_id),
    speaker_id INT REFERENCES speakers(speaker_id),
    PRIMARY KEY (session_id, speaker_id) -- Composite key
);

-- Create Attendees table
CREATE TABLE attendees (
    attendee_id SERIAL PRIMARY KEY,
    attendee_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Create AttendeeSessions junction table (Many-to-Many: Attendees <-> Sessions)
CREATE TABLE attendee_sessions (
    attendance_id SERIAL PRIMARY KEY,
    attendee_id INT REFERENCES attendees(attendee_id),
    session_id INT REFERENCES sessions(session_id),
    check_in_time TIMESTAMP NULL, -- Track actual attendance
    UNIQUE (attendee_id, session_id) -- Attendee attends a specific session once
);


-- Insert sample data
INSERT INTO venues (venue_name, address, capacity) VALUES
('Metro Convention Center', '123 Main St', 5000),
('Tech Park Auditorium', '456 Tech Ave', 800),
('University Hall', '789 Campus Dr', 1200);

INSERT INTO conferences (conf_name, start_date, end_date, venue_id) VALUES
('InnovateSphere 2024', '2024-10-15', '2024-10-17', 1),
('DataCon West', '2024-11-05', '2024-11-06', 2),
('FutureTech Summit', '2025-03-10', '2025-03-12', NULL); -- Venue TBD

INSERT INTO sessions (session_title, session_time, room, conf_id, prerequisite_session_id) VALUES
('Keynote: The Future of AI', '2024-10-15 09:00:00', 'Main Hall', 1, NULL),                  -- Session 1
('Workshop: Advanced SQL Techniques', '2024-10-15 11:00:00', 'Room 101', 1, NULL),           -- Session 2
('Panel: Cloud Native Architectures', '2024-10-15 14:00:00', 'Room 102', 1, NULL),           -- Session 3
('Deep Dive into Machine Learning', '2024-10-16 10:00:00', 'Room 101', 1, 1),                -- Session 4 (Prereq: Keynote)
('Mastering Big Data Pipelines', '2024-11-05 10:30:00', 'Auditorium A', 2, NULL),           -- Session 5
('Securing Your Data Lake', '2024-11-05 14:00:00', 'Auditorium B', 2, 5),                   -- Session 6 (Prereq: Big Data)
('Ethical AI Considerations', '2024-11-06 09:00:00', 'Auditorium A', 2, NULL);               -- Session 7

INSERT INTO speakers (speaker_name, affiliation, bio) VALUES
('Dr. Evelyn Reed', 'SynthAI Corp', 'Pioneer in AI research.'),
('Mark Johnson', 'DataWorks Inc.', 'Expert in database optimization.'),
('Sarah Chen', 'CloudNine Solutions', 'Cloud infrastructure specialist.'),
('David Lee', 'CloudNine Solutions', 'Security architect.'), -- Another speaker from CloudNine
('Prof. Alan Grant', 'State University', 'Leading ML researcher.'),
('Jane Matrix', 'BigData Systems', 'Specializes in data pipeline engineering.');

INSERT INTO session_speakers (session_id, speaker_id) VALUES
(1, 1), -- Keynote by Reed
(2, 2), -- SQL Workshop by Johnson
(3, 3), -- Cloud Panel - Chen
(3, 4), -- Cloud Panel - Lee (Multiple speakers for session 3)
(4, 5), -- ML Deep Dive by Grant
(5, 6), -- Big Data Pipelines by Matrix
(6, 6), -- Securing Data Lake by Matrix (Same speaker, different session)
(7, 1); -- Ethical AI by Reed

INSERT INTO attendees (attendee_name, email, registration_date) VALUES
('Alice Coder', 'alice@coder.com', '2024-08-01'),
('Bob Analyst', 'bob@analyst.net', '2024-08-15'),
('Charlie Engineer', 'charlie@engineer.org', '2024-09-01'),
('Diana Manager', 'diana@manager.co', '2024-09-10'); -- Registered, but maybe not attending sessions yet

INSERT INTO attendee_sessions (attendee_id, session_id, check_in_time) VALUES
(1, 1, '2024-10-15 08:55:00'), -- Alice, Keynote
(1, 2, '2024-10-15 10:58:00'), -- Alice, SQL Workshop
(1, 4, '2024-10-16 09:59:00'), -- Alice, ML Deep Dive
(2, 5, '2024-11-05 10:25:00'), -- Bob, Big Data Pipelines
(2, 6, '2024-11-05 13:55:00'), -- Bob, Securing Data Lake
(3, 1, '2024-10-15 08:50:00'), -- Charlie, Keynote
(3, 3, '2024-10-15 13:58:00'); -- Charlie, Cloud Panel
-- Note: Diana hasn't attended any sessions yet.
-- Note: Session 7 (Ethical AI) has no registered attendees yet.

Tasks for Variant 13:

List all sessions along with the name of the conference they belong to and the name of the venue hosting the conference. Include conferences that might not have a venue assigned yet.
Show the names of all speakers and the titles of the sessions they are presenting. Include speakers who are not assigned to any session yet. Order by speaker name, then session title.
For every session that has a prerequisite, list the session’s title and the title of its prerequisite session. (Requires self-join on sessions).
Display the names of attendees who have checked into at least one session. List the attendee’s name, the session title, and the check-in time. Order by attendee name, then check-in time.
Find all sessions presented by speakers affiliated with ‘CloudNine Solutions’. List the session title, speaker name, and conference name.
List the names of all registered attendees and the names of the conferences for the sessions they attended. If an attendee hasn’t attended any session, they should still appear in the list (with NULL for conference name). Produce a distinct list of attendee-conference pairs.
Show the titles of sessions that have more than one speaker assigned. List only the session titles.
Count the number of sessions each speaker is presenting. List the speaker’s name and their session count. Include speakers with zero sessions. Order by the count descending.
List the conferences that have more than 3 sessions scheduled. Show the conference name and the count of sessions.
Display the full details for attendees who attended the session titled ‘Keynote: The Future of AI’. Include the attendee’s name, email, registration date, and their check-in time for that specific session.

Variant 14: Bug Tracking System

Scenario: You are developing a database for a software company to track issues (bugs, feature requests) across different projects. The system needs to record who reported an issue, who is assigned to fix it, the current status, and potentially link related issues.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS issues;
DROP TABLE IF EXISTS statuses;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS projects;

-- Create Projects table
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    start_date DATE
);

-- Create Users table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    role VARCHAR(50) CHECK (role IN ('Developer', 'QA', 'Manager', 'Reporter'))
);

-- Create Statuses table
CREATE TABLE statuses (
    status_id SERIAL PRIMARY KEY,
    status_name VARCHAR(50) NOT NULL UNIQUE -- e.g., Open, In Progress, Resolved, Closed, Reopened
);

-- Create Issues table (with self-reference for related issues)
CREATE TABLE issues (
    issue_id SERIAL PRIMARY KEY,
    summary VARCHAR(255) NOT NULL,
    description TEXT,
    project_id INT REFERENCES projects(project_id) NOT NULL,
    reporter_id INT REFERENCES users(user_id) NOT NULL,
    assignee_id INT REFERENCES users(user_id) NULL, -- Can be unassigned
    status_id INT REFERENCES statuses(status_id) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    priority INT CHECK (priority BETWEEN 1 AND 5), -- 1=Highest, 5=Lowest
    related_issue_id INT REFERENCES issues(issue_id) NULL -- Link to a related/duplicate issue
);

-- Insert sample data
INSERT INTO projects (project_name, description, start_date) VALUES
('Phoenix Framework', 'Next-gen web framework', '2023-01-10'),
('Quantum DB', 'High-performance database engine', '2022-05-20'),
('Orion Analytics', 'Data visualization platform', '2023-08-01');

INSERT INTO users (user_name, email, role) VALUES
('alice_dev', 'alice@dev.com', 'Developer'),
('bob_qa', 'bob@qa.com', 'QA'),
('charlie_mgr', 'charlie@mgr.com', 'Manager'),
('david_dev', 'david@dev.com', 'Developer'),
('eve_reporter', 'eve@reporter.com', 'Reporter'); -- User who only reports

INSERT INTO statuses (status_name) VALUES
('Open'), ('In Progress'), ('Resolved'), ('Closed'), ('Reopened');

INSERT INTO issues (summary, description, project_id, reporter_id, assignee_id, status_id, priority, related_issue_id) VALUES
('UI button misaligned on login screen', 'The login button is 5px too low.', 1, 2, 1, 2, 2, NULL), -- Issue 1, Proj Phoenix, Rep Bob, Assign Alice, Status In Progress
('Database connection pool exhausted', 'Under heavy load, connections run out.', 2, 1, 4, 1, 1, NULL), -- Issue 2, Proj Quantum, Rep Alice, Assign David, Status Open
('Add export-to-CSV feature', 'Users need to export analytics data.', 3, 3, NULL, 1, 3, NULL), -- Issue 3, Proj Orion, Rep Charlie, Unassigned, Status Open
('Login fails with special characters in password', 'Passwords with & or % cause errors.', 1, 2, 1, 4, 2, NULL), -- Issue 4, Proj Phoenix, Rep Bob, Assign Alice, Status Closed
('Performance degradation on large datasets', 'Queries slow down significantly with >1M rows.', 2, 4, 4, 2, 1, NULL), -- Issue 5, Proj Quantum, Rep David, Assign David, Status In Progress
('Documentation unclear for API endpoint X', 'Need better examples for /api/users.', 1, 5, NULL, 1, 4, NULL), -- Issue 6, Proj Phoenix, Rep Eve, Unassigned, Status Open
('Typo in welcome email', 'Minor typo in the onboarding email template.', 1, 2, 1, 3, 5, NULL), -- Issue 7, Proj Phoenix, Rep Bob, Assign Alice, Status Resolved
('Login button issue (duplicate?)', 'Login button positioning seems off.', 1, 5, 1, 1, 2, 1); -- Issue 8, Proj Phoenix, Rep Eve, Assign Alice, Status Open, Related to Issue 1


-- Trigger simulation for updated_at (in a real scenario, a trigger would handle this)
UPDATE issues SET updated_at = NOW() WHERE issue_id IN (1, 5);

Tasks for Variant 14:

List all issues with their summary, the project name they belong to, and their current status name.
Show the names of all users and the summaries of the issues they have reported. Include users who haven’t reported any issues yet. Order by user name, then issue summary.
For every issue that has a related_issue_id, list the issue’s summary and the summary of the issue it is related to. (Requires self-join on issues).
Display the names of users who are currently assigned to at least one issue. List the user’s name, the issue summary, and the project name. Order by user name.
Find all issues currently assigned to the user ‘alice_dev’. List the issue summary, project name, and current status name.
List the names of all users with the role ‘Developer’ and the summaries of issues currently assigned to them where the status is ‘In Progress’. Include developers who have no ‘In Progress’ issues assigned (they should still appear, with NULL for issue summary).
Show the names of projects that have at least one issue with ‘Open’ status and priority 1 (Highest). List only the unique project names.
Count the number of issues assigned to each user (assignee). List the user’s name and their count of assigned issues. Include users with zero assigned issues. Order by the count descending.
List the statuses that have more than 2 issues currently associated with them. Show the status name and the count of issues.
Display the full details (summary, description, project name, reporter name, assignee name, status name, priority) for all issues related to issue ID 1 (use the related_issue_id column).

Variant 15: Car Rental Agency

Scenario: You are managing the database for a car rental agency. The system needs to track rental locations, types of vehicles, specific vehicles, customer information, and rental agreements (which car, which customer, dates, location).

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS rentals;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS vehicles;
DROP TABLE IF EXISTS vehicle_types;
DROP TABLE IF EXISTS locations;


-- Create Locations table
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    location_name VARCHAR(100) NOT NULL UNIQUE,
    address VARCHAR(255),
    phone_number VARCHAR(20)
);

-- Create VehicleTypes table
CREATE TABLE vehicle_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL UNIQUE, -- e.g., Sedan, SUV, Truck, Van
    daily_rate DECIMAL(8, 2) CHECK (daily_rate > 0)
);

-- Create Vehicles table
CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    license_plate VARCHAR(15) NOT NULL UNIQUE,
    make VARCHAR(50),
    model VARCHAR(50),
    year INT,
    color VARCHAR(30),
    type_id INT REFERENCES vehicle_types(type_id) NOT NULL,
    current_location_id INT REFERENCES locations(location_id) NOT NULL -- Where the car is currently parked
);

-- Create Customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(150) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    driver_license_number VARCHAR(50) UNIQUE
);

-- Create Rentals table
CREATE TABLE rentals (
    rental_id SERIAL PRIMARY KEY,
    vehicle_id INT REFERENCES vehicles(vehicle_id) NOT NULL,
    customer_id INT REFERENCES customers(customer_id) NOT NULL,
    pickup_location_id INT REFERENCES locations(location_id) NOT NULL,
    return_location_id INT REFERENCES locations(location_id) NULL, -- NULL if not returned yet, otherwise where it was returned
    pickup_date TIMESTAMP NOT NULL,
    expected_return_date DATE,
    actual_return_date TIMESTAMP NULL, -- NULL if currently rented out
    total_cost DECIMAL(10, 2) NULL -- Can be calculated upon return
);

-- Insert sample data
INSERT INTO locations (location_name, address, phone_number) VALUES
('Downtown Central', '100 City Ave', '555-1234'),
('Airport Branch', '200 Airport Rd', '555-5678'),
('West Suburb Lot', '300 Suburb Ln', '555-9012');

INSERT INTO vehicle_types (type_name, daily_rate) VALUES
('Sedan', 55.00),
('SUV', 75.50),
('Truck', 85.00),
('Van', 95.00);

INSERT INTO vehicles (license_plate, make, model, year, color, type_id, current_location_id) VALUES
('ABC-123', 'Toyota', 'Camry', 2022, 'Silver', 1, 1), -- Sedan at Downtown
('XYZ-789', 'Ford', 'Explorer', 2023, 'Black', 2, 1), -- SUV at Downtown
('JKL-456', 'Honda', 'CRV', 2021, 'Red', 2, 2),      -- SUV at Airport
('MNO-321', 'Ford', 'F-150', 2022, 'White', 3, 2),    -- Truck at Airport
('PQR-654', 'Toyota', 'Sienna', 2023, 'Blue', 4, 3),   -- Van at West Suburb
('STU-987', 'Nissan', 'Altima', 2020, 'Gray', 1, 1);   -- Sedan at Downtown (available)

INSERT INTO customers (customer_name, email, phone, driver_license_number) VALUES
('Arthur Dent', 'arthur@hitchhiker.com', '555-1111', 'DL112233'),
('Trillian Astra', 'trillian@heartofgold.com', '555-2222', 'DL445566'),
('Zaphod Beeblebrox', 'zaphod@prez.galaxy', '555-3333', 'DL778899'),
('Marvin Paranoid', 'marvin@sirius.cyber', '555-4444', 'DL990011'); -- New customer, no rentals yet

INSERT INTO rentals (vehicle_id, customer_id, pickup_location_id, return_location_id, pickup_date, expected_return_date, actual_return_date, total_cost) VALUES
(1, 1, 1, 1, '2024-07-01 10:00:00', '2024-07-05', '2024-07-05 09:30:00', 220.00), -- Arthur, Camry, Past Rental (Returned Downtown)
(3, 2, 2, 2, '2024-07-10 14:00:00', '2024-07-15', '2024-07-16 11:00:00', 453.00), -- Trillian, CRV, Past Rental (Returned Airport, late)
(2, 3, 1, NULL, '2024-07-18 09:00:00', '2024-07-22', NULL, NULL),                 -- Zaphod, Explorer, Current Rental (Picked up Downtown)
(4, 1, 2, NULL, '2024-07-20 11:30:00', '2024-07-25', NULL, NULL),                 -- Arthur, F-150, Current Rental (Picked up Airport)
(5, 2, 3, 3, '2024-06-15 12:00:00', '2024-06-20', '2024-06-20 10:00:00', 475.00); -- Trillian, Sienna, Past Rental (Returned West Suburb)

-- Update current location for vehicles that were returned elsewhere (or not updated after last rental)
-- Assuming Vehicle 1 (ABC-123) returned to Downtown (Location 1) - already there
-- Assuming Vehicle 3 (JKL-456) returned to Airport (Location 2) - already there
-- Assuming Vehicle 5 (PQR-654) returned to West Suburb (Location 3) - already there
-- Note: Vehicle 2 (XYZ-789) and Vehicle 4 (MNO-321) are currently rented out, their 'current_location_id' in the vehicles table might be outdated until they are returned. Let's assume the `vehicles.current_location_id` reflects where the car *should* be if not rented. For this exercise, we'll use the rentals table to determine current status.

Tasks for Variant 15:

List all vehicles (make, model, year) along with their vehicle type name and daily rental rate.
Show the names of all customers and the license plates of the vehicles they have rented (include past and current rentals). Include customers who have never rented a vehicle. Order by customer name, then pickup date.
List all vehicles (license plate, make, model) that are currently rented out (i.e., actual_return_date is NULL in the rentals table). Also show the name of the customer who rented it and the pickup date.
Display the names of customers who have rented an ‘SUV’. List each customer name only once.
Find all rentals that originated from the ‘Airport Branch’ location. List the rental ID, customer name, vehicle license plate, and pickup date.
List the names of all vehicle types and the make/model of vehicles belonging to that type currently parked at the ‘Downtown Central’ location. Include vehicle types for which no vehicles are currently at that location.
Show the names of locations that have more than 1 vehicle currently parked there (based on vehicles.current_location_id). List the location name and the count of vehicles.
Calculate the total number of rentals processed by each pickup location. List the location name and the count of rentals. Include locations that have had zero pickups. Order by the count descending.
List customers who have rented more than one vehicle (can be same vehicle multiple times or different vehicles). Show the customer’s name and the number of rentals they have made.
Display the details for all vehicles of type ‘Sedan’. Include license plate, make, model, year, color, daily rate, and the name of the location where the vehicle is currently listed (from the vehicles table).

Variant 16: Scientific Research Lab Inventory & Experiments

Scenario: You are managing the database for a scientific research facility. The database needs to track researchers, labs (or departments within the facility), equipment, chemical reagents, experiments conducted, and which reagents were used in specific experiments.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS experiment_reagents;
DROP TABLE IF EXISTS experiments;
DROP TABLE IF EXISTS reagents;
DROP TABLE IF EXISTS equipment;
DROP TABLE IF EXISTS researchers;
DROP TABLE IF EXISTS labs;

-- Create Labs table
CREATE TABLE labs (
    lab_id SERIAL PRIMARY KEY,
    lab_name VARCHAR(100) NOT NULL UNIQUE,
    building_floor VARCHAR(50),
    principal_investigator VARCHAR(100) -- Could reference researchers, simplified here
);

-- Create Researchers table
CREATE TABLE researchers (
    researcher_id SERIAL PRIMARY KEY,
    researcher_name VARCHAR(100) NOT NULL,
    specialization VARCHAR(100),
    lab_id INT REFERENCES labs(lab_id),
    start_date DATE
);

-- Create Equipment table
CREATE TABLE equipment (
    equipment_id SERIAL PRIMARY KEY,
    equipment_name VARCHAR(150) NOT NULL,
    model_number VARCHAR(100),
    lab_id INT REFERENCES labs(lab_id), -- Location of the equipment
    purchase_date DATE,
    status VARCHAR(30) CHECK (status IN ('Operational', 'Maintenance', 'Decommissioned'))
);

-- Create Reagents table
CREATE TABLE reagents (
    reagent_id SERIAL PRIMARY KEY,
    reagent_name VARCHAR(100) NOT NULL,
    formula VARCHAR(100),
    supplier VARCHAR(100),
    storage_temp_celsius INT, -- e.g., 4, -20, 25 (Room Temp)
    quantity_ml DECIMAL(8, 2) CHECK (quantity_ml >= 0)
);

-- Create Experiments table (with potential self-reference for follow-up experiments)
CREATE TABLE experiments (
    experiment_id SERIAL PRIMARY KEY,
    experiment_name VARCHAR(200) NOT NULL,
    researcher_id INT REFERENCES researchers(researcher_id), -- Lead researcher
    start_date DATE,
    end_date DATE NULL,
    objective TEXT,
    follow_up_to_experiment_id INT REFERENCES experiments(experiment_id) NULL
);

-- Create ExperimentReagents table (Junction table)
CREATE TABLE experiment_reagents (
    exp_reagent_id SERIAL PRIMARY KEY,
    experiment_id INT REFERENCES experiments(experiment_id),
    reagent_id INT REFERENCES reagents(reagent_id),
    quantity_used_ml DECIMAL(8, 2) CHECK (quantity_used_ml > 0),
    UNIQUE (experiment_id, reagent_id) -- A specific reagent is logged once per experiment
);

-- Insert sample data
INSERT INTO labs (lab_name, building_floor, principal_investigator) VALUES
('Biochemistry Lab', 'Building A, Floor 2', 'Dr. Evelyn Reed'),
('Genomics Core', 'Building B, Floor 1', 'Dr. Kenji Tanaka'),
('Materials Science Lab', 'Building A, Floor 3', 'Dr. Fatima Khan'),
('Unassigned Lab Space', 'Building C, Floor 1', NULL);

INSERT INTO researchers (researcher_name, specialization, lab_id, start_date) VALUES
('Dr. Alice Miller', 'Protein Chemistry', 1, '2021-08-15'),
('Dr. Bob Carter', 'DNA Sequencing', 2, '2022-01-10'),
('Dr. Chandra Singh', 'Polymer Synthesis', 3, '2022-05-20'),
('Dr. David Chen', 'Cell Biology', 1, '2023-03-01'),
('Dr. Eva Rostova', 'Bioinformatics', 2, '2023-07-01'),
('Dr. Frank Weber', NULL, NULL, '2023-09-01'); -- New researcher, no lab/specialization yet

INSERT INTO equipment (equipment_name, model_number, lab_id, purchase_date, status) VALUES
('Centrifuge 5810R', 'Eppendorf 5810R', 1, '2021-09-01', 'Operational'),
('DNA Sequencer', 'Illumina NovaSeq', 2, '2022-02-15', 'Operational'),
('Spectrophotometer', 'Thermo NanoDrop', 1, '2021-09-01', 'Maintenance'),
('Fume Hood', 'Labconco FH-101', 3, '2022-06-01', 'Operational'),
('Microscope', 'Zeiss Axio Imager', 1, '2023-04-10', 'Operational'),
('PCR Machine', 'BioRad T100', 2, '2022-03-01', 'Operational'); -- Located in Genomics Core

INSERT INTO reagents (reagent_name, formula, supplier, storage_temp_celsius, quantity_ml) VALUES
('Ethanol (95%)', 'C2H5OH', 'SigmaAldrich', 25, 5000.00),
('Tris Buffer (1M)', 'C4H11NO3', 'BioRad', 4, 1000.00),
('Sodium Chloride (NaCl)', 'NaCl', 'FisherSci', 25, 2000.00),
('dNTP Mix', NULL, 'NEB', -20, 50.00),
('Polymer X Resin', NULL, 'ChemSupply', 25, 500.00),
('HEPES Buffer', 'C8H18N2O4S', 'SigmaAldrich', 4, 500.00);

INSERT INTO experiments (experiment_name, researcher_id, start_date, end_date, objective, follow_up_to_experiment_id) VALUES
('Protein Purification Trial 1', 1, '2023-02-10', '2023-02-15', 'Isolate Protein Y using affinity column.', NULL), -- Exp 1
('PCR Amplification of Gene Z', 2, '2023-04-01', '2023-04-02', 'Amplify target gene Z for sequencing.', NULL),      -- Exp 2
('Polymer Strength Test', 3, '2023-06-15', NULL, 'Test tensile strength of new polymer.', NULL),                 -- Exp 3 (Ongoing)
('Protein Purification Trial 2', 1, '2023-08-20', '2023-08-25', 'Optimize buffer conditions for Protein Y.', 1), -- Exp 4 (Follow-up to Exp 1)
('Cell Staining Study', 4, '2023-09-05', NULL, 'Observe cell morphology after treatment.', NULL);                 -- Exp 5 (Ongoing, by David Chen)

INSERT INTO experiment_reagents (experiment_id, reagent_id, quantity_used_ml) VALUES
(1, 2, 50.00),  -- Exp 1 uses Tris Buffer
(1, 3, 10.00),  -- Exp 1 uses NaCl
(2, 1, 5.00),   -- Exp 2 uses Ethanol
(2, 4, 2.00),   -- Exp 2 uses dNTP Mix
(3, 5, 100.00), -- Exp 3 uses Polymer X Resin
(4, 2, 75.00),  -- Exp 4 uses Tris Buffer
(4, 6, 25.00);  -- Exp 4 uses HEPES Buffer
-- Note: Experiment 5 has no reagents logged yet.
-- Note: Reagent 'HEPES Buffer' used only in Experiment 4.

Tasks for Variant 16:

List all researchers and the name of the lab they belong to. Include researchers not yet assigned to a lab. Order by lab name, then researcher name.
Show all equipment items, their status, and the name of the lab where they are located. Include equipment that might not be assigned to a specific lab (if possible according to the schema).
For every experiment that is a follow-up to another experiment, list the follow-up experiment’s name and the name of the experiment it follows up on. (Requires self-join).
Display the names of researchers who are conducting experiments that are currently ongoing (i.e., end_date is NULL). List the researcher’s name and the name of the ongoing experiment.
Find all experiments that used the reagent ‘Tris Buffer (1M)’. List the experiment name and the date it started.
List the names of all researchers working in the ‘Biochemistry Lab’ and the names of the experiments they are leading. If a researcher in this lab is not leading any experiments listed, they should still appear (with NULL for experiment name).
Show the names of reagents that are stored at 4 degrees Celsius and have more than 100 ml currently in stock.
Calculate the total quantity (in ml) of each reagent used across all experiments. List the reagent name and the total quantity used. Include reagents that have never been used (show quantity 0). Order by reagent name.
List the labs that house more than 1 piece of ‘Operational’ equipment. Show the lab name and the count of operational equipment.
Display the details for all reagents used in the experiment named ‘Protein Purification Trial 1’. Include the experiment name, reagent name, its formula (if available), and the quantity used in that specific experiment.

Variant 17: Freelancer Marketplace Database

Scenario: You are developing the database for a freelancer marketplace platform. This platform connects clients who post projects with freelancers who have specific skills and can bid on those projects.

Schema:

-- Drop tables if they exist (to start fresh)
DROP TABLE IF EXISTS assignments;
DROP TABLE IF EXISTS proposals;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS freelancer_skills;
DROP TABLE IF EXISTS skills;
DROP TABLE IF EXISTS freelancers;


-- Create Clients table
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    client_name VARCHAR(100) NOT NULL,
    company_name VARCHAR(150) NULL,
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Create Skills table
CREATE TABLE skills (
    skill_id SERIAL PRIMARY KEY,
    skill_name VARCHAR(100) NOT NULL UNIQUE,
    skill_description TEXT NULL
);

-- Create Freelancers table
CREATE TABLE freelancers (
    freelancer_id SERIAL PRIMARY KEY,
    freelancer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hourly_rate DECIMAL(8, 2) NULL,
    join_date DATE DEFAULT CURRENT_DATE
);

-- Create FreelancerSkills table (Junction table: Freelancer <-> Skill)
CREATE TABLE freelancer_skills (
    freelancer_id INT REFERENCES freelancers(freelancer_id),
    skill_id INT REFERENCES skills(skill_id),
    years_experience INT CHECK (years_experience >= 0),
    PRIMARY KEY (freelancer_id, skill_id)
);

-- Create Projects table
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_title VARCHAR(200) NOT NULL,
    client_id INT REFERENCES clients(client_id),
    description TEXT,
    budget DECIMAL(10, 2) NULL, -- Can be fixed price or estimated
    post_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(30) CHECK (status IN ('Open', 'In Progress', 'Completed', 'Cancelled'))
);

-- Create Proposals table (Freelancers bid on Projects)
CREATE TABLE proposals (
    proposal_id SERIAL PRIMARY KEY,
    project_id INT REFERENCES projects(project_id),
    freelancer_id INT REFERENCES freelancers(freelancer_id),
    bid_amount DECIMAL(10, 2) NOT NULL,
    proposal_text TEXT,
    proposal_date DATE DEFAULT CURRENT_DATE,
    UNIQUE (project_id, freelancer_id) -- Freelancer can only submit one proposal per project
);

-- Create Assignments table (Final assignment after proposal accepted)
CREATE TABLE assignments (
    assignment_id SERIAL PRIMARY KEY,
    proposal_id INT UNIQUE REFERENCES proposals(proposal_id), -- Link to the accepted proposal
    project_id INT REFERENCES projects(project_id),        -- Denormalized for easier querying
    freelancer_id INT REFERENCES freelancers(freelancer_id),-- Denormalized for easier querying
    start_date DATE,
    completion_date DATE NULL
);


-- Insert sample data
INSERT INTO clients (client_name, company_name, registration_date) VALUES
('Acme Corp', 'Acme Corporation', '2022-11-01'),
('Beta Solutions', NULL, '2023-01-15'),
('Charlie Startup', 'Innovate Inc.', '2023-03-20'),
('Diana Retail', NULL, '2023-08-01');

INSERT INTO skills (skill_name, skill_description) VALUES
('SQL Database Design', 'Designing and implementing relational database schemas.'),
('Python Development', 'Programming using the Python language.'),
('Web UI/UX Design', 'Designing user interfaces and experiences for websites.'),
('Graphic Design', 'Creating visual concepts using software like Adobe Creative Suite.'),
('Project Management', 'Planning, executing, and closing projects.');

INSERT INTO freelancers (freelancer_name, email, hourly_rate, join_date) VALUES
('Alice Expert', 'alice@email.dev', 75.00, '2022-12-01'),
('Bob Coder', 'bob@email.dev', 60.00, '2023-02-10'),
('Carla Designer', 'carla@email.dev', 80.00, '2023-04-05'),
('David Manager', 'david@email.dev', 90.00, '2023-06-15'),
('Eva Graphics', 'eva@email.dev', NULL, '2023-09-10'); -- Hourly rate not set

INSERT INTO freelancer_skills (freelancer_id, skill_id, years_experience) VALUES
(1, 1, 5), (1, 2, 3), -- Alice: SQL, Python
(2, 2, 4),           -- Bob: Python
(3, 3, 6), (3, 4, 4), -- Carla: UI/UX, Graphic Design
(4, 5, 8),           -- David: Project Management
(1, 5, 2);           -- Alice: Also Project Management

INSERT INTO projects (project_title, client_id, description, budget, post_date, status) VALUES
('Develop E-commerce DB Schema', 1, 'Need schema for new online store.', 1500.00, '2023-05-01', 'Completed'), -- Proj 1
('Build Python Data Scraper', 2, 'Scrape data from public websites.', 2000.00, '2023-07-10', 'In Progress'), -- Proj 2
('Design Marketing Website UI', 1, 'Modern UI/UX for marketing site.', 3000.00, '2023-08-15', 'Open'),        -- Proj 3
('Create Company Logo', 3, 'Need a professional logo.', 500.00, '2023-09-01', 'Open'),           -- Proj 4
('Manage Software Project Launch', 2, 'Oversee launch activities.', 4000.00, '2023-09-20', 'Open'); -- Proj 5

INSERT INTO proposals (project_id, freelancer_id, bid_amount, proposal_text, proposal_date) VALUES
(1, 1, 1400.00, 'Experienced in SQL schema design.', '2023-05-05'), -- Alice bids on Proj 1
(2, 1, 1900.00, 'Can build the scraper using Python/Scrapy.', '2023-07-12'), -- Alice bids on Proj 2
(2, 2, 1850.00, 'Proficient Python dev, ready to start.', '2023-07-13'), -- Bob bids on Proj 2
(3, 3, 2800.00, 'Extensive UI/UX portfolio available.', '2023-08-20'), -- Carla bids on Proj 3
(4, 3, 450.00, 'Specializing in modern logo design.', '2023-09-03'), -- Carla bids on Proj 4
(4, 5, 480.00, 'Creative graphic designer.', '2023-09-05'), -- Eva bids on Proj 4
(5, 4, 3800.00, 'Certified PM with relevant experience.', '2023-09-22'); -- David bids on Proj 5

-- Assume proposals were accepted for some projects
INSERT INTO assignments (proposal_id, project_id, freelancer_id, start_date, completion_date) VALUES
(1, 1, 1, '2023-05-10', '2023-06-30'), -- Alice assigned Proj 1 (Completed)
(3, 2, 2, '2023-07-20', NULL);         -- Bob assigned Proj 2 (In Progress)
-- Note: Projects 3, 4, 5 are 'Open', no assignments yet.
-- Note: Freelancer Eva has bid but not been assigned.

Tasks for Variant 17:

List all freelancers and the skills they possess. Include freelancers who may not have listed any skills yet. Show Freelancer Name and Skill Name.
Show all clients and the titles of the projects they have posted. Include clients who haven’t posted any projects yet. Order by client name, then project title.
For every freelancer who has submitted a proposal, list their name, the project title they bid on, and their bid amount.
Display the names of freelancers who have skills in ‘Python Development’ AND have more than 2 years of experience in that skill.
Find all projects with a status of ‘Open’ and list the project title, client name (or company name if client name is generic), and the project budget.
List the names of all freelancers and the titles of the projects they are currently assigned to (via the assignments table). Include freelancers who are not currently assigned to any project (show NULL for project title).
Show the project titles for projects that have received proposals from freelancers with an hourly rate greater than $70.00. List only unique project titles.
Calculate the number of proposals submitted for each project. List the project title and the proposal count. Include projects with zero proposals. Order by proposal count descending.
List the skills that are possessed by more than 1 freelancer. Show the skill name and the count of freelancers who have that skill.
Display the details of active assignments (where completion_date is NULL): include the project title, the assigned freelancer’s name, the client’s name who posted the project, and the assignment start date.

Submission Instructions

Create a new Google Document. Example
Submit Your Google Doc Link (915-23 KII): Google Sheet
Submit Your Google Doc Link (953-23 AXI): Google Sheet