Advanced SQL Questions and Answers

Answer: A

Correct answer: Hard-coded SQL statements in a program language such as Java.

Embedded SQL is written into the source of a host program and fixed at compile time. It contrasts with dynamic SQL, which the program builds as a string while running - more flexible, but unable to be checked in advance and open to injection if the string is built from input.

Answer: A

Correct answer: True

A subquery may itself contain a subquery, to whatever depth the problem requires and the product permits. Depth is limited in practice by readability rather than by the language, and a deeply nested query is often clearer rewritten with joins or with a common table expression.

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: 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: C

Correct answer: Outer join

An outer join keeps the rows that have no match on the other side and fills the missing columns with nulls. Equi-joins and natural joins discard them, so a customer with no orders would simply disappear - which is precisely the case an outer join exists to report.

Answer: A

Correct answer: True

Each pair of tables joined needs a condition linking them, so joining n tables requires at least n minus one conditions. Omitting one leaves that pair unconstrained and produces a Cartesian product with the rest, which is why a query that suddenly returns an enormous result usually has a missing join condition.

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

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: D

Correct answer: Cartesian join

Two tables are named in the FROM clause and no join condition is given, so every row of one is paired with every row of the other. The result has the product of the two row counts, which is almost never wanted - an accidental Cartesian product from a forgotten WHERE clause is the classic runaway query.

Answer: A

Correct answer: A way to establish an IF-THEN-ELSE in SQL.

CASE evaluates conditions in order and yields the value belonging to the first that holds, which is a conditional expression rather than a statement. It can therefore appear anywhere a value is expected - in a select list, in an ORDER BY, or inside an aggregate.