Database Tutorial

Course Tutorial Site

Site Admin

Lab #7: Oracle

with 24 comments

Lab #7: Oracle Assignment

You begin these steps after running the create_oracle_store.sql, the seed_oracle_store.sql, apply_oracle_lab5.sql, and apply_oracle_lab6.sql scripts. You should create the apply_oracle_lab7.sql script as follows:

-- This calls Lab #6, Lab #6 calls Lab #5, and Lab #5 calls both the Creation and Seed script files.
@../lab6/apply_oracle_lab6.sql
 
SPOOL apply_oracle_lab7.txt
 
... insert code here ...
 
SPOOL OFF

You should embed the verification queries inside your apply_lab7_oracle.sql script.

  1. [3 points] Insert two new rows into the COMMON_LOOKUP table to support the ACTIVE_FLAG column in the PRICE table.

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.

  1. [3 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.

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.

  1. [4 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.

  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.

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.

  1. 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 rows.)

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

SELECT   i.item_id
,        CASE
           WHEN  ... implement logic ... THEN
           ELSE  ... implement logic ...
         END AS active flag
,        CASE
           WHEN  ... implement logic ... THEN
           ELSE  ... implement logic ...
         END AS price_type
,  ...
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 ...
ORDER BY 1, 2, 3;

The combination of valid ACTIVE_FLAG and AMOUNT columns are shown below:



Table Name: PRICE
Distribution of Rows
Active
Flag		 Rental
Type		 New Release Old Release Total
<= 30 > 30 <= 30 > 30
Y 1 3 21 24
3 3 21 24
5 3 21 24
N 1 21 21
3 21 21
5 21 21
Total 9 63 63 135

The logic represented in this problem is a standard business system. All items must maintain a price at the time of sale or rental, and prices change over time. Our simplified video store problem has only two prices for each type of rental. One price is the active price and the other the inactive price. All active prices have a start and end date values, but as a rule the active price has a null end date value. You need an active and inactive price for the three types of rentals – 1, 3, and 5 day rentals. Therefore, each row in the ITEM table requires:

  • Three rows in the PRICE table when they’re within 30 days of their release date.
  • Six rows in the PRICE table when the current date is 31 days or more from their release date.

The following is illustrates the business and system logic that occurs after close of business on the 30th from the video’s release date. It shows how you inactivate the original row and how you add a new active row.

Without a WHERE clause, the sample fabrication query returns 144 rows. The WHERE clause should filter out those rows where the ACTIVE_FLAG is 'N' and the current date minus 30 is less than the item’s release date (a negation filter). The SELECT-list uses a CASE statements to determine the correct ACTIVE_FLAG and AMOUNT based on the relationship of other column values.

Detailed Business Rules & System Logic ↓

You can view the detailed business rules and system logic by expanding this section.

Business Rules:

  1. Rentals are never made in advance of today’s date.
  2. Rental prices may change any day after midnight, typically through a batch process (spawned by a scheduler). A batch process will:
  1. Update the existing active PRICE rows to inactive PRICE rows by making two changes: Set the ACTIVE_FLAG column value to 'N' and set the END_DATE column value to the START_DATE column value plus 30.
  2. Insert new active PRICE rows, as follows: Use an ACTIVE_FLAG value of ‘Y’, a START_DATE that is 31 days greater than the related RELEASE_DATE column in the ITEM table, an END_DATE that is null, and a PRICE_TYPE that maps to a 1, 3, or 5 day rental.
  1. All rental rates should be stored for tax, historical and analytical purposes.
  2. Current rental prices are determined by the natural key of the PRICE table, which is the ITEM_ID, PRICE_TYPE, ACTIVE_FLAG, START_DATE, and END_DATE columns.
  3. Only three types of rentals exist. They are a 1-Day, 3-Day, and 5-Day rental.
  4. New release rental prices are $3, $10, and $15 for 1-Day, 3-Day, and 5-Day rentals respectively.
  5. Old release rental prices are $1, $3, and $5 for 1-Day, 3-Day, and 5-Day rentals respectively.

System Logic:

  1. The END_DATE column should be nullable. In an Oracle implementation, the END_DATE should also have a CHECK constraint that disallows any date but one that is 30 days later than the START_DATE. This requirement means that all existing date will need to be updated from a date timestamp to a truncated date timestamp value (midnight of any day). This could have been avoided had the original INSERT statements used a TRUNC(SYSDATE) syntax. When porting this to databases that don’t support a CHECK constraint, like MySQL, the column will be nullable but otherwise unconstrained.
  2. The ACTIVE_FLAG column should be constrained to accept only a 'Y' or 'N' value, and a value should be required. In an Oracle implementation, this may be effected through a CHECK constraint. In a MySQL implementation, this may be effected through a ENUM data type.
  3. The PRICE_TYPE column can only contain a valid value found in the COMMON_LOOKUP table. This requires a FOREIGN KEY constraint and a scalar subquery against the COMMON_LOOKUP table. Please note that the COMMON_LOOKUP table is also being re-engineered because of prior design problems, and you should defer updates to the PRICE table until the COMMON_LOOKUP table is fixed. You should also defer placing a FOREIGN KEY constraint until after Step #7 below (that’s when it’ll have values).
  4. The AMOUNT can be determined by using formulas based on existing data. There are two compound cases:
    1. When the ACTIVE_FLAG is 'Y' or 'N', and the current system date minus the RELEASE_DATE is less than 31, then the PRICE_TYPE of a 1-Day, 3-Day, or 5-Day rental type sets the amount values at $3, $10, and $15 respectively. A single PRICE row exists for each ITEM_ID and PRICE_TYPE combination when the current system date minus the RELEASE_DATE is less than 31 for new releases. Two PRICE table rows exist for each ITEM_ID and PRICE_TYPE combination when the current system date minus the RELEASE_DATE is greater than 30 because the ACTIVE_FLAG value can be either a ‘Y’ or ‘N’. All rentals have the ACTIVE_FLAG value of 'Y' when they’re new, and a value of 'N' when they’re 31 days from their release date.
    2. When the ACTIVE_FLAG is 'Y' or 'N', and the current system date minus the RELEASE_DATE is greater than 30, then the PRICE_TYPE of a 1-Day, 3-Day, or 5-Day rental type sets the amount values at $1, $3, and $5 respectively. In this model, all releases where the current system date minus the RELEASE_DATE is greater than 30 days are old releases with the lower price amounts.

You can use the following to calculate whether a ITEM row belongs in the new or old price group. All ITEM rows that have a RELEASE_DATE value more than 30 days older than the current system date should have two PRICE rows for each type of rental (1-Day, 3-Day, and 5-Day rentals).

New Release Date Calculation

You calculate whether an item is a new release by the following formula:

WHERE (TRUNC(SYSDATE) - TRUNC(i.release_date)) < 31

or

WHERE TRUNC(SYSDATE) - 30) < i.release_date

