Mixed Deck — All SQL Topics Flashcards

Which TCL-related command sets the isolation level for a transaction in standard SQL?

Answer: SET TRANSACTION

SET TRANSACTION ISOLATION LEVEL configures how a transaction interacts with others.

A developer needs to query an `Employees` table to find all employees who are part of a specific manager's organizational hierarchy (i.e., their direct and indirect reports). Which type of CTE is best suited for this task?

Answer: A recursive CTE

Recursive CTEs are specifically designed to handle hierarchical or graph-like data structures, such as organizational charts or parts explosions. A recursive CTE starts with a base case (the 'anchor member', e.g., the manager) and then iteratively references itself to traverse the hierarchy (the 'recursive member', e.g., finding employees who report to the people already in the result set) until the entire hierarchy is returned.

Which TCL command is used to release a savepoint without rolling back?

Answer: RELEASE SAVEPOINT

RELEASE SAVEPOINT removes a savepoint so it can no longer be used as a rollback target.

Which comparison is INVALID for matching NULLs in a join condition?

Answer: column = NULL

NULL is never equal to anything, so '= NULL' never matches; use IS NULL.

Which combination is valid: SELECT dept, MAX(salary) FROM emp GROUP BY dept ORDER BY MAX(salary) DESC?

Answer: Valid: orders departments by their max salary

ORDER BY can reference aggregate expressions to sort grouped output.

Which query returns rows where discount is NULL or zero?

Answer: WHERE discount IS NULL OR discount = 0

You must test IS NULL separately and combine it with the zero check using OR.

Why can a CTE improve maintainability of a complex aggregation query?

Answer: It breaks the logic into named, sequential steps

CTEs let you decompose complex logic into named steps that are easier to read and maintain.

What does LEAD(value, 1, 0) return when no following row exists?

Answer: 0

The third argument supplies a default (0 here) when the offset row does not exist.

In many relational database management systems, which of the following actions will typically cause an implicit `COMMIT`, automatically ending any active transaction?

Answer: Running a Data Definition Language (DDL) statement, such as `ALTER TABLE`.

Data Definition Language (DDL) statements (like CREATE, ALTER, DROP) modify the database's structure. In most RDBMS (like Oracle and MySQL), executing a DDL statement will cause an implicit COMMIT of the preceding transaction before the DDL statement runs.

What does COUNT return when applied to an empty table?

Answer: 0

COUNT returns 0 on an empty set, unlike SUM or AVG which return NULL.

Can ORDER BY use an expression like ORDER BY price * quantity?

Answer: Yes, expressions are allowed

ORDER BY accepts arbitrary expressions, not just plain columns.

How does case sensitivity in WHERE string comparisons typically behave?

Answer: It depends on the column's collation settings

Whether comparisons are case-sensitive is governed by the collation of the column or database.

Which operator checks whether a column value falls within an inclusive range of two values?

Answer: BETWEEN

BETWEEN tests an inclusive range, so BETWEEN 10 AND 20 includes both 10 and 20.

Why might a DELETE fail due to a foreign key?

Answer: Child rows still reference the row being deleted

A DELETE can fail if other rows reference it and ON DELETE behavior is restrictive.

When sorting strings of numbers stored as text, what happens?

Answer: They sort lexicographically (e.g., '10' before '2')

Text columns sort character-by-character, so '10' comes before '2'.

What does the COALESCE function return?

Answer: The first non-NULL value in a list

COALESCE returns the first non-NULL value among its arguments.

Which set of rows does the difference between a LEFT JOIN and an INNER JOIN consist of?

Answer: Left-table rows with no match in the right table

A LEFT JOIN adds the unmatched left rows that an INNER JOIN would drop.

Which statement creates a point within a transaction you can later roll back to?

Answer: SAVEPOINT

SAVEPOINT defines a named marker within a transaction for partial rollback.

What is index fragmentation?

Answer: Disorder in index pages caused by ongoing insertions, updates, and deletes

Index fragmentation occurs when frequent data modifications cause index pages to fall out of logical order, increasing I/O and degrading query performance.

Why are table aliases especially useful in multi-table joins?

Answer: They shorten references and disambiguate same-named columns

Aliases make queries readable and resolve ambiguity when columns share names.