Databases Tasks for Practical Class 8 (953-23 AXI)

Creating Queries Using Standard Functions in PostgreSQL.

Variant 1: Library Management System

Scenario: You are managing a database for a local library. You need to perform various queries related to members, books, and loans.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS loans;
-- DROP TABLE IF EXISTS books;
-- DROP TABLE IF EXISTS members;

-- 1. Create the '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,      -- For LOWER() and UNIQUE practice
    join_date DATE NOT NULL,
    membership_level VARCHAR(10) DEFAULT 'Bronze', -- For CASE or NULLIF
    postal_code VARCHAR(10)        -- Might have extra spaces, for TRIM()
);

-- 2. Create the 'books' table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100),
    isbn VARCHAR(20) UNIQUE,          -- May have hyphens/spaces, for REPLACE()
    publication_year INTEGER,       -- For EXTRACT() or CAST()
    list_price NUMERIC(7, 2)         -- For ROUND(), CEIL(), FLOOR(), ABS()
);

-- 3. Create the 'loans' table
CREATE TABLE loans (
    loan_id SERIAL PRIMARY KEY,
    book_id INTEGER REFERENCES books(book_id),
    member_id INTEGER REFERENCES members(member_id),
    loan_date DATE NOT NULL DEFAULT CURRENT_DATE,
    due_date DATE NOT NULL,
    return_date DATE                -- NULL if not returned, for COALESCE(), AGE()
);

-- Sample Data Insertion --

INSERT INTO members (first_name, last_name, email, join_date, membership_level, postal_code) VALUES
('Arthur', 'Dent', 'a.dent@galaxy.net', '2021-03-15', 'Silver', ' SW1A0AA'),
('Ford', 'Prefect', 'ford.p@betelgeuse.com', '2021-03-15', 'Gold', 'BG5-XQ1 '),
('Zaphod', 'Beeblebrox', 'z.beeblebrox@president.gov', '2022-11-01', NULL, 'HHGTTG'), -- NULL level
('Trillian', 'Astra', 'trillian@heartofgold.spc', '2020-08-25', 'Gold', ' E1 6AN'),
('Marvin', 'Android', 'paranoid.android@sirius.cyb', '2023-01-10', 'Bronze', ' ROBOT01');

INSERT INTO books (title, author, isbn, publication_year, list_price) VALUES
('The Hitchhiker''s Guide', 'Douglas Adams', '0-345-39180-2', 1979, 15.99),
('Dirk Gently''s Holistic Detective Agency', 'Douglas Adams', ' 0-671-69464-4', 1987, 18.50), -- Leading space ISBN
('Good Omens', 'Terry Pratchett & Neil Gaiman', '0-575-04800-X ', 1990, 22.00), -- Trailing space ISBN
('Stardust', 'Neil Gaiman', '0-380-97728-1', 1999, 14.75),
('Hyperion', 'Dan Simmons', '978-0553283686', 1989, 19.95);

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'),
(2, 2, '2023-10-05', '2023-10-26', NULL), -- Overdue
(3, 1, '2023-10-10', '2023-10-31', NULL), -- Not yet due (assume today is Oct 26th)
(4, 3, '2023-09-15', '2023-10-06', '2023-10-10'), -- Returned late
(5, 4, '2023-10-20', '2023-11-10', NULL),
(1, 5, '2023-08-01', '2023-08-22', NULL); -- Very overdue

Tasks for Variant 1:

Member Full Names: Select the member_id and generate a full name string for each member by concatenating their first_name and last_name, separated by a space. Alias this column as full_name.
Cleaned ISBNs: Display the title and isbn for all books. Clean the isbn by removing both leading/trailing spaces and any hyphens (‘-‘). Alias the cleaned column as cleaned_isbn. (Hint: You might need TRIM and REPLACE).
Membership Duration: Calculate how long each member has been part of the library as of CURRENT_DATE. Display the member’s first_name, join_date, and the calculated duration using AGE(). Alias the duration column as membership_duration.
Loan Status: For all loans, display the loan_id, due_date, and a calculated status. The status should be ‘Overdue’ if the return_date is NULL AND the due_date is before CURRENT_DATE. The status should be ‘Returned’ if return_date is NOT NULL. Otherwise, the status should be ‘On Loan’. (Hint: Use CASE and check for NULL).
Book Pricing Categories: Show the title, list_price, and a price_category for each book. Categorize as ‘Budget’ if price < $15.00, ‘Standard’ if price is between $15.00 and $19.99 (inclusive), and ‘Premium’ if $20.00 or more.
Member Email Lowercase: Display the first_name, last_name, and email address for all members, ensuring the email address is entirely in lowercase. Alias the lowercase email column as lower_email.
Book Title Abbreviation: Show the book_id and the first 15 characters of each book’s title. If the title is longer than 15 characters, append ‘…’. Alias this as short_title. (Hint: Use SUBSTRING and potentially LENGTH and CONCAT/||).
Loan Period: Calculate the number of days between the loan_date and due_date for each loan. Display loan_id and the calculated number of days as loan_period_days. (Hint: Date subtraction results in an integer number of days).
Price Rounded Up: Display the title and list_price for each book, along with the price rounded UP to the nearest whole dollar amount. Alias this as price_ceil.
Membership Level Display: Show the member_id and first_name. Also display the membership_level. If the membership_level is NULL, display ‘Standard’ instead. Alias this column as effective_level. (Hint: Use COALESCE).
Extract Join Quarter: Display the first_name and join_date for each member. Also, extract the calendar quarter (1, 2, 3, or 4) from their join_date. Alias this as join_quarter. (Hint: Use EXTRACT(QUARTER FROM ...)).
Postal Code Cleanup: Display the member_id and the postal_code for each member, removing any leading or trailing spaces. Alias the cleaned column as trimmed_postal_code.
Absolute Price Difference: Calculate the absolute difference between each book’s list_price and a target price of $18.00. Display the title, list_price, and the calculated absolute_difference.
Numeric ISBN Check (Conceptual): Identify books where the cleaned_isbn (from Task 2 logic) contains non-numeric characters (excluding potential ‘X’ at the end, common in ISBN-10). This is more complex; for this assignment, simply check if REPLACE(TRIM(isbn), '-', '') results in NULL when attempting to cast to a NUMERIC type (use NULLIF combined with a check or a more advanced technique if known, otherwise describe the logic). For a simpler version: Return NULL using NULLIF if the publication_year is exactly 1990. Alias this as null_if_1990_pub.
Member ID Padding: Display the member_id for all members, formatted as a 5-digit string with leading zeros (e.g., 1 becomes ‘00001’). Alias this as formatted_member_id. (Hint: Use LPAD after casting).

Variant 2: University Course Enrollment

Scenario: You are managing a database for university enrollments, tracking students, courses, and their grades.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS enrollments;
-- DROP TABLE IF EXISTS courses;
-- DROP TABLE IF EXISTS students;

-- 1. Create the 'students' table
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),         -- Allows NULLs, for COALESCE
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL, -- For LOWER(), UNIQUE
    date_of_birth DATE,              -- For AGE(), EXTRACT()
    major VARCHAR(50)                -- For CASE, NULLIF
);

-- 2. Create the 'courses' table
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_code VARCHAR(15) UNIQUE NOT NULL, -- e.g., ' CS 101 ', for TRIM()
    course_name VARCHAR(100) NOT NULL,    -- For UPPER(), SUBSTRING()
    credits NUMERIC(3, 1),                -- e.g., 3.0, 1.5. For ROUND(), ABS()
    department VARCHAR(50)                -- For general queries
);

-- 3. Create the 'enrollments' table
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    enrollment_date DATE DEFAULT CURRENT_DATE, -- For EXTRACT(), INTERVAL
    grade NUMERIC(4, 1)                        -- e.g. 85.5, 92.0. Allows NULL if not graded. For CEIL(), FLOOR(), CASE
);

-- Sample Data Insertion --

INSERT INTO students (first_name, middle_name, last_name, email, date_of_birth, major) VALUES
('Alice', 'Marie', 'Wonder', 'alice.wonder@uni.edu', '2003-04-10', 'Literature'),
('Bob', NULL, 'Builder', 'bob.the.builder@uni.edu', '2002-08-20', 'Engineering'),
('Charlie', 'Chaplin', 'Jr', 'charlie.jr@uni.edu', '2004-01-05', NULL), -- NULL Major
('Dorothy', 'Gale', 'Oz', 'dorothy.oz@uni.edu', '2003-11-11', 'Meteorology'),
('Eve', 'Ada', 'Lovelace', 'eve.lovelace@uni.edu', '2002-12-15', 'Computer Science');

INSERT INTO courses (course_code, course_name, credits, department) VALUES
(' CS 101 ', 'Introduction to Programming', 3.0, 'Computer Science'), -- Spaces in code
('LIT 205', 'Modernist Poetry', 3.0, 'Literature'),
(' ENG 310', 'Structural Analysis', 4.0, 'Engineering'), -- Leading space
('MATH 150 ', 'Calculus I', 4.5, 'Mathematics'), -- Trailing space
('CS 336', 'Database Systems', 3.0, 'Computer Science');

INSERT INTO enrollments (student_id, course_id, enrollment_date, grade) VALUES
(1, 2, '2023-09-05', 88.5),
(2, 3, '2023-09-05', 92.0),
(1, 4, '2023-09-06', 75.0),
(3, 1, '2023-09-07', NULL), -- Not graded yet
(4, 4, '2023-09-06', 65.5),
(5, 1, '2023-09-07', 95.0),
(5, 5, '2023-09-08', NULL); -- Not graded yet

Tasks for Variant 2:

