CHAPTER 11

Subqueries and Set Operators

Exam Objectives

In this chapter, you will learn to

• 061.7.1    Define Subqueries

• 061.7.2    Describe the Types of Problems that the Subqueries Can Solve

• 061.7.3    Describe the Types of Subqueries

• 061.7.4    Write Single-Row and Multiple-Row Subqueries

• 061.8.1    Describe Set Operators

• 061.8.2    Use a Set Operator to Combine Multiple Queries into a Single Query

• 061.8.3    Control the Order of Rows Returned

The previous chapters have dealt with the SELECT statement in considerable detail, but in every case the SELECT statement has been a single, self-contained command. This chapter is the first of two that show how you can combine two or more SELECT commands into one statement. The first technique (covered in this chapter) is the use of subqueries. A subquery is a SELECT statement whose output is used as input to another SELECT statement (or indeed to a data manipulation statement [DML] statement, as done in Chapter 6). The second technique is the use of set operators, where the results of several SELECT commands are combined into a single result set.

Define Subqueries

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery can return a set of rows or just one row to its parent query. A scalar subquery is a query that returns exactly one value: a single row, with a single column. Scalar subqueries can be used in most places in a SQL statement where you could use an expression or a literal value.

The places in a query where a subquery can be used are as follows:

•  In the SELECT list used for column projection

•  In the FROM clause

•  In the WHERE clause

•  In the HAVING clause

A subquery is often referred to as an inner query, and the statement within which it occurs is then called the outer query. There is nothing wrong with this terminology, except that it may imply that you can have only two levels, inner and outer. In fact, the Oracle implementation of subqueries does not impose any practical limits on the level of nesting. The depth of subquery nesting permitted is unlimited in the FROM clause and is capped at 255 levels in the WHERE clause.

A subquery can have any of the usual clauses for selection and projection. The following are required clauses:

•  A SELECT list

•  A FROM clause

The following are optional clauses:

•  WHERE

•  GROUP BY

•  HAVING

The subquery (or subqueries) within a statement must be executed before the parent query that calls it so that the results of the subquery can be passed to the parent.

Exercise 11-1: Explore Types of Subqueries    In this exercise, you will write code that demonstrates the places where subqueries can be used. Use either SQL*Plus or SQL Developer. All the queries should be run when connected to the HR schema.

1.  Log on to your database as user HR.

2.  Write a query that uses subqueries in the column projection list. The query will report on the current numbers of departments and staff.

3.  Write a query to identify all the employees who are managers. This will require using a subquery in the WHERE clause to select all the employees whose EMPLOYEE_ID appears as a MANAGER_ID.

4.  Write a query to identify the highest salary paid in each country. This will require using a subquery in the FROM clause.

Describe the Types of Problems that the Subqueries Can Solve

There are many situations where you will need the result of one query as the input for another.

Use of a Subquery Result Set for Comparison Purposes

Which employees have a salary that is less than the average salary? This could be answered by two statements or by a single statement with a subquery. The following example uses two statements:

Alternatively, the following example uses one statement with a subquery:

In this example, the subquery is used to substitute a value into the WHERE clause of the parent query; it returns a single value, used for comparison with the rows retrieved by the parent query.

The subquery could return a set of rows. For example, you could use the following to find all departments that have one or more employees assigned to them:

In the preceding example, the subquery is used as an alternative to an inner join. You could achieve the same result with the following:

If the subquery is going to return more than one row, then the comparison operator must be able to accept multiple values. These operators are IN, NOT IN, ANY, and ALL. If the comparison operator is any of the scalar equality or inequality operators (which each can accept only one value), the parent query will fail.

Star Transformation

An extension of the use of subqueries as an alternative to a join is to enable the star transformation often needed in data warehouse applications. Consider the large SALES table in the demo SH schema used for recording sales transactions. Each row captures a particular product sold to a particular customer through a particular channel. These attributes are identified by lookup codes used as foreign keys to dimension tables with rows that describe each product, customer, and channel. To identify all sales of an item called Comic Book Heroes to customers in the city of Oxford through Internet orders, you could run the following query:

This query uses the WHERE clause to join the tables and then to filter the results. The following is an alternative query that will yield the same result:

The rewrite of the first statement to the second is the star transformation. Apart from being an inherently more elegant structure (most SQL developers with any sense of aesthetics will agree with that), there are technical reasons why the database may be able to execute it more efficiently than the original query. Also, star queries are easier to maintain; it is simple to add more dimensions to the query or to replace the single literals ('Comic Book Heroes', 'Oxford', and 'Internet') with lists of values.

Generate a Table from Which to SELECT

Subqueries can also be used in the FROM clause where they are sometimes referred to as inline views. Consider the following problem based on the HR schema: Employees are assigned to a department, and departments have a location. Each location is in a country. How can you find the average salary of staff in a country, even though they work for different departments? Here’s the answer:

The subquery conceptually constructs a table with every employee’s salary and the country in which their department is based. The parent query then addresses this table, averaging the SALARY and grouping by COUNTRY_ID.

Generate Values for Projection

The third place a subquery can be used is in the SELECT list of a query. How can you identify the highest salary and the highest commission rate and thus what the maximum commission paid would be if the highest salaried employee also had the highest commission rate? Here’s the answer, with two subqueries:

In this usage, the SELECT list used to project columns is being populated with the results of the subqueries. A subquery used in this manner must be scalar or the parent query will fail with an error.

Generate Rows to Be Passed to a DML Statement

DML statements are covered in detail in Chapter 10. For now, consider these examples:

The first example uses a subquery to identify a set of rows in one table that will be inserted into another. The second example uses a subquery to calculate the average salary of all employees and passes this value (a scalar quantity) to an update statement. The third example uses a subquery to retrieve all DEPARTMENT_IDs that are in use and passes the list to a DELETE command, which will remove all departments that are not in use. Note the use of the additional WHERE clause in the subquery to ensure that no NULL values are returned by the subquery. If this clause were absent, no rows would be deleted.

Note that it is not legal to use a subquery in the VALUES clause of an insert statement. The following is fine:

But this is not:

Exercise 11-2: Explore Complex Subqueries    In this exercise, you will write more complex subqueries. Use either SQL*Plus or SQL Developer. All the queries should be run when connected to the HR schema.

1.  Log on to your database as user HR.

2.  Write a query that will identify all employees who work in departments located in the United Kingdom. This will require three levels of nested subqueries.

3.  Check that the result from step 2 is correct by running the subqueries independently. First, find the COUNTRY_ID for the United Kingdom.

The result will be UK. Then find the corresponding locations.

The LOCATION_IDs returned will be 2400, 2500, and 2600. Then find the DEPARTMENT_IDs of departments in these locations.

The result will be two departments, 40 and 80. Finally, find the relevant employees.

4.  Write a query to identify all the employees who earn more than the average and who work in any of the IT departments. This will require two subqueries, not nested.

Describe the Types of Subqueries

There are three broad divisions of subqueries:

•  Single-row subqueries

•  Multiple-row subqueries

•  Correlated subqueries

Single- and Multiple-Row Subqueries

The single-row subquery returns one row. A special case is the scalar subquery, which returns a single row with one column. Scalar subqueries are acceptable (and often useful) in virtually any situation where you could use a literal value, a constant, or an expression. Multiple-row subqueries return sets of rows. These queries are commonly used to generate result sets that will be passed to a DML or SELECT statement for further processing. Both single-row and multiple-row subqueries will be evaluated once, before the parent query is run.

Single- and multiple-row subqueries can be used in the WHERE and HAVING clauses of the parent query, but there are restrictions on the legal comparison operators. If the comparison operator is any of the ones in the following table, the subquery must be a single-row subquery:

If any of the operators in the preceding table are used with a subquery that returns more than one row, the query will fail. The operators in the following table can use multiple-row subqueries:

Correlated Subqueries

A correlated subquery has a more complex method of execution than single- and multiple-row subqueries and is potentially much more powerful. If a subquery references columns in the parent query, then its result will be dependent on the parent query. This makes it impossible to evaluate the subquery before evaluating the parent query. Consider this statement, which lists all employees who earn less than the average salary:

The single-row subquery needs to be executed only once, and its result will be substituted into the parent query. But now consider a query that will list all employees whose salary is less than the average salary of their department. In this case, the subquery must be run for each employee to determine the average salary for her department; it is necessary to pass the employee’s department code to the subquery. This can be done as follows:

In this example, the subquery references a column, p.department_id, from the select list of the parent query. This is the signal that rather than evaluating the subquery once, it must be evaluated for every row in the parent query. To execute the query, Oracle will look at every row in EMPLOYEES and, as it does so, run the subquery using the DEPARTMENT_ID of the current employee row.

The flow of execution is as follows:

1.  Start at the first row of the EMPLOYEES table.

2.  Read the DEPARTMENT_ID and SALARY values of the current row.

3.  Run the subquery using the DEPARTMENT_ID value from step 2.

4.  Compare the result of step 3 with the SALARY value from step 2, and return the row if the SALARY value is less than the result.

5.  Advance to the next row in the EMPLOYEES table.

6.  Repeat from step 2.

A single-row or multiple-row subquery is evaluated once, before evaluating the outer query; a correlated subquery must be evaluated once for every row in the outer query. A correlated subquery can be a single- or multiple-row subquery, if the comparison operator is appropriate.

Exercise 11-3: Investigate the Different Types of Subqueries    In this exercise, you will demonstrate problems that can occur with different types of subqueries. Use either SQL*Plus or SQL Developer. All the queries should be run when connected to the HR schema; it is assumed that the EMPLOYEES table has the standard sets of rows.

1.  Log on to your database as user HR.

2.  Write a query to determine who earns more than Mr. Tobias.

This will return 86 names, in alphabetical order.

3.  Write a query to determine who earns more than Mr. Taylor.

This will fail with the error “ORA-01427: single-row subquery returns more than one row.” Determine why the query in step 2 succeeded but the one in step 3 failed. The answer lies in the data.

The use of the “greater than” operator in the queries for steps 2 and 3 requires a single-row subquery, but the subquery used can return any number of rows, depending on the search predicate used.

4.  Fix the code in steps 2 and 3 so that the statements will succeed no matter what LAST_NAME is used. Here are two possible solutions: one uses a different comparison operator that can handle a multiple-row subquery; the other uses a subquery that will always be a single-row subquery.

Here’s the first solution:

Here’s the second solution:

Write Single-Row and Multiple-Row Subqueries

The following are examples of single- and multiple-row subqueries. They are based on the HR demonstration schema.

How would you figure out which employees have a manager who works for a department based in the United Kingdom? Here is a possible solution using multiple-row subqueries:

In the preceding example, subqueries are nested three levels deep. Note that the subqueries use the IN operator because it is possible that the queries could return several rows.

You have been asked to find the job with the highest average salary. This can be done with a single-row subquery.

The subquery returns a single value: the average salary of the department with the highest average salary. It is safe to use the equality operator for this subquery because the MAX function guarantees that only one row will be returned.

The ANY and ALL operators are supported syntax, but their function can be duplicated with other more commonly used operators combined with aggregations. For example, these two statements, which retrieve all employees whose salary is greater than anyone in department 80, will return identical result sets:

The following table summarizes the equivalents for ANY and ALL:

Using the EXISTS Condition

The EXISTS condition tests the existence of rows in a subquery and returns TRUE if one or more rows exist; otherwise, it returns FALSE. Consider the query that lists only the departments that have employees.

Eleven departments are returned. Some departments currently have no employees that belong to them. These 16 departments are easily identified by using the NOT EXISTS condition.

Null Results in a Subquery

Be wary of the operators being used when comparing expressions to subquery results. If one of the values returned by a subquery is a null, the entire query may return a null. Conditions that compare a null value result in a null being returned. Therefore, if null values are likely to be part of the results set of a subquery, avoid using the NOT IN operator since this is equivalent to <> ALL. Consider these statements:

There is one row in the EMPLOYEES table with a null MANAGER_ID. The first statement compares whether EMPLOYEE_ID <> ALL, and since a NULL is present in the subquery, the statement returns no rows. You can ensure that no nulls are returned in the subquery by using a WHERE clause, as in the second statement that returns the 89 employees who are not managers. The IN operator is equivalent to =ANY. There is no problem with null values in the result set, and the third statement returns the 18 employees who are managers.

Describe the Set Operators

