SQL query round: joins, nth salary and duplicates

State what one output row represents before writing a join. These exercises use invented employee records and deliberately include ties, missing relationships and duplicate email values. Salary values are toy integers, not compensation estimates.

Fixture and basic joins

Run the setup once in a disposable SQLite database. Every SQL block below was executed against this fixture with SQLite 3.46.1 through Python 3.14.4's sqlite3; expected rows were checked. No external database or application was contacted.

CREATE TABLE department(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE employee(
  id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept_id INTEGER,
  salary INTEGER, email TEXT
);
INSERT INTO department VALUES (1,'QA'),(2,'Platform'),(3,'Support');
INSERT INTO employee VALUES
 (1,'Demo A',1,100,'[email protected]'),
 (2,'Demo B',1,100,'[email protected]'),
 (3,'Demo C',1,80,'[email protected]'),
 (4,'Demo D',2,70,NULL),
 (5,'Demo E',NULL,NULL,NULL);

1. What questions should you ask before solving a join?

Ask which side must be retained and whether the relationship is one-to-one or one-to-many. “List employees and departments” might exclude unassigned employees or require them with a missing department. For this fixture, an inner join yields four employees while a left join from employees yields five.

2. How do you return only employees with matching departments?

Join on the department ID, not the display name. A name can change or repeat; the ID expresses the fixture's relationship. The ordered result is Demo A/QA, Demo B/QA, Demo C/QA and Demo D/Platform.

SELECT e.name, d.name
FROM employee e JOIN department d ON d.id = e.dept_id
ORDER BY e.id;

3. How do you retain the unassigned employee?

Start from employees and left join departments. Demo E appears with a NULL department name. Filtering department fields in WHERE can remove that row, so consider whether a condition belongs in the match rule or in the final result filter.

SELECT e.name, d.name
FROM employee e LEFT JOIN department d ON d.id = e.dept_id
ORDER BY e.id;

4. How do you include departments with zero employees?

Start from departments and count a non-null employee key, not COUNT(*). The unmatched Support row still exists after the outer join, but its employee ID is NULL. Expected rows are QA/3, Platform/1 and Support/0.

SELECT d.name, COUNT(e.id) AS employees
FROM department d LEFT JOIN employee e ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY d.id;

Rankings and duplicates

5. How do you get the second distinct highest salary?

Exclude unknown salaries, deduplicate and sort descending before taking the second value. The result is 80, not 100: the two employees at 100 occupy the same salary rank. If fewer than two distinct known salaries exist, this query returns no row.

SELECT DISTINCT salary FROM employee
WHERE salary IS NOT NULL
ORDER BY salary DESC LIMIT 1 OFFSET 1;

6. How do you return everyone at the nth distinct salary?

Use DENSE_RANK on known salaries and filter in an outer query. For rank 1, both Demo A and Demo B must be returned; for rank 2 below, the result is Demo C/80. Validate n as a positive integer in the caller and bind it as a parameter when building an application query.

WITH ranked AS (
  SELECT id, name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employee WHERE salary IS NOT NULL
)
SELECT name, salary FROM ranked WHERE salary_rank = 2 ORDER BY id;

Likely follow-up: Why not ROW_NUMBER? It assigns separate positions to tied rows. RANK also leaves gaps after ties; DENSE_RANK matches distinct salary levels.

7. How do you find duplicate email values?

Define whether NULL, case and whitespace count before grouping. This exercise excludes NULL and compares stored values literally, returning [email protected] with count 2. It detects repeated email values, not duplicate employee rows; those are different definitions of a duplicate.

SELECT email, COUNT(*) AS occurrences
FROM employee WHERE email IS NOT NULL
GROUP BY email HAVING COUNT(*) > 1
ORDER BY email;

8. How do you return the employee rows behind those duplicates?

Use the grouped result as a filter and return the original records. Expected IDs are 1 and 3. Avoid deleting “duplicates” merely because this query finds them: the business may allow shared addresses, and deletion requires an approved survivor rule and dependency analysis.

SELECT id, name FROM employee
WHERE email IN (
  SELECT email FROM employee WHERE email IS NOT NULL
  GROUP BY email HAVING COUNT(*) > 1
)
ORDER BY id;

Test the query, not just its syntax

9. How do you find departments with no employees?

A correlated NOT EXISTS expresses absence of a matching employee and returns Support. This avoids relying on NOT IN against a subquery that can contain NULL: a NULL can make comparisons unknown and unexpectedly remove results. Include an empty child table when testing this query outside the interview fixture.

SELECT d.name FROM department d
WHERE NOT EXISTS (SELECT 1 FROM employee e WHERE e.dept_id = d.id)
ORDER BY d.id;

10. How do you find employees earning above their department average?

Calculate the department average separately, then compare each employee's salary with it. Demo A and Demo B are above QA's average; Demo D equals Platform's average and should not appear. Unknown salaries do not contribute to AVG; state whether that matches the reporting requirement.

SELECT e.name FROM employee e
JOIN (
  SELECT dept_id, AVG(salary) AS average_salary
  FROM employee GROUP BY dept_id
) a ON a.dept_id = e.dept_id
WHERE e.salary > a.average_salary
ORDER BY e.id;

11. How do you count high earners while keeping empty departments?

Put the salary threshold in the join condition so it controls which employees match, while all departments remain. At threshold 90, expected counts are QA/2, Platform/0 and Support/0. Moving that condition into WHERE would remove the zero-match departments in this fixture.

SELECT d.name, COUNT(e.id)
FROM department d
LEFT JOIN employee e ON e.dept_id = d.id AND e.salary >= 90
GROUP BY d.id, d.name ORDER BY d.id;

12. How do you validate a query before claiming it is correct?

Check exact rows, duplicates, order when promised, and behaviour with empty and tied inputs. A query that executes successfully can still multiply rows through an incorrect join. This page verifies SQLite results only: production engine behaviour, collation and performance plans need their own checks with representative data.

Practise it

Change the requested rank to 1 and then to 4: expect two employees and no employees respectively. Add a second Platform employee and predict the average before running the query. Explain why a query test needs expected rows rather than merely a “no SQL error” assertion.