Databases Tasks for Practical Class 9

Using Aggregate Functions.

Variant 1: Online Bookstore Analytics

Scenario: You are working for an online bookstore. Your task is to analyze data related to books, customers, and their purchase history using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS purchases;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS customers;

-- Create Customers Table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    city VARCHAR(50)
);

-- Create Books Table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100) NOT NULL,
    genre VARCHAR(50),
    price NUMERIC(8, 2) CHECK (price > 0),
    stock_count INT DEFAULT 0
);

-- Create Purchases Table
CREATE TABLE purchases (
    purchase_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    book_id INT NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    quantity INT CHECK (quantity > 0),
    total_price NUMERIC(10, 2), -- Calculated potentially, but stored here for simplicity
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- Insert Sample Data into Customers
INSERT INTO customers (first_name, last_name, email, registration_date, city) VALUES
('Arthur', 'Dent', 'adent@galaxy.net', '2022-01-15', 'London'),
('Ford', 'Prefect', 'fprefect@guide.com', '2022-01-15', 'Betelgeuse'),
('Zaphod', 'Beeblebrox', 'zbeeblebrox@gov.glx', '2022-03-10', 'Maximegalon'),
('Trillian', 'Astra', 'tricia@earth.exp', '2022-05-20', 'London'),
('Marvin', 'Android', 'marvin@sirius.corp', '2021-12-01', NULL); -- NULL city

-- Insert Sample Data into Books
INSERT INTO books (title, author, genre, price, stock_count) VALUES
('The Hitchhiker''s Guide', 'Douglas Adams', 'Science Fiction', 12.99, 50),
('Dune', 'Frank Herbert', 'Science Fiction', 15.50, 35),
('Pride and Prejudice', 'Jane Austen', 'Romance', 9.99, 60),
('1984', 'George Orwell', 'Dystopian', 11.25, 40),
('Foundation', 'Isaac Asimov', 'Science Fiction', 14.00, 25),
('Moby Dick', 'Herman Melville', 'Adventure', 10.50, NULL); -- NULL stock_count

-- Insert Sample Data into Purchases
INSERT INTO purchases (customer_id, book_id, quantity, total_price, purchase_date) VALUES
(1, 1, 1, 12.99, '2023-01-20 10:00:00'),
(2, 1, 1, 12.99, '2023-01-21 11:30:00'),
(1, 3, 2, 19.98, '2023-02-10 14:00:00'),
(3, 2, 1, 15.50, '2023-02-15 09:45:00'),
(4, 4, 1, 11.25, '2023-03-01 16:20:00'),
(1, 2, 1, 15.50, '2023-03-05 12:00:00'),
(2, 5, 3, 42.00, '2023-03-10 18:00:00'),
(5, 1, 1, 12.99, '2023-03-12 10:15:00'),
(3, 5, 1, 14.00, '2023-04-01 11:00:00'),
(4, 1, 1, 12.99, '2023-04-05 15:30:00'); -- Customer 4 bought book 1 again

Tasks:

Count the total number of customers registered.
Calculate the total stock count of all books (ignore books with NULL stock count).
Find the average price of books in the ‘Science Fiction’ genre.
Determine the minimum and maximum book price available in the store.
Count the number of distinct book genres available (ignore NULL genres).
Calculate the total number of books purchased by each customer. Display customer_id and the total_books_purchased. Order by customer_id.
Find the average purchase total_price for each book. Display book_id and average_purchase_price.
Count the number of books written by each author. Display author and book_count. Order by book_count descending.
Find customers who have made more than 2 purchases. Display customer_id and number_of_purchases.
Find book genres where the average book price is greater than $12.00. Display genre and average_genre_price.

Variant 2: University Course Enrollment Analysis

Scenario: You are an administrator at a university. You need to analyze data about students, courses, and their enrollments using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS students;

-- Create Students Table
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    major VARCHAR(50),
    enrollment_year INT NOT NULL,
    gpa NUMERIC(3, 2) CHECK (gpa >= 0.0 AND gpa <= 4.0)
);

-- Create Courses Table
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_code VARCHAR(10) UNIQUE NOT NULL,
    course_title VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    credits INT CHECK (credits > 0)
);

-- Create Enrollments Table
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    semester VARCHAR(20) NOT NULL, -- e.g., 'Fall 2023'
    grade CHAR(2), -- e.g., 'A+', 'B', 'C-', 'F', NULL if incomplete
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- Insert Sample Data into Students
INSERT INTO students (student_name, major, enrollment_year, gpa) VALUES
('Alice Wonderland', 'Computer Science', 2021, 3.8),
('Bob The Builder', 'Engineering', 2020, 3.1),
('Charlie Chaplin', 'Film Studies', 2022, 3.5),
('Diana Prince', 'Political Science', 2021, 3.9),
('Evan Almighty', NULL, 2023, 3.0), -- NULL major
('Fiona Shrek', 'Engineering', 2020, 3.3);

-- Insert Sample Data into Courses
INSERT INTO courses (course_code, course_title, department, credits) VALUES
('CS101', 'Intro to Programming', 'Computer Science', 4),
('ENG201', 'Thermodynamics', 'Engineering', 3),
('FS101', 'Intro to Film', 'Film Studies', 3),
('PS305', 'International Relations', 'Political Science', 3),
('CS340', 'Databases', 'Computer Science', 4),
('ENG310', 'Fluid Mechanics', 'Engineering', 4);

-- Insert Sample Data into Enrollments
INSERT INTO enrollments (student_id, course_id, semester, grade) VALUES
(1, 1, 'Fall 2021', 'A'),
(1, 5, 'Spring 2022', 'A-'),
(2, 2, 'Spring 2021', 'B+'),
(2, 6, 'Fall 2021', 'B'),
(3, 3, 'Fall 2022', 'A'),
(4, 4, 'Spring 2022', 'A+'),
(4, 1, 'Fall 2022', 'B'), -- Diana took CS101
(1, 2, 'Fall 2022', 'B-'), -- Alice took ENG201
(2, 5, 'Spring 2023', 'C+'), -- Bob took Databases
(5, 1, 'Fall 2023', NULL), -- Evan enrolled, grade pending
(6, 2, 'Spring 2021', 'A-'),
(6, 6, 'Fall 2021', 'B+');

Tasks:

Count the total number of students enrolled in the university.
Calculate the total number of credits offered across all courses.
Find the average GPA of all students (ignore students where GPA might be NULL, though none are in sample).
Determine the earliest and latest student enrollment years.
Count the number of distinct student majors (ignore NULL majors).
Calculate the number of courses each student is currently enrolled in or has completed. Display student_id and course_count.
Find the number of students enrolled in each course for the ‘Fall 2022’ semester. Display course_id and student_count.
Calculate the total number of credits students are taking/have taken per major. Display major and total_credits. (Hint: You might need to sum credits based on enrollments).
Find courses that have been taken by more than 2 students across all semesters. Display course_id and enrollment_count.
Find departments where the average number of credits per course is greater than 3. Display department and average_credits.

Variant 3: Project and Task Management Analysis

Scenario: You are managing a team’s workload using a database that tracks projects, team members, and the tasks assigned to them. Use SQL aggregate functions to analyze project progress and team workload.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS team_members;
DROP TABLE IF EXISTS projects;

-- Create Projects Table
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) UNIQUE NOT NULL,
    client_name VARCHAR(100),
    start_date DATE NOT NULL,
    deadline DATE
);

-- Create Team Members Table
CREATE TABLE team_members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    role VARCHAR(50) NOT NULL, -- e.g., 'Developer', 'Designer', 'Manager'
    hourly_rate NUMERIC(8, 2) CHECK (hourly_rate >= 0)
);

-- Create Tasks Table
CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INT NOT NULL,
    assigned_member_id INT, -- Can be NULL if unassigned
    task_description TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'ToDo', -- e.g., 'ToDo', 'InProgress', 'Done', 'Blocked'
    estimated_hours NUMERIC(5, 2) CHECK (estimated_hours > 0),
    actual_hours NUMERIC(5, 2), -- NULL until completed or logged
    creation_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id),
    FOREIGN KEY (assigned_member_id) REFERENCES team_members(member_id)
);

