Advanced SQL Questions and Answers

Answer: A

Correct answer: True

UNION takes the results of two or more queries and stacks them into one table, removing duplicate rows unless UNION ALL is used. The queries must be union compatible - the same number of columns, with corresponding types - since otherwise the rows could not be stacked at all.

Answer: A

Correct answer: True

ROLLBACK WORK undoes the transaction by applying the before-images from the log and then discards them, so the log entries for that transaction are emptied. The database returns to the state it held when the transaction began.

Answer: B

Correct answer: False

The most frequently used join is the natural join, which matches on the common column and returns it once rather than twice. The equi-join is the same operation with the duplicate column retained, and it is the natural join that is wanted in nearly every practical query.

Answer: B

Correct answer: False

Eliminating the duplicate column is what distinguishes the natural join from the equi-join. An outer join does something else entirely: it keeps the rows that have no match on the other side, filling their missing columns with nulls. The statement has attached one join’s definition to another’s name.

Answer: B

Correct answer: False

The dependence runs the other way. In a correlated subquery the inner query refers to a column of the outer, so it must be evaluated afresh for each row the outer query considers. That is what makes it correlated, and what makes it potentially expensive.

Answer: B

Correct answer: False

A function returns a single value and takes only input parameters, which is what allows it to be used within an expression like any built-in function. Output parameters belong to procedures, which is one of the two things distinguishing the two kinds of routine.

Answer: A

Correct answer: True

The requirement has two parts and both are needed. The queries must produce the same number of columns, and the corresponding columns must be of compatible types - which together is what union compatibility means. Without it the rows could not be stacked into one result at all.