Databases | Tasks for Practical Class 12

Creating and using VIEWs in SQL

---

Variant 1: Online Bookstore

Scenario: You are managing the database for an online bookstore. The database tracks books, authors, customers, and their orders. You need to create several views to simplify common queries and restrict access to certain data.

Database Schema:

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

-- Books Table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT REFERENCES authors(author_id),
    genre VARCHAR(50),
    price NUMERIC(8, 2),
    publication_year INT,
    stock_count INT DEFAULT 0
);

-- 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,
    registration_date DATE DEFAULT CURRENT_DATE
);

-- 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) DEFAULT 'Pending' -- e.g., Pending, Shipped, Delivered
);

-- Order_Items Table (Connects Orders and Books)
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    book_id INT REFERENCES books(book_id),
    quantity INT NOT NULL,
    price_at_purchase NUMERIC(8, 2) NOT NULL -- Price when the order was placed
);

-- Sample Data
INSERT INTO authors (author_name, nationality) VALUES
('Jane Austen', 'British'),
('George Orwell', 'British'),
('Haruki Murakami', 'Japanese'),
('Leo Tolstoy', 'Russian');

INSERT INTO books (title, author_id, genre, price, publication_year, stock_count) VALUES
('Pride and Prejudice', 1, 'Romance', 12.99, 1813, 50),
('1984', 2, 'Dystopian', 10.50, 1949, 35),
('Norwegian Wood', 3, 'Fiction', 14.00, 1987, 20),
('War and Peace', 4, 'Historical Fiction', 19.95, 1869, 15),
('Emma', 1, 'Romance', 11.50, 1815, 40),
('Animal Farm', 2, 'Political Satire', 9.00, 1945, 60);

INSERT INTO customers (first_name, last_name, email, registration_date) VALUES
('Alice', ' Wonderland', 'alice.w@mail.com', '2023-01-10'),
('Bob', 'The Builder', 'bob.b@mail.com', '2023-02-15'),
('Charlie', 'Chaplin', 'charlie.c@mail.com', '2023-03-20');

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

INSERT INTO order_items (order_id, book_id, quantity, price_at_purchase) VALUES
(1, 1, 1, 12.99), -- Alice buys Pride and Prejudice
(1, 2, 1, 10.50), -- Alice buys 1984
(2, 3, 2, 14.00), -- Bob buys 2 Norwegian Wood
(3, 6, 1, 9.00);  -- Alice buys Animal Farm

Tasks:

1. Create book_catalog View: Create a view named book_catalog that shows the book_id, title, author_name, and price for all books. Join the books and authors tables.
2. Create customer_emails View: Create a view named customer_emails showing only the first_name, last_name, and email of all customers.
3. Create low_stock_alert View: Create a view named low_stock_alert that lists the title and stock_count for books with a stock_count less than 20.
4. Create pending_orders_summary View: Create a view named pending_orders_summary that displays the order_id, customer_id, and order_date for all orders with the status ‘Pending’.
5. Create detailed_order_info View: Create a view named detailed_order_info showing order_id, order_date, customer’s first_name and last_name, book title, quantity, and price_at_purchase. This will require joining orders, customers, order_items, and books.
6. Create author_book_count View: Create a view named author_book_count that shows each author_name and the total number of distinct book titles (COUNT(b.book_id)) they have in the books table. Group by author name.
7. Query book_catalog View: Write a query to select all books from the book_catalog view where the author is ‘George Orwell’.
8. Query detailed_order_info View: Write a query to find all entries in the detailed_order_info view related to order_id 1.
9. Modify customer_emails View: Use CREATE OR REPLACE VIEW to modify the customer_emails view to also include the registration_date.
10. Drop low_stock_alert View: Remove the low_stock_alert view from the database.

---

Variant 2: Project Management System

Scenario: You are responsible for a database that tracks projects, the tasks within those projects, employees, and their assignments to tasks. Views are needed to simplify reporting and access control.

Database Schema:

-- Employees Table
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_role VARCHAR(50), -- e.g., Developer, Manager, QA Tester
    hire_date DATE
);

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

-- Tasks Table
CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INT REFERENCES projects(project_id),
    task_description TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'To Do', -- e.g., To Do, In Progress, Done, Blocked
    priority INT -- e.g., 1 (High), 2 (Medium), 3 (Low)
);

-- Assignments Table (Links Employees to Tasks)
CREATE TABLE assignments (
    assignment_id SERIAL PRIMARY KEY,
    task_id INT REFERENCES tasks(task_id),
    employee_id INT REFERENCES employees(employee_id),
    assigned_date DATE DEFAULT CURRENT_DATE,
    hours_logged NUMERIC(5, 2) DEFAULT 0
);

-- Sample Data
INSERT INTO employees (emp_name, emp_role, hire_date) VALUES
('John Smith', 'Manager', '2020-05-15'),
('Alice Brown', 'Developer', '2021-08-20'),
('Bob White', 'Developer', '2021-09-01'),
('Carol Green', 'QA Tester', '2022-01-10');

INSERT INTO projects (project_name, start_date, deadline, budget) VALUES
('Website Redesign', '2023-09-01', '2024-03-01', 50000.00),
('Mobile App Launch', '2023-10-15', '2024-06-15', 75000.00),
('Internal CRM Update', '2023-11-01', '2024-02-01', 30000.00);

INSERT INTO tasks (project_id, task_description, status, priority) VALUES
(1, 'Define new site structure', 'Done', 1),
(1, 'Develop homepage mockups', 'In Progress', 1),
(1, 'Implement user authentication', 'To Do', 2),
(2, 'Setup development environment', 'Done', 1),
(2, 'Code core features', 'In Progress', 1),
(3, 'Analyze current CRM issues', 'In Progress', 2);

INSERT INTO assignments (task_id, employee_id, assigned_date, hours_logged) VALUES
(1, 1, '2023-09-05', 10.0), -- John managed task 1
(2, 2, '2023-09-10', 15.5), -- Alice working on task 2
(3, 3, '2023-09-15', 0.0),  -- Bob assigned task 3
(4, 2, '2023-10-16', 8.0),  -- Alice did task 4
(5, 3, '2023-10-20', 25.0), -- Bob working on task 5
(6, 1, '2023-11-02', 5.0);  -- John working on task 6

Tasks:

1. Create active_projects View: Create a view named active_projects showing project_name, start_date, and deadline for all projects where the deadline is in the future (or NULL, assuming NULL means ongoing indefinitely - for simplicity, let’s focus on deadline > CURRENT_DATE).
2. Create employee_roles View: Create a view named employee_roles showing only the emp_name and emp_role for all employees.
3. Create high_priority_tasks View: Create a view named high_priority_tasks that lists the task_description and status for tasks with priority = 1.
4. Create project_task_list View: Create a view named project_task_list that displays the project_name and the task_description for all tasks. Join projects and tasks tables.
5. Create employee_assignments_detailed View: Create a view named employee_assignments_detailed showing the emp_name, project_name, task_description, and assigned_date. This requires joining employees, assignments, tasks, and projects.
6. Create project_task_status_count View: Create a view named project_task_status_count that shows each project_name and the count of tasks in each status (e.g., ‘To Do’, ‘In Progress’, ‘Done’). Group by project_name and status. (Hint: COUNT(*) or COUNT(t.task_id)).
7. Query employee_roles View: Write a query to select all employees from the employee_roles view who have the role ‘Developer’.
8. Query employee_assignments_detailed View: Write a query to find all assignments in the employee_assignments_detailed view for the ‘Website Redesign’ project, ordered by assigned_date.
9. Modify active_projects View: Use CREATE OR REPLACE VIEW to modify the active_projects view to also include the budget.
10. Drop high_priority_tasks View: Remove the high_priority_tasks view from the database.

---

Variant 3: University Course Enrollment

Scenario: You are managing a university database that stores information about departments, courses offered by these departments, students, and their enrollments in courses. Views are required to simplify access to commonly needed information.

Database Schema:

-- Departments Table
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    building VARCHAR(50)
);

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

-- Students Table
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_year INT
);

-- Enrollments Table
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade CHAR(1) -- e.g., A, B, C, D, F (or NULL if not graded yet)
);

-- Sample Data
INSERT INTO departments (dept_name, building) VALUES
('Computer Science', 'Engineering Hall'),
('Mathematics', 'Science Wing'),
('History', 'Arts Building');

INSERT INTO courses (course_code, course_title, dept_id, credits) VALUES
('CS101', 'Introduction to Programming', 1, 3),
('CS305', 'Databases', 1, 4),
('MATH201', 'Calculus I', 2, 4),
('HIST101', 'World History I', 3, 3),
('MATH202', 'Calculus II', 2, 4);

INSERT INTO students (first_name, last_name, email, enrollment_year) VALUES
('Peter', 'Pan', 'peter.p@uni.edu', 2022),
('Wendy', 'Darling', 'wendy.d@uni.edu', 2021),
('James', 'Hook', 'james.h@uni.edu', 2022),
('Mary', 'Poppins', 'mary.p@uni.edu', 2023);

INSERT INTO enrollments (student_id, course_id, enrollment_date, grade) VALUES
(1, 1, '2023-09-01', 'A'), -- Peter takes CS101
(1, 3, '2023-09-01', 'B'), -- Peter takes MATH201
(2, 1, '2023-09-01', 'B'), -- Wendy takes CS101
(2, 4, '2023-09-01', NULL), -- Wendy takes HIST101
(3, 1, '2023-09-02', 'C'), -- James takes CS101
(3, 2, '2023-09-02', NULL), -- James takes CS305 (Databases)
(1, 2, '2024-01-15', NULL); -- Peter enrolls in CS305 later

Tasks:

1. Create course_directory View: Create a view named course_directory that shows course_code, course_title, credits, and the dept_name for all courses. Join courses and departments.
2. Create student_contact_info View: Create a view named student_contact_info showing the student_id, first_name, last_name, and email of all students.
3. Create ungraded_enrollments View: Create a view named ungraded_enrollments that lists the enrollment_id, student_id, and course_id for all enrollments where the grade is NULL.
4. Create cs_courses View: Create a view named cs_courses that displays the course_code and course_title for all courses offered by the ‘Computer Science’ department.
5. Create student_enrollment_details View: Create a view named student_enrollment_details showing the student’s first_name, last_name, the course_code, course_title, and the grade. This requires joining students, enrollments, and courses.
6. Create department_course_count View: Create a view named department_course_count that shows each dept_name and the total number of courses (COUNT(c.course_id)) offered by that department. Group by department name.
7. Query course_directory View: Write a query to select all courses from the course_directory view that are worth 4 credits.
8. Query student_enrollment_details View: Write a query to find all enrollments in the student_enrollment_details view for the student ‘Peter Pan’, ordered by course_code.
9. Modify student_contact_info View: Use CREATE OR REPLACE VIEW to modify the student_contact_info view to also include the enrollment_year.
10. Drop ungraded_enrollments View: Remove the ungraded_enrollments view from the database.

---

Variant 4: Music Streaming Service

Scenario: You are designing the database for a music streaming service. The database stores information about artists, albums, tracks, users, and their playlists. You need to create views to simplify common queries for displaying music catalogs and user libraries.

Database Schema:

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

-- 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)
);

-- 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),
    track_number INT -- Position of the track within the album
);

-- 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
);

-- Playlists Table
CREATE TABLE playlists (
    playlist_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    playlist_name VARCHAR(100) NOT NULL,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_public BOOLEAN DEFAULT FALSE
);

-- Playlist_Tracks Table (Associative table linking playlists and tracks)
CREATE TABLE playlist_tracks (
    playlist_track_id SERIAL PRIMARY KEY,
    playlist_id INT REFERENCES playlists(playlist_id),
    track_id INT REFERENCES tracks(track_id),
    added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(playlist_id, track_id) -- Prevent adding the same track twice to one playlist
);

-- Sample Data
INSERT INTO artists (artist_name, country) VALUES
('Queen', 'UK'),
('Daft Punk', 'France'),
('Led Zeppelin', 'UK'),
('Taylor Swift', 'USA');

INSERT INTO albums (album_title, artist_id, release_year, genre) VALUES
('A Night at the Opera', 1, 1975, 'Rock'),
('Discovery', 2, 2001, 'Electronic'),
('Led Zeppelin IV', 3, 1971, 'Hard Rock'),
('1989', 4, 2014, 'Pop'),
('News of the World', 1, 1977, 'Rock');

INSERT INTO tracks (track_title, album_id, duration_seconds, track_number) VALUES
('Bohemian Rhapsody', 1, 355, 1),
('You''re My Best Friend', 1, 171, 4),
('One More Time', 2, 320, 1),
('Digital Love', 2, 301, 3),
('Stairway to Heaven', 3, 482, 4),
('Black Dog', 3, 295, 1),
('We Will Rock You', 5, 121, 1),
('We Are The Champions', 5, 177, 2),
('Shake It Off', 4, 219, 6);

INSERT INTO users (username, email, join_date) VALUES
('musicfan1', 'fan1@email.com', '2023-01-15'),
('rocklover', 'rock@email.com', '2023-03-22'),
('djcool', 'dj@email.com', '2023-05-10');

INSERT INTO playlists (user_id, playlist_name, is_public) VALUES
(1, 'Workout Mix', TRUE),
(2, 'Classic Rock Anthems', TRUE),
(1, 'Chill Vibes', FALSE);

INSERT INTO playlist_tracks (playlist_id, track_id, added_date) VALUES
(1, 3, '2023-06-01 10:00:00'), -- Workout Mix: One More Time
(1, 7, '2023-06-01 10:01:00'), -- Workout Mix: We Will Rock You
(1, 9, '2023-06-01 10:02:00'), -- Workout Mix: Shake It Off
(2, 1, '2023-06-05 15:30:00'), -- Classic Rock: Bohemian Rhapsody
(2, 5, '2023-06-05 15:31:00'), -- Classic Rock: Stairway to Heaven
(2, 6, '2023-06-05 15:32:00'), -- Classic Rock: Black Dog
(3, 4, '2023-07-11 09:00:00'); -- Chill Vibes: Digital Love

Tasks:

1. Create full_track_info View: Create a view named full_track_info showing track_title, artist_name, album_title, release_year, and duration_seconds. This requires joining tracks, albums, and artists.
2. Create user_summary View: Create a view named user_summary showing only the username and join_date for all users.
3. Create long_tracks View: Create a view named long_tracks listing the track_title and duration_seconds for all tracks longer than 300 seconds (5 minutes).
4. Create public_playlists_overview View: Create a view named public_playlists_overview that displays the playlist_name, the username of the creator, and the creation_date for all playlists where is_public is TRUE. Join playlists and users.
5. Create playlist_details View: Create a view named playlist_details showing playlist_name, track_title, artist_name, and the added_date for tracks within playlists. This involves joining playlists, playlist_tracks, tracks, albums, and artists.
6. Create artist_album_count View: Create a view named artist_album_count that shows each artist_name and the total number of albums (COUNT(al.album_id)) they have in the albums table. Group by artist name.
7. Query full_track_info View: Write a query to select all track information from the full_track_info view for the artist ‘Queen’.
8. Query playlist_details View: Write a query to find all tracks in the playlist_details view belonging to the playlist named ‘Workout Mix’, ordered by added_date.
9. Modify user_summary View: Use CREATE OR REPLACE VIEW to modify the user_summary view to also include the user’s email.
10. Drop long_tracks View: Remove the long_tracks view from the database.

---

Variant 5: Airline Flight System

Scenario: You manage the database for an airline flight system. It tracks airports, airlines, flight schedules, passengers, and their bookings. Views are essential for generating flight information displays and passenger lists efficiently.

Database Schema:

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

-- Airlines Table
CREATE TABLE airlines (
    airline_id SERIAL PRIMARY KEY,
    airline_name VARCHAR(100) NOT NULL UNIQUE,
    iata_code CHAR(2) UNIQUE -- e.g., BA, AA, AF
);

-- Flights Table
CREATE TABLE flights (
    flight_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) NOT NULL, -- e.g., BA215, AA100
    airline_id INT REFERENCES airlines(airline_id),
    origin_airport CHAR(3) REFERENCES airports(airport_code),
    destination_airport CHAR(3) REFERENCES airports(airport_code),
    departure_time TIMESTAMP,
    arrival_time TIMESTAMP,
    status VARCHAR(20) DEFAULT 'Scheduled' -- e.g., Scheduled, Delayed, Cancelled, Departed, Arrived
);

-- Passengers Table
CREATE TABLE passengers (
    passenger_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    passport_number VARCHAR(20) UNIQUE,
    nationality VARCHAR(50)
);

-- Bookings 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(4) -- e.g., 12A, 30F
);

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

INSERT INTO airlines (airline_name, iata_code) VALUES
('British Airways', 'BA'),
('American Airlines', 'AA'),
('Air France', 'AF');

INSERT INTO flights (flight_number, airline_id, origin_airport, destination_airport, departure_time, arrival_time, status) VALUES
('BA175', 1, 'LHR', 'JFK', '2024-05-10 10:00:00', '2024-05-10 13:00:00', 'Scheduled'),
('AA101', 2, 'JFK', 'LAX', '2024-05-10 15:00:00', '2024-05-10 18:30:00', 'Scheduled'),
('AF008', 3, 'CDG', 'JFK', '2024-05-11 09:00:00', '2024-05-11 11:30:00', 'Scheduled'),
('BA287', 1, 'LHR', 'LAX', '2024-05-11 14:00:00', '2024-05-11 17:15:00', 'Delayed');

INSERT INTO passengers (first_name, last_name, passport_number, nationality) VALUES
('Clark', 'Kent', 'US123456', 'USA'),
('Lois', 'Lane', 'US654321', 'USA'),
('Bruce', 'Wayne', 'UK987654', 'UK'),
('Diana', 'Prince', 'FR112233', 'France');

INSERT INTO bookings (flight_id, passenger_id, booking_date, seat_number) VALUES
(1, 1, '2024-04-01', '10A'), -- Clark on BA175
(1, 2, '2024-04-02', '10B'), -- Lois on BA175
(2, 1, '2024-04-05', '22F'), -- Clark on AA101
(3, 4, '2024-04-10', '05C'), -- Diana on AF008
(4, 3, '2024-04-15', '01A'); -- Bruce on BA287 (delayed)

Tasks:

1. Create flight_schedule_detailed View: Create a view named flight_schedule_detailed showing flight_number, airline_name, origin airport name (orig_airport_name), destination airport name (dest_airport_name), departure_time, and arrival_time. This requires joining flights, airlines, and airports (twice, using aliases).
2. Create passenger_list View: Create a view named passenger_list showing only the first_name, last_name, and nationality of all passengers.
3. Create delayed_flights View: Create a view named delayed_flights that lists the flight_number, origin_airport, and destination_airport for flights with the status ‘Delayed’.
4. Create jfk_departures View: Create a view named jfk_departures displaying the flight_number, destination_airport, and departure_time for all flights originating from ‘JFK’.
5. Create booking_manifest View: Create a view named booking_manifest showing flight_number, departure_time, passenger first_name and last_name, and seat_number. Join bookings, passengers, and flights.
6. Create airline_flight_count View: Create a view named airline_flight_count that shows each airline_name and the total number of flights (COUNT(f.flight_id)) associated with that airline in the flights table. Group by airline name.
7. Query flight_schedule_detailed View: Write a query to select all flights from the flight_schedule_detailed view operated by ‘British Airways’.
8. Query booking_manifest View: Write a query to find all passenger bookings listed in the booking_manifest view for flight BA175.
9. Modify passenger_list View: Use CREATE OR REPLACE VIEW to modify the passenger_list view to also include the passport_number.
10. Drop delayed_flights View: Remove the delayed_flights view from the database.