Student Initials and Full Name: Display the student_id. Create a full_name (First Last). Create an initials string using the first letter of the first_name and the first letter of the last_name in uppercase (e.g., ‘AW’). If a middle_name exists, display it using COALESCE (show ‘N/A’ if NULL), otherwise skip the middle name display for this task.
Cleaned Course Codes: Show the course_name and the course_code after removing any leading or trailing whitespace. Alias the cleaned code as clean_code.
Student Age Calculation: Calculate the current age of each student based on their date_of_birth as of CURRENT_DATE. Display first_name, last_name, date_of_birth, and the calculated age.
Enrollment Grade Status: Display the enrollment_id, student_id, course_id, and a calculated grade_status. The status should be ‘Passed’ if grade >= 60, ‘Failed’ if grade < 60, and ‘Pending’ if grade is NULL.
Course Credit Categories: Display the course_name, credits, and a credit_category. Categorize as ‘Low’ if credits < 3.0, ‘Standard’ if credits are 3.0 or 3.5, and ‘High’ if credits > 3.5.
Standardized Student Emails: Show the student_id, first_name, and email, ensuring the email is entirely in lowercase. Alias this as standard_email.
Course Name Abbreviation: Display the course_id and the first 20 characters of the course_name. Alias this as short_course_name.
Semester of Enrollment: Display the enrollment_id and enrollment_date. Determine the semester of enrollment based on the month: Month 1-5 = ‘Spring’, Month 6-8 = ‘Summer’, Month 9-12 = ‘Fall’. Alias this as enrollment_semester. (Hint: Use EXTRACT and CASE).
Grade Rounded Down: Display the enrollment_id and grade. Also show the grade rounded DOWN to the nearest whole number. Alias this as grade_floor. Handle cases where grade might be NULL (it should likely remain NULL).
Major Display: Display the student_id and first_name. Use COALESCE to show the student’s major; if the major is NULL, display ‘Undeclared’. Alias this as declared_major.
Years Since Birth Year: Extract the birth year for each student. Calculate the number of full years that have passed between their birth year and the current year. Display first_name, date_of_birth, and years_passed. (Hint: EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM date_of_birth)).
Absolute Grade Difference from Average: Calculate the absolute difference between each student’s grade in ‘Calculus I’ (Course ID 4) and the average grade for that course (assume average is 70.0 for this task). Display student_id, grade, and abs_diff_from_avg. Only include enrollments for Course ID 4 with non-null grades.
Find Null Majors: Use NULLIF to return NULL if a student’s major is ‘Literature’. Otherwise, return the major. Display student_id, first_name, major, and the result as null_if_literature.
Check Student ID Parity: Determine if a student_id is odd or even. Display the student_id and ‘Even’ or ‘Odd’ as id_parity. (Hint: Use MOD or %).
Course Code Padding: Display the course_id and clean_code (from Task 2 logic). Create a padded_code by padding the clean_code on the right with spaces up to a total length of 10 characters. (Hint: Use RPAD).

Variant 3: Project & Task Management

Scenario: You are managing a database for tracking projects, tasks assigned to employees, and their progress.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS tasks;
-- DROP TABLE IF EXISTS projects;
-- DROP TABLE IF EXISTS employees;

-- 1. Create the 'employees' table
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    emp_code VARCHAR(10) UNIQUE,     -- e.g., 'EMP001', for SUBSTRING, CAST
    hire_date DATE NOT NULL,         -- For AGE(), EXTRACT()
    hourly_rate NUMERIC(6, 2),       -- For ROUND(), numeric ops
    department VARCHAR(50)           -- For general queries, NULLIF
);

-- 2. Create the 'projects' table
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL UNIQUE, -- For UPPER(), LENGTH()
    client_name VARCHAR(100),
    start_date DATE NOT NULL,
    deadline_date DATE,              -- Allows NULL, for COALESCE, CASE
    budget NUMERIC(12, 2)            -- For ABS(), CEIL()
);

-- 3. Create the 'tasks' table
CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INTEGER REFERENCES projects(project_id),
    assigned_to_emp_id INTEGER REFERENCES employees(emp_id), -- Allows NULL
    task_name VARCHAR(255) NOT NULL,
    estimated_hours NUMERIC(5, 1),     -- e.g., 8.0, 2.5. For ROUND()
    creation_date TIMESTAMP DEFAULT NOW(), -- For date/time functions
    completion_date DATE               -- NULL if not completed. For CASE, AGE()
);

-- Sample Data Insertion --

INSERT INTO employees (first_name, last_name, emp_code, hire_date, hourly_rate, department) VALUES
('Peter', 'Gibbons', 'EMP001', '2019-02-15', 35.00, 'Software'),
('Michael', 'Bolton', 'EMP002', '2018-07-01', 40.50, NULL), -- NULL Dept
('Samir', 'Nagheenanajar', 'EMP003', '2019-02-15', 38.75, 'Software'),
('Joanna', 'Planter', 'EMP004', '2020-11-20', 45.00, 'Management'),
('Bill', 'Lumbergh', 'EMP005', '2015-03-10', 75.00, 'Management');

INSERT INTO projects (project_name, client_name, start_date, deadline_date, budget) VALUES
('TPS Reports Automation', 'Initech', '2023-01-10', '2023-06-30', 50000.00),
('Website Redesign', 'Globex Corp', '2023-03-01', '2023-09-15', 75000.50),
('New Feature X', 'Initech', '2023-05-15', NULL, 30000.00), -- NULL Deadline
('Server Migration', 'Stark Industries', '2023-08-01', '2023-12-20', 120000.00);

INSERT INTO tasks (project_id, assigned_to_emp_id, task_name, estimated_hours, creation_date, completion_date) VALUES
(1, 1, 'Analyze current TPS report process', 8.0, '2023-01-12 10:00:00', '2023-01-20'),
(1, 3, 'Develop script for data extraction', 24.5, '2023-02-01 11:30:00', '2023-03-15'),
(2, 4, 'Create wireframes for new website', 16.0, '2023-03-05 09:00:00', NULL), -- Not completed
(2, NULL, 'Content population', 40.0, '2023-06-01 14:00:00', NULL), -- Unassigned
(3, 1, 'Define requirements for Feature X', 4.0, '2023-05-16 15:00:00', '2023-05-25'),
(4, 5, 'Plan migration strategy', 12.0, '2023-08-02 16:20:00', NULL),
(1, 5, 'Oversee TPS report testing', 6.5, '2023-04-01 08:00:00', '2023-06-10');

Tasks for Variant 3:

Employee Full Name and Code: Display the emp_id. Concatenate first_name and last_name into full_name. Also display the emp_code.
Project Name Uppercase: Show the project_id and the project_name entirely in uppercase, aliased as upper_project_name.
Employee Tenure: Calculate the duration each employee has been working at the company as of CURRENT_DATE. Display emp_code, hire_date, and the calculated tenure using AGE().
Task Status Determination: Display the task_id, task_name, deadline_date (from the associated project - requires a JOIN, assume you can look up the project deadline manually for now or just use task completion_date). Provide a status: ‘Completed’ if completion_date is NOT NULL, ‘Overdue’ if completion_date IS NULL AND the project’s deadline_date is before CURRENT_DATE (if deadline exists), ‘In Progress’ otherwise. (Simplified for assignment: Use only completion_date. ‘Completed’ if NOT NULL, ‘Pending’ if NULL).
Project Budget Categories: Display project_name, budget. Categorize projects based on budget: ‘Small’ if budget < 40000, ‘Medium’ if budget is between 40000 and 80000 (inclusive), ‘Large’ if budget > 80000. Alias as budget_category.
Estimated Hours Rounded: Show the task_id, task_name, and estimated_hours. Also display the estimated_hours rounded to the nearest whole number. Alias this as rounded_hours.
Employee Code Numeric Part: Extract the numeric part of the emp_code (assuming format ‘EMP’ followed by numbers). Display emp_id, emp_code, and the extracted number as emp_number. (Hint: Use SUBSTRING and possibly CAST).
Days Until Project Deadline: For projects that have a deadline_date, calculate the number of days remaining from CURRENT_DATE until the deadline. Display project_name, deadline_date, and days_remaining. Handle cases where the deadline might be in the past (result will be negative).
Budget Rounded Up: Display the project_name and budget. Also show the budget rounded UP to the nearest 1000 dollars. Alias this as budget_ceil_k. (Hint: Use CEIL carefully, maybe divide, ceil, then multiply, or use ROUND with negative precision appropriately if the goal is nearest 1000, use CEIL if strictly UP). Let’s stick to CEIL for the tutorial concept: display CEIL(budget) as budget_ceil_dollar.
Assigned Employee Display: Display task_id and task_name. Use COALESCE to show the assigned_to_emp_id. If it’s NULL, display 0 instead. Alias this as assigned_emp.
Project Start Month and Year: Extract the month and year from the start_date for each project. Display project_name, start_date, start_month, and start_year.
Absolute Budget Deviation: Calculate the absolute difference between each project’s budget and a standard budget of $60,000. Display project_name, budget, and abs_budget_deviation.
Task Creation Hour: Extract the hour (0-23) from the creation_date timestamp for each task. Display task_id, creation_date, and creation_hour.
Find Non-Software Employees: Use NULLIF to return NULL if an employee’s department is ‘Software’. Otherwise, return the department. Display emp_id, first_name, department, and the result as null_if_software.

Format Hourly Rate: Display the emp_id, first_name, and hourly_rate. Create a new string column named formatted_rate that shows the hourly_rate prefixed with a dollar sign (‘$’). (Hint: Use the concatenation operator

and cast the hourly_rate to VARCHAR).

Variant 4: E-commerce Order System

Scenario: You are managing the database for an online retail store. You need to query customer information, product details, orders, and the items within those orders.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS order_items;
-- DROP TABLE IF EXISTS orders;
-- DROP TABLE IF EXISTS products;
-- DROP TABLE IF EXISTS customers;

-- 1. Create the '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,   -- For LOWER(), UNIQUE
    registration_date DATE NOT NULL DEFAULT CURRENT_DATE, -- For AGE(), EXTRACT()
    address_line1 VARCHAR(255),
    city VARCHAR(100),
    postal_code VARCHAR(20)             -- Might have spaces, for TRIM()
);

