-- Create tables with relationships
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO users (username, email) VALUES 
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com');

INSERT INTO posts (user_id, title, content, status) VALUES 
    (1, 'First Post', 'This is my first blog post.', 'published'),
    (1, 'Second Post', 'Another interesting post.', 'published'),
    (2, 'Draft Post', 'This is still a draft.', 'draft');

INSERT INTO comments (post_id, user_id, content) VALUES 
    (1, 2, 'Great first post!'),
    (1, 3, 'Thanks for sharing.'),
    (2, 3, 'Very informative.');

-- Complex queries with JOINs, aggregations, and CTEs
WITH user_stats AS (
    SELECT 
        u.id,
        u.username,
        COUNT(DISTINCT p.id) as post_count,
        COUNT(DISTINCT c.id) as comment_count,
        MAX(p.created_at) as last_post_date
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id AND p.status = 'published'
    LEFT JOIN comments c ON u.id = c.user_id
    GROUP BY u.id, u.username
),
popular_posts AS (
    SELECT 
        p.id,
        p.title,
        u.username as author,
        COUNT(c.id) as comment_count,
        RANK() OVER (ORDER BY COUNT(c.id) DESC) as popularity_rank
    FROM posts p
    JOIN users u ON p.user_id = u.id
    LEFT JOIN comments c ON p.id = c.post_id
    WHERE p.status = 'published'
    GROUP BY p.id, p.title, u.username
)
SELECT 
    us.username,
    us.post_count,
    us.comment_count,
    us.last_post_date,
    COALESCE(pp.title, 'No posts') as most_popular_post,
    COALESCE(pp.comment_count, 0) as most_popular_post_comments
FROM user_stats us
LEFT JOIN popular_posts pp ON pp.popularity_rank = 1
ORDER BY us.post_count DESC, us.comment_count DESC;

-- Window functions example
SELECT 
    u.username,
    p.title,
    p.created_at,
    LAG(p.created_at) OVER (
        PARTITION BY u.id 
        ORDER BY p.created_at
    ) as previous_post_date,
    COUNT(*) OVER (
        PARTITION BY u.id
    ) as total_user_posts,
    ROW_NUMBER() OVER (
        PARTITION BY u.id 
        ORDER BY p.created_at
    ) as post_sequence
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.status = 'published'
ORDER BY u.username, p.created_at;

