Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
26 Cards in this Set
- Front
- Back
|
What are the 4 set operators used in compound queries? |
1. UNION 2. UNION ALL 3. INTERSECT 4. MINUS |
|
|
What does the UNION set operator do? |
It returns the combined rows from two queries, sorting them and removing duplicates. |
|
|
What does the UNION ALL set operator do? |
It returns the combined rows from two queries without sorting or removing duplicates. |
|
|
What does the INTERSECT set operator do? |
It returns only the rows that occur in both queries' result sets, sorting them and removing duplicates. |
|
|
What does the MINUS set operator do? |
It returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates. |
|
|
T/F All set operators make command queries by combining the result sets from two or more queries. |
True |
|
|
T/F Currently there is no precedence when it comes to set operators. |
True. There is no priority of one operator over another. |
|
|
How can you override set operator precedence? |
You can use parentheses. Operators within brackets will be evaluated before passing the results to operators outside the brackets. |
|
|
T/F The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query. |
True. |
|
|
The corresponding columns in the queries that make up a compound query must be of the same _____ _____ group. |
data type |
|
|
A compound query will be default returns rows sorted across all the columns, from ____ to _____. The only exception is ______ _____, where the rows will not be sorted. |
Left, right, UNION ALL |
|
|
What are compound queries? |
Two or more queries, linked with one or more set operators. The end result is a single result set. |
|
|
What does a UNION ALL operator do? |
It takes two result sets and concatenates them together into a single result set. The result sets come from two queries that must select the same number of columns, and the corresponding columns of the two queries must be of the same data type group. |
|
|
What does a UNION operator do? |
It performs a UNION ALL and then sorts the result across all the columns and removes duplicates. |
|
|
If you know that there can be no duplicates between two tables, then always use _______. It saves the database from doing a lot of sorting. |
UNION ALL |
|
|
The intersection of two sets is.... |
the rows that are common to both sets. |
|
|
What does a MINUS operator do? |
`It runs both queries, sorts the results, and returns only the rows from the first result set that do not appear in the second result set. |
|
|
Why aren't the queries that make up compound queries subqueries? |
A subquery generates a result set that is used by another query. The queries in a compound query run independently, and separate stage of execution combines the result sets. |
|
|
How can you present several tables with similar data as one table? |
This is a common problem, often caused by bad systems analysis or perhaps by attempts to integrate systems together. Compound queries are often the answer. By using type casting functions to force columns to the same data type and TO_CHAR (NULL) to generate missing columns, you can present the data as though it were from one table. |
|
|
Are there performance issues with compound queries? |
Perhaps. With the exception of UNION ALL, compound queries have to sort date, across the full width of the rows. This may be expensive in both memory and CPU. Also, if the two queries both address the same table, there will be two passes through the data as each query is run independently; if the same result could be achieved with one query, this would usually be a faster solution. Compound queries are a powerful tool but should be used with caution. |
|
|
T/F By default, the output of a UNION ALL compound query is not sorted at all. |
True - The rows will be returned in groups in the order of which query was listed first and within the groups in the order that they happen to be stored. |
|
|
T/F It is not syntactically possible to use an ORDER BY clause in the individual queries that make up a compound query. |
True. - This is because the execution of most compound queries has to sort the rows, which would conflict with the ORDER BY. |
|
|
T/F You cannot place an ORDER BY clause at the end of a compound query. |
False. There is no problem placing an ORDER BY clause at the end of the compound query. This will sort the entire output of the compound query. |
|
|
A query that uses a set operator is called what? |
a compound query |
|
|
T/F The set operators all have equal precedence. |
True. -If more than one is included in a compound query they will be executed in the order in which they occur--though this can be controlled by using parentheses. |
|
|
All the set operators except for ______ rely on sorting to merge result sets and remove duplicate rows. |
UNION ALL |
