SQL

[Core SQL | Beginner] What does it mean that SQL is declarative?

You describe the result you want, and the database optimizer chooses how to produce it.

[Core SQL | Beginner] What is the logical processing order of a SELECT query?

Conceptually it is FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then row limiting. Optimizers may execute it differently.

[Core SQL | Beginner] What does DISTINCT do?

It removes duplicate result rows based on all selected expressions, usually with sorting or hashing work.

[Core SQL | Beginner] When can you use a SELECT alias in the same query?

Usually in ORDER BY, but generally not in WHERE because WHERE is logically evaluated earlier. GROUP BY and HAVING alias rules vary by database.

[Core SQL | Beginner] What is CASE in SQL?

CASE is a conditional expression that returns a value, similar to if-then-else, and it can appear in SELECT, ORDER BY, aggregates, and other expressions.

[Core SQL | Beginner] What is the difference between CHAR and VARCHAR?

CHAR is fixed length and may pad values; VARCHAR stores variable-length text. Exact padding and comparison behavior varies by database.

[Core SQL | Beginner] What is the purpose of ORDER BY?

It is the only general way to request a deterministic result order. Without it, row order is not guaranteed.

[Core SQL | Beginner] Is ascending order the default for ORDER BY?

Yes, ASC is the usual default, but NULL placement differs by database and should be specified when it matters.

[Core SQL | Beginner] What is a scalar function?

A scalar function returns one value for each input row, such as LOWER, ABS, or a date function.

[Core SQL | Beginner] What is the difference between DDL and DML?

DDL defines objects, such as CREATE and ALTER; DML reads or changes data, such as SELECT, INSERT, UPDATE, and DELETE.

[Core SQL | Beginner] What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes chosen rows, TRUNCATE quickly removes all rows, and DROP removes the object. Logging, rollback, identity reset, and trigger behavior vary by database.

[Core SQL | Beginner] Why should you avoid SELECT star in production queries?

It reads unnecessary columns, makes interfaces fragile when schemas change, and can prevent narrower index-only plans.

[Filtering and Aggregation | Beginner] What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation.

[Filtering and Aggregation | Beginner] Can HAVING be used without GROUP BY?

Often yes: the whole filtered input is treated as one group, though the exact accepted syntax varies by database.

[Filtering and Aggregation | Beginner] Why can an aggregate not usually appear in WHERE?

WHERE runs before grouping, so the aggregate value does not exist yet. Use HAVING or an outer query.

[Filtering and Aggregation | Beginner] What does GROUP BY do?

It forms groups of rows with equal grouping values so aggregates can return one result per group.

[Filtering and Aggregation | Beginner] What must be true of selected columns in a grouped query?

Each selected expression should be aggregated, grouped, or functionally dependent on grouped columns where the database supports that rule.

[Filtering and Aggregation | Beginner] What happens when an aggregate query has no GROUP BY?

The filtered rows form one group, so the query returns one aggregate row even when the input is empty.

[Filtering and Aggregation | Beginner] What does SUM return for an empty input?

Standard SQL SUM returns NULL for no input rows, while COUNT returns zero. Use COALESCE if you need zero.

[Filtering and Aggregation | Beginner] How does AVG handle NULL values?

AVG ignores NULL inputs and divides by the count of non-NULL values, not by all rows.

[Filtering and Aggregation | Beginner] Do MIN and MAX ignore NULL values?

Yes, they ignore NULL inputs and return NULL only when no non-NULL value is available.

[Filtering and Aggregation | Beginner] What is conditional aggregation?

It puts a CASE or dialect-specific filter inside an aggregate, such as SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END).

[Filtering and Aggregation | Beginner] What is the FILTER clause on an aggregate?

It applies a condition to one aggregate, such as COUNT(*) FILTER (WHERE active). PostgreSQL supports it; SQL Server and MySQL commonly use CASE instead.

[Filtering and Aggregation | Beginner] What does ROLLUP produce?

It adds hierarchical subtotals and a grand total to ordinary GROUP BY results. Syntax details vary by database.

[Filtering and Aggregation | Beginner] What does CUBE produce?

It returns aggregates for every combination of the listed grouping dimensions, which can grow quickly.

