Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #7: SELECT-list Decision Making

Reinforce adding values to the COMMON_LOOKUP table, learn how to use a correlated UPDATE statement, and fabricate data in a query.

Lab Instructions

This lab has four unit testing steps placed into one test script.

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

Lab Instructions →

You begin these steps by running the following script:

  • The lab6/apply_oracle_lab6.sql script.

You put these two scripts into your apply_oracle_lab7.sql script, which should be in the following directory:

/home/student/Data/cit225/lab7

You should use the following general prototype as your starting point for the apply_oracle_lab7.sql script:

Sample script →

This expands to show you how to structure your apply_oracle_lab7.sql script.

-- ------------------------------------------------------------------
-- Call the prior lab.
-- ------------------------------------------------------------------
 
@@/home/student/Data/cit225/oracle/lab6/apply_oracle_lab6.sql
 
-- Open log file.  
SPOOL apply_oracle_lab7.txt
 
-- --------------------------------------------------------
--  Step #1
--  -------
--  Insert two ACTIVE_FLAG records in the COMMON_LOOKUP table.
-- --------------------------------------------------------
 
-- Insert step #1 statements here.
 
-- --------------------------------------------------------
--  Step #2
--  -------
--  Insert three PRICE_TYPE and three RENTAL_ITEM_TYPE 
--  records in the COMMON_LOOKUP table.
-- --------------------------------------------------------
 
-- Insert step #2 statements here.
 
-- --------------------------------------------------------
--  Step #3
--  -------
--  Update the RENTAL_ITEM_TYPE column values and add a
--  foreign key constraint on the RENTAL_ITEM_TYPE column.
-- --------------------------------------------------------
 
-- Insert step #3 statements here.
 
-- --------------------------------------------------------
--  Step #4
--  -------
--  Create a query to fabricate pricing data that you
--  will insert into a PRICE table in lab 8.
-- --------------------------------------------------------
 
-- Insert step #4 statements here.
 
-- Close log file.
SPOOL OFF
 
-- Make all changes permanent.
COMMIT;

You should complete the following four steps:

  1. [6 points] You insert two new rows into the COMMON_LOOKUP table to support the ACTIVE_FLAG column in the PRICE table that you created in Lab #6:

    Instruction Details →

    You write two INSERT statements to the COMMON_LOOKUP table.

    There are several confirmation queries in this lab. They are there to help work through each step. Please do not skip the confirmation queries.

    Table Name: COMMON_LOOKUP
    Table Column Code Type Meaning
    PRICE ACTIVE_FLAG Y YES Yes
    PRICE ACTIVE_FLAG N NO No

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

    1
2
3
4
5
6
7
8
9
10

    COLUMN common_lookup_table  FORMAT A20 HEADING "COMMON_LOOKUP_TABLE"
COLUMN common_lookup_column FORMAT A20 HEADING "COMMON_LOOKUP_COLUMN"
COLUMN common_lookup_type   FORMAT A20 HEADING "COMMON_LOOKUP_TYPE"
SELECT   common_lookup_table
,        common_lookup_column
,        common_lookup_type
FROM     common_lookup
WHERE    common_lookup_table = 'PRICE'
AND      common_lookup_column = 'ACTIVE_FLAG'
ORDER BY 1, 2, 3 DESC;

    It should display the following results:

    COMMON_LOOKUP_TABLE  COMMON_LOOKUP_COLUMN COMMON_LOOKUP_TYPE
-------------------- -------------------- --------------------
PRICE                ACTIVE_FLAG          YES
PRICE                ACTIVE_FLAG          NO
 
2 rows selected.

  2. [6 points] Insert three new rows into the COMMON_LOOKUP table to support the PRICE_TYPE column in the PRICE table, and three new rows into the COMMON_LOOKUP table to support the RENTAL_ITEM_TYPE column in the RENTAL_ITEM table.

    Instruction Details →

    You write six INSERT statements to the COMMON_LOOKUP table.

    Table Name: COMMON_LOOKUP
    Table Column Code Type Meaning
    PRICE PRICE_TYPE 1 1-DAY RENTAL 1-Day Rental
    PRICE PRICE_TYPE 3 3-DAY RENTAL 3-Day Rental
    PRICE PRICE_TYPE 5 5-DAY RENTAL 5-Day Rental
    RENTAL_ITEM RENTAL_ITEM_TYPE 1 1-DAY RENTAL 1-Day Rental
    RENTAL_ITEM RENTAL_ITEM_TYPE 3 3-DAY RENTAL 3-Day Rental
    RENTAL_ITEM RENTAL_ITEM_TYPE 5 5-DAY RENTAL 5-Day Rental

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

    1
