Database Tutorial

Course Tutorial Site

Site Admin

Lab #4 : Insert Patterns

with 44 comments

Lab #3: Oracle Assignment Underdevelopment

Objectives

The lab teaches you how to work with sequential and non-sequential INSERT statements across a dependent series of tables. Also, the lab teaches you how to call an API, use subqueries and literal values in the VALUES clause of the INSERT statement; and how to use a derived table instead of a table or view in a query and how to perform a correlated UPDATE statement.

The detail learning objectives are:

  • Learn how to write a set of sequential INSERT statements across a set of dependent tables.
  • Learn how to write a set of non-sequential INSERT statement by leveraging queries to discover appropriate primary key values in an independent table that you can use as foreign key values in a related dependent table.
  • Learn how to use the .NEXTVAL pseudo column in one table as a primary key value; and the .CURRVAL pseudo column in another table with a dependency on the former table as a foreign key value.
  • Learn how to call a store procedure that acts like an API (Application Programming Interface) to a set of tables.
  • Learn how to write a subquery as a derived table (or the result of a table subquery) inside the FROM clause.
  • Learn how to correlate (or join) the results of a subquery (a correlated subquery) with an external UPDATE statement.

Business Scenario

Building data-centric application software often requires that you pre-seed data in certain database tables. The process of pre-seeding involves inserting rows of data. Sometimes the Entity Relationship Diagram (ERD) can create conflicts, which require you to temporarily disable constraints to insert data.

The lab is designed to teach you how to write INSERT statements. The INSERT statements use:

  • Literal values
  • Pseudo columns for sequence values
  • Subqueries that retrieve surrogate primary key values from tables by using the natural keys of the tables

You will disable foreign key constraints and remove not null constraints to pre-seed some of the tables with data. After inserting the new data, you will add re-enable foreign key constraints and re-add not null constraints. You should use the preseed_oracle_store.sql script as a reference model for SQL syntax.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

Instructional Material →

The create_oracle_store2.sql script creates ten tables as the video store. It is an integration script that calls individual scripts that create individual tables. Each of those CREATE and ALTER SQL scripts contain diagnostic queries that verify the integrity of their Data Manipulation Language (DML) statements.

Students modified the DML statements and created ten tables that mirror the video store table in Lab #2. The mirrored tables all have the same table names plus an _LAB suffix. The surrogate key columns of the ten tables have column table names that adopt the convention of table name plus an _ID suffix.

The video store model uses the following ERD (double-click on the image to view it in full screen mode):

The Video Store ERD shows you the physical relationship between tables, which are all one-to-many relationships. All relationships between the tables are composed of two relations. The one side points to the parent primary key in a relationship between two tables; and the many side points to the foreign key, which is a copy of the primary key column’s value.

The grandma and tweetie_bird tables show you how these one-to-many relationships work between tables with foreign key constraints. They also show you how INSERT and UPDATE statements work.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

/* Conditionally drop the GRANDMA and TWEETIE_BIRD tables, and
   the GRANDMA_SEQ and TWEETIE_BIRD sequences. */
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('GRANDMA','GRANDMA_SEQ'
                                    ,'TWEETIE_BIRD','TWEETIE_BIRD_SEQ')
            ORDER BY object_type ) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
/* Create the grandma table:
    - A natural key of grandma_house and grandma_name columns.
    - A surrogate and primary key column that uses the table name 
      plus _ID suffix.
    - A primary key constraint on the surrogate key column.
    - A uq_grandma unique index across the two natural key columns.
*/
CREATE TABLE GRANDMA
( grandma_id     NUMBER       CONSTRAINT pk_grandma    PRIMARY KEY
, grandma_house  VARCHAR2(30) CONSTRAINT nn_grandma_1  NOT NULL
, grandma_name   VARCHAR2(30) CONSTRAINT nn_grandma_2  NOT NULL
, CONSTRAINT uq_grandma       UNIQUE (grandma_house, grandma_name));
 
/* Create the grandma_seq sequence. */
CREATE SEQUENCE grandma_seq;
 
/* Create the tweetie_bird table with:
    - A natural key of tweetie_bird_house and tweetie_bird columns.
    - A surrogate and primary key column that uses the table name
    - plus an _ID suffix.
    - A primary key constraint on the surrogate key column.
    - A foreign key constraint on the grandma_id column of the grandma table.
    - A uq_tweetie_bird unique index across the two natural key columns.
*/
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id    NUMBER        CONSTRAINT tweetie_bird_nn1 NOT NULL
, tweetie_bird_name  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn2 NOT NULL
, grandma_id         NUMBER        CONSTRAINT tweetie_bird_nn3 NOT NULL
, CONSTRAINT pk_tweetie_bird       PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT fk_tweetie_bird       FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
, CONSTRAINT uq_tweetie_bird       UNIQUE (tweetie_bird_name));
 
/* Create the tweetie_bird_seq sequence. */
CREATE SEQUENCE tweetie_bird_seq;

You can now describe the grandma and tweetie_bird tables. You must insert values into the independent or unconstrained grandma table before you insert a row in the tweetie_bird table.