-- 2. Create the 'products' table (different structure from tutorial)
CREATE TABLE products (
    product_sku VARCHAR(50) PRIMARY KEY, -- e.g., 'ELEC-LAP-1001', 'BOOK-FIC-205A'
    product_name VARCHAR(150) NOT NULL, -- For SUBSTRING(), UPPER()
    description TEXT,                    -- For LENGTH() (on excerpt)
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0), -- For ROUND(), CEIL(), FLOOR(), ABS()
    category VARCHAR(50),                -- For CASE, NULLIF
    stock_added_date DATE                -- For INTERVAL calculation
);

-- 3. Create the 'orders' table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(), -- For EXTRACT(), AGE()
    shipping_address VARCHAR(500),             -- Could be different from customer address, maybe NULL
    order_status VARCHAR(20) DEFAULT 'Pending', -- e.g., 'Pending', 'Shipped', 'Delivered', 'Cancelled'
    -- total_amount NUMERIC(12, 2) -- Usually calculated, but could be stored
    discount_code VARCHAR(15) NULL            -- For COALESCE
);

-- 4. Create the 'order_items' table (linking orders and products)
CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_sku VARCHAR(50) NOT NULL REFERENCES products(product_sku),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_per_unit NUMERIC(10, 2) NOT NULL -- Price at the time of order
);

-- Sample Data Insertion --

INSERT INTO customers (first_name, last_name, email, registration_date, postal_code) VALUES
('Jane', 'Doe', 'jane.d@email.com', '2022-01-15', ' 12345 '),
('John', 'Smith', 'J.Smith@email.com', '2021-11-30', '98765'),
('Peter', 'Jones', 'peterjones@email.com', '2023-05-20', ' SW1A 0AA'),
('Mary', 'Brown', 'mary.b@email.com', '2022-01-15', NULL); -- NULL postal code

INSERT INTO products (product_sku, product_name, description, unit_price, category, stock_added_date) VALUES
('ELEC-LAP-1001', 'UltraBook X1', 'A thin and light laptop.', 1299.99, 'Electronics', '2023-01-10'),
('BOOK-FIC-205A', 'The Forgotten Realm', 'A fantasy novel.', 19.95, 'Books', '2023-02-15'),
('HOME-KIT-050', 'Smart Coffee Maker', 'Brews coffee remotely.', 89.50, 'Home Goods', '2023-03-01'),
('ELEC-CAM-300', 'Action Camera Pro', NULL, 249.00, 'Electronics', '2023-01-10'), -- NULL description
('BOOK-NF-110B', 'History of Computing', 'Covers major milestones.', 35.00, 'Books', '2022-12-01');

INSERT INTO orders (customer_id, order_date, order_status, discount_code) VALUES
(1, '2023-10-01 10:30:00', 'Shipped', 'FALL10'),
(2, '2023-10-15 14:00:00', 'Pending', NULL),
(1, '2023-10-20 09:00:00', 'Pending', NULL),
(3, '2023-08-10 11:00:00', 'Delivered', NULL),
(4, '2023-10-25 16:00:00', 'Pending', 'NEWCUST5');

INSERT INTO order_items (order_id, product_sku, quantity, price_per_unit) VALUES
(1, 'ELEC-LAP-1001', 1, 1299.99),
(1, 'HOME-KIT-050', 1, 85.00), -- Price slightly different at time of order
(2, 'BOOK-FIC-205A', 2, 19.95),
(3, 'HOME-KIT-050', 1, 89.50),
(4, 'BOOK-NF-110B', 1, 35.00),
(5, 'ELEC-CAM-300', 1, 249.00);

Tasks for Variant 4:

Customer Full Name: Select customer_id and display the customer’s full name by concatenating first_name and last_name with a space in between. Alias the result as full_name.
Standardized Email Addresses: Display the customer_id and email address for all customers, ensuring the email is entirely in lowercase. Alias this as standard_email.
Customer Membership Duration: Calculate how long each customer has been registered (since registration_date) as of CURRENT_DATE. Display customer_id, registration_date, and the duration using AGE(), aliased as membership_duration.
Cleaned Postal Codes: Display the customer_id and postal_code. If the postal_code is not NULL, show it after removing any leading or trailing spaces; otherwise, show ‘N/A’. Alias this as cleaned_postal_code. (Hint: Use TRIM and COALESCE).
Product SKU Analysis: For each product, extract the category part (before the first ‘-‘) and the core code part (between the first and second ‘-‘). Display product_sku, the extracted sku_category, and sku_core. (Hint: Use SUBSTRING and potentially POSITION or fixed lengths if the pattern is consistent). Simplified for tutorial scope: Assume first part is 4 chars, second is 3 chars. Extract SUBSTRING(product_sku FROM 1 FOR 4) as sku_category_prefix and SUBSTRING(product_sku FROM 6 FOR 3) as sku_type_prefix.
Product Pricing Tiers: Display product_name, unit_price, and a calculated price_tier. The tier should be ‘Budget’ if price < $50, ‘Mid-Range’ if price is $50 to $250 (inclusive), and ‘Premium’ if price > $250. Use a CASE statement.
Order Item Total Value: For each item in order_items, calculate the total value (quantity * price_per_unit). Display item_id, order_id, product_sku, and the calculated item_total.
Days Since Order: For each order, calculate the number of full days that have passed since the order_date (ignore time part for simplicity). Display order_id, order_date, and days_passed. (Hint: CURRENT_DATE - CAST(order_date AS DATE)).
Unit Price Rounded: Display product_name and unit_price, along with the unit_price rounded to the nearest whole dollar. Alias this as rounded_price.
Minimum Shipping Charge: Display product_name and unit_price. Calculate a hypothetical minimum shipping charge by rounding the unit_price UP to the nearest dollar (CEIL). Alias this as min_ship_charge_base.
Order Status Display: Display order_id and order_status. Create a column is_processing that shows TRUE if the order_status is ‘Pending’, and FALSE otherwise. (Hint: Use CASE or a boolean expression).
Order Placement Hour: Extract the hour (0-23) from the order_date for each order. Display order_id, order_date, and order_hour.
Absolute Price Difference from $100: For each product, calculate the absolute difference between its unit_price and $100. Display product_name, unit_price, and abs_diff_from_100.
Formatted Product Price String: Display the product_name and unit_price. Create a string representation of the price prefixed with ‘$’. Alias this as formatted_price. (e.g., ‘$1299.99’). Use '$' || unit_price::VARCHAR.
Padded Order ID: Display the order_id formatted as a 10-digit string with leading zeros (e.g., 1 becomes ‘0000000001’). Alias this as formatted_order_id. Use LPAD.

Variant 5: Event Management System

Scenario: You are managing a database for an event organizing company, tracking events, venues, attendee registrations, and payments.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS registrations;
-- DROP TABLE IF EXISTS attendees;
-- DROP TABLE IF EXISTS events;
-- DROP TABLE IF EXISTS venues;

-- 1. Create the 'venues' table
CREATE TABLE venues (
    venue_id SERIAL PRIMARY KEY,
    venue_name VARCHAR(100) NOT NULL UNIQUE, -- For UPPER(), LENGTH()
    address VARCHAR(255),
    city VARCHAR(100),
    capacity INTEGER CHECK (capacity > 0),     -- For MOD(), numeric ops
    booking_cost NUMERIC(8, 2),            -- For ROUND(), CEIL(), ABS()
    contact_phone VARCHAR(20)              -- e.g., ' 123-456-7890 ', for TRIM(), REPLACE()
);

-- 2. Create the 'events' table
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(150) NOT NULL,       -- For SUBSTRING()
    venue_id INTEGER REFERENCES venues(venue_id),
    event_type VARCHAR(50),                 -- e.g., 'Conference', 'Workshop', 'Webinar', 'Gala'
    start_datetime TIMESTAMP NOT NULL,      -- For EXTRACT(), AGE(), INTERVAL
    end_datetime TIMESTAMP,                 -- Can be NULL (e.g., single day event), check start/end duration
    base_ticket_price NUMERIC(7, 2) DEFAULT 0.00 -- For CASE, NULLIF
);

-- 3. Create the '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,   -- For LOWER()
    signup_date DATE DEFAULT CURRENT_DATE,  -- For AGE()
    company_name VARCHAR(100)             -- Allows NULL, for COALESCE
);

-- 4. Create the 'registrations' table
CREATE TABLE registrations (
    registration_id SERIAL PRIMARY KEY,
    event_id INTEGER NOT NULL REFERENCES events(event_id),
    attendee_id INTEGER NOT NULL REFERENCES attendees(attendee_id),
    registration_time TIMESTAMP DEFAULT NOW(), -- For EXTRACT(), AGE()
    ticket_type VARCHAR(20) DEFAULT 'Standard', -- e.g., 'Standard', 'VIP', 'EarlyBird'
    final_price NUMERIC(7, 2),                 -- Price paid, could differ from base price
    payment_status VARCHAR(15)                 -- e.g., 'Paid', 'Pending', 'Refunded', NULL
);

-- Sample Data Insertion --

INSERT INTO venues (venue_name, capacity, booking_cost, contact_phone) VALUES
('Grand Hall', 500, 5000.00, ' 111-222-3333 '),
('Convention Center East Wing', 1500, 12000.50, '444-555-6666'),
('Cozy Corner Room', 50, 800.75, NULL), -- NULL phone
('Online Platform', 10000, 200.00, ' N/A ');

INSERT INTO events (event_name, venue_id, event_type, start_datetime, end_datetime, base_ticket_price) VALUES
('Annual Tech Conference 2024', 2, 'Conference', '2024-09-10 09:00:00', '2024-09-12 17:00:00', 499.00),
('Data Science Workshop', 1, 'Workshop', '2024-07-15 10:00:00', '2024-07-15 16:30:00', 150.00),
('Introduction to SQL Webinar', 4, 'Webinar', '2024-05-20 14:00:00', NULL, 0.00), -- Free event, NULL end time
('Charity Gala Dinner', 1, 'Gala', '2024-11-01 19:00:00', '2024-11-01 23:00:00', 250.50);

INSERT INTO attendees (first_name, last_name, email, company_name) VALUES
('Alice', 'Wonder', 'alice.w@example.com', 'Curious Inc.'),
('Bob', 'Marley', 'bob.m@example.com', NULL), -- NULL company
('Charlie', 'Day', 'charlie.d@example.com', 'Paddy''s Pub'),
('Diana', 'Prince', 'diana.p@example.com', 'Themyscira Exports');