Demonstrates advanced SQL features including JOINs, CTEs, window functions, and complex aggregations.

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2),
    department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO departments (department_name, location) VALUES
    ('Engineering', 'New York'),
    ('Marketing', 'Chicago'),
    ('Finance', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
    ('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1),
    ('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1),
    ('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2),
    ('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3);

SELECT
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS highest_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

SELECT
    first_name,
    last_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

CREATE VIEW employee_summary AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM employee_summary;

Demonstrates employee, department, payroll, and reporting operations using joins, aggregations, views, and window functions.

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2),
    department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO departments (department_name, location) VALUES
    ('Engineering', 'New York'),
    ('Marketing', 'Chicago'),
    ('Finance', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
    ('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1),
    ('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1),
    ('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2),
    ('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3);

SELECT
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS highest_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

SELECT
    first_name,
    last_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

CREATE VIEW employee_summary AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM employee_summary;

Demonstrates employee, department, payroll, and reporting operations using joins, aggregations, views, and window functions.

CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    supplier_name VARCHAR(100)
);

CREATE TABLE inventory (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(100),
    stock_quantity INTEGER,
    reorder_level INTEGER,
    supplier_id INTEGER REFERENCES suppliers(supplier_id)
);

INSERT INTO suppliers (supplier_name) VALUES
    ('Tech Supply Co'),
    ('Global Parts Ltd');

INSERT INTO inventory (
    item_name,
    stock_quantity,
    reorder_level,
    supplier_id
) VALUES
    ('SSD Drive', 50, 20, 1),
    ('RAM Module', 15, 25, 1),
    ('Power Supply', 10, 15, 2);

SELECT
    item_name,
    stock_quantity,
    reorder_level,
    CASE
        WHEN stock_quantity < reorder_level THEN 'REORDER'
        ELSE 'OK'
    END AS inventory_status
FROM inventory;

SELECT
    s.supplier_name,
    COUNT(i.item_id) AS supplied_items
FROM suppliers s
JOIN inventory i
    ON s.supplier_id = i.supplier_id
GROUP BY s.supplier_name;

CREATE INDEX idx_inventory_stock
ON inventory(stock_quantity);

SELECT *
FROM inventory
ORDER BY stock_quantity ASC;

Tracks stock levels, suppliers, inventory movement, and reorder alerts.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE purchases (
    purchase_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    purchase_amount DECIMAL(10,2),
    purchase_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie');

INSERT INTO purchases (
    customer_id,
    purchase_amount,
    purchase_date
) VALUES
    (1, 500, '2025-01-01'),
    (1, 700, '2025-02-01'),
    (2, 100, '2025-02-10'),
    (3, 2000, '2025-01-15');

WITH customer_value AS (
    SELECT
        customer_id,
        COUNT(*) AS purchase_count,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
)
SELECT
    customer_id,
    purchase_count,
    total_spent,
    CASE
        WHEN total_spent > 1000 THEN 'VIP'
        WHEN total_spent > 300 THEN 'REGULAR'
        ELSE 'NEW'
    END AS segment
FROM customer_value;

SELECT
    customer_id,
    total_spent,
    NTILE(4) OVER (
        ORDER BY total_spent DESC
    ) AS spending_quartile
FROM (
    SELECT
        customer_id,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
) stats;

Segments customers by purchase behavior, frequency, and lifetime value.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE purchases (
    purchase_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    purchase_amount DECIMAL(10,2),
    purchase_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie');

INSERT INTO purchases (
    customer_id,
    purchase_amount,
    purchase_date
) VALUES
    (1, 500, '2025-01-01'),
    (1, 700, '2025-02-01'),
    (2, 100, '2025-02-10'),
    (3, 2000, '2025-01-15');

WITH customer_value AS (
    SELECT
        customer_id,
        COUNT(*) AS purchase_count,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
)
SELECT
    customer_id,
    purchase_count,
    total_spent,
    CASE
        WHEN total_spent > 1000 THEN 'VIP'
        WHEN total_spent > 300 THEN 'REGULAR'
        ELSE 'NEW'
    END AS segment
FROM customer_value;

SELECT
    customer_id,
    total_spent,
    NTILE(4) OVER (
        ORDER BY total_spent DESC
    ) AS spending_quartile
FROM (
    SELECT
        customer_id,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
) stats;

Segments customers by purchase behavior, frequency, and lifetime value.

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_holder VARCHAR(100),
    account_type VARCHAR(50)
);

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INTEGER REFERENCES accounts(account_id),
    amount DECIMAL(12,2),
    transaction_type VARCHAR(20),
    transaction_date DATE
);

INSERT INTO accounts (account_holder, account_type) VALUES
    ('Alice Johnson', 'Savings'),
    ('Bob Smith', 'Checking');

INSERT INTO transactions (
    account_id,
    amount,
    transaction_type,
    transaction_date
) VALUES
    (1, 1500, 'deposit', '2025-01-01'),
    (1, 300, 'withdrawal', '2025-01-10'),
    (2, 2200, 'deposit', '2025-01-03'),
    (2, 500, 'withdrawal', '2025-01-15');

SELECT
    account_id,
    SUM(
        CASE
            WHEN transaction_type = 'deposit' THEN amount
            ELSE -amount
        END
    ) AS current_balance
FROM transactions
GROUP BY account_id;

SELECT
    transaction_date,
    amount,
    SUM(amount) OVER (
        ORDER BY transaction_date
    ) AS running_total
FROM transactions;

Analyzes financial transactions, balances, and monthly spending trends using aggregations and window functions.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE bank_accounts (
    account_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    balance DECIMAL(12,2)
);

INSERT INTO customers (customer_name, email) VALUES
    ('John Doe', 'john@example.com'),
    ('Emma Wilson', 'emma@example.com');

INSERT INTO bank_accounts (
    customer_id,
    balance
) VALUES
    (1, 10000),
    (2, 7500);

SELECT
    c.customer_name,
    b.account_id,
    b.balance
FROM customers c
JOIN bank_accounts b
    ON c.customer_id = b.customer_id;

SELECT
    AVG(balance) AS average_balance,
    MAX(balance) AS highest_balance,
    MIN(balance) AS lowest_balance
FROM bank_accounts;

Demonstrates customer accounts, transactions, and account summaries.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_status VARCHAR(30),
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob');

INSERT INTO orders (
    customer_id,
    order_status,
    order_date
) VALUES
    (1, 'Pending', '2025-02-01'),
    (1, 'Shipped', '2025-02-02'),
    (2, 'Delivered', '2025-02-03');

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

UPDATE orders
SET order_status = 'Completed'
WHERE order_status = 'Delivered';

SELECT *
FROM orders;

Handles order lifecycle, status updates, and customer purchases.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_status VARCHAR(30),
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob');

INSERT INTO orders (
    customer_id,
    order_status,
    order_date
) VALUES
    (1, 'Pending', '2025-02-01'),
    (1, 'Shipped', '2025-02-02'),
    (2, 'Delivered', '2025-02-03');

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

UPDATE orders
SET order_status = 'Completed'
WHERE order_status = 'Delivered';

SELECT *
FROM orders;

Handles order lifecycle, status updates, and customer purchases.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_status VARCHAR(30),
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob');

INSERT INTO orders (
    customer_id,
    order_status,
    order_date
) VALUES
    (1, 'Pending', '2025-02-01'),
    (1, 'Shipped', '2025-02-02'),
    (2, 'Delivered', '2025-02-03');

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

UPDATE orders
SET order_status = 'Completed'
WHERE order_status = 'Delivered';

SELECT *
FROM orders;

Handles order lifecycle, status updates, and customer purchases.

CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    patient_name VARCHAR(100),
    date_of_birth DATE
);

CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INTEGER REFERENCES patients(patient_id),
    appointment_date DATE,
    doctor_name VARCHAR(100)
);

INSERT INTO patients (
    patient_name,
    date_of_birth
) VALUES
    ('Alice Brown', '1990-05-20'),
    ('David Green', '1985-09-12');

INSERT INTO appointments (
    patient_id,
    appointment_date,
    doctor_name
) VALUES
    (1, '2025-03-01', 'Dr. Smith'),
    (2, '2025-03-02', 'Dr. Johnson');

SELECT
    p.patient_name,
    a.appointment_date,
    a.doctor_name
FROM patients p
JOIN appointments a
    ON p.patient_id = a.patient_id;

SELECT
    doctor_name,
    COUNT(*) AS total_appointments
FROM appointments
GROUP BY doctor_name;

Stores patients, appointments, and medical records with reporting queries.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100),
    credits INTEGER
);

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

INSERT INTO students (first_name, last_name, email) VALUES
    ('Alice', 'Johnson', 'alice@example.com'),
    ('Bob', 'Smith', 'bob@example.com');

INSERT INTO courses (course_name, credits) VALUES
    ('Database Systems', 3),
    ('Algorithms', 4);

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
    (1, 1, '2025-01-10'),
    (1, 2, '2025-01-10'),
    (2, 1, '2025-01-15');

SELECT
    s.first_name,
    s.last_name,
    c.course_name
FROM students s
JOIN enrollments e
    ON s.student_id = e.student_id
JOIN courses c
    ON c.course_id = e.course_id
ORDER BY s.first_name;

Manages students, courses, enrollments, and academic reporting.

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(150),
    author VARCHAR(100),
    available BOOLEAN DEFAULT TRUE
);

CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100)
);

CREATE TABLE borrow_records (
    record_id SERIAL PRIMARY KEY,
    book_id INTEGER REFERENCES books(book_id),
    member_id INTEGER REFERENCES members(member_id),
    borrow_date DATE
);

INSERT INTO books (title, author) VALUES
    ('Clean Code', 'Robert Martin'),
    ('Design Patterns', 'GoF');

INSERT INTO members (member_name) VALUES
    ('John Doe'),
    ('Sarah Brown');

INSERT INTO borrow_records (book_id, member_id, borrow_date) VALUES
    (1, 1, '2025-02-01'),
    (2, 2, '2025-02-05');

SELECT
    m.member_name,
    b.title,
    br.borrow_date
FROM borrow_records br
JOIN books b
    ON br.book_id = b.book_id
JOIN members m
    ON br.member_id = m.member_id;

Tracks books, members, and borrowing records.

CREATE TABLE rooms (
    room_id SERIAL PRIMARY KEY,
    room_number VARCHAR(10),
    room_type VARCHAR(50),
    price_per_night DECIMAL(10,2)
);

CREATE TABLE guests (
    guest_id SERIAL PRIMARY KEY,
    guest_name VARCHAR(100)
);

CREATE TABLE reservations (
    reservation_id SERIAL PRIMARY KEY,
    guest_id INTEGER REFERENCES guests(guest_id),
    room_id INTEGER REFERENCES rooms(room_id),
    check_in DATE,
    check_out DATE
);

INSERT INTO rooms (room_number, room_type, price_per_night) VALUES
    ('101', 'Single', 120),
    ('201', 'Suite', 300);

INSERT INTO guests (guest_name) VALUES
    ('Alice Johnson'),
    ('Mark Wilson');

INSERT INTO reservations (guest_id, room_id, check_in, check_out) VALUES
    (1, 1, '2025-04-01', '2025-04-05'),
    (2, 2, '2025-04-10', '2025-04-15');

SELECT
    g.guest_name,
    r.room_number,
    res.check_in,
    res.check_out
FROM reservations res
JOIN guests g
    ON g.guest_id = res.guest_id
JOIN rooms r
    ON r.room_id = res.room_id;

Handles guests, rooms, reservations, and occupancy reports.

CREATE TABLE dim_product (
    product_key SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50)
);