Old Release Date Calculation

You calculate whether an item is an old release by the following formula:

WHERE (TRUNC(SYSDATE) - TRUNC(i.release_date)) >= 30

or

WHERE TRUNC(SYSDATE) - 30) >= i.release_date

Active Flag Calculation

You calculate whether an item has an active flag by the following formula:

WHERE af.active_flag = 'Y'

Inactive Flag Calculation

You calculate whether an item has an inactive flag by the following formula:

WHERE af.active_flag = 'N'

Exclusionary WHERE clause

You combine a new or old release date calculation with an active or inactive flag calculation. Here’s a pseudocode formula:

WHERE NOT ((active OR inactive flag comparison) AND (NEW OR OLD release DATE gap))

This should help you visualize the use case for your query because you’ll use the query with an INSERT statement in Lab #8:

Table Name: PRICE
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
PRICE_ID PRIMARY KEY Integer Maximum
ITEM_ID FOREIGN KEY ITEM ITEM_ID Integer Maimum
PRICE_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
ACTIVE_FLAG NOT NULL Text 1
CHECK(‘Y’,’N’)
START_DATE NOT NULL Date Date
END_DATE Date Date
AMOUNT Integer Maimum
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATION_DATE NOT NULL Date Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
LAST_UPDATE_DATE NOT NULL Date Date

The preceding query displays the results shown below. 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 COMMON_LOOKUP_TABLE, COMMON_LOOKUP_COLUMN, and COMMON_LOOKUP_TYPE columns. String literals match the COMMON_LOOKUP_TABLE and COMMON_LOOKUP_COLUMN column values for a subset of the rows in the COMMON_LOOKUP table. The RENTAL_DAYS column from the fabricated result set matches the remaining COMMON_LOOKUP_TYPE column of the natural key. The one character RENTAL_DAYS column matches the first character of the COMMON_LOOKUP_TYPE column value to identify a unique result for the join.

The SELECT-list needs to return a date data type. Unfortunately, Oracle only returns a timestamp data type. You use the SQL TRUNC function to eliminate values to the right of the decimal point for timestamps. The SQL TRUNC function effectively returns an integer.

The following syntax shows the timestamp value of an Oracle DATE data type:

TO_CHAR(TRUNC(SYSDATE),'DD-MON-YY HH24:MI:SS')

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 data 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.

Written by michaelmclaughlin

May 29th, 2014 at 3:05 pm

Posted in

24 Responses to 'Lab #7: Oracle'