INSERT INTO registrations (event_id, attendee_id, registration_time, ticket_type, final_price, payment_status) VALUES
(1, 1, '2024-03-15 10:00:00', 'EarlyBird', 399.00, 'Paid'),
(1, 2, '2024-04-01 11:30:00', 'Standard', 499.00, 'Paid'),
(2, 3, '2024-06-10 16:00:00', 'Standard', 150.00, 'Pending'),
(3, 1, '2024-05-01 09:00:00', 'Standard', 0.00, 'Paid'), -- Free event
(4, 4, '2024-09-01 14:20:00', 'VIP', 350.00, 'Paid'),
(2, 4, '2024-07-01 13:00:00', 'Standard', 150.00, NULL); -- NULL payment status

Tasks for Variant 5:

Attendee Full Name: Select attendee_id and display the attendee’s full name by concatenating first_name and last_name with a space. Alias the result as full_name.
Cleaned Venue Phone: Display the venue_name and contact_phone. Show the contact_phone after removing all spaces and hyphens. If the phone is NULL or ‘ N/A ‘, display ‘Not Available’. Alias this as cleaned_phone. (Hint: Use REPLACE, TRIM, COALESCE).
Event Duration: Calculate the duration of each event (end_datetime - start_datetime). If end_datetime is NULL, display NULL. Display event_name, start_datetime, end_datetime, and the calculated duration (as an INTERVAL).
Time Since Registration: For each registration, calculate how long ago it occurred (registration_time) relative to NOW(). Display registration_id, registration_time, and the duration using AGE(), aliased as registration_age.
Event Pricing Category: Display event_name and base_ticket_price. Create a price_category using CASE: ‘Free’ if price is 0, ‘Affordable’ if price > 0 and <= 100, ‘Standard’ if price > 100 and <= 300, ‘Premium’ if price > 300.
Standardized Attendee Emails: Display the attendee_id, first_name, and email, ensuring the email is entirely in lowercase. Alias this as standard_email.
Venue Capacity Check: Display venue_name and capacity. Add a column is_large_venue which is TRUE if the capacity is greater than 1000, and FALSE otherwise.
Event Start Day: Extract the day of the week (e.g., ‘Monday’, ‘Tuesday’) from the start_datetime for each event. Display event_name, start_datetime, and start_day. (Hint: Use TO_CHAR with EXTRACT(DOW FROM ...) might be needed for the name, or just show the DOW number from EXTRACT). Let’s stick to the number: EXTRACT(DOW FROM start_datetime) AS start_dow (0=Sun, 6=Sat in PG).
Venue Cost Rounded Up: Display venue_name and booking_cost. Calculate the cost rounded UP to the nearest $100. Alias this as cost_ceil_100. (Hint: CEIL(booking_cost / 100.0) * 100).
Attendee Company Display: Display attendee_id, first_name. Use COALESCE to show the company_name; if it’s NULL, display ‘Individual Attendee’. Alias this as affiliation.
Absolute Price Difference from Base: For registrations, calculate the absolute difference between the final_price and the event’s base_ticket_price (requires joining registrations and events). Display registration_id, base_ticket_price, final_price, and price_difference.
Identify Pending Payments: Use NULLIF to return the registration_id only if the payment_status is NOT ‘Paid’. Otherwise, return NULL. Display registration_id, payment_status, and the result as unpaid_registration_id.
Short Event Name: Display the event_id and the first 30 characters of the event_name. Alias this as short_event_name. Use SUBSTRING.
Padded Venue ID: Display the venue_id formatted as a 5-digit string with leading zeros. Alias this as formatted_venue_id. Use LPAD.
Days Until Event Start: For events starting in the future, calculate the number of days from CURRENT_DATE until start_datetime. Display event_name, start_datetime, and days_until_start. Handle events in the past (result might be negative or filter them out). Use start_datetime::DATE - CURRENT_DATE.

Variant 6: Blog Platform

Scenario: You are managing the database for a multi-user blog platform, tracking users, posts, comments, and categories.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS comments;
-- DROP TABLE IF EXISTS posts;
-- DROP TABLE IF EXISTS categories;
-- DROP TABLE IF EXISTS users;

-- 1. Create the 'users' table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(30) UNIQUE NOT NULL,   -- For LENGTH(), LOWER()
    email VARCHAR(100) UNIQUE NOT NULL,   -- For LOWER()
    full_name VARCHAR(100),              -- For CONCAT (if split needed) or just display
    join_date DATE NOT NULL DEFAULT CURRENT_DATE, -- For AGE(), EXTRACT()
    profile_bio TEXT                     -- Allows NULL, for COALESCE, LENGTH()
    -- password_hash VARCHAR(255) NOT NULL -- Not used in tasks
);

-- 2. Create the 'categories' table
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(50) UNIQUE NOT NULL, -- For UPPER(), REPLACE()
    slug VARCHAR(60) UNIQUE                -- e.g., 'data-science-basics', for LOWER(), REPLACE()
);

-- 3. Create the 'posts' table
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES users(user_id),
    category_id INTEGER REFERENCES categories(category_id),
    title VARCHAR(200) NOT NULL,           -- For SUBSTRING()
    content TEXT,                         -- For LENGTH()
    publish_datetime TIMESTAMP,           -- Allows NULL for drafts. For EXTRACT(), AGE(), INTERVAL
    last_updated TIMESTAMP DEFAULT NOW(),
    status VARCHAR(15) DEFAULT 'Draft',   -- 'Draft', 'Published', 'Archived' - For CASE, NULLIF
    word_count INTEGER                    -- For MOD(), CEIL(), numeric ops
);

-- 4. Create the 'comments' table
CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(post_id),
    commenter_id INTEGER REFERENCES users(user_id), -- NULL if commenter is anonymous/not logged in
    commenter_name VARCHAR(50),              -- Used if commenter_id is NULL
    comment_text TEXT NOT NULL,
    comment_datetime TIMESTAMP DEFAULT NOW(), -- For EXTRACT(), AGE()
    parent_comment_id INTEGER REFERENCES comments(comment_id) -- For threaded comments (NULL if top-level)
);

-- Sample Data Insertion --

INSERT INTO users (username, email, full_name, join_date, profile_bio) VALUES
('john_doe', 'john.doe@blog.com', 'John Doe', '2022-01-10', 'Tech enthusiast and blogger.'),
('jane_smith', 'Jane.Smith@blog.com', 'Jane Smith', '2022-03-15', NULL), -- NULL bio
('bob_coder', 'bob@coder.net', 'Bob Coder', '2023-05-01', 'Software developer sharing tips.'),
('reader_x', 'readerX@mail.com', NULL, '2023-10-01', NULL); -- No full name, no bio

INSERT INTO categories (category_name, slug) VALUES
('Technology', 'technology'),
('Data Science', 'data-science'),
('Tutorials', 'tutorials'),
(' Opinion Pieces ', 'opinion-pieces'); -- Slug needs generating/cleaning

INSERT INTO posts (author_id, category_id, title, content, publish_datetime, status, word_count) VALUES
(1, 1, 'The Future of AI', 'AI is evolving rapidly...', '2023-06-01 10:00:00', 'Published', 1250),
(3, 2, 'Introduction to PostgreSQL Functions', 'This post covers several useful SQL functions...', '2023-07-15 14:30:00', 'Published', 1800),
(1, 3, 'Setting Up a Python Dev Environment', 'Step-by-step guide...', NULL, 'Draft', 950), -- Draft, no publish date
(2, 4, 'Why Static Sites Are Great', 'My thoughts on static site generators...', '2023-09-01 11:00:00', 'Published', 780),
(3, 1, 'Old Tech Thoughts', 'Some musings on older tech...', '2022-11-20 09:00:00', 'Archived', 600); -- Archived

INSERT INTO comments (post_id, commenter_id, commenter_name, comment_text, comment_datetime) VALUES
(1, 2, NULL, 'Great overview!', '2023-06-01 11:00:00'),
(2, 1, NULL, 'Very helpful, thanks Bob!', '2023-07-16 09:15:00'),
(1, NULL, 'Guest User', 'I disagree with point 2.', '2023-06-02 15:00:00'), -- Anonymous comment
(4, 3, NULL, 'Nice points, Jane!', '2023-09-01 12:30:00'),
(2, 4, NULL, 'Where can I learn more?', '2023-10-05 10:00:00');

Tasks for Variant 6:

Username Length Check: Display the user_id and username. Also show the length of each username, aliased as username_length.
Category Slug Generation: Display category_name. Generate a potential slug by converting category_name to lowercase, removing leading/trailing spaces, and replacing remaining spaces with hyphens. Alias this as generated_slug. (Use LOWER, TRIM, REPLACE).
User Account Age: Calculate how long each user has been registered (since join_date) as of CURRENT_DATE. Display username, join_date, and the duration using AGE(), aliased as account_age.
Post Excerpt: For each post, display the post_id, title, and the first 150 characters of the content. If the content is longer than 150 characters, append ‘…’. Alias this as post_excerpt. (Hint: Use SUBSTRING, LENGTH, CASE, CONCAT/||).
Standardized User Emails: Display the user_id, username, and email, ensuring the email is entirely in lowercase. Alias this as standard_email.
Post Publication Age: For published posts (publish_datetime is NOT NULL), calculate how long ago they were published relative to NOW(). Display post_id, title, publish_datetime, and the duration using AGE(), aliased as time_since_publish.
Comment Time Analysis: For each comment, extract the year, month, and hour from comment_datetime. Display comment_id, comment_datetime, comment_year, comment_month, and comment_hour.
Post Visibility Status: Display post_id, title, and status. Create a column is_visible using CASE: TRUE if status is ‘Published’, FALSE otherwise.
Post Word Count Parity: Display post_id, title, and word_count. Determine if the word_count is ‘Even’ or ‘Odd’. Alias this as word_count_parity. (Hint: Use MOD or %).
User Bio Display: Display username and profile_bio. Use COALESCE to show ‘No bio provided’ if profile_bio is NULL or an empty string. (Hint: COALESCE(NULLIF(TRIM(profile_bio), ''), 'No bio provided')).
Absolute Word Count Deviation: Calculate the absolute difference between each post’s word_count and a target length of 1000 words. Display post_id, word_count, and abs_diff_from_1000.
Identify Non-Published Posts: Use NULLIF to return the post_id if the post status is ‘Draft’ or ‘Archived’. Otherwise, return NULL. Display post_id, status, and the result as non_published_post_id.
Estimated Reading Time: Calculate an estimated reading time in minutes by dividing word_count by 200 (average words per minute) and rounding the result UP to the nearest whole number. Display post_id, word_count, and estimated_read_minutes. (Use CEIL).
Padded User ID: Display the user_id formatted as a 7-digit string with leading zeros. Alias this as formatted_user_id. Use LPAD.
Commenter Identification: Display comment_id and comment_text. Show the commenter’s identity: if commenter_id is not NULL, display ‘User: ‘ followed by the commenter_id (cast to VARCHAR). If commenter_id is NULL, display ‘Guest: ‘ followed by commenter_name. Alias this as commenter_identity. (Use CASE and COALESCE or nested CASE).