Insert statements are ACID-compliant transactions. ACID is an acronym for Atomic, Consistency, Isolated, and Durable. Atomic transactions are all or nothing transactions. Consistency means that whether you process the inserts serially or in parallel will succeed. Isolated means that you can’t see partial results until they’re complete. Durable means that the transactions becomes permanent.

SQL provides Transaction Control Language (TCL) to manage sets of INSERT statements across more than two tables. The following example will show you how to insert data across a pair of tables. The INSERT statement will use the .NEXTVAL pseudo column for primary keys and the .CURRVAL pseudo column for foreign keys.

/* Set the save point like a book mark sets a page number. If any part
   of the transaction fails, you return to this point by rolling back
   the parts that did complete. */
SAVEPOINT starting_place;
 
/* Insert into grandma table. */
INSERT INTO grandma
( grandma_id
, grandma_house
, grandma_name )
VALUES
( grandma_seq.NEXTVAL
,'Yellow'
,'Hazel');
 
/* Insert into tweetie_bird table. */
INSERT INTO tweetie_bird
( tweetie_bird_id 
, tweetie_bird_name
, grandma_id )
VALUES
( tweetie_bird_seq.NEXTVAL
,'Henry'
, grandma_seq.CURRVAL );

If one of the inserts failed, you would issue a:

ROLLBACK TO starting_place;

A ROLLBACK statement like that undoes everything after the SAVEPOINT statement. It basically returns the data in the grandma and tweetie_bird tables to their states before you attempted the INSERT statements.

If both of the INSERT statements succeeded, you would make their changes permanent with a:

COMMIT;

The COMMIT statement makes your changes permanent and visible to other sessions of the same student user and to other database users to whom you’ve granted access to the grandma and tweetie_bird tables.

The lab has ten parts. Each of the parts links to a SQL script file, and you should treat each file as a distinct use case that requires unit testing. The last part actually calls the nine SQL scripts in sequence and becomes an integration test of your code.

Lab Description

[25 points] Click the Lab Instructions link to open the instructions inside the current webpage.

The lab requires that you run two preparation scripts, which in their respective sequence of operation call other scripts. You will call the seeding.sql script from the /home/student/Data/cit225/oraclelib2/seed directory.

Its recommended that you create your apply_oracle_lab4.sql script in the lab4 directory first. While you’ll add to this file later, the initial file should have the following functional lines:

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lab3/apply_oracle_lab3.sql
@/home/student/Data/cit225/oracle/lib2/seed/seeding.sql
 
-- insert calls to other SQL script files here, in the order that they appear in the seeding.sql script ...
 
SPOOL apply_oracle_lab4.txt
 
-- insert your SQL statements here ... 
-- start with the validation scripts you find in the seeding.sql script.
-- copying the seeding.sql file and editing it to conform to this layout is the simplest approach to the lab. 
 
SPOOL OFF
 
-- ------------------------------------------------------------------
--  This is necessary to avoid a resource busy error. You can
--  inadvertently create a resource busy error when testing in two
--  concurrent SQL*Plus sessions unless you provide an explicit
--  COMMIT; statement. 
-- ------------------------------------------------------------------
COMMIT;

Click the Lab Instructions link to display the lab steps:

Lab Instructions →

You should complete the following ten steps:

  1. You should do the following in this part:

    • Copy the group_account1.sql from the ../lib2/seed directory into your ../lab4 directory as group_account1_lab.sql script file.
    • Edit the group_account1_lab.sql file to work exclusively with your _lab tables, sequences, and constraints.
    • Test the group_account1_lab.sql file to verify that it creates the desired group account and respective contact members.

    1. Copy the /home/student/Data/cit225/oracle/lib2/seed/group_account1.sql file to the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib2/seed/group_account1.sql group_account1_lab.sql

    2. Edit the group_account1_lab.sql file to work with the following _lab tables:

      • MEMBER_LAB
      • CONTACT_LAB
      • ADDRESS_LAB
      • STREET_ADDRESS_LAB
      • TELEPHONE_LAB
    3. Please check Lab 2 if you don’t recall the table definitions or use the DESC[RIBE] command in SQL*Plus to show them. At the bottom of the group_account1_lab.sql script you will see a diagnostic query. You should edit that script to look like the following script or replace it with the following script. The diagnostic script runs the query when you perform a unit test on your group_account1_lab.sql script. 

      COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
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
WHERE    c.last_name = 'Winn';

      It should show:

      Account    Name