---

Variant 6: E-commerce Product Inventory

Scenario: You are managing the database for an e-commerce platform’s inventory system. The database tracks product categories, suppliers, products, warehouses, and the stock levels of each product in different warehouses. Views are needed to provide easy access to catalog information and stock levels.

Database Schema:

-- Suppliers Table
CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    supplier_name VARCHAR(150) NOT NULL UNIQUE,
    contact_email VARCHAR(100),
    country VARCHAR(50)
);

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

-- Products Table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    sku VARCHAR(50) UNIQUE, -- Stock Keeping Unit
    category_id INT REFERENCES categories(category_id),
    supplier_id INT REFERENCES suppliers(supplier_id),
    unit_price NUMERIC(10, 2) CHECK (unit_price >= 0)
);

-- Warehouses Table
CREATE TABLE warehouses (
    warehouse_id SERIAL PRIMARY KEY,
    warehouse_name VARCHAR(100) NOT NULL,
    location_city VARCHAR(50),
    capacity_sqm INT -- Square meters
);

-- Inventory Table (Stock levels of products in warehouses)
CREATE TABLE inventory (
    inventory_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    warehouse_id INT REFERENCES warehouses(warehouse_id),
    quantity_on_hand INT NOT NULL DEFAULT 0 CHECK (quantity_on_hand >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(product_id, warehouse_id) -- Ensure only one entry per product per warehouse
);

-- Sample Data
INSERT INTO suppliers (supplier_name, contact_email, country) VALUES
('TechGadgets Inc.', 'sales@techgadgets.com', 'USA'),
('Office Supplies Co.', 'contact@officesupplies.co', 'Canada'),
('Home Decor Ltd.', 'info@homedecor.ltd', 'UK');

INSERT INTO categories (category_name, description) VALUES
('Electronics', 'Consumer electronics and accessories'),
('Office', 'Stationery, furniture, and equipment for offices'),
('Home Goods', 'Items for household use and decoration');

INSERT INTO products (product_name, sku, category_id, supplier_id, unit_price) VALUES
('Wireless Mouse', 'WM-101', 1, 1, 25.50),
('Mechanical Keyboard', 'MK-205', 1, 1, 75.00),
('Stapler (Heavy Duty)', 'STP-HD-50', 2, 2, 15.99),
('Desk Lamp (LED)', 'DL-LED-01', 2, 2, 35.00),
('Throw Pillow (Velvet)', 'TP-VL-BLUE', 3, 3, 19.95),
('Wall Clock (Modern)', 'WC-MOD-003', 3, 3, 45.00);

INSERT INTO warehouses (warehouse_name, location_city, capacity_sqm) VALUES
('Main Distribution Center', 'Chicago', 10000),
('West Coast Hub', 'Los Angeles', 5000),
('East Coast Depot', 'New York', 6000);

INSERT INTO inventory (product_id, warehouse_id, quantity_on_hand) VALUES
(1, 1, 500), -- Wireless Mouse in Main DC
(1, 2, 200), -- Wireless Mouse in West Coast Hub
(2, 1, 150), -- Keyboard in Main DC
(3, 1, 300), -- Stapler in Main DC
(3, 3, 100), -- Stapler in East Coast Depot
(4, 2, 50),  -- Desk Lamp in West Coast Hub
(5, 1, 80),  -- Throw Pillow in Main DC
(6, 3, 40);  -- Wall Clock in East Coast Depot

Tasks:

1. Create full_product_catalog View: Create a view named full_product_catalog showing product_name, sku, category_name, supplier_name, and unit_price. This requires joining products, categories, and suppliers.
2. Create supplier_contacts View: Create a view named supplier_contacts showing only the supplier_name and contact_email for all suppliers.
3. Create low_stock_items View: Create a view named low_stock_items listing the product_id, warehouse_id, and quantity_on_hand for all inventory records where quantity_on_hand is less than 50.
4. Create electronics_catalog View: Create a view named electronics_catalog displaying the product_name, sku, and unit_price for all products belonging to the ‘Electronics’ category.
5. Create warehouse_inventory_details View: Create a view named warehouse_inventory_details showing warehouse_name, location_city, product_name, sku, and quantity_on_hand. Join inventory, products, and warehouses.
6. Create category_product_count View: Create a view named category_product_count that shows each category_name and the total number of products (COUNT(p.product_id)) listed in that category. Group by category name.
7. Query full_product_catalog View: Write a query to select all products from the full_product_catalog view supplied by ‘TechGadgets Inc.’.
8. Query warehouse_inventory_details View: Write a query to find all inventory details in the warehouse_inventory_details view for the ‘Main Distribution Center’, ordered by product_name.
9. Modify supplier_contacts View: Use CREATE OR REPLACE VIEW to modify the supplier_contacts view to also include the supplier’s country.
10. Drop low_stock_items View: Remove the low_stock_items view from the database.

---

Variant 7: Hospital Patient Records

Scenario: You are working with a database for a hospital clinic. The database stores information about doctors, patients, their appointments, and diagnoses made during those appointments. Creating views will help simplify common data retrieval tasks for administrative staff and medical professionals.

Database Schema:

-- Doctors Table
CREATE TABLE doctors (
    doctor_id SERIAL PRIMARY KEY,
    doc_name VARCHAR(100) NOT NULL,
    specialization VARCHAR(100),
    phone_number VARCHAR(20)
);

-- 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,
    contact_number VARCHAR(20) UNIQUE
);

-- 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_datetime TIMESTAMP NOT NULL,
    reason_for_visit TEXT,
    status VARCHAR(20) DEFAULT 'Scheduled' -- e.g., Scheduled, Completed, Cancelled, No-Show
);

-- Diagnoses Table
CREATE TABLE diagnoses (
    diagnosis_id SERIAL PRIMARY KEY,
    appointment_id INT REFERENCES appointments(appointment_id) UNIQUE, -- Assuming one primary diagnosis per appointment for simplicity
    condition_name VARCHAR(150) NOT NULL,
    diagnosis_date DATE DEFAULT CURRENT_DATE,
    treatment_plan TEXT
);

-- Sample Data
INSERT INTO doctors (doc_name, specialization, phone_number) VALUES
('Dr. Evelyn Reed', 'Cardiology', '555-0101'),
('Dr. Samuel Green', 'Pediatrics', '555-0102'),
('Dr. Anya Sharma', 'General Medicine', '555-0103');

INSERT INTO patients (first_name, last_name, date_of_birth, contact_number) VALUES
('Michael', 'Jones', '1985-06-15', '555-0201'),
('Sarah', 'Chen', '1992-11-22', '555-0202'),
('David', 'Lee', '2018-03-10', '555-0203'),
('Laura', 'Martinez', '1970-01-30', '555-0204');

INSERT INTO appointments (patient_id, doctor_id, appointment_datetime, reason_for_visit, status) VALUES
(1, 1, '2023-10-20 09:00:00', 'Chest pain evaluation', 'Completed'),
(2, 3, '2023-10-21 11:30:00', 'Annual check-up', 'Completed'),
(3, 2, '2023-10-22 14:00:00', 'Vaccination', 'Completed'),
(1, 3, '2023-10-28 10:00:00', 'Follow-up consultation', 'Scheduled'),
(4, 1, '2023-11-05 15:00:00', 'Blood pressure check', 'Scheduled');

INSERT INTO diagnoses (appointment_id, condition_name, diagnosis_date, treatment_plan) VALUES
(1, 'Angina Pectoris', '2023-10-20', 'Prescribe medication, recommend lifestyle changes.'),
(2, 'Healthy', '2023-10-21', 'Continue healthy habits, return next year.'),
(3, 'Up-to-date Immunizations', '2023-10-22', 'No further action needed.');

Tasks:

1. Create patient_directory View: Create a view named patient_directory showing patient_id, first_name, last_name, and contact_number for all patients.
2. Create doctor_specializations View: Create a view named doctor_specializations listing doc_name and specialization for all doctors.
3. Create upcoming_appointments View: Create a view named upcoming_appointments that displays appointment_id, patient’s full name (concatenated first_name and last_name), doc_name, and appointment_datetime for all appointments with a status of ‘Scheduled’ and appointment_datetime in the future (use > CURRENT_TIMESTAMP). Requires joining patients, appointments, and doctors.
4. Create patient_diagnosis_history View: Create a view named patient_diagnosis_history showing the patient’s full name, appointment_datetime, condition_name, and treatment_plan. This requires joining patients, appointments, and diagnoses.
5. Create cardiology_patients View: Create a view named cardiology_patients that lists the first_name, last_name, and contact_number of patients who have had an appointment with a doctor whose specialization is ‘Cardiology’. Requires joining patients, appointments, and doctors. Use DISTINCT to avoid duplicates if a patient saw the cardiologist multiple times.
6. Create doctor_appointment_load View: Create a view named doctor_appointment_load showing doc_name and the total count of ‘Completed’ appointments (COUNT(ap.appointment_id)) associated with each doctor. Group by doctor name. Include doctors even if they have 0 completed appointments (use LEFT JOIN from doctors to appointments).
7. Query upcoming_appointments View: Write a query to select all upcoming appointments from the upcoming_appointments view scheduled with ‘Dr. Evelyn Reed’.
8. Query patient_diagnosis_history View: Write a query to find all diagnoses recorded for the patient ‘Michael Jones’ using the patient_diagnosis_history view, ordered by appointment_datetime.
9. Modify patient_directory View: Use CREATE OR REPLACE VIEW to modify the patient_directory view to also include the date_of_birth.
10. Drop doctor_specializations View: Remove the doctor_specializations view from the database.

