1. | A subquery is allowed in which parts of a SQL SELECT statement? |
|
2. | True or false: A correlated subquery references a table in the SELECT clause. |
|
3. | Which set operator will not remove duplicate rows from the result of a compound query? |
|
4. | What characteristics of the columns in a compound query using INTERSECT must match? |
|
5. | How are NULL values handled using set operators in a compound UNION query? |
|
6. | Why are ROLLUP and CUBE the preferred method for generating subtotals and grand totals for an aggregate query? |
|
7. | Which operators can be used to compare a column to a single-row subquery? |
|
8. | A compound query that needs to find only the rows that are the same between the two queries should use the set operator. |
|
9. | True or false: The IN operator cannot be used with a single-row subquery. |
|
10. | Put the set operators UNION, UNION ALL, INTERSECT, and MINUS in order of precedence. |
|
11. | What can be used to change the precedence of a pair of queries in a compound query with more than two queries? |
|
Answers
1. | A subquery is allowed in the SELECT clause, the FROM clause, and the WHERE clause. |
2. | False, the correlated subquery references a column in the main query. |
3. | UNION ALL will not remove duplicate rows from the result of a compound query. |
4. | The number of columns and their datatypes must match in a compound query using INTERSECT. The lengths of the columns and the names do not need to match. |
5. | NULL values in one query are considered equal to NULL values in the other query, for the purposes of eliminating duplicates in a UNION. |
6. | ROLLUP and CUBE need to make only one pass over the source table(s). Other methods, such as using a UNION between two similar queries, will make more than one pass. |
7. | The following operators can be used to compare a column to a single-row subquery: =, !=, >, <, >=, and <=. |
8. | INTERSECT |
9. | False, using IN with a single-row subquery would be equivalent to using =. |
10. | All of those operators have equal precedence and are evaluated left to right in a compound query. |
11. | As with any other part of a SQL query, parentheses may be used to change the evaluation order of the set operators. |