Advanced SQL

Combine data across tables with JOINs, summarize with GROUP BY, filter aggregates with HAVING, and speed things up with indexes.

JOINGROUP BYsubqueryINDEX

Duration

⏱ About 2.5 hours

Level

📊 Intermediate

Prerequisite

🎯 db-02

OUTCOME

Query across related tables and write efficient aggregate queries

What you'll learn

1Use INNER, LEFT, RIGHT, FULL JOIN appropriately
2Aggregate with COUNT, SUM, AVG, MIN, MAX
3Filter aggregated results with HAVING
4Speed up queries with indexes

1. JOINs

sql

-- Get each task with the owner's name
SELECT t.id, t.title, u.name AS owner
FROM tasks t
JOIN users u ON t.user_id = u.id;

-- LEFT JOIN keeps tasks even without an owner
SELECT t.id, t.title, u.name AS owner
FROM tasks t
LEFT JOIN users u ON t.user_id = u.id;

INNER JOIN — only rows that match in both
LEFT JOIN — all from the left, plus matches
RIGHT JOIN — all from the right, plus matches (less common)
FULL OUTER JOIN — all from both

2. GROUP BY and HAVING

sql

-- Number of tasks per user
SELECT user_id, COUNT(*) AS task_count
FROM tasks
GROUP BY user_id;

-- Users with more than 5 tasks
SELECT user_id, COUNT(*) AS c
FROM tasks
GROUP BY user_id
HAVING c > 5;

WHERE filters rows before grouping; HAVING filters groups after aggregation.

3. Subqueries & EXISTS

sql

-- Users who have at least one open task
SELECT name FROM users
WHERE EXISTS (
  SELECT 1 FROM tasks t
  WHERE t.user_id = users.id AND t.done = 0
);

-- The most expensive product
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);

4. Indexes

sql

CREATE INDEX idx_tasks_user_id ON tasks(user_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);

Index columns used in WHERE, JOIN, and ORDER BY
Indexes speed up reads — but slow down writes and use disk
Use EXPLAIN to verify the query is using your index

Example code / lecture materials

All lecture materials and example code are openly available on GitHub.

View on GitHub ↗