Number	   (Last, First MI) City	 State	    Telephone
---------- ---------------- ------------ ---------- ------------------
B293-71445 Winn, Brian	    San Jose	 CA	    001-(408) 111-1111
B293-71445 Winn, Randi	    San Jose	 CA	    001-(408) 111-1111

  1. You should do the following in this part:

    • Copy the group_account2.sql from the ../lib2/seed directory into your ../lab4 directory as group_account2_lab.sql script file.
    • Edit the group_account2_lab.sql file to work exclusively with your _lab tables, sequences, and constraints.
    • Test the group_account2_lab.sql file to verify that it creates the desired group account and respective contact members.

    1. Copy the /home/student/Data/cit225/oracle/lib2/seed/group_account2.sql file to the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib2/seed/group_account2.sql group_account2_lab.sql

    2. Edit the group_account2_lab.sql file to work with the following _lab tables:

      • MEMBER_LAB
      • CONTACT_LAB
      • ADDRESS_LAB
      • STREET_ADDRESS_LAB
      • TELEPHONE_LAB
    3. Please check Lab 2 if you don’t recall the table definitions or use the DESC[RIBE] command in SQL*Plus to show them. At the bottom of the group_account1_lab.sql script you will see a diagnostic query. You should edit that script to look like the following script or replace it with the following script. The diagnostic script runs the query when you perform a unit test on your group_account2_lab.sql script. 

      COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
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
WHERE    c.last_name = 'Vizquel';

      It should show:

      Account    Name
Number	   (Last, First MI) City	 State	    Telephone
---------- ---------------- ------------ ---------- ------------------
B293-71446 Vizquel, Oscar   San Jose	 CA	    001-(408) 222-2222
B293-71446 Vizquel, Doreen  San Jose	 CA	    001-(408) 222-2222

  1. You should do the following in this part:

    • Copy the group_account3.sql from the ../lib2/seed directory into your ../lab4 directory as group_account3_lab.sql script file.
    • Edit the group_account3_lab.sql file to work exclusively with your _lab tables, sequences, and constraints.
    • Test the group_account3_lab.sql file to verify that it creates the desired group account and respective contact members.

    1. Copy the /home/student/Data/cit225/oracle/lib2/seed/group_account3.sql file to the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib2/seed/group_account3.sql group_account3_lab.sql

    2. Edit the group_account3_lab.sql file to work with the following _lab tables:

      • MEMBER_LAB
      • CONTACT_LAB
      • ADDRESS_LAB
      • STREET_ADDRESS_LAB
      • TELEPHONE_LAB
    3. Please check Lab 2 if you don’t recall the table definitions or use the DESC[RIBE] command in SQL*Plus to show them. At the bottom of the group_account3_lab.sql script you will see a diagnostic query. You should edit that script to look like the following script or replace it with the following script. The diagnostic script runs the query when you perform a unit test on your group_account3_lab.sql script.

      COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
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
WHERE    c.last_name = 'Sweeney';

      It should show:

      Account    Name
