Old Oracle Lab 4 at Database Tutorial

Old Oracle Lab 4

Lab #2: Oracle Assignment

You begin these steps by running the apply_oracle_lab2.sql and seed_oracle_store.sql script. A great starting point for this lab is to review the seed_oracle_store.sql script. You should use the script provided in the downloaded instance or create a script like:

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lab2/apply_oracle_lab2.sql
@/home/student/Data/cit225/oracle/lib/seed_oracle_store.sql
 
SPOOL apply_oracle_lab4.txt
 
... insert code here ...
 
SPOOL OFF

You should embed the verification queries inside your apply_oracle_lab4.sql script. All foreign key columns in steps 1 through 10 should be populated by subqueries that use the natural key values to discover the surrogate key values. No credit will be given for INSERT statements that use numeric literals for foreign key column values with an exception for both the CREATED_BY and LAST_UPDATED_BY columns.

Step 11 requires you to enter data values into the MEMBER_LAB, CONTACT_LAB, ADDRESS_LAB, STREET_ADDRESS_LAB, TELEPHONE_LAB, RENTAL_LAB, and RENTAL_ITEM_LAB tables. You will insert data into these tables in a specific order. The order depends on the degree that tables are unconstrained or constrained by their foreign key to primary key dependencies. That means you insert into the least dependent table first, which is the MEMBER_LAB table because it only has dependencies on the SYSTEM_USER_LAB and COMMON_LOOKUP_LAB tables. After inserting into the MEMBER_LAB table, you insert customer data into the CONTACT_LAB, ADDRESS_LAB table, STREET_ADDRESS_LAB table, and TELEPHONE_LAB tables. Then, you insert rows in the RENTAL_LAB and RENTAL_ITEM_LAB tables. Step 11 mimics how you would insert values into a series of tables in a dependent relationship from a web application form.

[1 point] Insert two rows into the SYSTEM_USER_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table by using the COMMON_LOOKUP_CONTEXT and COMMON_LOOKUP_TYPE columns in the WHERE clause. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: SYSTEM_USER_LAB
Column Name	Column Value #1	Column Value #2
SYSTEM_USER_ID	SYSTEM_USER_S1.NEXTVAL	SYSTEM_USER_S1.NEXTVAL
SYSTEM_USER_NAME	REACHERJ	OWENSR
SYSTEM_USER_GROUP_ID	Database Administrator	Database Administrator
SYSTEM_USER_TYPE	Home	Home
FIRST_NAME	Jack	Ray
MIDDLE_NAME
LAST_NAME	Reacher	Owens
CREATED_BY	System Administrator	System Administrator
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	System Administrator	System Administrator
LAST_UPDATE_DATE	Today’s date	Today’s date

You should use the following formatting and query to verify completion of this step:

1	SELECT * FROM system_user_lab;

It should display the following results:

SYSTEM_USER_ID SYSTEM_USER_NAME     SYSTEM_USER_GROUP_ID SYSTEM_USER_TYPE FIRST_NAME           MIDDLE_NAME          LAST_NAME            CREATED_BY CREATION_ LAST_UPDATED_BY LAST_UPDA
-------------- -------------------- -------------------- ---------------- -------------------- -------------------- -------------------- ---------- --------- --------------- ---------
             1 SYSADMIN                                1                1                                                                         1 29-DEC-14               1 29-DEC-14
          1001 REACHERJ                                2             1008 Jack                                      Reacher                       1 29-DEC-14               1 29-DEC-14
          1002 OWENSR                                  2             1008 Ray                                       Owens                         1 29-DEC-14               1 29-DEC-14

[1 point] Insert two rows into the COMMON_LOOKUP_LAB table:

Instruction Details ↓

Here you enter only the new values for the American Express and Diner’s Club cards.

