Which TCL command is used to define an intermediate point within a transaction to which updates can later be selectively rolled back?
SAVEPOINT creates intermediate markers within a transaction, allowing partial rollback using the syntax ROLLBACK TO savepoint_name.
Which type of JOIN returns all matching rows between two relations, plus non-matching rows from BOTH relations with NULL values in place of missing attributes?
A Full Outer Join combines the results of both Left and Right outer joins, ensuring no tuple from either participating relation is dropped.
What is the result of the SQL query: SELECT COUNT(DISTINCT dept_id) FROM Employee; if dept_id contains {10, 20, 10, NULL, 30, 20}?
The distinct non-null values of dept_id are 10, 20, and 30. Aggregate functions ignore NULL, so COUNT(DISTINCT dept_id) evaluates to 3.
Which SQL constraint ensures that all values in a column are strictly distinct from each other, but permits the presence of NULL values?
A UNIQUE constraint enforces uniqueness of non-null values while allowing NULL values (in standard SQL), whereas PRIMARY KEY strictly forbids NULLs.
In SQL-92 and later standards, which construct allows conditional logic similar to IF-THEN-ELSE within a SELECT query?
The ANSI SQL standard construct for conditional branch evaluation inside expressions is the CASE WHEN ... THEN ... ELSE ... END statement.