All SELECT statements return a set of rows. The set operators take as their input the results of two or more SELECT statements and from these generate a single result set. This is known as a compound query. Oracle provides three set operators: UNION, INTERSECT, and MINUS. UNION can be qualified with ALL. There is a significant deviation from the International Organization for Standardization (ISO) standard for SQL here, in that ISO SQL uses EXCEPT where Oracle uses MINUS, but the functionality is identical. The set operators used in compound queries are as follows:

•  UNION    Returns the combined rows from two queries, sorting them and removing duplicates

•  UNION ALL    Returns the combined rows from two queries without sorting or removing duplicates

•  INTERSECT    Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates

•  MINUS    Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates

These commands are equivalent to the standard operators used in mathematics set theory, often depicted graphically as Venn diagrams.

Sets and Venn Diagrams

Consider groupings of living creatures, classified as follows:

•  Creatures with two legs    Humans, parrots, bats

•  Creatures that can fly    Parrots, bats, bees

•  Creatures with fur    Bears, bats

Each classification is known as a set, and each member of the set is an element. The union of the three sets is humans, parrots, bats, bees, and bears. This is all the elements in all the sets, without the duplications. The intersection of the sets is all elements that are common to all three sets, again removing the duplicates. In this simple example, the intersection has just one element: bats. The intersection of the two-legged set and the flying set has two elements: parrots and bats. The minus of the sets is the elements of one set without the elements of another, so the two-legged creatures set, minus the flying creatures set, minus the furry creatures set, results in a single element: humans.

These sets can be represented graphically as the Venn diagram shown in Figure 11-1. (Venn diagrams are named after John Venn, who formalized the theory at Cambridge University in the nineteenth century.)

Figure 11-1    A Venn diagram showing three sets and the universal set

The circle in the top left of the figure represents the set of two-legged creatures, the circle at the top right consists of creatures that can fly, and the bottom circle is furry animals. The unions, intersections, and minuses of the sets are immediately apparent by observing the elements in the various parts of the circles that do or do not overlap. The diagram in the figure also includes the universal set, represented by the rectangle. The universal set is all elements that exist, including those that are not members of the defined sets. In this case, the universal set would be defined as all living creatures, including those that did not develop fur, two legs, or the ability to fly (such as fish).

That’s enough school math; now proceed to the implementation within SQL.

Set Operator General Principles

All set operators make compound queries by combining the result sets from two or more queries. If a SELECT statement includes more than one set operator (and therefore more than two queries), they will be applied in the order the programmer specifies: top to bottom and left to right. Although enhancements to the SQL 2011 standard give INTERSECT a higher priority than the others, currently all Oracle set operators have equal precedence. To override this possible future change in precedence, based on the order in which the operators appear, you can use parentheses; operators within brackets will be evaluated before passing the results to operators outside the brackets.

Each query in a compound query will project its own list of selected columns. These lists must have the same number of elements, be nominated in the same sequence, and be of broadly similar data type. They do not need to have the same names (or column aliases), nor do they need to come from the same tables (or subqueries). If the column names (or aliases) are different, the result set of the compound query will have columns named as they were in the first query.

While the projected column lists do not have to be the same data type, they must be from the same data type group. For example, the columns selected by one query could be of data types DATE and NUMBER, and those from the second query could be TIMESTAMP and INTEGER. The result set of the compound query will have columns with the higher level of precision; in this case, they would be TIMESTAMP and NUMBER. Other than accepting data types from the same group, the set operators will not do any implicit type casting. If the second query retrieved columns of type VARCHAR2, the compound query would throw an error even if the string variables could be resolved to legitimate date and numeric values.

UNION, MINUS, and INTERSECT will always combine the results sets of the input queries and then sort the results to remove duplicate rows. The sorting is based on all the columns, from left to right. If all the columns in two rows have the same value, then only the first row is returned in the compound result set. A side effect of this is that the output of a compound query will be sorted. If the sort order (which is ascending, based on the order in which the columns happen to appear in the select lists) is not the order you want, it is possible to put a single ORDER BY clause at the end of the compound query. It is not possible to use ORDER BY in any of the queries that make up the whole compound query because this would disrupt the sorting that is necessary to remove duplicates.

UNION ALL is the exception to the sorting-no-duplicates rule; the result sets of the two input queries will be concatenated to form the result of the compound query. But you still can’t use ORDER BY in the individual queries; it can appear only at the end of the compound query where it will be applied to the complete result set.

