SQL

Both tables in scope expose a column named id, so the unqualified reference is ambiguous and the statement is rejected before it runs. Qualify it as orders.id or customers.id, or give each table an alias and select o.id.

Simple CASE compares one expression for equality against a list of values (CASE status WHEN 'N' THEN ...). Searched CASE takes an independent boolean predicate per WHEN, so it can test ranges, several columns, or IS NULL.

Yes. Standard SQL precedence is NOT, then AND, then OR, so a OR b AND c means a OR (b AND c). The classic bug is an OR list of values silently escaping a filter that was meant to apply to all of them.

DISTINCT applies to the whole select list, not to one column. SELECT DISTINCT city, state returns every unique city-and-state combination, so one city appears repeatedly if it occurs with several states.

BETWEEN is inclusive at both ends: x BETWEEN 10 AND 20 means x >= 10 AND x <= 20, so 10 and 20 both match. The lower bound must be written first, or the predicate matches nothing.

They disappear from the result. NULL = NULL evaluates to UNKNOWN rather than TRUE, and a join keeps only pairs whose predicate is TRUE, so a NULL key matches nothing — not even a NULL key on the other side.

For an INNER JOIN, ON and WHERE are logically equivalent: a row must satisfy both predicates to survive either way. For outer joins they are not equivalent, because ON is evaluated before NULL-extension and WHERE after it.

Filter with a semi-join instead of joining: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). An inner join emits one output row per matching order, so a customer with five orders appears five times.

2,000 rows. A CROSS JOIN returns the Cartesian product: every row of the left table paired with every row of the right, with no join predicate. The output size is N times M, never N plus M.

FULL OUTER JOIN returns every matched pair plus every unmatched row from both tables. An unmatched left row gets NULLs in all right-side columns, and an unmatched right row gets NULLs in all left-side columns.

COUNT accepts any expression, or *, and returns an integer count. SUM and AVG require numeric arguments and return numeric results. MIN and MAX accept any orderable type — numbers, text, dates — and return a value of that same type.

Put a CASE inside the aggregate. SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) contributes 1 for each matching row and 0 for the rest, so one GROUP BY query returns a separate count per condition.

COUNT() returns 10 and COUNT(city) returns 7. COUNT() counts rows; COUNT(expression) counts only the rows where that expression is not NULL, so the three NULL cities are skipped.

GROUP BY partitions the rows that survive FROM and WHERE into groups sharing the same grouping-key values, then emits exactly one row per group. The result has as many rows as there are distinct key combinations.

WHERE filters individual rows before grouping; HAVING filters whole groups after aggregation. Only HAVING may reference aggregate results such as COUNT(*) or SUM(amount), because those values exist only once rows have been collapsed into groups.

One value per outer row: the inner SELECT is evaluated with that customer's id substituted, so each row shows that customer's own order count — and 0, not NULL, for a customer with no orders.

All three name a subresult. A derived table is a subquery written inline in FROM and used once there; a CTE is named in a WITH clause and visible only to that one statement; a view is a stored schema object any statement can query.

EXISTS is TRUE when its subquery returns at least one row and FALSE when it returns none. It is two-valued and never UNKNOWN. The subquery's select list is irrelevant; only whether rows come back matters.

A scalar subquery must produce exactly one column and at most one row, so it can stand where a single value is expected. Returning two or more rows is a cardinality error raised while the statement runs, not a syntax error caught beforehand.

A subquery can sit in the SELECT list as one computed value per row, in FROM as a derived table that must be aliased, and in WHERE or HAVING as an operand of a predicate.

Existing rows get NULL unless the new column declares a DEFAULT, in which case every existing row is populated with that default value. Adding a NOT NULL column with no DEFAULT to a table that already holds rows is rejected.

Any rule covering more than one column — a composite PRIMARY KEY, UNIQUE or FOREIGN KEY, or a CHECK comparing two columns — must be written as a table constraint after the column list. Single-column rules may be written either way.

DEFAULT names the value the engine stores when an INSERT supplies none for that column. The expression is evaluated at insert time, so each row records its own insertion moment rather than one value frozen when the table was created.

FLOAT and REAL store binary approximations, so a value such as 0.10 is never held exactly and the error accumulates across sums and multiplications. Money needs an exact type: DECIMAL/NUMERIC with a declared precision and scale.

Standard SQL uses an identity column: id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY. The engine supplies each value from an attached sequence generator, whose START WITH, INCREMENT BY, CYCLE and MINVALUE/MAXVALUE options you set in parentheses.

Because the types must match. Comparing a character column to a numeric literal is not a valid comparison in standard SQL: engines either reject it or silently convert every row's stored value, which throws away the index on that column.

OFFSET is scan-and-discard. The engine still produces the first 200,000 rows in ORDER BY sequence and throws them away before emitting 20, so the work grows with the offset and deep pages get steadily slower.

The index on id already holds rows in id order, so the engine walks it and stops after ten entries. Nothing indexes last_name, so every row must be read and sorted before the first ten are known.

SELECT * projects every column, so the engine reads and ships bytes the caller discards, gives up access paths a narrower projection would allow, and returns a result whose shape silently changes when the table changes.

A loop pays a network round trip, a parse and a statement execution per row, and often a commit per row. One set-based UPDATE pays all of that once and lets the engine work through the whole row set in bulk.

Exactly one row per distinct email value: the one with the newest created_at. ROW_NUMBER numbers rows 1, 2, 3 inside each email partition in the given order, so number 1 is that partition's first row.

It returns 0. LAG reads the value one row back in the window's ordering; the first row has no predecessor, so the third argument (the default) is substituted. Omit that argument and you get NULL.

PARTITION BY splits the rows into independent groups so the function restarts in each one; ORDER BY sequences the rows inside a partition so position-dependent functions and frames have a defined order. Both parts are optional.

ROW_NUMBER() numbers every row 1, 2, 3 with no repeats. RANK() gives tied rows the same number and then skips ahead, leaving a gap. DENSE_RANK() also gives ties the same number but continues with the next integer, no gap.

Rank each customer's orders in a CTE with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC), then select rows WHERE rn = 1 in an outer query. That keeps the whole row, not just the maximum date.