Variant 7: Airline Flight Management

Scenario: You are working with a database for a small airline, managing information about flights, aircraft, airports, and passenger bookings.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS bookings;
-- DROP TABLE IF EXISTS flights;
-- DROP TABLE IF EXISTS aircraft;
-- DROP TABLE IF EXISTS airports;
-- DROP TABLE IF EXISTS passengers;

-- 1. Create the 'airports' table
CREATE TABLE airports (
    airport_code CHAR(3) PRIMARY KEY,        -- e.g., 'JFK', 'LAX'. Fixed length.
    airport_name VARCHAR(100) NOT NULL,    -- For LENGTH(), UPPER()
    city VARCHAR(50) NOT NULL,
    country_code CHAR(2) NOT NULL          -- e.g., 'US', 'GB'. For LOWER()
);

-- 2. Create the 'aircraft' table
CREATE TABLE aircraft (
    aircraft_id SERIAL PRIMARY KEY,
    model_name VARCHAR(50) NOT NULL,       -- e.g., 'Boeing 737-800'
    manufacturer VARCHAR(50),
    seat_capacity INTEGER NOT NULL,        -- For MOD()
    range_miles NUMERIC(6, 0)              -- For numeric functions
);

-- 3. Create the 'flights' table
CREATE TABLE flights (
    flight_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) UNIQUE NOT NULL, -- e.g., ' AA101 ', for TRIM(), SUBSTRING()
    departure_airport CHAR(3) REFERENCES airports(airport_code),
    arrival_airport CHAR(3) REFERENCES airports(airport_code),
    aircraft_id INTEGER REFERENCES aircraft(aircraft_id),
    scheduled_departure TIMESTAMP WITH TIME ZONE NOT NULL, -- For EXTRACT(), INTERVAL, date/time funcs
    scheduled_arrival TIMESTAMP WITH TIME ZONE NOT NULL,
    base_price NUMERIC(7, 2)                -- For ROUND(), CEIL(), FLOOR(), ABS()
);

-- 4. Create the 'passengers' table
CREATE TABLE passengers (
    passenger_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),             -- For COALESCE()
    last_name VARCHAR(50) NOT NULL,      -- For CONCAT()
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE                   -- For AGE()
);

-- 5. Create the 'bookings' table
CREATE TABLE bookings (
    booking_id SERIAL PRIMARY KEY,
    flight_id INTEGER REFERENCES flights(flight_id),
    passenger_id INTEGER REFERENCES passengers(passenger_id),
    booking_date DATE DEFAULT CURRENT_DATE,
    seat_number VARCHAR(4),              -- e.g., '12A'
    final_price NUMERIC(7, 2),           -- May differ from base_price due to fees/discounts
    status VARCHAR(15) DEFAULT 'Confirmed' -- e.g., Confirmed, Cancelled, Checked-In. For CASE(), NULLIF()
);


-- Sample Data Insertion --

INSERT INTO airports (airport_code, airport_name, city, country_code) VALUES
('JFK', 'John F. Kennedy International Airport', 'New York', 'US'),
('LAX', 'Los Angeles International Airport', 'Los Angeles', 'US'),
('LHR', 'London Heathrow Airport', 'London', 'GB'),
('ORD', 'O''Hare International Airport', 'Chicago', 'US'),
('ATL', 'Hartsfield-Jackson Atlanta International Airport', 'Atlanta', 'US');

INSERT INTO aircraft (model_name, manufacturer, seat_capacity, range_miles) VALUES
('Boeing 737-800', 'Boeing', 189, 3500),
('Airbus A320neo', 'Airbus', 195, 4000),
('Boeing 777-300ER', 'Boeing', 396, 7370),
('Embraer E175', 'Embraer', 88, 2200);

INSERT INTO flights (flight_number, departure_airport, arrival_airport, aircraft_id, scheduled_departure, scheduled_arrival, base_price) VALUES
(' AA101 ', 'JFK', 'LAX', 1, '2024-09-15 08:00:00-04', '2024-09-15 11:30:00-07', 350.00), -- Spaces
('BA202', 'LHR', 'JFK', 3, '2024-09-16 10:30:00+01', '2024-09-16 13:30:00-04', 680.50),
(' UA303', 'ORD', 'ATL', 2, '2024-09-15 14:00:00-05', '2024-09-15 16:45:00-04', 180.75), -- Leading space
('AA102 ', 'LAX', 'JFK', 1, '2024-09-17 13:00:00-07', '2024-09-17 21:30:00-04', 365.25), -- Trailing space
('DL404', 'ATL', 'ORD', 4, '2024-09-18 09:15:00-04', '2024-09-18 10:00:00-05', 175.00);

INSERT INTO passengers (first_name, middle_name, last_name, email, date_of_birth) VALUES
('James', 'Tiberius', 'Kirk', 'j.kirk@starfleet.com', '1985-03-22'),
('Leia', NULL, 'Organa', 'leia.o@rebellion.org', '1990-11-01'),
('Han', NULL, 'Solo', 'han.solo@falcon.net', '1988-07-10'),
('Sarah', 'Jane', 'Connor', 'sarah.connor@resistance.fut', '1984-05-13'),
('Ellen', 'Louise', 'Ripley', 'e.ripley@wey-yu.corp', '1980-01-07');

INSERT INTO bookings (flight_id, passenger_id, booking_date, seat_number, final_price, status) VALUES
(1, 1, '2024-07-10', '5A', 375.00, 'Confirmed'),
(2, 2, '2024-08-01', '22K', 710.50, 'Confirmed'),
(3, 3, '2024-08-15', '10C', 180.75, 'Cancelled'),
(1, 4, '2024-07-11', '5B', 380.00, 'Checked-In'),
(4, 5, '2024-09-01', NULL, 365.25, 'Confirmed'), -- NULL seat
(2, 3, '2024-08-20', '40A', 680.50, 'Confirmed');

Tasks for Variant 7:

Passenger Full Name: Display the passenger_id, and create a full_name by concatenating first_name, middle_name (if it exists, otherwise skip), and last_name. Use COALESCE to handle the potential NULL middle_name elegantly (e.g., first || COALESCE(' ' || middle_name, '') || ' ' || last).
Clean Flight Numbers: Show the flight_id and the flight_number after removing any leading or trailing whitespace. Alias the cleaned column as clean_flight_no.
Passenger Age: Calculate the current age of each passenger based on their date_of_birth. Display first_name, last_name, date_of_birth, and the calculated age.
Flight Duration: Calculate the scheduled duration of each flight (difference between scheduled_arrival and scheduled_departure). Display flight_id, flight_number, and the calculated duration as an INTERVAL.
Booking Status Summary: Display the booking_id, final_price, and a booking_summary. Use a CASE statement: If status is ‘Cancelled’, show ‘CANCELLED’. If status is ‘Checked-In’, show ‘CHECKED-IN’. Otherwise, show ‘CONFIRMED’.
Airport Name Length: Display the airport_code and the length of the airport_name for all airports. Alias the length column as name_length.
Airline Code from Flight Number: Extract the first 2 characters (typically the airline code) from the clean_flight_no (from Task 2 logic). Display the clean_flight_no and the extracted airline_code. (Use SUBSTRING on the cleaned flight number).
Time Until Departure: For flights scheduled in the future, calculate the time remaining until scheduled_departure from NOW(). Display flight_number, scheduled_departure, and time_to_departure. (Use scheduled_departure - NOW()).
Price Rounded to Nearest $10: Display the flight_id and base_price. Also, show the base_price rounded to the nearest 10 dollars. Alias this as price_rounded_10. (Hint: ROUND(price, -1)).
Seat Assignment Check: Display the booking_id and passenger_id. Use COALESCE to show the seat_number. If seat_number is NULL, display ‘Unassigned’. Alias this as seat_status.
Departure Month and Day of Week: Extract the month (numeric) and the day of the week (numeric, e.g., 0 for Sunday, 6 for Saturday in PostgreSQL) from the scheduled_departure time. Display flight_number, scheduled_departure, departure_month, and departure_dow.
Absolute Price Difference: Calculate the absolute difference between the final_price of a booking and the base_price of the corresponding flight (requires a JOIN - assume you can join bookings and flights on flight_id). Display booking_id, final_price, base_price, and price_difference.
Nullify Specific Aircraft: Use NULLIF to return NULL if an aircraft manufacturer is ‘Embraer’. Otherwise, return the manufacturer. Display aircraft_id, model_name, manufacturer, and the result as null_if_embraer.
Aircraft Capacity Parity: Determine if the seat_capacity of an aircraft is odd or even. Display aircraft_id, model_name, seat_capacity, and ‘Even’ or ‘Odd’ as capacity_parity. (Use MOD).
Format Booking ID: Display the booking_id formatted as a 7-digit string with leading zeros (e.g., 1 becomes ‘0000001’). Alias this as formatted_booking_id. (Hint: Use LPAD).

