www.oracle.

com/academy

SQL Programming Project Tables

OracleFlix Online Media Rentals

Table name: CUSTOMERS

Column CUSTOMER_ID LAST_NAME FIRST_NAME HOME_PHONE ADDRESS CITY STATE EMAIL CELL_PHONE
Name
Key Type PK
Not Null = NN NN, U NN NN NN NN NN NN
Unique = U
Data Type NUMBER VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2
Length 10 25 25 12 100 30 2 25 12

Sample Data – Make up at least six members, using the sequence to generate the PKs:

CUSTOMER_ID LAST_NAME FIRST_NAME HOME_PHONE ADDRESS CITY STATE EMAIL CELL_PHONE

101 Palombo Lisa 716-270-2669 123 Main St Buffalo NY palombo@ecc.edu 716-555-1212

Table Name: MOVIES

Column TITLE_ID TITLE DESCRIPTION RATING CATEGORY RELEASE_DATE
Name
Key Type PK
Not Null = NN NN, U NN NN NN
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
 2

Unique = U
Check G, PG, DRAMA, COMEDY, ACTION,
PG13, R, CHILD, SCIFI, DOCUMENTARY
Data Type NUMBER VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 DATE
Length 10 60 400 4 20

Sample Data – Add at least six titles, use www.imdb.com for information and the sequence for the PKs:

TITLE_ID TITLE DESCRIPTION RATING CATEGORY RELEASE_DATE

1 Remember The true story of a newly appointed African- PG DRAMA 29-SEP-2000
the Titans American coach and his high school team on
their first season as a racially integrated unit.

Table Name: MEDIA

Column MEDIA_ID FORMAT TITLE_ID
Name
Key Type PK FK
Not Null = NN NN, U NN NN
Unique = U
FK Ref Table MOVIES
FK Ref Column TITLE_ID
Data Type NUMBER VARCHAR2 NUMBER
Length 10 3 10

Sample Data : Use TITLE_ID from the MOVIES table, with one or two copies of each title, using the sequence to generate the PKs:

MEDIA_ID FORMAT TITLE_ID

92 DVD 1
93 VHS 1
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
 3

Table Name: RENTAL HISTORY

Column MEDIA_ID RENTAL_DATE CUSTOMER_ID RETURN_DATE
Name
KeyType PK, FK1 PK FK2
Not Null = NN NN, U NN NN
Unique = U
Default Value SYSDATE
FK Ref Table MEDIA CUSTOMERS
FK Ref Column MEDIA_ID CUSTOMER_ID
Data Type NUMBER DATE NUMBER DATE
Length 10 10

Sample Data – Add four rows using MEDIA_ID from the MEDIA table and CUSTOMER_ID from the CUSTOMER table:

MEDIA_ID RENTAL_DATE CUSTOMER_ID RETURN_DATE

92 19-SEP-2010 101 20-SEP-2010

Table Name: ACTORS

Table Definition:

Column ACTOR_ID STAGE_NAME FIRST_NAME LAST_NAME BIRTH_DATE

Name
Key Type PK
Not Null = NN NN, U NN NN NN NN
Unique = U
Data Type NUMBER VARCHAR2 VARCHAR2 VARCHAR2 DATE
Length 10 40 25 25

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
 4

Sample Data : add at least 4 rows, using the sequence to generate the PKs :

ACTOR_ID STAGE_NAME FIRST_NAME LAST_NAME BIRTH_DATE

1001 Brad Pitt William Pitt 18-DEC-1963

Table Name: STAR_BILLINGS

Column ACTOR_ID TITLE_ID COMMENTS
Name
Key Type PK PK
Not Null = NN NN NN
Unique = U
FK Ref Table ACTORS MOVIES
FK Ref Column ACTOR_ID TITLE_ID
Data Type NUMBER NUMBER VARCHAR2
Length 10 10 40

Sample Data : add at least 4 rows, using ACTOR_IDs from the ACTORS table and TITLE_IDs from the MOVIES table :

ACTOR_ID TITLE_ID COMMENTS

1001 2 Romantic Lead

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.