Core SQL Querying — Interview Notes

1

Logical Query Execution Order

SQL reads top to bottom, but the engine does not run in that order. This is the #1 "gotcha" in interviews — know it cold.

FROM→ WHERE→ GROUP BY→ HAVING→ SELECT→ ORDER BY→ LIMIT

FROM / JOIN — build the working table(s) first.
WHERE — filter raw rows. Can't use SELECT aliases here yet.
GROUP BY — bucket rows into groups.
HAVING — filter the groups (works on aggregates).
SELECT — pick / compute the output columns. Aliases are born here.
ORDER BY — sort the final result. Can use SELECT aliases.
LIMIT / OFFSET — trim the row count, last of all.

Why WHERE can't filter on an aggregate: aggregates don't exist yet at that stage → use HAVING instead.

-- written order vs execution order example
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 50000        -- runs 2nd (row filter)
GROUP BY department        -- runs 3rd
HAVING COUNT(*) > 5          -- runs 4th (group filter)
ORDER BY emp_count DESC  -- runs 6th
LIMIT 10;                   -- runs last

2

DDL vs DML vs DCL vs TCL

Four command families — asked constantly as a quick warm-up question.

Family	Stands for	Commands	Purpose
DDL	Data Definition Language	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`	Defines / changes structure (tables, schemas). Auto-commits.
DML	Data Manipulation Language	`SELECT`, `INSERT`, `UPDATE`, `DELETE`	Reads / changes the data inside tables.
DCL	Data Control Language	`GRANT`, `REVOKE`	Controls access & permissions.
TCL	Transaction Control Language	`COMMIT`, `ROLLBACK`, `SAVEPOINT`	Manages the lifecycle of a transaction.

Watch out: DELETE is DML (row-by-row, logged, can rollback). TRUNCATE is DDL (resets the whole table, minimal logging, usually can't rollback). Classic trap question.

3

Constraints

Rules enforced on columns to protect data integrity.

Primary Key

Uniquely identifies each row. Implies UNIQUE + NOT NULL. One per table.

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50)
);

Foreign Key

Links a column to another table's primary key. Enforces referential integrity.

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  FOREIGN KEY (student_id)
    REFERENCES students(student_id)
);

Unique

No duplicate values, but NULLs are allowed (usually more than one, depending on engine).

ALTER TABLE students
ADD CONSTRAINT uq_email UNIQUE (email);

Check

Validates values against a boolean condition before insert/update.

ALTER TABLE students
ADD CONSTRAINT chk_age CHECK (age >= 18);

Not Null

Column must always have a value — the simplest, most common constraint.

ALTER TABLE students
ALTER COLUMN name SET NOT NULL;

4

Essential Functions

String

-- SUBSTR(string, start, length)
SELECT SUBSTR('analyst', 1, 3);   -- 'ana'

-- CONCAT joins strings
SELECT CONCAT(first_name,' ',last_name)
AS full_name FROM employees;

-- LENGTH: char count
SELECT LENGTH(email) FROM users;

Date

-- DATEDIFF(end, start) → days between
SELECT DATEDIFF(order_date, ship_date);

-- DATE_ADD: shift a date forward
SELECT DATE_ADD(order_date, INTERVAL 7 DAY);

-- DATE_TRUNC: round down to a unit
SELECT DATE_TRUNC('month', order_date)
AS order_month FROM orders;

NULL Handling

-- COALESCE: first non-null in list
SELECT COALESCE(phone, email, 'N/A');

-- NULLIF: NULL if two values equal
SELECT NULLIF(budget_used, 0);

-- NVL (Oracle only, 2-arg COALESCE)
SELECT NVL(commission, 0) FROM sales;

Tip: NULLIF(a,b) is handy to avoid divide-by-zero: total / NULLIF(count, 0) returns NULL instead of erroring.

5

Joins

The most-tested topic. Think of two tables, A (left) and B (right).

Inner Join

Only rows that match in both tables.

SELECT a.*, b.*
FROM orders a
INNER JOIN customers b
  ON a.cust_id = b.cust_id;

Edge case: NULL on join key never matches anything (NULL ≠ NULL).

Left (Outer) Join

All of A, plus matches from B. Unmatched B columns come back NULL.

SELECT a.*, b.*
FROM orders a
LEFT JOIN customers b
  ON a.cust_id = b.cust_id;

Use case: find rows in A with no match in B → add WHERE b.cust_id IS NULL.

Right (Outer) Join

All of B, plus matches from A. Mirror image of LEFT JOIN.

SELECT a.*, b.*
FROM orders a
RIGHT JOIN customers b
  ON a.cust_id = b.cust_id;

Rarely used in practice — most people just flip the tables and write LEFT JOIN.

Full Outer Join

Everything from both sides. Unmatched rows padded with NULL on the other side.

SELECT a.*, b.*
FROM orders a
FULL OUTER JOIN customers b
  ON a.cust_id = b.cust_id;

MySQL has no native FULL OUTER JOIN — simulate with LEFT JOIN UNION RIGHT JOIN.

Cross Join

Cartesian product — every row of A paired with every row of B. No ON clause.

SELECT a.size, b.color
FROM sizes a
CROSS JOIN colors b;

Edge case: rows explode fast — 100 × 100 = 10,000 rows. Rarely intentional in interviews unless generating combinations.

Self Join

A table joined to itself, using two aliases — for hierarchies or comparisons within one table.

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

Must alias the table twice — SQL can't tell the two copies apart otherwise.

6

Aggregations & GROUP BY vs HAVING

Aggregate functions: COUNT, SUM, AVG, MIN, MAX. They collapse many rows into one value per group.

SELECT department,
       COUNT(*)      AS headcount,
       AVG(salary)   AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000
ORDER BY avg_salary DESC;

GROUP BY

Splits rows into buckets sharing the same value(s). Every non-aggregated SELECT column must appear here.

HAVING

Filters the groups after aggregation — the "WHERE for aggregates." Can't use HAVING without something to group or aggregate.

Rule of thumb: filter rows → WHERE. Filter groups/aggregates → HAVING.

7

Set Operations

Combine results of two queries with the same number & type of columns.

Operation	Behavior
`UNION`	Combines rows from both queries, removes duplicates (extra sort/dedup cost).
`UNION ALL`	Combines rows, keeps duplicates — faster, preferred when you know sets don't overlap or duplicates are fine.
`INTERSECT`	Only rows present in both result sets.
`EXCEPT` / `MINUS`	Rows in the first query not present in the second (order matters!).

SELECT customer_id FROM web_orders
UNION ALL
SELECT customer_id FROM store_orders;

-- customers who ordered online AND in-store
SELECT customer_id FROM web_orders
INTERSECT
SELECT customer_id FROM store_orders;

-- online-only customers
SELECT customer_id FROM web_orders
EXCEPT
SELECT customer_id FROM store_orders;

Interview favorite: "Why would you pick UNION ALL over UNION?" → performance, since UNION forces a duplicate-removal pass.

8

Subqueries

Scalar Subquery

Returns exactly one value (one row, one column). Usable anywhere a single value is expected.

SELECT name, salary,
  (SELECT AVG(salary) FROM employees)
    AS company_avg
FROM employees;

Multi-row Subquery

Returns a list of values — pair with IN, ANY, ALL.

SELECT name FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments
  WHERE location = 'NYC'
);

Correlated Subquery

References the outer query's row — re-evaluated once per outer row (can be slow at scale).

SELECT e.name, e.salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

Tell-tale sign of correlated: the inner query mentions a column from the outer table (e.department_id above) — it can't run standalone.