-- Insert Sample Data into Projects
INSERT INTO projects (project_name, client_name, start_date, deadline) VALUES
('Website Redesign', 'Alpha Corp', '2023-01-15', '2023-06-30'),
('Mobile App Dev', 'Beta LLC', '2023-03-01', '2023-09-30'),
('Data Migration', 'Gamma Inc', '2023-02-01', NULL), -- No deadline
('Marketing Campaign', 'Alpha Corp', '2023-04-10', '2023-07-31');

-- Insert Sample Data into Team Members
INSERT INTO team_members (member_name, email, role, hourly_rate) VALUES
('Neo Anderson', 'neo@matrix.dev', 'Developer', 75.00),
('Trinity Moss', 'trinity@matrix.dev', 'Developer', 80.00),
('Morpheus Fishburne', 'morpheus@oracle.net', 'Manager', 90.00),
('Cypher Reagan', 'cypher@neb.ship', 'Designer', 65.00),
('Tank Dozer', 'tank@operator.io', 'Developer', 70.00);

-- Insert Sample Data into Tasks
INSERT INTO tasks (project_id, assigned_member_id, task_description, status, estimated_hours, actual_hours, creation_date) VALUES
(1, 1, 'Develop homepage layout', 'Done', 20.0, 22.5, '2023-01-20'),
(1, 4, 'Design color palette', 'Done', 8.0, 7.0, '2023-01-18'),
(1, 2, 'Implement user login', 'InProgress', 16.0, NULL, '2023-02-10'),
(2, 1, 'Setup project structure', 'Done', 10.0, 9.5, '2023-03-05'),
(2, 2, 'Develop API endpoints', 'InProgress', 40.0, NULL, '2023-03-15'),
(2, 5, 'Implement push notifications', 'ToDo', 24.0, NULL, '2023-04-01'),
(3, 1, 'Analyze source schema', 'Done', 12.0, 15.0, '2023-02-05'),
(3, 5, 'Develop migration scripts', 'InProgress', 30.0, 10.5, '2023-02-20'), -- Partially logged hours
(3, NULL, 'Perform final data validation', 'ToDo', 16.0, NULL, '2023-03-01'), -- Unassigned task
(4, 4, 'Create ad visuals', 'ToDo', 25.0, NULL, '2023-04-12'),
(1, 5, 'Fix CSS bugs', 'ToDo', 8.0, NULL, '2023-04-15');

Tasks:

Count the total number of active projects.
Calculate the sum of estimated_hours for all tasks across all projects.
Find the average hourly_rate for members with the ‘Developer’ role.
Determine the earliest project start_date and the latest project deadline (ignore NULL deadlines).
Count the number of distinct team member roles.
Calculate the number of tasks assigned to each team member (ignore unassigned tasks). Display member_id and task_count. Order by task_count descending.
Find the total actual_hours logged for each project. Display project_id and total_actual_hours. Only include tasks where actual_hours is not NULL.
Calculate the average estimated_hours for tasks grouped by their status. Display status and average_estimated_hours.
Find projects that have more than 3 tasks assigned to them. Display project_id and number_of_tasks.
Find team members who have logged (actual_hours) more than 20 hours in total across all their assigned tasks. Display member_id, member_name, and total_logged_hours.

Variant 4: E-commerce Product Reviews Analysis

Scenario: You manage an e-commerce platform and need to analyze product ratings and review activity using SQL aggregate functions.

Schema:

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

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

-- Create Products Table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    category VARCHAR(50) NOT NULL,
    brand VARCHAR(50),
    price NUMERIC(10, 2) CHECK (price > 0),
    launch_date DATE
);

-- Create Reviews Table
CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5), -- Star rating (1 to 5)
    review_text TEXT,
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Insert Sample Data into Users
INSERT INTO users (username, email, join_date, country) VALUES
('Reviewer1', 'rev1@email.com', '2022-05-10', 'USA'),
('GadgetFan', 'gf@email.com', '2021-11-20', 'Canada'),
('Bookworm', 'bw@email.com', '2023-01-05', 'UK'),
('StyleGuru', 'sg@email.com', '2022-08-15', 'USA'),
('AnonUser', 'anon@email.com', '2023-03-01', NULL); -- NULL country

-- Insert Sample Data into Products
INSERT INTO products (product_name, category, brand, price, launch_date) VALUES
('Super Smartphone X', 'Electronics', 'TechCorp', 799.99, '2022-09-01'),
('Cozy Reading Lamp', 'Home Goods', 'LightUp', 45.50, '2021-05-15'),
('Quantum Computing Explained', 'Books', NULL, 29.95, '2023-02-01'), -- NULL brand
('Running Shoes Pro', 'Apparel', 'FastFeet', 120.00, '2022-03-10'),
('Organic Coffee Beans', 'Groceries', 'BeanCo', 18.75, NULL); -- NULL launch date

-- Insert Sample Data into Reviews
INSERT INTO reviews (product_id, user_id, rating, review_text, review_date, is_verified_purchase) VALUES
(1, 1, 5, 'Amazing phone, great features!', '2022-10-01 10:00:00', TRUE),
(1, 2, 4, 'Good value for money, battery could be better.', '2022-10-05 15:30:00', TRUE),
(2, 3, 5, 'Perfect lamp for my reading nook.', '2023-01-20 09:00:00', TRUE),
(4, 4, 3, 'Comfortable, but wore out faster than expected.', '2022-07-15 11:00:00', FALSE),
(1, 3, 4, 'Solid device.', '2023-02-10 14:20:00', FALSE), -- Bookworm reviewed phone
(3, 3, 5, 'Excellent introduction to a complex topic.', '2023-03-01 18:00:00', TRUE),
(2, 1, 4, NULL, '2023-03-05 12:45:00', TRUE), -- NULL review text
(5, 2, 5, 'Best coffee beans I ve tried!', '2023-04-01 08:00:00', TRUE),
(4, 1, 4, 'Great for running.', '2023-04-10 16:00:00', TRUE),
(1, 4, 4, 'Nice design, performs well.', '2023-04-15 19:30:00', TRUE);

Tasks:

Count the total number of reviews submitted.
Count the number of reviews that have review text (i.e., review_text is not NULL).
Calculate the sum of all ratings provided across all reviews.
Find the average rating for product with product_id = 1 (‘Super Smartphone X’).
Determine the lowest and highest rating given to any product.
Find the earliest and latest review_date recorded in the system.
Calculate the number of reviews submitted for each product. Display product_id and review_count. Order by product_id.
Determine the average rating for each product. Display product_id, average_rating, and number_of_reviews. Order by average_rating descending.
Find users who have submitted more than 2 reviews. Display user_id and total_reviews_submitted.
Find product categories where the average product price is greater than $100. Display category and average_price.

Variant 5: Flight Booking System Analysis

Scenario: You work for an airline analytics company. Your task is to analyze flight data, including capacity, pricing, and routes, using SQL aggregate functions.

Schema:

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


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

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

-- Create Flights Table
CREATE TABLE flights (
    flight_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) NOT NULL,
    airline_id INT NOT NULL,
    origin_airport CHAR(3) NOT NULL,
    destination_airport CHAR(3) NOT NULL,
    departure_datetime TIMESTAMP NOT NULL,
    arrival_datetime TIMESTAMP NOT NULL,
    capacity INT CHECK (capacity > 0),
    base_price NUMERIC(10, 2) CHECK (base_price >= 0),
    FOREIGN KEY (airline_id) REFERENCES airlines(airline_id),
    FOREIGN KEY (origin_airport) REFERENCES airports(airport_code),
    FOREIGN KEY (destination_airport) REFERENCES airports(airport_code)
);

-- Create Bookings Table
CREATE TABLE bookings (
    booking_id SERIAL PRIMARY KEY,
    flight_id INT NOT NULL,
    passenger_name VARCHAR(100) NOT NULL,
    booking_date DATE DEFAULT CURRENT_DATE,
    seat_count INT DEFAULT 1 CHECK (seat_count > 0),
    total_fare NUMERIC(10, 2), -- Can be different from base_price * seat_count due to fees/discounts
    FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
);


-- Insert Sample Data into Airlines
INSERT INTO airlines (airline_name, country) VALUES
('AirConnect', 'USA'),
('EuroFly', 'Germany'),
('AsiaWings', 'Singapore');