Variant 8: Hospital Patient Records

Scenario: You are managing a simplified database for a clinic, tracking patients, doctors, and appointments.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS appointments;
-- DROP TABLE IF EXISTS doctors;
-- DROP TABLE IF EXISTS patients;

-- 1. Create the 'patients' table
CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    middle_initial CHAR(1),            -- Single initial, allows NULL. For COALESCE
    last_name VARCHAR(50) NOT NULL,     -- For CONCAT
    date_of_birth DATE NOT NULL,        -- For AGE, EXTRACT
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')), -- M=Male, F=Female, O=Other
    phone_number VARCHAR(20),           -- May have formatting e.g., '(123) 456-7890'. For REPLACE
    email VARCHAR(100) UNIQUE          -- For LOWER
);

-- 2. Create the 'doctors' table
CREATE TABLE doctors (
    doctor_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    specialty VARCHAR(100),             -- For general queries, NULLIF
    office_number VARCHAR(10) UNIQUE,   -- e.g., ' RM 301 ', for TRIM
    start_date DATE                   -- For AGE
);

-- 3. Create the 'appointments' table
CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INTEGER REFERENCES patients(patient_id),
    doctor_id INTEGER REFERENCES doctors(doctor_id),
    appointment_datetime TIMESTAMP WITH TIME ZONE NOT NULL, -- For EXTRACT, INTERVAL, CAST
    reason VARCHAR(255),                 -- For LENGTH
    duration_minutes INTEGER DEFAULT 30, -- For numeric ops
    billing_amount NUMERIC(8, 2),        -- For ROUND, CEIL, FLOOR, ABS
    status VARCHAR(20) DEFAULT 'Scheduled' -- e.g., Scheduled, Completed, Cancelled, No-Show. For CASE
);

-- Sample Data Insertion --

INSERT INTO patients (first_name, middle_initial, last_name, date_of_birth, gender, phone_number, email) VALUES
('Gregory', 'H', 'House', '1959-06-11', 'M', '(609) 555-0100', 'g.house@ppth.org'),
('Lisa', NULL, 'Cuddy', '1968-10-15', 'F', '609-555-0101', 'L.Cuddy@ppth.org'),
('James', 'E', 'Wilson', '1965-05-22', 'M', ' 609.555.0102 ', 'jewilson@ppth.org'), -- Spaces in phone
('Allison', NULL, 'Cameron', '1979-09-01', 'F', NULL, 'ACameron@ppth.org'), -- NULL phone
('Robert', 'C', 'Chase', '1978-02-17', 'M', '6095550104', 'r.chase@ppth.org');

INSERT INTO doctors (first_name, last_name, specialty, office_number, start_date) VALUES
('Gregory', 'House', 'Nephrology, Infectious Disease', ' RM 201', '1995-04-01'), -- Leading space
('Lisa', 'Cuddy', 'Endocrinology, Administration', ' ADMIN 01', '1998-07-15'), -- Mixed space/case
('James', 'Wilson', 'Oncology', ' ONC 305 ', '1992-11-01'), -- Leading/trailing space
('Eric', 'Foreman', 'Neurology', 'NEURO 110', '2004-08-20');

INSERT INTO appointments (patient_id, doctor_id, appointment_datetime, reason, duration_minutes, billing_amount, status) VALUES
(1, 1, '2024-08-15 09:00:00-04', 'Consultation', 45, 250.00, 'Completed'),
(2, 2, '2024-08-15 10:00:00-04', 'Follow-up', 30, 150.50, 'Completed'),
(3, 3, '2024-09-20 14:30:00-04', 'Check-up', 30, NULL, 'Scheduled'), -- Future, no bill yet
(4, 1, '2024-08-01 11:00:00-04', 'Initial Visit', 60, 300.75, 'No-Show'),
(5, 4, '2024-09-25 16:00:00-04', 'Neurology consult', 45, NULL, 'Scheduled'), -- Future
(1, 3, '2023-12-10 09:30:00-05', 'Scan results', 20, 120.00, 'Completed'); -- Past

Tasks for Variant 8:

Patient Full Name: Display patient_id. Create a full_name string formatted as ‘LastName, FirstName M.’ where M is the middle_initial. If middle_initial is NULL, omit it. (e.g., ‘House, Gregory H.’, ‘Cuddy, Lisa’). Use CONCAT and COALESCE.
Standardized Phone Number: Display the patient_id, first_name, and phone_number. Create a clean_phone column by removing spaces, parentheses, and hyphens from phone_number. (Hint: Use nested REPLACE). Handle NULL phones gracefully (they should remain NULL).
Patient Age Calculation: Calculate the current age of each patient in years based on their date_of_birth. Display first_name, last_name, date_of_birth, and the integer part of the age in years (use EXTRACT(YEAR FROM AGE(...)) or similar). Alias as age_years.
Appointment Time Formatting: Display appointment_id, appointment_datetime. Convert the appointment_datetime to a string formatted as ‘YYYY-MM-DD HH24:MI’. Alias this as formatted_datetime. (Hint: Use TO_CHAR if available, or combine CAST with string functions). For simplicity with CAST: Cast appointment_datetime::DATE and appointment_datetime::TIME separately and concatenate.
Appointment Status Summary: Show appointment_id, patient_id, doctor_id, and a status_summary. Use CASE: If status is ‘Completed’, show ‘Done’. If ‘Cancelled’ or ‘No-Show’, show ‘Missed’. If ‘Scheduled’, show ‘Upcoming’.
Doctor Specialty Length: Display the doctor_id, last_name, and the length of the specialty string. Alias the length as specialty_length. Handle NULL specialties (length should be 0 or NULL depending on DB).
Patient Email Domain: Extract the domain name (the part after ‘@’) from the patient’s email address. Display patient_id, email, and the extracted email_domain. (Hint: Use SUBSTRING and POSITION('@' IN email)).
Days Since Last Appointment: For ‘Completed’ appointments, calculate the number of days that have passed between the appointment_datetime and CURRENT_DATE. Display appointment_id, appointment_datetime, and days_since_appointment.
Billing Amount Rounded Up: Display appointment_id, billing_amount. Also show the billing_amount rounded UP to the nearest whole dollar. Alias this as billed_ceil. Handle NULL amounts.
Patient Middle Initial Display: Display patient_id, first_name, last_name. Use COALESCE to show the middle_initial; if it’s NULL, display ‘-‘. Alias this as display_initial.
Appointment Year and Quarter: Extract the year and the calendar quarter (1-4) from the appointment_datetime. Display appointment_id, appointment_datetime, appointment_year, and appointment_quarter.
Absolute Billing Difference: Calculate the absolute difference between each appointment’s billing_amount and a standard fee of $175.00. Display appointment_id, billing_amount, and abs_diff_from_standard. Only include appointments where billing_amount is not NULL.
Nullify Specific Specialty: Use NULLIF to return NULL if a doctor’s specialty is ‘Administration’. Otherwise, return the specialty. Display doctor_id, last_name, specialty, and the result as null_if_admin.
Doctor ID Parity: Determine if a doctor_id is odd or even. Display doctor_id, last_name, and ‘Even’ or ‘Odd’ as id_parity. (Use MOD).
Clean Office Number: Display doctor_id, last_name, and the office_number. Create a clean_office_no by removing all leading/trailing spaces and converting the result to uppercase. (Use TRIM and UPPER).

Variant 9: Car Rental Service

Scenario: You are managing the database for a car rental company, tracking vehicles, customers, and rental agreements.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS rentals;
-- DROP TABLE IF EXISTS customers;
-- DROP TABLE IF EXISTS vehicles;
-- DROP TABLE IF EXISTS locations;

-- 1. Create the 'locations' table
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    location_code VARCHAR(5) UNIQUE NOT NULL, -- e.g., ' LGA01', for TRIM
    address VARCHAR(255) NOT NULL,
    city VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20)             -- For REPLACE
);

-- 2. Create the 'vehicles' table
CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    vin VARCHAR(17) UNIQUE NOT NULL,        -- Vehicle Identification Number, for UPPER, SUBSTRING
    make VARCHAR(50) NOT NULL,              -- e.g., 'Toyota', 'Ford'
    model VARCHAR(50) NOT NULL,             -- e.g., 'Camry', 'Mustang'. For NULLIF
    year INTEGER NOT NULL,                  -- For EXTRACT (from a date), AGE (vehicle age)
    license_plate VARCHAR(10) UNIQUE,       -- e.g., ' ABC-123 '. For TRIM, REPLACE
    daily_rate NUMERIC(6, 2) NOT NULL,      -- For ROUND, CEIL, FLOOR, ABS
    current_mileage NUMERIC(7, 0) DEFAULT 0 -- For numeric ops
);

-- 3. Create the 'customers' table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,         -- For CONCAT, LENGTH
    email VARCHAR(100) UNIQUE NOT NULL,     -- For LOWER
    date_of_birth DATE,                     -- For AGE
    driver_license_no VARCHAR(20) UNIQUE,
    join_date DATE DEFAULT CURRENT_DATE     -- For AGE, EXTRACT
);

-- 4. Create the 'rentals' table
CREATE TABLE rentals (
    rental_id SERIAL PRIMARY KEY,
    vehicle_id INTEGER REFERENCES vehicles(vehicle_id),
    customer_id INTEGER REFERENCES customers(customer_id),
    pickup_location_id INTEGER REFERENCES locations(location_id),
    dropoff_location_id INTEGER REFERENCES locations(location_id), -- Allows NULL if same as pickup
    pickup_datetime TIMESTAMP WITH TIME ZONE NOT NULL, -- For EXTRACT, INTERVAL, CAST
    return_datetime TIMESTAMP WITH TIME ZONE,           -- NULL if currently rented. For COALESCE, CASE
    total_amount NUMERIC(8, 2)                  -- Calculated upon return. Allows NULL.
);

-- Sample Data Insertion --