Table Name: COMMON_LOOKUP_LAB
Column Name	Column Value #1	Column Value #2
COMMON_LOOKUP_LAB_ID	COMMON_LOOKUP_LAB_S1.NEXTVAL	COMMON_LOOKUP_LAB_S1.NEXTVAL
COMMON_LOOKUP_CONTEXT	MEMBER_LAB	MEMBER_LAB
COMMON_LOOKUP_TYPE	AMERICAN_EXPRESS_CARD	DINERS_CLUB_CARD
COMMON_LOOKUP_MEANING	American Express Card	Diners Club Card
CREATED_BY	System Administrator	System Administrator
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	System Administrator	System Administrator
LAST_UPDATE_DATE	Today’s date	Today’s date

You should use the following formatting and query to verify completion of this step:

SELECT   *
FROM     common_lookup_lab
WHERE    common_lookup_context = 'MEMBER_LAB'
AND      common_lookup_type IN ('AMERICAN_EXPRESS_CARD','DINERS_CLUB_CARD');

It should display the following results:

COMMON_LOOKUP_ID COMMON_LOOKUP_CONTEXT          COMMON_LOOKUP_TYPE             COMMON_LOOKUP_MEANING       CREATED_BY CREATION_ LAST_UPDATED_BY LAST_UPDA
---------------- ------------------------------ ------------------------------ ------------------------------ ---------- --------- --------------- ---------
            1016 MEMBER_LAB                     AMERICAN_EXPRESS_CARD          American Express Card                   1 29-DEC-14               1 29-DEC-14
            1017 MEMBER_LAB                     DINERS_CLUB_CARD               Diners Club Card                        1 29-DEC-14               1 29-DEC-14

[1 point] Insert two rows into the MEMBER_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: MEMBER_LAB
Column Name	Column Value #1	Column Value #2
MEMBER_LAB_ID	MEMBER_LAB_S1.NEXTVAL	MEMBER_LAB_S1.NEXTVAL
MEMBER_TYPE	Individual	Individual
ACCOUNT_NUMBER	X15-500-01	X15-500-02
CREDIT_CARD_NUMBER	9876-5432-1234-5678	9876-5432-1234-5679
CREDIT_CARD_TYPE	American Express Card	Diners Club Card
CREATED_BY	System Administrator	System Administrator
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	System Administrator	System Administrator
LAST_UPDATE_DATE	Today’s date	Today’s date

You should use the following formatting and query to verify completion of this step:

CLEAR COLUMNS
SELECT   m.member_lab_id
,        m.member_type
,        m.account_number
,        m.credit_card_number
,        cl.common_lookup_meaning AS credit_card_type
FROM     member_lab m INNER JOIN common_lookup_lab cl
ON       m.credit_card_type = cl.common_lookup_lab_id
WHERE    common_lookup_context = 'MEMBER_LAB'
AND      common_lookup_type IN ('AMERICAN_EXPRESS_CARD','DINERS_CLUB_CARD');

It should display the following results:

MEMBER_LAB_ID MEMBER_TYPE ACCOUNT_NU CREDIT_CARD_NUMBER  CREDIT_CARD_TYPE
------------- ----------- ---------- ------------------- ------------------------------
         1001        1003 X15-500-01 9876-5432-1234-5678 American Express Card
         1002        1003 X15-500-02 9876-5432-1234-5679 Diners Club Card

[1 point] Insert two rows into the CONTACT_LAB table:

Instruction Details ↓

Table Name: CONTACT_LAB
Column Name	Column Value #1	Column Value #2
CONTACT_LAB_ID	CONTACT_LAB_S1.NEXTVAL	CONTACT_LAB_S1.NEXTVAL
MEMBER_LAB_ID	MEMBER_LAB.MEMBER_LAB_ID	MEMBER_LAB.MEMBER_LAB_ID
CONTACT_TYPE	Customer	Customer
LAST_NAME	Jones	Jones
FIRST_NAME	John	Jane
CREATED_BY	SYSTEM_USER.SYSTEM_USER_ID	SYSTEM_USER.SYSTEM_USER_ID
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	SYSTEM_USER.SYSTEM_USER_ID	SYSTEM_USER.SYSTEM_USER_ID
LAST_UPDATE_DATE	Today’s date	Today’s date

