SQL Basics & Query Fundamentals

SELECT, WHERE, ORDER BY, LIMIT

SELECT name AS employee_name, salary * 12 AS annual_salary FROM employees;

SELECT * FROM employees WHERE department = 'Engineering' AND salary > 80000;
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;
SELECT * FROM employees WHERE department IN ('Engineering', 'Product', 'Design');
SELECT * FROM employees WHERE name LIKE 'A%';       -- starts with A
SELECT * FROM employees WHERE name ILIKE 'ann%';    -- case-insensitive (Postgres)

SELECT name, department, salary FROM employees ORDER BY department ASC, salary DESC;
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 10;   -- pagination, page 2

DISTINCT

SELECT DISTINCT department FROM employees;
SELECT COUNT(DISTINCT department) FROM employees;

NULL Handling

SELECT * FROM employees WHERE manager_id IS NULL;      -- never = NULL, always IS NULL
SELECT name, COALESCE(phone, email, 'no contact') AS contact FROM employees;
SELECT total_sales / NULLIF(total_orders, 0) AS avg_order_value FROM sales_summary;  -- divide-by-zero guard

SELECT NULL + 5;     -- NULL — any arithmetic with NULL is NULL
SELECT NULL = NULL;  -- NULL, not TRUE — this is why `= NULL` in WHERE never matches

Why NULL = NULL isn't TRUE: NULL represents "unknown," and the comparison of two unknowns is itself unknown — not a special-cased equality. This is the root cause behind the classic NOT IN + NULL trap (see [[SQL JOINs]]'s anti-join section).

CASE WHEN

SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 60000  THEN 'Mid'
    ELSE 'Junior'
  END AS level
FROM employees;

-- CASE inside aggregation — conditional counting
SELECT
  COUNT(CASE WHEN salary >= 100000 THEN 1 END) AS senior_count,
  COUNT(CASE WHEN salary < 60000  THEN 1 END) AS junior_count
FROM employees;

Arithmetic — the Integer Division Gotcha

SELECT 7 / 2;       -- 3   — integer division truncates (many DBs)
SELECT 7 / 2.0;     -- 3.5 — force decimal division
SELECT 7::FLOAT / 2; -- 3.5

SELECT ROUND(3.14159, 2);  -- 3.14
SELECT CEIL(3.1);          -- 4
SELECT FLOOR(3.9);         -- 3
SELECT GREATEST(10, 20, 15);  -- 20 — row-level max of args, not an aggregate

The Relational Division Pattern

"Find all X such that X relates to EVERY Y in a list" — a genuinely distinct query shape from a normal filter.

-- Users who ordered ALL of a required set of products
SELECT user_id
FROM orders
WHERE product_id IN (SELECT id FROM required_products)
GROUP BY user_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM required_products);

-- Students who passed ALL exams (no failures at all)
SELECT student_id FROM exam_results
GROUP BY student_id
HAVING SUM(CASE WHEN passed = FALSE THEN 1 ELSE 0 END) = 0;

The pattern: filter to the relevant rows, group, then compare a count against the total required count — matching means the group satisfies every required condition, not just at least one.

SQL Execution Order (Logical, Not Written)

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

WHERE runs before aggregation (can't reference an aggregate); HAVING runs after. SELECT aliases aren't visible in WHERE/GROUP BY because those clauses logically execute before SELECT assigns the alias.

Interview Drills

1. Why does WHERE COUNT(*) > 5 fail with a syntax/semantic error? — WHERE executes before GROUP BY/aggregation in logical execution order — no aggregate value exists yet at that point in the pipeline. Use HAVING, which runs after aggregation.
2. A query does SELECT AVG(score) FROM results and the score column has NULLs. What does the result reflect? — AVG (like all standard aggregates) silently skips NULLs — it computes the average of non-NULL values only, which is not the same as treating NULL as 0; use AVG(COALESCE(score, 0)) if zero-treatment is actually intended.

Cross-links

[[SQL JOINs]] · [[SQL Aggregates & GROUP BY]] · [[SQL Subqueries & CTEs]]