An interview query is a small specification. Before typing, state what one output row represents, which records qualify, and how ties or missing values behave. These fifteen exercises share a tiny company dataset. Amounts are integer units, dates use sortable ISO format, and identifiers settle otherwise ambiguous ordering. Try each prompt before reading its solution.
Use SQLite 3.25 or later, with window functions enabled. Paste the seed once into the SQL playground, or save it as seed.sql and load it into an empty database. Exercises 1–14 preserve the rows; exercise 15 rolls its changes back. Expected blocks are ordered comma-separated results with column names; NULL means SQL NULL, not a string. SQLite's expression reference documents its missing-value operators.
Shared runnable seed
PRAGMA foreign_keys = ON;
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER REFERENCES departments(id),
salary INTEGER CHECK (salary >= 0),
manager_id INTEGER REFERENCES employees(id)
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
employee_id INTEGER NOT NULL REFERENCES employees(id),
sold_on TEXT NOT NULL,
amount INTEGER NOT NULL CHECK (amount > 0)
);
INSERT INTO departments VALUES
(1,'Engineering'), (2,'Sales'), (3,'Support');
INSERT INTO employees VALUES
(1,'Ada',1,120,NULL), (2,'Ben',1,100,1),
(3,'Cy',1,100,1), (4,'Dee',2,90,NULL),
(5,'Eli',2,70,4), (6,'Flo',NULL,NULL,NULL);
INSERT INTO sales VALUES
(1,4,'2026-09-01',40), (2,4,'2026-09-02',60),
(3,5,'2026-09-02',30), (4,5,'2026-09-03',70),
(5,2,'2026-09-03',20);
The empty Support department, unassigned Flo, tied salaries, and same-day sales are deliberate. A solution that works only after deleting these cases misses the point. Keep the seed unchanged while comparing answers, and resist adding a DISTINCT merely to hide an accidental join multiplication.
1. Filter with a stable order
Find employees earning at least 100, highest salary first.
SELECT name, salary FROM employees
WHERE salary >= 100 ORDER BY salary DESC, id;
name,salary
Ada,120
Ben,100
Cy,100
The identifier makes the Ben/Cy tie reproducible. Filtering precedes sorting; Flo does not satisfy the predicate because an unknown salary cannot establish the comparison. Ask whether the threshold is inclusive.
2. Locate unknown salaries
Return the names whose salary is missing.
SELECT name FROM employees WHERE salary IS NULL ORDER BY id;
name
Flo
Use IS NULL, not equality with NULL. Unknown is distinct from zero, so replacing every missing salary with zero changes the dataset's meaning.
3. Preserve empty departments
Count employees for every department, including Support.
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name ORDER BY d.id;
name,headcount
Engineering,3
Sales,2
Support,0
Count a non-null employee key. COUNT(*) counts the placeholder row produced for Support and incorrectly reports one. Flo belongs to no department and therefore does not appear in these departmental counts.
4. Filter the right side without losing rows
For each department count employees earning at least 100.
SELECT d.name, COUNT(e.id) AS high_earners
FROM departments d LEFT JOIN employees e
ON e.department_id = d.id AND e.salary >= 100
GROUP BY d.id, d.name ORDER BY d.id;
name,high_earners
Engineering,3
Sales,0
Support,0
The salary condition belongs in ON because zero-count departments must survive. Putting it in WHERE rejects their null-extended rows.
5. Distinguish row count from known-value count
Report total employees, known salaries, and average known salary.
SELECT COUNT(*) AS people, COUNT(salary) AS known,
ROUND(AVG(salary), 1) AS average_salary FROM employees;
people,known,average_salary
6,5,96.0
The denominator for the average is five, not six. State that assumption before presenting the figure. AVG(COALESCE(salary,0)) answers a different question: it assigns a real zero to the missing observation.
6. Find the second distinct salary
Return one scalar value for the second salary level globally.
SELECT MAX(salary) AS second_salary FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
second_salary
100
The inner maximum removes the top level; the outer maximum selects the next level. Tied employees do not consume extra ranks. If there is only one distinct known salary, the result is one row containing NULL. ORDER BY salary DESC LIMIT 1 OFFSET 1 counts rows and can fail when first place is tied.
7. Rank salary levels within departments
Keep all employees at either of the top two distinct salary levels.
WITH ranked AS (
SELECT id, department_id, name, salary,
DENSE_RANK() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS level
FROM employees WHERE salary IS NOT NULL
)
SELECT department_id, name, level FROM ranked
WHERE level <= 2 ORDER BY department_id, salary DESC, id;
department_id,name,level
1,Ada,1
1,Ben,2
1,Cy,2
2,Dee,1
2,Eli,2
DENSE_RANK preserves ties without gaps. ROW_NUMBER would instead cap people, and RANK can skip subsequent positions. Window results need the outer filtering query. These distinctions and frame behavior are specified in SQLite's window documentation.
8. Aggregate before filtering groups
Find employees whose total sales reach 100.
SELECT e.name, SUM(s.amount) AS total
FROM employees e JOIN sales s ON s.employee_id = e.id
GROUP BY e.id, e.name HAVING SUM(s.amount) >= 100 ORDER BY e.id;
name,total
Dee,100
Eli,100
HAVING tests a completed group; a row-level amount filter would discard qualifying contributions. Ben's single sale stays below the threshold. Group by identity as well as display name, since two employees could share a name.
9. Find employees without sales
Return employees with no matching sale.
SELECT e.name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM sales s WHERE s.employee_id = e.id)
ORDER BY e.id;
name
Ada
Cy
Flo
Existence expresses absence directly without producing duplicate outer rows. A NOT IN alternative becomes dangerous if its subquery can contain NULL: comparisons become unknown. This seed disallows null sale owners, but an interview follow-up may relax that constraint.
10. Show a manager relationship
List each employee beside their manager, preserving top-level staff.
SELECT e.name AS employee, m.name AS manager
FROM employees e LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;
employee,manager
Ada,NULL
Ben,Ada
Cy,Ada
Dee,NULL
Eli,Dee
Flo,NULL
Aliases distinguish two roles of one table. A self-join does not require recursive SQL for a single hop; recursion becomes relevant when traversing an arbitrary-depth reporting chain.
11. Compute daily running revenue
Aggregate each date first, then accumulate the daily totals.
WITH daily AS (
SELECT sold_on, SUM(amount) AS revenue FROM sales GROUP BY sold_on
)
SELECT sold_on, revenue,
SUM(revenue) OVER (ORDER BY sold_on
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM daily ORDER BY sold_on;
sold_on,revenue,running
2026-09-01,40,40
2026-09-02,90,130
2026-09-03,90,220
The daily CTE defines the output grain. Applying the window directly to sales would produce five rows instead of three. The explicit frame describes accumulation clearly; it also avoids depending on default peer handling.
12. Measure the previous-sale difference
For each sale compare its amount with that employee's previous sale.
SELECT employee_id, id, amount,
amount - LAG(amount) OVER (
PARTITION BY employee_id ORDER BY sold_on, id
) AS change
FROM sales ORDER BY employee_id, sold_on, id;
employee_id,id,amount,change
2,5,20,NULL
4,1,40,NULL
4,2,60,20
5,3,30,NULL
5,4,70,40
A partition resets history for each employee. The first difference stays unknown because there is no earlier observation. Replacing it with zero would suggest a real unchanged amount. Ordering by date and identifier makes the definition of “previous” stable even after another sale arrives on an existing date.
13. Compare against a department average
Find employees earning above their department's known-salary average.
SELECT e.name FROM employees e
WHERE e.salary > (
SELECT AVG(peer.salary) FROM employees peer
WHERE peer.department_id = e.department_id
) ORDER BY e.id;
name
Ada
Dee
The subquery correlates on department identity. Engineering averages about 106.67; Sales averages 80. Flo cannot pass because the department comparison and salary are unknown. Clarify whether unassigned employees should form a separate group before altering null equality semantics. A window average would be another valid implementation.
14. Design and inspect an index
Fetch Eli's sales from September 2 onward, in date order.
CREATE INDEX sales_owner_date ON sales(employee_id, sold_on, id);
SELECT id, amount FROM sales
WHERE employee_id = 5 AND sold_on >= '2026-09-02'
ORDER BY sold_on, id;
id,amount
3,30
4,70
Equality on the leading column followed by a date range suits this access pattern. Inspect rather than promise a speedup:
EXPLAIN QUERY PLAN SELECT id, amount FROM sales
WHERE employee_id = 5 AND sold_on >= '2026-09-02'
ORDER BY sold_on, id;
Expected plan property: a search using sales_owner_date; exact diagnostic wording varies. Tiny tables are poor benchmarks. An index also consumes space and adds write maintenance. SQLite documents the syntax in CREATE INDEX.
15. Transfer salary units atomically
Demonstrate moving ten units from Ada to Ben, then cancel it.
BEGIN IMMEDIATE;
UPDATE employees SET salary = salary - 10 WHERE id = 1;
UPDATE employees SET salary = salary + 10 WHERE id = 2;
SELECT name, salary FROM employees WHERE id IN (1,2) ORDER BY id;
ROLLBACK;
SELECT name, salary FROM employees WHERE id IN (1,2) ORDER BY id;
Before rollback: name,salary
Ada,110
Ben,110
After rollback: name,salary
Ada,120
Ben,100
Both changes share a transaction. In an application verify affected-row counts, catch errors, and explicitly roll back; a failed statement does not guarantee every earlier statement was undone. SQLite allows one writer at a time, so BEGIN IMMEDIATE can fail busy. See transaction semantics.
Next practice
Explain each query without reading it, then change one assumption: ties, missing salaries, empty groups, or concurrent writers. Continue with database fundamentals, databases and SQL, hands-on SQL practice, and system design fundamentals.
Continue in the SDE preparation track with core CS interview answers, library system design, parking lot design.