For example, you need to use either the MEMBER_LAB_S1.CURRVAL value or the return value from a scalar subquery. You can use the MEMBER_LAB_S1.CURRVAL value when you just inserted the parent record in the MEMBER table and want to insert a copy of that parent record’s primary key value in the CONTACT table. You use a scalar subquery when you don’t want the sequence of INSERT statements to govern the selection of foreign key values. Both of these approaches are used in the seed_oracle_store.sql script, which you should use as a guide.

Use this when you’re managing the context by which table precedes the other:

INSERT INTO contact_lab
( ...
, member_lab_id
  ... )
VALUES
( ...
  member_lab_s1.currval
  ...);

Use this when you’re not managing the context by which table precedes the other. That means you need to use the account number literal value in place of the “some_string” value in the subquery below:

INSERT INTO contact_lab
( ...
, member_lab_id
  ... )
VALUES
( ...
,(SELECT m.member_lab_id
  FROM   member_lab m
  WHERE  m.account_number = 'some_string')  -- member_lab_id
,(SELECT common_lookup_lab_id
  FROM   common_lookup
  WHERE  common_lookup_context = 'CONTACT_LAB'
  AND    common_lookup_type = 'INDIVIDUAL') -- contact_type
  ...);

You should use the following formatting and query to verify completion of this step:

CLEAR COLUMNS
SELECT   c.contact_lab_id
,        m.credit_card_type
,        c.member_lab_id
,        c.contact_type
,        c.last_name
,        c.first_name
FROM     member_lab m INNER JOIN common_lookup_lab cl1
ON       m.credit_card_type = cl1.common_lookup_lab_id INNER JOIN contact_lab c
ON       m.member_lab_id = c.member_lab_id INNER JOIN common_lookup_lab cl2
ON       c.contact_type = cl2.common_lookup_lab_id
WHERE    cl1.common_lookup_context = 'MEMBER_LAB'
AND      cl1.common_lookup_type IN ('AMERICAN_EXPRESS_CARD','DINERS_CLUB_CARD')
AND      cl2.common_lookup_context = 'CONTACT_LAB'
AND      cl2.common_lookup_type = 'CUSTOMER';

It should display the following results:

CONTACT_LAB_ID CREDIT_CARD_TYPE MEMBER_LAB_ID CONTACT_TYPE LAST_NAME            FIRST_NAME
-------------- ---------------- ------------- ------------ -------------------- --------------------
          1001             1016          1001         1002 Jones                John
          1002             1016          1001         1002 Jones                Jane

[1 point] Insert two rows into the ADDRESS_LAB table::

Instruction Details ↓

Table Name: ADDRESS_LAB
Column Name	Column Value #1	Column Value #2
ADDRESS_LAB_ID	ADDRESS_LAB_S.NEXTVAL	ADDRESS_LAB_S.NEXTVAL
CONTACT_LAB_ID	CONTACT_LAB Foreign Key	CONTACT_LAB Foreign Key
ADDRESS_TYPE	Home	Home
CITY	Draper	Draper
STATE_PROVINCE	Utah	Utah
POSTAL_CODE	84020	84020
CREATED_BY	System Administrator	System Administrator
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	System Administrator	System Administrator
LAST_UPDATE_DATE	Today’s date	Today’s date

You should use the following formatting and query to verify completion of this step:

CLEAR COLUMNS
SELECT   c.contact_lab_id
,        a.address_type
,        c.first_name
,        c.last_name
,        a.city
,        a.state_province
,        a.postal_code
FROM     contact_lab c INNER JOIN common_lookup_lab cl1
ON       c.contact_type = cl1.common_lookup_lab_id INNER JOIN address_lab a
ON       c.contact_lab_id = a.contact_lab_id INNER JOIN common_lookup_lab cl2
ON       a.address_type = cl2.common_lookup_lab_id
WHERE    cl1.common_lookup_context = 'CONTACT_LAB'
AND      cl1.common_lookup_type = 'CUSTOMER'
AND      cl2.common_lookup_context = 'MULTIPLE'
AND      cl2.common_lookup_type = 'HOME';