---

Variant 8: Rental Car Agency

Scenario: You manage the database for a car rental agency. The system tracks customers, the vehicle fleet, rental locations, and individual rental transactions. Views are needed to simplify reporting on vehicle availability, customer rentals, and location activity.

Database Schema:

-- 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,
    drivers_license_no VARCHAR(50) UNIQUE NOT NULL,
    member_since DATE DEFAULT CURRENT_DATE
);

-- Locations Table
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    location_name VARCHAR(100) NOT NULL, -- e.g., Airport Branch, Downtown Office
    city VARCHAR(50),
    address VARCHAR(255)
);

-- Vehicles Table
CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year INT,
    license_plate VARCHAR(15) UNIQUE NOT NULL,
    category VARCHAR(20), -- e.g., Sedan, SUV, Van, Luxury
    current_location_id INT REFERENCES locations(location_id),
    daily_rental_rate NUMERIC(6, 2)
);

-- Rentals Table
CREATE TABLE rentals (
    rental_id SERIAL PRIMARY KEY,
    vehicle_id INT REFERENCES vehicles(vehicle_id),
    customer_id INT REFERENCES customers(customer_id),
    pickup_location_id INT REFERENCES locations(location_id),
    return_location_id INT REFERENCES locations(location_id), -- Can be different from pickup
    pickup_datetime TIMESTAMP NOT NULL,
    expected_return_datetime TIMESTAMP NOT NULL,
    actual_return_datetime TIMESTAMP, -- NULL if currently rented out
    total_amount NUMERIC(8, 2) -- Calculated upon return
);

-- Sample Data
INSERT INTO customers (first_name, last_name, email, drivers_license_no, member_since) VALUES
('Arthur', 'Dent', 'adent@galaxy.com', 'DL12345', '2022-01-10'),
('Ford', 'Prefect', 'fprefect@guide.com', 'DL67890', '2022-02-15'),
('Zaphod', 'Beeblebrox', 'zbeeblebrox@prez.gov', 'DL11111', '2023-03-20');

INSERT INTO locations (location_name, city, address) VALUES
('Airport Branch', 'Metropolis', '1 Airport Rd'),
('Downtown Office', 'Metropolis', '123 Main St'),
('Suburb Depot', 'Gotham', '45 Sidekick Ln');

INSERT INTO vehicles (make, model, year, license_plate, category, current_location_id, daily_rental_rate) VALUES
('Toyota', 'Camry', 2022, 'ABC-123', 'Sedan', 1, 55.00),
('Ford', 'Explorer', 2023, 'XYZ-789', 'SUV', 2, 75.00),
('Honda', 'Civic', 2021, 'DEF-456', 'Sedan', 1, 50.00),
('Chevrolet', 'Suburban', 2023, 'GHI-000', 'SUV', 3, 80.00);

INSERT INTO rentals (vehicle_id, customer_id, pickup_location_id, return_location_id, pickup_datetime, expected_return_datetime, actual_return_datetime, total_amount) VALUES
(1, 1, 1, 1, '2023-10-15 09:00:00', '2023-10-18 09:00:00', '2023-10-18 08:45:00', 165.00),
(2, 2, 2, 2, '2023-10-20 12:00:00', '2023-10-25 12:00:00', NULL, NULL), -- Currently rented
(3, 1, 1, 2, '2023-10-22 10:00:00', '2023-10-24 10:00:00', NULL, NULL); -- Currently rented

Tasks:

1. Create vehicle_inventory View: Create a view named vehicle_inventory showing license_plate, make, model, category, daily_rental_rate, and the location_name of the vehicle’s current_location_id. Join vehicles and locations.
2. Create customer_details View: Create a view named customer_details showing customer_id, full name (concatenated first_name and last_name), email, and drivers_license_no.
3. Create active_rentals_summary View: Create a view named active_rentals_summary listing the rental_id, customer’s full name, vehicle’s make and model, and the expected_return_datetime for all rentals where actual_return_datetime IS NULL. Requires joining rentals, customers, and vehicles.
4. Create available_vehicles_now View: Create a view named available_vehicles_now showing license_plate, make, model, category, daily_rental_rate, and location_name for vehicles that are NOT currently rented out (i.e., their vehicle_id does not appear in the rentals table with an actual_return_datetime that IS NULL). You might need a subquery or a LEFT JOIN approach.
5. Create rental_history_customer View: Create a view named rental_history_customer showing customer’s full name, vehicle’s make and model, pickup_datetime, actual_return_datetime, and total_amount for completed rentals (actual_return_datetime IS NOT NULL). Requires joining rentals, customers, and vehicles.
6. Create location_vehicle_count View: Create a view named location_vehicle_count that shows each location_name and the total number of vehicles (COUNT(v.vehicle_id)) currently assigned to that location (current_location_id). Group by location name. Include locations with 0 vehicles.
7. Query available_vehicles_now View: Write a query to select all available ‘SUV’ category vehicles from the available_vehicles_now view located at the ‘Airport Branch’.
8. Query active_rentals_summary View: Write a query to find the active rental details for the customer ‘Arthur Dent’ using the active_rentals_summary view.
9. Modify customer_details View: Use CREATE OR REPLACE VIEW to modify the customer_details view to also include the member_since date.
10. Drop rental_history_customer View: Remove the rental_history_customer view from the database.

---

Variant 9: Blog Platform

Scenario: You are the database administrator for a blog platform. The database stores information about users (authors), blog posts, categories for posts, and comments left by users on posts. You need to create views to simplify common queries for displaying blog content and managing users.

Database Schema:

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

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

-- Posts Table
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    author_id INT REFERENCES users(user_id),
    category_id INT REFERENCES categories(category_id),
    title VARCHAR(255) NOT NULL,
    content TEXT,
    published_datetime TIMESTAMP, -- NULL if draft
    status VARCHAR(15) DEFAULT 'Draft' -- e.g., Draft, Published, Archived
);

-- Comments Table
CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(post_id) ON DELETE CASCADE, -- Delete comments if post is deleted
    commenter_id INT REFERENCES users(user_id), -- NULL for anonymous comments
    commenter_name VARCHAR(50), -- Used if commenter_id is NULL
    comment_text TEXT NOT NULL,
    comment_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sample Data
INSERT INTO users (username, email, registration_date) VALUES
('john_doe', 'john.doe@email.com', '2023-01-15 10:00:00'),
('jane_smith', 'jane.s@email.com', '2023-02-20 11:00:00'),
('blog_admin', 'admin@blog.com', '2023-01-01 09:00:00');

INSERT INTO categories (category_name, description) VALUES
('Technology', 'Latest tech news and reviews'),
('Travel', 'Adventures around the world'),
('Food', 'Recipes and restaurant reviews');

INSERT INTO posts (author_id, category_id, title, content, published_datetime, status) VALUES
(1, 1, 'Understanding SQL Views', 'Views are virtual tables...', '2023-10-26 14:00:00', 'Published'),
(2, 2, 'Trip to the Mountains', 'The scenery was breathtaking...', '2023-11-01 09:30:00', 'Published'),
(1, 1, 'Introduction to Docker', 'Docker helps containerize apps...', NULL, 'Draft'),
(2, 3, 'Best Pizza Places', 'Reviewing local pizza joints...', '2023-11-05 16:00:00', 'Published');

INSERT INTO comments (post_id, commenter_id, commenter_name, comment_text, comment_datetime) VALUES
(1, 2, NULL, 'Great explanation, thanks!', '2023-10-26 15:00:00'), -- Jane comments on John's post
(1, NULL, 'Visitor', 'Very helpful article.', '2023-10-27 10:00:00'), -- Anonymous comment
(2, 1, NULL, 'Looks like an amazing trip!', '2023-11-01 11:00:00'); -- John comments on Jane's post

Tasks:

1. Create published_posts_feed View: Create a view named published_posts_feed that shows post_id, title, author’s username, category_name, and published_datetime for all posts with status ‘Published’. Order by published_datetime descending. Requires joining posts, users, and categories.
2. Create user_profiles View: Create a view named user_profiles showing user_id, username, and email for all registered users.
3. Create draft_posts_list View: Create a view named draft_posts_list that lists the post_id, title, and author’s username for all posts currently in ‘Draft’ status. Join posts and users.
4. Create post_comment_summary View: Create a view named post_comment_summary showing post_id, title, and the comment_text, along with the commenter’s identity (use COALESCE(u.username, c.commenter_name, 'Anonymous') AS commenter_display_name). Requires joining posts, comments, and optionally users (use LEFT JOIN for users).
5. Create category_overview View: Create a view named category_overview that lists the category_name and its description.
6. Create author_post_statistics View: Create a view named author_post_statistics showing each author’s username and the total count of posts (COUNT(p.post_id)) they have created (regardless of status). Group by username. Include authors with 0 posts (use LEFT JOIN from users to posts).
7. Query published_posts_feed View: Write a query to select posts from the published_posts_feed view belonging to the ‘Technology’ category.
8. Query post_comment_summary View: Write a query to find all comments associated with the post titled ‘Understanding SQL Views’ using the post_comment_summary view.
9. Modify user_profiles View: Use CREATE OR REPLACE VIEW to modify the user_profiles view to also include the registration_date.
10. Drop draft_posts_list View: Remove the draft_posts_list view from the database.

---

Variant 10: Library System

Scenario: You are developing the database backend for a public library. The system needs to track books, authors, library members, and book loans. Creating views will help librarians easily access common information and generate reports.

Database Schema:

-- Authors Table
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    birth_year INT
);

-- Books Table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT REFERENCES authors(author_id),
    isbn VARCHAR(20) UNIQUE,
    genre VARCHAR(50),
    publication_year INT,
    total_copies INT DEFAULT 1,
    available_copies INT DEFAULT 1 CHECK (available_copies <= total_copies AND available_copies >= 0)
);