INSERT INTO locations (location_code, address, city, phone_number) VALUES
('JFK01', 'Building 78, JFK Airport', 'New York', '718-555-0110'),
('LAX01', '9020 Aviation Blvd', 'Los Angeles', '310-555-0120'),
('ORD01 ', '10000 Bessie Coleman Dr', 'Chicago', '773-555-0130'), -- Trailing space
(' MIA01', '4200 NW 21st St', 'Miami', '(305) 555-0140'); -- Leading space

INSERT INTO vehicles (vin, make, model, year, license_plate, daily_rate, current_mileage) VALUES
('1GKS19EC5L0123456', 'Chevrolet', 'Malibu', 2020, ' NYC-101', 55.00, 35000), -- Leading space plate
('JTDKBRFU0P0654321', 'Toyota', 'RAV4', 2023, 'CAL-202 ', 70.50, 12000), -- Trailing space plate
('1FA6P8CFXH5987654', 'Ford', 'Mustang', 2021, 'FLA-303', 95.75, 21000),
('WVWZZZAUZMW112233', 'Volkswagen', 'Golf', 2022, 'ILL-404', 60.00, 18500),
('JTDKBRFU0P0654ABC', 'Toyota', 'RAV4', 2023, NULL, 72.00, 9500); -- NULL plate

INSERT INTO customers (first_name, last_name, email, date_of_birth, driver_license_no, join_date) VALUES
('Walter', 'White', 'w.white@jpwynne.edu', '1958-09-07', 'NM123456', '2022-01-15'),
('Jesse', 'Pinkman', 'capncook@kryskay.com', '1984-08-24', 'NM654321', '2022-05-20'),
('Skyler', 'White', 's.white@accountant.net', '1970-08-11', 'NM789012', '2023-03-10'),
('Saul', 'Goodman', 'saul@bettercallsaul.com', '1960-11-07', NULL, '2021-11-01'), -- NULL license
('Mike', 'Ehrmantraut', 'mike.e@security.pro', '1945-03-18', 'PA999888', '2023-06-01');

INSERT INTO rentals (vehicle_id, customer_id, pickup_location_id, dropoff_location_id, pickup_datetime, return_datetime, total_amount) VALUES
(1, 1, 1, 1, '2024-07-01 10:00:00-04', '2024-07-08 11:30:00-04', 412.50),
(2, 2, 2, 2, '2024-08-10 14:00:00-07', NULL, NULL), -- Currently rented
(3, 3, 4, 4, '2024-08-20 09:00:00-04', '2024-08-25 10:00:00-04', 574.50),
(4, 4, 3, NULL, '2024-09-05 12:00:00-05', NULL), -- Currently rented, NULL dropoff (assume same)
(1, 5, 1, 3, '2024-09-10 15:30:00-04', NULL); -- Currently rented

Tasks for Variant 9:

Customer Full Name and Length: Display customer_id. Create a full_name by concatenating first_name and last_name. Also display the length of the full_name. Alias as name_length.
Cleaned License Plates: Show vehicle_id, make, model, and the license_plate. Create a clean_plate column by removing any leading/trailing spaces and converting the result to uppercase. Handle NULL plates gracefully.
Customer Age: Calculate the current age of each customer based on their date_of_birth. Display first_name, last_name, date_of_birth, and the calculated age. Handle NULL date_of_birth if any were added.
Rental Duration (Completed Rentals): For rentals where return_datetime is NOT NULL, calculate the duration of the rental. Display rental_id, pickup_datetime, return_datetime, and the calculated rental_duration as an interval.
Vehicle Status: Display vehicle_id, make, model. Determine the vehicle’s status. Check if the vehicle_id exists in the rentals table with a NULL return_datetime. If yes, status is ‘Rented’. Otherwise, status is ‘Available’. (Requires a LEFT JOIN and CASE or subquery). Simplified for assignment: Base this only on the sample data shown. If a vehicle appears in rentals with NULL return_datetime, it’s ‘Rented’, else ‘Available’. Use CASE.
VIN Uppercase: Display vehicle_id and the vin number converted entirely to uppercase. Alias as upper_vin.
Vehicle Make Abbreviation: Show the vehicle_id and the first 3 characters of the make. Alias as make_abbr. (Use SUBSTRING).
Days Since Customer Joined: Calculate how many days have passed since each customer’s join_date. Display customer_id, email, join_date, and days_as_member. (Use CURRENT_DATE - join_date).
Daily Rate Rounded Down: Display vehicle_id, daily_rate. Also show the daily_rate rounded DOWN to the nearest whole dollar. Alias this as rate_floor.
Return Location Display: Display rental_id, pickup_location_id. Use COALESCE to show the dropoff_location_id. If it’s NULL, display the pickup_location_id instead (assuming same location return). Alias this as effective_dropoff_id.
Pickup Month and Hour: Extract the month (numeric) and the hour (0-23) from the pickup_datetime. Display rental_id, pickup_datetime, pickup_month, and pickup_hour.
Absolute Rate Difference: Calculate the absolute difference between each vehicle’s daily_rate and an average rate of $75.00. Display vehicle_id, make, model, daily_rate, and abs_diff_from_avg.
Nullify Specific Model: Use NULLIF to return NULL if a vehicle’s model is ‘RAV4’. Otherwise, return the model. Display vehicle_id, make, model, and the result as null_if_rav4.
Customer ID Parity Check: Determine if a customer_id is odd or even. Display the customer_id, first_name, last_name, and ‘Even’ or ‘Odd’ as id_parity. (Use MOD).
Format Daily Rate as Text: Display vehicle_id, make, model, and daily_rate. Create a formatted_rate string that shows the rate like ‘$XX.YY per day’. (e.g., ‘$55.00 per day’). Use CAST and ||.

Variant 10: Restaurant Ordering System

Scenario: You are managing the database for a restaurant. You need to query information about menu items, customer orders, and customer details.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS OrderItems;
-- DROP TABLE IF EXISTS Orders;
-- DROP TABLE IF EXISTS Customers;
-- DROP TABLE IF EXISTS MenuItems;

-- 1. Create the 'MenuItems' table
CREATE TABLE MenuItems (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(100) NOT NULL,         -- For UPPER, LENGTH
    description TEXT,                        -- Allows NULL
    category VARCHAR(50),                    -- For CASE
    price NUMERIC(6, 2) NOT NULL,            -- For ROUND, CEIL, FLOOR, ABS
    prep_time_minutes INTEGER,               -- For simple numeric ops
    item_code VARCHAR(20) UNIQUE             -- e.g., ' APP001 ', for TRIM
);

-- 2. Create the 'Customers' table
CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20),                -- May contain '-', ' ', '(', ')' for REPLACE
    email VARCHAR(100) UNIQUE,               -- For LOWER, COALESCE (maybe nullable)
    join_date DATE DEFAULT CURRENT_DATE      -- For AGE, EXTRACT
);

-- 3. Create the 'Orders' table
CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES Customers(customer_id), -- Can be NULL for walk-ins
    order_time TIMESTAMP NOT NULL DEFAULT NOW(), -- For EXTRACT, CAST
    status VARCHAR(20) DEFAULT 'Pending',     -- 'Pending', 'Preparing', 'Ready', 'Completed', 'Cancelled'
    table_number INTEGER,                     -- For NULLIF
    is_takeaway BOOLEAN NOT NULL DEFAULT FALSE
);

-- 4. Create the 'OrderItems' table
CREATE TABLE OrderItems (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES Orders(order_id),
    item_id INTEGER NOT NULL REFERENCES MenuItems(item_id),
    quantity INTEGER NOT NULL DEFAULT 1,
    special_requests VARCHAR(255)             -- Allows NULL, for COALESCE
);

-- Sample Data Insertion --

INSERT INTO MenuItems (item_name, description, category, price, prep_time_minutes, item_code) VALUES
('Classic Burger', 'Beef patty, lettuce, tomato, cheese', 'Burgers', 12.50, 10, ' BUR001 '),
('Veggie Delight Pizza', NULL, 'Pizza', 15.00, 15, 'PIZ005'),
('Caesar Salad', 'Romaine lettuce, croutons, parmesan, caesar dressing', 'Salads', 9.75, 7, ' SAL002'),
('French Fries', 'Crispy potato fries', 'Sides', 4.50, 5, 'SID001'),
('Spaghetti Carbonara', 'Pasta with egg, cheese, pancetta', 'Pasta', 14.00, 12, 'PAS003 '), -- Trailing space
('Chocolate Lava Cake', 'Warm chocolate cake with molten center', 'Desserts', 7.99, 8, ' DES001');

INSERT INTO Customers (first_name, last_name, phone_number, email, join_date) VALUES
('Walter', 'White', '505-123-4567', 'heisenberg@bb.net', '2022-01-15'),
('Jesse', 'Pinkman', '(505) 987-6543', 'CAPNCook@bb.net', '2022-05-20'),
('Skyler', 'White', NULL, 'skyler.white@carwash.com', '2022-01-15'),
('Gus', 'Fring', '505 555 0101', NULL, '2023-03-10'); -- NULL email

INSERT INTO Orders (customer_id, order_time, status, table_number, is_takeaway) VALUES
(1, '2023-10-26 19:05:00', 'Completed', 5, FALSE),
(2, '2023-10-26 19:15:00', 'Preparing', NULL, TRUE),
(NULL, '2023-10-26 19:20:00', 'Pending', 12, FALSE),
(1, '2023-09-10 12:30:00', 'Completed', 3, FALSE),
(3, '2023-10-27 08:00:00', 'Pending', NULL, TRUE);

INSERT INTO OrderItems (order_id, item_id, quantity, special_requests) VALUES
(1, 1, 1, 'Extra cheese'),
(1, 4, 1, NULL),
(2, 2, 1, 'No onions'),
(2, 3, 1, 'Dressing on the side'),
(3, 5, 2, NULL),
(4, 1, 1, NULL),
(4, 6, 1, 'Add ice cream');

Tasks for Variant 10:

Customer Full Name: Display customer_id, first_name, last_name, and concatenate first_name and last_name into full_name.
Item Name Length: Show the item_name and its character LENGTH for all menu items. Alias the length column as name_length.
Clean Phone Numbers: Display customer_id and phone_number. Create a cleaned_phone column by removing spaces, hyphens, parentheses ‘(‘ and ‘)’ from the phone_number. (Hint: Multiple REPLACE calls).
Item Price Rounded: Show item_name, price, and the price rounded to the nearest whole dollar (ROUND with 0 decimal places). Alias as rounded_price.
Order Day of Week: Display the order_id and order_time. Extract the day of the week (e.g., 0 for Sunday, 1 for Monday… 6 for Saturday in PostgreSQL using DOW) from the order_time. Alias as order_dow.
Membership Duration: Calculate how long each customer (who has a join_date) has been registered. Display customer_id, join_date, and the duration using AGE(). Alias as membership_age.
Item Code Cleanup: Display the item_id and item_code, removing any leading or trailing spaces. Alias the cleaned column as clean_item_code.
Special Requests Display: Show the order_item_id. Use COALESCE to display the special_requests; if NULL, display ‘None’. Alias as requests_info.
Categorize Prep Time: Display item_name, prep_time_minutes. Create a prep_category column: ‘Quick’ if < 8 minutes, ‘Standard’ if 8-12 minutes (inclusive), ‘Long’ if > 12 minutes. Handle NULL prep_time_minutes as ‘Unknown’.
Standardized Customer Emails: Display customer_id and email. Show the email in all lowercase, aliased as lower_email. If the email is NULL, display ‘N/A’ (Use LOWER and COALESCE).
Absolute Price Difference from $10: For each menu item, calculate the absolute difference between its price and $10.00. Display item_name, price, and abs_diff_from_10.
Order Time - Hour: Extract the hour (0-23) from the order_time for each order. Display order_id, order_time, and order_hour.
Identify Non-Takeaway Table Orders: Use NULLIF to return NULL if an order is_takeaway is TRUE. Combine this with checking if table_number is not NULL to conceptually identify non-takeaway orders assigned to a table. Simplified Task: Use NULLIF to return NULL if the table_number is 5. Display order_id, table_number, and null_if_table5.
Next Week’s Orders (Conceptual): Show orders placed within the last 7 days. Display order_id, order_time. Use order_time >= CURRENT_DATE - INTERVAL '7 day'.
Format Item Price: Display item_name. Create a formatted_price string by prefixing the price with ‘$’ and casting it to VARCHAR. (e.g., ‘$12.50’).

Variant 11: Real Estate Listing Service

Scenario: You manage a database for a real estate agency, tracking properties for sale, agents, clients, and property viewings.

Database Schema:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS Viewings;
-- DROP TABLE IF EXISTS Clients;
-- DROP TABLE IF EXISTS Agents;
-- DROP TABLE IF EXISTS Properties;

-- 1. Create the 'Properties' table
CREATE TABLE Properties (
    property_id SERIAL PRIMARY KEY,
    address_line1 VARCHAR(255) NOT NULL,       -- For general use
    city VARCHAR(100),
    postal_code VARCHAR(12),                   -- e.g., ' SW1A 0AA ', for TRIM
    property_type VARCHAR(50),                 -- 'House', 'Apartment', 'Commercial', etc. For CASE
    list_price NUMERIC(12, 2),                 -- For ROUND, CEIL, ABS
    bedrooms INTEGER,                          -- For MOD
    square_footage INTEGER,                    -- For simple numeric ops
    list_date DATE NOT NULL,                   -- For AGE, EXTRACT
    status VARCHAR(20) DEFAULT 'Active'        -- 'Active', 'Pending', 'Sold'
);

-- 2. Create the 'Agents' table
CREATE TABLE Agents (
    agent_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),                   -- For COALESCE
    last_name VARCHAR(50) NOT NULL,
    license_number VARCHAR(30) UNIQUE NOT NULL,-- For LENGTH, SUBSTRING
    phone VARCHAR(20),
    email VARCHAR(100) UNIQUE NOT NULL,        -- For LOWER
    hire_date DATE                             -- For AGE
);

-- 3. Create the 'Clients' table
CREATE TABLE Clients (
    client_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)                          -- For REPLACE (cleaning)
    -- preferred_contact_method VARCHAR(10) NULL -- Not used in tasks for simplicity
);

-- 4. Create the 'Viewings' table
CREATE TABLE Viewings (
    viewing_id SERIAL PRIMARY KEY,
    property_id INTEGER NOT NULL REFERENCES Properties(property_id),
    client_id INTEGER NOT NULL REFERENCES Clients(client_id),
    agent_id INTEGER NOT NULL REFERENCES Agents(agent_id),
    viewing_time TIMESTAMP NOT NULL,           -- For EXTRACT, CAST to DATE
    client_feedback_score INTEGER              -- NULLable, 1-5. For NULLIF
    -- feedback_notes TEXT NULL -- Not used in tasks
);

-- Sample Data Insertion --

INSERT INTO Properties (address_line1, city, postal_code, property_type, list_price, bedrooms, square_footage, list_date, status) VALUES
('10 Downing St', 'London', ' SW1A 2AA ', 'House', 15000000.00, 10, 5000, '2023-01-15', 'Active'),
('221B Baker St', 'London', 'NW1 6XE', 'Apartment', 1200000.50, 3, 1500, '2023-03-01', 'Active'),
('1600 Pennsylvania Ave NW', 'Washington', ' 20500', 'House', 40000000.00, 132, 55000, '2022-11-10', 'Pending'), -- Space in postal code
('1 Privet Drive', 'Little Whinging', ' GU15 3EJ', 'House', 350000.75, 4, 1200, '2023-05-20', 'Sold'), -- Space
('42 Wallaby Way', 'Sydney', ' NSW 2000 ', 'Commercial', 850000.00, 0, 2500, '2023-07-01', 'Active'); -- Spaces

INSERT INTO Agents (first_name, middle_name, last_name, license_number, phone, email, hire_date) VALUES
('James', 'Tiberius', 'Kirk', 'NCC-1701-A', '555-1234', 'j.kirk@starfleet.org', '2018-04-01'),
('Jean-Luc', NULL, 'Picard', 'NCC-1701-D', '555-5678', 'jl.picard@starfleet.org', '2015-09-01'),
('Kathryn', 'Janeway', 'V', 'NCC-74656', '555-9101', 'K.JANEWAY@starfleet.org', '2019-11-15');

INSERT INTO Clients (first_name, last_name, email, phone) VALUES
('Frodo', 'Baggins', 'frodo@shire.net', '123-456-7890'),
('Samwise', 'Gamgee', 'sam.gamgee@shire.net', '123-456-7891'),
('Luke', 'Skywalker', 'luke@rebellion.org', '(987) 654-3210'),
('Leia', 'Organa', 'leia.organa@rebellion.org', NULL);

INSERT INTO Viewings (property_id, client_id, agent_id, viewing_time, client_feedback_score) VALUES
(1, 1, 2, '2023-02-10 14:00:00', 4),
(2, 3, 1, '2023-03-15 11:00:00', 5),
(2, 4, 1, '2023-03-18 15:30:00', NULL),
(4, 2, 3, '2023-06-01 10:00:00', 3),
(5, 1, 2, '2023-08-05 16:00:00', 2);

Tasks for Variant 11:

Agent Full Name Display: Show agent_id, first_name, last_name. Use COALESCE to display the middle_name; if NULL, show an empty string ‘’. Concatenate first, middle (if present), and last names into full_name.
Property Postal Code Cleanup: Display property_id and postal_code. Clean the postal_code by removing leading/trailing spaces and converting it to uppercase. Alias as clean_postal_code.
Agent Tenure: Calculate how long each agent has been with the agency as of CURRENT_DATE. Display agent_id, hire_date, and the calculated tenure.
Property Price Categorization: Display property_id, list_price. Categorize based on price: ‘Affordable’ if < 500,000, ‘Mid-Range’ if 500,000 to 1,999,999.99, ‘Luxury’ if >= 2,000,000. Alias as price_category.
Standardized Agent Emails: Show the agent_id and email, ensuring the email is entirely in lowercase. Alias as standard_email.
Property Listing Age: Calculate how many days each property has been listed (from list_date to CURRENT_DATE). Display property_id, list_date, and the difference in days. (Hint: CURRENT_DATE - list_date). Alias as days_on_market.
License Number Length Check: Display agent_id, license_number, and the LENGTH of the license_number. Alias as license_length.
Viewings This Year: Find viewings that occurred in the current calendar year. Display viewing_id, viewing_time. Use EXTRACT(YEAR FROM viewing_time) = EXTRACT(YEAR FROM CURRENT_DATE).
Price Rounded Up (Nearest Million): Display property_id, list_price. Show the list_price rounded UP to the nearest million. Alias as price_ceil_million. (Hint: Divide by 1,000,000, use CEIL, then multiply back).
Extract Last 4 Digits of Phone (Conceptual): For clients with a phone number, extract the last 4 digits. Display client_id, phone. (Hint: Use SUBSTRING and LENGTH. Requires assumption about phone format or cleaning first). Simplified Task: Extract the first 3 characters of the first_name. Alias as name_prefix.
Absolute Price Difference from Average: Assume an average property price of $1,000,000. Calculate the absolute difference between each property’s list_price and this average. Display property_id, list_price, and abs_diff_from_avg.
Odd/Even Bedrooms: Determine if a property has an odd or even number of bedrooms. Display property_id, bedrooms, and ‘Odd’ or ‘Even’ as bedroom_parity. Handle NULL bedrooms.
NULL Feedback Scores: Use NULLIF to return NULL if the client_feedback_score for a viewing is 3. Display viewing_id, client_feedback_score, and null_if_score_3.
Client Phone Number Cleanup: Display client_id, phone. Use REPLACE to remove ( , ) and - characters from the phone number. Alias as clean_phone.
Agent ID Padding: Display the agent_id formatted as a 4-digit string with leading zeros (e.g., 1 becomes ‘0001’). Alias as formatted_agent_id. (Hint: LPAD and CAST).

Submission Instructions

Create a new Google Document. Example
Submit Your Google Doc Link: Google Sheet