Subscribe to comments with RSS or TrackBack to 'Lab #7: Oracle'.

  1. I found a spelling error in step 3.B. it says “Change the RENTAL_ITEM_TYPE column of the RENTAL_ITEM column from…” Should it say “RENTAL_ITEM table” and not “RENTAL_ITEM column?”

    Kent

    2 Jun 14 at 12:49 pm

  2. Could you please provide the business scenario that this lab is supposed to satisfy? I understand that we are tracking the prices for goods sold that are being used in the present and in the past but knowing the business rules and how they are translated into an actual database operation would be helpful.

    Matthew

    2 Jun 14 at 4:02 pm

  3. STEP 4:
    Paragraph after price distribution table. “All active prices have a start and end date values except the current or active price…”
    – The first active makes the statement sound contradictory.

    Bullets after price distribution table.”Six rows in the PRICE…; and three inactive prices and three active prices”
    – The and makes it sound like a total of twelve rows.

    Paragraph after the row diagram. “Without a WHERE clause, this query returns 144 rows.”
    – There is no preceding query that I can find.

    Chris Hepworth

    3 Jun 14 at 3:57 pm

  4. Step #3a’s check is missing a closing semicolon.

    Matthew

    3 Jun 14 at 8:25 pm

  5. Fixed already.

    michaelmclaughlin

    3 Jun 14 at 9:18 pm

  6. It should be RENTAL_ITEM table; and it’s fixed.

    michaelmclaughlin

    3 Jun 14 at 9:25 pm

  7. SHould the instruction in step 2 say add 6 new rows 3 for price_ype and 3 for rental_item_type?

    Matt

    25 Oct 14 at 11:08 am

  8. Matt, good catch. I’ve made the change.

    michaelmclaughlin

    25 Oct 14 at 1:20 pm

  9. apply_lab6_oracle.SQL
    SPOOL apply_lab7_oracle.txt

    A few people have been confused because of these.
    Can you change it to the correct file names with “oracle” in the middle.

    RJ

    29 Oct 14 at 1:38 pm

  10. RJ, It’s fixed. Thanks.

    michaelmclaughlin

    30 Oct 14 at 8:21 pm

  11. The following line seemed somewhat vague to me: “The following query, let’s you verify that you’ve set the foreign key constraint for the RENTAL_ITEM_TYPE column.” In my mind, it would be more clear If you had another step that said something along the lines of “Set the foreign key constraint.” And then after that, we could copy in the code to verify the script.

    Bryce

    31 Oct 14 at 3:19 pm

  12. Bryce, I believe it’s fixed. Let me know if you don’t concur with the fix.

    michaelmclaughlin

    1 Nov 14 at 1:41 am

  13. Step #3a refers to a casting error that doesn’t actually occur in 11g, only in 10g. The code works as is in 11g due to the built-in implicit casting functionality.

    Gabe Ventilla

    4 Nov 14 at 2:04 am

  14. In step# 3a of lab 7 we check if the RENTAL_ITEM_PRICE and RENTAL_ITEM_TYPE columns have been added to the RENTAL_ITEM table correctly but that is done as the first step in lab 6. In other words, we validate a step that was done in a previous lab which gives reason for confusion. I would suggest to either clarify that we are just double checking a step done in lab 6 or to skip the validation at this point as the exact same validation query is already run in lab 6. The text that precedes the validation query is the following: “You should use the following to verify the addition of the two new columns:”

    Gabe Ventilla

    4 Nov 14 at 2:47 am

  15. In order for the PRICE_TYPE column to reference the correct rows from the COMMON_LOOKUP table, I believe that the bottom of the given data fabrication query should read as follows:

    WHERE    cl.common_lookup_table  = 'PRICE'
AND      cl.common_lookup_column = 'PRICE_TYPE'

    instead of

    WHERE    cl.common_lookup_table  = 'RENTAL_ITEM'
AND      cl.common_lookup_column = 'RENTAL_ITEM_TYPE'

    Gabe Ventilla

    4 Nov 14 at 3:34 am

  16. Gabe, great catch. I’ve updated the sentence, please let me know if you concur.

    michaelmclaughlin

    6 Nov 14 at 1:40 pm

  17. Gabe, This exists in Lab #7 because sometimes students move through Lab #6 without completing the step. I’ve added a note to indicate that this is a repeat of action in Lab #6.

    michaelmclaughlin

    6 Nov 14 at 1:57 pm

  18. Gabe, That’s great. I fixed the lack of the three rows in the COMMON_LOOKUP table but forgot to change the reference in the fabrication query. The PRICE_TYPE column of the PRICE table should be the reference to garner the correct foreign key value.

    michaelmclaughlin

    9 Nov 14 at 1:06 am

  19. Sounds great to me, thanks.

    Gabe Ventilla

    10 Nov 14 at 2:20 pm

  20. If anyone has trouble with the update provided by McLaughlin, make sure that the way you insert the rentals in lab 6 is really working, otherwise the subtraction done won’t work.

    Hanlet

    21 Feb 15 at 3:44 pm

  21. Hanlet, I believe you’re referring to a similar comment made by Greg. Is that correct? Did the qualification in that comment answer help clarify the issue you’re referring to?

    michaelmclaughlin

    7 Mar 15 at 1:13 am

  22. Also, the rental_item_price column’s not null does not need to be there until lab 8.

    RJ

    15 Nov 16 at 9:32 pm

  23. RENTAL_ITEM_PRICE in the table display shows that it should be set to NOT NULL. However this doesn’t appear to be correct and only the RENTAL_ITEM_PRICE should be set to NOT NULL after populating the records.

    Rick Smith

    21 Feb 18 at 12:47 am

  24. Yes, I’m re-writing the instructions. I’ll update you with the new URL.

    michaelmclaughlin

    22 Feb 18 at 10:29 am

Leave a Reply