QA Engineer (Manual) interview prep

Short answer: WHERE filters rows, ORDER BY sorts the result, LIMIT n OFFSET m returns n rows, skipping the first m.

In depth:

-- Pagination: page 3 with 20 records each (skip 40, take 20)
SELECT id, name
FROM users
WHERE active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

⚠️ Gotcha: LIMIT without ORDER BY returns a NON-deterministic set of rows — the order isn't guaranteed. Also, OFFSET is slow at large values (the DBMS still reads and discards the skipped rows) — for deep pagination use keyset pagination:

-- keyset: faster than a large OFFSET
SELECT * FROM users WHERE id > :last_seen_id ORDER BY id LIMIT 20;

Short answer: DISTINCT removes duplicate rows from the result.

In depth:

SELECT DISTINCT department FROM employees;        -- unique departments
SELECT DISTINCT department, city FROM employees;  -- unique PAIRS (department, city)

⚠️ Gotcha: DISTINCT applies to ALL columns in the SELECT, not to a single one. SELECT DISTINCT a, b ≠ "unique a". Also, COUNT(DISTINCT col) counts unique values, ignoring NULL.

Short answer: INNER — matches only; LEFT — all left rows + matches on the right (otherwise NULL); RIGHT — the mirror of LEFT; FULL — everything from both sides; CROSS — Cartesian product; SELF — a table joined to itself.

In depth:

Suppose we have:

employees           departments
+----+--------+----+   +----+----------+
| id | name   |dept|   | id | title    |
+----+--------+----+   +----+----------+
| 1  | Anna   | 10 |   | 10 | IT       |
| 2  | Boris  | 20 |   | 30 | Finance  |
| 3  | Vera   | NULL|  +----+----------+
+----+--------+----+

INNER JOIN — the intersection (∩). Only rows where there's a match in BOTH tables:

SELECT e.name, d.title
FROM employees e
INNER JOIN departments d ON e.dept = d.id;
-- Anna | IT      (Boris dropped: dept 20 isn't in departments;
--                 Vera dropped: dept = NULL)

employees    departments
   [ ##### ]              <- only the intersection

LEFT JOIN — all rows of the left table + matches on the right (no match -> NULL):

SELECT e.name, d.title
FROM employees e
LEFT JOIN departments d ON e.dept = d.id;
-- Anna  | IT
-- Boris | NULL   <- kept, no department
-- Vera  | NULL   <- kept, dept = NULL

[ left ##### ]   <- all of the left + the intersection

RIGHT JOIN — the mirror of LEFT (all rows of the right):

SELECT e.name, d.title
FROM employees e
RIGHT JOIN departments d ON e.dept = d.id;
-- Anna | IT
-- NULL | Finance  <- department 30 has no employees

FULL OUTER JOIN — all rows of both tables:

SELECT e.name, d.title
FROM employees e
FULL OUTER JOIN departments d ON e.dept = d.id;
-- Anna  | IT
-- Boris | NULL
-- Vera  | NULL
-- NULL  | Finance

CROSS JOIN — the Cartesian product (each with each), with no condition:

SELECT e.name, d.title FROM employees e CROSS JOIN departments d;
-- 3 rows × 2 rows = 6 rows

SELF JOIN — a table to itself (e.g., employee -> manager):

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

💡 How LEFT differs from INNER, by example: if there's no match on the right, INNER DROPS the row, while LEFT KEEPS it, substituting NULL into the right-hand columns. That's why LEFT JOIN is often used to find "orphaned" records:

-- Employees without a department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept = d.id
WHERE d.id IS NULL;

⚠️ Gotcha: a condition on the right table in a LEFT JOIN must go in ON, not in WHERE, otherwise LEFT turns into INNER:

-- WRONG: the filter in WHERE cuts off rows with NULL on the right -> behaves like INNER
SELECT e.name, d.title FROM employees e
LEFT JOIN departments d ON e.dept = d.id
WHERE d.title = 'IT';

-- RIGHT: if you need to keep all rows on the left
SELECT e.name, d.title FROM employees e
LEFT JOIN departments d ON e.dept = d.id AND d.title = 'IT';

Short answer: GROUP BY groups rows by column values; aggregates (COUNT, SUM, AVG, MIN, MAX) compute one value per group.

In depth:

SELECT department,
       COUNT(*)        AS headcount,
       SUM(salary)     AS payroll,
       AVG(salary)     AS avg_salary,
       MIN(salary)     AS min_salary,
       MAX(salary)     AS max_salary
FROM employees
GROUP BY department;

⚠️ Gotcha: in standard SQL, the SELECT can only use columns from GROUP BY or aggregates. SELECT name, dept, COUNT(*) ... GROUP BY dept is an error (name is neither grouped nor aggregated). PostgreSQL forbids it; MySQL (in non-strict mode) silently returns an arbitrary name.

Short answer: COUNT(*) — all rows; COUNT(col) — rows where col is NOT NULL; COUNT(DISTINCT col) — unique non-NULL values.

In depth:

-- Table: 5 rows, 2 NULLs in phone, 1 duplicate
SELECT
  COUNT(*)               AS total,        -- 5
  COUNT(phone)           AS with_phone,   -- 3 (NULLs not counted)
  COUNT(DISTINCT phone)  AS unique_phones -- 2
FROM users;

⚠️ Gotcha: COUNT(col) silently ignores NULL — a common cause of discrepancies in reports. If you need to count all rows, always use COUNT(*).