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.
