SQL Interview Cheat Sheet With a Complete PostgreSQL Fixture

This SQL interview reference uses PostgreSQL 16 and a single small dataset. Run the setup, examples, and final ROLLBACK in the same disposable learning session. The tables are temporary. The goal is to connect syntax with row counts, NULL behavior, keys, and query meaning.

Start with a complete fixture

BEGIN;
CREATE TEMP TABLE cheat_departments (
  department_id integer PRIMARY KEY,
  department_name text NOT NULL
) ON COMMIT DROP;
CREATE TEMP TABLE cheat_employees (
  employee_id integer PRIMARY KEY,
  first_name text NOT NULL,
  department_id integer REFERENCES cheat_departments,
  salary integer CHECK (salary >= 0)
) ON COMMIT DROP;
INSERT INTO cheat_departments VALUES (1, 'Sales'), (2, 'Research'), (3, 'Support');
INSERT INTO cheat_employees VALUES
  (101, 'Ada', 1, 100), (102, 'Ben', 1, 80),
  (103, 'Cy', 2, NULL), (104, 'Dee', NULL, 90);

The fixture has three departments and four employees. Salary is an illustrative integer amount, and NULL means unknown. The foreign key allows an employee with no department because that column is nullable. A CHECK alone also permits NULL when its expression evaluates to unknown; use NOT NULL when missing values are forbidden.

Filter and order deterministically

SELECT employee_id, first_name, salary
FROM pg_temp.cheat_employees
WHERE salary >= 90
ORDER BY salary DESC, employee_id;

This returns Ada, 100, then Dee, 90, with their IDs. Cy is excluded because a comparison with unknown salary is not true. Use IS NULL to find missing salaries. Add a tie-breaker when stable ordering matters; a query without ORDER BY does not promise row order.

Distinguish WHERE from HAVING

SELECT department_id, COUNT(*) AS employees,
       COUNT(salary) AS known_salaries, AVG(salary) AS mean_known_salary
FROM pg_temp.cheat_employees
GROUP BY department_id
HAVING COUNT(*) >= 2
ORDER BY department_id;

Only department 1 remains: two employees, two known salaries, average 90. WHERE filters input rows before grouping; HAVING filters groups. COUNT(*) counts rows, while COUNT(salary) excludes NULL. An average over observed salaries is not proof that the whole department’s salary total is known.

Preserve unmatched departments correctly

SELECT d.department_name, COUNT(e.employee_id) AS known_paid_employees
FROM pg_temp.cheat_departments AS d
LEFT JOIN pg_temp.cheat_employees AS e
  ON e.department_id = d.department_id AND e.salary >= 90
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;

Sales returns 1; Research and Support each return 0. Keeping the salary condition in ON preserves departments without a match. Moving it to WHERE would remove those unmatched rows. COUNT(*) would count their placeholder rows and give the wrong zero-match count.

Use EXISTS for an existence question

SELECT d.department_name
FROM pg_temp.cheat_departments AS d
WHERE EXISTS (
  SELECT 1 FROM pg_temp.cheat_employees AS e
  WHERE e.department_id = d.department_id AND e.salary >= 90
)
ORDER BY d.department_id;

This returns Sales once. A join can produce one output per matching employee; EXISTS asks whether any qualifying employee exists. Choose the form that expresses the required result. There is no universal rule that joins outperform subqueries: inspect the plan and measure under representative data.

Retain rows while calculating group context

SELECT employee_id, department_id, salary,
       ROW_NUMBER() OVER (
         PARTITION BY department_id ORDER BY salary DESC NULLS LAST, employee_id
       ) AS position_in_department
FROM pg_temp.cheat_employees
ORDER BY department_id NULLS LAST, position_in_department;

Ada and Ben have positions 1 and 2 in Sales. Cy and Dee each have position 1 in their respective partitions; all NULL department values share one partition. ROW_NUMBER assigns unique positions. RANK or DENSE_RANK with the intended ordering is a different contract when ties should share a rank.

Explain design and performance with evidence

Normalization separates independently maintained entities and relationships to reduce update anomalies. For example, this fixture keeps a department’s name in its own keyed table. Merely naming a table “orders” does not demonstrate normalization. Denormalization can support a read workload but introduces duplicated facts and a synchronization responsibility; its value must be measured.

An index can help a selective predicate or ordering, but its presence does not guarantee use or improvement. Small tables may be cheaper to scan. Extra indexes cost writes and storage. Read EXPLAIN output and use EXPLAIN ANALYZE only when executing the statement is safe. A manually nested “filter early” subquery is not automatically faster than an equivalent query the optimizer can transform.

For PostgreSQL, adding a non-null constraint uses ALTER TABLE … ALTER COLUMN … SET NOT NULL, not the MySQL MODIFY syntax. Validate existing data and operational locking requirements before schema changes; this cheat sheet does not ask you to alter production tables.

Finish the session and vary the data

ROLLBACK;

Rerun the fixture with a tied salary, a second employee with no department, and another empty department. Predict the counts and ranks before querying. Consult PostgreSQL’s table-expression documentation and constraint documentation for the precise dialect behavior.