-- Members Table
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    join_date DATE DEFAULT CURRENT_DATE,
    membership_status VARCHAR(15) DEFAULT 'Active' -- e.g., Active, Expired, Suspended
);

-- 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 NOT NULL,
    return_date DATE -- NULL means the book is still on loan
);

-- Sample Data
INSERT INTO authors (author_name, birth_year) VALUES
('J.R.R. Tolkien', 1892),
('Isaac Asimov', 1920),
('Ursula K. Le Guin', 1929),
('Frank Herbert', 1920);

INSERT INTO books (title, author_id, isbn, genre, publication_year, total_copies, available_copies) VALUES
('The Hobbit', 1, '978-0547928227', 'Fantasy', 1937, 5, 3),
('Foundation', 2, '978-0553293357', 'Science Fiction', 1951, 3, 1),
('A Wizard of Earthsea', 3, '978-0547773742', 'Fantasy', 1968, 4, 4),
('Dune', 4, '978-0441172719', 'Science Fiction', 1965, 2, 0),
('The Left Hand of Darkness', 3, '978-0441478125', 'Science Fiction', 1969, 3, 3);

INSERT INTO members (first_name, last_name, email, join_date, membership_status) VALUES
('Arthur Dent', 'Ford', 'a.dent@galaxy.net', '2022-08-15', 'Active'),
('Zaphod', 'Beeblebrox', 'z.beeblebrox@galaxy.net', '2023-01-20', 'Active'),
('Trillian', 'Astra', 't.astra@galaxy.net', '2022-08-15', 'Expired');

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'), -- The Hobbit, returned
(2, 1, '2023-10-10', '2023-10-24', NULL),      -- Foundation, currently loaned
(1, 2, '2023-10-12', '2023-10-26', NULL),      -- The Hobbit, currently loaned
(4, 2, '2023-09-15', '2023-09-29', NULL),      -- Dune, overdue loan
(4, 1, '2023-08-01', '2023-08-15', '2023-08-14'); -- Dune, loaned and returned previously

Tasks:

1. Create available_books_list View: Create a view named available_books_list showing the book_id, title, isbn, and author_name for all books where available_copies is greater than 0. Join books and authors.
2. Create active_member_contacts View: Create a view named active_member_contacts showing the member_id, first_name, last_name, and email for members whose membership_status is ‘Active’.
3. Create overdue_loans_alert View: Create a view named overdue_loans_alert that lists the loan_id, book_id (you can join to get title if desired), member_id, loan_date, and due_date for all loans where the return_date is NULL AND the due_date is before the current date (CURRENT_DATE).
4. Create book_details_with_author View: Create a simple view named book_details_with_author joining books and authors to show book_id, title, isbn, genre, publication_year, and author_name.

5. Create member_loan_history View: Create a view named member_loan_history showing member’s full name (first_name

   ’ ‘

   last_name), book title, loan_date, due_date, and return_date. This requires joining members, loans, and books.

6. Create author_book_inventory View: Create a view named author_book_inventory showing each author_name and the total number of book copies (SUM(b.total_copies)) associated with them in the library. Group by author_name.
7. Query available_books_list View: Write a query to select all books from available_books_list view within the ‘Fantasy’ genre.
8. Query member_loan_history View: Write a query to find all loan records in member_loan_history for the member ‘Arthur Dent Ford’, showing only loans that have not yet been returned.
9. Modify active_member_contacts View: Use CREATE OR REPLACE VIEW to modify the active_member_contacts view to also include the join_date.
10. Drop overdue_loans_alert View: Remove the overdue_loans_alert view from the database.

---

Variant 11: Event Management System

Scenario: You’re building a database for a company that organizes professional events like conferences and workshops. The database tracks events, venues where they are held, attendees, and their registrations. Views will simplify common queries for event organizers and attendees.

Database Schema:

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

-- Events Table
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(200) NOT NULL,
    venue_id INT REFERENCES venues(venue_id),
    event_date TIMESTAMP NOT NULL, -- Date and time of the event
    description TEXT,
    category VARCHAR(50) -- e.g., Conference, Workshop, Networking, Webinar
);

-- Attendees Table
CREATE TABLE attendees (
    attendee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    company VARCHAR(100)
);

-- Registrations Table
CREATE TABLE registrations (
    registration_id SERIAL PRIMARY KEY,
    event_id INT REFERENCES events(event_id),
    attendee_id INT REFERENCES attendees(attendee_id),
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ticket_type VARCHAR(20) DEFAULT 'Standard', -- e.g., Standard, VIP, Early Bird
    UNIQUE(event_id, attendee_id) -- Ensure an attendee registers only once per event
);

-- Sample Data
INSERT INTO venues (venue_name, address, capacity) VALUES
('Grand Conference Hall', '123 Main St, Cityville', 500),
('Tech Incubator Hub', '456 Tech Ave, Cityville', 150),
('Downtown Convention Center', '789 Market St, Cityville', 2000);

INSERT INTO events (event_name, venue_id, event_date, description, category) VALUES
('Future of AI Conference', 1, '2024-05-10 09:00:00', 'Exploring advancements in AI and ML.', 'Conference'),
('Web Development Workshop', 2, '2024-05-15 10:00:00', 'Hands-on workshop for modern web tech.', 'Workshop'),
('Digital Marketing Summit', 1, '2024-06-20 09:30:00', 'Strategies for online growth.', 'Conference'),
('Startup Networking Night', 2, '2024-06-25 18:00:00', 'Connect with local entrepreneurs.', 'Networking');

INSERT INTO attendees (first_name, last_name, email, company) VALUES
('Alice', 'Smith', 'alice.s@techcorp.com', 'TechCorp'),
('Bob', 'Johnson', 'bob.j@innovate.com', 'Innovate LLC'),
('Charlie', 'Davis', 'charlie.d@startup.io', 'Startup Inc.'),
('Diana', 'Miller', 'diana.m@freelance.com', NULL);

INSERT INTO registrations (event_id, attendee_id, registration_date, ticket_type) VALUES
(1, 1, '2024-03-01 11:00:00', 'Early Bird'), -- Alice for AI Conf
(1, 2, '2024-03-05 14:20:00', 'Standard'),   -- Bob for AI Conf
(2, 1, '2024-03-10 09:00:00', 'Standard'),   -- Alice for Web Workshop
(2, 3, '2024-03-11 16:45:00', 'Standard'),   -- Charlie for Web Workshop
(3, 2, '2024-04-01 10:15:00', 'VIP'),        -- Bob for Marketing Summit
(4, 4, '2024-05-01 12:00:00', 'Standard');   -- Diana for Networking

Tasks:

1. Create upcoming_events_schedule View: Create a view named upcoming_events_schedule showing event_name, event_date, venue_name, and address for all events scheduled for today or later (event_date >= CURRENT_DATE). Join events and venues.

2. Create attendee_directory View: Create a view named attendee_directory showing attendee_id, full name (first_name

   ’ ‘

   last_name), email, and company for all attendees.

3. Create events_at_tech_hub View: Create a view named events_at_tech_hub that lists the event_name, category, and event_date for all events taking place at the ‘Tech Incubator Hub’ venue.
4. Create conference_attendee_list View: Create a view named conference_attendee_list showing the event_name, attendee’s first_name, last_name, and email specifically for events where the category is ‘Conference’. Requires joining registrations, attendees, and events.
5. Create detailed_registration_report View: Create a view named detailed_registration_report showing registration_id, event_name, event_date, attendee’s full name, attendee’s email, registration_date, and ticket_type. This requires joining registrations, events, and attendees.
6. Create event_registration_count View: Create a view named event_registration_count showing each event_name and the total number of registered attendees (COUNT(r.attendee_id)). Group by event_name. Include events with zero registrations if applicable (use appropriate JOIN).
7. Query upcoming_events_schedule View: Write a query to select events from upcoming_events_schedule that are happening at the ‘Grand Conference Hall’.
8. Query detailed_registration_report View: Write a query to find all registrations in the detailed_registration_report view for the ‘Future of AI Conference’ event, showing only those with ‘Early Bird’ ticket_type.
9. Modify attendee_directory View: Use CREATE OR REPLACE VIEW to modify the attendee_directory view to show email before the company name.
10. Drop events_at_tech_hub View: Remove the events_at_tech_hub view from the database.

---

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, trainers conducting classes, and member attendance. Views will help staff manage memberships, schedules, and track class popularity.

Database Schema:

-- MembershipTypes Table
CREATE TABLE membership_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL UNIQUE, -- e.g., Basic, Premium, Annual, Off-Peak
    monthly_fee NUMERIC(6, 2) NOT NULL
);

-- Members Table
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    join_date DATE DEFAULT CURRENT_DATE,
    type_id INT REFERENCES membership_types(type_id),
    status VARCHAR(15) DEFAULT 'Active' -- e.g., Active, Inactive, Frozen
);

-- Trainers Table
CREATE TABLE trainers (
    trainer_id SERIAL PRIMARY KEY,
    trainer_name VARCHAR(100) NOT NULL,
    specialization VARCHAR(100) -- e.g., Yoga, Strength Training, Cardio
);

-- Classes Table
CREATE TABLE classes (
    class_id SERIAL PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL,
    trainer_id INT REFERENCES trainers(trainer_id),
    schedule_day VARCHAR(10), -- e.g., Monday, Tuesday
    schedule_time TIME,
    duration_minutes INT DEFAULT 60,
    max_capacity INT
);

-- AttendanceLog Table
CREATE TABLE attendance_log (
    log_id SERIAL PRIMARY KEY,
    member_id INT REFERENCES members(member_id),
    class_id INT REFERENCES classes(class_id),
    attendance_date DATE DEFAULT CURRENT_DATE -- Records the specific date the member attended
);

