Oracle SQL*Plus: The Definitive Guide

Creating and Loading the Sample Tables

Many of the examples in this book, particularly the reporting examples, have been developed against a sample time-tracking database. It’s a fairly simplistic database, containing only three tables, but it’s enough to illustrate everything I talk about in this book. You may or may not wish to create this database for yourself. Creating the database will allow you to try all the examples in this book exactly as they are shown. If you choose not to create and load the sample database, at least familiarize yourself with the data model. Also glance at the sample data itself, which is reproduced later in this section. If you have looked at the model and at the data, you shouldn’t have any trouble following and understanding the examples in this book.

The Data Model

Figure 1.2 shows an Entity Relationship Diagram, or ERD, for the sample database.

Figure 1-2. The sample database

As you can see from the ERD, there are only three entities: EMPLOYEE, PROJECT, and PROJECT_HOURS. Table 1.2 gives a brief description of each entity.

Table 1-2. Entity Descriptions

Entity Name	Description
EMPLOYEE	Contains one record for each employee. This record contains the employee’s name, hire date, termination date, and billing rate. The primary key is an arbitrary employee ID number. The termination date for current employees is set to NULL.
PROJECT	Contains one record for each project that an employee may work on. Contains the project name and budget. The primary key is an arbitrary project ID number.
PROJECT_HOURS	Each time an employee logs time to a project, a record is generated in this table. The record contains the number of hours charged against the project as well as the total dollar amount charged. The dollar amount charged is calculated at the time the record is created because an employee’s billing rate may fluctuate over time. The primary key is a combination key made up of an employee ID, a project ID, and the date.

The number of employees and projects is fairly small. However, there is a fairly large amount of data in the PROJECT_HOURS table to allow for the generation of multiple page reports, which are needed to demonstrate pagination, page headings, page footings, and summarization.

The Tables

This section shows the column descriptions, including column datatypes and lengths, for each of the three sample tables. This is the same information you would get using SQL*Plus’s DESCRIBE command.

The EMPLOYEE table

Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_ID                     NOT NULL NUMBER
 EMPLOYEE_NAME                            VARCHAR2(40)
 EMPLOYEE_HIRE_DATE                       DATE
 EMPLOYEE_TERMINATION_DATE                DATE
 EMPLOYEE_BILLING_RATE                    NUMBER

The PROJECT table

Name                            Null?    Type
 ------------------------------- -------- ----
 PROJECT_ID                      NOT NULL NUMBER
 PROJECT_NAME                             VARCHAR2(40)
 PROJECT_BUDGET                           NUMBER

The PROJECT_HOURS table

Name                            Null?    Type
 ------------------------------- -------- ----
 PROJECT_ID                      NOT NULL NUMBER
 EMPLOYEE_ID                     NOT NULL NUMBER
 TIME_LOG_DATE                   NOT NULL DATE
 HOURS_LOGGED                             NUMBER
 DOLLARS_CHARGED                          NUMBER

The Data

This section shows the data contained in the three sample tables.

The EMPLOYEE table

ID Name                 Hire Date    Term Date      Billing Rate
----- -------------------- ------------ -------------- ------------
  101 Jonathan Gennick     15-Nov-1961  Still Employed       169.00
  102 Jenny Gennick        16-Sep-1964  05-May-1998          135.00
  104 Jeff Gennick         29-Dec-1987  01-Apr-1998           99.00
  105 Horace Walker        15-Jun-1998  Still Employed       121.00
  107 Bohdan Khmelnytsky   02-Jan-1998  Still Employed        45.00
  108 Pavlo Chubynsky      01-Mar-1994  15-Nov-1998          220.00
  110 Ivan Mazepa          04-Apr-1998  30-Sep-1998           84.00
  111 Taras Shevchenko     23-Aug-1976  Still Employed       100.00
  112 Hermon Goche         15-Nov-1961  04-Apr-1998           70.00
  113 Jacob Marley         03-Mar-1998  31-Oct-1998          300.00

The PROJECT table

ID Project Name                              Budget
----- -------------------------------- ---------------
 1001 Corporate Web Site                  1,912,000.00
 1002 Year 2000 Fixes                   999,998,000.00
 1003 Accounting System Implementation      897,000.00
 1004 Data Warehouse Maintenance            294,000.00
 1005 TCP/IP Implementation                 415,000.00

The PROJECT_HOURS table

The PROJECT_HOURS table contains the following information, repeated for each employee.

Proj ID Emp ID Log Date     Hours Charged Amt Charged
------- ------ ------------ ------------- -----------
   1001    101 01-Jan-1998           1.00      169.00
   1003    101 01-Jan-1998           3.00      507.00
   1005    101 01-Jan-1998           5.00      845.00
   1002    101 01-Feb-1998           7.00    1,183.00
   1004    101 01-Feb-1998           1.00      169.00
   1001    101 01-Mar-1998           3.00      507.00
   1003    101 01-Mar-1998           5.00      845.00
   1005    101 01-Mar-1998           7.00    1,183.00
   1002    101 01-Apr-1998           1.00      169.00
   1004    101 01-Apr-1998           3.00      507.00
   1001    101 01-May-1998           5.00      845.00
   1003    101 01-May-1998           7.00    1,183.00
   1005    101 01-May-1998           1.00      169.00
   1002    101 01-Jun-1998           3.00      507.00
   1004    101 01-Jun-1998           5.00      845.00
   1001    101 01-Jul-1998           7.00    1,183.00
   1003    101 01-Jul-1998           1.00      169.00
   1005    101 01-Jul-1998           3.00      507.00
   1002    101 01-Aug-1998           5.00      845.00
   1004    101 01-Aug-1998           7.00    1,183.00
   1001    101 01-Sep-1998           1.00      169.00
   1003    101 01-Sep-1998           3.00      507.00
   1005    101 01-Sep-1998           5.00      845.00
   1002    101 01-Oct-1998           7.00    1,183.00
   1004    101 01-Oct-1998           1.00      169.00
   1001    101 01-Nov-1998           3.00      507.00
   1003    101 01-Nov-1998           5.00      845.00
   1005    101 01-Nov-1998           7.00    1,183.00
   1002    101 01-Dec-1998           1.00      169.00
   1004    101 01-Dec-1998           3.00      507.00

