**1. Which is NOT a characteristic of subqueries? **
a) Are nested inside of another query.
b) Usually used in the WHERE clause of the outer query.
c) Are excuted last, after the outer query excutes. ← Correct
d) Can be used in SELECT, INSERT, UPDATE, or DELETE statements.
Explanation:
Subqueries are executed, first, before its outer query. A subquery returns a result set to be used by the outer query as its input.
Objective
Understand: Explain how subqueries can be used and why
Covered in:
Subqueries
**2. Which of the following SQL statement contains a valid subquery for the tables library and books? **
a) SELECT l.library_id, l.name FROM library l WHERE l.library_id IN (SELECT b.library_id FROM books b WHERE b.available = 'Yes' AND b.author = "Mark Twain"); ← Correct
b) SELECT l.library_id, l.name FROM library l WHERE l.library_id IN (books b WHERE b.available = 'Yes' AND b.author = "Mark Twain");
c) SELECT l.library_id, l.name FROM library l WHERE (SELECT b.library_id FROM books b WHERE b.available = 'Yes' AND b.author = "Mark Twain");
d) None of the above.
Explanation:
A subquery should be a valid SQL statement itself. The subquery here is correct: (SELECT b.library_id FROM books b WHERE b.available = 'Yes' AND b.author = "Mark Twain") and returns a result set containing the library id's of all libraries that contain books written by Mark Twain and are available for checkout. This result set is used by the outer query in the WHERE clause, WHERE l.library_id IN, to retrieve matching library id's.
Objective
Apply: Execute a subquery on an existing data set
Covered in:
Subqueries
**3. How efficient are subqueries compared to joins? **
a) Both are equal in performance
b) Joins will always be more efficient
c) Subqueries will always be more efficient
d) None of the above ← Correct
Explanation:
There are several factors that effect performance of subqueries vs joins: Amount of data in the tables used in the queries, indexes defined on the tables, the database management system's execution plan and how it may optimize a query, etc. Therefore, in some cases, a subquery may outperform a join statement, while a join may outperform a subquery in other cases. In general, EXPLAIN PLAN should still be used to compare the performance between a subquery and equivalent join statement.
Objective
Understand: Compare efficiency of subqueries vs joins
Covered in:
Subqueries
**4. When should a subquery be used in place of a join? **
a) When the subquery results in better performance than a join
b) For better readability
c) For larger data sets where the subquery can reduce the amount of data to be evaluated by the outer query
d) All of the above ← Correct
Explanation:
Subqueries generally have better readability than joins. When there is little or no performance tradeoff, subqueries can be used for to better readability. When in doubt, use EXPLAIN PLAN to compare the performance between a subquery and equivalent join statement.
Objective
Analyze: Compare/contrast when subqueries should be used vs. joins
Covered in:
Subqueries