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:

  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.
  2. 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).
  3. 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.
  4. 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).
  5. 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.
  6. 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.
  7. 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/||).
  8. 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).
  9. 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.
  10. 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).
  11. 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 ...)).
  12. 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.
  13. 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.
  14. 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.
  15. 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. Standardized Student Emails: Show the student_id, first_name, and email, ensuring the email is entirely in lowercase. Alias this as standard_email.
  7. Course Name Abbreviation: Display the course_id and the first 20 characters of the course_name. Alias this as short_course_name.
  8. 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).
  9. 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).
  10. 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.
  11. 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)).
  12. 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.
  13. 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.
  14. 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 %).
  15. 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:

  1. Employee Full Name and Code: Display the emp_id. Concatenate first_name and last_name into full_name. Also display the emp_code.
  2. Project Name Uppercase: Show the project_id and the project_name entirely in uppercase, aliased as upper_project_name.
  3. 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().
  4. 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).
  5. 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.
  6. 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.
  7. 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).
  8. 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).
  9. 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.
  10. 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.
  11. 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.
  12. 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.
  13. Task Creation Hour: Extract the hour (0-23) from the creation_date timestamp for each task. Display task_id, creation_date, and creation_hour.
  14. 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.
  15. 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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).
  5. 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.
  6. 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.
  7. 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.
  8. 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)).
  9. 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.
  10. 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.
  11. 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).
  12. Order Placement Hour: Extract the hour (0-23) from the order_date for each order. Display order_id, order_date, and order_hour.
  13. 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.
  14. 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.
  15. 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:

  1. 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.
  2. 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).
  3. 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).
  4. 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.
  5. 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.
  6. Standardized Attendee Emails: Display the attendee_id, first_name, and email, ensuring the email is entirely in lowercase. Alias this as standard_email.
  7. 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.
  8. 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).
  9. 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).
  10. 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.
  11. 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.
  12. 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.
  13. Short Event Name: Display the event_id and the first 30 characters of the event_name. Alias this as short_event_name. Use SUBSTRING.
  14. Padded Venue ID: Display the venue_id formatted as a 5-digit string with leading zeros. Alias this as formatted_venue_id. Use LPAD.
  15. 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:

  1. Username Length Check: Display the user_id and username. Also show the length of each username, aliased as username_length.
  2. 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).
  3. 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.
  4. 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/||).
  5. Standardized User Emails: Display the user_id, username, and email, ensuring the email is entirely in lowercase. Alias this as standard_email.
  6. 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.
  7. 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.
  8. Post Visibility Status: Display post_id, title, and status. Create a column is_visible using CASE: TRUE if status is ‘Published’, FALSE otherwise.
  9. 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 %).
  10. 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')).
  11. 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.
  12. 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.
  13. 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).
  14. Padded User ID: Display the user_id formatted as a 7-digit string with leading zeros. Alias this as formatted_user_id. Use LPAD.
  15. 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).

Submission Instructions

Playful GIF