2
3
4
5
6
7
8
9
10

    COLUMN common_lookup_table  FORMAT A20 HEADING "COMMON_LOOKUP_TABLE"
COLUMN common_lookup_column FORMAT A20 HEADING "COMMON_LOOKUP_COLUMN"
COLUMN common_lookup_type   FORMAT A20 HEADING "COMMON_LOOKUP_TYPE"
SELECT   common_lookup_table
,        common_lookup_column
,        common_lookup_type
FROM     common_lookup
WHERE    common_lookup_table IN ('PRICE','RENTAL_ITEM')
AND      common_lookup_column IN ('PRICE_TYPE','RENTAL_ITEM_TYPE')
ORDER BY 1, 3;

    It should display the following results:

    COMMON_LOOKUP_TABLE  COMMON_LOOKUP_COLUMN COMMON_LOOKUP_TYPE
-------------------- -------------------- --------------------
PRICE		     PRICE_TYPE 	  1-DAY RENTAL
PRICE		     PRICE_TYPE 	  3-DAY RENTAL
PRICE		     PRICE_TYPE 	  5-DAY RENTAL
RENTAL_ITEM	     RENTAL_ITEM_TYPE	  1-DAY RENTAL
RENTAL_ITEM	     RENTAL_ITEM_TYPE	  3-DAY RENTAL
RENTAL_ITEM	     RENTAL_ITEM_TYPE	  5-DAY RENTAL
 