Exercise 11-4: Describe the Set Operators    In this exercise, you will see the effect of the set operators. Either SQL*Plus or SQL Developer can be used.

1.  Connect to your database as user HR.

2.  Run this query:

Note the result, in particular the order of the rows. If the table is as originally created, there will be four rows returned. The order will be Europe, Americas, Asia, Middle East, and Africa.

3.  Query the Regions table twice, using UNION.

The rows returned will be like for step 1 but sorted alphabetically.

4.  This time, use UNION ALL.

There will be double the number of rows, and they will not be sorted.

5.  An intersection will retrieve rows common to two queries.

All four rows are common, and the result is sorted.

6.  A MINUS will remove common rows.

The second query will remove all the rows in the first query. The result is that no rows are left.

Use a Set Operator to Combine Multiple Queries into a Single Query

Compound queries are two or more queries, linked with one or more set operators. The end result is a single result set.

The examples that follow are based on two tables, OLD_DEPT and NEW_DEPT. The table OLD_DEPT is intended to represent a table created with an earlier version of Oracle, when the only data type available for representing date and time data was DATE, the only option for numeric data was NUMBER, and character data was fixed-length CHAR. The table NEW_DEPT uses the more closely defined INTEGER numeric data type (which Oracle implements as a NUMBER of up to 38 significant digits but no decimal places), the more space-efficient VARCHAR2 for character data, and the TIMESTAMP data type, which can, by default, store date and time values with six decimals of precision on the seconds. There are two rows in each table.

The UNION ALL Operator

A UNION ALL takes two result sets and concatenates them 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 (in the order in which they are specified) must be of the same data type group. The columns do not need to have the same names.

Figure 11-2 demonstrates a UNION ALL operation from two tables. The UNION ALL of the two tables converts all the values to the higher level of precision; the dates are returned as timestamps (the less precise DATEs padded with zeros), the character data is the more efficient VARCHAR2 with the length of the longer input column, and the numbers (though this is not obvious because of the nature of the data) will accept decimals. The order of the rows is the rows from the first table in whatever order they happen to be stored, followed by the rows from the second table in whatever order they happen to be stored.

Figure 11-2    A UNION ALL with data type conversions

The UNION Operator

A UNION performs a UNION ALL and then sorts the result across all the columns and removes duplicates. The first query in Figure 11-3 returns all four rows because there are no duplicates. However, the rows are now in order. It may appear that the first two rows are not in order because of the values in DATED, but they are. The DNAME in the table OLD_DEPTS is 20 bytes long (padded with spaces), whereas the DNAME in NEW_DEPT, where it is a VARCHAR2, is only as long as the name itself. The spaces give the row from OLD_DEPT a higher sort value, even though the date value is less.

Figure 11-3    UNION compound queries

The second query in Figure 11-3 removes any leading or trailing spaces from the DNAME columns and chops off the time elements from DATED and STARTD. Two of the rows thus become identical, so only one appears in the output.

Because of the sort, the order of the queries in a UNION compound query makes no difference to the order of the rows returned.

The INTERSECT Operator

The intersection of two sets is the rows that are common to both sets, as shown in Figure 11-4.

Figure 11-4    INTERSECT and MINUS

The first query shown in Figure 11-4 returns no rows because every row in the two tables is different. Next, applying functions to eliminate some of the differences returns the one common row. In this case, only one row is returned; had there been several common rows, they would be in order. The order in which the queries appear in the compound query has no effect on this.

The MINUS Operator

A MINUS 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.

The third query in Figure 11-4 returns all the rows in OLD_DEPT because there are no matching rows in NEW_DEPT. The last query forces some commonality, causing one of the rows to be removed. Because of the sort, the rows will be in order irrespective of the order in which the queries appear in the compound query.

More Complex Examples

If two queries do not return the same number of columns, it may still be possible to run them in a compound query by generating additional columns with NULL values. For example, consider a classification system for animals: All animals have a name and a weight, but the birds have a wingspan, whereas the cats have a tail length. The following is a query to list all the birds and cats:

Note the use of NULL to generate the missing values.

