Part 1: Query Logic & NULL Handling

1. The “Order of Execution” Trap

We write SQL linearly (starting with SELECT), but the engine executes it in a specific, non-linear sequence. If you understand this order, you will never be stumped by why an alias isn’t recognized or why a query is running slowly.

The Logical Execution Flow

1. FROM / JOIN: The engine identifies the data source and resolves physical joins (the most expensive part).
2. WHERE: Individual rows are filtered before any grouping or calculation happens.
3. GROUP BY: The remaining rows are collapsed into buckets based on specified criteria.
4. HAVING: Groups are filtered based on aggregate results (e.g., HAVING COUNT(*) > 5).
5. SELECT: The final expressions are computed, and column aliases are applied.
6. DISTINCT: Duplicates are stripped.
7. ORDER BY: The final set is sorted.
8. LIMIT / OFFSET: The result set is truncated to the requested size.

Interview Tip: When asked why you can’t use a column alias in a WHERE clause, the answer is: “Because the engine evaluates the WHERE clause at Step 2, but the alias isn’t defined until Step 5.”

---

2. Row Filtering: WHERE vs. HAVING

This is a high-yield concept because it forces you to think about performance.

Feature	WHERE	HAVING
Stage	Pre-aggregation (Step 2)	Post-aggregation (Step 4)
Operates On	Raw, individual rows	Calculated groups (SUM, AVG, etc.)
Performance	High (indexes can be used)	Lower (done after grouping)

Scenario: You have a table of Orders and you only want departments with a total revenue over $1M.

• Wrong: WHERE SUM(revenue) > 1000000 (The engine doesn’t know what SUM is yet).
• Correct: GROUP BY dept_id HAVING SUM(revenue) > 1000000.

---

3. Handling Data Logic: The CASE Statement

In interviews, don’t just use WHERE clauses for filtering; use CASE WHEN for dynamic bucketing within your SELECT. This is a professional way to pivot data without multiple queries.

SELECT 
    product_name,
    CASE 
        WHEN stock_count = 0 THEN 'Out of Stock'
        WHEN stock_count < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS status
FROM inventory;

---

4. The NULL Logic Trap

Interviewers love to test if you understand that NULL is not a value—it is an unknown state.

• Math: 10 + NULL = NULL. Any operation with a null result is null.
• Equality: WHERE salary = NULL will always return false, even for null rows. You must use IS NULL or IS NOT NULL.
• Functions: COUNT(*) counts total rows; COUNT(column_name) counts rows where that column is not null.

Essential Helper Functions

• COALESCE(col, 0): The industry standard for replacing NULL with a default value.
• IFNULL() / ISNULL(): Database-specific alternatives (MySQL vs SQL Server), but COALESCE is ANSI-SQL standard and works almost everywhere.

---

5. Aggregate Functions

Aggregates collapse many rows into one summary value. They always pair with GROUP BY (to define the groups) and HAVING (to filter the groups).

• COUNT(*) — counts all rows in a group (includes NULLs).
• COUNT(col) — counts only non-NULL values of col.
• SUM(col) — total (ignores NULLs; NULL if no non-null rows).
• AVG(col) — mean (ignores NULLs).
• MIN(col) / MAX(col) — extremes (ignore NULLs; work on strings too).

Gotcha: a SELECT with an aggregate but no GROUP BY treats the whole table as one group and returns a single row.

GROUP BY vs DISTINCT

Both collapse duplicates, but they do different jobs:

• DISTINCT — removes duplicate rows from the result; no aggregation, returns every column listed.
• GROUP BY — groups rows and allows aggregate functions on each group; typically you SELECT the group key + aggregates.

SELECT DISTINCT dept_id FROM employees;          -- just the unique depts
SELECT dept_id, COUNT(*) FROM employees
GROUP BY dept_id;                                -- count per dept

ORDER BY & Pagination

• ORDER BY col ASC|DESC — sorts the final result; can reference select aliases (unlike WHERE).
• Pagination — LIMIT n OFFSET m (MySQL/Postgres) or OFFSET m ROWS FETCH NEXT n ROWS ONLY (SQL Server), or TOP (SQL Server).
• Page k of size n → LIMIT n OFFSET (k-1)*n.

String & Date Functions (quick sheet)

• Strings: LENGTH(), UPPER()/LOWER(), SUBSTRING(), CONCAT(), TRIM(), REPLACE(), LIKE '%pattern%' (wildcards % = any, _ = one char).
• Dates: CURRENT_DATE, NOW(), EXTRACT(YEAR FROM date), DATE_ADD/DATE_SUB, DATEDIFF().
• LIKE vs =: = is exact equality (and works on indexes); LIKE does pattern matching. A leading % (LIKE '%abc') defeats the index.

---