It should display the following results:

CONTACT_LAB_ID ADDRESS_TYPE FIRST_NAME           LAST_NAME            CITY                           STATE_PROVINCE                 POSTAL_CODE
-------------- ------------ -------------------- -------------------- ------------------------------ ------------------------------ --------------------
          1001         1008 John                 Jones                Draper                         Utah                           84020
          1002         1008 Jane                 Jones                Draper                         Utah                           84020

[1 point] Insert two rows into the STREET_ADDRESS_LAB table:

Instruction Details ↓

Table Name: STREET_ADDRESS_LAB
Column Name	Column Value #1	Column Value #2
STREET_ADDRESS_LAB_ID	STREET_ADDRESS_LAB_S.NEXTVAL	STREET_ADDRESS_LAB_S.NEXTVAL
ADDRESS_LAB_ID	ADDRESS_LAB Foreign Key	ADDRESS_LAB Foreign Key
STREET_ADDRESS	372 East 12300 South	1872 West 5400 South
CREATED_BY	System Administrator	System Administrator
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	System Administrator	System Administrator
LAST_UPDATE_DATE	Today’s date	Today’s date

You should use the following formatting and query to verify completion of this step:

CLEAR COLUMNS
SELECT   c.contact_lab_id
,        a.address_lab_id
,        a.address_type
,        c.first_name
,        c.last_name
,        sa.street_address
,        a.city
,        a.state_province
,        a.postal_code
FROM     contact_lab c INNER JOIN common_lookup_lab cl1
ON       c.contact_type = cl1.common_lookup_lab_id INNER JOIN address_lab a
ON       c.contact_lab_id = a.contact_lab_id INNER JOIN street_address_lab sa
ON       a.address_lab_id = sa.address_lab_id INNER JOIN common_lookup_lab cl2
ON       a.address_type = cl2.common_lookup_lab_id
WHERE    cl1.common_lookup_context = 'CONTACT_LAB'
AND      cl1.common_lookup_type = 'CUSTOMER'
AND      cl2.common_lookup_context = 'MULTIPLE'
AND      cl2.common_lookup_type = 'HOME';

It should display the following results:

CONTACT_LAB_ID ADDRESS_LAB_ID ADDRESS_TYPE FIRST_NAME           LAST_NAME            STREET_ADDRESS                 CITY                           STATE_PROVINCE                 POSTAL_CODE
-------------- -------------- ------------ -------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ --------------------
          1001           1001         1008 John                 Jones                372 East 12300 South           Draper                         Utah                           84020
          1002           1002         1008 Jane                 Jones                1872 West 5400 South           Draper                         Utah                           84020

[1 point] Insert two rows into the TELEPHONE_LAB table:

Instruction Details ↓

Table Name: TELEPHONE_LAB
Column Name	Column Value #1	Column Value #2
TELEPHONE_LAB_ID	TELEPHONE_LAB_S1.NEXTVAL	TELEPHONE_LAB_S1.NEXTVAL
CONTACT_LAB_ID	CONTACT_LAB Foreign Key	CONTACT_LAB Foreign Key
ADDRESS_LAB_ID	ADDRESS_LAB Foreign Key	ADDRESS_LAB Foreign Key
TELEPHONE_TYPE	Home	Home
COUNTRY_CODE	001	001
AREA_CODE	801	801
TELEPHONE_CODE	435-7654	435-7655
CREATED_BY	System Administrator	System Administrator
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	System Administrator	System Administrator
LAST_UPDATE_DATE	Today’s date	Today’s date

You should use the following formatting and query to verify completion of this step:

