- Read each concept section (visual diagrams included)
- Try each practice problem by writing your answer
- Paste your answer to Claude and say "check my answer for Problem X"
- Move to the next section once you feel comfortable
SELECT = WHAT columns you want to see
FROM = WHICH table to look in
Think of it like:
"Show me [these columns] from [this table]"
TABLE: employees
+----+----------+-----------+--------+---------------+
| id | name | department| salary | hire_date |
+----+----------+-----------+--------+---------------+
| 1 | Alice | Sales | 60000 | 2020-01-15 |
| 2 | Bob | Engineer | 85000 | 2019-06-01 |
| 3 | Charlie | Sales | 55000 | 2021-03-20 |
| 4 | Diana | Engineer | 90000 | 2018-11-10 |
| 5 | Eve | Marketing | 50000 | 2022-07-01 |
| 6 | Frank | Engineer | 78000 | 2020-09-15 |
| 7 | Grace | Marketing | 52000 | 2021-01-10 |
| 8 | Hank | Sales | 62000 | 2019-04-22 |
+----+----------+-----------+--------+---------------+
-- Get everything
SELECT * FROM employees;
-- Get just names and salaries
SELECT name, salary FROM employees;
-- Get just department names
SELECT department FROM employees;Problem 1.1: Write a query to get all columns from the employees table.
Problem 1.2: Write a query to get only the name and department of all employees.
Problem 1.3: Write a query to get the name, salary, and hire_date of all employees.
WHERE = a FILTER. Only rows that match the condition come through.
Think of it like a bouncer at a door:
"You can only come in IF you meet this condition"
BEFORE WHERE: AFTER WHERE salary > 60000:
+----------+--------+ +----------+--------+
| Alice | 60000 | | Bob | 85000 |
| Bob | 85000 | ====> | Diana | 90000 |
| Charlie | 55000 | | Frank | 78000 |
| Diana | 90000 | | Hank | 62000 |
| Eve | 50000 | +----------+--------+
| Frank | 78000 |
| Grace | 52000 |
| Hank | 62000 |
+----------+--------+
= equals WHERE department = 'Sales'
!= or <> not equal WHERE department != 'Sales'
> greater than WHERE salary > 60000
< less than WHERE salary < 60000
>= greater or equal WHERE salary >= 60000
<= less or equal WHERE salary <= 60000
AND both must be true WHERE salary > 60000 AND department = 'Sales'
OR either can be true WHERE department = 'Sales' OR department = 'Marketing'
IN matches a list WHERE department IN ('Sales', 'Marketing')
BETWEEN range (inclusive) WHERE salary BETWEEN 50000 AND 70000
LIKE pattern match WHERE name LIKE 'A%' (starts with A)
IS NULL checks for empty WHERE hire_date IS NULL
-- Employees in Sales
SELECT * FROM employees WHERE department = 'Sales';
-- Engineers making over 80k
SELECT name, salary FROM employees
WHERE department = 'Engineer' AND salary > 80000;
-- Hired in 2020 or later
SELECT name, hire_date FROM employees
WHERE hire_date >= '2020-01-01';Problem 2.1: Get the names of all employees in the Marketing department.
Problem 2.2: Get all employees with a salary between 55000 and 80000.
Problem 2.3: Get the name and salary of employees who are in Engineering OR have a salary greater than 60000.
Problem 2.4: Get all employees whose name starts with the letter 'E' or 'F'.
Problem 2.5: Get all employees hired after 2020-01-01 who are NOT in Sales.
ASC = ascending (A-Z, 1-100, oldest-newest) [DEFAULT]
DESC = descending (Z-A, 100-1, newest-oldest)
ORDER BY salary ASC: ORDER BY salary DESC:
+----------+--------+ +----------+--------+
| Eve | 50000 | | Diana | 90000 |
| Grace | 52000 | | Bob | 85000 |
| Charlie | 55000 | | Frank | 78000 |
| Alice | 60000 | | Hank | 62000 |
| Hank | 62000 | | Alice | 60000 |
| Frank | 78000 | | Charlie | 55000 |
| Bob | 85000 | | Grace | 52000 |
| Diana | 90000 | | Eve | 50000 |
+----------+--------+ +----------+--------+
SELECT department FROM employees; SELECT DISTINCT department FROM employees;
+-----------+ +-----------+
| Sales | | Sales |
| Engineer | | Engineer |
| Sales | ====> removes dupes | Marketing |
| Engineer | +-----------+
| Marketing |
| Engineer |
| Marketing |
| Sales |
+-----------+
Problem 3.1: Get all employees sorted by salary from highest to lowest.
Problem 3.2: Get all unique departments from the employees table.
Problem 3.3: Get the name and hire_date of employees in Sales, sorted by hire_date (earliest first).
Problem 3.4: Get the top 3 highest-paid employees (name and salary). Hint: use LIMIT
COUNT() = how many rows? COUNT(*) = count all rows
SUM() = add up all values SUM(salary) = total of all salaries
AVG() = average value AVG(salary) = average salary
MIN() = smallest value MIN(salary) = lowest salary
MAX() = largest value MAX(salary) = highest salary
Example on our employees table:
+-------------------------------------------+
| COUNT(*) = 8 (8 employees) |
| SUM(salary) = 532000 |
| AVG(salary) = 66500 |
| MIN(salary) = 50000 (Eve) |
| MAX(salary) = 90000 (Diana) |
+-------------------------------------------+
-- How many employees total?
SELECT COUNT(*) FROM employees;
-- Average salary in Engineering?
SELECT AVG(salary) FROM employees WHERE department = 'Engineer';
-- Highest salary?
SELECT MAX(salary) FROM employees;Problem 4.1: How many employees are in the Sales department?
Problem 4.2: What is the total salary paid across all employees?
Problem 4.3: What is the average salary of employees hired after 2020-01-01?
Problem 4.4: What is the difference between the highest and lowest salary?
Think of GROUP BY like sorting items into buckets, then counting each bucket.
Raw Data: GROUP BY department:
+----------+-----------+ +-----------+-------+-----------+
| Alice | Sales | | dept | count | avg_sal |
| Bob | Engineer | +-----------+-------+-----------+
| Charlie | Sales | ====> | Sales | 3 | 59000 |
| Diana | Engineer | | Engineer | 3 | 84333 |
| Eve | Marketing | | Marketing | 2 | 51000 |
| Frank | Engineer | +-----------+-------+-----------+
| Grace | Marketing |
| Hank | Sales |
+----------+-----------+
WHERE filters ROWS (before grouping)
HAVING filters GROUPS (after grouping)
-- "Give me departments where average salary is over 55000"
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 55000;
Result:
+-----------+-----------+
| Sales | 59000 | (passes - 59000 > 55000)
| Engineer | 84333 | (passes - 84333 > 55000)
+-----------+-----------+
Marketing excluded (51000 < 55000)
You WRITE it: SQL EXECUTES it:
SELECT (1st) FROM (1st) -- pick the table
FROM (2nd) WHERE (2nd) -- filter rows
WHERE (3rd) GROUP BY (3rd) -- make groups
GROUP BY (4th) HAVING (4th) -- filter groups
HAVING (5th) SELECT (5th) -- pick columns
ORDER BY (6th) ORDER BY (6th) -- sort results
-- Count of employees per department
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department;
-- Departments with more than 2 employees
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;
-- Average salary by department, only for depts averaging over 60k
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;Problem 5.1: Get the count of employees in each department.
Problem 5.2: Get the average salary for each department.
Problem 5.3: Get departments that have more than 2 employees.
Problem 5.4: Get the department with the highest average salary. Hint: ORDER BY + LIMIT
Problem 5.5: For each department, get the count of employees hired after 2020-01-01. Only show departments with at least 2 such employees.
TABLE: departments
+----+-----------+----------+---------+
| id | dept_name | manager | budget |
+----+-----------+----------+---------+
| 1 | Sales | Sarah | 200000 |
| 2 | Engineer | Tom | 500000 |
| 3 | Marketing | Uma | 150000 |
| 4 | HR | Victor | 100000 |
+----+-----------+----------+---------+
TABLE: orders
+----+-------------+--------+------------+
| id | employee_id | amount | order_date |
+----+-------------+--------+------------+
| 1 | 1 | 5000 | 2023-01-15 |
| 2 | 1 | 3000 | 2023-02-20 |
| 3 | 3 | 7000 | 2023-01-30 |
| 4 | 8 | 2000 | 2023-03-10 |
| 5 | 3 | 4500 | 2023-04-05 |
| 6 | 99 | 1000 | 2023-05-01 |
+----+-------------+--------+------------+
(Note: employee_id 99 does NOT exist in employees table)
INNER JOIN - only matching rows from BOTH tables
+-----------+ +-----------+
| Table A | | Table B |
| +---+-----+---+ | |
| | |XXXXX| | | |
| +---+-----+---+ | |
+-----------+ +-----------+
^^^^^^^^^
only this part
LEFT JOIN - ALL rows from left table + matches from right
+-----------+ +-----------+
| Table A | | Table B |
| XXXXXXXXX+-----+---+ | |
| XXXXXXXXX|XXXXX| | | |
| XXXXXXXXX+-----+---+ | |
+-----------+ +-----------+
^^^^^^^^^^^^^^^^^^^^
all of this
RIGHT JOIN - matches from left + ALL rows from right table
+-----------+ +-----------+
| Table A | | Table B |
| +---+-----+XXXXXXXXXX| |
| | |XXXXX|XXXXXXXXXX| |
| +---+-----+XXXXXXXXXX| |
+-----------+ +-----------+
^^^^^^^^^^^^^^^^^^^^
all of this
FULL OUTER JOIN - ALL rows from BOTH tables
+-----------+ +-----------+
| Table A | | Table B |
| XXXXXXXXX+-----+XXXXXXXXXX| |
| XXXXXXXXX|XXXXX|XXXXXXXXXX| |
| XXXXXXXXX+-----+XXXXXXXXXX| |
+-----------+ +-----------+
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
everything
-- INNER JOIN: only employees in departments that exist in departments table
SELECT e.name, d.dept_name, d.manager
FROM employees e
INNER JOIN departments d ON e.department = d.dept_name;
-- LEFT JOIN: ALL employees, even if their department isn't in departments table
SELECT e.name, d.manager
FROM employees e
LEFT JOIN departments d ON e.department = d.dept_name;
-- RIGHT JOIN: ALL departments, even if no employees are in them
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department = d.dept_name; INNER JOIN employees e ON orders o WHERE e.id = o.employee_id:
employees: orders: result:
id=1 Alice <---> employee_id=1 ==> Alice | 5000
id=1 Alice <---> employee_id=1 ==> Alice | 3000
id=3 Charlie <---> employee_id=3 ==> Charlie| 7000
id=3 Charlie <---> employee_id=3 ==> Charlie| 4500
id=8 Hank <---> employee_id=8 ==> Hank | 2000
employee_id=99 has NO match --> excluded from INNER JOIN
Bob, Diana, Eve, Frank, Grace have no orders --> excluded
Problem 6.1: Write a query to get each employee's name along with their department's manager. Use employees and departments tables.
Problem 6.2: Get all employees and their order amounts. Include employees who have no orders (show NULL for amount).
Problem 6.3: Get all departments and the count of employees in each, including departments with zero employees (like HR).
Problem 6.4: Get the total order amount per employee name. Only include employees who have placed orders.
Problem 6.5: Get each department's name, manager, budget, and the average salary of employees in that department.
A query INSIDE another query. The inner query runs first.
Think of it like:
"First, figure out X... then use X to answer the real question"
Types:
1. In WHERE clause - filter based on another query's result
2. In FROM clause - treat a query result as a temporary table
3. In SELECT clause - calculate a value for each row
-- Find employees who earn more than the average salary
-- Step 1: What's the average? --> 66500
-- Step 2: Who earns more than that?
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Find employees in departments with budget over 200000
SELECT name, department
FROM employees
WHERE department IN (
SELECT dept_name FROM departments WHERE budget > 200000
);
-- Get each employee's salary vs department average
SELECT name, salary,
(SELECT AVG(salary) FROM employees e2
WHERE e2.department = e1.department) as dept_avg
FROM employees e1;Problem 7.1: Find all employees who earn more than the average salary.
Problem 7.2: Find the employee(s) with the highest salary (use a subquery, not ORDER BY/LIMIT).
Problem 7.3: Find employees whose department has a budget greater than 150000.
Problem 7.4: For each employee, show their name, salary, and how much more/less they earn compared to their department's average.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
Like a series of if/elif/else checks:
if salary >= 80000: "High"
elif salary >= 60000: "Medium"
else: "Low"
-- Categorize employees by salary tier
SELECT name, salary,
CASE
WHEN salary >= 80000 THEN 'High'
WHEN salary >= 60000 THEN 'Medium'
ELSE 'Low'
END as salary_tier
FROM employees;
-- Result:
-- +----------+--------+-------------+
-- | Alice | 60000 | Medium |
-- | Bob | 85000 | High |
-- | Charlie | 55000 | Low |
-- | Diana | 90000 | High |
-- | Eve | 50000 | Low |
-- | Frank | 78000 | Medium |
-- | Grace | 52000 | Low |
-- | Hank | 62000 | Medium |
-- +----------+--------+-------------+
-- Count employees in each salary tier
SELECT
CASE
WHEN salary >= 80000 THEN 'High'
WHEN salary >= 60000 THEN 'Medium'
ELSE 'Low'
END as salary_tier,
COUNT(*) as emp_count
FROM employees
GROUP BY salary_tier;Problem 8.1: Label each employee as 'Senior' (hired before 2020) or 'Junior' (hired 2020 or later).
Problem 8.2: Create a report showing each department's name and a label: 'Large' if 3+ employees, 'Small' otherwise.
Problem 8.3: Calculate each department's bonus pool: Engineers get 10% of salary, Sales gets 15%, everyone else gets 5%. Show department and total bonus.
Regular aggregates collapse rows:
SELECT department, AVG(salary) --> 3 rows (one per dept)
GROUP BY department
Window functions keep ALL rows and add the calculation:
SELECT name, department,
AVG(salary) OVER(PARTITION BY department) --> 8 rows (all employees)
each with their dept avg
OVER() = "calculate across..."
PARTITION BY = "group these rows together" (like GROUP BY but keeps rows)
ORDER BY (in OVER) = "in this order" (needed for rankings and running totals)
GROUP BY department + AVG(salary): Window function AVG(salary) OVER(PARTITION BY department):
+-----------+--------+ +----------+-----------+--------+-----------+
| Sales | 59000 | | Alice | Sales | 60000 | 59000 |
| Engineer | 84333 | | Bob | Engineer | 85000 | 84333 |
| Marketing | 51000 | | Charlie | Sales | 55000 | 59000 |
+-----------+--------+ | Diana | Engineer | 90000 | 84333 |
3 rows, detail lost | Eve | Marketing | 50000 | 51000 |
| Frank | Engineer | 78000 | 84333 |
| Grace | Marketing | 52000 | 51000 |
| Hank | Sales | 62000 | 59000 |
+----------+-----------+--------+-----------+
8 rows, all detail preserved!
ROW_NUMBER() - assigns 1, 2, 3... (no ties)
RANK() - assigns 1, 2, 2, 4... (ties get same rank, skips next)
DENSE_RANK() - assigns 1, 2, 2, 3... (ties get same rank, no skip)
LAG() - value from previous row
LEAD() - value from next row
SUM() OVER() - running total
AVG() OVER() - running/group average
-- Rank employees by salary within each department
SELECT name, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;
-- Result:
-- +----------+-----------+--------+-----------+
-- | Diana | Engineer | 90000 | 1 |
-- | Bob | Engineer | 85000 | 2 |
-- | Frank | Engineer | 78000 | 3 |
-- | Hank | Sales | 62000 | 1 |
-- | Alice | Sales | 60000 | 2 |
-- | Charlie | Sales | 55000 | 3 |
-- | Grace | Marketing | 52000 | 1 |
-- | Eve | Marketing | 50000 | 2 |
-- +----------+-----------+--------+-----------+
-- Running total of salary (ordered by hire date)
SELECT name, hire_date, salary,
SUM(salary) OVER(ORDER BY hire_date) as running_total
FROM employees;Problem 9.1: Show each employee's name, salary, and their rank by salary across the whole company (highest = rank 1).
Problem 9.2: For each department, show the employee name, salary, and the department's average salary alongside each row.
Problem 9.3: Show each employee's name, salary, and the difference between their salary and the next highest-paid person. Hint: use LAG or LEAD
Problem 9.4: Get the highest-paid employee in each department using ROW_NUMBER. Hint: use a subquery with ROW_NUMBER
WITH cte_name AS (
-- query here
)
SELECT ... FROM cte_name;
Think of it like: "Let me calculate this first, give it a name,
then use it in my main query."
Benefits:
- Makes complex queries readable
- Can reference the same CTE multiple times
- Interviewers LOVE seeing CTEs (shows clean thinking)
-- Find employees earning above their department's average
WITH dept_avg AS (
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
)
SELECT e.name, e.salary, e.department, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;Problem 10.1: Using a CTE, find departments where the total salary exceeds 150000.
Problem 10.2: Using a CTE, find employees who earn more than their department's average salary.
Problem 10.3: Write a query using two CTEs: one for department employee counts, one for department average salaries. Join them to show departments with 3+ employees AND average salary over 60000.
These combine multiple concepts. This is what interviews actually look like.
Problem 11.1: "Get the second highest salary in each department." Hint: window function + subquery or CTE
Problem 11.2: "For each employee, show their name, salary, department, and what percentage of their department's total salary they represent." Hint: window function or subquery
Problem 11.3: "Find departments where every employee earns more than 50000." Hint: GROUP BY + HAVING with MIN
Problem 11.4: "Get the names of employees who have placed more orders than the average number of orders per employee." Hint: subquery + JOIN + GROUP BY + HAVING
Problem 11.5: "Show a summary: for each department, the employee count, average salary, highest salary, name of the highest earner, and the department budget." Hint: CTE or subquery + JOIN + window function
Problem 11.6: "Find pairs of employees in the same department where one earns at least 20% more than the other." Hint: self-join
Problem 11.7: "Get a running total of orders by date, and show the percentage each order contributes to the total." Hint: window function + subquery
QUERY STRUCTURE:
SELECT columns -- what to show
FROM table -- where to look
JOIN table ON condition -- combine tables
WHERE condition -- filter rows
GROUP BY column -- make groups
HAVING condition -- filter groups
ORDER BY column -- sort results
LIMIT n -- cap row count
JOINS:
INNER JOIN = only matches
LEFT JOIN = all left + matches right
RIGHT JOIN = all right + matches left
FULL JOIN = everything
AGGREGATES: COUNT SUM AVG MIN MAX
WINDOW: func() OVER (PARTITION BY x ORDER BY y)
Ranking: ROW_NUMBER, RANK, DENSE_RANK
Offset: LAG(col, n), LEAD(col, n)
CTE: WITH name AS (query) SELECT ... FROM name
CASE: CASE WHEN cond THEN val ELSE val END
NULL HANDLING:
IS NULL / IS NOT NULL
COALESCE(val1, val2) -- returns first non-null
NULLIF(val1, val2) -- returns NULL if equal
STRING FUNCTIONS:
UPPER(), LOWER(), LENGTH(), TRIM()
CONCAT(), SUBSTRING(), REPLACE()
DATE FUNCTIONS:
CURRENT_DATE, CURRENT_TIMESTAMP
EXTRACT(YEAR FROM date), DATE_DIFF, DATE_ADD
1. WHERE vs HAVING
WHERE filters ROWS (before GROUP BY)
HAVING filters GROUPS (after GROUP BY)
You CANNOT use aggregates in WHERE!
BAD: WHERE COUNT(*) > 5
GOOD: HAVING COUNT(*) > 5
2. GROUP BY requires listing all non-aggregate columns
BAD: SELECT name, department, COUNT(*)
GROUP BY department
GOOD: SELECT name, department, COUNT(*)
GROUP BY name, department
3. NULL behavior
NULL = NULL --> UNKNOWN (not TRUE!)
Use IS NULL, not = NULL
COUNT(*) counts NULLs, COUNT(column) does NOT
4. JOIN without ON = cartesian product (every row x every row)
Always specify your join condition!
5. DISTINCT vs GROUP BY
Both remove duplicates, but GROUP BY lets you aggregate.
Use DISTINCT for simple dedup, GROUP BY when you need counts/sums.
6. Aliases
Column aliases (AS) can be used in ORDER BY but NOT in WHERE/HAVING
Table aliases (FROM employees e) can be used everywhere after FROM
7. UNION vs UNION ALL
UNION removes duplicates (slower)
UNION ALL keeps all rows (faster)
READY TO PRACTICE? Start with Section 1 and work your way down. Paste your answers and say "check Problem X.X" and I'll review it!