Transcript 15 - Christopher Lee

15 SQL Workshop
Important Legal Notice:
Materials on this lecture are from a book titled “Oracle Education”
by Kochhar, Gravina, and Nathan (1999), published by Oracle Corp.
For further information, visit www.oracle.com
This presentation must be used for only education purpose for
students at Central Washington University which is a member of
Oracle Academic Initiatives (OAI) and has used Oracle systems for
HRIS & Accounting Systems as a database platform for its
PeopleSoft ERP system, since 1999.
Workshop Overview
•
•
•
•
•
Creating tables and sequences
Modifying data in the tables
Modifying a table definition
Creating a view
Writing scripts containing SQL and
SQL*Plus commands
• Generating a simple report
Practice 15
1. Create the tables based on the following table instance charts. Choose the appropriate datatypes and be
sure to add integrity constraints.
A. Table name: MEMBER
Column_
Name
Key
Type
Null/
Unique
Default
Value
Datatype
Length
MEMBER_
ID
PK
LAST_
NAME
FIRST_
NAME
ADDRESS
CITY
PHONE
JOIN_
DATE
NN,U
NN
Number
10
System
Date
VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 Date
25
25
100
30
15
NN
b. Table name: TITLE
Column_ TITLE_ID
Name
PK
Key
Type
NN,U
Null/
Unique
Check
TITLE
DESCRIPTION
NN
NN
Datatype Number
10
Length
VARCHAR2
60
VARCHAR2
400
RATING
CATEGORY
G, PG, R
NC17, NR
DRAMA,
COMEDY,
ACTION,
CHILD,
SCIFI,
DOCUMENTARY
VARCHAR2
20
VARCHAR2
4
RELEASE_DATE
VARCHAR2
Practice 15 (continued)
c. Table name: TITLE_COPY
Column_ COPY_ID
Name
PK
Key
Type
NN,U
Null/
Unique
Check
TITLE_ID
FK Ref
Table
FK Ref
Col
Datatype Number
10
Length
TITLE
STATUS
PK, FK
NN,U
NN
AVAILABLE, DESTROYED,
RENTED, RESERVED
TITLE_ID
Number
10
VARCHAR2
15
d. Table name: RENTAL
Column_
Name
Key
Type
Default
Value
FK Ref
Table
FK Ref
Col
Datatype
Length
BOOK_
DATE
PK
MEMBER_
ID
PK,FK1
COPY_ID
ACT_RET_
DATE
PK,FK2
TITLE_ID
PK,FK2
System
System Date
+ 2 days
Date
Date
EXP_RET_
DATE
MEMBER
TITLE_COPY
TITLE_COPY
MEMBER_
ID
Number
10
COPY_ID
TITLE_ID
Number
10
Date
Date
Number
10
Practice 15 (continued)
e. Table name: RESERVATION
Column_
Name
Key
Type
Null/
Unique
FK Ref
Table
FK Ref
Column
Datatype
Length
RES_DATE
MEMBER_ID
TITLE_ID
PK
PK,FK1
PK,FK2
NN,U
NN,U
NN
MEMBER
TITLE
MEMBER_ID
TITLE_ID
Number
10
Number
10
Date
2. Verify that the tables and constraints were created properly by checking the data dictionary.
TABLE_NAME
-------------------MEMBER
RENTAL
RESERVATION
TITLE
TITLE_COPY
CONSTRAINT_NAME
------------------------------------------------------------MEMBER_LAST_NAME_NN
MEMBER_JOIN_DATE_NN
MEMBER_MEMBER_ID_PK
RENTAL_BOOK_DATE_COPY_TITLE_PK
RENTAL_MEMBER_ID_FK
RENTAL_COPY_ID_TITLE_ID_FK
RESERVATION_RESDATE_MEM_TIT_PK
RESERVATION_MEMBER_ID
RESERVATION_TITLE_ID
...
17 rows selected.
C
-C
C
P
P
R
R
P
R
R
TABLE_NAME
---------------------------------MEMBER
MEMBER
MEMBER
RENTAL
RENTAL
RENTAL
RESERVATION
RESERVATION
RESERVATION
Practice 15 (continued)
3. Create sequences to uniquely identify each row in the MEMBER table and the TITLE table.
a. Member number for theMEMBER table.: start with 101; do not allow caching of the values. Name the
sequence member_id_seq.
b. Title number for the TITLE table: start with 92; no caching. Name the sequence title_id_seq.
c. Verify the existence of the sequences in the data dictionary.
SEQUENCE_NAME INCREMENT_BY
------------------------- ----------------------TITLE_ID_SEQ
1
MEMBER_ID_SEQ
1
LAST_NUMBER
---------------------92
101
4. Add data to the tables. Create a script for each set of data to add.
a. Add movie titles to the TITLE table. Write a script to enter the movie information.Save the script as
p15q4a.sql. Use the sequences to uniquely identify each title. Enter the release dates in the DD-MON-YYYY
format. Remember that single quotation marks in a character field must be specifically handled. Verify your
additions.
TITLE
------------------------------Willie and Christmas Too
Again
The Glob
My Day Off
Miracles on Ice
Soda Gang
6 rows selected.
Alien
Practice 15 (continued)
Title
Willie and
Christmas
Too
Alien Again
The Glob
My Day Off
Miracles on
Ice
Soda Gang
Description
All of Willie’s friends make a
Christmas list, but Willie has yet to add
his own wish list.
Yet another installation of science
fiction history. Can the heroine save
the plane from the alien life form?
A meteor crashes near a small
American town and unleashes
carnivorous goo in this classic.
With a little luck and a lot of ingenuity,
a teenager skips school for a day in
New York
A six-year-old has doubts about Santa
Claus, but she discovers that miracles
really do exist
After discovering a cache of drugs, a
young couple find themselves pitted
against a vicious gang.
Rating Category
G
CHILD
Release_date
05-OCT-1995
R
SCIFI
19-MAY-1995
NR
SCIFI
12-AUG-1995
PG
COMEDY 12-JUL-1995
PG
DRAMA
12-SEP-1995
NR
ACTION
01-JUN-1995
Practice 15 (continued)
b. Add data to the MEMBER table. Write a script named p15q4b.sql to prompt users for the information.
Execute the script. Be sure to use the sequence to add the member numbers.
First_
Name
Carmen
LaDoris
Midori
Mark
Audry
Molly
Last_
Name
Velasquez
Ngao
Nagayama
Quick-toSee
Ropeburn
Urguhart
Address
City
Phone
Join_Date
283 King Street
5 Modrany
68 Via Centrale
6921 King Way
Seattle
Bratislava
Sao Paolo
Lagos
206-899-6666
586-355-8882
254-852-5764
63-559-7777
08-MAR-1990
08-MAR-1990
17-JUN-1991
07-APR-1990
86 Chy Street
3035 Laurier
Hong Kong 41-559-87
18-JAN-1991
Quebec
418-542-9988 18-JAN-1991