-- Insert Sample Data into Airports
INSERT INTO airports (airport_code, airport_name, city, country) VALUES
('JFK', 'John F. Kennedy International', 'New York', 'USA'),
('LAX', 'Los Angeles International', 'Los Angeles', 'USA'),
('LHR', 'London Heathrow', 'London', 'UK'),
('FRA', 'Frankfurt Airport', 'Frankfurt', 'Germany'),
('SIN', 'Singapore Changi', 'Singapore', 'Singapore');

-- Insert Sample Data into Flights
INSERT INTO flights (flight_number, airline_id, origin_airport, destination_airport, departure_datetime, arrival_datetime, capacity, base_price) VALUES
('AC101', 1, 'JFK', 'LAX', '2024-08-01 08:00:00', '2024-08-01 11:30:00', 150, 299.99),
('EF205', 2, 'FRA', 'LHR', '2024-08-01 10:00:00', '2024-08-01 11:30:00', 120, 99.50),
('AW300', 3, 'SIN', 'LHR', '2024-08-02 22:00:00', '2024-08-03 05:00:00', 250, 650.00),
('AC102', 1, 'LAX', 'JFK', '2024-08-05 14:00:00', '2024-08-05 22:30:00', 150, 310.00),
('EF206', 2, 'LHR', 'FRA', '2024-08-06 09:00:00', '2024-08-06 10:30:00', 110, 85.00), -- Lower capacity
('AC550', 1, 'JFK', 'LHR', '2024-08-10 19:00:00', '2024-08-11 07:00:00', 200, 450.00),
('EF410', 2, 'FRA', 'JFK', '2024-08-12 12:00:00', '2024-08-12 15:00:00', 180, NULL); -- NULL base_price

-- Insert Sample Data into Bookings
INSERT INTO bookings (flight_id, passenger_name, seat_count, total_fare, booking_date) VALUES
(1, 'Alice Smith', 1, 320.50, '2024-06-15'),
(1, 'Bob Johnson', 2, 641.00, '2024-06-18'), -- 2 seats booked
(2, 'Charlie Williams', 1, 99.50, '2024-07-01'),
(3, 'Diana Brown', 1, 700.00, '2024-05-20'),
(4, 'Ethan Jones', 1, 310.00, '2024-07-10'),
(1, 'Fiona Garcia', 1, 325.00, '2024-07-12'), -- Another booking for flight 1
(5, 'George Miller', 3, 255.00, '2024-07-20'), -- 3 seats booked
(6, 'Hannah Davis', 1, 480.00, '2024-07-25'),
(2, 'Ian Rodriguez', 1, 105.00, '2024-07-28'); -- Another booking for flight 2

Tasks:

Count the total number of flights scheduled.
Count the number of flights that have a base_price listed (not NULL).
Calculate the total capacity across all flights offered by airline with airline_id = 1 (‘AirConnect’).
Find the average base_price for all flights originating from ‘JFK’ (ignore flights with NULL base_price).
Determine the minimum and maximum flight capacity recorded.
Find the earliest flight departure_datetime and the latest flight arrival_datetime.
Calculate the total number of seats booked for each flight. Display flight_id and total_seats_booked. Order by flight_id. (Hint: Use SUM on seat_count).
Find the number of flights operated by each airline. Display airline_id and num_flights.
Find flights where the total number of booked seats is greater than 1. Display flight_id and total_seats_booked. (Hint: Use the result from task 7 with HAVING).
Calculate the average total_fare per seat for each flight based on the bookings. Display flight_id and average_fare_per_seat. Be mindful of potential division by zero if a flight has bookings but 0 total seats (though unlikely with the schema constraint).

Variant 6: Restaurant Order Management Analysis

Scenario: You are analyzing data for a chain of restaurants to understand sales patterns, popular items, and order values using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS restaurants;

-- Create Restaurants Table
CREATE TABLE restaurants (
    restaurant_id SERIAL PRIMARY KEY,
    restaurant_name VARCHAR(100) NOT NULL,
    cuisine VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    opening_year INT
);

-- Create Menu Items Table
CREATE TABLE menu_items (
    item_id SERIAL PRIMARY KEY,
    restaurant_id INT NOT NULL, -- Item belongs to a specific restaurant chain location, assume items can exist at multiple locations but are distinct rows if prices differ.
    item_name VARCHAR(100) NOT NULL,
    category VARCHAR(50), -- e.g., 'Appetizer', 'Main Course', 'Dessert', 'Beverage'
    price NUMERIC(8, 2) CHECK (price >= 0),
    is_vegetarian BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
);

-- Create Orders Table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    restaurant_id INT NOT NULL,
    customer_name VARCHAR(100), -- Optional customer name
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    order_type VARCHAR(20) DEFAULT 'Dine-In', -- e.g., 'Dine-In', 'Takeaway', 'Delivery'
    total_amount NUMERIC(10, 2), -- This might be calculated or pre-calculated
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
);

-- Create Order Details Table
CREATE TABLE order_details (
    order_detail_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity INT CHECK (quantity > 0),
    price_per_item NUMERIC(8, 2), -- Price at the time of order
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (item_id) REFERENCES menu_items(item_id)
);

-- Insert Sample Data into Restaurants
INSERT INTO restaurants (restaurant_name, cuisine, city, opening_year) VALUES
('Pasta Palace', 'Italian', 'New York', 2018),
('Curry Corner', 'Indian', 'London', 2020),
('Burger Barn', 'American', 'New York', 2019),
('Sushi Spot', 'Japanese', 'London', NULL); -- NULL opening_year

-- Insert Sample Data into Menu Items
-- Pasta Palace Items
INSERT INTO menu_items (restaurant_id, item_name, category, price, is_vegetarian) VALUES
(1, 'Spaghetti Carbonara', 'Main Course', 18.50, FALSE),
(1, 'Margherita Pizza', 'Main Course', 15.00, TRUE),
(1, 'Tiramisu', 'Dessert', 8.00, TRUE),
-- Curry Corner Items
(2, 'Chicken Tikka Masala', 'Main Course', 16.00, FALSE),
(2, 'Vegetable Korma', 'Main Course', 14.50, TRUE),
(2, 'Naan Bread', 'Side', 3.50, TRUE),
-- Burger Barn Items
(3, 'Classic Cheeseburger', 'Main Course', 12.00, FALSE),
(3, 'Veggie Burger', 'Main Course', 11.50, TRUE),
(3, 'French Fries', 'Side', 4.00, TRUE),
-- Sushi Spot Items (associated with restaurant 4)
(4, 'Salmon Nigiri', 'Sushi', 5.00, FALSE),
(4, 'Avocado Roll', 'Sushi', 6.50, TRUE);

-- Insert Sample Data into Orders
INSERT INTO orders (restaurant_id, customer_name, order_time, order_type, total_amount) VALUES
(1, 'Alice', '2023-10-01 19:30:00', 'Dine-In', 42.00), -- Order for items 1 & 3
(2, 'Bob', '2023-10-02 13:00:00', 'Takeaway', 19.50), -- Order for items 4 & 6
(3, 'Charlie', '2023-10-02 18:00:00', 'Dine-In', 27.50), -- Order for items 7 & 9 (2x fries)
(1, NULL, '2023-10-03 20:00:00', 'Delivery', 15.00), -- Order for item 2
(2, 'Diana', '2023-10-04 12:30:00', 'Dine-In', 14.50), -- Order for item 5
(4, 'Ethan', '2023-10-05 14:00:00', 'Takeaway', 11.50); -- Order for items 10 & 11

-- Insert Sample Data into Order Details
INSERT INTO order_details (order_id, item_id, quantity, price_per_item) VALUES
(1, 1, 1, 18.50), -- Spaghetti
(1, 3, 1, 8.00),  -- Tiramisu (Order 1 Total: 26.50 - Mistake in orders.total_amount sample, ignore for aggregation tasks)
(2, 4, 1, 16.00), -- Chicken Tikka
(2, 6, 1, 3.50),  -- Naan (Order 2 Total: 19.50)
(3, 7, 1, 12.00), -- Cheeseburger
(3, 9, 2, 4.00),  -- 2x Fries (Order 3 Total: 12 + 2*4 = 20.00 - Mistake in orders.total_amount sample)
(4, 2, 1, 15.00), -- Margherita Pizza (Order 4 Total: 15.00)
(5, 5, 1, 14.50), -- Veg Korma (Order 5 Total: 14.50)
(6, 10, 1, 5.00), -- Salmon Nigiri
(6, 11, 1, 6.50); -- Avocado Roll (Order 6 Total: 11.50)

