What You’ll Learn in This Hour:
▶ Identifying the operators that combine queries
▶ Knowing when to use the commands to combine queries
▶ Using the GROUP BY clause with the compound operators
▶ Using the ORDER BY clause with the compound operators
▶ Retrieving accurate data
In this hour, you learn how to combine SQL queries using the UNION, UNION ALL, INTERSECT, and EXCEPT operators. Because SQL is meant to work on data in sets, you need to combine and compare various sets of query data. The UNION, INTERSECT, and EXCEPT operators enable you to work with different SELECT statements and then combine and compare the results in different ways. Again, check your particular implementation for any variations in the use of these operators.
A single query uses one SELECT statement, whereas a compound query includes two or more SELECT statements.
You form compound queries using some type of operator to join the two queries. The UNION operator in the following examples joins two queries.
A single SQL statement can be written as follows:
select bird_name from birds ;
This is the same statement using the UNION operator:
select bird_name from birds where wingspan > 48 UNION select bird_name from birds where wingspan <= 48 ;
The previous statements both return a complete list of birds from the BIRDS table. The first query simply selects all birds with no conditions. The second query has two components to the compound query: The first selects birds with a wingspan greater than 48 inches, and the second selects birds that have wingspan less than or equal to 48 inches.
Compound operators combine and restrict the results of two SELECT statements. You can use these operators to return or suppress the output of duplicate records. Compound operators can bring together similar data that is stored in different fields.
Note
How UNION Works
The UNION operator simply merges the results of one or more queries. When using the UNION operator, column headings are determined by column names or column aliases used in the first SELECT statement.
Compound queries enable you to combine the results of more than one query to return a single set of data. This type of query is often simpler to write than a single query with complex conditions. Compound queries also allow for more flexibility in the never-ending task of data retrieval.
Compound query operators vary among database vendors. The ANSI standard includes the UNION, UNION ALL, EXCEPT, and INTERSECT operators, all of which are discussed in the following sections.
The UNION operator combines the results of two or more SELECT statements without returning duplicate rows. In other words, if a row of output exists in the results of one query, the same row is not returned even though it exists in the second query. To use the UNION operator, each SELECT statement must have the same number of columns selected, the same number of column expressions, the same data type, and the same order—but the SELECT statements do not have to be the same length.
The syntax follows:
SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ] UNION SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ]
Let’s look at two queries individually. The first query returns the MIGRATION_LOCATION for every row of data in the MIGRATION table. The second query returns the BIRD_NAME from the BIG_BIRDS table created earlier, based on the BIRDS table.
SQL> select migration_location 2 from migration; MIGRATION_LOCATION ------------------------------ Central America Mexico No Significant Migration Partial, Open Water South America Southern United States 6 rows selected. SQL> select bird_name 2 from big_birds; BIRD_NAME ------------------------------ Great Blue Heron Common Loon Bald Eagle Golden Eagle Osprey Canadian Goose Ring-billed Gull Double-crested Cormorant Turkey Vulture Mute Swan Brown Pelican Great Egret 12 rows selected.
Between the two previous queries, there are 18 rows of data. Now let’s combine the following two queries using the UNION operator. The UNION operator merges the results but does not show duplicate records (which do not exist in this example).
SQL> select migration_location 2 from migration 3 UNION 4 select bird_name 5 from big_birds; MIGRATION_LOCATION ------------------------------ Bald Eagle Brown Pelican Canadian Goose Central America Common Loon Double-crested Cormorant Golden Eagle Great Blue Heron Great Egret Mexico Mute Swan No Significant Migration Osprey Partial, Open Water Ring-billed Gull South America Southern United States Turkey Vulture 18 rows selected.
The previous example is a simple one, to show you how the UNION operator works. You might think that the data that is returned, displayed in a single column, is not necessarily relevant to other data in the column. This is true. However, this example illustrates that the database returns whatever data you ask for. It’s important when writing any SQL statement that relevant and inaccurate data is returned. This data is accurate but might not have met a purpose.
In the next example, the first query returns a list of bird names and the food items they eat, but only for birds that eat fish. The second query also returns a list of bird names and the food they eat, but only for birds that eat small mammals. These are two individual queries that are combined. Take a minute to study the results of the two individual queries; look for birds that exist in both lists.
SQL> select b.bird_name, f.food_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and f.food_name = 'Fish'; BIRD_NAME FOOD_NAME ------------------------------ ------------------------------ Great Blue Heron Fish Common Loon Fish Bald Eagle Fish Golden Eagle Fish Osprey Fish Belted Kingfisher Fish Common Sea Gull Fish Ring-billed Gull Fish Double-crested Cormorant Fish Common Merganser Fish American Crow Fish Green Heron Fish Brown Pelican Fish Great Egret Fish Anhinga Fish Black Skimmer Fish 16 rows selected.
SQL> select b.bird_name, f.food_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and f.food_name = 'Small Mammals'; BIRD_NAME FOOD_NAME ------------------------------ ------------------------------ Golden Eagle Small Mammals American Crow Small Mammals 2 rows selected.
In the following example, the UNION operator combines the results of both previous queries. In the result set that follows, 18 rows of data are returned and include everything in the previous two result sets.
SQL> select b.bird_name, f.food_name SQL> select b.bird_name, f.food_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and f.food_name = 'Fish' 8 UNION 9 select b.bird_name, f.food_name 10 from birds b, 11 birds_food bf, 12 food f 13 where b.bird_id = bf.bird_id 14 and f.food_id = bf.food_id 15 and f.food_name = 'Small Mammals'; BIRD_NAME FOOD_NAME ------------------------------ ------------------------------ American Crow Fish American Crow Small Mammals Anhinga Fish Bald Eagle Fish Belted Kingfisher Fish Black Skimmer Fish Brown Pelican Fish Common Loon Fish Common Merganser Fish Common Sea Gull Fish Double-crested Cormorant Fish Golden Eagle Fish Golden Eagle Small Mammals Great Blue Heron Fish Great Egret Fish Green Heron Fish Osprey Fish Ring-billed Gull Fish 18 rows selected.
The following example is a query that produces the same results, except that the OR operator is used in the WHERE clause instead of the UNION operator to combine two queries. Sometimes using a compound query to get the results that you seek is easier, and sometimes you can more easily join more tables and use other operators to return one result set.
SQL> select b.bird_name, f.food_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and (f.food_name = 'Fish' or f.food_name = 'Small Mammals'); BIRD_NAME FOOD_NAME ------------------------------ ------------------------------ Great Blue Heron Fish Common Loon Fish Bald Eagle Fish Golden Eagle Fish Osprey Fish Belted Kingfisher Fish Common Sea Gull Fish Ring-billed Gull Fish Double-crested Cormorant Fish Common Merganser Fish American Crow Fish Green Heron Fish Brown Pelican Fish Great Egret Fish Anhinga Fish Black Skimmer Fish Golden Eagle Small Mammals American Crow Small Mammals 18 rows selected.
UNION ALL OperatorYou use the UNION ALL operator to combine the results of two SELECT statements, including duplicate rows. The same rules that apply to UNION apply to the UNION ALL operator. The UNION and UNION ALL operators are the same, although one returns duplicate rows of data and the other does not.
The syntax follows:
SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ] UNION ALL SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ]
Following is a similar example that lists only the bird names. In the first SELECT statement, only birds that eat fish are returned, and in the second SELECT statement, only the birds that eat small mammals are returned. The UNION operator combines the two result sets. Take a look at the output:
SQL>> select b.bird_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and f.food_name = 'Fish' 8 UNION 9 select b.bird_name 10 from birds b, 11 birds_food bf, 12 food f 13 where b.bird_id = bf.bird_id 14 and f.food_id = bf.food_id 15 and f.food_name = 'Small Mammals' 16 order by 1; BIRD_NAME ------------------------------ American Crow Anhinga Bald Eagle Belted Kingfisher Black Skimmer Brown Pelican Common Loon Common Merganser Common Sea Gull Double-crested Cormorant Golden Eagle Great Blue Heron Great Egret Green Heron Osprey Ring-billed Gull 16 rows selected.
Notice that only 16 rows of data are returned in this example, compared to the 18 rows of data in the previous UNION example. This is because previous queries selected the bird’s name and also the food item that the bird eats. Even though duplicate bird names existed, the entire row of data itself was never duplicated in the previous results. In these results, you select only the bird’s name. Two birds appear twice in the results sets, so this result set does not repeat those duplicate bird names; instead, only 16 rows of data are returned. Remember that the UNION operator does not return duplicate records.
In the following example, you have the same query with the UNION ALL operator—it works the same as the UNION operator, except that it returns all values, including any duplicate records. The ORDER BY clause also sorts the result by the bird’s name so that you can more easily see the bird names that are duplicated. Notice that this example returns all 18 rows of data.
SQL> select b.bird_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and f.food_name = 'Fish' 8 UNION ALL 9 select b.bird_name 10 from birds b, 11 birds_food bf, 12 food f 13 where b.bird_id = bf.bird_id 14 and f.food_id = bf.food_id 15 and f.food_name = 'Small Mammals' 16 order by 1; BIRD_NAME ------------------------------ American Crow American Crow Anhinga Bald Eagle Belted Kingfisher Black Skimmer Brown Pelican Common Loon Common Merganser Common Sea Gull Double-crested Cormorant Golden Eagle Golden Eagle Great Blue Heron Great Egret Green Heron Osprey Ring-billed Gull 18 rows selected.
INTERSECT OperatorYou use the INTERSECT operator to combine two SELECT statements, but it returns only rows from the first SELECT statement that are identical to rows in the second SELECT statement. The same rules apply when using the INTERSECT operator as when using the UNION operator.
The syntax follows:
SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ] INTERSECT SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ]
The following SQL statement takes the same query as before and uses the INTERSECT operator instead of the UNION ALL operator. Study the results:
SQL> select b.bird_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and f.food_name = 'Fish' 8 INTERSECT 9 select b.bird_name 10 from birds b, 11 birds_food bf, 12 food f 13 where b.bird_id = bf.bird_id 14 and f.food_id = bf.food_id 15 and f.food_name = 'Small Mammals' 16 order by 1; BIRD_NAME ------------------------------ American Crow Golden Eagle 2 rows selected.
Notice that only two rows are returned because only two rows were identical between the output of the two single queries. The only bird names that were duplicated in the UNION ALL example were the American Crow and the Golden Eagle.
EXCEPT and MINUS OperatorsThe EXCEPT operator combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. Again, the same rules that apply to the UNION operator also apply to the EXCEPT operator. In Oracle, the EXCEPT operator is referenced by using the term MINUS, but it performs the same functionality.
The syntax follows:
SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ] MINUS SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ]
The following example applies the MINUS operator to the SQL statement that you have been using, to combine the results of the two queries.
SQL> select b.bird_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and f.food_name = 'Fish' 8 MINUS 9 select b.bird_name 10 from birds b, 11 birds_food bf, 12 food f 13 where b.bird_id = bf.bird_id 14 and f.food_id = bf.food_id 15 and f.food_name = 'Small Mammals' 16 order by 1; BIRD_NAME ------------------------------ Anhinga Bald Eagle Belted Kingfisher Black Skimmer Brown Pelican Common Loon Common Merganser Common Sea Gull Double-crested Cormorant Great Blue Heron Great Egret Green Heron Osprey Ring-billed Gull 14 rows selected.
As you can see, only 14 rows of data were selected. Remember that the first SELECT statement in this compound query returns 16 rows of data, and the second SELECT statement returns 2 rows of data. The two rows of data in a second SELECT, which consists of the birds American Crow and Golden Eagle, are eliminated from the final result set. This query returns all birds found in the first set that are not found in a second set. In other words, the bird names found in the second set are eliminated from the first set.
ORDER BY with a Compound QueryYou can use the ORDER BY clause with a compound query. However, you can use the ORDER BY clause only to order the results of both queries. Therefore, a compound query can have only one ORDER BY clause, even though the compound query might consist of multiple individual queries or SELECT statements. The ORDER BY clause must reference the columns ordered by an alias or by the column number.
The syntax follows:
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
OPERATOR{UNION | EXCEPT | INTERSECT | UNION ALL}
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ ORDER BY ]
The following example shows the simple use of the ORDER BY that you previously saw in this hour. Here you are sorting by 1, which is the first column and happens to be the only column selected in this case.
SQL> select b.bird_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and f.food_name = 'Fish' 8 MINUS 9 select b.bird_name 10 from birds b, 11 birds_food bf, 12 food f 13 where b.bird_id = bf.bird_id 14 and f.food_id = bf.food_id 15 and f.food_name = 'Small Mammals' 16 order by 1; BIRD_NAME ------------------------------ Anhinga Bald Eagle Belted Kingfisher Black Skimmer Brown Pelican Common Loon Common Merganser Common Sea Gull Double-crested Cormorant Great Blue Heron Great Egret Green Heron Osprey Ring-billed Gull 14 rows selected.
The following example is the same, except that you explicitly state the name of the column in the ORDER BY clause instead of using the numeric position of the column in the SELECT clause.
SQL> select b.bird_name 2 from birds b, 3 birds_food bf, 4 food f 5 where b.bird_id = bf.bird_id 6 and f.food_id = bf.food_id 7 and f.food_name = 'Fish' 8 MINUS 9 select b.bird_name 10 from birds b, 11 birds_food bf, 12 food f 13 where b.bird_id = bf.bird_id 14 and f.food_id = bf.food_id 15 and f.food_name = 'Small Mammals' 16 order by bird_name; BIRD_NAME ------------------------------ Anhinga Bald Eagle Belted Kingfisher Black Skimmer Brown Pelican Common Loon Common Merganser Common Sea Gull Double-crested Cormorant Great Blue Heron Great Egret Green Heron Osprey Ring-billed Gull 14 rows selected.
Note
Using Numbers in the ORDER BY Clause
The column in the ORDER BY clause is referenced by the number 1 instead of the actual column name. Sorting compound queries lets you easily recognize duplicate records.
GROUP BY with a Compound QueryUnlike ORDER BY, you can use GROUP BY in each SELECT statement of a compound query. You also can use it following all individual queries. In addition, you can use the HAVING clause (sometimes used with the GROUP BY clause) in each SELECT statement of a compound statement.
The syntax follows:
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ GROUP BY ]
[ HAVING ]
OPERATOR {UNION | EXCEPT | INTERSECT | UNION ALL}
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ GROUP BY ]
[ HAVING ]
[ ORDER BY ]
To best illustrate this example, several queries follow that select data about migration locations in the BIRDS database. The first query that follows shows the migration location and a count of birds that migrate to each specific migration location. Notice that six rows of data are returned because the database contains six migration locations and you have not placed any conditions on the data that this query returns.
SQL> select m.migration_location, 2 count(bm.bird_id) "COUNT OF BIRDS" 3 from migration m, 4 birds_migration bm 5 where m.migration_id = bm.migration_id 6 group by m.migration_location; MIGRATION_LOCATION COUNT OF BIRDS ------------------------------ -------------- Southern United States 18 Central America 12 South America 6 Mexico 14 No Significant Migration 5 Partial, Open Water 1 6 rows selected.
The next query is the same as the first, except that you use the HAVING clause to return only migration locations that have more than six types of birds that migrate to each specific location. Verify the following results against the previous output:
SQL> select m.migration_location, 2 count(bm.bird_id) "COUNT OF BIRDS" 3 from migration m, 4 birds_migration bm 5 where m.migration_id = bm.migration_id 5 group by m.migration_location 7 having count(bm.bird_id) > 6 8 order by 1; MIGRATION_LOCATION COUNT OF BIRDS ------------------------------ -------------- Central America 12 Mexico 14 Southern United States 18 3 rows selected.
In the following query, the HAVING clause returns only migration locations that have six or fewer types of birds that migrate to each specific location. Verify the following results against the previous output:
SQL> select m.migration_location, 2 count(bm.bird_id) "COUNT OF BIRDS" 3 from migration m, 4 birds_migration bm 5 where m.migration_id = bm.migration_id 6 group by m.migration_location 7 having count(bm.bird_id) <= 6 8 order by 1; MIGRATION_LOCATION COUNT OF BIRDS ------------------------------ -------------- No Significant Migration 5 Partial, Open Water 1 South America 6 3 rows selected.
In this last example, you use the UNION operator to combine two of the previous queries. Each individual SELECT statement in this compound query uses the GROUP BY and HAVING clauses, as well as the aggregate function COUNT to summarize and group the data. The results are combined using the UNION operator, which essentially shows the original results of a single query shown in this section. You can also see that ORDER BY is used only one time in this example, to perform a final sort of the results by MIGRATION_LOCATION. Remember that GROUP BY and HAVING can be used in each individual SELECT statement in a compound query, whereas ORDER BY can be used only at the end of the compound query, to sort the entire data set.
SQL> select m.migration_location,
2 count(bm.bird_id) "COUNT OF BIRDS"
3 from migration m,
4 birds_migration bm
5 where m.migration_id = bm.migration_id
6 group by m.migration_location
7 having count(bm.bird_id) > 6
8 UNION
9 select m.migration_location,
count(bm.bird_id) "COUNT OF BIRDS"
10 from migration m,
11 birds_migration bm
12 where m.migration_id = bm.migration_id
13 group by m.migration_location
14 having count(bm.bird_id) <= 6
15 order by 1;
MIGRATION_LOCATION COUNT OF BIRDS
------------------------------ --------------
Central America 12
Mexico 14
No Significant Migration 5
Partial, Open Water 1
South America 6
Southern United States 18
6 rows selected.
Be cautious when using compound operators. You can return incorrect or incomplete data if you use the INTERSECT operator and you use the wrong SELECT statement as the first individual query. In addition, consider whether you want duplicate records when using the UNION and UNION ALL operators. What about EXCEPT and MINUS? Do you need any of the rows that the second query did not return? As you can see, using the wrong compound query operator or the wrong order of individual queries in a compound query can easily return misleading data.
This hour introduced you to compound queries. All SQL statements discussed before this hour consisted of a single query. Compound queries enable you to use multiple individual queries together as a single query to achieve the data result set you want as output. The compound query operators discussed include UNION, UNION ALL, INTERSECT, and EXCEPT (MINUS). UNION returns the output of two single queries without displaying duplicate rows of data. UNION ALL displays all output of single queries, regardless of existing duplicate rows. INTERSECT returns identical rows between two queries. EXCEPT (MINUS in Oracle) returns the results of one query that do not exist in another query. Compound queries provide greater flexibility when trying to satisfy the requirements of various queries that, without the use of compound operators, can result in complex queries.
Q. How are the columns referenced in the GROUP BY clause in a compound query?
A. The columns can be referenced by the actual column name or by the number of the column placement in the query if the column names are not identical in the two queries.
Q. I understand what the EXCEPT operator does, but would the outcome change if I reversed the SELECT statements?
A. Yes, the order of the individual queries is important when using the EXCEPT or MINUS operator. Remember that all rows are returned from the first query that are not returned by the second query. Changing the order of the two individual queries in the compound query can definitely affect the results.
Q. Must the data type and the length of columns in a compound query be the same in both queries?
A. No, only the data type must be the same; the length can differ.
Q. What determines the column names when using the UNION operator?
A. The first query set determines the column names for the data returned when using a UNION operator.
The following workshop consists of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
1. Match the correct operator to the following statements:
Statement |
Operator |
a. Show duplicates |
|
b. Return only rows from the first query that match those in the second query |
|
c. Return no duplicates |
|
d. Return only rows from the first query that are not returned by the second |
|
2. How many times can ORDER BY be used in a compound query?
3. How many times can GROUP BY be used in a compound query?
4. How many times can HAVING be used in a compound query?
5. Consider a query that uses the EXCEPT (or MINUS) operator. Suppose that the first SELECT statement returns 10 rows of distinct rows, and the second SELECT statement returns 4 distinct rows of data. How many rows of data are returned in the final result set of the compound query?
1. Create a table using the following SQL code; then write a query to select all records from the table.
SQL> create table birds_menu as
2 select b.bird_id, b.bird_name,
3 b.incubation + b.fledging parent_time,
4 f.food_name
5 from birds b,
6 food f,
7 birds_food bf
8 where b.bird_id = bf.bird_id
9 and bf.food_id = f.food_id
10 and f.food_name in ('Crustaceans', 'Insects',
11 'Seeds', 'Snakes')
12 order by 1;2. Issue the following queries and study the results. The first query selects the bird's name from the new table for birds whose parenting time exceeds 85 days. The second query is for those birds in the new table whose parenting time is less than or equal to 85 days. The third query combines both queries using the UNION operator.
SQL> select bird_name 2 from birds_menu 3 where parent_time > 85 4 order by 1; SQL> select bird_name 2 from birds_menu 3 where parent_time <= 85 4 order by 1; SQL> select bird_name 2 from birds_menu 3 where parent_time > 85 4 UNION 5 select bird_name 6 from birds_menu 7 where parent_time <= 85 8 order by 1;
3. Issue the following SQL statement to practice with the previous query using the UNION ALL operator, and compare the results to the previous result set.
SQL> select bird_name 2 from birds_menu 3 where parent_time > 85 4 UNION ALL 5 select bird_name 6 from birds_menu 7 where parent_time <= 85 8 order by 1;
4. Issue the following SQL statement to practice with the INTERSECT operator, and compare the results to the base data in the BIRDS_MENU table.
SQL> select bird_name
2 from birds_menu
3 INTERSECT
4 select bird_name
5 from birds_menu
6 where food_name in ('Insects', 'Snakes')
7 order by 1;5. Issue the following SQL statement to practice with the MINUS operator, and compare the results to the base data in the BIRDS_MENU table.
SQL> select bird_name
2 from birds_menu
3 MINUS
4 select bird_name
5 from birds_menu
6 where food_name in ('Insects', 'Snakes')
7 order by 1;6. Issue the following SQL statement to return a count of the number of food items eaten by each bird in the BIRDS_MENU table.
SQL> select bird_name, count(food_name) 2 from birds_menu 3 group by bird_name;
7. Issue the following SQL statement to use aggregate functions in a query. Study the results closely.
SQL> select bird_name, count(food_name) 2 from birds_menu 3 where parent_time > 100 4 group by bird_name 5 UNION 6 select bird_name, count(food_name) 7 from birds_menu 8 where parent_time < 80 9 group by bird_name;
8. Experiment with some compound queries on your own using the new table you created during these exercises, or for any tables in the BIRDS database, or for any tables you have created thus far.