-- Sample Data
INSERT INTO membership_types (type_name, monthly_fee) VALUES
('Basic', 30.00),
('Premium', 50.00),
('Annual', 500.00); -- Let's simplify and assume Annual is paid upfront but track 'type'

INSERT INTO members (first_name, last_name, email, join_date, type_id, status) VALUES
('Clark', 'Kent', 'clark.k@dailyplanet.com', '2023-01-10', 2, 'Active'),
('Lois', 'Lane', 'lois.l@dailyplanet.com', '2023-01-10', 2, 'Active'),
('Bruce', 'Wayne', 'bruce.w@waynecorp.com', '2022-11-01', 3, 'Active'),
('Diana', 'Prince', 'diana.p@museum.org', '2023-03-15', 1, 'Frozen');

INSERT INTO trainers (trainer_name, specialization) VALUES
('John Doe', 'Strength Training'),
('Jane Smith', 'Yoga & Pilates'),
('Robert Paulson', 'Cardio & HIIT');

INSERT INTO classes (class_name, trainer_id, schedule_day, schedule_time, duration_minutes, max_capacity) VALUES
('Morning Yoga', 2, 'Monday', '08:00:00', 60, 20),
('Power Lifting', 1, 'Tuesday', '18:00:00', 75, 15),
('HIIT Blast', 3, 'Wednesday', '19:00:00', 45, 25),
('Evening Pilates', 2, 'Thursday', '18:30:00', 60, 20),
('Circuit Training', 1, 'Friday', '17:00:00', 60, 20);

INSERT INTO attendance_log (member_id, class_id, attendance_date) VALUES
(1, 1, '2023-10-02'), -- Clark attended Morning Yoga on Oct 2nd
(2, 1, '2023-10-02'), -- Lois attended Morning Yoga on Oct 2nd
(1, 2, '2023-10-03'), -- Clark attended Power Lifting on Oct 3rd
(3, 5, '2023-10-06'), -- Bruce attended Circuit Training on Oct 6th
(1, 1, '2023-10-09'); -- Clark attended Morning Yoga on Oct 9th

Tasks:

1. Create active_members_info View: Create a view named active_members_info showing member_id, first_name, last_name, email, and type_name for all members whose status is ‘Active’. Join members and membership_types.
2. Create member_primary_contact View: Create a view named member_primary_contact showing only the first_name, last_name, and email of all members, regardless of status.
3. Create yoga_pilates_schedule View: Create a view named yoga_pilates_schedule that lists the class_name, schedule_day, schedule_time, and trainer_name for classes taught by trainers specializing in ‘Yoga & Pilates’ (or specifically by ‘Jane Smith’). Join classes and trainers.
4. Create class_schedule_detailed View: Create a view named class_schedule_detailed that displays class_name, trainer_name, specialization, schedule_day, schedule_time, duration_minutes, and max_capacity. Join classes and trainers.
5. Create member_attendance_history View: Create a view named member_attendance_history showing the member’s full name, class_name, and attendance_date. Requires joining members, attendance_log, and classes.
6. Create class_popularity_report View: Create a view named class_popularity_report showing each class_name and the total number of attendances logged (COUNT(al.log_id)) for that class. Group by class_name.
7. Query active_members_info View: Write a query to select all members from active_members_info who have a ‘Premium’ membership type.
8. Query member_attendance_history View: Write a query to find all attendance records in member_attendance_history for ‘Clark Kent’, ordered by attendance_date descending.
9. Modify member_primary_contact View: Use CREATE OR REPLACE VIEW to modify the member_primary_contact view to also include the member’s join_date.
10. Drop yoga_pilates_schedule View: Remove the yoga_pilates_schedule view from the database.

---

Variant 13: Recipe Database

Scenario: You are managing a database for a recipe-sharing website. The database stores information about recipes, ingredients, the cuisines they belong to, and user ratings. You need to create views to simplify common data retrieval tasks.

Database Schema:

-- Cuisines Table
CREATE TABLE cuisines (
    cuisine_id SERIAL PRIMARY KEY,
    cuisine_name VARCHAR(50) NOT NULL UNIQUE -- e.g., Italian, Mexican, Indian
);

-- Ingredients Table
CREATE TABLE ingredients (
    ingredient_id SERIAL PRIMARY KEY,
    ingredient_name VARCHAR(100) NOT NULL UNIQUE,
    category VARCHAR(50) -- e.g., Vegetable, Meat, Spice, Dairy
);

-- Recipes Table
CREATE TABLE recipes (
    recipe_id SERIAL PRIMARY KEY,
    recipe_name VARCHAR(150) NOT NULL,
    cuisine_id INT REFERENCES cuisines(cuisine_id),
    prep_time_minutes INT, -- Preparation time
    cook_time_minutes INT, -- Cooking time
    instructions TEXT
);

-- Recipe_Ingredients Table (Many-to-Many between Recipes and Ingredients)
CREATE TABLE recipe_ingredients (
    recipe_ingredient_id SERIAL PRIMARY KEY,
    recipe_id INT REFERENCES recipes(recipe_id),
    ingredient_id INT REFERENCES ingredients(ingredient_id),
    quantity VARCHAR(50) -- e.g., '2 cups', '1 tsp', '100g'
);

-- User_Ratings Table
CREATE TABLE user_ratings (
    rating_id SERIAL PRIMARY KEY,
    recipe_id INT REFERENCES recipes(recipe_id),
    user_id INT, -- Assuming user IDs exist elsewhere, simplified here
    rating INT CHECK (rating >= 1 AND rating <= 5), -- e.g., 1 to 5 stars
    rating_date DATE DEFAULT CURRENT_DATE
);


-- Sample Data
INSERT INTO cuisines (cuisine_name) VALUES
('Italian'), ('Mexican'), ('Indian');

INSERT INTO ingredients (ingredient_name, category) VALUES
('Tomato', 'Vegetable'), ('Onion', 'Vegetable'), ('Garlic', 'Vegetable'),
('Pasta', 'Grain'), ('Chicken Breast', 'Meat'), ('Chili Powder', 'Spice'),
('Cumin', 'Spice'), ('Olive Oil', 'Oil'), ('Cheese', 'Dairy');

INSERT INTO recipes (recipe_name, cuisine_id, prep_time_minutes, cook_time_minutes, instructions) VALUES
('Spaghetti Bolognese', 1, 20, 40, 'Brown meat, add onions, garlic, tomatoes... Simmer... Serve over pasta.'),
('Chicken Tacos', 2, 15, 20, 'Cook chicken with spices... Warm tortillas... Assemble tacos.'),
('Simple Pasta Pomodoro', 1, 10, 15, 'Sauté garlic in olive oil, add tomatoes... Simmer... Toss with pasta.'),
('Chicken Curry', 3, 25, 35, 'Sauté onions, garlic, ginger... Add spices, chicken, tomatoes, coconut milk... Simmer.');

INSERT INTO recipe_ingredients (recipe_id, ingredient_id, quantity) VALUES
(1, 1, '1 can'), (1, 2, '1 medium'), (1, 3, '2 cloves'), (1, 4, '400g'), (1, 5, '500g'), (1, 8, '2 tbsp'),
(2, 5, '2 medium'), (2, 2, '1 small'), (2, 6, '1 tbsp'), (2, 7, '1 tsp'),
(3, 1, '1 large can'), (3, 3, '3 cloves'), (3, 8, '3 tbsp'), (3, 4, '300g'), (3, 9, '50g grated'),
(4, 5, '600g'), (4, 2, '1 large'), (4, 3, '4 cloves'), (4, 7, '1.5 tsp');

INSERT INTO user_ratings (recipe_id, user_id, rating, rating_date) VALUES
(1, 101, 5, '2023-10-01'),
(1, 102, 4, '2023-10-02'),
(2, 101, 4, '2023-10-05'),
(3, 103, 5, '2023-10-06'),
(1, 103, 4, '2023-11-01');

Tasks:

1. Create recipe_summary View: Create a view named recipe_summary that shows recipe_name, cuisine_name, prep_time_minutes, and cook_time_minutes. Join recipes and cuisines.
2. Create ingredient_list View: Create a view named ingredient_list showing only the ingredient_name and category for all ingredients, ordered by category then ingredient_name.
3. Create quick_italian_recipes View: Create a view named quick_italian_recipes listing the recipe_name for Italian recipes (cuisine_name = ‘Italian’) where the total time (prep_time_minutes + cook_time_minutes) is less than 45 minutes.
4. Create recipe_ingredient_details View: Create a view named recipe_ingredient_details that displays the recipe_name, ingredient_name, and quantity needed for each ingredient in a recipe. Join recipes, recipe_ingredients, and ingredients.
5. Create average_recipe_ratings View: Create a view named average_recipe_ratings showing recipe_name and the average rating (AVG(rating)) rounded to 2 decimal places, aliased as average_rating. Only include recipes that have at least one rating. Group by recipe_name.
6. Create recipes_using_chicken View: Create a view named recipes_using_chicken that lists the recipe_name for all recipes that use ‘Chicken Breast’. Join recipes, recipe_ingredients, and ingredients.
7. Query recipe_summary View: Write a query to select all recipes from the recipe_summary view belonging to the ‘Mexican’ cuisine.
8. Query average_recipe_ratings View: Write a query to find recipes in the average_recipe_ratings view with an average rating greater than 4.0.
9. Modify ingredient_list View: Use CREATE OR REPLACE VIEW to modify the ingredient_list view to also include the ingredient_id.
10. Drop quick_italian_recipes View: Remove the quick_italian_recipes view from the database.

---

Variant 14: Real Estate Agency

Scenario: You are managing the database for a real estate agency. The database tracks properties for sale, real estate agents, clients (buyers/sellers), and property viewings. Views are needed to simplify common queries for agents and managers.

Database Schema:

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