Tasks:

Count the total number of menu items offered across all restaurants.
Count the number of menu items that are marked as vegetarian (is_vegetarian = TRUE).
Calculate the sum of quantity for all items sold across all orders (total items sold). (Hint: Use order_details).
Find the average price of ‘Main Course’ items across all restaurants.
Determine the minimum and maximum price for any menu item.
Find the earliest and latest order_time recorded.
Calculate the total number of items ordered for each order. Display order_id and total_items. (Hint: Sum quantity in order_details, grouped by order_id).
Find the total number of menu items offered by each restaurant. Display restaurant_id and item_count.
Find menu item categories where the average price per item is greater than $10. Display category and average_category_price.
Find orders that contain more than 2 items in total (sum of quantities). Display order_id and total_items_in_order. (Hint: Use the logic from task 7 with HAVING).

Variant 7: Social Media Post Engagement Analysis

Scenario: You are analyzing user engagement data for a social media platform. You need to understand post activity, likes, and user interaction patterns using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS users_sm; -- Renamed to avoid conflict if 'users' table exists

-- Create Users Table
CREATE TABLE users_sm (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    country_code CHAR(2) -- e.g., 'US', 'GB', 'DE'
);

-- Create Posts Table
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    post_content TEXT, -- Can be NULL if it's just an image/video post conceptually
    post_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    location_tag VARCHAR(100), -- e.g., 'New York, NY', NULL
    FOREIGN KEY (user_id) REFERENCES users_sm(user_id)
);

-- Create Likes Table
CREATE TABLE likes (
    like_id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL, -- User who liked the post
    like_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (user_id) REFERENCES users_sm(user_id),
    UNIQUE(post_id, user_id) -- A user can only like a post once
);

-- Insert Sample Data into Users
INSERT INTO users_sm (username, registration_date, country_code) VALUES
('SocialButterfly', '2022-01-10', 'US'),
('PhotoMaster', '2021-11-05', 'CA'),
('TravelBug', '2022-03-20', 'GB'),
('CodeNinja', '2020-08-15', 'US'),
('FoodieFan', '2023-02-28', NULL); -- NULL country

-- Insert Sample Data into Posts
INSERT INTO posts (user_id, post_content, post_timestamp, location_tag) VALUES
(1, 'Having a great time at the park! #sunnyday', '2023-05-01 14:30:00', 'Central Park, NY'),
(2, 'Golden hour shot from the mountains.', '2023-05-02 18:00:00', 'Rocky Mountains'),
(1, 'Loving this new cafe downtown.', '2023-05-03 11:00:00', NULL),
(3, 'Exploring the streets of London.', '2023-05-05 09:15:00', 'London, UK'),
(4, NULL, '2023-05-10 16:00:00', NULL), -- Post without text content
(1, 'My thoughts on the latest tech trends.', '2023-05-12 10:00:00', NULL),
(5, 'Delicious brunch today!', '2023-05-14 12:45:00', 'The Brunch Spot');

-- Insert Sample Data into Likes
INSERT INTO likes (post_id, user_id, like_timestamp) VALUES
(1, 2, '2023-05-01 15:00:00'), -- PhotoMaster liked SocialButterfly's post
(1, 3, '2023-05-01 16:30:00'), -- TravelBug liked SocialButterfly's post
(2, 1, '2023-05-02 19:00:00'), -- SocialButterfly liked PhotoMaster's post
(2, 4, '2023-05-03 08:00:00'), -- CodeNinja liked PhotoMaster's post
(4, 1, '2023-05-05 10:00:00'),
(4, 2, '2023-05-05 11:00:00'),
(4, 5, '2023-05-06 14:00:00'), -- FoodieFan liked TravelBug's post
(3, 5, '2023-05-14 13:00:00'), -- FoodieFan liked SocialButterfly's cafe post
(6, 4, '2023-05-12 11:00:00'), -- CodeNinja liked SocialButterfly's tech post
(7, 1, '2023-05-14 13:30:00'); -- SocialButterfly liked FoodieFan's post

Tasks:

Count the total number of registered users.
Count the total number of posts created.
Count the number of posts that have textual content (i.e., post_content is NOT NULL).
Count the total number of likes given across all posts.
Find the earliest and latest post_timestamp.
Calculate the total number of posts created by each user. Display user_id and post_count. Order by post_count descending.
Calculate the total number of likes received for each post. Display post_id and like_count. Order by post_id.
Find the average number of likes per post (consider only posts that received at least one like).
Find users who have created more than 1 post. Display user_id and number_of_posts.
Find posts that have received more than 2 likes. Display post_id and number_of_likes.

Variant 8: Music Streaming Service Analytics

Scenario: You are working for a music streaming service and need to analyze data about artists, albums, tracks, and user listening activity using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS user_listens;
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(100) UNIQUE NOT NULL,
    genre VARCHAR(50) NOT NULL,
    country VARCHAR(50)
);

-- Create Albums Table
CREATE TABLE albums (
    album_id SERIAL PRIMARY KEY,
    artist_id INT NOT NULL,
    album_title VARCHAR(150) NOT NULL,
    release_year INT CHECK (release_year > 1900),
    label VARCHAR(100),
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

-- Create Tracks Table
CREATE TABLE tracks (
    track_id SERIAL PRIMARY KEY,
    album_id INT NOT NULL,
    track_title VARCHAR(150) NOT NULL,
    duration_seconds INT CHECK (duration_seconds > 0), -- Duration in seconds
    track_number INT CHECK (track_number > 0), -- Position on the album
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

-- Create User Listens Table
CREATE TABLE user_listens (
    listen_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL, -- Assuming a user table exists elsewhere, just using ID here
    track_id INT NOT NULL,
    listen_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (track_id) REFERENCES tracks(track_id)
);


-- Insert Sample Data into Artists
INSERT INTO artists (artist_name, genre, country) VALUES
('The Rockers', 'Rock', 'USA'),
('SynthWave Masters', 'Electronic', 'UK'),
('Jazz Cats', 'Jazz', 'USA'),
('Global Beats', 'World', NULL); -- NULL country

-- Insert Sample Data into Albums
INSERT INTO albums (artist_id, album_title, release_year, label) VALUES
(1, 'Rock On', 2020, 'Rock Records'),
(2, 'Neon Nights', 2021, 'Synth Sounds'),
(1, 'Acoustic Sessions', 2022, 'Rock Records'),
(3, 'Midnight Jazz', 2019, 'Jazz Label'),
(4, 'World Rhythms', 2023, NULL); -- NULL label

-- Insert Sample Data into Tracks
INSERT INTO tracks (album_id, track_title, duration_seconds, track_number) VALUES
(1, 'Highway Song', 245, 1),
(1, 'Rock Anthem', 300, 2),
(2, 'City Lights', 280, 1),
(2, 'Retro Drive', 320, 2),
(3, 'Quiet Fire', 180, 1),
(3, 'Unplugged Melody', 210, 2),
(4, 'Smooth Sax', 350, 1),
(4, 'Late Night Blues', 400, 2),
(5, 'Desert Mirage', 290, 1),
(5, 'Jungle Groove', 260, 2);

-- Insert Sample Data into User Listens
INSERT INTO user_listens (user_id, track_id, listen_timestamp) VALUES
(101, 1, '2023-06-01 10:00:00'),
(102, 3, '2023-06-01 11:30:00'),
(101, 2, '2023-06-02 09:00:00'),
(103, 7, '2023-06-03 15:00:00'),
(101, 1, '2023-06-04 10:30:00'), -- User 101 listened to track 1 again
(102, 4, '2023-06-05 16:00:00'),
(104, 9, '2023-06-06 12:00:00'),
(103, 8, '2023-06-07 18:45:00'),
(101, 5, '2023-06-08 14:00:00'),
(102, 3, '2023-06-09 10:15:00'); -- User 102 listened to track 3 again

Tasks:

Count the total number of artists in the database.
Count the total number of albums released.
Count the number of tracks with a duration longer than 300 seconds (5 minutes).
Calculate the total number of times any track has been listened to (total rows in user_listens).
Find the minimum and maximum track duration_seconds.
Find the earliest and latest album release_year.
Calculate the number of tracks on each album. Display album_id, album_title, and track_count.
Find the average track duration (in seconds) for each artist. Display artist_id, artist_name, and average_duration.
Find users (user_id) who have listened to more than 2 tracks. Display user_id and listen_count.
Find artists who have released albums where the average track duration on those albums is greater than 250 seconds. Display artist_id, artist_name, album_id, and average_album_track_duration. (Hint: Group by artist and album).

Variant 9: Library Management System Analysis

Scenario: You are a librarian analyzing data about the library’s collection, members, and borrowing history using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS books_lib; -- Renamed to avoid conflict
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS members;


-- Create Authors Table
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    nationality VARCHAR(50)
);

-- Create Books Table
CREATE TABLE books_lib (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT NOT NULL,
    isbn VARCHAR(20) UNIQUE, -- International Standard Book Number
    genre VARCHAR(50),
    publication_year INT,
    copies_available INT DEFAULT 1 CHECK (copies_available >= 0),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

-- Create Members Table
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100) NOT NULL,
    join_date DATE DEFAULT CURRENT_DATE,
    membership_type VARCHAR(20) DEFAULT 'Standard' -- e.g., 'Standard', 'Student', 'Premium'
);