[Filtering and Aggregation | Beginner] What are GROUPING SETS?

They let one query request several specific grouping combinations instead of combining separate aggregate queries.

[Filtering and Aggregation | Beginner] Why can grouping by a high-cardinality column be expensive?

It can create nearly one group per row, requiring substantial memory, sorting, or hashing.

[Filtering and Aggregation | Beginner] How do you filter groups with more than five rows?

Group the rows and use HAVING COUNT(*) > 5.

[NULL and Counting | Beginner] What is the difference between COUNT star and COUNT of a column?

COUNT(*) counts rows; COUNT(column) counts rows where that column is not NULL.

[NULL and Counting | Beginner] What does COUNT DISTINCT do?

It counts distinct non-NULL values of an expression. Multi-column syntax and NULL treatment for tuples vary by database.

[NULL and Counting | Beginner] Does COUNT of 1 differ from COUNT star?

Normally no meaningful difference: both count rows, and optimizers treat them similarly. COUNT(*) states the intent most clearly.

[NULL and Counting | Beginner] What does NULL represent?

It represents missing, unknown, or inapplicable information; it is not zero or an empty string, except Oracle treats empty strings as NULL.

[NULL and Counting | Beginner] Why does column equals NULL not work?

Comparisons with NULL evaluate to UNKNOWN, so use IS NULL or IS NOT NULL.

[NULL and Counting | Beginner] What is SQL three-valued logic?

Predicates can be TRUE, FALSE, or UNKNOWN. WHERE and HAVING keep only TRUE rows.

[NULL and Counting | Beginner] What happens to NOT UNKNOWN?

It remains UNKNOWN, which is why simply negating a NULL-related predicate may still exclude the row.

[NULL and Counting | Beginner] Why is NOT IN dangerous when the subquery can return NULL?

One NULL can make the comparison UNKNOWN for every unmatched row. NOT EXISTS is usually the safer anti-join.

[NULL and Counting | Beginner] Do NULL values join to each other with equals?

No. NULL = NULL is UNKNOWN, so ordinary equality joins do not match them unless you add explicit NULL-safe logic.

[NULL and Counting | Beginner] How do CHECK constraints treat UNKNOWN?

Under standard behavior, a CHECK rejects FALSE but allows TRUE or UNKNOWN, so add NOT NULL when missing values must also be rejected.

[NULL and Counting | Beginner] Can a UNIQUE constraint contain multiple NULL values?

It depends on the database and index options. PostgreSQL and MySQL usually allow multiple NULLs, while SQL Server's ordinary unique constraint usually allows one.

[NULL and Counting | Beginner] How are NULLs ordered?

Defaults vary: PostgreSQL normally puts NULLs last for ASC, while MySQL and SQL Server usually put them first. Some databases support NULLS FIRST or NULLS LAST.

[NULL and Counting | Beginner] What is IS DISTINCT FROM?

It is a NULL-safe comparison: two NULLs are not distinct, and one NULL versus a value is distinct. PostgreSQL supports it; other databases use different syntax or emulation.

[Joins | Intermediate] What does an INNER JOIN return?

Only row combinations that satisfy the join condition on both sides.

[Joins | Intermediate] What does a LEFT JOIN return?

Every left-side row plus matching right-side rows; unmatched right columns are filled with NULLs.

[Joins | Intermediate] What is the difference between LEFT JOIN and RIGHT JOIN?

They preserve opposite sides. A RIGHT JOIN can usually be rewritten as a LEFT JOIN by swapping table order.

[Joins | Intermediate] What does a FULL OUTER JOIN return?

All matches plus unmatched rows from both sides. MySQL and SQLite do not support it natively in all commonly deployed versions, so it may need a UNION-based rewrite.

[Joins | Intermediate] What does a CROSS JOIN return?

The Cartesian product: every left row paired with every right row.

[Joins | Intermediate] What is a self join?

It joins a table to itself using aliases, often for hierarchies, comparisons, or predecessor relationships.

[Joins | Intermediate] What is a non-equi join?

A join using a condition other than equality, such as a date falling within a range.

[Joins | Intermediate] Why can moving a right-table filter from ON to WHERE change a LEFT JOIN?

