SQL (Structured Query Language) is how you ask questions of a database. We’ll use two tables:
| employees | ||
|---|---|---|
| id | name | dept_id |
| 1 | Ravi | 10 |
| 2 | Maria | 20 |
| 3 | Chen | 10 |
| departments | |
|---|---|
| id | dept_name |
| 10 | Engineering |
| 20 | Finance |
SELECT and WHERE
SELECT name
FROM employees
WHERE dept_id = 10;
-- Ravi, Chen
ORDER BY
SELECT name FROM employees ORDER BY name;
-- Chen, Maria, Ravi
GROUP BY
SELECT dept_id, COUNT(*) AS headcount
FROM employees
GROUP BY dept_id;
-- 10 | 2
-- 20 | 1
JOIN
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON d.id = e.dept_id;
-- Ravi | Engineering
-- Maria | Finance
-- Chen | Engineering
Tip: use WHERE to filter rows before grouping and HAVING to filter groups after, for example
HAVING COUNT(*) > 1.Practice
List each department name with its number of employees.
SELECT d.dept_name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.dept_name;