The detail is actually the same for each employee. They all work the same hours on all projects. There are enough PROJECT_HOURS records to produce some reasonable summary reports, as you will see in Chapter 3.

Loading the Sample Data

In order to load the sample data you will need an Oracle userid and password. If you are accessing a remote database, often the case for people using Windows, you will also need a connect string. If you are using Oracle Personal Edition (formerly Personal Oracle), then the connect string is not needed to connect to your local database. You must have the necessary privileges and quotas to create tables in the database you are using. Specifically, you must have the following system privileges:

CREATE SESSION

ALTER SESSION

CREATE TABLE

CREATE VIEW

CREATE TRIGGER

CREATE PROCEDURE

CREATE SYNONYM

CREATE SEQUENCE

CREATE TYPE (Oracle8 only)

Your database administrator can help you with any of these items. Once you have a username and password and have been granted the necessary privileges, you can create the sample tables and data by following these four steps:

1. Download and unzip the script files.

2. Start SQL*Plus.

3. Log into your Oracle database.

4. Run the BLD_DB.SQL script file.

The screenshots in the remainder of this section show you how things would look if you were creating the sample tables and data using SQL*Plus in a Windows 95 or NT environment. Under other operating systems, such as Unix, everything would look much the same, except that SQL*Plus may not have its own window and the userid/password prompts may not be in a dialog box.

If you are new to SQL*Plus and are completely uncertain how to start it in your particular environment, you should first read the section titled “Starting SQL*Plus” in Chapter 2. Once you know how to start SQL*Plus, you can come back here and run the script to create the sample tables and fill them with data.

Step 1: Download and unzip the script files

The SQL scripts to create the tables and data used for the examples in this book can be downloaded from O’Reilly & Associates’ web site. See the Preface for more information.

The scripts are stored in a ZIP file named BLD_DB.ZIP. Download this file, and extract the contents into a directory on your hard disk. If you do not have an unzip utility, you may download BLD_DB.EXE instead. BLD_DB.EXE is a self-extracting ZIP file. Just run it, the scripts will be extracted, and you won’t need a separate unzip utility.

Step 2: Start SQL*Plus

When you first start SQL*Plus in a Windows environment, you will see a SQL*Plus window open. You will also immediately be prompted for a userid, a password, and a host string. Your screen should look like the one shown in Figure 1.3.

Figure 1-3. SQL*Plus immediately after startup

On a Unix system, you won’t see a dialog box like that shown in Figure 1.3. Instead you will simply see a userid prompt.

Step 3: Log into your Oracle database

Type your userid, password, and host string into the three dialog box fields. If you are using Oracle Personal Edition, you typically leave the host string field blank. Once the correct information is entered, click the OK button. If you are using a Unix system, answer the username prompt by typing in your username and pressing ENTER. Answer the password prompt in the same way. Once logged in, you should see a screen similar to that shown in Figure 1.4.

Figure 1-4. SQL*Plus after a successful login

The opening screen shows the SQL*Plus version, some copyright information, and some Oracle database version information. The exact version numbers and options you see may vary from those shown in Figure 1.4.

Step 4: Run the BLD_DB.SQL script file

The next and final step is to run the BLD_DB.SQL script file, which is one of the files in the ZIP archive you downloaded in step 1. To do that, simply use the @ command as shown below:

SQL> @c:\...\bld_db

You need to specify the full directory path to this file.

After you type in the above command and press ENTER, your SQL*Plus screen should look like the one shown in Figure 1.5.

Figure 1-5. After the BLD_DB script is started

The first thing the script does is confirm that you really do want to load the sample data. Go ahead and answer with a Y, or with an N if you’ve changed your mind about loading the data.

Tip

You must answer the script questions with either a Y or an N. Your reply is not case-sensitive, so a lowercase response is fine. Input validation is minimal, but it is there. If you give an invalid response, an error message will be displayed and the script will stop. You will then need to restart the script, using the START command as shown earlier, and answer correctly.

If you’ve answered Y to the question about continuing, the next thing you will see is another prompt asking if you want to first drop the sample tables. This is shown in Figure 1.6.

Figure 1-6. The BLD_DB script asking to drop the sample tables

This option to first drop the sample tables is convenient if you have loaded them before and wish to quickly reload them. If this is your first time running this script, you should answer this question with an N. If you have loaded the tables previously, and you know that they exist now, then you should answer with a Y.

Now you can just sit back and watch. The next thing the script does is create the sample tables. Following that it inserts data into each table. Figure 1.7 shows the progress messages being displayed on the screen while all this is occurring.

Figure 1-7. Progress messages from the BLD_DB script

The entire load process should take less than a minute. When the load is complete, you will be asked to press ENTER one final time. After doing that, you can use the EXIT command to exit SQL*Plus.

Now that you have loaded the sample data, you can proceed with the book and try out the examples as you go.