-- Create Loans Table
CREATE TABLE loans (
    loan_id SERIAL PRIMARY KEY,
    book_id INT NOT NULL,
    member_id INT NOT NULL,
    loan_date DATE DEFAULT CURRENT_DATE,
    due_date DATE NOT NULL,
    return_date DATE, -- NULL if not yet returned
    FOREIGN KEY (book_id) REFERENCES books_lib(book_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);


-- Insert Sample Data into Authors
INSERT INTO authors (author_name, nationality) VALUES
('Jane Reader', 'British'),
('Leo Writer', 'American'),
('Ken Follett', 'British'), -- Real author for relatability
('Haruki Murakami', 'Japanese'); -- Real author

-- Insert Sample Data into Books
INSERT INTO books_lib (title, author_id, isbn, genre, publication_year, copies_available) VALUES
('The Secret Garden', 1, '978-0141321066', 'Childrens', 1911, 3),
('War and Peace', 2, '978-0199232765', 'Historical Fiction', 1869, 2),
('The Pillars of the Earth', 3, '978-0451488336', 'Historical Fiction', 1989, 5),
('Norwegian Wood', 4, '978-0375704024', 'Fiction', 1987, 4),
('Another Story', 1, '978-1234567890', 'Fiction', 2010, 2),
('Space Adventures', 2, '978-0987654321', NULL, 2022, 1), -- NULL genre
('Kafka on the Shore', 4, '978-1400079278', 'Fiction', 2002, 0); -- 0 copies available

-- Insert Sample Data into Members
INSERT INTO members (member_name, join_date, membership_type) VALUES
('Alice Borrower', '2022-01-15', 'Standard'),
('Bob Student', '2023-09-01', 'Student'),
('Charlie Reader', '2021-05-20', 'Premium'),
('Diana Frequent', '2022-11-10', 'Standard');

-- Insert Sample Data into Loans
INSERT INTO loans (book_id, member_id, loan_date, due_date, return_date) VALUES
(1, 1, '2023-10-01', '2023-10-15', '2023-10-14'), -- Returned early
(3, 2, '2023-10-05', '2023-11-05', NULL), -- Still on loan
(4, 1, '2023-10-10', '2023-10-24', NULL), -- Still on loan
(2, 3, '2023-09-15', '2023-10-15', '2023-10-18'), -- Returned late
(5, 4, '2023-11-01', '2023-11-15', NULL),
(1, 3, '2023-11-02', '2023-11-16', NULL), -- Book 1 loaned again
(3, 1, '2023-11-05', '2023-12-05', NULL); -- Book 3 loaned again

Tasks:

Count the total number of books in the library catalog.
Count the number of books that currently have 0 copies_available.
Calculate the total number of copies available across all books in the library.
Find the average publication_year for books in the ‘Historical Fiction’ genre.
Determine the minimum and maximum publication_year found in the book collection.
Find the earliest and latest member join_date.
Calculate the total number of books written by each author. Display author_id, author_name, and book_count.
Find the number of times each book has been loaned out. Display book_id, title, and loan_count.
Find members who have borrowed more than 1 book (either currently or previously). Display member_id, member_name, and total_loans.
Find book genres where the total number of available copies across all books in that genre is greater than 4. Display genre and total_available_copies.

Variant 10: Healthcare Clinic Patient Visits Analysis

Scenario: You work for a healthcare clinic and need to analyze patient visit data to understand doctor workloads, patient demographics, and visit patterns using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS visits;
DROP TABLE IF EXISTS doctors;
DROP TABLE IF EXISTS patients;

-- Create Patients Table
CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender CHAR(1), -- 'M', 'F', 'O' (Other)
    contact_number VARCHAR(20) UNIQUE
);

-- Create Doctors Table
CREATE TABLE doctors (
    doctor_id SERIAL PRIMARY KEY,
    doctor_name VARCHAR(100) NOT NULL,
    specialty VARCHAR(100) NOT NULL,
    hire_date DATE,
    office_number VARCHAR(10)
);

-- Create Visits Table
CREATE TABLE visits (
    visit_id SERIAL PRIMARY KEY,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    visit_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reason_for_visit TEXT,
    diagnosis VARCHAR(255),
    visit_duration_minutes INT CHECK (visit_duration_minutes > 0), -- Estimated or actual duration
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

-- Insert Sample Data into Patients
INSERT INTO patients (first_name, last_name, date_of_birth, gender, contact_number) VALUES
('Peter', 'Pan', '2010-05-10', 'M', '555-1111'),
('Wendy', 'Darling', '2008-11-20', 'F', '555-2222'),
('James', 'Hook', '1975-02-15', 'M', '555-3333'),
('Tinker', 'Bell', '2015-07-01', 'F', NULL), -- NULL contact
('John', 'Darling', '2011-03-25', 'M', '555-4444');

-- Insert Sample Data into Doctors
INSERT INTO doctors (doctor_name, specialty, hire_date, office_number) VALUES
('Dr. House', 'Diagnostics', '2005-09-16', '221B'),
('Dr. Watson', 'General Practice', '2010-01-20', '101A'),
('Dr. Grey', 'Surgery', '2008-07-01', 'OR-3'),
('Dr. Strange', 'Neurology', '2016-11-04', 'SANCTUM');

-- Insert Sample Data into Visits
INSERT INTO visits (patient_id, doctor_id, visit_date, reason_for_visit, diagnosis, visit_duration_minutes) VALUES
(1, 2, '2023-01-10 09:00:00', 'Annual Checkup', 'Healthy', 30),
(2, 2, '2023-01-12 10:30:00', 'Sore Throat', 'Strep Throat', 20),
(3, 1, '2023-01-15 11:00:00', 'Leg Pain', 'Gout', 45),
(1, 2, '2023-02-05 14:00:00', 'Follow-up', 'Recovered', 15),
(4, 3, '2023-02-10 08:15:00', 'Consultation', 'Pre-op assessment', 60),
(5, 2, '2023-02-20 09:45:00', 'Fever', 'Influenza', 25),
(3, 4, '2023-03-01 13:00:00', 'Headaches', 'Migraine', 50),
(2, 2, '2023-03-10 10:00:00', 'Annual Checkup', 'Healthy', 30),
(1, 1, '2023-04-01 15:30:00', 'Rash', 'Allergic Reaction', 40),
(3, 1, '2023-04-05 16:00:00', 'Follow-up', 'Condition stable', 20);

Tasks:

Count the total number of registered patients.
Calculate the total number of visits recorded across all patients and doctors.
Find the average visit_duration_minutes for all visits.
Determine the date of the earliest and latest recorded visits.
Count the number of distinct doctor specialties available at the clinic.
Calculate the total number of visits handled by each doctor. Display doctor_id, doctor_name (requires a join, but focus on aggregate on visits table for this exercise, so just doctor_id), and visit_count. Order by visit_count descending.
Find the average visit_duration_minutes for each doctor specialty. Display specialty (requires join, but focus on aggregate first - students might group by doctor_id then look up specialty, or do the join) and average_duration.
Calculate the minimum and maximum visit_duration_minutes for visits associated with ‘Dr. House’ (doctor_id = 1).
Find patients who have visited the clinic more than twice. Display patient_id and number_of_visits.
Find doctor specialties where the average visit duration is longer than 35 minutes. Display the specialty (requires join) and the average_duration.

Variant 11: Real Estate Listings Analysis

Scenario: You are working for a real estate agency. Analyze property listing data, agent performance, and market trends using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS listings;
DROP TABLE IF EXISTS properties;
DROP TABLE IF EXISTS agents;
DROP TABLE IF EXISTS locations;


-- Create Locations Table
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    city VARCHAR(50) NOT NULL,
    state CHAR(2) NOT NULL,
    zip_code VARCHAR(10) NOT NULL UNIQUE
);