CREATE TABLE dim_date (
    date_key DATE PRIMARY KEY,
    year INTEGER,
    month INTEGER
);

CREATE TABLE fact_sales (
    sales_key SERIAL PRIMARY KEY,
    product_key INTEGER REFERENCES dim_product(product_key),
    date_key DATE REFERENCES dim_date(date_key),
    amount DECIMAL(12,2)
);

INSERT INTO dim_product (product_name, category) VALUES
    ('Laptop', 'Electronics'),
    ('Desk', 'Furniture');

INSERT INTO dim_date VALUES
    ('2025-01-01', 2025, 1),
    ('2025-02-01', 2025, 2);

INSERT INTO fact_sales (product_key, date_key, amount) VALUES
    (1, '2025-01-01', 1500),
    (1, '2025-02-01', 1800),
    (2, '2025-02-01', 700);

SELECT
    dp.category,
    SUM(fs.amount) AS total_sales
FROM fact_sales fs
JOIN dim_product dp
    ON fs.product_key = dp.product_key
GROUP BY dp.category;

Performs analytical queries and dimensional aggregations.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE audit_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    action VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username) VALUES
    ('admin'),
    ('manager');

INSERT INTO audit_logs (user_id, action) VALUES
    (1, 'Created employee record'),
    (1, 'Updated salary information'),
    (2, 'Generated monthly report');