A compound query can consist of more than two queries, in which case operator precedence can be controlled with parentheses. Without parentheses, the set operators will be applied in the sequence in which they are specified. Consider the situation where there is a table PERMSTAFF with a listing of all permanent staff members and a table CONSULTANTS with a listing of consultant staff. There is also a table BLACKLIST of people blacklisted for one reason or another. The following query will list all the permanent and consulting staff in a certain geographical area, removing those on the blacklist:

Note the use of UNION ALL because it is assumed that no one will be in both the PERMSTAFF table and the CONSULTANTS table; a UNION would force an unnecessary sort. The order of precedence for set operators is the order specified by the programmer, so the MINUS operation will compare the BLACKLIST value with the result of the UNION ALL operation. The result will be all staff (permanent and consulting) who do not appear on the blacklist. If the blacklisting could be applied only to consulting staff and not to permanent staff, there would be two possibilities. First, the queries could be listed in a different order.

This would return consultants who are not blacklisted and then append all permanent staff. Alternatively, parentheses could control the precedence explicitly.

This query will list all permanent staff and then append all consultant staff who are not blacklisted.

These two queries will return the same rows, but the order will be different because the UNION ALL operations list the PERMSTAFF and CONSULTANTS tables in a different sequence. To ensure that the queries return identical result sets, there would need to be an ORDER BY clause at the foot of the compound queries.

Control the Order of Rows Returned

By default, the output of a UNION ALL compound query is not sorted at all; 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. The output of any other set operator will be sorted in ascending order of all the columns, starting with the first column named.

It is not syntactically possible to use an ORDER BY clause in the individual queries that make up a compound query. This is because the execution of most compound queries has to sort the rows, which would conflict with the ORDER BY. It might seem theoretically possible that a UNION ALL (which does not sort the rows) could take an ORDER BY for each query, but the Oracle implementation of UNION ALL does not permit this.

There is no problem with placing an ORDER BY clause at the end of the compound query, however. This will sort the entire output of the compound query. The default sorting of rows is based on all the columns in the sequence they appear. A specified ORDER BY clause has no restrictions; it can be based on any columns (and functions applied to columns) in any order. Here’s an example:

Two-Minute Drill

Define Subqueries

•  A subquery is a SELECT statement embedded within another SQL statement.

•  Subqueries can be nested within each other.

•  With the exception of the correlated subquery, subqueries are executed before the outer query within which they are embedded.

Describe the Types of Problems that the Subqueries Can Solve

•  Selecting rows from a table with a condition that depends on the data within the table can be implemented with a subquery.

•  Complex joins can sometimes be replaced with subqueries.

•  Subqueries can add values to the outer query’s output that are not available in the tables the outer query addresses.

Describe the Types of Subqueries

•  Multiple-row subqueries can return several rows, possibly with several columns.

•  Single-row subqueries return one row, possibly with several columns.

•  A scalar subquery returns a single value; it is a single-row, single-column subquery.

•  A correlated subquery is executed once for every row in the outer query.

Write Single-Row and Multiple-Row Subqueries

•  Single-row subqueries should be used with single-row comparison operators.

•  Multiple-row subqueries should be used with multiple-row comparison operators.

•  The ALL and ANY operators can be alternatives to use of aggregations.

Describe the Set Operators

•  UNION ALL concatenates the results of two queries.

•  UNION sorts the results of two queries and removes duplicates.

•  INTERSECT returns only the rows common to the result of two queries.

•  MINUS returns the rows from the first query that do not exist in the second query.

Use a Set Operator to Combine Multiple Queries into a Single Query

•  The queries in the compound query must return the same number of columns.

•  The corresponding columns must be of compatible data type.

•  The set operators have equal precedence and will be applied in the order they are specified.

Control the Order of Rows Returned

•  It is not possible to use ORDER BY in the individual queries that make a compound query.

•  An ORDER BY clause specifying a column number or alias can be appended to the end of a compound query.

•  The rows returned by a UNION ALL will be in the order they occur in the two source queries.

•  The rows returned by a UNION will be sorted across all their columns, left to right.

Self Test

1.  Consider this generic description of a SELECT statement:

Where could subqueries be used? (Choose all correct answers.)

A.  select_list

B.  table

C.  condition

D.  expression_1

E.  expression_2

F.  expression_3