-- Create Agents Table
CREATE TABLE agents (
    agent_id SERIAL PRIMARY KEY,
    agent_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    hire_date DATE NOT NULL
);

-- Create Properties Table
CREATE TABLE properties (
    property_id SERIAL PRIMARY KEY,
    location_id INT NOT NULL,
    address VARCHAR(255) NOT NULL,
    property_type VARCHAR(50) NOT NULL, -- e.g., 'Single Family', 'Condo', 'Townhouse'
    year_built INT,
    bedrooms INT CHECK (bedrooms > 0),
    bathrooms NUMERIC(3,1) CHECK (bathrooms > 0), -- e.g., 2.5
    square_footage INT CHECK (square_footage > 0),
    FOREIGN KEY (location_id) REFERENCES locations(location_id)
);

-- Create Listings Table
CREATE TABLE listings (
    listing_id SERIAL PRIMARY KEY,
    property_id INT NOT NULL UNIQUE, -- Assuming a property has only one active listing at a time
    agent_id INT NOT NULL,
    listing_price NUMERIC(12, 2) CHECK (listing_price > 0),
    listing_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'Active', -- e.g., 'Active', 'Pending', 'Sold', 'Withdrawn'
    sold_price NUMERIC(12, 2) CHECK (sold_price > 0), -- Can be NULL if not sold
    sold_date DATE, -- Can be NULL if not sold
    FOREIGN KEY (property_id) REFERENCES properties(property_id),
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id)
);


-- Insert Sample Data into Locations
INSERT INTO locations (city, state, zip_code) VALUES
('Metropolis', 'NY', '10001'),
('Gotham', 'NJ', '07030'),
('Star City', 'CA', '90210'),
('Central City', 'MO', '63005');

-- Insert Sample Data into Agents
INSERT INTO agents (agent_name, email, phone, hire_date) VALUES
('Clark Kent', 'ckent@dailyplanet.com', '555-SUPER', '2018-06-01'),
('Bruce Wayne', 'bwayne@wayne.ent', '555-BATS', '2015-04-10'),
('Oliver Queen', 'oqueen@queen.inc', '555-ARROW', '2019-11-15'),
('Barry Allen', 'ballen@ccpd.gov', NULL, '2020-01-25'); -- NULL phone

-- Insert Sample Data into Properties
INSERT INTO properties (location_id, address, property_type, year_built, bedrooms, bathrooms, square_footage) VALUES
(1, '1 Superman Plaza', 'Condo', 2005, 3, 2.0, 1800),
(2, '1 Wayne Manor', 'Single Family', 1939, 10, 8.5, 15000),
(3, '1 Queen Mansion', 'Single Family', 1960, 8, 6.0, 12000),
(1, '123 Lois Lane Apt 5B', 'Condo', 1998, 2, 1.0, 950),
(4, '456 Flash Street', 'Townhouse', 2010, 3, 2.5, 1600),
(2, '789 Joker Ave', 'Condo', 1985, NULL, 1.0, 800); -- NULL bedrooms

-- Insert Sample Data into Listings
INSERT INTO listings (property_id, agent_id, listing_price, listing_date, status, sold_price, sold_date) VALUES
(1, 1, 850000.00, '2023-01-10', 'Active', NULL, NULL),
(2, 2, 15000000.00, '2022-11-01', 'Withdrawn', NULL, NULL), -- Withdrawn
(3, 3, 12000000.00, '2023-02-15', 'Active', NULL, NULL),
(4, 1, 475000.00, '2023-03-01', 'Sold', 470000.00, '2023-04-20'), -- Sold
(5, 4, 320000.00, '2023-03-20', 'Pending', NULL, NULL), -- Pending
(6, 2, 250000.00, '2023-04-05', 'Active', NULL, NULL);

Tasks:

Count the total number of properties listed (regardless of status).
Calculate the total listing_price of all ‘Active’ listings.
Find the average listing_price for properties located in ‘Metropolis’ (location_id = 1).
Determine the minimum and maximum listing_price among all ‘Active’ listings.
Count the number of distinct property types listed (e.g., ‘Condo’, ‘Single Family’).
Calculate the number of listings managed by each agent. Display agent_id and listing_count. Order by listing_count descending.
Find the average square_footage for each property_type. Display property_type and avg_sqft. Ignore properties with NULL square footage (though none in sample).
Calculate the total sold_price amount generated by each agent. Display agent_id and total_sales_value. Only include listings that are ‘Sold’.
Find locations (cities) where there are more than 1 property listed (regardless of status). Display location_id and property_count.
Find agents who have listed properties with an average listing_price greater than $1,000,000. Display agent_id and average_listing_price.

Variant 12: Bug Tracking System Analysis

Scenario: You are a project manager or QA lead overseeing software development. Analyze bug tracking data to understand project health, developer workload, and bug trends using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS bugs;
DROP TABLE IF EXISTS projects_tracker; -- Renamed to avoid potential keyword conflict
DROP TABLE IF EXISTS users_tracker; -- Renamed to avoid potential keyword conflict


-- Create Projects Table (using a different name)
CREATE TABLE projects_tracker (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) UNIQUE NOT NULL,
    start_date DATE,
    manager_name VARCHAR(100)
);

-- Create Users Table (for reporters and assignees, using a different name)
CREATE TABLE users_tracker (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    role VARCHAR(50) NOT NULL -- e.g., 'Developer', 'QA', 'Manager'
);

-- Create Bugs Table
CREATE TABLE bugs (
    bug_id SERIAL PRIMARY KEY,
    project_id INT NOT NULL,
    reported_by_user_id INT NOT NULL,
    assigned_to_user_id INT, -- NULL if unassigned
    summary VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'Open', -- e.g., 'Open', 'InProgress', 'Resolved', 'Closed', 'WontFix'
    severity VARCHAR(20) NOT NULL, -- e.g., 'Critical', 'High', 'Medium', 'Low'
    priority INT CHECK (priority BETWEEN 1 AND 5), -- 1 = Highest
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    estimated_effort_hours NUMERIC(4,1), -- e.g., 0.5, 1.0, 4.0, 8.0
    FOREIGN KEY (project_id) REFERENCES projects_tracker(project_id),
    FOREIGN KEY (reported_by_user_id) REFERENCES users_tracker(user_id),
    FOREIGN KEY (assigned_to_user_id) REFERENCES users_tracker(user_id)
);


-- Insert Sample Data into Projects
INSERT INTO projects_tracker (project_name, start_date, manager_name) VALUES
('Zeus', '2022-01-10', 'Hera'),
('Apollo', '2022-06-15', 'Artemis'),
('Poseidon', '2023-02-01', 'Amphitrite');

-- Insert Sample Data into Users
INSERT INTO users_tracker (user_name, email, role) VALUES
('Hercules', 'herc@olympus.dev', 'Developer'),
('Athena', 'athena@wisdom.qa', 'QA'),
('Ares', 'ares@war.dev', 'Developer'),
('Hermes', 'hermes@messenger.ops', 'Manager'),
('Hades', 'hades@under.dev', 'Developer'); -- Developer in the underworld :)

