SQL Oracle Help

My initials for the assignment is MA

create_workbook_store.sql
— create_store.sql
— Introcution, Oracle Database 11g PL/SQL Programming Workbook
— by Michael McLaughlin and John Harper
—
— This demonstrates building a DVD, Game Cartridge and VHS tape store.
— It also highlights constraints and compound check constraints and
— seeds the tables with data.

SPOOL create_store.log
SET ECHO OFF
SET FEEDBACK ON
SET NULL ‘‘
SET PAGESIZE 999
SET SERVEROUTPUT ON
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘SYSTEM_USER’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE system_user CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘SYSTEM_USER_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE system_user_s1′;
END LOOP;
END;
/
— ——————————————————————
— Create SYSTEM_USER table and sequence and seed data.
— ——————————————————————
CREATE TABLE system_user
( system_user_id NUMBER CONSTRAINT pk_system_user PRIMARY KEY
, system_user_name VARCHAR2(20) CONSTRAINT nn_system_user_1 NOT NULL
, system_user_group_id NUMBER CONSTRAINT nn_system_user_2 NOT NULL
, system_user_type NUMBER CONSTRAINT nn_system_user_3 NOT NULL
, last_name VARCHAR2(20)
, first_name VARCHAR2(20)
, middle_initial VARCHAR2(1)
, created_by NUMBER CONSTRAINT nn_system_user_4 NOT NULL
, creation_date DATE CONSTRAINT nn_system_user_5 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_system_user_6 NOT NULL
, last_update_date DATE CONSTRAINT nn_system_user_7 NOT NULL);
CREATE SEQUENCE system_user_s1 START WITH 1000;
INSERT INTO system_user
VALUES ( 1,’SYSADMIN’,1,1,1,NULL,NULL,1,SYSDATE,1,SYSDATE);
ALTER TABLE system_user ADD CONSTRAINT fk_system_user_1 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id);
ALTER TABLE system_user ADD CONSTRAINT fk_system_user_2 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id);
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘COMMON_LOOKUP’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE common_lookup CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘COMMON_LOOKUP_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE common_lookup_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create COMMON_LOOKUP table and sequence and seed data.
— ——————————————————————
CREATE TABLE common_lookup
( common_lookup_id NUMBER CONSTRAINT pk_common_lookup PRIMARY KEY
, common_lookup_table VARCHAR2(30) CONSTRAINT nn_common_lookup_1 NOT NULL
, common_lookup_column VARCHAR2(30) CONSTRAINT nn_common_lookup_2 NOT NULL
, common_lookup_type VARCHAR2(30) CONSTRAINT nn_common_lookup_3 NOT NULL
, common_lookup_code VARCHAR2(8)
, common_lookup_meaning VARCHAR2(255) CONSTRAINT nn_common_lookup_4 NOT NULL
, created_by NUMBER CONSTRAINT nn_common_lookup_5 NOT NULL
, creation_date DATE CONSTRAINT nn_common_lookup_6 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_common_lookup_7 NOT NULL
, last_update_date DATE CONSTRAINT nn_common_lookup_8 NOT NULL
, CONSTRAINT fk_common_lookup_1 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_common_lookup_2 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE INDEX common_lookup_n1
ON common_lookup(common_lookup_table);
CREATE UNIQUE INDEX common_lookup_u2
ON common_lookup(common_lookup_table,common_lookup_column,common_lookup_type);
CREATE SEQUENCE common_lookup_s1 START WITH 1000;
— ——————————————————————
— Insert Common Lookups.
— ——————————————————————
SELECT ‘INSERT COMMON_LOOKUP’ AS “Section Header” FROM dual;
INSERT INTO common_lookup VALUES
( 1,’SYSTEM_USER’,’SYSTEM_USER_TYPE’,’SYSTEM_ADMIN’,NULL,’System Administrator’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’SYSTEM_USER’,’SYSTEM_USER_TYPE’,’DBA’,NULL,’Database Administrator’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’CONTACT’,’CONTACT_TYPE’,’EMPLOYEE’,NULL,’Employee’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’CONTACT’,’CONTACT_TYPE’,’CUSTOMER’,NULL,’Customer’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’MEMBER’,’MEMBER_TYPE’,’INDIVIDUAL’,NULL,’Individual Membership’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’MEMBER’,’MEMBER_TYPE’,’GROUP’,NULL,’Group Membership’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’MEMBER’,’CREDIT_CARD_TYPE’,’DISCOVER_CARD’,NULL,’Discover Card’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’MEMBER’,’CREDIT_CARD_TYPE’,’MASTER_CARD’,NULL,’Master Card’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’MEMBER’,’CREDIT_CARD_TYPE’,’VISA_CARD’,NULL,’VISA Card’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ADDRESS’,’ADDRESS_TYPE’,’HOME’,NULL,’Home’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ADDRESS’,’ADDRESS_TYPE’,’WORK’,NULL,’Work’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’TELEPHONE’,’TELEPHONE_TYPE’,’HOME’,NULL,’Home’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’TELEPHONE’,’TELEPHONE_TYPE’,’WORK’,NULL,’Work’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_TYPE’,’DVD_FULL_SCREEN’,NULL,’DVD: Full Screen’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_TYPE’,’DVD_WIDE_SCREEN’,NULL,’DVD: Wide Screen’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_TYPE’,’GAMECUBE’,NULL,’Nintendo GameCube’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_TYPE’,’PLAYSTATION2′,NULL,’PlayStation2′
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_TYPE’,’XBOX’,NULL,’XBOX’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_TYPE’,’VHS_SINGLE_TAPE’,NULL,’VHS: Single Tape’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_TYPE’,’VHS_DOUBLE_TAPE’,NULL,’VHS: Double Tape’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING_AGENCY’,’MPAA’,’MPAA’,’Motion Picture Association of America’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING_AGENCY’,’ESRB’,’ESRB’,’Entertainment Software Rating Board’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’MPAA G’,’G’,’G – General Audiences: All ages admitted.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’MPAA PG’,’PG’,’PG – Parental guidance suggested: Some material may not be suitable for children.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’MPAA PG-13′,’PG-13′,’PG-13 – Parents strongly cautioned: Some material may be inappropriate for children under 13.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’MPAA R’,’R’,’R – Restricted: Under 17 requires accompanying parent or adult guardian.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’MPAA NC-17′,’NC-17′,’NC-17 – No one 17 and under admitted.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’MPAA NR’,’NR’,’NR – Not Rated.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’ESRB EC’,’ED’,’EC – Early Childhood: Contains content that may be suitable for ages 3 and older. Contains no material that parents would find inappropriate.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’ESRB E’,’E’,’E – Everyone: Contains content that may be suitable for ages 6 and older. Titles in this category may contain minimal cartoon, fantasy or mild violence and/or infrequent use of mild language.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’ESRB E10+’,’E10+’,’E10+ – Everyone 10+: Contains content that may be suitable for ages 10 and older. Titles in this category may contain more cartoon, fantasy or mild violence, mild language, minimal and/or infrequent blood and/or minimal suggestive themes.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’ESRB T’,’T’,’T – Teen: Contains content that may be suitable for ages 13 and older. Titles in this category may contain violence, suggestive themes, crude humor, minimal blood, simulated gambling, and/or infrequent use of strong language.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’ESRB M’,’M’,’M – Mature: Contains content that may be suitable for ages 17 and older. Titles in this category may contain intense violence, blood and gore, sexual content and/or strong language.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’ESRB AO’,’AO’,’AO – Adult Only: Contains content that is suitable only for adults. Titles in this category may include prolonged scenes of intense violence and/or graphic sexual content and nudity.’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,’ITEM’,’ITEM_RATING’,’ESRB RP’,’RP’,’RP – Rating Pending: Product has been submitted to the ESRB and is awaiting final rating (prior to product release).’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO COMMON_LOOKUP VALUES
( common_lookup_s1.nextval, ‘TRANSACTION’, ‘TRANSACTION_TYPE’, ‘DEBIT’, ‘DB’, ‘Debit’, 1, SYSDATE, 1, SYSDATE);
INSERT INTO COMMON_LOOKUP VALUES
( common_lookup_s1.nextval, ‘TRANSACTION’, ‘TRANSACTION_TYPE’, ‘CREDIT’, ‘CR’, ‘Credit’, 1, SYSDATE, 1, SYSDATE);
INSERT INTO COMMON_LOOKUP VALUES
( common_lookup_s1.nextval, ‘TRANSACTION’, ‘PAYMENT_METHOD_TYPE’, ‘DISCOVER_CARD’, ”, ‘Discover Card’, 1, SYSDATE, 1, SYSDATE);
INSERT INTO COMMON_LOOKUP VALUES
( common_lookup_s1.nextval, ‘TRANSACTION’, ‘PAYMENT_METHOD_TYPE’, ‘VISA_CARD’, ”, ‘Visa Card’, 1, SYSDATE, 1, SYSDATE);
INSERT INTO COMMON_LOOKUP VALUES
( common_lookup_s1.nextval, ‘TRANSACTION’, ‘PAYMENT_METHOD_TYPE’, ‘MASTER_CARD’, ”, ‘Master Card’, 1, SYSDATE, 1, SYSDATE);
INSERT INTO COMMON_LOOKUP VALUES
( common_lookup_s1.nextval, ‘TRANSACTION’, ‘PAYMENT_METHOD_TYPE’, ‘CASH’, ”, ‘Cash’, 1, SYSDATE, 1, SYSDATE);
INSERT INTO COMMON_LOOKUP VALUES
( common_lookup_s1.nextval, ‘RENTAL_ITEM’, ‘RENTAL_ITEM_TYPE’, ‘1-DAY RENTAL’, ”, ‘1-Day Rental’, 1, SYSDATE, 1, SYSDATE);
INSERT INTO COMMON_LOOKUP VALUES
( common_lookup_s1.nextval, ‘RENTAL_ITEM’, ‘RENTAL_ITEM_TYPE’, ‘3-DAY RENTAL’, ”, ‘3-Day Rental’, 1, SYSDATE, 1, SYSDATE);
INSERT INTO COMMON_LOOKUP VALUES
( common_lookup_s1.nextval, ‘RENTAL_ITEM’, ‘RENTAL_ITEM_TYPE’, ‘5-DAY RENTAL’, ”, ‘5-Day Rental’, 1, SYSDATE, 1, SYSDATE);
— Format and query output.
–COLUMN common_lookup_id FORMAT 9999 HEADING “Lookup|ID #”
–COLUMN common_lookup_table FORMAT A18 HEADING “Lookup|Table”
–COLUMN common_lookup_column FORMAT A20 HEADING “Lookup|Column”
–COLUMN common_lookup_type FORMAT A18 HEADING “Lookup|Type”
–COLUMN common_lookup_code FORMAT A8 HEADING “Lookup|Code”
–SELECT common_lookup_id
–, common_lookup_table
–, common_lookup_column
–, common_lookup_type
–, common_lookup_code
–FROM common_lookup;
ALTER TABLE system_user ADD CONSTRAINT fk_system_user_3 FOREIGN KEY(system_user_type)
REFERENCES common_lookup(common_lookup_id);
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘MEMBER’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE member CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘MEMBER_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE member_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create MEMBER table and sequence and seed data.
— ——————————————————————
CREATE TABLE member
( member_id NUMBER CONSTRAINT pk_member PRIMARY KEY
, member_type NUMBER CONSTRAINT nn_member_1 NOT NULL
, account_number VARCHAR2(10) CONSTRAINT nn_member_2 NOT NULL
, credit_card_number VARCHAR2(19) CONSTRAINT nn_member_3 NOT NULL
, credit_card_type NUMBER CONSTRAINT nn_member_4 NOT NULL
, created_by NUMBER CONSTRAINT nn_member_5 NOT NULL
, creation_date DATE CONSTRAINT nn_member_6 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_member_7 NOT NULL
, last_update_date DATE CONSTRAINT nn_member_8 NOT NULL
, CONSTRAINT fk_member_1 FOREIGN KEY(member_type)
REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_member_2 FOREIGN KEY(credit_card_type)
REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_member_3 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_member_4 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE SEQUENCE member_s1 START WITH 1000;
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘CONTACT’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE contact CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘CONTACT_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE contact_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create CONTACT table and sequence and seed data.
— ——————————————————————
CREATE TABLE contact
( contact_id NUMBER CONSTRAINT pk_contact PRIMARY KEY
, member_id NUMBER CONSTRAINT nn_contact_1 NOT NULL
, contact_type NUMBER CONSTRAINT nn_contact_2 NOT NULL
, last_name VARCHAR2(20) CONSTRAINT nn_contact_3 NOT NULL
, first_name VARCHAR2(20) CONSTRAINT nn_contact_4 NOT NULL
, middle_name VARCHAR2(20)
, created_by NUMBER CONSTRAINT nn_contact_5 NOT NULL
, creation_date DATE CONSTRAINT nn_contact_6 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_contact_7 NOT NULL
, last_update_date DATE CONSTRAINT nn_contact_8 NOT NULL
, CONSTRAINT fk_contact_1 FOREIGN KEY(member_id)
REFERENCES member(member_id)
, CONSTRAINT fk_contact_2 FOREIGN KEY(contact_type)
REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_contact_3 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_contact_4 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE INDEX contact_n1 ON contact(member_id);
CREATE SEQUENCE contact_s1 START WITH 1000;
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘ADDRESS’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE address CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘ADDRESS_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE address_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create ADDRESS table and sequence and seed data.
— ——————————————————————
CREATE TABLE address
( address_id NUMBER CONSTRAINT pk_address PRIMARY KEY
, contact_id NUMBER CONSTRAINT nn_address_1 NOT NULL
, address_type NUMBER CONSTRAINT nn_address_2 NOT NULL
, city VARCHAR2(30) CONSTRAINT nn_address_3 NOT NULL
, state_province VARCHAR2(30) CONSTRAINT nn_address_4 NOT NULL
, postal_code VARCHAR2(20) CONSTRAINT nn_address_5 NOT NULL
, created_by NUMBER CONSTRAINT nn_address_6 NOT NULL
, creation_date DATE CONSTRAINT nn_address_7 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_address_8 NOT NULL
, last_update_date DATE CONSTRAINT nn_address_9 NOT NULL
, CONSTRAINT fk_address_1 FOREIGN KEY(contact_id)
REFERENCES contact(contact_id)
, CONSTRAINT fk_address_2 FOREIGN KEY(address_type)
REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_address_3 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_address_4 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE INDEX address_n1 ON address(contact_id);
CREATE SEQUENCE address_s1 START WITH 1000;
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘STREET_ADDRESS’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE street_address CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘STREET_ADDRESS_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE street_address_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create STREET_ADDRESS table and sequence and seed data.
— ——————————————————————
CREATE TABLE street_address
( street_address_id NUMBER CONSTRAINT pk_st_address PRIMARY KEY
, address_id NUMBER CONSTRAINT nn_st_address_1 NOT NULL
, line_number NUMBER CONSTRAINT nn_st_address_2 NOT NULL
, street_address VARCHAR2(30) CONSTRAINT nn_st_address_3 NOT NULL
, created_by NUMBER CONSTRAINT nn_st_address_4 NOT NULL
, creation_date DATE CONSTRAINT nn_st_address_5 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_st_address_6 NOT NULL
, last_update_date DATE CONSTRAINT nn_st_address_7 NOT NULL
, CONSTRAINT fk_st_address_1 FOREIGN KEY(address_id)
REFERENCES address(address_id)
, CONSTRAINT fk_st_address_2 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_st_address_3 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE SEQUENCE street_address_s1 START WITH 1000;
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘TELEPHONE’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE telephone CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘TELEPHONE_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE telephone_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create TELEPHONE table and sequence and seed data.
— ——————————————————————
CREATE TABLE telephone
( telephone_id NUMBER CONSTRAINT pk_telephone PRIMARY KEY
, contact_id NUMBER CONSTRAINT nn_telephone_1 NOT NULL
, address_id NUMBER CONSTRAINT nn_telephone_2 NOT NULL
, telephone_type NUMBER CONSTRAINT nn_telephone_3 NOT NULL
, country_code VARCHAR2(3) CONSTRAINT nn_telephone_4 NOT NULL
, area_code VARCHAR2(6) CONSTRAINT nn_telephone_5 NOT NULL
, telephone_number VARCHAR2(10) CONSTRAINT nn_telephone_6 NOT NULL
, created_by NUMBER CONSTRAINT nn_telephone_7 NOT NULL
, creation_date DATE CONSTRAINT nn_telephone_8 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_telephone_9 NOT NULL
, last_update_date DATE CONSTRAINT nn_telephone_10 NOT NULL
, CONSTRAINT fk_telephone_1 FOREIGN KEY(contact_id)
REFERENCES contact(contact_id)
, CONSTRAINT fk_telephone_2 FOREIGN KEY(telephone_type)
REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_telephone_3 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_telephone_4 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE INDEX telephone_n1 ON telephone(contact_id,address_id);
CREATE INDEX telephone_n2 ON telephone(address_id);
CREATE SEQUENCE telephone_s1 START WITH 1000;
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘RENTAL’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE rental CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘RENTAL_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE rental_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create RENTAL table and sequence and seed data.
— ——————————————————————
CREATE TABLE rental
( rental_id NUMBER CONSTRAINT pk_rental PRIMARY KEY
, customer_id NUMBER CONSTRAINT nn_rental_1 NOT NULL
, check_out_date DATE CONSTRAINT nn_rental_2 NOT NULL
, return_date DATE CONSTRAINT nn_rental_3 NOT NULL
, created_by NUMBER CONSTRAINT nn_rental_4 NOT NULL
, creation_date DATE CONSTRAINT nn_rental_5 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_rental_6 NOT NULL
, last_update_date DATE CONSTRAINT nn_rental_7 NOT NULL
, CONSTRAINT fk_rental_1 FOREIGN KEY(customer_id)
REFERENCES contact(contact_id)
, CONSTRAINT fk_rental_2 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_rental_3 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE SEQUENCE rental_s1 START WITH 1000;
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘ITEM’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE item CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘ITEM_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE item_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create ITEM table and sequence and seed data.
— ——————————————————————
CREATE TABLE item
( item_id NUMBER CONSTRAINT pk_item PRIMARY KEY
, item_barcode VARCHAR2(20) CONSTRAINT nn_item_1 NOT NULL
, item_type NUMBER CONSTRAINT nn_item_2 NOT NULL
, item_title VARCHAR2(60) CONSTRAINT nn_item_3 NOT NULL
, item_subtitle VARCHAR2(60)
, item_desc CLOB CONSTRAINT nn_item_4 NOT NULL
, item_photo BFILE
, item_rating VARCHAR2(8) CONSTRAINT nn_item_5 NOT NULL
, item_rating_agency VARCHAR2(4) CONSTRAINT nn_item_6 NOT NULL
, item_release_date DATE CONSTRAINT nn_item_7 NOT NULL
, created_by NUMBER CONSTRAINT nn_item_8 NOT NULL
, creation_date DATE CONSTRAINT nn_item_9 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_item_10 NOT NULL
, last_update_date DATE CONSTRAINT nn_item_11 NOT NULL
, CONSTRAINT fk_item_1 FOREIGN KEY(item_type)
REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_item_2 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_item_3 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE SEQUENCE item_s1 START WITH 1000;
— ——————————————————————
— Create ITEM table triggers for content reference column.
— ——————————————————————
— Check for valid rating in COMMON_LOOKUP table.
CREATE OR REPLACE TRIGGER item_t1
BEFORE INSERT OR UPDATE OF item_rating ON item
FOR EACH ROW

DECLARE
— Declare guard variable.
found BOOLEAN := FALSE;

— Declare dynamic cursor.
CURSOR get_ratings
( code VARCHAR2 ) IS
SELECT common_lookup_code
FROM common_lookup
WHERE common_lookup_table = ‘ITEM’
AND common_lookup_column = ‘ITEM_RATING’
AND common_lookup_code = code;

— Define and set user-defined error.
bad_rating EXCEPTION;
PRAGMA EXCEPTION_INIT(bad_rating,-20101);
BEGIN
— Check for valid rating.
FOR i IN get_ratings(:new.item_rating) LOOP
found := TRUE;
END LOOP;
— Raise exception for invalid rating.
IF NOT found THEN
RAISE_APPLICATION_ERROR(-20101,’Rating Value not defined in COMMON_LOOKUP table.’);
END IF;

DECLARE
— Declare guard variable.
found BOOLEAN := FALSE;

— Declare dynamic cursor.
CURSOR get_ratings
( agency VARCHAR2 ) IS
SELECT common_lookup_code
FROM common_lookup
WHERE common_lookup_table = ‘ITEM’
AND common_lookup_column = ‘ITEM_RATING_AGENCY’
AND common_lookup_code = agency;

— Define and set user-defined error.
bad_rating EXCEPTION;
PRAGMA EXCEPTION_INIT(bad_rating,-20102);
BEGIN
— Check for valid rating.
FOR i IN get_ratings(:new.item_rating_agency) LOOP
found := TRUE;
END LOOP;
— Raise exception for invalid rating.
IF NOT found THEN
RAISE_APPLICATION_ERROR(-20102,’Rating Agency Value not defined in COMMON_LOOKUP table.’);
END IF;

END item_t1;
/
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘RENTAL_ITEM’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE rental_item CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘RENTAL_ITEM_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE rental_item_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create RENTAL_ITEM table and sequence and seed data.
— ——————————————————————
CREATE TABLE rental_item
( rental_item_id NUMBER CONSTRAINT pk_rental_item PRIMARY KEY
, rental_id NUMBER CONSTRAINT nn_rental_item_1 NOT NULL
, item_id NUMBER CONSTRAINT nn_rental_item_2 NOT NULL
, rental_item_price NUMBER CONSTRAINT nn_rental_item_3 NOT NULL
, rental_item_type NUMBER CONSTRAINT nn_rental_item_4 NOT NULL
, created_by NUMBER CONSTRAINT nn_rental_item_5 NOT NULL
, creation_date DATE CONSTRAINT nn_rental_item_6 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_rental_item_7 NOT NULL
, last_update_date DATE CONSTRAINT nn_rental_item_8 NOT NULL
, CONSTRAINT fk_rental_item_1 FOREIGN KEY(rental_id)
REFERENCES rental(rental_id)
, CONSTRAINT fk_rental_item_2 FOREIGN KEY(item_id)
REFERENCES item(item_id)
, CONSTRAINT fk_rental_item_3 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_rental_item_4 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE SEQUENCE rental_item_s1 START WITH 1000;
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘PRICE’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE price CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘PRICE_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE price_s1′;
END LOOP;
END;
/
— ——————————————————————
— Create PRICE table and sequence and seed data.
— ——————————————————————
CREATE TABLE price
( price_id NUMBER CONSTRAINT pk_price PRIMARY KEY
, item_id NUMBER CONSTRAINT nn_price_1 NOT NULL
, price_type NUMBER CONSTRAINT nn_price_2 NOT NULL
, active_flag VARCHAR2(1) CONSTRAINT nn_price_3 NOT NULL
, amount NUMBER CONSTRAINT nn_price_4 NOT NULL
, start_date DATE CONSTRAINT nn_price_5 NOT NULL
, end_date DATE
, created_by NUMBER CONSTRAINT nn_price_6 NOT NULL
, creation_date DATE CONSTRAINT nn_price_7 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_price_8 NOT NULL
, last_update_date DATE CONSTRAINT nn_price_9 NOT NULL
, CONSTRAINT fk_price_1 FOREIGN KEY(item_id)
REFERENCES item(item_id)
, CONSTRAINT fk_price_2 FOREIGN KEY(price_type)
REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_price_3 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_price_4 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE SEQUENCE price_s1 START WITH 1000;
— ——————————————————————
— Seed PRICE data in COMMON_LOOKUP table.
— ——————————————————————
INSERT INTO common_lookup
VALUES
( common_lookup_s1.nextval,’PRICE’,’ACTIVE_FLAG’,’YES’,’Y’,’Yes’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup
VALUES
( common_lookup_s1.nextval,’PRICE’,’ACTIVE_FLAG’,’NO’,’N’,’Yes’
, 1, SYSDATE, 1, SYSDATE);
INSERT INTO common_lookup
( common_lookup_id — 1
, common_lookup_table — 2
, common_lookup_column — 3
, common_lookup_type — 4
, common_lookup_code — 5
, common_lookup_meaning — 6
, created_by — 7
, creation_date — 8
, last_updated_by — 9
, last_update_date) — 10
SELECT common_lookup_s1.nextval — 1 (id)
, ‘PRICE’ — 2 (table)
, ‘AMOUNT’ — 3 (column)
, il.type — 4 (type)
, TO_CHAR(il.fabricated) — 5 (code)
, il.meaning — 6 (meaning)
, 1 — 7 (c_id)
, SYSDATE — 8 (c_date)
, 1 — 9 (u_id)
, SYSDATE — 10 (u_date)
FROM dual CROSS JOIN (SELECT ‘New’ AS status
, 1 AS fabricated
, ‘1-DAY RENTAL’ type
, ‘1-Day Rental’ meaning FROM dual
UNION ALL
SELECT ‘New’ AS type
, 3 AS fabricated
, ‘3-DAY RENTAL’ type
, ‘3-Day Rental’ meaning FROM dual
UNION ALL
SELECT ‘New’ AS type
, 5 AS fabricated
, ‘5-DAY RENTAL’ type
, ‘5-Day Rental’ meaning FROM dual) il;
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘TRANSACTION’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE transaction CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘TRANSACTION_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE transaction_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create TRANSACTION table and sequence and seed data.
— ——————————————————————
CREATE TABLE transaction
( transaction_id NUMBER CONSTRAINT pk_transaction PRIMARY KEY
, transaction_account VARCHAR2(20) CONSTRAINT nn_transaction_1 NOT NULL
, transaction_type NUMBER CONSTRAINT nn_transaction_2 NOT NULL
, transaction_date DATE CONSTRAINT nn_transaction_3 NOT NULL
, transaction_amount NUMBER CONSTRAINT nn_transaction_4 NOT NULL
, rental_id NUMBER CONSTRAINT nn_transaction_5 NOT NULL
, payment_method NUMBER CONSTRAINT nn_transaction_6 NOT NULL
, payment_account_number VARCHAR2(20) CONSTRAINT nn_transaction_7 NOT NULL
, created_by NUMBER CONSTRAINT nn_transaction_8 NOT NULL
, creation_date DATE CONSTRAINT nn_transaction_9 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_transaction_10 NOT NULL
, last_update_date DATE CONSTRAINT nn_transaction_11 NOT NULL
, CONSTRAINT fk_transaction_1 FOREIGN KEY(rental_id)
REFERENCES rental(rental_id)
, CONSTRAINT fk_transaction_2 FOREIGN KEY(transaction_type)
REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_transaction_3 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_transaction_4 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE SEQUENCE transaction_s1 START WITH 1000;
— Conditionally drop objects.
BEGIN
FOR i IN (SELECT null
FROM user_tables
WHERE table_name = ‘CALENDAR’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE calendar CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT null
FROM user_sequences
WHERE sequence_name = ‘CALENDAR_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE calendar_s1’;
END LOOP;
END;
/
— ——————————————————————
— Create CALENDAR table and sequence and seed data.
— ——————————————————————
CREATE TABLE calendar
( calendar_id NUMBER CONSTRAINT pk_calendar PRIMARY KEY
, calendar_month_name VARCHAR2(3) CONSTRAINT nn_calendar_1 NOT NULL
, calendar_full_month_name VARCHAR2(9) CONSTRAINT nn_calendar_2 NOT NULL
, start_date DATE CONSTRAINT nn_calendar_3 NOT NULL
, end_date DATE CONSTRAINT nn_calendar_4 NOT NULL
, created_by NUMBER CONSTRAINT nn_calendar_5 NOT NULL
, creation_date DATE CONSTRAINT nn_calendar_6 NOT NULL
, last_updated_by NUMBER CONSTRAINT nn_calendar_7 NOT NULL
, last_update_date DATE CONSTRAINT nn_calendar_8 NOT NULL
, CONSTRAINT fk_calendar_1 FOREIGN KEY(created_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_calendar_2 FOREIGN KEY(last_updated_by)
REFERENCES system_user(system_user_id));
CREATE SEQUENCE calendar_s1 START WITH 1000;
— Seed the Calendar table.
DECLARE
— Define local variables.
start_date DATE;
end_date DATE;
— Define local array types.
TYPE number_list IS TABLE OF NUMBER;
TYPE month_list IS TABLE OF VARCHAR2(3);
TYPE name_list IS TABLE OF VARCHAR2(9);
— Define and initialize array for month ending date.
end_day NUMBER_LIST := number_list(31,28,31
,30,31,30
,31,31,30
,31,30,31);

— Define and initial short and long month names.
months MONTH_LIST := month_list(‘JAN’,’FEB’,’MAR’
,’APR’,’MAY’,’JUN’
,’JUL’,’AUG’,’SEP’
,’OCT’,’NOV’,’DEC’);
month_names NAME_LIST := name_list(‘January’,’February’,’March’
,’April’,’May’,’June’
,’July’,’August’,’September’
,’October’,’November’,’December’);
— Define and initialize years.
years NUMBER_LIST := number_list(1996,1997,1998,1999,2000
,2001,2002,2003,2004,2005
,2006,2007,2008,2009,2010
,2011,2012,2013,2014,2015
,2016,2017,2018,2019,2020);
BEGIN
— Loop through years and then months.
FOR i IN 1..years.COUNT LOOP
FOR j IN 1..months.COUNT LOOP
— Set month starting date.
start_date := TO_DATE(’01’||months(j)||years(i),’DD-MON-YYYY’);
— Check, modify leap year February, and set month ending date.
IF ((MOD(years(i) , 4) = 0) AND (j = 2)) THEN
end_date := TO_DATE((end_day(j) + 1)||’-‘||months(j)||’-‘||years(i),’DD-MON-YYYY’);
ELSE
end_date := TO_DATE((end_day(j))||’-‘||months(j)||’-‘||years(i),’DD-MON-YYYY’);
END IF;
— Insert month into calendar.
INSERT INTO calendar
VALUES
( calendar_s1.nextval
, months(j)
, month_names(j)
, start_date
, end_date
, 1,SYSDATE,1,SYSDATE);
END LOOP;
END LOOP;

END;
/
/*
— ——————————————————————
— Insert DBA values in SYSTEM_USER table.
— —————————————————————— */
SELECT ‘INSERT DBAs IN SYSTEM_USER’ AS “Section Header” FROM dual;
INSERT
INTO system_user
( system_user_id
, system_user_name
, system_user_group_id
, system_user_type
, last_name
, first_name
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( 2,’DBA’, 2, 1,’Adams’,’Samuel’, 1, SYSDATE, 1, SYSDATE);
INSERT
INTO system_user
( system_user_id
, system_user_name
, system_user_group_id
, system_user_type
, last_name
, first_name
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( 3,’DBA’, 2, 1,’Henry’,’Patrick’, 1, SYSDATE, 1, SYSDATE);
INSERT
INTO system_user
( system_user_id
, system_user_name
, system_user_group_id
, system_user_type
, last_name
, first_name
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( 4,’DBA’, 2, 1,’Puri’,’Manmohan’, 1, SYSDATE, 1, SYSDATE);
INSERT
INTO system_user
( system_user_id
, system_user_name
, system_user_group_id
, system_user_type
, last_name
, first_name
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( 5,’DBA’, 2, 1,’Fullerton’,’Gail’, 1, SYSDATE, 1, SYSDATE);
— ——————————————————————
— This seeds rows in a dependency chain, including the MEMBER, CONTACT
— ADDRESS, and TELEPHONE tables.
— ——————————————————————
— Insert record set #1.
— ——————————————————————
SELECT ‘INSERT MEMBER, CONTACT, ADDRESS, ETC’ AS “Section Header” FROM dual;
INSERT INTO member VALUES
( member_s1.nextval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘MEMBER’
AND common_lookup_column = ‘MEMBER_TYPE’
AND common_lookup_type = ‘GROUP’)
,’B293-71445′
,’1111-2222-3333-4444′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘MEMBER’
AND common_lookup_column = ‘CREDIT_CARD_TYPE’
AND common_lookup_type = ‘DISCOVER_CARD’)
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO contact
( contact_id
, member_id
, contact_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( contact_s1.nextval
, member_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘CONTACT’
AND common_lookup_column = ‘CONTACT_TYPE’
AND common_lookup_type = ‘CUSTOMER’)
,’Randi’,”,’Winn’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO address VALUES
( address_s1.nextval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘ADDRESS’
AND common_lookup_column = ‘ADDRESS_TYPE’
AND common_lookup_type = ‘HOME’)
,’San Jose’,’CA’,’95192′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO street_address VALUES
( street_address_s1.nextval
, address_s1.currval
, 1
,’10 El Camino Real’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO telephone VALUES
( telephone_s1.nextval
, address_s1.currval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘TELEPHONE’
AND common_lookup_column = ‘TELEPHONE_TYPE’
AND common_lookup_type = ‘HOME’)
,’USA’,’408′,’111-1111′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO contact
( contact_id
, member_id
, contact_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( contact_s1.nextval
, member_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘CONTACT’
AND common_lookup_column = ‘CONTACT_TYPE’
AND common_lookup_type = ‘CUSTOMER’)
,’Brian’,”,’Winn’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO address VALUES
( address_s1.nextval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘ADDRESS’
AND common_lookup_column = ‘ADDRESS_TYPE’
AND common_lookup_type = ‘HOME’)
,’San Jose’,’CA’,’95192′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO street_address VALUES
( street_address_s1.nextval
, address_s1.currval
, 1
,’10 El Camino Real’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO telephone VALUES
( telephone_s1.nextval
, address_s1.currval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘TELEPHONE’
AND common_lookup_column = ‘TELEPHONE_TYPE’
AND common_lookup_type = ‘HOME’)
,’USA’,’408′,’111-1111′
, 2, SYSDATE, 2, SYSDATE);
— ——————————————————————
— Insert record set #2.
— ——————————————————————
INSERT INTO member VALUES
( member_s1.nextval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘MEMBER’
AND common_lookup_column = ‘MEMBER_TYPE’
AND common_lookup_type = ‘GROUP’)
,’B293-71446′
,’2222-3333-4444-5555′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘MEMBER’
AND common_lookup_column = ‘CREDIT_CARD_TYPE’
AND common_lookup_type = ‘DISCOVER_CARD’)
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO contact
( contact_id
, member_id
, contact_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( contact_s1.nextval
, member_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘CONTACT’
AND common_lookup_column = ‘CONTACT_TYPE’
AND common_lookup_type = ‘CUSTOMER’)
,’Oscar’,”,’Vizquel’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO address VALUES
( address_s1.nextval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘ADDRESS’
AND common_lookup_column = ‘ADDRESS_TYPE’
AND common_lookup_type = ‘HOME’)
,’San Jose’,’CA’,’95192′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO street_address VALUES
( street_address_s1.nextval
, address_s1.currval
, 1
,’12 El Camino Real’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO telephone VALUES
( telephone_s1.nextval
, address_s1.currval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘TELEPHONE’
AND common_lookup_column = ‘TELEPHONE_TYPE’
AND common_lookup_type = ‘HOME’)
,’USA’,’408′,’222-2222′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO contact
( contact_id
, member_id
, contact_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( contact_s1.nextval
, member_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘CONTACT’
AND common_lookup_column = ‘CONTACT_TYPE’
AND common_lookup_type = ‘CUSTOMER’)
,’Doreen’,”,’Vizquel’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO address VALUES
( address_s1.nextval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘ADDRESS’
AND common_lookup_column = ‘ADDRESS_TYPE’
AND common_lookup_type = ‘HOME’)
,’San Jose’,’CA’,’95192′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO street_address VALUES
( street_address_s1.nextval
, address_s1.currval
, 1
,’12 El Camino Real’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO telephone VALUES
( telephone_s1.nextval
, address_s1.currval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘TELEPHONE’
AND common_lookup_column = ‘TELEPHONE_TYPE’
AND common_lookup_type = ‘HOME’)
,’USA’,’408′,’222-2222′
, 2, SYSDATE, 2, SYSDATE);
— ——————————————————————
— Insert record set #3.
— ——————————————————————
INSERT INTO member VALUES
( member_s1.nextval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘MEMBER’
AND common_lookup_column = ‘MEMBER_TYPE’
AND common_lookup_type = ‘GROUP’)
,’B293-71447′
,’3333-4444-5555-6666′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘MEMBER’
AND common_lookup_column = ‘CREDIT_CARD_TYPE’
AND common_lookup_type = ‘DISCOVER_CARD’)
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO contact
( contact_id
, member_id
, contact_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( contact_s1.nextval
, member_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘CONTACT’
AND common_lookup_column = ‘CONTACT_TYPE’
AND common_lookup_type = ‘CUSTOMER’)
,’Meaghan’,”,’Sweeney’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO address VALUES
( address_s1.nextval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘ADDRESS’
AND common_lookup_column = ‘ADDRESS_TYPE’
AND common_lookup_type = ‘HOME’)
,’San Jose’,’CA’,’95192′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO street_address VALUES
( street_address_s1.nextval
, address_s1.currval
, 1
,’14 El Camino Real’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO telephone VALUES
( telephone_s1.nextval
, address_s1.currval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘TELEPHONE’
AND common_lookup_column = ‘TELEPHONE_TYPE’
AND common_lookup_type = ‘HOME’)
,’USA’,’408′,’333-3333′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO contact
( contact_id
, member_id
, contact_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( contact_s1.nextval
, member_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘CONTACT’
AND common_lookup_column = ‘CONTACT_TYPE’
AND common_lookup_type = ‘CUSTOMER’)
,’Matthew’,”,’Sweeney’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO address VALUES
( address_s1.nextval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘ADDRESS’
AND common_lookup_column = ‘ADDRESS_TYPE’
AND common_lookup_type = ‘HOME’)
,’San Jose’,’CA’,’95192′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO street_address VALUES
( street_address_s1.nextval
, address_s1.currval
, 1
,’14 El Camino Real’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO telephone VALUES
( telephone_s1.nextval
, address_s1.currval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘TELEPHONE’
AND common_lookup_column = ‘TELEPHONE_TYPE’
AND common_lookup_type = ‘HOME’)
,’USA’,’408′,’333-3333′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO contact
( contact_id
, member_id
, contact_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( contact_s1.nextval
, member_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘CONTACT’
AND common_lookup_column = ‘CONTACT_TYPE’
AND common_lookup_type = ‘CUSTOMER’)
,’Ian’,’M’,’Sweeney’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO address VALUES
( address_s1.nextval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘ADDRESS’
AND common_lookup_column = ‘ADDRESS_TYPE’
AND common_lookup_type = ‘HOME’)
,’San Jose’,’CA’,’95192′
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO street_address VALUES
( street_address_s1.nextval
, address_s1.currval
, 1
,’14 El Camino Real’
, 2, SYSDATE, 2, SYSDATE);
INSERT INTO telephone VALUES
( telephone_s1.nextval
, address_s1.currval
, contact_s1.currval
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘TELEPHONE’
AND common_lookup_column = ‘TELEPHONE_TYPE’
AND common_lookup_type = ‘HOME’)
,’USA’,’408′,’333-3333′
, 2, SYSDATE, 2, SYSDATE);
/*
— ——————————————————————
— Insert values in ITEM table.
— —————————————————————— */
SELECT ‘INSERT ITEM’ AS “Section Header” FROM dual;
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0001US8F8′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Around the World in 80 Days’
,’Two-Disc Special Edition’
, empty_clob()
, NULL
,’NR’
,’MPAA’
,’18-MAY-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0002S64TQ’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Around the World in 80 Days’
,”
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’02-NOV-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000MNP2KI’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Casino Royale’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’13-MAR-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000MNP2K8′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Casino Royale’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’13-MAR-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JLBE’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Die Another Day’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’03-JUN-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000LY9CMC’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Die Another Day’
,’2-Disc Ultimate Version’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’03-JUN-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00008S2SF’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Die Another Day’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’03-JUN-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00000K0E5′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Golden Eye’
,’Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’22-OCT-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000M53GM2′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Golden Eye’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’06-FEB-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: 6304916558′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Tomorrow Never Dies’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’13-MAY-1998′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00000K0EA’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Tomorrow Never Dies’
,’Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’22-OCT-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: 6305784922′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The World Is Not Enough’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’16-MAY-2000′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000NIBURQ’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The World Is Not Enough’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’22-MAY-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00003CX95′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Brave Heart’
,”
, empty_clob()
, NULL
,’R’
,’MPAA’
,’29-AUG-2000′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: 6304712944′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Camelot’
,”
, empty_clob()
, NULL
,’G’
,’MPAA’
,’29-JUL-1998′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000B5XOZ2′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Christmas Carol’
,”
, empty_clob()
, NULL
,’NR’
,’MPAA’
,’08-NOV-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00000K3CJ’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Christmas Carol’
,”
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’05-OCT-1999′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0000AQS5D’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Scrooge’
,”
, empty_clob()
, NULL
,’G’
,’MPAA’
,’23-SEP-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: 6305127719′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Clear and Present Danger’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’21-OCT-1998′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00008K76V’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Clear and Present Danger’
,’Special Collector”s Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’06-MAY-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00003CXI1′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Harry Potter and the Sorcer”s Stone’
,’Two-Disc Special Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’28-MAY-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000062TU1′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Harry Potter and the Sorcer”s Stone’
,’Full Screen Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’28-MAY-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00008DDXC’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Harry Potter and the Chamber of Secrets’
,’Two-Disc Special Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’11-APR-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00008DDXL’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Harry Potter and the Chamber of Secrets’
,’Full Screen Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’11-APR-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JMAH’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Harry Potter and the Prisoner of Azkaban’
,’Two-Disc Special Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’23-NOV-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0002TT0NW’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Harry Potter and the Prisoner of Azkaban’
,’2-Disc Full Screen Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’23-NOV-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0002VB24K’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Harry Potter and the Chamber of Secrets’
,”
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’23-NOV-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000E6EK2Y’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Harry Potter and the Goblet of Fire’
,’Widescreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’07-MAR-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000E6EK38′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Harry Potter and the Goblet of Fire’
,’Full Screen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’07-MAR-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000E6EK3S’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Harry Potter and the Goblet of Fire’
,’Two-Disc Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’07-MAR-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000E6EZ3Z’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Harry Potter and the Order of the Phoenix’
,’Widescreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’11-DEC-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: 6305182043′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Hunt for Red October’
,”
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’01-DEC-1998′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00008K76U’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Hunt for Red October’
,’Special Collector”s Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’06-MAY-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0002YLCG0′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’King Arthur – The Director”s Cut’
,’Wide Screen Edition’
, empty_clob()
, NULL
,’R’
,’MPAA’
,’21-DEC-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0002YLCFQ’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’King Arthur’
,’PG-13 Full Screen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’21-DEC-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0002YLCG0′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’King Arthur – The Director”s Cut’
,’Wide Screen Edition’
, empty_clob()
, NULL
,’R’
,’MPAA’
,’21-DEC-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00003CWT6′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Lord of the Rings – Fellowship of the Ring’
,’Widescreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’06-AUG-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000065U3Q’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’The Lord of the Rings – Fellowship of the Ring’
,’Fullscreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’06-AUG-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000067DNF’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Lord of the Rings – Fellowship of the Ring’
,’Platinum Series Special Extended Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’12-NOV-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JKZV’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Lord of the Rings – Two Towers’
,’Widescreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’26-AUG-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00009APK1′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’The Lord of the Rings – Two Towers’
,’Fullscreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’26-AUG-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00009TB5G’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Lord of the Rings – Two Towers’
,’Platinum Series Special Extended Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’18-NOV-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JKZY’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Lord of the Rings – The Return of the King’
,’Widescreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’25-MAY-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0001US8E4′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’The Lord of the Rings – The Return of the King’
,’Fullscreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’25-MAY-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000634DCW’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Lord of the Rings – The Return of the King’
,’Platinum Series Special Extended Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’14-DEC-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: 6305222878′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Patriot Games’
,”
, empty_clob()
, NULL
,’R’
,’MPAA’
,’15-DEC-1998′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00008K76W’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Patriot Games’
,’Special Collector”s Edition’
, empty_clob()
, NULL
,’R’
,’MPAA’
,’06-MAY-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JM5E’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Pirates of the Caribbean – The Curse of the Black Pearl’
,’Two-Disc Collector”s Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’02-DEC-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0002XL342′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Pirates of the Caribbean – The Curse of the Black Pearl’
,’Three-Disc Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’02-NOV-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0000C6DWS’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Pirates of the Caribbean – The Curse of the Black Pearl’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’02-DEC-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000I0RQVI’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Pirates of the Caribbean – Dead Man”s Chest’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’05-DEC-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JP0F’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Pirates of the Caribbean – Dead Man”s Chest’
,’Two-Disc Collector”s Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’05-DEC-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0R0I0RDEI’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Pirates of the Caribbean – At World”s End’
,”
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’05-DEC-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0R0I0RRES’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Pirates of the Caribbean – At World”s End’
,’Two-Disc Collector”s Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’05-DEC-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00009Q98M’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Indiana Jones and the Raiders of the Lost Ark’
,’Widescreen Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’20-FEB-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000F21K66′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Indiana Jones and the Raiders of the Lost Ark’
,’Fullscreen Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’20-FEB-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000F7OMZ2′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Indiana Jones and the Temple of Doom’
,’Widescreen Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’20-FEB-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000F7OMZ3′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Indiana Jones and the Temple of Doom’
,’Fullscreen Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’20-FEB-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000F7OPCC’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Indiana Jones and the Last Crusade’
,’Widescreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’20-FEB-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000F7OPCR’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Indiana Jones and the Last Crusade’
,’Fullscreen Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’20-FEB-2003′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JKCH’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Spider-Man’
,’Widescreen Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’01-NOV-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00006F2TV’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Spider-Man’
,’Fullscreen Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’01-NOV-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JMQW’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Spider-Man 2′
,’Widescreen Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’30-NOV-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0002XK186′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Spider-Man 2′
,’Fullscreen Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’30-NOV-2004′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: C00005JMQW’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Spider-Man 3′
,’Widescreen Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’30-NOV-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: CB0002XK186′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Spider-Man 3′
,’Fullscreen Special Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’30-NOV-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00003CX5P’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Star Wars – Episode I’
,’The Phantom Menace’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’22-MAR-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00006HBUJ’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Star Wars – Episode II’
,’Attack of the Clones’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’22-MAR-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00006HBUI’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Star Wars – Episode II’
,’Attack of the Clones’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’22-MAR-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JLXH’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Star Wars – Episode III’
,’Revenge of the Sith’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’01-NOV-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00006HBUI’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Star Wars – Episode III’
,’Revenge of the Sith’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’01-NOV-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0006VIE4C’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Star Wars – Episode IV’
,’A New Hope’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’12-AUG-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0236VIE4D’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Star Wars – Episode IV’
,’A New Hope’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’12-AUG-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0006VIXGQ’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Star Wars – Episode V’
,’The Empire Strikes Back’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’12-AUG-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0026VJLGD’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Star Wars – Episode V’
,’The Empire Strikes Back’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’12-AUG-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00076SCPW’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Star Wars – Episode VI’
,’Return of the Jedi’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’12-AUG-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00276LJKZ’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Star Wars – Episode VI’
,’Return of the Jedi’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’12-AUG-2005′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JL8F’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Sum of All Fears’
,’Special Collector”s Edition’
, empty_clob()
, NULL
,’PG-13′
,’MPAA’
,’29-OCT-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00004XPPG’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Patriot’
,’Special Edition’
, empty_clob()
, NULL
,’R’
,’MPAA’
,’24-OCT-2000′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000ELL1S0′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’The Patriot’
,’Unrated Extended Cut’
, empty_clob()
, NULL
,’NR’
,’MPAA’
,’25-APR-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000068TPN’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’We Were Soldiers’
,”
, empty_clob()
, NULL
,’R’
,’MPAA’
,’20-AUG-2002′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000E8M0VA’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_WIDE_SCREEN’)
,’Chronicles of Narnia – The Lion, the Witch and the Wardrobe’
,’Widescreen Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’04-APR-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00005JO1X’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘DVD_FULL_SCREEN’)
,’Chronicles of Narnia – The Lion, the Witch and the Wardrobe’
,’Fullscreen Edition’
, empty_clob()
, NULL
,’PG’
,’MPAA’
,’04-APR-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0009EK534′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘GAMECUBE’)
,’Chronicles of Narnia – The Lion, the Witch and the Wardrobe’
,”
, empty_clob()
, NULL
,’T’
,’ESRB’
,’15-JUN-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0009ELZXI’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘PLAYSTATION2′)
,’Chronicles of Narnia – The Lion, the Witch and the Wardrobe’
,”
, empty_clob()
, NULL
,’T’
,’ESRB’
,’15-JUN-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B0009EP9DU’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘XBOX’)
,’Chronicles of Narnia – The Lion, the Witch and the Wardrobe’
,”
, empty_clob()
, NULL
,’T’
,’ESRB’
,’15-JUN-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000930DPU’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘GAMECUBE’)
,’Harry Potter: Goblet of Fire’
,”
, empty_clob()
, NULL
,’E10+’
,’ESRB’
,’15-JUN-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000930DQ4′
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘PLAYSTATION2′)
,’Harry Potter: Goblet of Fire’
,”
, empty_clob()
, NULL
,’E10+’
,’ESRB’
,’15-JUN-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000930DQE’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘XBOX’)
,’Harry Potter: Goblet of Fire’
,”
, empty_clob()
, NULL
,’E10+’
,’ESRB’
,’15-JUN-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000930DPU’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘GAMECUBE’)
,’Pirates of the Caribbean’
,”
, empty_clob()
, NULL
,’T’
,’ESRB’
,’15-JUN-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000FWAGNY’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘PLAYSTATION2′)
,’Pirates of the Caribbean’
,’The Legend of Jack Sparrow’
, empty_clob()
, NULL
,’T’
,’ESRB’
,’27-JUN-2006′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B00008V6TF’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘XBOX’)
,’Pirates of the Caribbean’
,”
, empty_clob()
, NULL
,’T’
,’ESRB’
,’22-MAY-2007′
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO item VALUES
( item_s1.nextval
,’ASIN: B000MK4G1M’
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_type = ‘XBOX’)
,’Pirates of the Caribbean’
,’At Worlds End’
, empty_clob()
, NULL
,’T’
,’ESRB’
,’22-MAY-2007′
, 3, SYSDATE, 3, SYSDATE);
— ——————————————————————
— Inserts 5 rentals with 9 dependent rental items. This section inserts
— 5 rows in the RENTAL table, then 9 rows in the RENTAL_ITEM table. The
— inserts into the RENTAL_ITEM tables use scalar subqueries to find the
— proper foreign key values by querying the RENTAL table primary keys.
— ——————————————————————
— Insert 5 records in the RENTAL table.
— ——————————————————————
SELECT ‘INSERT RENTAL’ AS “Section Header” FROM dual;
INSERT INTO rental VALUES
( rental_s1.nextval
,(SELECT contact_id
FROM contact
WHERE last_name = ‘Vizquel’
AND first_name = ‘Oscar’)
, SYSDATE, SYSDATE + 5
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental VALUES
( rental_s1.nextval
,(SELECT contact_id
FROM contact
WHERE last_name = ‘Vizquel’
AND first_name = ‘Doreen’)
, SYSDATE, SYSDATE + 5
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental VALUES
( rental_s1.nextval
,(SELECT contact_id
FROM contact
WHERE last_name = ‘Sweeney’
AND first_name = ‘Meaghan’)
, SYSDATE, SYSDATE + 5
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental VALUES
( rental_s1.nextval
,(SELECT contact_id
FROM contact
WHERE last_name = ‘Sweeney’
AND first_name = ‘Ian’)
, SYSDATE, SYSDATE + 5
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental VALUES
( rental_s1.nextval
,(SELECT contact_id
FROM contact
WHERE last_name = ‘Winn’
AND first_name = ‘Brian’)
, SYSDATE, SYSDATE + 5
, 3, SYSDATE, 3, SYSDATE);
/*
— ——————————————————————
— Insert 9 records in the RENTAL_ITEM table.
— —————————————————————— */
SELECT ‘INSERT RENTAL_ITEM’ AS “Section Header” FROM dual;
INSERT INTO rental_item
( rental_item_id
, rental_id
, item_id
, rental_item_price
, rental_item_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( rental_item_s1.nextval
,(SELECT r.rental_id
FROM rental r
, contact c
WHERE r.customer_id = c.contact_id
AND c.last_name = ‘Vizquel’
AND c.first_name = ‘Oscar’)
,(SELECT i.item_id
FROM item i
, common_lookup cl
WHERE i.item_title = ‘Star Wars – Episode I’
AND i.item_subtitle = ‘The Phantom Menace’
AND i.item_type = cl.common_lookup_id
AND cl.common_lookup_type = ‘DVD_WIDE_SCREEN’)
, 5
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘RENTAL_ITEM’
AND common_lookup_column = ‘RENTAL_ITEM_TYPE’
AND common_lookup_type = ‘5-DAY RENTAL’)
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental_item
( rental_item_id
, rental_id
, item_id
, rental_item_price
, rental_item_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( rental_item_s1.nextval
,(SELECT r.rental_id
FROM rental r inner join contact c
ON r.customer_id = c.contact_id
WHERE c.last_name = ‘Vizquel’
AND c.first_name = ‘Oscar’)
,(SELECT d.item_id
FROM item d join common_lookup cl
ON d.item_title = ‘Star Wars – Episode II’
WHERE d.item_subtitle = ‘Attack of the Clones’
AND d.item_type = cl.common_lookup_id
AND cl.common_lookup_type = ‘DVD_WIDE_SCREEN’)
, 5
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘RENTAL_ITEM’
AND common_lookup_column = ‘RENTAL_ITEM_TYPE’
AND common_lookup_type = ‘5-DAY RENTAL’)
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental_item
( rental_item_id
, rental_id
, item_id
, rental_item_price
, rental_item_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( rental_item_s1.nextval
,(SELECT r.rental_id
FROM rental r
, contact c
WHERE r.customer_id = c.contact_id
AND c.last_name = ‘Vizquel’
AND c.first_name = ‘Oscar’)
,(SELECT d.item_id
FROM item d
, common_lookup cl
WHERE d.item_title = ‘Star Wars – Episode III’
AND d.item_subtitle = ‘Revenge of the Sith’
AND d.item_type = cl.common_lookup_id
AND cl.common_lookup_type = ‘DVD_WIDE_SCREEN’)
, 5
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘RENTAL_ITEM’
AND common_lookup_column = ‘RENTAL_ITEM_TYPE’
AND common_lookup_type = ‘5-DAY RENTAL’)
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental_item
( rental_item_id
, rental_id
, item_id
, rental_item_price
, rental_item_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( rental_item_s1.nextval
,(SELECT r.rental_id
FROM rental r
, contact c
WHERE r.customer_id = c.contact_id
AND c.last_name = ‘Vizquel’
AND c.first_name = ‘Doreen’)
,(SELECT d.item_id
FROM item d
, common_lookup cl
WHERE d.item_title = ‘Tomorrow Never Dies’
AND d.item_subtitle IS NULL
AND d.item_type = cl.common_lookup_id
AND cl.common_lookup_type = ‘DVD_WIDE_SCREEN’)
, 5
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘RENTAL_ITEM’
AND common_lookup_column = ‘RENTAL_ITEM_TYPE’
AND common_lookup_type = ‘5-DAY RENTAL’)
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental_item
( rental_item_id
, rental_id
, item_id
, rental_item_price
, rental_item_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( rental_item_s1.nextval
,(SELECT r.rental_id
FROM rental r
, contact c
WHERE r.customer_id = c.contact_id
AND c.last_name = ‘Vizquel’
AND c.first_name = ‘Doreen’)
,(SELECT d.item_id
FROM item d
, common_lookup cl
WHERE d.item_title = ‘Around the World in 80 Days’
AND d.item_subtitle IS NULL
AND d.item_type = cl.common_lookup_id
AND cl.common_lookup_type = ‘DVD_WIDE_SCREEN’)
, 5
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘RENTAL_ITEM’
AND common_lookup_column = ‘RENTAL_ITEM_TYPE’
AND common_lookup_type = ‘5-DAY RENTAL’)
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental_item
( rental_item_id
, rental_id
, item_id
, rental_item_price
, rental_item_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( rental_item_s1.nextval
,(SELECT r.rental_id
FROM rental r
, contact c
WHERE r.customer_id = c.contact_id
AND c.last_name = ‘Sweeney’
AND c.first_name = ‘Meaghan’)
,(SELECT d.item_id
FROM item d
, common_lookup cl
WHERE d.item_title = ‘The Patriot’
AND d.item_subtitle = ‘Special Edition’
AND d.item_type = cl.common_lookup_id
AND cl.common_lookup_type = ‘DVD_WIDE_SCREEN’)
, 5
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘RENTAL_ITEM’
AND common_lookup_column = ‘RENTAL_ITEM_TYPE’
AND common_lookup_type = ‘5-DAY RENTAL’)
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental_item
( rental_item_id
, rental_id
, item_id
, rental_item_price
, rental_item_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( rental_item_s1.nextval
,(SELECT r.rental_id
FROM rental r
, contact c
WHERE r.customer_id = c.contact_id
AND c.last_name = ‘Sweeney’
AND c.first_name = ‘Ian’)
,(SELECT d.item_id
FROM item d
, common_lookup cl
WHERE d.item_title = ‘Spider-Man’
AND d.item_subtitle = ‘Fullscreen Special Edition’
AND d.item_type = cl.common_lookup_id
AND cl.common_lookup_type = ‘DVD_FULL_SCREEN’)
, 5
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘RENTAL_ITEM’
AND common_lookup_column = ‘RENTAL_ITEM_TYPE’
AND common_lookup_type = ‘5-DAY RENTAL’)
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental_item
( rental_item_id
, rental_id
, item_id
, rental_item_price
, rental_item_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( rental_item_s1.nextval
,(SELECT r.rental_id
FROM rental r
, contact c
WHERE r.customer_id = c.contact_id
AND c.last_name = ‘Winn’
AND c.first_name = ‘Brian’)
,(SELECT d.item_id
FROM item d
, common_lookup cl
WHERE d.item_title = ‘We Were Soldiers’
AND d.item_subtitle IS NULL
AND d.item_type = cl.common_lookup_id
AND cl.common_lookup_type = ‘DVD_WIDE_SCREEN’)
, 5
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘RENTAL_ITEM’
AND common_lookup_column = ‘RENTAL_ITEM_TYPE’
AND common_lookup_type = ‘5-DAY RENTAL’)
, 3, SYSDATE, 3, SYSDATE);
INSERT INTO rental_item
( rental_item_id
, rental_id
, item_id
, rental_item_price
, rental_item_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( rental_item_s1.nextval
,(SELECT r.rental_id
FROM rental r
, contact c
WHERE r.customer_id = c.contact_id
AND c.last_name = ‘Winn’
AND c.first_name = ‘Brian’)
,(SELECT d.item_id
FROM item d
, common_lookup cl
WHERE d.item_title = ‘The Hunt for Red October’
AND d.item_subtitle = ‘Special Collector”s Edition’
AND d.item_type = cl.common_lookup_id
AND cl.common_lookup_type = ‘DVD_WIDE_SCREEN’)
, 5
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = ‘RENTAL_ITEM’
AND common_lookup_column = ‘RENTAL_ITEM_TYPE’
AND common_lookup_type = ‘5-DAY RENTAL’)
, 3, SYSDATE, 3, SYSDATE);
— Write all prior to program units.
COMMIT;
— ———————————————-
— Create update_price_function.
— ———————————————-
SELECT ‘CREATE PRICE UPDATE FUNCTION’ AS “Section Header” FROM dual;
@@update_price_function.sql
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
lv_count INTEGER;
BEGIN
IF update_price = 0 THEN
SELECT COUNT(*) INTO lv_count FROM price;
dbms_output.put_line(‘Inserted Prices [‘||lv_count||’] records.’);
ELSE
dbms_output.put_line(‘No Prices Inserted.’);
END IF;
END;
/
BEGIN
FOR i IN (SELECT product
FROM product_component_version
WHERE REGEXP_LIKE(product,’.+11g.+’)) LOOP
dbms_output.put_line(‘Valid [‘||i.product||’].’);
END LOOP;
END;
/
— This creates a transactionally safe individual member insert
— into the MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, and
— TELEPHONE tables for Oracle 11g; it won’t run in Oracle 10g
— and you should use the contact_insert_10g.sql script in the
— create_store.sql when working on Oracle 10g XE.
@@contact_insert.sql
@@contact_insert_10g.sql
— Insert complete contact information using stored procedure.
— If you’re running on Oracle 10g XE, switch contact_insert to contact_insert_10g as
— a procedure call in the following statements.
EXECUTE contact_insert(‘INDIVIDUAL’,’R11-514-34′,’1111-1111-1111-1111′,’VISA_CARD’,’Clinton’,’Goeffrey’,’Ward’,’CUSTOMER’,’HOME’,’Provo’,’Utah’,’84606′,’118 South 9th East’,’HOME’,’011′,’801′,’423-1234′);
EXECUTE contact_insert(‘INDIVIDUAL’,’R11-514-35′,’1111-1111-1111-1111′,’VISA_CARD’,’Brandt’,’Henry’,’Ward’,’CUSTOMER’,’HOME’,’Provo’,’Utah’,’84606′,’118 South 9th East’,’HOME’,’011′,’801′,’423-1234′);
EXECUTE contact_insert(‘INDIVIDUAL’,’R11-514-36′,’1111-2222-1111-1111′,’VISA_CARD’,’Wendy’,’Jane’,’Moss’,’CUSTOMER’,’HOME’,’Provo’,’Utah’,’84606′,’1218 South 10th East’,’HOME’,’011′,’801′,’423-1234′);
EXECUTE contact_insert(‘INDIVIDUAL’,’R11-514-37′,’1111-1111-2222-1111′,’VISA_CARD’,’Jonah’,’Simon’,’Gretelz’,’CUSTOMER’,’HOME’,’Provo’,’Utah’,’84606′,’2118 South 7th East’,’HOME’,’011′,’801′,’423-1234′);
EXECUTE contact_insert(‘INDIVIDUAL’,’R11-514-38′,’1111-1111-1111-2222′,’MASTER_CARD’,’Jane’,’Elizabeth’,’Royal’,’CUSTOMER’,’HOME’,’Provo’,’Utah’,’84606′,’2228 South 14th East’,’HOME’,’011′,’801′,’423-1234′);
EXECUTE contact_insert_10g(‘INDIVIDUAL’,’R11-514-39′,’1111-1111-3333-1111′,’VISA_CARD’,’Brian’,’Nathan’,’Smith’,’CUSTOMER’,’HOME’,’Spanish Fork’,’Utah’,’84606′,’333 North 2nd East’,’HOME’,’011′,’801′,’423-1234′);
— Place Chapter 6 Procedure in database.
@@transaction_procedure.sql
— Update the address table.
UPDATE address
SET state_province = ‘California’
WHERE state_province = ‘CA’;
— Conditionally drop table.
BEGIN
FOR i IN (SELECT table_name
FROM user_tables
WHERE table_name = ‘AIRPORT’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE ‘||i.table_name||’ CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT sequence_name
FROM user_sequences
WHERE sequence_name = ‘AIRPORT_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE ‘||i.sequence_name;
END LOOP;
END;
/
— Create table.
CREATE TABLE airport
( airport_id NUMBER CONSTRAINT pk_airport PRIMARY KEY
, airport_code VARCHAR2(3) CONSTRAINT nn_airport1 NOT NULL
, airport_city VARCHAR2(30) CONSTRAINT nn_airport2 NOT NULL
, city VARCHAR2(30) CONSTRAINT nn_airport3 NOT NULL
, state_province VARCHAR2(30) CONSTRAINT nn_airport4 NOT NULL
, created_by NUMBER
, creation_date DATE
, last_updated_by NUMBER
, last_update_date DATE
, CONSTRAINT fk_airport1 FOREIGN KEY (created_by)
REFERENCES system_user (system_user_id)
, CONSTRAINT fk_airport2 FOREIGN KEY (last_updated_by)
REFERENCES system_user (system_user_id));
— Create sequence.
CREATE SEQUENCE airport_s1;
— Create unique index.
CREATE INDEX airport_u1 ON airport(airport_code, airport_city, city, state_province);
— Seed table.
INSERT INTO airport VALUES
( airport_s1.nextval, ‘LAX’, ‘Los Angeles’, ‘Los Angeles’, ‘California’, 2, SYSDATE, 2, SYSDATE);
INSERT INTO airport VALUES
( airport_s1.nextval, ‘SLC’, ‘Salt Lake City’, ‘Provo’, ‘Utah’, 2, SYSDATE, 2, SYSDATE);
INSERT INTO airport VALUES
( airport_s1.nextval, ‘SLC’, ‘Salt Lake City’,’Spanish Fork’, ‘Utah’, 2, SYSDATE, 2, SYSDATE);
INSERT INTO airport VALUES
( airport_s1.nextval, ‘SFO’, ‘San Francisco’, ‘San Francisco’, ‘California’, 2, SYSDATE, 2, SYSDATE);
INSERT INTO airport VALUES
( airport_s1.nextval, ‘SJC’, ‘San Jose’, ‘San Carlos’, ‘California’, 2, SYSDATE, 2, SYSDATE);
INSERT INTO airport VALUES
( airport_s1.nextval, ‘SJC’, ‘San Jose’, ‘San Jose’, ‘California’, 2, SYSDATE, 2, SYSDATE);
COLUMN airport_id FORMAT 9999
COLUMN airport_code FORMAT A3
COLUMN airport_city FORMAT A20
COLUMN city FORMAT A14
COLUMN state_province FORMAT A10
— Query table.
SELECT airport_id
, airport_code
, airport_city
, city
, state_province
FROM airport;
— Conditionally drop table.
BEGIN
FOR i IN (SELECT table_name
FROM user_tables
WHERE table_name = ‘ACCOUNT_LIST’) LOOP
EXECUTE IMMEDIATE ‘DROP TABLE ‘||i.table_name||’ CASCADE CONSTRAINTS’;
END LOOP;
FOR i IN (SELECT sequence_name
FROM user_sequences
WHERE sequence_name = ‘ACCOUNT_LIST_S1’) LOOP
EXECUTE IMMEDIATE ‘DROP SEQUENCE ‘||i.sequence_name;
END LOOP;
END;
/
— Create a account number table.
CREATE TABLE account_list
( account_list_id NUMBER CONSTRAINT pk_account_list PRIMARY KEY
, account_number VARCHAR2(10) CONSTRAINT nn_account_list1 NOT NULL
, consumed_date DATE
, consumed_by NUMBER
, created_by NUMBER
, creation_date DATE
, last_updated_by NUMBER
, last_update_date DATE
, CONSTRAINT fk_account_list1 FOREIGN KEY (consumed_by)
REFERENCES system_user(system_user_id)
, CONSTRAINT fk_account_list2 FOREIGN KEY (created_by)
REFERENCES system_user (system_user_id)
, CONSTRAINT fk_account_list3 FOREIGN KEY (last_updated_by)
REFERENCES system_user (system_user_id));
— Create sequence.
CREATE SEQUENCE account_list_s1;
— Create or replace seeding procedure.
CREATE OR REPLACE PROCEDURE seed_account_list IS
BEGIN
/* Set savepoint. */
SAVEPOINT all_or_none;

FOR i IN (SELECT DISTINCT airport_code FROM airport) LOOP
FOR j IN 1..50 LOOP

INSERT INTO account_list
VALUES
( account_list_s1.NEXTVAL
, i.airport_code||’-‘||LPAD(j,6,’0’)
, NULL
, NULL
, 2
, SYSDATE
, 2
, SYSDATE);

END LOOP;
END LOOP;

/* Commit the writes as a group. */
COMMIT;

EXCEPTION
WHEN OTHERS THEN
/* This undoes all DML statements to this point in the procedure. */
ROLLBACK TO SAVEPOINT all_or_none;
END;
/
— Execute seed_account_list procedure.
EXECUTE seed_account_list();
— Create UPDATE_MEMBER_ACCOUNT procedure.
CREATE OR REPLACE PROCEDURE update_member_account IS

/* Declare a local variable. */
lv_account_number VARCHAR2(10);

/* Declare a SQL cursor fabricated from local variables. */
CURSOR member_cursor IS
SELECT DISTINCT
m.member_id
, a.city
, a.state_province
FROM member m INNER JOIN contact c
ON m.member_id = c.member_id INNER JOIN address a
ON c.contact_id = a.contact_id
ORDER BY m.member_id;

BEGIN

/* Set savepoint. */
SAVEPOINT all_or_none;

/* Open a local cursor. */
FOR i IN member_cursor LOOP

/* Secure a unique account number as they’re consumed from the list. */
SELECT al.account_number
INTO lv_account_number
FROM account_list al INNER JOIN airport ap
ON SUBSTR(al.account_number,1,3) = ap.airport_code
WHERE ap.city = i.city
AND ap.state_province = i.state_province
AND consumed_by IS NULL
AND consumed_date IS NULL
AND ROWNUM < 2; /* Update a member with a unique account number linked to their nearest airport. */ UPDATE member SET account_number = lv_account_number WHERE member_id = i.member_id; /* Mark consumed the last used account number. */ UPDATE account_list SET consumed_by = 2 , consumed_date = SYSDATE WHERE account_number = lv_account_number; END LOOP; /* Commit the writes as a group. */ COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('You have an error in your AIRPORT table inserts.'); /* This undoes all DML statements to this point in the procedure. */ ROLLBACK TO SAVEPOINT all_or_none; WHEN OTHERS THEN /* This undoes all DML statements to this point in the procedure. */ ROLLBACK TO SAVEPOINT all_or_none; END; / -- Update member accounts. SET SERVEROUTPUT ON SIZE UNLIMITED EXECUTE update_member_account(); -- Format column for query results. --COLUMN member_id FORMAT 9999 HEADING "Member|ID #" --COLUMN members FORMAT 9999 HEADING "Contact|#'s" --COLUMN last_name FORMAT A14 HEADING "Last Name" --COLUMN member_type FORMAT 9999 HEADING "Member|Type" --COLUMN common_lookup_id FORMAT 9999 HEADING "Lookup|ID #" --COLUMN common_lookup_type FORMAT A12 HEADING "Lookup|Type" -- Verify MEMBER_TYPE values. --SELECT m.member_id --, COUNT(contact_id) AS MEMBERS --, m.account_number --, c.last_name --, m.member_type --, cl.common_lookup_id --, cl.common_lookup_type --FROM member m INNER JOIN contact c --ON m.member_id = c.member_id INNER JOIN common_lookup cl --ON m.member_type = cl.common_lookup_id --GROUP BY m.member_id --, m.account_number --, c.last_name --, m.member_type --, cl.common_lookup_id --, cl.common_lookup_type --ORDER BY m.member_id; -- Create a transaction view. CREATE OR REPLACE VIEW current_rental AS SELECT m.account_number , c.first_name || DECODE(c.middle_name,NULL,' ',' '||c.middle_name||' ') || c.last_name FULL_NAME , i.item_title TITLE , i.item_subtitle SUBTITLE , SUBSTR(cl.common_lookup_meaning,1,3) PRODUCT , r.check_out_date , r.return_date FROM common_lookup cl , contact c , item i , member m , rental r , rental_item ri WHERE r.customer_id = c.contact_id AND r.rental_id = ri.rental_id AND ri.item_id = i.item_id AND i.item_type = cl.common_lookup_id AND c.member_id = m.member_id ORDER BY 1,2,3; --COL full_name FORMAT A16 --COL title FORMAT A30 --COL subtitle FORMAT A4 --SELECT cr.full_name --, cr.title --, cr.product --, cr.check_out_date --, cr.return_date --FROM current_rental cr; @@insert_rental.sql @@insert_rental_items.sql -- Seed transaction table. DECLARE -- Declare local variables. lv_transaction_account VARCHAR2(20) := '222-22-22222'; lv_transaction_type NUMBER; lv_transaction_date DATE; lv_transaction_amount NUMBER; lv_payment_method NUMBER; lv_payment_account_number VARCHAR2(20) := '1000-10-43-20001'; -- Declare cursor to read rentals. CURSOR c1 IS SELECT rental_id , return_date FROM rental; -- Declare cursor to read rentals. CURSOR c2 (cv_rental_id NUMBER) IS SELECT SUM(ri.rental_item_price) AS transaction_price FROM rental_item ri WHERE ri.rental_id = cv_rental_id; BEGIN -- Get foreign key for column value. SELECT common_lookup_id INTO lv_transaction_type FROM common_lookup WHERE common_lookup_table = 'TRANSACTION' AND common_lookup_column = 'TRANSACTION_TYPE' AND common_lookup_type = 'DEBIT'; -- Get foreign key for column value. SELECT common_lookup_id INTO lv_payment_method FROM common_lookup WHERE common_lookup_table = 'TRANSACTION' AND common_lookup_column = 'PAYMENT_METHOD_TYPE' AND common_lookup_type = 'VISA_CARD'; -- Read rental table entries. FOR i IN c1 LOOP -- Aggregate prices. FOR j IN c2(i.rental_id) LOOP -- added test for NULL in case a rental has no items if j.transaction_price is null then continue; end if; lv_transaction_amount := j.transaction_price; -- Insert transaction records. INSERT INTO transaction ( transaction_id , transaction_account , transaction_type , transaction_date , transaction_amount , rental_id , payment_method , payment_account_number , created_by , creation_date , last_updated_by , last_update_date ) VALUES ( transaction_s1.NEXTVAL , lv_transaction_account , lv_transaction_type , i.return_date , lv_transaction_amount , i.rental_id , lv_payment_method , lv_payment_account_number , 3 , SYSDATE , 3 , SYSDATE ); END LOOP; END LOOP; END; / COMMIT; SPOOL OFF

PROG3040 Assignment 1 – Fall 2013 Glenn Paulley – 2A605, x2525

Assignment Type: INDIVIDUAL

Due Date: 2-October-2013 (Wednesday), 10 a.m.

Submission: via D2L drop-box; please take all of the files documented below and combine them into a single .zip file using WinZip so that your D2L submission is a single .zip file. In addition, paper output (script and results for marking) is due in class on 3 October 2013 (or before).

Instructions:

· Ensure that you have run the latest version of the database creation script files located in K:\GPAULLEY\PROG3040\DATABASES\PLSQL_WORKBOOK\storedb\create_workbook_store.sql to create the required tables and data.

· Familiarize yourself with the assignment standards. You should, at a minimum, capitalize SQL language keywords. Identifiers may be in lower case or mixed case – strive for consistency. Be neat.

Task 1. Database design and functional dependencies. [10 marks]

Closure: Certain functional dependencies imply others. Given a set
F
of functional dependencies that hold for a table
R
, the closure
F+
of that set is the set of all FDs implied by the FDs in
F
.
F+
can be computed by applying Armstrong’s axioms to
F
. Furthermore, given a subset
Z
of the attributes of a table
R
, and a set
F
of functional dependencies that hold for
R
, the closure
Z+
of
Z
is defined as the set
X
of the attributes of R such that the functional dependency Z X is a member of
F+
.

If the closures
F+
and
G+
of two sets of dependencies
F
and
G
are equal, then we say that
F
and
G
are equivalent.

To repeat from class, here are Armstrong’s axioms for functional dependencies, and additional axioms that can be derived from Armstrong’s original three axioms. Remember that X, Y, and Z are sets of attributes:

1. Inclusion: if Y is a subset of X, then X Y.

2. Augmentation: if X Y then WX WY.

3. Transitivity: if X Y and Y Z then X Z.

Other axioms:

· Self-determination: for any X, X X. X X is known as a trivial functional dependency.

· Union: if X Y and X Z then X YZ.

· Decomposition: if X YZ then X Y and X Z.

· Composition: if X Y and W Z, then XW YZ.

· Pseudo-transitivity: if X Y and WY Z then XW Z.

Example. Suppose we have a table R with non-null attributes A, B, C, D; we commonly write this as

R
( A, B, C, D).

Further suppose we have the following set
F
of non-trivial functional dependencies:

· A B

· B C

· BC D

We can determine the closure A+ of A as follows:

We are given A B. Trivially, attribute A functionally determines itself, so we have A A. Since we are given B C, by transitivity we have A C (because A B and B C, thus A C). Combining these through union, we have A ABC. Finally, with BC D, again by transitivity we have A D, because A BC and BC D, and therefore A D. Combining again, we have A ABCD, that is, A functionally determines all of the attributes of
R
. (Aside: hence A is called a superkey of
R
).

Task 1, Question 1:

Suppose we have the table
S
with non-null attributes A through G; that is, we have

S( A, B, C, D, E, F, G).

Suppose we are given that
S
satisfies the following set
G
of non-trivial functional dependencies:

· A B

· BC DE

· AEF G

(1a). Determine the closure { A,C }+ under this set
G
of functional dependencies; remember that closure includes self-determination. Remember from mathematics that a pair of braces ({}) denotes a set; hence {A,C} denotes a set made up of the union of attributes from the sets A and C.

Is the closure {A,C }+ equal to (choose one only):

i.) ABC

ii.) ABCDE

iii.) AC

iv.) ABCDEFG

v.) ACDFG

(1b). Is the functional dependency ACF DG implied by
G
? (Answer yes or no).

Submit your responses to 1(a) and 1(b) in a separate text file. Name your file Task1Q1XX.txt where XX are your first and last initials. You’ll include this file in your D2L submission.

Task 1, Question 2: [30 marks]

Create an SQL script that creates a set of tables in Boyce-Codd Normal Form to represent entities in an order/entry system. Note: as is often the case, there is no “right answer” for doing this; logical database design is always subject to tradeoffs because normalizing every functional dependency usually isn’t desirable. For example, we usually do not decompose the functional dependency address postal code because there is little value in doing so. An example from the schema used in the textbook are the superfluous tables for telephone numbers and street addresses.

This order/entry system will retain information similar to that modelled by the Video Store schema documented in the textbook, but with some differences. To prevent confusion with the Video Store tables, prefix all of your table names for this new set of tables with “PG$_”. Use lowercase and underscores for table and column names and for constraint names.

Your new schema is to include at least the following information:

· For each customer:

· Customer number (unique)

· Personal information (names)

· “Ship-to” addresses (multiple addresses per customer)

· Balance

· Credit Limit

· Discount percentage

· For each order:

· Order information: customer number, ship-to address, date of order

· Detail lines (several per order): item number, quantity ordered

· For each item:

· Item number (unique)

· Manufacturing plants (a plant may manufacture many items; each item may be manufactured by different plants)

· Quantity-on-hand at each plant

· Stock danger level at each plant

· Item description

· For each plant:

· Mailing Address

· Contact information

· Telephone number

· Assume that the company intends to handle tens of thousands of items in total, and thousands of orders per month.

· Also, for processing reasons a “quantity outstanding” value is associated with each detail line of each order; this value is initially set equal to the quantity of the item ordered and is progressively reduced to zero as (partial) shipments are made.

If you need to create surrogate keys for entities in this schema, choose data types and formats that are appropriate for the purpose. Include comment lines in your schema creation script that documents your proposed surrogate key formats, and why you chose those formats and data types. In addition to creating the appropriate tables and their columns, define the appropriate referential integrity constraints (primary keys and foreign keys) that relate the tables together. Give each referential constraint an appropriate name.

Your marks for this question will be based on the “reasonable-ness” of your design choices. You should provide commentary (in the form of comments in your SQL script) that explains your design choices, particularly if there might be debate about such a choice. Appropriate commentary will enhance your mark.

Create a script file (Task1Q2XX.sql, where XX are your initials in upper case letters) that creates the tables. You will include both your completed script file and its output file (Task1Q2XX.out) to your .zip file submission to D2L.

Task 2. SQL Queries [10 marks each]

Create a script file (Task2XX.sql, where XX are your initials in upper case letters) to combine the SQL queries for each problem below, in order. You will submit both your completed script file and its combined output file (Task2XX.out) to D2L.

1) Write a single SQL query that computes, for each contact, the average and sum of the rental transactions for rentals that were made in June 2011 (they could have possibly been returned in July or later). Include the overall grand total and overall transaction average in the result. You may not use a UNION query expression for your answer. For each contact, output:

a. Contact ID

b. Contact last name

c. Contact first name

d. Sum of the contact’s transactions for June 2011, in the format $9999.99

e. Average of the contact’s transactions for June 2011, in the format $99.99

f. For the grand total, substitute the literal string ‘June 2011 totals’ for the contact’s last name.

g. Output the list of contacts ordered by first name within last name.

Hints:

i. Use COALESCE() to substitute the ‘June 2011 totals’ string in the output

ii. Use GROUP BY ROLLUP

iii. Use a derived table in the FROM clause

iv. You will find the EXTRACT() and TO_CHAR() functions useful

2) Using the Video Store schema, write a query to produce a list of items that have never been rented. (Note: there is currently only one, with the Item ID of 1000). Rewrite your query in at least three different ways – that are semantically equivalent – that will return the identical result. Your rewrites may not be trivial ones – for example, reordering tables in the FROM clause or reordering predicates in the WHERE clause, or reversing or negating comparison operations.

3) Using the Video Store schema, write a query to produce a report that lists the number of times each of the Star Wars movies were rented in October, 2011. Include the total rental amount for each of the films. Consider only those movies in Wide Screen format on DVD. Output the ID of each film, its title, the number of times it was rented in October 2011 (use the column heading “Rentals”) and the total rental amount for each film, using proper money formatting in dollars and cents – use the column header “Total Amt” for this column. Order your result by film title.

4) Using the HR schema, write a query that lists all of the departments located in Seattle, Washington, along with their managers and the start date for each manager. If a department does not have a manager, output the value “Unknown”. Output the manager’s start date using the format “YYYY-MM-DD”. If the start date for that manager is unknown, also use the literal “Unknown”. Use appropriate headings for each column in the result. Order the list by department name.

Task 3. Supplementary task. [5 marks]

You have come into possession of a vintage VHS tape (on two cassettes) of the film “Lawrence of Arabia”. Add the tape to the Item table in the Video Store database using an INSERT statement that contains the necessary SELECT subquery to determine the proper item type code for this VHS double-set. COMMIT the change.

Once the film has been added to the database, re-run your queries from Task 2, Item 2. Ensure that your queries find both the new “Lawrence of Arabia” film, and the “Pirates of the Caribbean” film, both of which have yet to be rented.

After your query executes, delete the “Lawrence of Arabia” film using a DELETE statement, and COMMIT the deletion as part of your SQL script.

Create a script file (Task3ExtraXX.sql) that creates the new item, answers the queries, and deletes the item. Submit to D2L both your completed script file and output file (Task3ExtraXX.lst).

Turn in your highest-quality paper
Get a qualified writer to help you with

“ SQL Oracle Help ”

Get high-quality paper

NEW! AI matching with writer

Still stressed with your coursework?

Get quality coursework help from an expert!

Order now