SELECT
    u.username,
    a.action,
    a.created_at
FROM audit_logs a
JOIN users u
    ON a.user_id = u.user_id
ORDER BY a.created_at DESC;

SELECT
    u.username,
    COUNT(*) AS total_actions
FROM audit_logs a
JOIN users u
    ON a.user_id = u.user_id
GROUP BY u.username;

Stores user activity logs and generates audit reports.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INTEGER REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_name, manager_id) VALUES
    ('CEO', NULL),
    ('Engineering Manager', 1),
    ('Sales Manager', 1),
    ('Developer A', 2),
    ('Developer B', 2),
    ('Sales Executive', 3);

WITH RECURSIVE organization_chart AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        oc.level + 1
    FROM employees e
    JOIN organization_chart oc
        ON e.manager_id = oc.employee_id
)
SELECT *
FROM organization_chart
ORDER BY level, employee_name;

Demonstrates hierarchical queries using recursive common table expressions.

CREATE TABLE monthly_sales (
    sale_id SERIAL PRIMARY KEY,
    salesperson VARCHAR(100),
    sale_month DATE,
    revenue DECIMAL(12,2)
);

INSERT INTO monthly_sales (salesperson, sale_month, revenue) VALUES
    ('John', '2025-01-01', 12000),
    ('John', '2025-02-01', 15000),
    ('Sarah', '2025-01-01', 18000),
    ('Sarah', '2025-02-01', 22000);