6 rows selected.

  3. [6 points] You added a RENTAL_ITEM_PRICE and RENTAL_ITEM_TYPE columns in the previous (Lab #6). Here you update the RENTAL_ITEM_TYPE column with values for all pre-existing rows. After you have updated the pre-existing rows, you add a NOT NULL constraint on the RENTAL_ITEM_TYPE column.

    Instruction Details →

    You update the null values in RENTAL_ITEM_TYPE column of the RENTAL_ITEM table with a correlated UPDATE statement; and then you enable a NOT NULL constraint not he RENTAL_ITEM_TYPE column.

    1. Update the rental_item_type column with values for all pre-existing rows, so you can add a FOREIGN KEY and NOT NULL constraint on the rental_item_type column in steps b and c respectively. You can not populate the rental_item_price until you get to Lab #8.

    Table Name: RENTAL_ITEM
    Column Name Constraint Data
    Type    		 Physical
    Size
    Type Reference Table Reference Column
    RENTAL_ITEM_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
    NOT NULL
    RENTAL_ITEM_PRICE NOT NULL Integer Maximum

    You should use the following to verify the addition of the two new columns from Lab #6:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

    COLUMN table_name   FORMAT A14
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'RENTAL_ITEM'
ORDER BY 2;

    It should display the following:

    TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
RENTAL_ITEM            1 RENTAL_ITEM_ID         NOT NULL NUMBER(22)
RENTAL_ITEM            2 RENTAL_ID              NOT NULL NUMBER(22)
RENTAL_ITEM            3 ITEM_ID                NOT NULL NUMBER(22)
RENTAL_ITEM            4 CREATED_BY             NOT NULL NUMBER(22)
RENTAL_ITEM            5 CREATION_DATE          NOT NULL DATE
RENTAL_ITEM            6 LAST_UPDATED_BY        NOT NULL NUMBER(22)
RENTAL_ITEM            7 LAST_UPDATE_DATE       NOT NULL DATE
RENTAL_ITEM            8 RENTAL_ITEM_TYPE                NUMBER(22)
RENTAL_ITEM            9 RENTAL_ITEM_PRICE               NUMBER(22)
 
9 rows selected.

    The following shows you how to UPDATE existing rows with the proper values from the COMMON_LOOKUP table, but it may have a casting error in Oracle Database 10g and earlier. If you fix the casting error it’ll work like a charm.

    1
2
3
4
5
6
7
8
9
10

    UPDATE   rental_item ri
SET      rental_item_type =
           (SELECT   cl.common_lookup_id
            FROM     common_lookup cl
            WHERE    cl.common_lookup_code =
              (SELECT   r.return_date - r.check_out_date
               FROM     rental r
               WHERE    r.rental_id = ri.rental_id)
            AND      cl.common_lookup_table = 'RENTAL_ITEM'
            AND      cl.common_lookup_column = 'RENTAL_ITEM_TYPE');

    You should use the following to verify that the UPDATE statement worked successfully in this step:

    1
2
3
4
5
6
7

    SELECT   ROW_COUNT
,        col_count
FROM    (SELECT   COUNT(*) AS ROW_COUNT
         FROM     rental_item) rc CROSS JOIN
        (SELECT   COUNT(rental_item_type) AS col_count
         FROM     rental_item
         WHERE    rental_item_type IS NOT NULL) cc;

    It should display the following results:

     ROW_COUNT  COL_COUNT
---------- ----------
        13         13
 
1 row selected.

    If you fail to get a count of 13 rows and columns, you most likely made an error with foreign key constraints in the prior lab (Lab #6). You can run the following diagnostic query to see if you have matching foreign key values to the common_lookup table.

    COL ROWNUM              FORMAT 999999  HEADING "Row|Number"
COL rental_item_type    FORMAT 999999  HEADING "Rental|Item|Type"
COL common_lookup_id    FORMAT 999999  HEADING "Common|Lookup|ID #"
COL common_lookup_code  FORMAT A6      HEADING "Common|Lookup|Code"
COL return_date         FORMAT A11     HEADING "Return|Date"
COL check_out_date      FORMAT A11     HEADING "Check Out|Date"
COL r_rental_id         FORMAT 999999  HEADING "Rental|ID #"
COL ri_rental_id        FORMAT 999999  HEADING "Rental|Item|Rental|ID #"
SELECT   ROWNUM
,        ri.rental_item_type
,        cl.common_lookup_id
,        cl.common_lookup_code
,        r.return_date
,        r.check_out_date
,        CAST((r.return_date - r.check_out_date) AS CHAR) AS lookup_code
,        r.rental_id AS r_rental_id
,        ri.rental_id AS ri_rental_id
FROM     rental r FULL JOIN rental_item ri
ON       r.rental_id = ri.rental_id FULL JOIN common_lookup cl
ON       cl.common_lookup_code =
           CAST((r.return_date - r.check_out_date) AS CHAR)
WHERE    cl.common_lookup_table = 'RENTAL_ITEM'
AND      cl.common_lookup_column = 'RENTAL_ITEM_TYPE'
AND      cl.common_lookup_type LIKE '%-DAY RENTAL'
ORDER BY r.rental_id
,        ri.rental_id;

    It should return the following output unless you have an error, which should occur in row numbers 10, 11, 12, or 13. Row numbers 1 through 9 are inserted by the master seeding scripts.

    								  Rental
	 Rental  Common Common					    Item
    Row    Item  Lookup Lookup Return	   Check Out	  Rental  Rental
 Number    Type    ID # Code   Date	   Date        L    ID #    ID #
------- ------- ------- ------ ----------- ----------- - ------- -------
      1    1025    1025 5      13-JUN-18   08-JUN-18   5    1001    1001
      2    1025    1025 5      13-JUN-18   08-JUN-18   5    1001    1001
      3    1025    1025 5      13-JUN-18   08-JUN-18   5    1001    1001
      4    1025    1025 5      13-JUN-18   08-JUN-18   5    1002    1002
      5    1025    1025 5      13-JUN-18   08-JUN-18   5    1002    1002
      6    1025    1025 5      13-JUN-18   08-JUN-18   5    1003    1003
      7    1025    1025 5      13-JUN-18   08-JUN-18   5    1004    1004
      8    1025    1025 5      13-JUN-18   08-JUN-18   5    1005    1005
      9    1025    1025 5      13-JUN-18   08-JUN-18   5    1005    1005
     10    1023    1023 1      09-JUN-18   08-JUN-18   1    1006    1006
     11    1023    1023 1      09-JUN-18   08-JUN-18   1    1006    1006
     12    1024    1024 3      11-JUN-18   08-JUN-18   3    1007    1007
     13    1025    1025 5      13-JUN-18   08-JUN-18   5    1008    1008

    1. Add the FK_RENTAL_ITEM_7 foreign key to the RENTAL_ITEM table. The FK_RENTAL_ITEM_7 foreign key belongs on the RENTAL_ITEM_TYPE column of the RENTAL_ITEM table and references the COMMON_LOOKUP_ID column of the COMMON_LOOKUP table.
    The following query, let’s you verify that you’ve set the foreign key constraint for the RENTAL_ITEM_TYPE column:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

    COLUMN table_name      FORMAT A12 HEADING "TABLE NAME"
COLUMN constraint_name FORMAT A18 HEADING "CONSTRAINT NAME"
COLUMN constraint_type FORMAT A12 HEADING "CONSTRAINT|TYPE"
COLUMN column_name     FORMAT A18 HEADING "COLUMN NAME"
SELECT   uc.table_name
,        uc.constraint_name
,        CASE
           WHEN uc.constraint_type = 'R' THEN
            'FOREIGN KEY'
         END AS constraint_type
,        ucc.column_name
FROM     user_constraints uc INNER JOIN user_cons_columns ucc
ON       uc.constraint_name = ucc.constraint_name
WHERE    uc.table_name = 'RENTAL_ITEM'
AND      ucc.column_name = 'RENTAL_ITEM_TYPE';

    The query should return the following (the constraint name may differ):

                                    CONSTRAINT
TABLE NAME   CONSTRAINT NAME    TYPE         COLUMN NAME
------------ ------------------ ------------ ------------------
RENTAL_ITEM  FK_RENTAL_ITEM_7   FOREIGN KEY  RENTAL_ITEM_TYPE
 
1 row selected.

    1. Change the RENTAL_ITEM_TYPE column of the RENTAL_ITEM table from a null allowed column to a not null constrained column.

    You should use the following query to verify that the RENTAL_ITEM_TYPE column has been converted from a nullable to a not null constrained column:

    1
2
3
4
5
6
7
8
9
10

    COLUMN CONSTRAINT FORMAT A10
SELECT   TABLE_NAME
,        column_name
,        CASE
           WHEN NULLABLE = 'N' THEN 'NOT NULL'
           ELSE 'NULLABLE'
         END AS CONSTRAINT
FROM     user_tab_columns
WHERE    TABLE_NAME = 'RENTAL_ITEM'
AND      column_name = 'RENTAL_ITEM_TYPE';

    It should display the following results for a not null constrained column:

    TABLE_NAME     COLUMN_NAME            CONSTRAINT
-------------- ---------------------- ----------
RENTAL_ITEM    RENTAL_ITEM_TYPE       NOT NULL
 
1 row selected.

  4. [30 points] You need to write a SELECT statement that returns a data set that you can subsequently insert into the PRICE table. This is a complex problem because you must fabricate rows from a base set of rows, and then you must perform mathematical calculations with the CASE statement. (HINT: All computations are performed inside individual columns of the SELECT-list by individual rows.)

    Instruction Details →

    You write the logic for the SELECT-list, which includes the use of CASE operators; and the negation logic to filter out the fabricated rows that do not apply to the data set.

    The SELECT-list (or query) draws data from the current ITEM table and adds values that let you calculate different value sets for the ACTIVE_FLAG and AMOUNT column. Specifically, you must add the following values:

    • ACTIVE_FLAG values: 'Y' and 'N'
    • AMOUNT values: '1', '3', '5', '10', and '15'

    You can fabricate the data set with a query like the example below. The sample query returns 24 (the number of) rows from the ITEM table times 2 rows from the fabrication of 'Y' and 'N' values times 3 rows from the fabrication of the '1', '3', and '5' rental day values.

    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

    SELECT   i.item_id
,        af.active_flag
,        cl.common_lookup_id AS price_type
,        cl.common_lookup_type AS price_desc
,        CASE
           WHEN  ...implement logic "B"... THEN ...result VALUE...
           ELSE  ...result VALUE ...
         END AS start_date
,        CASE
           WHEN  ...implement logic "C"... THEN ...result VALUE...
         END AS end_date
,        CASE
           WHEN  ...implement logic "D"... THEN ...result VALUE...
           ELSE  ...result VALUE ...
         END AS amount
FROM     item i CROSS JOIN
        (SELECT 'Y' AS active_flag FROM dual
         UNION ALL
         SELECT 'N' AS active_flag FROM dual) af CROSS JOIN
        (SELECT '1' AS rental_days FROM dual
         UNION ALL
         SELECT '3' AS rental_days FROM dual
         UNION ALL
         SELECT '5' AS rental_days FROM dual) dr INNER JOIN
         common_lookup cl ON dr.rental_days = SUBSTR(cl.common_lookup_type,1,1)
WHERE    cl.common_lookup_table = 'PRICE'
AND      cl.common_lookup_column = 'PRICE_TYPE'
AND NOT ( ...implement logic "A"...)
ORDER BY 1, 2, 3;

    When you have written the correct logic for the preceding query, you should see the following results:

    You should note that the result set displays the price_type value. The price_type value is a foreign key and a copy of the common_lookup_id value in the common_lookup table.

    You find the common_lookup_id value by using the natural key. Three columns define the natural key. They are the uppercase strings:

    • common_lookup_table column
    • common_lookup_column column
    • common_lookup_type column

    String literals of PRICE and PRICE_TYPE match the common_lookup_table and common_lookup_column column values and the Cartesian join resolves the common_lookup_type column by comparing it against the first character of the rental_days column. The rental_days column is fabricated into the data set via a CROSS JOIN.

    You can use the following SQL*Plus formatting commands to display the information from your query:

    COLUMN item_id     FORMAT 9999 HEADING "ITEM|ID"
COLUMN active_flag FORMAT A6   HEADING "ACTIVE|FLAG"
COLUMN price_type  FORMAT 9999 HEADING "PRICE|TYPE"
COLUMN price_desc  FORMAT A12  HEADING "PRICE DESC"
COLUMN start_date  FORMAT A10  HEADING "START|DATE"
COLUMN end_date    FORMAT A10  HEADING "END|DATE"
COLUMN amount      FORMAT 9999 HEADING "AMOUNT"

    The output should look like the following:

     ITEM ACTIVE PRICE
   ID FLAG    TYPE PRICE DESC   START DATE         END DATE           AMOUNT
----- ------ ----- ------------ ------------------ ------------------ ------
 1001 N       1020 1-DAY RENTAL 02-MAR-90 00:00:00 01-APR-90 00:00:00      3
 1001 N       1021 3-DAY RENTAL 02-MAR-90 00:00:00 01-APR-90 00:00:00     10
 1001 N       1022 5-DAY RENTAL 02-MAR-90 00:00:00 01-APR-90 00:00:00     15
 1001 Y       1020 1-DAY RENTAL 02-APR-90 00:00:00                         1
 1001 Y       1021 3-DAY RENTAL 02-APR-90 00:00:00                         3
 1001 Y       1022 5-DAY RENTAL 02-APR-90 00:00:00                         5
 1002 N       1020 1-DAY RENTAL 04-MAY-99 00:00:00 03-JUN-99 00:00:00      3
 1002 N       1021 3-DAY RENTAL 04-MAY-99 00:00:00 03-JUN-99 00:00:00     10
 1002 N       1022 5-DAY RENTAL 04-MAY-99 00:00:00 03-JUN-99 00:00:00     15
 1002 Y       1020 1-DAY RENTAL 04-JUN-99 00:00:00                         1
 1002 Y       1021 3-DAY RENTAL 04-JUN-99 00:00:00                         3
 1002 Y       1022 5-DAY RENTAL 04-JUN-99 00:00:00                         5
 1003 N       1020 1-DAY RENTAL 16-MAY-02 00:00:00 15-JUN-02 00:00:00      3
 1003 N       1021 3-DAY RENTAL 16-MAY-02 00:00:00 15-JUN-02 00:00:00     10
 1003 N       1022 5-DAY RENTAL 16-MAY-02 00:00:00 15-JUN-02 00:00:00     15
 1003 Y       1020 1-DAY RENTAL 16-JUN-02 00:00:00                         1
 1003 Y       1021 3-DAY RENTAL 16-JUN-02 00:00:00                         3
 1003 Y       1022 5-DAY RENTAL 16-JUN-02 00:00:00                         5
 1004 N       1020 1-DAY RENTAL 16-MAY-02 00:00:00 15-JUN-02 00:00:00      3
 1004 N       1021 3-DAY RENTAL 16-MAY-02 00:00:00 15-JUN-02 00:00:00     10
 1004 N       1022 5-DAY RENTAL 16-MAY-02 00:00:00 15-JUN-02 00:00:00     15
 1004 Y       1020 1-DAY RENTAL 16-JUN-02 00:00:00                         1
 1004 Y       1021 3-DAY RENTAL 16-JUN-02 00:00:00                         3
 1004 Y       1022 5-DAY RENTAL 16-JUN-02 00:00:00                         5
 1005 N       1020 1-DAY RENTAL 19-MAY-05 00:00:00 18-JUN-05 00:00:00      3
 1005 N       1021 3-DAY RENTAL 19-MAY-05 00:00:00 18-JUN-05 00:00:00     10
 1005 N       1022 5-DAY RENTAL 19-MAY-05 00:00:00 18-JUN-05 00:00:00     15
 1005 Y       1020 1-DAY RENTAL 19-JUN-05 00:00:00                         1
 1005 Y       1021 3-DAY RENTAL 19-JUN-05 00:00:00                         3
 1005 Y       1022 5-DAY RENTAL 19-JUN-05 00:00:00                         5
 1006 N       1020 1-DAY RENTAL 16-MAY-02 00:00:00 15-JUN-02 00:00:00      3
 1006 N       1021 3-DAY RENTAL 16-MAY-02 00:00:00 15-JUN-02 00:00:00     10
 1006 N       1022 5-DAY RENTAL 16-MAY-02 00:00:00 15-JUN-02 00:00:00     15
 1006 Y       1020 1-DAY RENTAL 16-JUN-02 00:00:00                         1
 1006 Y       1021 3-DAY RENTAL 16-JUN-02 00:00:00                         3
 1006 Y       1022 5-DAY RENTAL 16-JUN-02 00:00:00                         5
 1007 N       1020 1-DAY RENTAL 24-JUL-03 00:00:00 23-AUG-03 00:00:00      3
 1007 N       1021 3-DAY RENTAL 24-JUL-03 00:00:00 23-AUG-03 00:00:00     10
 1007 N       1022 5-DAY RENTAL 24-JUL-03 00:00:00 23-AUG-03 00:00:00     15
 1007 Y       1020 1-DAY RENTAL 24-AUG-03 00:00:00                         1
 1007 Y       1021 3-DAY RENTAL 24-AUG-03 00:00:00                         3
 1007 Y       1022 5-DAY RENTAL 24-AUG-03 00:00:00                         5
 1008 N       1020 1-DAY RENTAL 30-JUN-03 00:00:00 30-JUL-03 00:00:00      3
 1008 N       1021 3-DAY RENTAL 30-JUN-03 00:00:00 30-JUL-03 00:00:00     10
 1008 N       1022 5-DAY RENTAL 30-JUN-03 00:00:00 30-JUL-03 00:00:00     15
 1008 Y       1020 1-DAY RENTAL 31-JUL-03 00:00:00                         1
 1008 Y       1021 3-DAY RENTAL 31-JUL-03 00:00:00                         3
 1008 Y       1022 5-DAY RENTAL 31-JUL-03 00:00:00                         5
 1009 N       1020 1-DAY RENTAL 30-JUN-03 00:00:00 30-JUL-03 00:00:00      3
 1009 N       1021 3-DAY RENTAL 30-JUN-03 00:00:00 30-JUL-03 00:00:00     10
 1009 N       1022 5-DAY RENTAL 30-JUN-03 00:00:00 30-JUL-03 00:00:00     15
 1009 Y       1020 1-DAY RENTAL 31-JUL-03 00:00:00                         1
 1009 Y       1021 3-DAY RENTAL 31-JUL-03 00:00:00                         3
 1009 Y       1022 5-DAY RENTAL 31-JUL-03 00:00:00                         5
 1010 N       1020 1-DAY RENTAL 17-NOV-03 00:00:00 17-DEC-03 00:00:00      3
 1010 N       1021 3-DAY RENTAL 17-NOV-03 00:00:00 17-DEC-03 00:00:00     10
 1010 N       1022 5-DAY RENTAL 17-NOV-03 00:00:00 17-DEC-03 00:00:00     15
 1010 Y       1020 1-DAY RENTAL 18-DEC-03 00:00:00                         1
 1010 Y       1021 3-DAY RENTAL 18-DEC-03 00:00:00                         3
 1010 Y       1022 5-DAY RENTAL 18-DEC-03 00:00:00                         5
 1011 N       1020 1-DAY RENTAL 08-APR-03 00:00:00 08-MAY-03 00:00:00      3
 1011 N       1021 3-DAY RENTAL 08-APR-03 00:00:00 08-MAY-03 00:00:00     10
 1011 N       1022 5-DAY RENTAL 08-APR-03 00:00:00 08-MAY-03 00:00:00     15
 1011 Y       1020 1-DAY RENTAL 09-MAY-03 00:00:00                         1
 1011 Y       1021 3-DAY RENTAL 09-MAY-03 00:00:00                         3
 1011 Y       1022 5-DAY RENTAL 09-MAY-03 00:00:00                         5
 1012 N       1020 1-DAY RENTAL 15-NOV-04 00:00:00 15-DEC-04 00:00:00      3
 1012 N       1021 3-DAY RENTAL 15-NOV-04 00:00:00 15-DEC-04 00:00:00     10
 1012 N       1022 5-DAY RENTAL 15-NOV-04 00:00:00 15-DEC-04 00:00:00     15
 1012 Y       1020 1-DAY RENTAL 16-DEC-04 00:00:00                         1
 1012 Y       1021 3-DAY RENTAL 16-DEC-04 00:00:00                         3
 1012 Y       1022 5-DAY RENTAL 16-DEC-04 00:00:00                         5
 1013 N       1020 1-DAY RENTAL 19-MAY-06 00:00:00 18-JUN-06 00:00:00      3
 1013 N       1021 3-DAY RENTAL 19-MAY-06 00:00:00 18-JUN-06 00:00:00     10
 1013 N       1022 5-DAY RENTAL 19-MAY-06 00:00:00 18-JUN-06 00:00:00     15
 1013 Y       1020 1-DAY RENTAL 19-JUN-06 00:00:00                         1
 1013 Y       1021 3-DAY RENTAL 19-JUN-06 00:00:00                         3
 1013 Y       1022 5-DAY RENTAL 19-JUN-06 00:00:00                         5
 1014 N       1020 1-DAY RENTAL 28-APR-06 00:00:00 28-MAY-06 00:00:00      3
 1014 N       1021 3-DAY RENTAL 28-APR-06 00:00:00 28-MAY-06 00:00:00     10
 1014 N       1022 5-DAY RENTAL 28-APR-06 00:00:00 28-MAY-06 00:00:00     15
 1014 Y       1020 1-DAY RENTAL 29-MAY-06 00:00:00                         1
 1014 Y       1021 3-DAY RENTAL 29-MAY-06 00:00:00                         3
 1014 Y       1022 5-DAY RENTAL 29-MAY-06 00:00:00                         5
 1015 N       1020 1-DAY RENTAL 01-MAR-92 00:00:00 31-MAR-92 00:00:00      3
 1015 N       1021 3-DAY RENTAL 01-MAR-92 00:00:00 31-MAR-92 00:00:00     10
 1015 N       1022 5-DAY RENTAL 01-MAR-92 00:00:00 31-MAR-92 00:00:00     15
 1015 Y       1020 1-DAY RENTAL 01-APR-92 00:00:00                         1
 1015 Y       1021 3-DAY RENTAL 01-APR-92 00:00:00                         3
 1015 Y       1022 5-DAY RENTAL 01-APR-92 00:00:00                         5
 1016 N       1020 1-DAY RENTAL 05-JAN-98 00:00:00 04-FEB-98 00:00:00      3
 1016 N       1021 3-DAY RENTAL 05-JAN-98 00:00:00 04-FEB-98 00:00:00     10
 1016 N       1022 5-DAY RENTAL 05-JAN-98 00:00:00 04-FEB-98 00:00:00     15
 1016 Y       1020 1-DAY RENTAL 05-FEB-98 00:00:00                         1
 1016 Y       1021 3-DAY RENTAL 05-FEB-98 00:00:00                         3
 1016 Y       1022 5-DAY RENTAL 05-FEB-98 00:00:00                         5
 1017 N       1020 1-DAY RENTAL 02-NOV-99 00:00:00 02-DEC-99 00:00:00      3
 1017 N       1021 3-DAY RENTAL 02-NOV-99 00:00:00 02-DEC-99 00:00:00     10
 1017 N       1022 5-DAY RENTAL 02-NOV-99 00:00:00 02-DEC-99 00:00:00     15
 1017 Y       1020 1-DAY RENTAL 03-DEC-99 00:00:00                         1
 1017 Y       1021 3-DAY RENTAL 03-DEC-99 00:00:00                         3
 1017 Y       1022 5-DAY RENTAL 03-DEC-99 00:00:00                         5
 1018 N       1020 1-DAY RENTAL 28-JUN-94 00:00:00 28-JUL-94 00:00:00      3
 1018 N       1021 3-DAY RENTAL 28-JUN-94 00:00:00 28-JUL-94 00:00:00     10
 1018 N       1022 5-DAY RENTAL 28-JUN-94 00:00:00 28-JUL-94 00:00:00     15
 1018 Y       1020 1-DAY RENTAL 29-JUL-94 00:00:00                         1
 1018 Y       1021 3-DAY RENTAL 29-JUL-94 00:00:00                         3
 1018 Y       1022 5-DAY RENTAL 29-JUL-94 00:00:00                         5
 1019 N       1020 1-DAY RENTAL 11-DEC-91 00:00:00 10-JAN-92 00:00:00      3
 1019 N       1021 3-DAY RENTAL 11-DEC-91 00:00:00 10-JAN-92 00:00:00     10
 1019 N       1022 5-DAY RENTAL 11-DEC-91 00:00:00 10-JAN-92 00:00:00     15
 1019 Y       1020 1-DAY RENTAL 11-JAN-92 00:00:00                         1
 1019 Y       1021 3-DAY RENTAL 11-JAN-92 00:00:00                         3
 1019 Y       1022 5-DAY RENTAL 11-JAN-92 00:00:00                         5
 1020 N       1020 1-DAY RENTAL 04-DEC-92 00:00:00 03-JAN-93 00:00:00      3
 1020 N       1021 3-DAY RENTAL 04-DEC-92 00:00:00 03-JAN-93 00:00:00     10
 1020 N       1022 5-DAY RENTAL 04-DEC-92 00:00:00 03-JAN-93 00:00:00     15
 1020 Y       1020 1-DAY RENTAL 04-JAN-93 00:00:00                         1
 1020 Y       1021 3-DAY RENTAL 04-JAN-93 00:00:00                         3
 1020 Y       1022 5-DAY RENTAL 04-JAN-93 00:00:00                         5
 1021 N       1020 1-DAY RENTAL 15-MAY-98 00:00:00 14-JUN-98 00:00:00      3
 1021 N       1021 3-DAY RENTAL 15-MAY-98 00:00:00 14-JUN-98 00:00:00     10
 1021 N       1022 5-DAY RENTAL 15-MAY-98 00:00:00 14-JUN-98 00:00:00     15
 1021 Y       1020 1-DAY RENTAL 15-JUN-98 00:00:00                         1
 1021 Y       1021 3-DAY RENTAL 15-JUN-98 00:00:00                         3
 1021 Y       1022 5-DAY RENTAL 15-JUN-98 00:00:00                         5
 1022 Y       1020 1-DAY RENTAL 28-MAY-14 00:00:00                         3
 1022 Y       1021 3-DAY RENTAL 28-MAY-14 00:00:00                        10
 1022 Y       1022 5-DAY RENTAL 28-MAY-14 00:00:00                        15
 1023 Y       1020 1-DAY RENTAL 28-MAY-14 00:00:00                         3
 1023 Y       1021 3-DAY RENTAL 28-MAY-14 00:00:00                        10
 1023 Y       1022 5-DAY RENTAL 28-MAY-14 00:00:00                        15
 1024 Y       1020 1-DAY RENTAL 28-MAY-14 00:00:00                         3
 1024 Y       1021 3-DAY RENTAL 28-MAY-14 00:00:00                        10
 1024 Y       1022 5-DAY RENTAL 28-MAY-14 00:00:00                        15
 
135 rows selected.

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_lab7.sql script, you should navigate to the /home/student/Data/cit225/oracle/lab7 directory. You call the sqlplus utility from the same directory and then you run the apply_oracle_lab7.sql script with the following syntax:

@apply_oracle_lab7.sql

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

Written by michaelmclaughlin

August 13th, 2018 at 11:25 am

Posted in