2.  A query can have a subquery embedded within it. Under what circumstances could there be more than one subquery? (Choose the best answer.)

A.  The outer query can include an inner query. It is not possible to have another query within the inner query.

B.  It is possible to embed a single-row subquery inside a multiple-row subquery, but not the other way around.

C.  The outer query can have multiple inner queries, but they must not be embedded within each other.

D.  Subqueries can be embedded within each other with no practical limitations on depth.

3.  Consider this statement:

When will the subquery be executed? (Choose the best answer.)

A.  It will be executed before the outer query.

B.  It will be executed after the outer query.

C.  It will be executed concurrently with the outer query.

D.  It will be executed once for every row in the EMPLOYEES table.

4.  Consider this statement:

When will the subquery be executed? (Choose the best answer.)

A.  It will be executed before the outer query.

B.  It will be executed after the outer query.

C.  It will be executed concurrently with the outer query.

D.  It will be executed once for every row in the EMPLOYEES table.

5.  Consider the following statement:

Also consider this statement:

What can be said about the two statements? (Choose two correct answers.)

A.  The two statements should generate the same result.

B.  The two statements could generate different results.

C.  The first statement will always run successfully; the second statement will error if there are two departments with DEPARTMENT_NAME=‘Executive’.

D.  Both statements will always run successfully, even if there are two departments with DEPARTMENT_NAME=‘Executive’.

6.  What are the distinguishing characteristics of a scalar subquery? (Choose two correct answers.)

A.  A scalar subquery returns one row.

B.  A scalar subquery returns one column.

C.  A scalar subquery cannot be used in the SELECT LIST of the parent query.

D.  A scalar subquery cannot be used as a correlated subquery.

7.  Which comparison operator cannot be used with multiple-row subqueries? (Choose the best answer.)

A.  ALL

B.  ANY

C.  IN

D.  NOT IN

E.  All the above can be used.

8.  Consider this statement:

What is wrong with it? (Choose the best answer.)

A.  Nothing is wrong—the statement should run without error.

B.  The statement will fail because the subquery in the SELECT list references a table that is not listed in the FROM clause.

C.  The statement will fail if the second query returns more than one row.

D.  The statement will run but is extremely inefficient because of the need to run the second subquery once for every row in EMPLOYEES.

9.  Which of the following statements are equivalent? (Choose two answers.)

10.  Consider this statement, which is intended to prompt for an employee’s name and then find all employees who have the same job as the first employee:

What would happen if a value were given for &Name that did not match with any row in EMPLOYEES? (Choose the best answer.)

A.  The statement would fail with an error.

B.  The statement would return every row in the table.

C.  The statement would return no rows.

D.  The statement would return all rows where JOB_ID is NULL.

11.  Which of these set operators will not sort the rows? (Choose the best answer.)

A.  INTERSECT

B.  MINUS

C.  UNION

D.  UNION ALL

12.  Which of these operators will remove duplicate rows from the final result? (Choose all that apply.)

A.  INTERSECT

B.  MINUS

C.  UNION

D.  UNION ALL

13.  If a compound query contains both a MINUS operator and an INTERSECT operator, which will be applied first? (Choose the best answer.)

A.  The INTERSECT, because INTERSECT has higher precedence than MINUS.

B.  The MINUS, because MINUS has a higher precedence than INTERSECT.

C.  The precedence is determined by the order in which they are specified.

D.  It is not possible for a compound query to include both MINUS and INTERSECT.

14.  There are four rows in the REGIONS table. Consider the following statements, and choose how many rows will be returned for each: 0, 4, 8, or 16:

15.  Consider this compound query:

The columns EMP.EMPNO and EX_EMP.EMP_ID are integer, the column EMP.HIRED is timestamp, and the columns EX_EMP.HIRED and EX_EMP.FIRED are date. Why will the statement fail? (Choose the best answer.)

A.  Because the columns EMPNO and EMP_ID have different names.

B.  Because the columns EMP.HIRED and EX_EMP.HIRED are different data types.

C.  Because there are two columns in the first query and three columns in the second query.

D.  For all the reasons above.

E.  The query will succeed.

16.  Which line of this statement will cause it to fail? (Choose the best answer.)

A.  select ename, hired from current_staff

B.  order by ename

C.  minus

