Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #4: Insert Patterns

Learn how to write INSERT statements that include literal values, sequence values, and scalar subqueries. The following bullets describe the values:

  • The literal values can be numbers, dates, or strings. Numbers are inserted without delimiters. Strings and dates are inserted with single quote delimiters. Dates are single quote delimited and must conform to one of the default format masks or use the TO_DATE function to use a non-default format mask.
  • The sequence values use the .nextval for new primary key sequence values and .currval for foreign key sequence values where the inserted values are sequential.
  • The scalar subqueries use the natural key in the WHERE clause to discover a row, and the subquery’s SELECT-list returns the primary key value of that row. Scalar subqueries are the way developers discover and copy a primary key into a foreign key column.

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

[110 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/oracle/lib1/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/lib1/seed/seeding.sql
 
-- ------------------------------------------------------------------
-- Call the lab versions of the file.
-- ------------------------------------------------------------------
@@group_account1_lab.sql
@@group_account2_lab.sql
@@group_account3_lab.sql
@@item_inserts_lab.sql
@@create_insert_contacts_lab.sql
@@individual_accounts_lab.sql
@@update_members_lab.sql
@@rental_inserts_lab.sql
@@create_view_lab.sql
 
-- Open log file.  
SPOOL apply_oracle_lab4.txt
 
-- ------------------------------------------------------------------
--  The following queries should be placed here:
-- ------------------------------------------------------------------
--  6(c) diagnostics for the individual_accounts.sql script.
--  7(c) diagnostics for the update_members.sql script.
--  8(c) diagnostics for the rental_inserts.sql script.
--  9(c) diagnostics for the create_view_lab.sql script.
-- ------------------------------------------------------------------
 
-- Close log file.
SPOOL OFF
 
-- Make all changes permanent.
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 ../lib1/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/lib1/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/lib1/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 ../lib1/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/lib1/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/lib1/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 ../lib1/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/lib1/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/lib1/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 ../lib1/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/lib1/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 ../lib1/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 ../lib1/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib1/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 ../lib1/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 ../lib1/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib1/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 ../lib1/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 ../lib1/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib1/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 ../lib1/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 ../lib1/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib1/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 ../lib1/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 ../lib1/seed directory into the lab4 directory by navigating to the lab4 directory and running the following command: 

      cp /home/student/Data/cit225/oracle/lib1/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. 

      CREATE OR REPLACE VIEW current_rental_lab AS
  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
  ,        i.item_title AS title
  ,        i.item_subtitle AS subtitle
  ,        SUBSTR(cl.common_lookup_meaning,1,3) AS product
  ,        r.check_out_date
  ,        r.return_date
  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 INNER JOIN
           common_lookup_lab cl ON i.item_type = cl.common_lookup_lab_id
  ORDER BY 1, 2, 3;

    3. Run the create_view_lab.sql to check the contents of seeding activities and view generation. You should run the DESC command to verify the view definition: 

      DESC current_rental_lab

      It should show:

      Name					   Null?    Type
----------------------------------------- -------- ----------------------------
ACCOUNT_NUMBER 	                          NOT NULL VARCHAR2(10)
FULL_NAME                                          VARCHAR2(47)
TITLE	                                  NOT NULL VARCHAR2(60)
SUBTITLE                                           VARCHAR2(60)
PRODUCT	                                           VARCHAR2(12)
CHECK_OUT_DATE 	                          NOT NULL DATE
RETURN_DATE                               NOT NULL DATE

      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			13-OCT-18   18-OCT-18
Winn, Brian	 The Hunt for Red October	13-OCT-18   18-OCT-18
Vizquel, Doreen  Camelot			13-OCT-18   18-OCT-18
Vizquel, Doreen  I Remember Mama		13-OCT-18   18-OCT-18
Vizquel, Oscar	 Star Wars I			13-OCT-18   18-OCT-18
Vizquel, Oscar	 Star Wars II			13-OCT-18   18-OCT-18
Vizquel, Oscar	 Star Wars III			13-OCT-18   18-OCT-18
Sweeney, Ian M	 Cars				13-OCT-18   18-OCT-18
Sweeney, Meaghan Hook				13-OCT-18   18-OCT-18

  1. You should do the following in this part:

    • Copy the seeding.sql script from the ../lib1/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

August 13th, 2018 at 10:50 am

Posted in