CLEAR COLUMNS
SELECT   c.contact_lab_id
,        t.telephone_lab_id
,        t.telephone_type
,        c.first_name
,        c.last_name
,        t.country_code
,        t.area_code
,        t.telephone_number
FROM     contact_lab c INNER JOIN telephone_lab t
ON       c.contact_lab_id = t.contact_lab_id
WHERE    c.first_name IN ('John','Jane')
AND      c.last_name = 'Jones';

It should display the following results:

CONTACT_LAB_ID TELEPHONE_LAB_ID TELEPHONE_TYPE FIRST_NAME           LAST_NAME            COU AREA_C TELEPHONE_
-------------- ---------------- -------------- -------------------- -------------------- --- ------ ----------
          1001             1001           1008 John                 Jones                001 801    389-0687
          1002             1002           1008 Jane                 Jones                001 801    389-0688

[1 point] Insert two rows into the RENTAL_LAB table:

Instruction Details ↓

Table Name: RENTAL_LAB
Column Name	Column Value #1	Column Value #2
RENTAL_LAB_ID	RENTAL_LAB_S1.NEXTVAL	RENTAL_LAB_S1.NEXTVAL
CUSTOMER_ID	CONTACT_LAB Foreign Key	CONTACT_LAB Foreign Key
CHECK_OUT_DATE	January 2, 2015	January 3, 2015
RETURN_DATE	January 6, 2015	January 5, 2015
CREATED_BY	System Administrator	System Administrator
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	System Administrator	System Administrator
LAST_UPDATE_DATE	Today’s date	Today’s date

You should use the following formatting and query to verify completion of this step:

CLEAR COLUMNS
SELECT   r.rental_lab_id
,        r.customer_id
,        r.check_out_date
,        r.return_date
FROM     rental_lab r 
WHERE    r.check_out_date IN ('02-JAN-2015','03-JAN-2015');

It should display the following results:

RENTAL_LAB_ID CUSTOMER_ID CHECK_OUT RETURN_DA
------------- ----------- --------- ---------
         1001        1001 02-JAN-15 06-JAN-15
         1002        1002 03-JAN-15 05-JAN-15

[1 points] Insert two rows into the ITEM_LAB table:

Instruction Details ↓

Table Name: ITEM_LAB
Column Name	Column Value #1	Column Value #2
ITEM_LAB_ID	ITEM_LAB_S1.NEXTVAL	ITEM_LAB_S1.NEXTVAL
ITEM_BARCODE	B00N1JQ2UO	B00OY7YPGK
ITEM_TYPE	DVD Wide Screen	Blu-ray
ITEM_TITLE	Guardians of the Galaxy	The Maze Runner
ITEM_SUBTITLE
ITEM_RATING	PG-13	PG-13
ITEM_RELEASE_DATE	Dec 9, 2014	Dec 16, 2014
CREATED_BY	System Administrator	System Administrator
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	System Administrator	System Administrator
LAST_UPDATE_DATE	Today’s date	Today’s date

You should use the following formatting and query to verify completion of this step:

CLEAR COLUMNS
SELECT   i.item_lab_id
,        i.item_title
,        i.item_rating
,        i.item_release_date
FROM     item_lab i 
WHERE    i.item_release_date IN ('09-DEC-2014','16-DEC-2014');

It should display the following results:

ITEM_LAB_ID ITEM_TITLE                                                   ITEM_RAT ITEM_RELE
----------- ------------------------------------------------------------ -------- ---------
       1001 Guardians of the Galaxy                                      PG-13    09-DEC-14
       1002 The Maze Runner                                              PG-13    16-DEC-14

[1 point] Insert two rows into the RENTAL_ITEM_LAB table:

Instruction Details ↓

