Computer Science: Advanced SQL

What is a scalar subquery in SQL?

A scalar subquery is a subquery that returns exactly one value (one row and one column), which can be used in expressions where a single value is expected.

Which keyword is used to define a Common Table Expression (CTE)?

The WITH keyword is used to define a CTE, allowing for a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

What is the result of a CROSS JOIN between two tables?

A CROSS JOIN produces a Cartesian product, where every row from the first table is combined with every row from the second table.

In the context of transactions, what does the COMMIT command do?

The COMMIT command permanently saves all changes made during the current transaction to the database, making them visible to other users.

What is a database View?

A View is a virtual table based on the result-set of an SQL statement; it does not store data physically but provides a way to simplify complex queries.

What is the primary purpose of the EXPLAIN statement in SQL optimization?

The EXPLAIN statement is used to show the execution plan of a query, detailing how the database engine intends to join tables and use indexes.

Explain the difference between the RANK() and DENSE_RANK() window functions.

RANK() leaves gaps in the numbering sequence when there are ties (e.g., 1, 2, 2, 4), whereas DENSE_RANK() does not leave gaps (e.g., 1, 2, 2, 3).

How do the LEAD() and LAG() window functions differ in their operation?

LEAD() provides access to a row at a specified physical offset after the current row, while LAG() provides access to a row at a specified physical offset before the current row.

What characterizes a correlated subquery?

A correlated subquery is a subquery that references one or more columns from the outer query, meaning it must be executed repeatedly for each row processed by the outer query.

What is a recursive CTE and what are its two main components?

A recursive CTE is a CTE that references itself to handle hierarchical data. It consists of an 'anchor member' (base query) and a 'recursive member' joined by a UNION ALL.