-- Insert Sample Data into Bugs
INSERT INTO bugs (project_id, reported_by_user_id, assigned_to_user_id, summary, status, severity, priority, estimated_effort_hours, created_at) VALUES
(1, 2, 1, 'UI button misaligned on login screen', 'Resolved', 'Low', 4, 1.0, '2023-01-20 10:00:00'),
(1, 2, 1, 'Crash when submitting empty form', 'InProgress', 'Critical', 1, 8.0, '2023-01-25 11:30:00'),
(2, 2, 3, 'Incorrect calculation in reporting module', 'Open', 'High', 2, 12.0, '2023-02-10 14:00:00'),
(1, 4, 1, 'Improve error message clarity', 'Open', 'Low', 5, 2.0, '2023-02-15 09:45:00'),
(3, 2, 5, 'Data corruption on large uploads', 'Open', 'Critical', 1, 20.0, '2023-03-01 16:20:00'),
(2, 3, 3, 'API endpoint returning 500 error', 'InProgress', 'High', 2, 6.0, '2023-03-05 12:00:00'),
(1, 2, NULL, 'Typo in user documentation', 'Open', 'Low', 5, 0.5, '2023-03-10 18:00:00'), -- Unassigned
(3, 2, 5, 'Slow performance on main dashboard', 'Open', 'Medium', 3, 16.0, '2023-04-01 11:00:00'),
(2, 1, 3, 'Add unit tests for payment gateway', 'Open', 'Medium', 3, 24.0, '2023-04-15 15:00:00'); -- Task disguised as bug

Tasks:

Count the total number of bugs reported across all projects.
Calculate the total estimated_effort_hours for all bugs currently in ‘Open’ status.
Find the average estimated_effort_hours for bugs with ‘Critical’ severity.
Determine the timestamp (created_at) of the first and the most recently reported bug.
Count the number of distinct bug statuses currently in use (e.g., ‘Open’, ‘InProgress’).
Calculate the number of bugs assigned to each user (assigned_to_user_id). Display user_id and assigned_bug_count. Ignore NULL assignments. Order by count descending.
Find the total number of bugs reported for each project. Display project_id and total_bugs_reported.
Calculate the average priority for bugs grouped by their severity. Display severity and average_priority.
Find projects that have more than 3 bugs currently open. Display project_id and open_bug_count.
Find users (assignees) who are assigned bugs with a total estimated_effort_hours greater than 20 hours. Display assigned_to_user_id and total_assigned_effort.

Variant 13: Event Ticketing System Analysis

Scenario: You are working for an event management company that sells tickets online. Analyze data related to events, venues, and ticket sales using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS ticket_sales;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS venues;
DROP TABLE IF EXISTS customers_ticketing; -- Renamed to avoid conflicts

-- Create Venues Table
CREATE TABLE venues (
    venue_id SERIAL PRIMARY KEY,
    venue_name VARCHAR(100) NOT NULL UNIQUE,
    city VARCHAR(50) NOT NULL,
    capacity INT CHECK (capacity > 0)
);

-- Create Events Table
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(150) NOT NULL,
    venue_id INT NOT NULL,
    event_date DATE NOT NULL,
    category VARCHAR(50), -- e.g., 'Music', 'Sports', 'Theater', 'Conference'
    base_ticket_price NUMERIC(8, 2) CHECK (base_ticket_price >= 0),
    FOREIGN KEY (venue_id) REFERENCES venues(venue_id)
);

-- Create Customers Table (for ticketing system)
CREATE TABLE customers_ticketing (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100), -- Simplified for this example
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Create Ticket Sales Table
CREATE TABLE ticket_sales (
    sale_id SERIAL PRIMARY KEY,
    event_id INT NOT NULL,
    customer_id INT NOT NULL,
    purchase_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    num_tickets INT CHECK (num_tickets > 0),
    total_amount NUMERIC(10, 2) NOT NULL, -- Could include booking fees, etc.
    ticket_type VARCHAR(30) DEFAULT 'General Admission', -- e.g., 'VIP', 'Early Bird'
    FOREIGN KEY (event_id) REFERENCES events(event_id),
    FOREIGN KEY (customer_id) REFERENCES customers_ticketing(customer_id)
);

-- Insert Sample Data into Venues
INSERT INTO venues (venue_name, city, capacity) VALUES
('Grand Arena', 'New York', 20000),
('Melody Hall', 'Los Angeles', 5000),
('Convention Center', 'Chicago', 50000),
('Open Air Stage', 'Austin', 15000);

-- Insert Sample Data into Events
INSERT INTO events (event_name, venue_id, event_date, category, base_ticket_price) VALUES
('Rock Legends Concert', 1, '2024-07-20', 'Music', 85.00),
('Tech Summit 2024', 3, '2024-09-15', 'Conference', 499.00),
('Knights vs Dragons Championship', 1, '2024-08-10', 'Sports', 60.00),
('Shakespeare in the Park', 4, '2024-07-05', 'Theater', 30.00),
('Indie Music Fest', 2, '2024-08-25', 'Music', 55.00),
('AI Symposium', 3, '2024-10-01', 'Conference', 350.00); -- Another conference

-- Insert Sample Data into Customers
INSERT INTO customers_ticketing (customer_name, email) VALUES
('Alice Cooper', 'alice@rock.com'),
('Bob Marley', 'bob@reggae.org'),
('Charlie Parker', 'charlie@jazz.net'),
('Diana Ross', 'diana@motown.com');

-- Insert Sample Data into Ticket Sales
INSERT INTO ticket_sales (event_id, customer_id, num_tickets, total_amount, ticket_type, purchase_timestamp) VALUES
(1, 1, 2, 175.50, 'General Admission', '2024-05-10 10:00:00'),
(2, 3, 1, 499.00, 'Early Bird', '2024-05-15 11:30:00'),
(3, 2, 4, 250.00, 'General Admission', '2024-06-01 14:00:00'),
(1, 4, 1, 90.00, 'General Admission', '2024-06-05 09:45:00'), -- Same event, different customer
(5, 1, 2, 115.00, 'General Admission', '2024-06-10 16:20:00'),
(2, 4, 1, 525.00, 'Standard', '2024-06-20 12:00:00'), -- Same event, different customer/price
(4, 3, 2, 65.00, 'General Admission', '2024-06-25 18:00:00'),
(1, 2, 2, 350.00, 'VIP', '2024-07-01 10:15:00'), -- VIP tickets for event 1
(6, 1, 1, 350.00, 'Standard', '2024-07-02 11:00:00');

Tasks:

Count the total number of venues available.
Calculate the total capacity across all venues.
Find the average base_ticket_price for events in the ‘Music’ category.
Determine the minimum and maximum base_ticket_price across all events.
Count the number of distinct event categories.
Calculate the total number of tickets (num_tickets) sold for each event. Display event_id and total_tickets_sold. Order by total_tickets_sold descending.
Find the total sales amount (total_amount) generated for each event category. Display category (requires join, but aggregate first) and total_revenue.
Calculate the average number of tickets purchased per sale (AVG(num_tickets)).
Find events where more than 3 tickets have been sold in total (across all sales). Display event_id and total_tickets_sold.
Find ticket types (ticket_type) for which the average total_amount per sale is greater than $100. Display ticket_type and average_sale_amount.

Variant 14: Manufacturing Inventory Analysis

Scenario: You are responsible for managing inventory in a manufacturing plant. Analyze data about components, suppliers, and stock levels using SQL aggregate functions.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS inventory_log;
DROP TABLE IF EXISTS components;
DROP TABLE IF EXISTS suppliers;

-- Create Suppliers Table
CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    supplier_name VARCHAR(100) NOT NULL UNIQUE,
    country VARCHAR(50),
    contact_person VARCHAR(100),
    rating INT CHECK (rating BETWEEN 1 AND 5) -- Supplier reliability rating
);

-- Create Components Table
CREATE TABLE components (
    component_id SERIAL PRIMARY KEY,
    component_name VARCHAR(100) NOT NULL,
    description TEXT,
    unit_cost NUMERIC(10, 4) CHECK (unit_cost > 0),
    category VARCHAR(50) -- e.g., 'Electronics', 'Mechanical', 'Fasteners'
);

