Hour 25
Bonus Workshop for the Road
What You’ll Learn in This Hour:
▶ Explore the expansion of the sample database used for examples and exercises
▶ Use SQL to define and modify new database objects
▶ Manage transactions within your database to create SQL queries and composite queries with multiple types of joins
▶ Use functions to dig deeper into information and group data sets
▶ Create more advanced queries with views and subqueries
▶ Use SQL to generate SQL code
In this hour, you expand upon the current BIRDS database and walk through numerous additional examples using the SQL knowledge you have learned in this book. Afterward, you perform a final set of exercises. The goal of this bonus hour is to bring everything together while applying the concepts of the SQL language from beginning to end with hands-on, real-world practice. This hour includes very little instruction—mostly you’ll find examples to follow and opportunities to unleash what you have learned. Do not be intimidated by the length of this hands-on chapter; just follow along at your own pace—maybe 1 hour, maybe 10 hours, or maybe more as you explore at your leisure. Reflect on the investment you have made and your progress in learning SQL. Your knowledge of SQL has incubated and the fledging period is over—now it is time for you to fly. Let’s have some fun!
The BIRDS Database
In this hour, you continue to work with the original BIRDS database, adding two data sets (on predators and photographers) that will be integrated with the database. Figure 25.1 is an ERD of the original BIRDS database, for a quick reference to the data and relationships. Refer back to Hour 3, “Getting to Know Your Data,” to see a list of all the data in the BIRDS database. Remember that you can also simply query from each table at any time (SELECT * FROM TABLE_NAME) to view the data before you perform any queries.
FIGURE 25.1
The BIRDS database
For your convenience during this hour, the following is a list of the bird identifications and the name of the birds from the BIRDS table.
SQL> select bird_id, bird_name
2 from birds;
BIRD_ID BIRD_NAME
---------- ------------------------------
1 Great Blue Heron
2 Mallard
3 Common Loon
4 Bald Eagle
5 Golden Eagle
6 Red Tailed Hawk
7 Osprey
8 Belted Kingfisher
9 Canadian Goose
10 Pied-billed Grebe
11 American Coot
12 Common Sea Gull
13 Ring-billed Gull
14 Double-crested Cormorant
15 Common Merganser
16 Turkey Vulture
17 American Crow
18 Green Heron
19 Mute Swan
20 Brown Pelican
21 Great Egret
22 Anhinga
23 Black Skimmer
23 rows selected.
Predators of Birds
The first of the two data sets that will be integrated into the BIRDS database is data about predators of birds. Figure 25.2 provides an ERD for the two tables that will be related to the BIRDS database. As you can see by the relationships in the ERD, a bird can have many predators. Likewise, a predator might prey on many different types of birds. The BIRDS_PREDATORS table is a base table that is used to join the BIRDS table with the PREDATORS table. If you want, you can take this a step further and keep track of the predators that are also eaten by birds. We have not taken the data to this extent at this point, but you can consider what that might look like and try it on your own.
FIGURE 25.2
Predators of birds
Following is the data in both of the predators tables. You can see that the database has 106 rows of data in the database; this is only a subset of the data in the table, to give you an idea of how the data is stored. Take a few minutes to study the output and even make manual comparisons between these new tables and the BIRDS table. For example, what are the predators of the Great Blue Heron?
SQL> select * from birds_predators;
BIRD_ID PRED_ID
---------- ----------
1 1
1 4
1 17
1 18
2 1
2 2
2 4
2 5
2 7
2 9
2 11
2 14
2 17
2 19
2 20
3 4
…………
…………
…………
106 rows selected.
SQL> select * from predators;
PRED_ID PREDATOR
---------- ------------------------------
1 Humans
2 Cats
3 Chipmunks
4 Other Birds
5 Snakes
6 Frogs
7 Dogs
8 Deer
9 Coyotes
10 Reptiles
11 Weasels
12 Foxes
13 Carnivorous Plants
14 Predatory Fish
15 Seals
16 Insects
17 Racoons
18 Bears
19 Skunks
20 Turtles
21 Wolves
22 Cougars
23 Bobcats
24 Alligators
25 Minks
26 Rats
27 Squirrels
28 Opposums
29 Crocodiles
29 rows selected.
Photographers of Birds
The second set of data that will be integrated into the BIRDS database is information about photographers of birds. Figure 25.3 provides an ERD to illustrate the data, relationships, and how everything ties into the BIRDS database through the BIRDS table. Take a few minutes to study Figure 25.3.
FIGURE 25.3
Photographers of birds
Notice the different types of relationships in Figure 25.3. For example, the PHOTOGRAPHERS table has a recursive relationship with itself. This was previously discussed by example in this book: Photographers might mentor other photographers, and photographers might be mentored by other photographers. Remember that a self join is used to relate data in a table to itself.
Following is a list of data in each of the new photographer-related tables. Study the data and make sure you understand how it relates to the BIRDS database and how it might be used. Perform a few manual queries. For example, what is the favorite bird of Steve Hamm? How many different cameras does Gordon Flash use? What photographer mentors Jenny Forest?
In addition to the previous questions, manually answer the following questions just by looking at the lists of data:
▶ Which photographers use a Canon camera?
▶ Which photographers do not use a Canon camera?
▶ Which photographers have the highest photo level?
▶ What equipment does Gordon Flash use?
▶ Which photographer has a favorite bird that is preyed upon by coyotes?
▶ Which photographer has the most diverse styles of photography?
▶ Does any photographer like to take still photographs of the Bald Eagle as a favorite bird?
Note
Explore Your New Data
Use your imagination to ask and answer some of your own questions about the data. Think about how that data might be used in the real world.
SQL> select * from photographers;
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- -------------------- ---------------------- ----------
7 Ryan Notstephens 7
8 Susan Willamson 6
9 Mark Fife 6
1 Shooter McGavin 1
2 Jenny Forest 8 3
3 Steve Hamm 4
4 Harry Henderson 9 5
5 Kelly Hairtrigger 8 2
6 Gordon Flash 1
10 Kate Kapteur 7 4
10 rows selected.
SQL> select * from favorite_birds;
PHOTOGRAPHER_ID BIRD_ID
--------------- ----------
1 3
1 4
1 7
2 19
2 20
2 23
3 1
3 3
4 4
5 8
6 18
7 11
7 16
7 22
8 14
9 4
9 5
10 2
10 6
10 17
10 21
21 rows selected.
SQL> select * from photographer_cameras;
PHOTOGRAPHER_ID CAMERA_ID
--------------- ----------
1 1
2 1
2 8
3 2
3 9
4 3
5 4
6 7
7 1
7 5
7 9
8 2
8 8
9 6
9 9
10 8
16 rows selected.
SQL> select * from cameras;
CAMERA_ID CAMERA
---------- ------------------------------
1 Canon
2 Nikon
3 Sony
4 Olympus
5 GoPro
6 Fujifilm
7 Polaroid
8 Apple iPhone
9 Samsung Galaxy
9 rows selected.
SQL> select * from photo_levels;
LEVEL_ID PHOTO_LEVEL
---------- ------------------------------
1 Beginner
2 Novice
3 Hobbyist
4 Competent
5 Skilled
6 Artist
7 World-class
7 rows selected.
SQL> select * from photographer_styles;
PHOTOGRAPHER_ID STYLE_ID
--------------- ----------
1 3
1 8
2 1
3 2
4 5
4 8
5 3
5 5
5 7
6 2
6 6
7 1
8 5
8 8
9 3
9 4
10 6
10 8
18 rows selected.
SQL> select * from photo_styles;
STYLE_ID STYLE
---------- ------------------------------
1 Action
2 Portrait
3 Landscape
4 Sunset
5 Artistic
6 Close-up
7 Underwater
8 Still
8 rows selected.
Creating the New Tables
This section walks through the process of creating the new tables for predators and photographers that will be integrated into the existing BIRDS database. To give you a clean start, you execute the following scripts that have been provided with this book. It is recommended that you create a new user so that any previous work that you performed is still saved under your original schema; you can then execute the provided scripts without error. Log in to SQL and perform the following steps.
Create the new user (schema owner):
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user your_new_username 2 identified by your_new_passwd;
User created.
SQL> grant dba to your_new_username;
Grant succeeded.
Note
Follow Along
Follow along with all the examples in this hour as the new user that you are creating. You will have the opportunity to perform exercises on your own, without any guidance.
Connect to the database as the new user:
SQL> connect your_new_username Connected. SQL> show user USER is "YOUR_USERNAME"
Execute the provided script called bonus_tables.sql to create the table for this hour.
SQL> start c:\sqlbook\bonus_tables.sql
drop table birds_food
*
ERROR at line 1:
ORA-00942: table or view does not exist
drop table birds_nests
*
ERROR at line 1:
ORA-00942: table or view does not exist
drop table birds_migration
*
ERROR at line 1:
ORA-00942: table or view does not exist
drop table migration
*
ERROR at line 1:
ORA-00942: table or view does not exist
In the previous output, notice that errors were received when attempting to drop tables. This is because the first time you run this script, the tables do not exist—and you cannot drop a table that does not exist. The following output is the second half of the output from executing the bonus_tables.sql script, where the tables are actually created. The output, or feedback, confirms the results of any SQL statement that you issue at the SQL> prompt.
Table created. Table created. Table created. Table created. Table created.
Execute the provided script called bonus_data.sql to insert data into the new tables for this chapter:
SQL> start c:\sqlbook\bonus_data.sql 0 rows deleted. 0 rows deleted. 0 rows deleted. 0 rows deleted. 0 rows deleted. 0 rows deleted. 0 rows deleted.
In the previous output, you can see that zero rows were deleted from the new tables that you just created. This is because no data currently exists in those tables. If you run this script in the future, you will see that rows are deleted and then reinserted into the new tables. In the following second half of the output from the bonus_data.sql script, you can see feedback for every row that is created.
1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created.
Workshop: Describing Your Tables
Use the DESC command to study the structure of each new table during this hour that was incorporated into the BIRDS database.
SQL> desc birds; Name Null? Type -------------------------- -------- -------------------------- BIRD_ID NOT NULL NUMBER(3) BIRD_NAME NOT NULL VARCHAR2(30) HEIGHT NOT NULL NUMBER(4,2) WEIGHT NOT NULL NUMBER(4,2) WINGSPAN NUMBER(4,2) EGGS NOT NULL NUMBER(2) BROODS NUMBER(1) INCUBATION NOT NULL NUMBER(2) FLEDGING NOT NULL NUMBER(3) NEST_BUILDER NOT NULL CHAR(1)
Workshop: Basic Queries
Issue some basic SELECT statements to explore the data within the new tables.
SQL> select * from cameras;
CAMERA_ID CAMERA
---------- ------------------------------
1 Canon
2 Nikon
3 Sony
4 Olympus
5 GoPro
6 Fujifilm
7 Polaroid
8 Apple iPhone
9 Samsung Galaxy
9 rows selected.
Workshop: Adding Tables
Use the CREATE TABLE command to create the following new tables that are related to bird habitats.
SQL> create table habitats 2 (habitat_id number(3) not null primary key, 3 habitat varchar(30) not null); Table created. SQL> create table birds_habitats 2 (habitat_id number(3) not null primary key, 3 bird_id number(3) not null); Table created. SQL> create table habitat_types 2 (habitat_type_id number(3) not null primary key, 3 habitat_type varchar(30) not null); Table created.
Workshop: Manipulating Data
Follow along with the next series of SQL statements to manipulate data within the new tables using Data Manipulation Language and transactional control commands. Study the results between each SQL statement.
SQL> select * from photographers;
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- ------------------------- ---------------------- ----------
7 Ryan Notstephens 7
8 Susan Willamson 6
9 Mark Fife 6
1 Shooter McGavin 1
2 Jenny Forest 8 3
3 Steve Hamm 4
4 Harry Henderson 9 5
5 Kelly Hairtrigger 8 2
6 Gordon Flash 1
10 Kate Kapteur 7 4
10 rows selected.
SQL> insert into photographers
2 values (11, 'Sam Song', null, 4);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from photographers;
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- ------------------------- ---------------------- ----------
7 Ryan Notstephens 7
8 Susan Willamson 6
9 Mark Fife 6
1 Shooter McGavin 1
2 Jenny Forest 8 3
3 Steve Hamm 4
4 Harry Henderson 9 5
5 Kelly Hairtrigger 8 2
6 Gordon Flash 1
10 Kate Kapteur 7 4
11 Sam Song 4
11 rows selected.
SQL> update photographers set level_id = 5
2 where photographer_id = 11;
1 row updated.
SQL> select * from photographers;
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- ------------------------- ---------------------- ----------
7 Ryan Notstephens 7
8 Susan Willamson 6
9 Mark Fife 6
1 Shooter McGavin 1
2 Jenny Forest 8 3
3 Steve Hamm 4
4 Harry Henderson 9 5
5 Kelly Hairtrigger 8 2
6 Gordon Flash 1
10 Kate Kapteur 7 4
11 Sam Song 5
11 rows selected.
SQL> rollback;
Rollback complete.
SQL> select * from photographers;
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- ------------------------- ---------------------- ----------
7 Ryan Notstephens 7
8 Susan Willamson 6
9 Mark Fife 6
1 Shooter McGavin 1
2 Jenny Forest 8 3
3 Steve Hamm 4
4 Harry Henderson 9 5
5 Kelly Hairtrigger 8 2
6 Gordon Flash 1
10 Kate Kapteur 7 4
11 Sam Song 4
11 rows selected.
SQL> delete from photographers
2 where photographer_id = 11;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from photographers;
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- ------------------------- ---------------------- ----------
7 Ryan Notstephens 7
8 Susan Willamson 6
9 Mark Fife 6
1 Shooter McGavin 1
2 Jenny Forest 8 3
3 Steve Hamm 4
4 Harry Henderson 9 5
5 Kelly Hairtrigger 8 2
6 Gordon Flash 1
10 Kate Kapteur 7 4
10 rows selected.
Workshop: Joining Tables
Issue the following SQL statements to join three tables in a query. Notice that the table names are fully qualified in the first query and aliases are not used. The second query uses aliases when joining the tables.
SQL> select photographers.photographer, cameras.camera 2 from photographers, cameras, photographer_cameras 3 where photographers.photographer_id = photographer_cameras.camera_id 4 and cameras.camera_id = photographer_cameras.camera_id 5 order by photographer; PHOTOGRAPHER CAMERA ------------------------------ ------------------------------ Gordon Flash Fujifilm Harry Henderson Olympus Jenny Forest Nikon Jenny Forest Nikon Kelly Hairtrigger GoPro Mark Fife Samsung Galaxy Mark Fife Samsung Galaxy Mark Fife Samsung Galaxy Ryan Notstephens Polaroid Shooter McGavin Canon Shooter McGavin Canon Shooter McGavin Canon Steve Hamm Sony Susan Willamson Apple iPhone Susan Willamson Apple iPhone Susan Willamson Apple iPhone 16 rows selected. SQL> select p.photographer, c.camera 2 from photographers p, 3 cameras c, 4 photographer_cameras pc 5 where p.photographer_id = pc.photographer_id 6 and c.camera_id = pc.camera_id 7 order by 1; PHOTOGRAPHER CAMERA ------------------------------ ------------------------------ Gordon Flash Polaroid Harry Henderson Sony Jenny Forest Apple iPhone Jenny Forest Canon Kate Kapteur Apple iPhone Kelly Hairtrigger Olympus Mark Fife Fujifilm Mark Fife Samsung Galaxy Ryan Notstephens Samsung Galaxy Ryan Notstephens Canon Ryan Notstephens GoPro Shooter McGavin Canon Steve Hamm Samsung Galaxy Steve Hamm Nikon Susan Willamson Apple iPhone Susan Willamson Nikon 16 rows selected.
Following is an example of a self join. This example joins the PHOTOGRAPHERS table to itself to get information about photographers that mentor other photographers. This is also an example that was previously used.
SQL> select p1.photographer photographer, 2 p2.photographer mentor 3 from photographers p1, 4 photographers p2 5 where p2.photographer_id = p1.mentor_photographer_id 6 order by 1; PHOTOGRAPHER MENTOR ------------------------------ ------------------------------ Harry Henderson Mark Fife Jenny Forest Susan Willamson Kate Kapteur Ryan Notstephens Kelly Hairtrigger Susan Willamson 4 rows selected.
The following SQL statements demonstrate the concept of an outer join in a query.
SQL> select * from photographers;
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- ------------------------- ---------------------- ----------
7 Ryan Notstephens 7
8 Susan Willamson 6
9 Mark Fife 6
1 Shooter McGavin 1
2 Jenny Forest 8 3
3 Steve Hamm 4
4 Harry Henderson 9 5
5 Kelly Hairtrigger 8 2
6 Gordon Flash 1
10 Kate Kapteur 7 4
10 rows selected.
SQL> select p1.photographer photographer, p2.photographer mentor
2 from photographers p1, photographers p2
3 where p2.photographer_id(+) = p1.mentor_photographer_id
4 order by 1;
PHOTOGRAPHER MENTOR
------------------------------ ------------------------------
Gordon Flash
Harry Henderson Mark Fife
Jenny Forest Susan Willamson
Kate Kapteur Ryan Notstephens
Kelly Hairtrigger Susan Willamson
Mark Fife
Ryan Notstephens
Shooter McGavin
Steve Hamm
Susan Willamson
10 rows selected.
Workshop: Comparison Operators
Follow along with the next examples to refresh your memory on comparison operators.
SQL> select bird_name from birds
2 where bird_name = 'Bald Eagle';
BIRD_NAME
------------------------------
Bald Eagle
1 row selected.
SQL> select * from cameras
2 where camera != 'Canon';
CAMERA_ID CAMERA
---------- ----------------------------
2 Nikon
3 Sony
4 Olympus
5 GoPro
6 Fujifilm
7 Polaroid
8 Apple iPhone
9 Samsung Galaxy
8 rows selected.
SQL> select bird_id, bird_name, wingspan
2 from birds
3 where wingspan > 48;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
1 Great Blue Heron 78
3 Common Loon 54
4 Bald Eagle 84
5 Golden Eagle 90
7 Osprey 72
9 Canadian Goose 72
13 Ring-billed Gull 50
14 Double-crested Cormorant 54
16 Turkey Vulture 72
19 Mute Swan 94.8
20 Brown Pelican 90
21 Great Egret 67.2
12 rows selected.
SQL> select bird_id, bird_name, wingspan
2 from birds
3 where wingspan <= 20;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
2 Mallard 3.2
10 Pied-billed Grebe 6.5
12 Common Sea Gull 18
23 Black Skimmer 15
4 rows selected.
Workshop: Logical Operators
The following examples use logical operators in queries to test the data that is returned.
SQL> select *
2 from photographers
3 where mentor_photographer_id is null;
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- ------------------------- ---------------------- ----------
7 Ryan Notstephens 7
8 Susan Willamson 6
9 Mark Fife 6
1 Shooter McGavin 1
3 Steve Hamm 4
6 Gordon Flash 1
6 rows selected.
SQL> select *
2 from photographers
3 where mentor_photographer_id is not null;
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- ------------------------- ---------------------- ----------
2 Jenny Forest 8 3
4 Harry Henderson 9 5
5 Kelly Hairtrigger 8 2
10 Kate Kapteur 7 4
4 rows selected.
SQL> select bird_id, bird_name, wingspan
2 from birds
3 where wingspan between 30 and 65;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
3 Common Loon 54
6 Red Tailed Hawk 48
13 Ring-billed Gull 50
14 Double-crested Cormorant 54
15 Common Merganser 34
17 American Crow 39.6
22 Anhinga 42
7 rows selected.
SQL> select *
2 from migration
3 where migration_location in ('Central America', 'South America');
MIGRATION_ID MIGRATION_LOCATION
------------ ------------------------------
3 Central America
4 South America
2 rows selected.
SQL> select bird_name
2 from birds
3 where bird_name like '%Eagle%';
BIRD_NAME
------------------------------
Bald Eagle
Golden Eagle
2 rows selected.
SQL> select bird_name
2 from birds
3 where bird_name like '%Eagle';
BIRD_NAME
------------------------------
Bald Eagle
Golden Eagle
2 rows selected.
Workshop: Conjunctive Operators
The following queries are examples of using conjunctive operators, which consist of the AND and OR operators. Remember that, with the AND operator, both conditions on each side of the operator must be true for data to be returned. With the OR operator, either condition on surrounding the OR operator must be true for data to be returned by the query.
SQL> select * from predators
2 where predator = 'Crocodiles'
3 and predator = 'Snakes';
no rows selected
SQL> select * from predators
2 where predator = 'Crocodiles'
3 or predator = 'Snakes';
PRED_ID PREDATOR
---------- ------------------------------
5 Snakes
29 Crocodiles
2 rows selected.
Workshop: Arithmetic Operators
The following example refreshes your memory on the use of arithmetic operators in a query. This is a simple and useful concept, but make sure that your math is correct; otherwise, the query might return incorrect data.
SQL> select bird_name, eggs * broods "EGGS PER SEASON" 2 from birds 3 where wingspan > 48 4 order by 2 desc; BIRD_NAME EGGS PER SEASON ------------------------------ --------------- Canadian Goose 10 Mute Swan 8 Great Blue Heron 5 Ring-billed Gull 4 Brown Pelican 4 Double-crested Cormorant 4 Osprey 4 Great Egret 3 Golden Eagle 3 Common Loon 2 Bald Eagle 2 Turkey Vulture 2 12 rows selected.
Workshop: Character Functions
Character functions are useful in changing the way data appears in the output of a query. They can also be used to change the way data is viewed by a query while searching the data. Following are several examples. Take time to carefully study the results; refer back to previous hours of instruction, if necessary.
SQL> select 'Bald Eagle'
2 from photographers;
'BALDEAGLE
----------
Bald Eagle
Bald Eagle
Bald Eagle
Bald Eagle
Bald Eagle
Bald Eagle
Bald Eagle
Bald Eagle
Bald Eagle
Bald Eagle
10 rows selected.
SQL> select 'Bald Eagle'
2 from birds
3 where bird_name = 'Great Blue Heron';
'BALDEAGLE
----------
Bald Eagle
1 row selected.
SQL> select p2.photographer || ' mentors ' ||
2 p1.photographer || '.' "MENTORS"
3 from photographers p1, photographers p2
4 where p1.mentor_photographer_id = p2.photographer_id;
MENTORS
------------------------------------------------------------
Ryan Notstephens mentors Kate Kapteur.
Susan Willamson mentors Jenny Forest.
Susan Willamson mentors Kelly Hairtrigger.
Mark Fife mentors Harry Henderson.
4 rows selected.
SQL> select photographer,
2 decode(mentor_photographer_id, null, 'None',
3 mentor_photographer_id) "MENTOR"
4 from photographers;
PHOTOGRAPHER MENTOR
------------------------------ ------------------------
Ryan Notstephens None
Susan Willamson None
Mark Fife None
Shooter McGavin None
Jenny Forest 8
Steve Hamm None
Harry Henderson 9
Kelly Hairtrigger 8
Gordon Flash None
Kate Kapteur 7
10 rows selected.
SQL> select bird_name,
2 decode(nest_builder, 'M', 'Male', 'F', 'Female', 'B', 'Both',
3 'Neither') "NESTER"
4 from birds
5 where nest_builder != 'B';
BIRD_NAME NESTER
------------------------------ -------
Mallard Female
Canadian Goose Female
Common Merganser Female
Turkey Vulture Neither
American Crow Female
Brown Pelican Female
6 rows selected.
SQL> select * from cameras;
CAMERA_ID CAMERA
---------- ------------------------------
1 Canon
2 Nikon
3 Sony
4 Olympus
5 GoPro
6 Fujifilm
7 Polaroid
8 Apple iPhone
9 Samsung Galaxy
9 rows selected.
SQL> select upper(substr(camera, 1, 3)) ABBR
2 from cameras;
ABBR
------------
CAN
NIK
SON
OLY
GOP
FUJ
POL
APP
SAM
9 rows selected.
SQL> select substr(photographer, 1, instr(photographer, ' ', 1, 1))
2 first_name
3 from photographers;
FIRST_NAME
-------------------------------------------
Ryan
Susan
Mark
Shooter
Jenny
Steve
Harry
Kelly
Gordon
Kate
10 rows selected.
SQL> select substr(photographer, instr(photographer, ' ', 1, 1) +1) last_name
2 from photographers;
LAST_NAME
-------------------------------------------
Notstephens
Willamson
Fife
McGavin
Forest
Hamm
Henderson
Hairtrigger
Flash
Kapteur
10 rows selected.
Workshop: Aggregating Data
The capability to aggregate data is an important feature when constructing SQL queries. Follow along with the simple examples here. Study the results before you move on to the next section, which groups data output from aggregate functions.
SQL> select p1.photographer, p2.photographer "MENTOR"
2 from photographers p1, photographers p2
3 where p1.mentor_photographer_id = p2.photographer_id;
PHOTOGRAPHER MENTOR
------------------------------ ------------------------------
Kate Kapteur Ryan Notstephens
Jenny Forest Susan Willamson
Kelly Hairtrigger Susan Willamson
Harry Henderson Mark Fife
4 rows selected.
SQL> select count(mentor_photographer_id) "TOTAL PHOTOGRAPHERS MENTORED"
2 from photographers;
TOTAL PHOTOGRAPHERS MENTORED
----------------------------
4
1 row selected.
SQL> select count(distinct(mentor_photographer_id)) "TOTAL MENTORS"
2 from photographers;
TOTAL MENTORS
-------------
3
1 row selected.
SQL> select sum(eggs * broods) "TOTAL EGGS LAYED BY ALL BIRDS IN A SEASON"
2 from birds;
TOTAL EGGS LAYED BY ALL BIRDS IN A SEASON
-----------------------------------------
127
1 row selected.
SQL> select max(wingspan)
2 from birds;
MAX(WINGSPAN)
-------------
94.8
1 row selected.
SQL> select avg(wingspan)
2 from birds;
AVG(WINGSPAN)
-------------
50.5695652
1 row selected.
Workshop: GROUP BY and HAVING
These examples use the GROUP BY and HAVING clauses in SQL queries to further massage the output of aggregate functions before the final result set is returned.
SQL> select photo_levels.photo_level, 2 count(photographers.photographer_id) "NUMBER OF PHOTOGRAPHERS" 3 from photo_levels, photographers 4 where photo_levels.level_id = photographers.level_id 5 group by photo_levels.photo_level; PHOTO_LEVEL NUMBER OF PHOTOGRAPHERS ------------------------------ ----------------------- Artist 2 World-class 1 Beginner 2 Competent 2 Novice 1 Skilled 1 Hobbyist 1 7 rows selected. SQL> select photo_levels.photo_level, 2 count(photographers.photographer_id) "NUMBER OF PHOTOGRAPHERS" 3 from photo_levels, photographers 4 where photo_levels.level_id = photographers.level_id 5 group by photo_levels.photo_level 6 having count(photographers.photographer_id) > 1; PHOTO_LEVEL NUMBER OF PHOTOGRAPHERS ------------------------------ ----------------------- Artist 2 Beginner 2 Competent 2 3 rows selected.
Workshop: Composite Queries
Composite queries combine the result sets of two or more queries. Several examples follow.
SQL> select bird_name from birds
2 UNION
3 select predator from predators;
BIRD_NAME
------------------------------
Alligators
American Coot
American Crow
Anhinga
Bald Eagle
Bears
Belted Kingfisher
Black Skimmer
Bobcats
Brown Pelican
Canadian Goose
Carnivorous Plants
Cats
Chipmunks
Common Loon
Common Merganser
Common Sea Gull
Cougars
Coyotes
Crocodiles
Deer
Dogs
Double-crested Cormorant
Foxes
Frogs
Golden Eagle
Great Blue Heron
Great Egret
Green Heron
Humans
Insects
Mallard
Minks
Mute Swan
Opposums
Osprey
Other Birds
Pied-billed Grebe
Predatory Fish
Racoons
Rats
Red Tailed Hawk
Reptiles
Ring-billed Gull
Seals
Skunks
Snakes
Squirrels
Turkey Vulture
Turtles
Weasels
Wolves
52 rows selected.
SQL> select photographer,
2 decode(mentor_photographer_id, null, 'Not Mentored',
3 'Mentored') "MENTORED?"
4 from photographers
5 UNION
6 select photographer,
7 decode(mentor_photographer_id, null, 'Mentored',
8 'Mentored') "MENTORED?"
9 from photographers
10 where mentor_photographer_id is not null;
PHOTOGRAPHER MENTORED?
------------------------------ ------------
Gordon Flash Not Mentored
Harry Henderson Mentored
Jenny Forest Mentored
Kate Kapteur Mentored
Kelly Hairtrigger Mentored
Mark Fife Not Mentored
Ryan Notstephens Not Mentored
Shooter McGavin Not Mentored
Steve Hamm Not Mentored
Susan Willamson Not Mentored
10 rows selected.
SQL> select photographer,
2 decode(mentor_photographer_id, null, 'Not Mentored',
3 'Mentored') "MENTORED?"
4 from photographers
5 UNION
6 select photographer,
7 decode(mentor_photographer_id, null, 'Mentored',
8 'Mentored') "MENTORED?"
9 from photographers
10 where mentor_photographer_id is not null;
PHOTOGRAPHER MENTORED?
------------------------------ ------------
Gordon Flash Not Mentored
Harry Henderson Mentored
Jenny Forest Mentored
Kate Kapteur Mentored
Kelly Hairtrigger Mentored
Mark Fife Not Mentored
Ryan Notstephens Not Mentored
Shooter McGavin Not Mentored
Steve Hamm Not Mentored
Susan Willamson Not Mentored
10 rows selected.
SQL> select photographer
2 from photographers
3 where level_id > 4
4 INTERSECT
5 select photographer
6 from photographers, photographer_cameras, cameras
7 where photographers.photographer_id =
8 photographer_cameras.photographer_id
9 and photographer_cameras.camera_id = cameras.camera_id
10 and camera not in ('Apple iPhone', 'Samsung Galaxy');
PHOTOGRAPHER
------------------------------
Harry Henderson
Mark Fife
Ryan Notstephens
Susan Willamson
4 rows selected.
SQL> select photographer
2 from photographers
3 where level_id > 4
4 MINUS
5 select photographer
6 from photographers, photographer_cameras, cameras
7 where photographers.photographer_id =
8 photographer_cameras.photographer_id
9 and photographer_cameras.camera_id = cameras.camera_id
10 and camera not in ('Apple iPhone', 'Samsung Galaxy');
no rows selected
Workshop: Creating Tables from Existing Tables
The results of a query can easily be used to create a new table that is based on a result set from one or more existing tables. Try the following example.
SQL> create table old_cameras as
2 select * from cameras;
Table created.
SQL> select * from old_cameras;
CAMERA_ID CAMERA
---------- ------------------------------
1 Canon
2 Nikon
3 Sony
4 Olympus
5 GoPro
6 Fujifilm
7 Polaroid
8 Apple iPhone
9 Samsung Galaxy
9 rows selected.
Workshop: Inserting Data into a Table from Another Table
The next example walks through truncating the new table that was just created to remove the data and then inserting data into that table based on the results of a query. This is similar to the previous example that used the CREATE TABLE statement; the concept is the same.
SQL> truncate table old_cameras;
Table truncated.
SQL> select * from old_cameras;
no rows selected
SQL> insert into old_cameras
2 select * from cameras;
9 rows created.
SQL> select * from old_cameras;
CAMERA_ID CAMERA
---------- ------------------------------
1 Canon
2 Nikon
3 Sony
4 Olympus
5 GoPro
6 Fujifilm
7 Polaroid
8 Apple iPhone
9 Samsung Galaxy
9 rows selected.
Workshop: Creating Views
Views are arguably one of the most powerful features of SQL when querying the database, especially with complex data sets. Follow along with these examples and create some views of your own. Remember that a view is a virtual table and does not actually store data. The results of a view are just like a query. The data set is stored temporarily in memory, not written physically to the database.
SQL> create or replace view fish_eaters as
2 select b.bird_id, b.bird_name
3 from birds b,
4 birds_food bf,
5 food f
6 where b.bird_id = bf.bird_id
7 and bf.food_id = f.food_id
8 and f.food_name = 'Fish';
View created.
SQL> select * from fish_eaters;
BIRD_ID BIRD_NAME
---------- ------------------------------
1 Great Blue Heron
3 Common Loon
4 Bald Eagle
5 Golden Eagle
7 Osprey
8 Belted Kingfisher
12 Common Sea Gull
13 Ring-billed Gull
14 Double-crested Cormorant
15 Common Merganser
17 American Crow
18 Green Heron
20 Brown Pelican
21 Great Egret
22 Anhinga
23 Black Skimmer
16 rows selected.
SQL> create or replace view predators_of_big_birds as
2 select distinct(p.predator)
3 from predators p,
4 birds_predators bp,
5 birds b
6 where p.pred_id = bp.pred_id
7 and b.bird_id = bp.bird_id
8 and b.wingspan > 48;
View created.
SQL> select * from predators_of_big_birds;
PREDATOR
------------------------------
Humans
Other Birds
Predatory Fish
Seals
Coyotes
Weasels
Cougars
Bobcats
Opposums
Bears
Skunks
Wolves
Foxes
Racoons
14 rows selected.
Workshop: Embedding Subqueries
The concept of a subquery is powerful as well. A subquery is simply a query that is embedded within another query. Remember that the innermost subquery is always resolved first in an SQL statement. Try this one:
SQL> select *
2 from photographers
3 where level_id >
4 (select avg(level_id)
5 from photo_levels);
PHOTOGRAPHER_ID PHOTOGRAPHER MENTOR_PHOTOGRAPHER_ID LEVEL_ID
--------------- ------------------------- ---------------------- ----------
7 Ryan Notstephens 7
8 Susan Willamson 6
9 Mark Fife 6
4 Harry Henderson 9 5
4 rows selected.
Workshop: Creating Views from Subqueries
Following is an example of creating a view based on a subquery. Then a query is written to join the view to other database tables.
SQL> select avg(level_id)
2 from photo_levels;
AVG(LEVEL_ID)
-------------
4
1 row selected.
SQL> create or replace view top_photographers as
2 select *
3 from photographers
4 where level_id >
5 (select avg(level_id)
6 from photo_levels);
View created.
SQL> select distinct(ps2.style)
2 from photographer_styles ps,
3 top_photographers tp,
4 photo_styles ps2
5 where ps.photographer_id = tp.photographer_id
6 and ps.style_id = ps2.style_id
7 order by 1;
STYLE
------------------------------
Action
Artistic
Landscape
Still
Sunset
5 rows selected.
Workshop: Generating SQL Code from a SQL Statement
Let’s say that you want to create a new user account for every photographer in the database. This is a simple example of why you might need to generate SQL statements automatically. Only 10 photographers are included here, but what if the database listed 1,000 photographers? A query such as the following can be written using a combination of literal strings and character functions to generate output that corresponds to the syntax of the CREATE USER command. Try this one, and see if you can think of other situations when it might be useful to generate SQL code from a SQL statement.
SQL> select 'CREATE USER ' || 2 substr(photographer, 1, instr(photographer, ' ', 1, 1) -1) || 3 '_' || 4 substr(photographer, instr(photographer, ' ', 1, 1) +1) || 5 ' IDENTIFIED BY NEW_PASSWORD_1;' "SQL CODE TO CREATE NEW USERS" 6 from photographers; SQL CODE TO CREATE NEW USERS --------------------------------------------------------------- CREATE USER Ryan_Notstephens IDENTIFIED BY NEW_PASSWORD_1; CREATE USER Susan_Willamson IDENTIFIED BY NEW_PASSWORD_1; CREATE USER Mark_Fife IDENTIFIED BY NEW_PASSWORD_1; CREATE USER Shooter_McGavin IDENTIFIED BY NEW_PASSWORD_1; CREATE USER Jenny_Forest IDENTIFIED BY NEW_PASSWORD_1; CREATE USER Steve_Hamm IDENTIFIED BY NEW_PASSWORD_1; CREATE USER Harry_Henderson IDENTIFIED BY NEW_PASSWORD_1; CREATE USER Kelly_Hairtrigger IDENTIFIED BY NEW_PASSWORD_1; CREATE USER Gordon_Flash IDENTIFIED BY NEW_PASSWORD_1; CREATE USER Kate_Kapteur IDENTIFIED BY NEW_PASSWORD_1; 10 rows selected.
Summary
In this hour, you walked through numerous examples touching on most of the SQL topics you covered in this book. Hopefully this has been an enjoyable journey. Of course, you will have so much more to learn as you get more practice with SQL. Thank you for taking the time to start learning SQL using this book. The information technology world offers many opportunities for those who truly understand relational databases and know how to utilize data for business intelligence. With SQL, you can be much more effective in your daily job and help make your organization more competitive in today’s global market. Enjoy this final batch of exercises.
Workshop
The following workshop consists of only an assortment of exercises during this hour, since this hour is a workshop itself. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Exercises
1. Select data from your tables to explore your data, starting with the following:
▶ Select all the data from the
BIRDStable.▶ Select all the data from the
PHOTOGRAPHERStable.▶ What are the predators of the Bald Eagle?
2. When looking at the ERD for this database, you can see that a relationship is missing between the
PHOTOGRAPHERSandPHOTOStables.▶ What kind of relationship should be added?
▶ Use SQL to define a relationship between these two tables in your database. (Hint: add any necessary foreign key constraints.)
3. Add a table called
COLORSfor colors that each bird might have and create any other necessary tables and relationships (primary and foreign keys) to create a relationship betweenBIRDSandCOLORS. If you want, you can save yourCREATE TABLEstatements in a file to easily regenerate at a later time, or you can add them to your maintables.sql script. If you add these statements to the maintables.sqlscript, be sure that theDROPandCREATE TABLEstatements are in the proper order in relation to the other tables in your database, based on primary/foreign key relationships (for example, you must drop child tables before parent tables, and you must create parent tables before child tables).4. Insert a few rows of data into the
COLORStable and any other tables you may have created.5. Add the following bird to your database: Easter Kingbird, 9 inches, .2 pounds, 15-inch wingspan, 5 eggs, up to 4 broods per season, incubation period of 17 days, fledging period of 17 days, both participate with the nest.
6. Add the following photographer to your database: Margaret Hatcher, mentored by Susan Williamson, hobbyist level.
7. Make sure you have saved your recent transactions. If you want, you can save the previous
INSERTstatements to a file so that you can regenerate this data later if you need to, or you can add them to the main data.sql script.8. Generate a simple list of all photographers, skill levels, and styles. Then sort the results, showing the photographers with the highest skill levels first in your results.
9. Generate a list of photographers and their protégés (the photographers they mentor). List only the photographers that are mentors.
10. Re-create the same list of photographers and their protégés, but show all photographers, regardless of whether they mentor anyone, and sort the results by the photographer with the highest skill levels first.
11. Using
UNIONto combine the results of multiple queries, show a list of all animals in your database (for example, birds and predators).12. Create a table called
FORMER_PHOTOGRAPHERSthat is based on thePHOTOGRAPHERStable.13. Truncate the table
FORMER_PHOTOGRAPHERS.14. Insert all data from the
PHOTOGRAPHERStable into theFORMER_PHOTOGRAPHERStable.15. Can you think of any views that can be created to make the exercises from the previous section easier, or more easily repeatable, for similar future queries?
16. Create at least one view that you can imagine.
17. Write a query that produces a list of the most versatile photographers in your database. For example, the query might contain one or more of the following subqueries:
▶ Photographers that have a skill level greater than the average skill level of all photographers
▶ Photographers whose count of birds of interest is more than the average of all photographers
▶ Photographers who have more styles than the average photographer
18. Create a view called
Versatile_Photographersthat is based on the subqueries you used earlier.19. Suppose that each photographer needs a database user account to access information in the database about birds and other photographers. Generate a SQL script that creates database user accounts for each photographer with the following syntax:
CREATE USER MARGARET_HATCHER IDENTIFIED BY NEW_PASSWORD_1;
20. What are the predators of each bird?
21. Which birds have the most predators?
22. Which predators eat the most birds in your database?
23. Which predators eat only fish-eating birds?
24. Which types of nests attract the most predators?
25. Which photographers are most likely to see a crocodile when capturing an image of a bird?
26. Which predators are most likely to be in a landscape-style photograph?
27. What birds have a diet more diverse than the average bird in the database?
28. What cameras are used by these photographers:
▶ Photographers that mentor others
▶ Photographers with a skill level above average