Table Name: RENTAL_ITEM_LAB
Column Name	Column Value #1	Column Value #2
RENTAL_ITEM_LAB_ID	RENTAL_ITEM_LAB_S1.NEXTVAL	RENTAL_ITEM_LAB_S1.NEXTVAL
RENTAL_ID	RENTAL_LAB Foreign Key	RENTAL_LAB Foreign Key
ITEM_ID	ITEM_LAB Foreign Key	ITEM_LAB Foreign Key
CREATED_BY	System Administrator	System Administrator
CREATION_DATE	Today’s date	Today’s date
LAST_UPDATED_BY	System Administrator	System Administrator
LAST_UPDATE_DATE	Today’s date	Today’s date

You should use the following formatting and query to verify completion of this step:

CLEAR COLUMNS
SELECT   ri.rental_item_lab_id
,        ri.rental_lab_id
,        ri.item_lab_id
FROM     rental_item_lab ri INNER JOIN rental_lab r
ON       r.rental_lab_id = ri.rental_lab_id INNER JOIN item_lab i
ON       i.item_lab_id = ri.item_lab_id
WHERE    r.rental_lab_id IN (SELECT   r.rental_lab_id
                             FROM     rental_lab r 
                             WHERE    r.check_out_date IN ('02-JAN-2015','03-JAN-2015'))
AND      i.item_lab_id IN (SELECT   i.item_lab_id
                           FROM     item_lab i
                           WHERE    item_title IN ('Guardians of the Galaxy','The Maze Runner')
                           AND      item_release_date IN ('09-DEC-2014','16-DEC-2014'));

It should display the following results:

RENTAL_ITEM_LAB_ID RENTAL_LAB_ID ITEM_LAB_ID
------------------ ------------- -----------
              1001          1001        1001
              1002          1002        1002

[10 points] You insert rows into the a series of tables differently than you insert rows individually. For example, you insert the primary key by using the sequence and .NEXTVAL pseudo column value; and you insert the foreign key by using the sequence and .CURRVAL pseudo column value. You can read more about the .NEXTVAL and .CURRVAL pseudo columns on this web page on automatic numbering.

Instruction Details ↓

This step requires you to enter data values into the MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, TELEPHONE, RENTAL, and RENTAL_ITEM tables. As mentioned earlier, you need to insert data into these tables in a specific order. The order depends on the degree that tables are unconstrained or constrained by their foreign key to primary key dependencies.

The video store model for the course has five key components. They are these:

An Access Control List (ACL), which lets tracks individuals who enter and update data.
A common lookup repository, which hosts a series of small tables to support various end-user forms.
Customer information, which supports member accounts, customers, and their addresses and telephone numbers.
An inventory system for Blu-ray and DVD items.
A order management component that supports rental agreements with multiple rental items.

You’ve already seeded a value in the SYSTEM_USER table (or the ACL component) and COMMON_LOOKUP table (or common lookup repository). This step skips those tables. In this step you mimic setting up a new customer and renting them their first set of videos.

You create a customer by determining the least to most dependent tables. To simplify this step the order is:

You insert into the MEMBER table because it only has dependencies on the SYSTEM_USER and COMMON_LOOKUP tables, which are outside of the customer information process.
You insert into the MEMBER table to establish an account that support multiple parties, like family members.
You insert customer data into the CONTACT, ADDRESS table, STREET_ADDRESS table, and TELEPHONE tables respectively for all family members because they may have different addresses and telephone numbers.
You insert a row in the RENTAL table for a rental agreement and one row into the RENTAL_ITEM table for each item attached to the rental.

You must write the INSERT statements in order and use the Oracle .NEXTVAL and .CURRVAL to manage sequence values for the surrogate primary and foreign key column values.

You will insert an account for Peter Quill and Yondu Udonta as a family. Yondu has kidnapped Peter and treats him as an adopted child. Yondu is setting up the account based on the following information because Peter likes classic 1970 and 1980 Terran films:

Customer Entry
Column Name	Column Value #1	Column Value #2
MEMBER_TYPE	Group Membership
ACCOUNT_NUMBER	X21-777-01
CREDIT_CARD_NUMBER	9876-5432-1234-5678
CREDIT_CARD_TYPE	Discover Card
CONTACT_TYPE	Group	Group
LAST_NAME	Udonta	Quill
FIRST_NAME	Yondu	Peter
ADDRESS_TYPE	Home	Home
STREET_ADDRESS	12129 South State Street	12129 South State Street
CITY	Draper	Draper
STATE_PROVINCE	Utah	Utah
POSTAL_CODE	84020	84020
TELEPHONE_TYPE	Home	Home
COUNTRY_CODE	001	001
AREA_CODE	801	801
TELEPHONE_CODE	342-8940	342-8941

The following query should return the following four rows. The first two rows come from the earlier INSERT statements, and the latter two from the INSERT statements that use the .NEXTVAL and .CURRVAL pseudo columns:

SELECT   m.member_type
,        m.account_number
,        m.credit_card_number
,        m.credit_card_type
,        c.first_name
,        c.last_name
,        sa.street_address
,        a.city
,        a.state_province
,        a.postal_code
,        t.country_code||' ('||t.area_code||') '||t.telephone_number
FROM     member_lab m INNER JOIN contact_lab c
ON       m.member_lab_id = c.member_lab_id INNER JOIN address_lab a
ON       c.contact_lab_id = a.contact_lab_id INNER JOIN street_address_lab sa
ON       a.address_lab_id = sa.address_lab_id INNER JOIN telephone_lab t
ON       c.contact_lab_id = t.contact_lab_id
AND      a.address_lab_id = t.address_lab_id;

These are the rows that should be returned:

MEMBER_TYPE ACCOUNT_NU CREDIT_CARD_NUMBER  CREDIT_CARD_TYPE FIRST_NAME           LAST_NAME            STREET_ADDRESS                 CITY                           STATE_PROVINCE                 POSTAL_CODE          T.COUNTRY_CODE||'('||T
----------- ---------- ------------------- ---------------- -------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ -------------------- -----------------------
       1003 X15-500-01 9876-5432-1234-5678             1016 John                 Jones                372 East 12300 South           Draper                         Utah                           84020                001 (801) 389-0687
       1003 X15-500-01 9876-5432-1234-5678             1016 Jane                 Jones                1872 West 5400 South           Draper                         Utah                           84020                001 (801) 389-0688
       1004 X21-777-01 8765-1234-9988-8899             1016 Yondu                Udonta               12129 South State Street       Draper                         Utah                           84020                001 (801) 342-8940
       1004 X21-777-01 8765-1234-9988-8899             1016 Peter                Quill                12129 South State Street       Draper                         Utah                           84020                001 (801) 342-8940

After entering a customer, you can now enter a two item rental with the following data:

Rental Entry
Column Name	Column Value #1	Column Value #2
CHECK_OUT_DATE	January 8, 2015	January 9, 2015
RETURN_DATE	January 12, 2015	January 11, 2015
ITEM_ID	Guardians of the Galaxy	The Maze Runners

SELECT   r.check_out_date
,        r.return_date
,        ri.rental_item_lab_id
,        i.item_title
FROM     rental_lab r INNER JOIN rental_item_lab ri
ON       r.rental_lab_id = ri.rental_lab_id INNER JOIN item_lab i
ON       ri.item_lab_id = i.item_lab_id
WHERE    r.check_out_date IN ('08-JAN-2015','09-JAN-2015');

These are the rows that should be returned:

CHECK_OUT RETURN_DA RENTAL_ITEM_LAB_ID ITEM_TITLE
--------- --------- ------------------ ---------------------------
08-JAN-15 12-JAN-15               1004 Guardians of the Galaxy
09-JAN-15 11-JAN-15               1006 The Maze Runner

Written by michaelmclaughlin

May 4th, 2018 at 4:49 pm

Posted in

Database Tutorial

Old Oracle Lab 4

My Technical Blog

Tutorial Pages

Blogroll