Log inStart free

Lesson 7 of 10 · 15 min · 1 hands-on step

JOINs & GROUP BY

Combine tables and summarize data, the queries interviewers ask most often.

Why tables are split up

Good databases avoid repeating data. Instead of writing "Engineering" on every employee row, employees store a department_id that points to the departments table. This is called a foreign key.

JOIN

SQL · try it
SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id;
  • JOIN (inner join): only rows that match in both tables
  • LEFT JOIN: every row from the left table, with NULL where there's no match

Aggregates

SQL · try it
SELECT COUNT(*), AVG(salary), MAX(salary) FROM employees;

GROUP BY

Get one result row per group:

SQL · try it
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 90000;

Interview classic: "Find the second-highest salary" and "count orders per customer" are asked constantly. Both use exactly these tools. Try them in the Playground's SQL tab.

🧪 Practice

Using employees and departments(id, name), return each department name as department and its number of employees as headcount.

Only include departments with at least 2 employees. Order by headcount descending.

SQL
SELECT d.name AS department, e.name AS employee
FROM employees e
JOIN departments d ON d.id = e.department_id;

Output appears here. Press Run or Ctrl/⌘ + Enter.

✅ Check your understanding

  1. Which join keeps departments that have no employees?

  2. What's the difference between WHERE and HAVING?

Create a free account to track your progress.