SELECT
    salesperson,
    sale_month,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY salesperson
        ORDER BY sale_month
    ) AS running_total,
    LAG(revenue) OVER (
        PARTITION BY salesperson
        ORDER BY sale_month
    ) AS previous_month_revenue,
    RANK() OVER (
        ORDER BY revenue DESC
    ) AS revenue_rank
FROM monthly_sales
ORDER BY salesperson, sale_month;

Uses ranking, running totals, and lag functions for analytical reporting.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE,
    total_amount DECIMAL(12,2)
);

CREATE INDEX idx_order_date
ON orders(order_date);

CREATE INDEX idx_customer_name
ON orders(customer_name);

INSERT INTO orders (customer_name, order_date, total_amount) VALUES
    ('Alice', '2025-01-01', 1200),
    ('Bob', '2025-01-05', 450),
    ('Alice', '2025-02-01', 850);

CREATE VIEW customer_totals AS
SELECT
    customer_name,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY customer_name;

SELECT *
FROM customer_totals
ORDER BY revenue DESC;

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_name = 'Alice';

Illustrates indexes, views, and query optimization techniques.

CREATE TABLE legacy_customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO legacy_customers (full_name, email) VALUES
    ('Alice Johnson', 'alice@example.com'),
    ('Bob Smith', 'bob@example.com');

BEGIN;

INSERT INTO customers (
    first_name,
    last_name,
    email
)
SELECT
    SPLIT_PART(full_name, ' ', 1),
    SPLIT_PART(full_name, ' ', 2),
    email
FROM legacy_customers;

COMMIT;

SELECT *
FROM customers;

Shows data migration between legacy and new tables using transactions.

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

CREATE TABLE publishers (
    publisher_id SERIAL PRIMARY KEY,
    publisher_name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES authors(author_id),
    publisher_id INTEGER REFERENCES publishers(publisher_id),
    publication_year INTEGER
);

INSERT INTO authors (author_name) VALUES
    ('Robert Martin'),
    ('Martin Fowler');

INSERT INTO publishers (publisher_name) VALUES
    ('Prentice Hall'),
    ('Addison-Wesley');

INSERT INTO books (
    title,
    author_id,
    publisher_id,
    publication_year
) VALUES
    ('Clean Code', 1, 1, 2008),
    ('Refactoring', 2, 2, 1999);

SELECT
    b.title,
    a.author_name,
    p.publisher_name,
    b.publication_year
FROM books b
JOIN authors a
    ON b.author_id = a.author_id
JOIN publishers p
    ON b.publisher_id = p.publisher_id
ORDER BY b.publication_year DESC;

Demonstrates normalized database design with relationships and constraints.