-- Properties Table
CREATE TABLE properties (
    property_id SERIAL PRIMARY KEY,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(50),
    zip_code VARCHAR(10),
    property_type VARCHAR(50), -- e.g., House, Apartment, Condo
    bedrooms INT,
    bathrooms NUMERIC(3,1), -- e.g., 2.5 for 2 full, 1 half bath
    square_footage INT,
    listing_price NUMERIC(12, 2),
    listing_date DATE,
    status VARCHAR(20) DEFAULT 'For Sale', -- e.g., For Sale, Under Contract, Sold
    agent_id INT REFERENCES agents(agent_id) -- Agent responsible for the listing
);

-- Clients Table
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    client_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    client_type VARCHAR(10) -- 'Buyer' or 'Seller'
);

-- Viewings Table
CREATE TABLE viewings (
    viewing_id SERIAL PRIMARY KEY,
    property_id INT REFERENCES properties(property_id),
    client_id INT REFERENCES clients(client_id), -- Client who viewed
    agent_id INT REFERENCES agents(agent_id), -- Agent who conducted the viewing
    viewing_date TIMESTAMP,
    client_feedback TEXT
);

-- Sample Data
INSERT INTO agents (agent_name, email, phone, hire_date) VALUES
('Alice Green', 'alice.g@realestate.com', '555-1111', '2019-03-15'),
('Bob White', 'bob.w@realestate.com', '555-2222', '2020-07-01'),
('Carol Black', 'carol.b@realestate.com', '555-3333', '2021-11-20');

INSERT INTO properties (address, city, zip_code, property_type, bedrooms, bathrooms, square_footage, listing_price, listing_date, status, agent_id) VALUES
('123 Oak St', 'Metropolis', '12345', 'House', 3, 2.0, 1800, 350000.00, '2023-08-01', 'For Sale', 1),
('456 Maple Ave', 'Metropolis', '12346', 'Apartment', 2, 1.0, 950, 180000.00, '2023-09-10', 'For Sale', 2),
('789 Pine Ln', 'Gotham', '67890', 'House', 4, 2.5, 2400, 480000.00, '2023-07-15', 'Sold', 1),
('101 Birch Rd', 'Metropolis', '12345', 'Condo', 2, 2.0, 1200, 250000.00, '2023-10-05', 'Under Contract', 3);

INSERT INTO clients (client_name, email, phone, client_type) VALUES
('David King', 'david.k@email.com', '555-4444', 'Buyer'),
('Eva Queen', 'eva.q@email.com', '555-5555', 'Seller'), -- Corresponds to 123 Oak St (implied)
('Frank Prince', 'frank.p@email.com', '555-6666', 'Buyer');

INSERT INTO viewings (property_id, client_id, agent_id, viewing_date, client_feedback) VALUES
(1, 1, 1, '2023-08-10 14:00:00', 'Liked the backyard, kitchen needs update.'),
(2, 1, 2, '2023-09-15 11:00:00', 'Good location, but a bit small.'),
(1, 3, 1, '2023-08-12 16:30:00', 'Very interested, considering an offer.'),
(4, 3, 3, '2023-10-10 10:00:00', 'Loved the condo amenities.');

Tasks:

1. Create active_listings View: Create a view named active_listings showing property_id, address, city, listing_price, and agent_name for all properties currently ‘For Sale’. Join properties and agents.
2. Create agent_contact_list View: Create a view named agent_contact_list showing only the agent_name, email, and phone for all agents.
3. Create large_homes_metropolis View: Create a view named large_homes_metropolis that lists the address and square_footage for properties in ‘Metropolis’ with 3 or more bedrooms and over 2000 square_footage.
4. Create viewing_schedule View: Create a view named viewing_schedule that displays viewing_date, property_address (from properties.address), client_name, and the agent_name who conducted the viewing. Join viewings, properties, clients, and agents.
5. Create agent_listing_count View: Create a view named agent_listing_count showing each agent_name and the total number of properties (COUNT(p.property_id)) they are currently listing (status = ‘For Sale’). Group by agent_name. Include agents even if they have 0 active listings.
6. Create client_viewing_history View: Create a view named client_viewing_history that lists the client_name and the address of the properties they have viewed, along with the viewing_date. Join clients, viewings, and properties.
7. Query active_listings View: Write a query to select all listings from the active_listings view handled by ‘Alice Green’.
8. Query viewing_schedule View: Write a query to find all viewings in the viewing_schedule view that occurred after ‘2023-09-01’, ordered by date.
9. Modify agent_contact_list View: Use CREATE OR REPLACE VIEW to modify the agent_contact_list view to also include the hire_date.
10. Drop large_homes_metropolis View: Remove the large_homes_metropolis view from the database.

---

Variant 15: Manufacturing Production Line

Scenario: You are managing a database for a factory’s production line. The database tracks manufactured products, the components they use, the production lines where they are assembled, and quality control checks. Views are needed for reporting and monitoring.

Database Schema:

-- Components Table
CREATE TABLE components (
    component_id SERIAL PRIMARY KEY,
    component_name VARCHAR(100) NOT NULL UNIQUE,
    supplier VARCHAR(100),
    cost NUMERIC(8, 2)
);

-- Products Table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_sku VARCHAR(50) NOT NULL UNIQUE, -- Stock Keeping Unit
    product_name VARCHAR(150) NOT NULL,
    assembly_time_minutes INT -- Estimated time to assemble
);

-- Bill_Of_Materials Table (Links Products to Components)
CREATE TABLE bill_of_materials (
    bom_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    component_id INT REFERENCES components(component_id),
    quantity_required INT NOT NULL
);

-- Production_Lines Table
CREATE TABLE production_lines (
    line_id SERIAL PRIMARY KEY,
    line_name VARCHAR(50) NOT NULL UNIQUE, -- e.g., Line A, Line B
    location VARCHAR(100) -- e.g., Building 1, Floor 2
);

-- Production_Log Table
CREATE TABLE production_log (
    log_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    line_id INT REFERENCES production_lines(line_id),
    units_produced INT NOT NULL,
    production_date DATE DEFAULT CURRENT_DATE,
    shift INT -- e.g., 1, 2, 3
);

-- Quality_Control Table
CREATE TABLE quality_control (
    qc_id SERIAL PRIMARY KEY,
    log_id INT REFERENCES production_log(log_id), -- Links to a specific production run
    check_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(10) NOT NULL, -- 'Pass' or 'Fail'
    notes TEXT
);

-- Sample Data
INSERT INTO components (component_name, supplier, cost) VALUES
('Casing A', 'SupplierX', 5.50),
('Circuit Board V1', 'SupplierY', 12.75),
('Power Supply Unit', 'SupplierX', 8.00),
('Screw Pack M3', 'SupplierZ', 0.50),
('Display Panel', 'SupplierY', 25.00);

INSERT INTO products (product_sku, product_name, assembly_time_minutes) VALUES
('WIDGET-001', 'Standard Widget', 30),
('GADGET-X', 'Advanced Gadget', 75),
('DOODAD-BLUE', 'Blue Doodad', 20);

INSERT INTO bill_of_materials (product_id, component_id, quantity_required) VALUES
(1, 1, 1), (1, 2, 1), (1, 3, 1), (1, 4, 4), -- Widget components
(2, 1, 1), (2, 2, 2), (2, 3, 1), (2, 4, 8), (2, 5, 1), -- Gadget components
(3, 1, 1), (3, 4, 2); -- Doodad components

INSERT INTO production_lines (line_name, location) VALUES
('Assembly Line 1', 'Building A - West Wing'),
('Assembly Line 2', 'Building B - East Wing'),
('Testing Bench', 'Building A - QC Area');

INSERT INTO production_log (product_id, line_id, units_produced, production_date, shift) VALUES
(1, 1, 100, '2023-11-01', 1),
(2, 2, 50, '2023-11-01', 1),
(1, 1, 120, '2023-11-01', 2),
(3, 1, 200, '2023-11-02', 1);

INSERT INTO quality_control (log_id, check_timestamp, status, notes) VALUES
(1, '2023-11-01 10:00:00', 'Pass', 'Sample check ok.'),
(2, '2023-11-01 11:30:00', 'Pass', NULL),
(3, '2023-11-01 18:00:00', 'Fail', '3 units had cosmetic defects.'),
(3, '2023-11-01 18:05:00', 'Pass', 'Reworked units passed re-inspection.'),
(4, '2023-11-02 09:45:00', 'Pass', NULL);

Tasks:

1. Create product_component_list View: Create a view named product_component_list that shows product_name, component_name, and quantity_required for each product. Join products, bill_of_materials, and components.
2. Create component_suppliers View: Create a view named component_suppliers showing only the component_name and supplier for all components, ordered by supplier.
3. Create failed_qc_checks View: Create a view named failed_qc_checks that lists the log_id, check_timestamp, and notes for all quality control checks with a status of ‘Fail’.
4. Create daily_production_summary View: Create a view named daily_production_summary that displays production_date, product_name, line_name, and units_produced. Join production_log, products, and production_lines.
5. Create product_component_cost View: Create a view named product_component_cost showing product_name and the total cost of its components (Sum of bom.quantity_required * c.cost), aliased as total_component_cost. Group by product_name. Join products, bill_of_materials, and components.
6. Create line_production_totals View: Create a view named line_production_totals that shows the line_name and the total units_produced for each production line across all dates/products recorded in the log. Group by line_name.
7. Query product_component_list View: Write a query to select all components required for the ‘Advanced Gadget’ from the product_component_list view.
8. Query daily_production_summary View: Write a query to find all production records in the daily_production_summary view for ‘2023-11-01’.
9. Modify component_suppliers View: Use CREATE OR REPLACE VIEW to modify the component_suppliers view to also include the component cost.
10. Drop failed_qc_checks View: Remove the failed_qc_checks view from the database.

---

Variant 16: Meteorological Data Tracking