Number	   (Last, First MI) City	 State	    Telephone
---------- ---------------- ------------ ---------- ------------------
B293-71447 Sweeney, Matthew San Jose	 CA	    001-(408) 333-3333
B293-71447 Sweeney, Meaghan San Jose	 CA	    001-(408) 333-3333
B293-71447 Sweeney, Ian M   San Jose	 CA	    001-(408) 333-3333

  1. You should do the following in this part:

    • Copy the item_inserts.sql from the from the ../lib2/seed directory into your ../lab4 directory as the item_inserts_lab.sql script file.
    • Edit the item_inserts_lab.sql file to work exclusively with your item_lab tables, sequences, and constraints. (HINT: You also need to change subquery components to use the COMMON_LOOKUP_LAB table, column names, and values.
    • Test the item_inserts_lab.sql file to verify that it creates the twenty-one rows in the ITEM table.

    1. Copy the item_inserts.sql file to the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib2/seed/item_inserts.sql item_inserts_lab.sql

    2. Edit the item_inserts_lab.sql file to work with the item_lab and common_lookup_lab tables.
    3. Please check Lab 2 if you don’t recall the table definitions or use the DESC[RIBE] command in SQL*Plus to show them. At the bottom of the item_inserts.sql script you will see a diagnostic query. You should edit that script to look like the following script or replace it with the following script. The diagnostic script runs the query when you perform a unit test on your item_inserts_lab.sql script.

      SET PAGESIZE 99
COL item_lab_id                FORMAT 9999  HEADING "Item|ID #"
COL common_lookup_meaning  FORMAT A20  HEADING "Item Description"
COL item_title             FORMAT A30  HEADING "Item Title"
COL item_release_date      FORMAT A11  HEADING "Item|Release|Date"
SELECT   i.item_lab_id
,        cl.common_lookup_meaning
,        i.item_title
,        i.item_release_date
FROM     item_lab i INNER JOIN common_lookup_lab cl ON i.item_type = cl.common_lookup_lab_id;

      It should show:

      							  Item
 Item							  Release
 ID # Item Description	   Item Title			  Date
----- -------------------- ------------------------------ -----------
 1001 DVD: Wide Screen	   The Hunt for Red October	  02-MAR-90
 1002 DVD: Wide Screen	   Star Wars I			  04-MAY-99
 1003 DVD: Full Screen	   Star Wars II 		  16-MAY-02
 1004 DVD: Wide Screen	   Star Wars II 		  16-MAY-02
 1005 DVD: Wide Screen	   Star Wars III		  19-MAY-05
 1006 DVD: Wide Screen	   The Chronicles of Narnia	  16-MAY-02
 1007 XBOX		   RoboCop			  24-JUL-03
 1008 XBOX		   Pirates of the Caribbean	  30-JUN-03
 1009 XBOX		   The Chronicles of Narnia	  30-JUN-03
 1010 Nintendo Gamecube    MarioKart			  17-NOV-03
 1011 Playstation2	   Splinter Cell		  08-APR-03
 1012 Playstation2	   Need for Speed		  15-NOV-04
 1013 XBOX		   The DaVinci Code		  19-MAY-06
 1014 XBOX		   Cars 			  28-APR-06
 1015 Blu-ray		   Beau Geste			  01-MAR-92
 1016 Blu-ray		   I Remember Mama		  05-JAN-98
 1017 Blu-ray		   Tora! Tora! Tora!		  02-NOV-99
 1018 Blu-ray		   A Man for All Seasons	  28-JUN-94
 1019 Blu-ray		   Hook 			  11-DEC-91
 1020 Blu-ray		   Around the World in 80 Days	  04-DEC-92
 1021 Blu-ray		   Camelot			  15-MAY-98

  1. You should do the following in this part:

    • Copy the create_insert_contacts.sql from the ../lib2/seed directory into your ../lab4 directory as create_insert_contacts_lab.sql script file.
    • Edit the create_insert_contacts.sql file to work exclusively with your _lab tables, sequences, and constraints without conflicting with the existing contact_insert procedure.
    • Test the contact_insert_lab.sql file to verify that it creates a valid procedure. The next step (Step #6) will verify that it actually creates valid individual accounts in the _lab tables.

    1. Copy the create_insert_contacts.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib2/seed/create_insert_contacts.sql create_insert_contacts_lab.sql

    2. Edit the create_insert_contacts.sql file to work exclusively with your _lab tables, sequences, and constraints without conflicting with the existing contact_insert procedure. You accomplish that by:

      • Changing the name of the contact_insert procedure to contact_insert_lab. You must change the name on the first line and the last line or it will not compile correctly.
      • Changing the table, column, and sequence names required to work with your _lab tables and sequences.
      • Avoid changing any of the parameter names, which ensures you won’t need to change the list of parameters in the individual_accounts.sql script.
    3. Please check Lab 2 if you don’t recall the table definitions or use the DESC[RIBE] command in SQL*Plus to show the definition of the contact_insert_lab procedure, like 

      DESC contact_insert_lab

      It should show:

      PROCEDURE contact_insert_lab
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PV_MEMBER_TYPE 		VARCHAR2		IN
 PV_ACCOUNT_NUMBER		VARCHAR2		IN
 PV_CREDIT_CARD_NUMBER		VARCHAR2		IN
 PV_CREDIT_CARD_TYPE		VARCHAR2		IN
 PV_FIRST_NAME			VARCHAR2		IN
 PV_MIDDLE_NAME 		VARCHAR2		IN     DEFAULT
 PV_LAST_NAME			VARCHAR2		IN
 PV_CONTACT_TYPE		VARCHAR2		IN
 PV_ADDRESS_TYPE		VARCHAR2		IN
 PV_CITY			VARCHAR2		IN
 PV_STATE_PROVINCE		VARCHAR2		IN
 PV_POSTAL_CODE 		VARCHAR2		IN
 PV_STREET_ADDRESS		VARCHAR2		IN
 PV_TELEPHONE_TYPE		VARCHAR2		IN
 PV_COUNTRY_CODE		VARCHAR2		IN
 PV_AREA_CODE			VARCHAR2		IN
 PV_TELEPHONE_NUMBER		VARCHAR2		IN
 PV_CREATED_BY			NUMBER			IN     DEFAULT
 PV_CREATION_DATE		DATE			IN     DEFAULT
 PV_LAST_UPDATED_BY		NUMBER			IN     DEFAULT
 PV_LAST_UPDATE_DATE		DATE			IN     DEFAULT

  1. You should do the following in this part:

    • Copy the individual_accounts.sql script from the ../lib2/seed directory into your ../lab4 directory as individual_accounts_lab.sql script file.
    • Edit the individual_accounts.sql file to call the contact_insert_lab procedure.
    • Test the individual_accounts.sql file to verify that it creates a valid individual accounts.

    1. Copy the individual_accounts.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib2/seed/individual_accounts.sql individual_accounts_lab.sql

    2. Edit the individual_accounts.sql file to call your new contact_insert_lab procedure completed in the previous step. You should modify all the calls from using the contact_insert procedure name to using the contact_insert_lab procedure.
    3. Run the individual_accounts.sql to create individual membership accounts in the _lab tables; and run the following confirmation query: 

      COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A20  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || SUBSTR(c.middle_name,1,1)
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
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
WHERE    m.member_type = (SELECT common_lookup_lab_id
                          FROM   common_lookup_lab
                          WHERE  common_lookup_context = 'MEMBER_LAB'
                          AND    common_lookup_type = 'INDIVIDUAL');

      It should show:

      Account    Name
Number	   (Last, First MI)	City	     State	Telephone
---------- -------------------- ------------ ---------- ------------------
R11-514-34 Clinton, Goeffrey W	Provo	     Utah	001-(801) 423-1234
R11-514-35 Moss, Wendy		Provo	     Utah	001-(801) 423-1235
R11-514-36 Gretelz, Simon J	Provo	     Utah	001-(801) 423-1236
R11-514-37 Royal, Elizabeth J	Provo	     Utah	001-(801) 423-1237
R11-514-38 Smith, Brian N	Spanish Fork Utah	001-(801) 423-1238

  1. You should do the following in this part:

    • Copy the update_members.sql script from the ../lib2/seed directory into your ../lab4 directory as update_members_lab.sql script file.
    • Edit the update_members.sql file to replace file, column, constraints, and sequence names to include the _lab suffix.
    • Test the update_members.sql file to verify that it creates a valid individual accounts.

    1. Copy the update_members.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib2/seed/update_members.sql update_members_lab.sql

    2. Edit the individual_accounts.sql file to call your new contact_insert_lab procedure completed in the previous step. You should modify all the calls from using the contact_insert procedure name to using the contact_insert_lab procedure.
    3. Run the update_members.sql to update the member_type column with correct values before adding a NOT NULL constraint on the member_type column. Then, add the NOT NULL constraint on the member_type column before you run the confirmation query: 

      -- Use SQL*Plus report formatting commands.
COLUMN member_lab_id         FORMAT 999999 HEADING "Member|ID"
COLUMN members               FORMAT 999999 HEADING "Member|Qty #"
COLUMN member_type           FORMAT 999999 HEADING "Member|Type|ID #"
COLUMN common_lookup_lab_id  FORMAT 999999 HEADING "Member|Lookup|ID #"
COLUMN common_lookup_type    FORMAT A12    HEADING "Common|Lookup|Type"
SELECT   m.member_lab_id
,        COUNT(contact_lab_id) AS MEMBERS
,        m.member_type
,        cl.common_lookup_lab_id
,        cl.common_lookup_type
FROM     member_lab m INNER JOIN contact_lab c
ON       m.member_lab_id = c.member_lab_id INNER JOIN common_lookup_lab cl
ON       m.member_type = cl.common_lookup_lab_id
GROUP BY m.member_lab_id
,        m.member_type
,        cl.common_lookup_lab_id
,        cl.common_lookup_type
ORDER BY m.member_lab_id;

      It should show:

      		 Member  Member Common
 Member  Member    Type  Lookup Lookup
     ID   Qty #    ID #    ID # Type
------- ------- ------- ------- ------------
   1001       2    1004    1004 GROUP
   1002       2    1004    1004 GROUP
   1003       3    1004    1004 GROUP
   1004       1    1003    1003 INDIVIDUAL
   1005       1    1003    1003 INDIVIDUAL
   1006       1    1003    1003 INDIVIDUAL
   1007       1    1003    1003 INDIVIDUAL
   1008       1    1003    1003 INDIVIDUAL

  1. You should do the following in this part:

    • Copy the rental_inserts.sql script from the ../lib2/seed directory into your ../lab4 directory as rental_inserts_lab.sql script file.
    • Edit the rental_inserts_lab.sql file to replace file, column, constraints, and sequence names to include the _lab suffix.
    • Test the rental_inserts_lab.sql file to verify that it creates a valid rental agreements with one or more items assigned to each rental agreement.

    1. Copy the rental_inserts.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib2/seed/rental_inserts.sql rental_inserts_lab.sql

    2. Edit the rental_inserts_lab.sql file to accommodate all of the table, column, constraint, and sequence names that need an _lab added
    3. Run the rental_inserts_lab.sql to insert rental_lab and rental_item_lab rows. You should run the following confirmation query: 

      -- Use SQL*Plus report formatting commands.
COL member_lab_id       FORMAT 9999 HEADING "Member|ID #"
COL account_number      FORMAT A10  HEADING "Account|Number"
COL full_name           FORMAT A20  HEADING "Name|(Last, First MI)"
COL rental_lab_id       FORMAT 9999 HEADING "Rent|ID #"
COL rental_item_lab_id  FORMAT 9999 HEADING "Rent|Item|ID #"
COL item_title          FORMAT A26  HEADING "Item Title"
SELECT   m.member_lab_id
,        m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || SUBSTR(c.middle_name,1,1)
         END AS full_name
,        r.rental_lab_id
,        ri.rental_item_lab_id
,        i.item_title
FROM     member_lab m INNER JOIN contact_lab c ON m.member_lab_id = c.member_lab_id INNER JOIN
         rental_lab r ON c.contact_lab_id = r.customer_id 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
ORDER BY r.rental_lab_id;

      It should show:

      					      Rent
Member Account	  Name			Rent  Item
  ID # Number	  (Last, First MI)	ID #  ID # Item Title
------ ---------- -------------------- ----- ----- --------------------------
  1002 B293-71446 Vizquel, Oscar	1001  1010 Star Wars I
  1002 B293-71446 Vizquel, Oscar	1001  1001 Star Wars II
  1002 B293-71446 Vizquel, Oscar	1001  1002 Star Wars III
  1002 B293-71446 Vizquel, Doreen	1002  1003 Camelot
  1002 B293-71446 Vizquel, Doreen	1002  1011 I Remember Mama
  1003 B293-71447 Sweeney, Meaghan	1003  1004 Hook
  1003 B293-71447 Sweeney, Ian M	1004  1005 Cars
  1001 B293-71445 Winn, Brian		1005  1007 The Hunt for Red October
  1001 B293-71445 Winn, Brian		1005  1006 RoboCop

  1. You should do the following in this part:

    • Copy the create_view.sql script from the ../lib2/seed directory into your ../lab4 directory as create_view_lab.sql script file.
    • Edit the create_view_lab.sql file to replace file, column, constraints, and sequence names to include the _lab suffix.
    • Test the create_view_lab.sql file to verify that it creates a view by querying results from the current_rental view.

    1. Copy the create_view.sql file from the ../lib2/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib2/seed/create_view.sql create_view_lab.sql

    2. Edit the create_view_lab.sql file to accommodate all of the table, column, constraint, and sequence names that need an _lab added. You should rename the current_rental view as current_rental_lab view.
    3. Run the create_view_lab.sql to check the contents of seeding activities and view generation. You should run the following confirmation query: 

      -- Use SQL*Plus report formatting commands.
COL member_lab_id       FORMAT 9999  HEADING "Member|ID #"
COL members             FORMAT 9999  HEADING "Member|#"
COL common_lookup_type  FORMAT A12   HEADING "Common|Lookup Type"
SELECT   m.member_lab_id
,        COUNT(contact_lab_id) AS MEMBERS
,        cl.common_lookup_type
FROM     member_lab m INNER JOIN contact_lab c
ON       m.member_lab_id = c.member_lab_id INNER JOIN common_lookup_lab cl
ON       m.member_type = cl.common_lookup_lab_id
GROUP BY m.member_lab_id
,        m.member_type
,        cl.common_lookup_lab_id
,        cl.common_lookup_type
ORDER BY m.member_lab_id;

      It should show:

      Member Member Common
  ID #	    # Lookup Type
------ ------ ------------
  1001	    2 GROUP
  1002	    2 GROUP
  1003	    3 GROUP
  1004	    1 INDIVIDUAL
  1005	    1 INDIVIDUAL
  1006	    1 INDIVIDUAL
  1007	    1 INDIVIDUAL
  1008	    1 INDIVIDUAL

      Modify the view name from current_rental to current_rental_lab and compile the view. Then, you can query the view with the following SELECT statement:

      COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL title           FORMAT A30  HEADING "Item Title"
COL check_out_date  FORMAT A11  HEADING "Check|Out|Date"
COL return_date     FORMAT A11  HEADING "Return|Date"
SELECT   cr.full_name
,        cr.title
,        cr.check_out_date
,        cr.return_date 
FROM     current_rental_lab cr;

      It should show:

      						Check
Name						Out	    Return
(Last, First MI) Item Title			Date	    Date
---------------- ------------------------------ ----------- -----------
Winn, Brian	 RoboCop			02-FEB-18   07-FEB-18
Winn, Brian	 The Hunt for Red October	02-FEB-18   07-FEB-18
Vizquel, Oscar	 Star Wars I			02-FEB-18   07-FEB-18
Vizquel, Oscar	 Star Wars II			02-FEB-18   07-FEB-18
Vizquel, Oscar	 Star Wars III			02-FEB-18   07-FEB-18
Sweeney, Ian M	 Cars				02-FEB-18   07-FEB-18

  1. You should do the following in this part:

    • Copy the seeding.sql script from the ../lib2/seed directory into your ../lab4 directory as apply_oracle_lab4.sql script file.
    • Edit the apply_oracle_lab4.sql file to replace file, column, constraints, and sequence names to include the _lab suffix.
    • Test the apply_oracle_lab4.sql file to verify that the series of files run as a group.

Test Case

Click the Test Case Instructions link to open the test case instructions inside the current webpage.

Test Case Instructions →

After you write the apply_oracle_lab4.sql script, you should navigate to the /home/student/Data/cit225/oracle/lab4 directory. You call the sqlplus utility from the same directory and then you run the apply_oracle_lab4.sql script with the following syntax:

@apply_oracle_lab4.sql

You should submit your apply_oracle_lab4.sql script file and apply_oracle_lab4.txt log file for a grade.

Written by michaelmclaughlin

January 26th, 2010 at 2:34 am

Posted in

44 Responses to 'Lab #4 : Insert Patterns'

Subscribe to comments with RSS or TrackBack to 'Lab #4 : Insert Patterns'.

  1. CREDIT_CARD_TYPE in member ADDRESS_ID in telephone are missing the foreign key information on this page.

    William Sawyer

    27 Jan 10 at 2:51 pm

  2. Another thing we found: The table definition of MEMBER of this page shows MEMBER with the column MEMBER_TYPE. In the create_store.sql script MEMBER does not have that column.

    William Sawyer

    27 Jan 10 at 5:09 pm

  3. The create_store.sql script doesn’t contain the column, and they should add the column with an ALTER TABLE statement.

    michaelmclaughlin

    28 Jan 10 at 12:42 am

  4. I’ve fixed the table definition for CREDIT_CARD_TYPE, and it now contains a foreign key reference. The other isn’t an error but it was missing a buisness rule. I’ve noted it in the page above. Thanks for the great catch.

    michaelmclaughlin

    28 Jan 10 at 12:47 am

  5. In the CONTACT table MEMBER_ID is referenced incorrectly to common_lookup, It should be referenced to the MEMBER table.

    William Sawyer

    28 Jan 10 at 1:49 pm

  6. Suggestion: middle_name column be renamed to middle_initial in the contact table for clarity.

    Blaine Forbush

    29 Jan 10 at 12:46 pm

  7. Actually in the provided script on I-Learn middle_name is of CHAR(20) just like first_name and last_name so this is appropriate. He just needs to update the table on this website to reflect that. Also the order in the CONTACT table goes first_name, middle_name, then last_name. He has last_name, first_name, middle_name. So for this lab it is better to actually use the script he provides as reference.

    Trent Davis

    29 Jan 10 at 7:03 pm

  8. Great catch. The Oracle script differs from the MySQL script. That wasn’t the intention. I’m fixing the scripts so they’re mirrors. In the meantime, I’ve updated this page to reflect the correct model for the future.

    michaelmclaughlin

    29 Jan 10 at 11:01 pm

  9. “The TELEPHONE table contains an ADDRESS_ID column without a foreign key constraint”

    Should read “without a NOT NULL constraint”

    Andrew Thimmig

    21 Jan 11 at 2:25 pm

  10. In the deliverables section, in the ADDRESS table the POSTAL_CODE column does not specify a constraint, but the create_oracle_store.sql and create_mysql_store.sql put a NOT NULL constraint on POSTAL_CODE.

    Eli

    12 May 11 at 1:37 pm

  11. Eli, Thanks, I’ve fixed it.

    michaelmclaughlin

    25 Oct 14 at 6:19 pm

  12. in step 2 dealing with the common_lookup_lab table. output should have member_lab instead of member.

    Tyler Nelson

    27 Jan 15 at 8:59 pm

  13. On step one the system_user_name for Ray Owens is OWENSR but in the output it is OWENS.

    Danielle

    27 Jan 15 at 9:02 pm

  14. On the rental_lab_id and the item_lab_id I am producing different values from the lab. The lab returns the values 1005 and 1006 for the rental_lab_id, and the item_lab_id returns 1003 and 1004. However, I am both returning 1001 and 1002 for both ID’s because the sequence I thought was supposed to start at 1001 on lab #2. If someone could explain that would be great!

    Carissa L

    29 Jan 15 at 7:45 pm

  15. With the addresses themselves being Identical in step 5 how would you use a Scalar Subquery to tie the Street addresses in step 6 to the correct address in step 5. The only way I can think is with a join or a nested subquery which has not been covered yet and I feel is beyond current scope of the class.

    Matt Mason

    29 Jan 15 at 7:48 pm

  16. Also, on the last query in step 11, the table says to input the return date for the second column to be January 9, 2015; however, the validation code only looks for January 8, 2015.

    Carissa L

    29 Jan 15 at 10:52 pm

  17. All throughout you need to change the last name of Jones to Smith because that is the last name you had us use when inserting John and Jane.

    Tyler Nelson

    30 Jan 15 at 1:23 am

  18. Tyler, It should be fixed now.

    michaelmclaughlin

    30 Jan 15 at 5:28 pm

  19. step 11

    the credit card number for ‘john’ and ‘youndo’ are the same

    Joseph Tracy

    30 Jan 15 at 5:28 pm

  20. Step 11

    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 = '08-JAN-2015';

    need to search for January 8, 2015 and January 9, 2015

    matthew fox

    31 Jan 15 at 11:26 am

  21. you are missing CONTACT_LAB_ID in the data table telling the students the data fro the inserts into address step 5.

    (AS PER TA WITH ROUND GLASSES)

    Tyler Wilhelm

    31 Jan 15 at 2:48 pm

  22. Step 3 instructions say 'MEMBER_LAB' should be inserted into column COMMON_LOOKUP_CONTEXT, but the expected results list 'MEMBER'.

    Dave Stevenson

    4 Feb 15 at 3:43 pm

  23. In step 4, the common lookup entry for 'CUSTOMER' has context 'CONTACT'. However, the verification query uses context 'CONTACT_LAB', so it fails to find the inserted rows.

    Dave Stevenson

    4 Feb 15 at 3:57 pm

  24. In step 5, the common lookup entry for 'CUSTOMER' has context 'CONTACT'. However, the verification query uses context 'CONTACT_LAB', so it fails to find the inserted rows.

    Dave Stevenson

    4 Feb 15 at 3:59 pm

  25. In step 6, the common lookup entry for 'CUSTOMER' has context 'CONTACT'. However, the verification query uses context 'CONTACT_LAB', so it fails to find the inserted rows.

    Dave Stevenson

    4 Feb 15 at 4:00 pm

  26. Step 10 expects results where foreign keys seem more spread out, perhaps because several records are created in the database used by the course creator. However, our instructions are to only create two records, so I believe we should expect foreign keys no greater than 1002:

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

    Dave Stevenson

    4 Feb 15 at 4:32 pm

  27. In step 5, you forgot to include contact_lab_id in the diagram for the address_lab insert.

    Jeremy

    9 Feb 15 at 1:15 pm

  28. Step 7’s chart should be telephone_number and not telephone_code.

    Jeremy

    9 Feb 15 at 2:59 pm

  29. Joseph, They should be the same because they belong to the same record (row) in the MEMBER table. Much as a dependent would use their parents’ credit card.

    michaelmclaughlin

    6 Mar 15 at 9:33 pm

  30. Jeremy, I believe that telephone_code is already telephone_number fixed in Step #7.

    michaelmclaughlin

    7 Mar 15 at 2:41 am

  31. Jeremy, The corrections has already been made.

    michaelmclaughlin

    7 Mar 15 at 2:46 am

  32. Matthew, I fixed the date to TRUNC(SYSDATE).

    michaelmclaughlin

    7 Mar 15 at 3:14 am

  33. Dave, That’s a great catch. I’ve updated the results from steps 8 through 10. Here are the results from step 10:

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

    michaelmclaughlin

    8 Mar 15 at 1:31 am

  34. Dave, Those are incorrect assessments of Steps #3, #4, #5, and #6. Step #2 in the Oracle Lab #2 web page instructs you to enter the following values into the COMMON_LOOKUP_LAB table:

    Table Name: COMMON_LOOKUP_LAB
    Context Type Meaning
    SYSTEM_USER_LAB SYSTEM_ADMIN System Administrator
    SYSTEM_USER_LAB DBA Database Administrator
    CONTACT_LAB EMPLOYEE Employee
    CONTACT_LAB CUSTOMER Customer
    MEMBER_LAB INDIVIDUAL Individual Membership
    MEMBER_LAB GROUP Group Membership
    MEMBER_LAB DISCOVER_CARD Discover Card
    MEMBER_LAB MASTER_CARD Master Card
    MEMBER_LAB VISA_CARD VISA Card
    MULTIPLE HOME Home
    MULTIPLE WORK Work
    ITEM_LAB DVD_FULL_SCREEN DVD: Full Screen
    ITEM_LAB DVD_WIDE_SCREEN DVD: Wide Screen
    ITEM_LAB NINTENDO_GAMECUBE Nintendo Gamecube
    ITEM_LAB PLAYSTATION2 PlayStation2
    ITEM_LAB XBOX XBox
    ITEM_LAB BLU-RAY Blu-ray

    michaelmclaughlin

    8 Mar 15 at 1:40 am

  35. Tyler, Brett (the TA with the round glasses) is right. I’ve fixed the table, and it’s now correct. Thanks!

    michaelmclaughlin

    8 Mar 15 at 2:00 am

  36. Carissa, I’ve fixed that earlier by making the insert date into a TRUNC(SYSDATE) value, and providing a remark that if the current date is January 8, 2015, the value would return 08-JAN-15. Does that work for you?

    michaelmclaughlin

    8 Mar 15 at 2:04 am

  37. Matt, It’s never a good sign when the tutor asks how. 🙂 The subquery needs to tie the address and contact table together to resolve the address_id, like this subquery in a VALUES clause of an INSERT statement:

    (SELECT   address_lab_id
 FROM     address_lab a INNER JOIN contact_lab c
 ON       a.contact_lab_id = c.contact_lab_id
 WHERE    c.first_name = 'John'
 AND      c.last_name = 'Jones'
 AND      a.city = 'Draper'
 AND      a.state_province = 'Utah')

    michaelmclaughlin

    8 Mar 15 at 2:14 am

  38. Carissa, You’re correct and the wrong output values where copied due to an error in my test script. I’ve fixed the solution script and the displayed values. Thanks!

    michaelmclaughlin

    8 Mar 15 at 2:15 am

  39. Tyler, Great catch. Yes, you’re right. It’s fixed now, and the output now displays MEMBER_LAB.

    michaelmclaughlin

    8 Mar 15 at 2:20 am

  40. Danielle, You’re right, and OWENS is now OWENSR. Thanks!

    michaelmclaughlin

    8 Mar 15 at 2:23 am

  41. All of the test queries for common_lookup_lab don’t have the right names (like rather than it saying common_lookup_lab_context or common_lookup_lab_type it says common_lookup_context or common_lookup_type). We were instructed to change everything to common_lookup_lab, so now we have to go and modify all of the test queries to put lab on the end. Would it be possible to get that changed in the future?

    Joshua

    6 Oct 15 at 7:16 pm

  42. So, we noticed that there is a error in the last line of the validation code of Step 11 part 2. It asks for a checkout date validating SYSDATE and not the dates listed in the lab.

    Robert Collins

    8 Oct 15 at 7:54 pm

  43. Joshua, Only the table name, common_lookup becomes common_lookup_lab and common_lookup_id becomes common_lookup_lab_id in Lab #2. You should not add _lab to the other columns of the common_lookup_lab table.

    michaelmclaughlin

    8 Oct 15 at 10:49 pm

  44. Robert, Thank you! Excellent catch, which I’ve fixed as noted below:

    WHERE    r.check_out_date IN ('08-JAN-2015','09-JAN-2015');

    michaelmclaughlin

    8 Oct 15 at 10:58 pm

Leave a Reply