Joins and Relationships

-- One-to-Many: One user has many orders
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(200) UNIQUE NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product VARCHAR(200),
    amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Many-to-Many: Students enroll in courses
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200)
);

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

SELECT users.name, orders.product, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- Result:
-- Alice | Laptop    | 999.99
-- Alice | Mouse     | 29.99
-- Bob   | Keyboard  | 79.99

-- Using aliases (common pattern)
SELECT u.name, o.product, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Join with WHERE
SELECT u.name, o.product
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

-- Join three tables
SELECT u.name, o.product, p.category
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Result:
-- Alice   | 2
-- Bob     | 1
-- Charlie | 0  ← Even though Charlie has no orders

SELECT u.name, o.product
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

SELECT u.name, o.product
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Find employees and their managers
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Result:
-- Alice   | NULL    (CEO, no manager)
-- Bob     | Alice
-- Charlie | Alice
-- Diana   | Bob

SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;

-- Result: Every user × every product
-- (Alice, Laptop), (Alice, Mouse), (Bob, Laptop), (Bob, Mouse), ...

-- Total spending per user
SELECT
    u.name,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS total_spent,
    AVG(o.amount) AS avg_order,
    MAX(o.amount) AS largest_order,
    MIN(o.amount) AS smallest_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

-- Users who spent more than average
SELECT u.name, SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING SUM(o.amount) > (
    SELECT AVG(total) FROM (
        SELECT SUM(amount) AS total
        FROM orders
        GROUP BY user_id
    ) user_totals
);