-- Create Inventory Log Table (Tracks stock levels over time or adjustments)
CREATE TABLE inventory_log (
    log_id SERIAL PRIMARY KEY,
    component_id INT NOT NULL,
    supplier_id INT, -- NULL if internally produced or source unknown
    log_date DATE DEFAULT CURRENT_DATE,
    transaction_type VARCHAR(10) NOT NULL, -- 'IN', 'OUT', 'ADJUST'
    quantity INT NOT NULL, -- Positive for IN/ADJUST+, Negative for OUT/ADJUST-
    current_stock_level INT, -- Optional: Snapshot of stock after transaction
    notes TEXT,
    FOREIGN KEY (component_id) REFERENCES components(component_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

-- Insert Sample Data into Suppliers
INSERT INTO suppliers (supplier_name, country, contact_person, rating) VALUES
('Acme Components', 'USA', 'Wile E. Coyote', 3),
('Global Parts Inc.', 'Germany', 'Hans Gruber', 5),
('Fasteners Unlimited', 'USA', 'Penny Pincher', 4),
('Micro Circuits Ltd.', 'Japan', 'Kenji Tanaka', 5);

-- Insert Sample Data into Components
INSERT INTO components (component_name, description, unit_cost, category) VALUES
('Resistor 10k Ohm', 'Standard 1/4W resistor', 0.0150, 'Electronics'),
('Capacitor 100uF', 'Electrolytic capacitor', 0.0500, 'Electronics'),
('M3x10mm Screw', 'Stainless steel machine screw', 0.0200, 'Fasteners'),
('Gear Assembly A', 'Small plastic gear assembly', 1.5000, 'Mechanical'),
('Microcontroller X1', '8-bit MCU', 2.7500, 'Electronics');

-- Insert Sample Data into Inventory Log (Simplified: only showing additions/initial stock)
-- In reality, this would track IN and OUT continuously. We'll use positive quantity for simplicity here.
INSERT INTO inventory_log (component_id, supplier_id, log_date, transaction_type, quantity, current_stock_level) VALUES
(1, 4, '2023-01-15', 'IN', 10000, 10000),
(2, 4, '2023-01-15', 'IN', 5000, 5000),
(3, 3, '2023-01-20', 'IN', 20000, 20000),
(4, 1, '2023-02-01', 'IN', 500, 500),
(5, 4, '2023-02-10', 'IN', 1000, 1000),
(1, 4, '2023-03-01', 'IN', 5000, 15000), -- Received more resistors
(3, 3, '2023-03-05', 'IN', 10000, 30000), -- Received more screws
(2, 4, '2023-04-01', 'OUT', -2000, 3000), -- Used capacitors
(4, 1, '2023-04-10', 'OUT', -100, 400),  -- Used gear assemblies
(5, 4, '2023-04-15', 'OUT', -300, 700);   -- Used microcontrollers

-- Task Focus: Analyzing components and maybe the *net* effect from the log for simplicity.
-- Let's add a simple Current Stock table for easier aggregation exercises, reflecting final state.

DROP TABLE IF EXISTS current_stock;
CREATE TABLE current_stock (
    component_id INT PRIMARY KEY,
    quantity_on_hand INT NOT NULL CHECK (quantity_on_hand >= 0),
    last_updated DATE,
    FOREIGN KEY (component_id) REFERENCES components(component_id)
);

INSERT INTO current_stock (component_id, quantity_on_hand, last_updated) VALUES
(1, 15000, '2023-03-01'),
(2, 3000, '2023-04-01'),
(3, 30000, '2023-03-05'),
(4, 400, '2023-04-10'),
(5, 700, '2023-04-15');

Tasks (using components and current_stock primarily):

Count the total number of different components tracked.
Calculate the total quantity_on_hand across all components in current_stock.
Find the average unit_cost for components in the ‘Electronics’ category.
Determine the minimum and maximum unit_cost among all components.
Count the number of distinct component categories.
Calculate the total value of the current inventory for each component category (Sum of quantity_on_hand * unit_cost). Display category and total_category_value. (Requires join).
Find the average quantity_on_hand per component in the current_stock.
Count the number of components supplied by each supplier (based on the first entry in inventory_log for simplicity, or requires more complex logic). Let’s simplify: Count components per category instead. Display category and component_count.
Find component categories where the total number of components (distinct types) is greater than 1. Display category and component_type_count.
Find component categories where the average unit_cost is less than $0.10. Display category and average_unit_cost.

Variant 15: Gym Membership and Class Attendance Analysis

Scenario: You manage a fitness center. Analyze member data, class schedules, and attendance records using SQL aggregate functions to understand business performance and member engagement.

Schema:

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS class_attendance;
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS membership_types;

-- Create Membership Types Table
CREATE TABLE membership_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) UNIQUE NOT NULL, -- e.g., 'Basic', 'Premium', 'Family'
    monthly_fee NUMERIC(7, 2) CHECK (monthly_fee >= 0),
    access_level INT DEFAULT 1 -- Higher level = more access
);

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

-- Create Classes Table
CREATE TABLE classes (
    class_id SERIAL PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL,
    instructor_name VARCHAR(100),
    day_of_week VARCHAR(10), -- e.g., 'Monday', 'Tuesday'
    start_time TIME,
    duration_minutes INT CHECK (duration_minutes > 0),
    max_capacity INT CHECK (max_capacity > 0)
);

-- Create Class Attendance Table
CREATE TABLE class_attendance (
    attendance_id SERIAL PRIMARY KEY,
    class_id INT NOT NULL,
    member_id INT NOT NULL,
    attendance_date DATE NOT NULL,
    check_in_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES classes(class_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

-- Insert Sample Data into Membership Types
INSERT INTO membership_types (type_name, monthly_fee, access_level) VALUES
('Basic', 29.99, 1),
('Premium', 49.99, 2),
('Platinum', 79.99, 3);

-- Insert Sample Data into Members
INSERT INTO members (member_name, email, join_date, membership_type_id, date_of_birth) VALUES
('Rocky Balboa', 'rocky@philly.com', '2022-01-15', 2, '1976-11-21'),
('Sarah Connor', 'sarah@skynet.def', '2021-08-01', 3, '1965-05-13'),
('Luke Skywalker', 'luke@jedi.org', '2023-03-10', 1, '19BBY-05-25'), -- DOB format example
('Ellen Ripley', 'ellen@nostromo.corp', '2022-05-20', 2, NULL), -- NULL DOB
('Indiana Jones', 'indy@archaeology.edu', '2020-11-01', 3, '1899-07-01');

-- Insert Sample Data into Classes
INSERT INTO classes (class_name, instructor_name, day_of_week, start_time, duration_minutes, max_capacity) VALUES
('Yoga Flow', 'Padme', 'Monday', '18:00:00', 60, 20),
('Spin Cycle Fury', 'Max', 'Tuesday', '07:00:00', 45, 25),
('Heavy Lifting', 'Arnold', 'Wednesday', '19:00:00', 75, 15),
('Zumba Party', 'Shakira', 'Thursday', '18:30:00', 60, 30),
('Meditation', 'Yoda', 'Friday', '12:00:00', 30, 20);

-- Insert Sample Data into Class Attendance
INSERT INTO class_attendance (class_id, member_id, attendance_date, check_in_time) VALUES
(1, 1, '2023-04-03', '2023-04-03 17:55:00'), -- Rocky Yoga
(2, 2, '2023-04-04', '2023-04-04 06:58:00'), -- Sarah Spin
(3, 1, '2023-04-05', '2023-04-05 18:50:00'), -- Rocky Lifting
(1, 4, '2023-04-10', '2023-04-10 17:59:00'), -- Ripley Yoga
(4, 5, '2023-04-13', '2023-04-13 18:25:00'), -- Indy Zumba
(1, 2, '2023-04-17', '2023-04-17 17:56:00'), -- Sarah Yoga
(3, 5, '2023-04-19', '2023-04-19 18:55:00'), -- Indy Lifting
(2, 1, '2023-04-25', '2023-04-25 06:55:00'), -- Rocky Spin
(5, 3, '2023-04-28', '2023-04-28 11:58:00'); -- Luke Meditation

Tasks:

Count the total number of active members.
Calculate the total number of classes offered per week (count distinct class_id from classes).
Find the average monthly_fee across all membership types.
Determine the minimum and maximum max_capacity among all classes.
Count the number of distinct class instructors.
Calculate the total number of attendances recorded for each class. Display class_id, class_name (requires join), and total_attendance. Order by total_attendance descending.
Find the number of members associated with each membership_type_id. Display type_id, type_name (requires join), and member_count.
Calculate the average class duration_minutes.
Find members who have attended more than 1 class. Display member_id, member_name (requires join), and classes_attended_count.
Find the day of the week (day_of_week) with the highest average class max_capacity. Display day_of_week and average_capacity.

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