Advanced SQL Questions and Answers

Answer: B

Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query while Uncorrelated subquery executes the subquery first and passes the value to the outer query.

Answer: A

Correct answer: True

This is the definition of atomicity. The set of updates either all take effect or none does, so the database moves from one consistent state to another and is never left part way - the transfer that debits one account and fails to credit the other being the standard illustration.

Answer: A

Correct answer: Involves the use of an inner and outer query.

A subquery is a SELECT nested inside another, the inner supplying a value or a set to the outer. Many subqueries can be rewritten as joins and return identical results, and the choice between them is usually a matter of readability rather than of what is possible.

Answer: D

Correct answer: All of the above

Tables can be brought together by joining them in the FROM or WHERE clause, by a subquery whose result the outer query uses, or by a set operation such as UNION. Each suits a different shape of question, and many queries can be written in more than one of them.

Answer: C

Correct answer: They include procedural and SQL statements.

A stored procedure combines SQL with the procedural constructs SQL lacks - variables, conditionals, loops, exception handling - so logic that would otherwise sit in an application can live in the database. It is created with SQL, must be uniquely named, and differs from a function in that it need not return a value.

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

Correct answer: All of the above.

There is no limit in principle: a query may join as many tables as it needs, and reports joining half a dozen are commonplace. What rises with the count is the optimiser’s work in choosing an order, and the care needed to supply a condition for every pair.

Answer: A

Correct answer: Equi-join

The join condition tests two columns for equality, which is what makes it an equi-join, and both CUSTOMER_ID columns appear in the select list. A natural join would have dropped one of them; the presence of the duplicate is the distinguishing feature.

Answer: D

Correct answer: All of the above.

All three return the matching rows; what distinguishes them is what else they do. The equi-join returns matching rows with both join columns, the natural join drops the duplicate column, and the outer join adds the unmatched rows besides. The matching rows appear in every case.