Hour 4
Setting Up Your Database
What You’ll Learn in This Hour:
▶ Locating the files you need for this book
▶ Downloading the database software used for examples in this book
▶ Understanding examples of other database software products available for download
▶ Creating the tables and data used in this book
▶ Getting to know the specific data in the
Birdsdatabase▶ Manually querying the bird database as a pathway into SQL code
In this hour, you look at the database software used for the examples in this book and check out the specific data in the bird database. You see how to download the software and also learn about other popular relational database software products available for download. After you install the appropriate database software, you can create your tables and data using the scripts provided with this book. Then you delve into the actual data and begin using that data to gather some basic information. Finally, you learn to manually query the data that is listed. Coding SQL is almost as simple as asking questions that anybody in your organization might ask about the data stored. Let’s do this.
Locating the Files You Need
The file, or script, named tables.sql has the code to create the tables that will comprise your Birds database. The script data.sql has the code to insert the data into your tables. You can find these two files on the book’s website, http:\\www.informit.com/9780137543120. When you download these files, make a note of the destination folder. The recommendation is to create a folder named c:\sqlbook\file_name for easy reference throughout the hands-on exercises in this book.
Getting Set Up for Hands-on Exercises
In this section, you learn how to download and install the database software used in this book. You also check out other options for database software and learn how to create a user with the required privileges in your database to create and manage objects. Additionally, you use the files created with this book to build the tables and then enter data into those tables that is used for all the examples and exercises in this book.
Downloading and Installing the Database Software
These are guidelines for installing a free version of the Oracle database that is used for the examples and hands-on exercises. Note that websites change, however; the destination of the free download could be different when you read this. Neither the author nor Pearson Education places any warranties on either the software or software support. For help with installation problems, or to inquire about software support, refer to the particular implementation’s documentation or contact customer support for the specific implementation.
First, navigate to www.oracle.com in your web browser.
Click the Products link in the main menu (see Figure 4.1).
FIGURE 4.1
Oracle’s website (image © 2021 Oracle)Then click the Oracle Database link (see Figure 4.2).
FIGURE 4.2
Download the Oracle Database product (image © 2021 Oracle)Click Download Oracle Database 19c (see Figure 4.3).
FIGURE 4.3
Oracle Database download (image © 2021 Oracle)Scroll down and click Oracle Database 18c Express Edition (see Figure 4.4). Any version of the Oracle database works for the examples and hands-on exercises in this book. The recommendation is to select the latest version that has a license for free use.
FIGURE 4.4
Oracle Database Express link (image © 2021 Oracle)Select the Oracle Database download that applies to the operating system you are using (see Figure 4.5); in most cases, this is probably Windows. After downloading, install the software and follow the onscreen instructions. Make a note of any usernames, passwords, or destination folders for files.
FIGURE 4.5
Oracle choices for the appropriate operating system (image © 2021 Oracle)
Note
Websites Change
As you know, websites change constantly. These instructions illustrate how to download the latest free version of Oracle Express as of the publication date of this book. Almost any version of the Oracle database is adequate for this book; the next section references other database implementation options.
Other Options for Database Software
So many vendors offer so many implementations of the relational database that this book cannot adequately show examples or syntax from even the top implementations. We have chosen Oracle for this book, but the examples stay as close to the SQL standard as possible so that they can easily be adapted to other database implementations. Following are some of the most popular implementations available as of the publication of this book, along with websites where you can access their database software. Many software products are available for free download or are freely distributed for personal, development, and educational purposes.
Oracle Database XE |
|
Oracle Live SQL |
|
Microsoft SQL Server |
|
MySQL |
|
PostgreSQL |
|
MariaDB |
|
SQLite |
|
Firebirdsql |
|
InterBase |
|
DB2 Express-C |
|
CUBRID |
Note
RDBMS Recommendation for This Book
Oracle is the recommended database management system to use for this book—not because Oracle is necessarily the right database for you or your organization, but because you will have a seamless transition from examples to hands-on exercises. The examples and exercises in this book are simple and stay as close to the SQL standard as possible. Any code used in this book should thus be portable to most relational database management systems, with minor adjustments to vendor-specific SQL syntax.
Create a User with Required Privileges
Before you run the scripts to create the tables and data used in this book, you must create a database user within your database that has privileges to create and manage objects in the database. You then can run the scripts to build the tables and do everything else that you need to do within the scope of this book.
After you have installed the Oracle database software, do the following:
Click the Start button (if you are using Windows).
Type
sqlplusin the search bar.Click SQL Plus.
Figure 4.6 shows the SQL prompt after completing these steps.
FIGURE 4.6
The SQL prompt (image © 2021 Oracle)
Log in at the SQL> prompt with the SYSTEM, SYS, or other administrative username and password that you set up when you installed the software.
Note
The SQL Command-Line Prompt
The examples in this class use the SQL command-line prompt, which is text based. This is because the code shown is as simple as possible, for learning purposes. You physically type the SQL code instead of using a product to generate the code because that is more effective for the initial learning. Many vendor-specific products provide a graphical interface for interacting with the database using SQL.
Now execute the following commands at the SQL> prompt to create a user for yourself to use for exercises throughout this book.
SQL> alter session set "_ORACLE_SCRIPT"=true; Session altered. SQL> create user your_username 2 identified by your_passwd; User created. SQL> grant dba to your_username; Grant succeeded. SQL> connect your_username Connected. SQL> SQL> show user USER is "YOUR_USERNAME" SQL>
From now on, you will log in with this new user account to create tables, manage data, and query the database using SQL.
Note
Caution with the DBA Role
Somebody once said, “With great power comes great responsibility.” This is true of the DBA role in a relational database—as well as any kind of administrative role on any information system. Anyone with an administrative role has access to a lot of information and can easily—and erroneously—delete data or make changes to a database and have a negative impact. In this situation, the DBA role is being granted because this is your personal database. If you are using an organization’s database for the exercises in this book, you will most likely be granted privileges only to connect to the database and to create and manage objects within the database.
Create the Tables and Data for the Birds Database
At this point, you should have downloaded and installed a relational database management system product. You also should have created a user within your database that has the required privileges to create and manage database objects. Primarily, the objects the text is referring to are tables in the database. In the previous hour, you learned about entities and attributes because a database is logically represented. When the database is built using SQL commands such as the ones provided in your scripts, the entities are actually tables and the attributes are columns. At various points throughout this book, additional objects are discussed as alternatives for maximizing the use of your data.
Next, you create the tables that comprise the Birds database by issuing the following command at the SQL> prompt. The start command executes the file, and the filename is the location of the files that you downloaded for examples and hands-on exercises.
SQL> start c:\sqlbook\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—you cannot drop a table that does not exist. The following output is the second half of the output from executing the tables.sql script, where the tables are actually created. The output, or feedback, gives you confirmation of the results of any SQL statement that you issue at the SQL> prompt.
Table created. Table created. Table created. Table created. Table created. SQL>
Now you need to create the data. You do this by also issuing the start command at the SQL prompt and executing the file called data.sql. Notice in the following output that zero rows are deleted. This is because the first time you run this script, there is no data in the tables—and you cannot delete data that does not exist. If you run this script again later with existing data in the tables, you will receive feedback that rows are deleted.
SQL> start c:\sqlbook\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 following second half of the output from the 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. SQL>
List of Data by Table
Now it’s time to look at the data that comprises the Birds database. This is a small database for learning purposes so it is not difficult to have a complete list of the data contained in it. Obviously, if this was a larger database with hundreds or thousands of tables and millions of rows of data, it would not be feasible to have a hard copy list of the data. However, even in a situation like that, you would ideally have diagrams and can always query the database to get a list of all the objects and columns within the database. You can also query the database at any time to get samples of data from various tables of your choosing within the database to see how the data looks.
The list of data follows for each table in our Birds database.
Note
Why There Are Two Sets of Output for the Birds Table
In the following list, you see a Birds table Part 1 of 2 and a Birds table Part 2 of 2. The output of the data in the Birds table was split only for readability purposes in this book. The bird and the bird name are included for your convenience in both data output sets.
BIRDS Table, Part 1 of 2
SQL> select bird_id, bird_name, height, wingspan, eggs, broods
2 from birds;
BIRD_ID BIRD_NAME HEIGHT WINGSPAN EGGS BROODS
------- ------------------------- ------ -------- ---- ------
1 Great Blue Heron 52 78 5 1
2 Mallard 28 3 10 1
3 Common Loon 36 54 2 1
4 Bald Eagle 37 84 2 1
5 Golden Eagle 40 90 3 1
6 Red Tailed Hawk 25 48 3 1
7 Osprey 24 72 4 1
8 Belted Kingfisher 13 23 7 1
9 Canadian Goose 43 72 10 1
10 Pied-billed Grebe 13 7 7 1
11 American Coot 16 29 12 1
12 Common Sea Gull 18 18 3 1
13 Ring-billed Gull 19 50 4 1
14 Double-crested Cormorant 33 54 4 1
15 Common Merganser 27 34 11 1
16 Turkey Vulture 32 72 2 1
17 American Crow 18 40 6 1
18 Green Heron 22 27 4 2
19 Mute Swan 60 95 8 1
20 Brown Pelican 54 90 4 1
21 Great Egret 38 67 3 1
22 Anhinga 35 42 4 1
23 Black Skimmer 20 15 5 1
23 rows selected.
BIRDS Table, Part 2 of 2
SQL> select bird_id, bird_name, incubation, fledging, nest_builder
2 from birds;
BIRD_ID BIRD_NAME INCUBATION FLEDGING NEST_BUILDER
------- ------------------------- ---------- -------- ------------
1 Great Blue Heron 28 60 B
2 Mallard 30 52 F
3 Common Loon 31 80 B
4 Bald Eagle 36 90 B
5 Golden Eagle 45 80 B
6 Red Tailed Hawk 35 46 B
7 Osprey 42 58 B
8 Belted Kingfisher 24 24 B
9 Canadian Goose 30 55 F
10 Pied-billed Grebe 24 24 B
11 American Coot 25 52 B
12 Common Sea Gull 28 36 B
13 Ring-billed Gull 21 40 B
14 Double-crested Cormorant 29 42 B
15 Common Merganser 33 80 F
16 Turkey Vulture 41 88 N
17 American Crow 18 35 F
18 Green Heron 25 36 B
19 Mute Swan 40 150 B
20 Brown Pelican 30 77 F
21 Great Egret 26 49 B
22 Anhinga 30 42 B
23 Black Skimmer 25 30 B
23 rows selected.
FOOD Table
SQL> select * from food;
FOOD_ID FOOD_NAME
---------- ------------------------------
1 Seeds
2 Birds
3 Fruit
4 Frogs
5 Fish
6 Berries
7 Aquatic Plants
8 Aquatic Insects
9 Worms
10 Nuts
11 Rodents
12 Snakes
13 Small Mammals
14 Nectar
15 Pollen
16 Carrion
17 Moths
18 Ducks
19 Insects
20 Plants
21 Corn
22 Crayfish
23 Crustaceans
24 Reptiles
25 Deer
25 rows selected.
BIRDS_FOOD Table
SQL> select * from birds_food;
BIRD_ID FOOD_ID
---------- ----------
1 5
1 4
1 19
1 12
2 1
2 20
2 8
2 21
3 5
3 8
4 5
4 16
4 18
5 13
5 5
5 24
5 2
5 19
5 25
6 2
6 12
6 11
6 19
7 5
8 5
9 7
9 19
9 1
10 8
11 8
11 7
12 5
12 23
12 16
13 19
13 5
13 16
14 5
14 8
15 5
15 8
16 16
17 3
17 8
17 13
17 5
17 16
17 1
18 5
18 19
18 7
19 8
19 20
20 5
20 23
21 5
21 8
21 4
21 22
22 5
22 8
23 5
23 23
23 8
64 rows selected.
MIGRATION Table
SQL> select * from migration;
MIGRATION_ID MIGRATION_LOCATION
------------ ------------------------------
1 Southern United States
2 Mexico
3 Central America
4 South America
5 No Significant Migration
6 Partial, Open Water
6 rows selected.
BIRDS_MIGRATION Table
SQL> select * from birds_migration;
BIRD_ID MIGRATION_ID
---------- ------------
1 1
1 2
1 3
1 4
2 1
3 1
3 2
3 3
4 1
5 5
6 1
7 1
7 2
7 3
7 4
8 1
8 2
8 3
8 4
9 1
9 5
10 1
10 2
10 3
11 1
11 2
11 3
12 1
12 2
12 3
12 4
13 1
13 2
14 1
14 2
14 3
15 1
15 2
15 3
16 1
16 2
16 3
16 4
17 5
18 1
18 2
18 3
18 4
19 6
20 5
21 1
21 2
21 3
22 1
22 2
23 5
56 rows selected.
NESTS Table
SQL> select * from nests;
NEST_ID NEST_NAME
---------- --------------------
1 Ground Nest
2 Platform Nest
3 Cup Nest
4 Pendulous Nest
5 Cavity Nest
6 None/Minimal
7 Floating Platform
7 rows selected.
BIRDS_NESTS Table
SQL> select * from birds_nests;
BIRD_ID NEST_ID
---------- ----------
1 2
2 1
3 2
4 2
5 2
6 2
7 2
8 5
9 1
10 7
11 3
12 1
13 1
14 2
15 5
16 6
17 2
18 2
19 1
20 1
21 2
22 2
23 1
23 rows selected.
NICKNAMES Table
SQL> select * from nicknames;
BIRD_ID NICKNAME
---------- ------------------------------
1 Big Cranky
1 Blue Crane
2 Green Head
2 Green Cap
3 Great Northern Diver
4 Sea Eagle
4 Eagle
5 War Eagle
6 Chicken Hawk
7 Sea Hawk
8 Preacher Bird
9 Honker
10 Water Witch
11 Soul Chicken
11 Devil Duck
12 Seagull
13 Seagull
14 Booby
14 Sea Turkey
15 Sawbill
16 Turkey Buzzard
16 Buzzard
17 Crow
18 Poke
18 Chucklehead
19 Tundra
20 Pelican
21 Common Egret
21 White Egret
22 Water Turkey
22 Spanish Crossbird
22 Snake Bird
23 Sea Dog
33 rows selected.
LOCATIONS Table
SQL> select * from locations;
LOCATION_ID LOCATION_NAME
----------- ------------------------------
1 Heron Lake
2 Loon Creek
3 Eagle Creek
4 White River
5 Sarasota Bridge
6 Fort Lauderdale Beach
6 rows selected.
PHOTOS Table
SQL> select * from photos; no rows selected
Summary
During this hour, you downloaded a relational database management system implementation to perform the hands-on exercises in this book. You saw how to install the software and create a user with the appropriate privileges in the database to create and manage database objects. You looked through the tables and data for the Birds database and, hopefully, ran the scripts to create the database. The last section gave you a hard copy of the data, which will come in handy as you progress with the exercises until you really get to know the data itself. Remember, you must know your data so that you can most effectively use it to benefit yourself and the organization.
Q&A
Q. Can I use an implementation other than Oracle for this book?
A. Oracle is the implementation used for the examples and exercises in this book. You can select virtually any relational database implementation, but you might need to make minor changes to the syntax that is specific to your implementation. The recommendation is to use the same software we are using in this book, for simplicity’s sake, and then take your SQL knowledge with you to any other implementation that you choose. The examples and exercises given throughout this book are as compliant to the SQL standard as possible, for easy portability.
Q. If I make a mistake or just want to start over with a new database, can I do so?
A. Absolutely. If you make mistakes, delete data or tables accidentally, or simply want to reset or re-create the database, you can do so using the scripts provided. Simply log into SQL and rerun the script tables.sql. Then rerun the script data.sql. All the old tables will be dropped, new tables will be created, and data will be inserted as it was in the beginning.
Workshop
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.
Quiz
Refer to the data for the BIRDS database that was listed during this hour.
1. Why is the
BIRDStable split into two separate output sets?2. Why do errors occur the first time you execute the file
tables.sql?3. Why are zero rows deleted the first time you execute the file
data.sql?4. What must an administrative user do after creating a user before that user can create and manage objects in the database?
5. How many tables are in the
BIRDSdatabase?
Exercises
Refer to the data for the BIRDS database that was listed during this hour.
1. Give some examples of parent tables in the
BIRDSdatabase.2. Give some examples of child tables in the
BIRDSdatabase.3. How many unique types of birds are in the database?
4. What foods does the Bald Eagle eat?
5. Who builds the most nests, male or female, or both?
6. How many birds migrate to Central America?
7. Which bird spends the most time raising its young?
8. Which birds have the term eagle in their nickname?
9. What is the most popular migration location for birds in the database?
10. Which bird(s) has/have the most diverse diet?
11. What is the average wingspan of birds that eat fish?