SQLMentor
SQL Practice Exercises with Answers — on a Live HR Schema
30 hands-on SQL exercises with full solutions, split into beginner, intermediate, and advanced sets. Every task runs against the classic HR schema, and every solution has a one-click "run in editor" link that executes it live in SQLMentor's free in-browser SQL editor — no install, no signup.
The right way to use this page: open the editor in another tab, attempt each task yourself, then expand Show solution to compare. Several of the advanced exercises are classic interview questions — if that is your goal, pair this page with the interview question sets.
The HR schema you are querying
Seven tables, linked by foreign keys — the same sample schema Oracle uses in its documentation and certification material:
- employees — employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
- departments — department_id, department_name, manager_id, location_id
- jobs — job_id, job_title, min_salary, max_salary
- job_history — employee_id, start_date, end_date, job_id, department_id
- locations — location_id, street_address, postal_code, city, state_province, country_id
- countries — country_id, country_name, region_id
- regions — region_id, region_name
The editor runs SQLite, so the solutions use portable standard SQL;
where Oracle differs (date functions, FETCH FIRST), the
solution notes say so.
Beginner — 10 exercises
Single-table queries: SELECT, WHERE, ORDER BY, LIKE, NULL handling, and simple aggregates. If any of these feel unfamiliar, the basic SQL and filtering topics cover the theory.
Show the first name, last name, and salary of the first 10 employees.
Show solution
SELECT first_name, last_name, salary
FROM employees
LIMIT 10;A plain projection — pick only the columns you need instead of SELECT *.
Show the names of all employees who work in department 60 (IT).
Show solution
SELECT first_name, last_name
FROM employees
WHERE department_id = 60;WHERE filters rows before anything else happens to them.
List employees earning more than 10,000, highest paid first.
Show solution
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 10000
ORDER BY salary DESC;ORDER BY ... DESC sorts the filtered result, not the whole table.
Show every distinct job ID that appears in the employees table.
Show solution
SELECT DISTINCT job_id
FROM employees
ORDER BY job_id;DISTINCT collapses duplicate values after the rows are selected.
List employees hired on or after 1 January 1998, earliest first.
Show solution
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date >= '1998-01-01'
ORDER BY hire_date;ISO-formatted dates (YYYY-MM-DD) compare correctly as text — one reason the format is a standard.
Find every employee whose last name starts with the letter K.
Show solution
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'K%';% matches any run of characters; _ would match exactly one.
Count how many employees each department has, largest first.
Show solution
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
ORDER BY emp_count DESC;Every column in the SELECT that isn't aggregated must appear in GROUP BY.
List employees who have no commission (commission_pct is NULL).
Show solution
SELECT first_name, last_name, salary
FROM employees
WHERE commission_pct IS NULL;= NULL never matches — NULL comparisons need IS NULL / IS NOT NULL.
Show each employee's full name in one column called full_name.
Show solution
SELECT first_name || ' ' || last_name AS full_name, salary
FROM employees
ORDER BY full_name;|| is the standard SQL concatenation operator (Oracle, PostgreSQL, SQLite, DB2).
Show the company's minimum, maximum, and average salary (rounded).
Show solution
SELECT MIN(salary) AS min_sal,
MAX(salary) AS max_sal,
ROUND(AVG(salary), 2) AS avg_sal
FROM employees;Aggregates without GROUP BY collapse the whole table into one row — and they skip NULLs.
Intermediate — 10 exercises
Joins, GROUP BY with HAVING, subqueries, and the classic anti-join and self-join patterns. Backed by the joins and subqueries topics.
Show each employee's name together with their department name.
Show solution
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.last_name;An inner join drops employees with a NULL department_id — use LEFT JOIN to keep them.
Show each employee's name, job title, and the city they work in.
Show solution
SELECT e.first_name, e.last_name, j.job_title, l.city
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
ORDER BY l.city, e.last_name;Chain joins one relationship at a time: employee → job, employee → department → location.
List the departments that have more than 5 employees.
Show solution
SELECT d.department_name, COUNT(*) AS emp_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING COUNT(*) > 5
ORDER BY emp_count DESC;WHERE filters rows before grouping; HAVING filters the groups after.
List employees who earn more than the company-wide average salary.
Show solution
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;The scalar subquery runs once; its single value feeds the outer comparison.
Find the departments that currently have no employees.
Show solution
SELECT d.department_id, d.department_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.employee_id IS NULL
ORDER BY d.department_name;LEFT JOIN + IS NULL keeps only the rows with no match — the classic anti-join. NOT EXISTS does the same job.
Show each employee alongside their manager's full name.
Show solution
SELECT e.first_name || ' ' || e.last_name AS employee,
m.first_name || ' ' || m.last_name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
ORDER BY manager, employee;Joining a table to itself just needs two aliases. The president (no manager) disappears — a LEFT JOIN would keep them.
Show each job title's average salary, but only for jobs averaging above 8,000.
Show solution
SELECT j.job_title, ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
GROUP BY j.job_title
HAVING AVG(e.salary) > 8000
ORDER BY avg_salary DESC;You can aggregate joined columns like any other; HAVING can reference the aggregate directly.
List employees who appear in the job history table (they changed jobs at least once).
Show solution
SELECT e.first_name, e.last_name
FROM employees e
WHERE EXISTS (
SELECT 1 FROM job_history jh
WHERE jh.employee_id = e.employee_id
)
ORDER BY e.last_name;EXISTS stops at the first match — often faster than IN on large sets, and NULL-safe unlike NOT IN.
Find the highest-paid employee in each department.
Show solution
SELECT e.department_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id;The inner query re-runs per outer row, correlated on department_id. Window functions solve the same problem — see the advanced set.
Count how many employees work in each country.
Show solution
SELECT c.country_name, COUNT(e.employee_id) AS emp_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id = c.country_id
GROUP BY c.country_name
ORDER BY emp_count DESC;Long join chains are normal in normalised schemas — follow the foreign keys one hop at a time.
Advanced — 10 exercises
Interview territory: second-highest salary, top-N per group, running totals, and window functions. The window functions topic explains the machinery.
Find the second-highest salary in the company (the classic interview question).
Show solution
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);MAX below the MAX. Ties are handled naturally because both MAXes work on values, not rows.
Find the third-highest distinct salary using LIMIT/OFFSET.
Show solution
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;OFFSET n-1 skips the top n−1 distinct values. In Oracle you'd use FETCH FIRST ... ROWS ONLY with OFFSET.
Rank employees by salary within their department using a window function.
Show solution
SELECT department_id, first_name, last_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees
WHERE department_id IS NOT NULL
ORDER BY department_id, dept_rank;PARTITION BY restarts the ranking per department; RANK leaves gaps after ties, DENSE_RANK doesn't.
Show the top 3 earners in each department.
Show solution
SELECT department_id, first_name, last_name, salary
FROM (
SELECT department_id, first_name, last_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
WHERE department_id IS NOT NULL
)
WHERE rn <= 3
ORDER BY department_id, salary DESC;Window functions can't be filtered in WHERE directly — wrap them in a subquery (or CTE) and filter the alias.
Show a running total of salaries in hire-date order.
Show solution
SELECT first_name, last_name, hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date, employee_id) AS running_total
FROM employees
ORDER BY hire_date, employee_id;SUM with ORDER BY inside OVER accumulates row by row. The employee_id tie-breaker makes the order deterministic.
Show each employee's salary next to their department's average and the difference.
Show solution
SELECT first_name, last_name, department_id, salary,
ROUND(AVG(salary) OVER (PARTITION BY department_id), 2) AS dept_avg,
ROUND(salary - AVG(salary) OVER (PARTITION BY department_id), 2) AS diff
FROM employees
WHERE department_id IS NOT NULL
ORDER BY department_id, diff DESC;Unlike GROUP BY, a window aggregate keeps every row — perfect for row-vs-group comparisons.
Find the salary amounts that more than one employee earns.
Show solution
SELECT salary, COUNT(*) AS how_many
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1
ORDER BY how_many DESC, salary DESC;GROUP BY + HAVING COUNT(*) > 1 is the standard duplicate-detection pattern for any column.
Count how many employees were hired in each year.
Show solution
SELECT strftime('%Y', hire_date) AS hire_year, COUNT(*) AS hires
FROM employees
GROUP BY hire_year
ORDER BY hire_year;This editor runs SQLite, where strftime('%Y', ...) extracts the year; in Oracle you'd write EXTRACT(YEAR FROM hire_date) or TO_CHAR.
Show each department's total salary bill as a percentage of the company total.
Show solution
SELECT d.department_name,
SUM(e.salary) AS dept_total,
ROUND(100.0 * SUM(e.salary) / SUM(SUM(e.salary)) OVER (), 2) AS pct_of_company
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY pct_of_company DESC;A window function over an aggregate — SUM(SUM(...)) OVER () — turns group totals into shares of the grand total.
For each employee (by hire date), show the previous hire's salary and the difference.
Show solution
SELECT first_name, last_name, hire_date, salary,
LAG(salary) OVER (ORDER BY hire_date, employee_id) AS prev_salary,
salary - LAG(salary) OVER (ORDER BY hire_date, employee_id) AS diff
FROM employees
ORDER BY hire_date, employee_id;LAG reaches back one row in the window order; the first row has no predecessor, so it returns NULL.
Want the reasoning behind a pattern, not just the answer? The SQL articles cover the comparisons that trip people up most — IN vs EXISTS, WHERE vs HAVING, and NULL handling among them.
Want a blank canvas?
Open the free in-browser SQL editor and write your own queries against the same live HR schema — results in under a second, no signup.
Open the SQL editor →