A WHERE filter can remove the NULL-extended unmatched rows, effectively turning the result into an inner join.

[Joins | Intermediate] What is join cardinality?

It describes how many rows on one side can match each row on the other, such as one-to-one, one-to-many, or many-to-many.

[Joins | Intermediate] Why does a one-to-many join repeat parent data?

Each matching child creates a separate result row, so the parent columns appear once per child.

[Joins | Intermediate] Why can a many-to-many join explode row counts?

If a key has m rows on one side and n on the other, that key can produce m times n result rows.

[Joins | Intermediate] How do you diagnose unexpected duplicate rows after a join?

Check whether the join keys are unique on each side and count matches per key before adding DISTINCT.

[Joins | Intermediate] What is a semi-join?

It returns left rows that have at least one match without returning or multiplying by right-side rows; EXISTS is the usual SQL expression.

[Joins | Intermediate] What is an anti-join?

It returns left rows with no match, commonly written with NOT EXISTS or a carefully written LEFT JOIN and IS NULL.

[Joins | Intermediate] What is the risk of NATURAL JOIN?

It silently joins every same-named column, so schema changes can alter results. Explicit join columns are safer.

[Joins | Intermediate] What does JOIN USING do?

It joins on same-named columns and usually exposes one copy of each join key. Support and output naming differ by database; SQL Server does not support USING.

[Set Operations | Intermediate] What is the difference between UNION and UNION ALL?

UNION removes duplicate rows; UNION ALL keeps them and is usually faster.

[Set Operations | Intermediate] What must set-operation queries have in common?

They need the same number of columns in the same order with compatible data types.

[Set Operations | Intermediate] Which SELECT supplies column names after a UNION?

The first SELECT normally supplies the output column names.

[Set Operations | Intermediate] What does INTERSECT return?

Rows present in both inputs, with duplicates removed unless the database supports and you request INTERSECT ALL.

[Set Operations | Intermediate] What does EXCEPT return?

Rows in the first input but not the second, normally with duplicates removed. Oracle traditionally calls this MINUS.

[Set Operations | Intermediate] Does MySQL support INTERSECT and EXCEPT?

Current MySQL 8 releases support them, but older MySQL versions did not, so confirm the deployed version.

[Set Operations | Intermediate] Where should ORDER BY appear in a set query?

Usually once at the end for the combined result; ordering individual branches requires parentheses and is often useful only with row limiting.

[Set Operations | Intermediate] How do NULLs behave during duplicate removal in set operations?

For duplicate elimination, rows with NULLs in corresponding positions are generally treated as duplicates rather than compared with ordinary equals logic.

[Keys, Constraints, and Normalization | Intermediate] What is a primary key?

A primary key uniquely identifies each row and is both UNIQUE and NOT NULL. A table has at most one primary key, which may contain several columns.

[Keys, Constraints, and Normalization | Intermediate] What is a candidate key?

Any minimal set of columns that can uniquely identify a row; one candidate is chosen as the primary key.

[Keys, Constraints, and Normalization | Intermediate] What is a surrogate key?

A generated identifier with no business meaning, such as an identity value or UUID.

[Keys, Constraints, and Normalization | Intermediate] What is a natural key?

A real business attribute or combination that already identifies the entity, such as a country code plus an account number.

[Keys, Constraints, and Normalization | Intermediate] What is a composite key?

A key made from multiple columns whose combination is unique.

[Keys, Constraints, and Normalization | Intermediate] What does a foreign key enforce?

It ensures each non-NULL child key references an existing parent key, unless a defined action handles the change.

[Keys, Constraints, and Normalization | Intermediate] What do ON DELETE CASCADE and SET NULL do?

CASCADE deletes matching child rows; SET NULL clears their foreign-key values and requires nullable columns.

[Keys, Constraints, and Normalization | Intermediate] Why index foreign-key columns?

It can speed joins and parent deletes or updates; many databases do not automatically create that child-side index.

[Keys, Constraints, and Normalization | Intermediate] What is a CHECK constraint?

It enforces a row-level predicate, though support and enforcement history vary by database and version.

[Keys, Constraints, and Normalization | Intermediate] What is first normal form?

Columns hold atomic values for the model, rows are identifiable, and repeating groups are moved out of the row.