D.  select ename, hired from current staff

E.  where deptno=10

F.  order by ename;

17.  Study this statement:

In what order will the rows be returned? (Choose the best answer.)

A.  The rows from each table will be grouped and within each group will be sorted on ENAME.

B.  The rows from each table will be grouped but not sorted.

C.  The rows will not be grouped but will all be sorted on ENAME.

D.  The rows will be neither grouped nor sorted.

Self Test Answers

1.      A, B, C, and E. Subqueries can be used at all these points.
    D and F are incorrect. A subquery cannot be used in the GROUP BY and ORDER BY clauses of a query.

2.      D. Subquery nesting can be done to many levels.
    A, B, and C are incorrect. A and C are incorrect because subqueries can be nested. B is incorrect because the number of rows returned is not relevant to nesting subqueries, only to the operators being used.

3.      A. The result set of the inner query is needed before the outer query can run.
    B, C, and D are incorrect. B and C are not possible because the result of the subquery is needed before the parent query can start. D is wrong because the subquery is run only once.

4.      D. This is a correlated subquery that must be run for every row in the table.
    A, B, and C are incorrect. The result of the inner query is dependent on a value from the outer query; it must therefore be run once for every row.

5.      A and D. The two statements will deliver the same result, and neither will fail if the name is duplicated.
    B and C are incorrect. B is incorrect because the statements are functionally identical, though syntactically different. C is incorrect because the comparison operator used, IN, can handle a multiple-row subquery.

6.      A and B. A scalar subquery can be defined as a query that returns a single value.
    C and D are incorrect. C is incorrect because a scalar subquery is the only subquery that can be used in the SELECT LIST. D is incorrect because scalar subqueries can be correlated.

7.      E. ALL, ANY, IN, and NOT IN are the multiple-row comparison operators.
    A, B, C, and D are incorrect. All of these can be used.

8.      C. The equality operator requires a single-row subquery, and the second subquery could return several rows.
    A, B, and D are incorrect. A is incorrect because the statement will fail in all circumstances except the unlikely case where there is zero or one employee. B is incorrect because this is not a problem; there doesn’t need to be a relationship between the source of data for the inner and outer queries. D is incorrect because the subquery will run only once; it is not a correlated subquery.

9.      A and B. These are identical.
    C and D are incorrect. C is logically the same as A and B but syntactically is not possible; it will give an error. D will always return no rows, because it asks for all employees who have a salary lower than all employees. This is not an error but can never return any rows. The filter on DEPARTMENTS is not relevant.

10.      C. If a subquery returns NULL, the comparison will also return NULL, meaning that no rows will be retrieved.
    A, B, and D are incorrect. A is incorrect because this would not cause an error. B is incorrect because a comparison with NULL will return nothing, not everything. D is incorrect because a comparison with NULL can never return anything, not even other NULLs.

11.      D. UNION ALL returns rows in the order that they are delivered by the two queries from which the compound query is made up.
    A, B, and C are incorrect. INTERSECT, MINUS, and UNION all use sorting as part of their execution.

12.      A, B, and C. INTERSECT, MINUS, and UNION all remove duplicate rows.
    D is incorrect. UNION ALL returns all rows, duplicates included.

13.      C. All set operators have equal precedence, so the precedence is determined by the sequence in which they occur.
    A, B, and D are incorrect. A and B are incorrect because set operato rs have equal precedence—though this may change in future releases. D is incorrect because many set operators can be used in one compound query.

14.      A = 4; B = 8; C = 0; D = 4.
    Note that 16 is not used; that would be the result of a Cartesian product query.

15.      C. Every query in a compound query must return the same number of columns.
    A, B, D, and E are incorrect. A is incorrect because the columns can have different names. B is incorrect because the two columns are of the same data type group, which is all that was required. It therefore follows that D and E are also incorrect.

16.      B. You cannot use ORDER BY for one query of a compound query; you can place only a single ORDER BY clause at the end.
    A, C, D, E, and F are incorrect. All these lines are legal.

17.      B. The rows from each query will be together, but there will be no sorting.
    A, C, and D are incorrect. A is not possible with any syntax. C is incorrect because that would be the result of a UNION, not a UNION ALL. D is incorrect because UNION ALL will return the rows from each query grouped together.