Creating the Sample Database

[ Home | Contact ]

SQL: Visual QuickStart Guide, 3rd Edition

SQL 3/e cover

Creating the Sample Database - Oracle 10g

To create books in Oracle 10g:

Start Database Configuration Assistant.

This procedure varies by platform. In Windows, for example, choose Start > All Programs > Oracle - OraDb10g_home1 > Configuration and Migration Tools > Database Configuration Assistant.
Click Next to skip the Welcome page.
On the Operations page, select Create a Database; then click Next.

Database Configuration Assistant guides you through the steps needed to create a database.
On the Database Templates page, select General Purpose; then click Next.
On the Database Identification page, type books in the Global Database Name and SID boxes; then click Next.
On the Management Options page, accept the default settings; then click Next.
On the Database Credentials page, set the user password(s); then click Next.
On the Storage Options page, select File System; then click Next.
On the Database File Locations page, select Use Database File Locations from Template; then click Next.
On the Recovery Configuration page, accept the default settings; then click Next.
On the Database Content page, accept the default settings; then click Next.
On the Initialization Parameters page, select the following options; then click Next.

Memory tab: Select Typical and accept the default percentage. (This setting is appropriate to create a database with minimal user input.)

Sizing tab: Accept the default settings.

Character Sets tab: Accept the default settings.

Connection Mode tab: Select Dedicated Server mode. (This setting is appropriate when only a small number of clients will use the database.)
On the Database Storage page, accept the default settings; then click Next.
On the Create Options page, select Create Database; then click Finish.
In the Confirmation dialog box that appears, review the configuration options (and save them to an HTML file if you like); then click OK.

A progress box appears while Oracle creates the database.
Click Exit in the message box that appears when Oracle finishes creating the database.
Start SQL*Plus (sqlplus) and connect to the books database.

At a command prompt, type:
sqlplus user/password@dbname
user is your Oracle user name, password is your password, and dbname is the name of the database to connect to (books, in this case). For security, you can omit the password and instead type:
sqlplus user@dbname
SQL*Plus will prompt you for your password.

If you're running Oracle locally, you can use the user name system and the password that you set in step 7:
sqlplus system@books

If you're connecting to a remote Oracle database, ask your database administrator (DBA) for the connection parameters.
Tip To open a command prompt in Windows, choose Start > All Programs > Accessories > Command Prompt.
At the SQL prompt, type
@books_oracle.sql
and then press Enter. You can include an absolute or relative pathname (see the "Pathnames" sidebar in "Running SQL Programs" in Chapter 1).

sqlplus displays the results. Ignore the messages about nonexistent tables — they're caused by the script's DROP TABLE statements, which you'll need to rerun books_oracle.sql later to restore the tables to their original states.
To run SQL scripts and interactive statements against books, see "Oracle" in Chapter 1.

Tip

The script books_oracle.sql differs slightly from the standard SQL script books_standard.sql. In the Oracle script, the value in the column au_fname in the table authors for author A06 is a space character (' '), rather than an empty string (''). This change prevents Oracle from interpreting the first name of author A06 as null; see the DBMS Tip in "Nulls" in Chapter 3.