[Keys, Constraints, and Normalization | Intermediate] What is second normal form?

It is in first normal form and every non-key attribute depends on the whole candidate key, not part of a composite key.

[Keys, Constraints, and Normalization | Intermediate] What is third normal form?

It is in second normal form and non-key attributes do not depend transitively on a key through another non-key attribute.

[Keys, Constraints, and Normalization | Intermediate] What is BCNF?

Every determinant must be a candidate key; it is stricter than third normal form for certain overlapping-key cases.

[Keys, Constraints, and Normalization | Intermediate] When is denormalization reasonable?

When measured read performance or simplicity justifies controlled duplication and the system can reliably maintain consistency.

[Subqueries and CTEs | Intermediate] What is a scalar subquery?

A subquery used where one value is expected; it must return at most one row and one column.

[Subqueries and CTEs | Intermediate] What is a correlated subquery?

A subquery that refers to columns from the outer query, conceptually evaluating per outer row even if the optimizer rewrites it.

[Subqueries and CTEs | Intermediate] What is the difference between EXISTS and IN?

EXISTS tests whether any matching row exists; IN compares a value to a set. NULL behavior differs, especially for NOT IN.

[Subqueries and CTEs | Intermediate] Why can EXISTS be efficient?

The database can stop looking after the first match, and optimizers often transform it into a semi-join.

[Subqueries and CTEs | Intermediate] When can a subquery in FROM be useful?

It creates a derived table that can pre-aggregate, reshape, or isolate a logical step before the outer query.

[Subqueries and CTEs | Intermediate] What is a common table expression?

A CTE is a named query expression scoped to one statement, introduced with WITH.

[Subqueries and CTEs | Intermediate] Does a CTE always improve performance?

No. It mainly improves structure; databases may inline it, materialize it, or let hints and version-specific rules decide.

[Subqueries and CTEs | Intermediate] What is a recursive CTE?

A CTE with an anchor query and a recursive member that repeatedly builds rows until no new rows are produced or a limit is reached.

[Subqueries and CTEs | Intermediate] What are common uses for recursive CTEs?

Traversing hierarchies, expanding graphs carefully, generating sequences, and following parent-child paths.

[Subqueries and CTEs | Intermediate] What prevents an infinite recursive CTE?

A terminating predicate or naturally exhausted join, plus a database-specific recursion limit as a safety net.

[Subqueries and CTEs | Intermediate] What is a lateral join?

It lets a FROM item reference earlier FROM items. PostgreSQL uses LATERAL; SQL Server uses CROSS APPLY or OUTER APPLY.

[Subqueries and CTEs | Intermediate] When would you use CROSS APPLY in SQL Server?

Use it like a lateral inner join when a table-valued expression depends on each left row; OUTER APPLY preserves unmatched left rows.

[Subqueries and CTEs | Intermediate] Can a subquery return multiple columns to a scalar context?

No. A scalar context requires one column and at most one row; use a join, row constructor where supported, or separate expressions.

[Window Functions | Intermediate] What is a window function?

It calculates across related rows while preserving one result row per input row, unlike GROUP BY.

[Window Functions | Intermediate] What does the OVER clause define?

It defines the window's partitions, ordering, and optional frame for a window function.

[Window Functions | Intermediate] What does PARTITION BY do in a window function?

It restarts the calculation for each partition, similar to grouping without collapsing rows.

[Window Functions | Intermediate] Why is ORDER BY inside OVER different from the final ORDER BY?

The window ORDER BY defines calculation sequence; the final ORDER BY controls displayed row order.

[Window Functions | Intermediate] What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

ROW_NUMBER is unique, RANK ties and leaves gaps, and DENSE_RANK ties without gaps.

[Window Functions | Intermediate] Is ROW_NUMBER deterministic when its ordering has ties?

No. Add a unique tie-breaker to the window ORDER BY when stable numbering matters.

[Window Functions | Intermediate] How do you return the latest row per customer?

Assign ROW_NUMBER ordered by timestamp descending within each customer, then filter for row number one in an outer query or QUALIFY where supported.

[Window Functions | Intermediate] What does LAG do?

It returns a value from an earlier row in the window order without a self join.