Scenario: You are working with a database that collects weather data from various stations. You need to create views to simplify access to readings, station information, and summary statistics.

Database Schema:

-- Locations Table (Where stations are physically located)
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    city VARCHAR(100) NOT NULL,
    country_code CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2
    latitude NUMERIC(8, 6), -- e.g., 40.7128
    longitude NUMERIC(9, 6) -- e.g., -74.0060
);

-- WeatherStations Table
CREATE TABLE weather_stations (
    station_id SERIAL PRIMARY KEY,
    station_name VARCHAR(100) NOT NULL UNIQUE,
    location_id INT REFERENCES locations(location_id),
    elevation_meters INT,
    established_date DATE,
    status VARCHAR(20) DEFAULT 'Active' -- Active, Inactive, Maintenance
);

-- Readings Table
CREATE TABLE readings (
    reading_id BIGSERIAL PRIMARY KEY, -- Use BIGSERIAL for potentially large number of readings
    station_id INT REFERENCES weather_stations(station_id),
    reading_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    temperature_celsius NUMERIC(4, 1), -- e.g., 25.5 C
    humidity_percent NUMERIC(4, 1), -- e.g., 60.2 %
    pressure_hpa NUMERIC(6, 1), -- e.g., 1013.2 hPa
    wind_speed_kph NUMERIC(5, 1)
);

-- Meteorologists Table (Staff managing stations or analyzing data)
CREATE TABLE meteorologists (
    met_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    specialty VARCHAR(100), -- e.g., Climatology, Forecasting
    assigned_station_id INT REFERENCES weather_stations(station_id) NULL -- A meteorologist might oversee a specific station
);

-- Sample Data
INSERT INTO locations (city, country_code, latitude, longitude) VALUES
('London', 'GB', 51.5074, -0.1278),
('Tokyo', 'JP', 35.6895, 139.6917),
('New York', 'US', 40.7128, -74.0060);

INSERT INTO weather_stations (station_name, location_id, elevation_meters, established_date, status) VALUES
('Heathrow Main', 1, 25, '1980-05-10', 'Active'),
('Shibuya Crossing Tower', 2, 40, '2005-11-20', 'Active'),
('Central Park Kiosk', 3, 50, '1995-03-15', 'Maintenance'),
('London City Airport', 1, 5, '1999-08-01', 'Active');

INSERT INTO readings (station_id, reading_timestamp, temperature_celsius, humidity_percent, pressure_hpa, wind_speed_kph) VALUES
(1, '2024-01-10 10:00:00+00', 8.5, 75.2, 1005.1, 15.3),
(2, '2024-01-10 19:00:00+09', 5.2, 50.0, 1015.5, 5.0),
(1, '2024-01-10 11:00:00+00', 9.0, 74.8, 1005.0, 16.1),
(4, '2024-01-10 11:00:00+00', 9.1, 78.0, 1004.9, 18.0),
(2, '2024-01-10 20:00:00+09', 4.9, 51.5, 1015.8, 5.5);

INSERT INTO meteorologists (full_name, specialty, assigned_station_id) VALUES
('Dr. Evelyn Reed', 'Climatology', 1),
('Kenji Tanaka', 'Forecasting', 2),
('Sam Weatherby', 'Atmospheric Physics', NULL);

Tasks:

1. Create station_locations View: Create a view named station_locations showing the station_name, status, city, and country_code. Join weather_stations and locations.
2. Create met_directory View: Create a view named met_directory showing only the full_name and specialty of all meteorologists.
3. Create high_humidity_readings View: Create a view named high_humidity_readings showing reading_id, station_id, reading_timestamp, and humidity_percent for readings where humidity is greater than 75%.
4. Create stations_under_maintenance View: Create a view named stations_under_maintenance showing the station_name and established_date for all stations with status = ‘Maintenance’.
5. Create detailed_readings_info View: Create a view named detailed_readings_info showing reading_timestamp, temperature_celsius, humidity_percent, pressure_hpa, wind_speed_kph, along with the station_name and city. This requires joining readings, weather_stations, and locations.
6. Create avg_temp_per_station View: Create a view named avg_temp_per_station showing the station_name and the average temperature_celsius calculated from all its readings. Use AVG() and GROUP BY. Format the average temperature to one decimal place (::NUMERIC(4,1)).
7. Query station_locations View: Write a query to select all stations from the station_locations view located in the country ‘GB’.
8. Query detailed_readings_info View: Write a query to find all readings in the detailed_readings_info view from the ‘Heathrow Main’ station, ordered by reading_timestamp descending.
9. Modify met_directory View: Use CREATE OR REPLACE VIEW to modify the met_directory view to also include the met_id.
10. Drop stations_under_maintenance View: Remove the stations_under_maintenance view from the database.

---

Variant 17: Vehicle Fleet Management

Scenario: You manage a database for a company’s vehicle fleet, tracking vehicles, drivers, trips, and maintenance. Views are needed to streamline reporting and data access for different user roles.

Database Schema:

-- Drivers Table
CREATE TABLE drivers (
    driver_id SERIAL PRIMARY KEY,
    driver_name VARCHAR(100) NOT NULL,
    license_number VARCHAR(50) NOT NULL UNIQUE,
    hire_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
);

-- Vehicles Table
CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    license_plate VARCHAR(15) NOT NULL UNIQUE,
    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year INT,
    vehicle_type VARCHAR(30), -- e.g., Sedan, Van, Truck
    status VARCHAR(20) DEFAULT 'Operational' -- Operational, Maintenance, Decommissioned
);

-- MaintenanceLogs Table
CREATE TABLE maintenance_logs (
    log_id SERIAL PRIMARY KEY,
    vehicle_id INT REFERENCES vehicles(vehicle_id),
    service_date DATE NOT NULL,
    service_type VARCHAR(100) NOT NULL, -- e.g., Oil Change, Tire Rotation, Engine Repair
    cost NUMERIC(8, 2),
    notes TEXT
);

-- Trips Table
CREATE TABLE trips (
    trip_id SERIAL PRIMARY KEY,
    vehicle_id INT REFERENCES vehicles(vehicle_id),
    driver_id INT REFERENCES drivers(driver_id),
    start_datetime TIMESTAMP WITH TIME ZONE NOT NULL,
    end_datetime TIMESTAMP WITH TIME ZONE, -- Can be NULL if trip is ongoing
    start_odometer_km INT,
    end_odometer_km INT,
    purpose VARCHAR(255)
);

-- Sample Data
INSERT INTO drivers (driver_name, license_number, hire_date, is_active) VALUES
('Alice Ray', 'DL123456', '2020-03-15', TRUE),
('Bob Carr', 'DL987654', '2021-07-22', TRUE),
('Charlie Fox', 'DL555111', '2019-01-10', FALSE); -- Inactive driver

INSERT INTO vehicles (license_plate, make, model, year, vehicle_type, status) VALUES
('FLEET-001', 'Toyota', 'Camry', 2021, 'Sedan', 'Operational'),
('FLEET-002', 'Ford', 'Transit', 2020, 'Van', 'Operational'),
('FLEET-003', 'Toyota', 'Camry', 2021, 'Sedan', 'Maintenance'),
('FLEET-004', 'Honda', 'Civic', 2019, 'Sedan', 'Decommissioned');

INSERT INTO maintenance_logs (vehicle_id, service_date, service_type, cost, notes) VALUES
(1, '2023-12-01', 'Oil Change', 75.50, 'Synthetic oil used'),
(2, '2023-11-15', 'Tire Rotation', 50.00, NULL),
(3, '2024-01-05', 'Check Engine Light', 250.80, 'Replaced O2 sensor');

INSERT INTO trips (vehicle_id, driver_id, start_datetime, end_datetime, start_odometer_km, end_odometer_km, purpose) VALUES
(1, 1, '2024-01-08 09:00:00+00', '2024-01-08 11:30:00+00', 50100, 50185, 'Client Meeting Site A'),
(2, 2, '2024-01-09 13:00:00+00', '2024-01-09 17:00:00+00', 75200, 75350, 'Equipment Delivery'),
(1, 1, '2024-01-10 08:30:00+00', NULL, 50200, NULL, 'Client Meeting Site B - Ongoing'); -- Ongoing trip

Tasks:

1. Create operational_vehicles View: Create a view named operational_vehicles showing license_plate, make, model, and year for all vehicles with status = ‘Operational’.
2. Create active_driver_licenses View: Create a view named active_driver_licenses showing only the driver_name and license_number for drivers where is_active is TRUE.
3. Create recent_maintenance View: Create a view named recent_maintenance showing log_id, vehicle_id, service_date, and service_type for maintenance logs recorded within the last 90 days (service_date >= CURRENT_DATE - INTERVAL '90 days').
4. Create vans_list View: Create a view named vans_list displaying the license_plate, make, and model for all vehicles where vehicle_type = ‘Van’.
5. Create trip_summary View: Create a view named trip_summary showing trip_id, start_datetime, driver_name, license_plate, and purpose. This requires joining trips, drivers, and vehicles.
6. Create vehicle_maintenance_cost View: Create a view named vehicle_maintenance_cost showing the license_plate and the total maintenance cost (SUM(ml.cost)) for each vehicle. Join vehicles and maintenance_logs. Group by license_plate. Handle vehicles with no maintenance logs (they should appear with 0 or NULL cost - use a LEFT JOIN).
7. Query active_driver_licenses View: Write a query to select the driver name from the active_driver_licenses view for license number ‘DL123456’.
8. Query trip_summary View: Write a query to find all trips in the trip_summary view driven by ‘Alice Ray’, ordered by start_datetime descending.
9. Modify operational_vehicles View: Use CREATE OR REPLACE VIEW to modify the operational_vehicles view to also include the vehicle_type.
10. Drop vans_list View: Remove the vans_list view from the database.

---

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