Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #8: Insert data from queries

Learn how to use an INSERT statement with a query of other data and an embedded sequence value. Reinforce how to alter a table’s column to add a NOT NULL constraint, update a column with a correlated UPDATE statement, learn how to use a correlated UPDATE statement to set an initial value in a newly added column.

Lab Instructions

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

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

Lab Instructions →

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

Sample script →

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

-- ------------------------------------------------------------------
-- Call the prior lab.
-- ------------------------------------------------------------------
 
@@/home/student/Data/cit225/oracle/lab7/apply_oracle_lab7.sql
 
-- Open log file.  
SPOOL apply_oracle_lab8.txt
 
-- --------------------------------------------------------
--  Step #1
--  -------
--  Using the query from Lab 7, Step 4, insert the 135
--  rows in the PRICE table created in Lab 6.
-- --------------------------------------------------------
 
-- Insert step #1 statements here.
 
-- --------------------------------------------------------
--  Step #2
--  -------
--  Add a NOT NULL constraint on the PRICE_TYPE column
--  of the PRICE table.
-- --------------------------------------------------------
 
-- Insert step #2 statements here.
 
-- --------------------------------------------------------
--  Step #3
--  -------
--  Update the RENTAL_ITEM_PRICE column with valid price
--  values in the RENTAL_ITEM table.
-- --------------------------------------------------------
 
-- Insert step #3 statements here.
 
-- --------------------------------------------------------
--  Step #4
--  -------
--  Add a NOT NULL constraint on the RENTAL_ITEM_PRICE
--  column of the RENTAL_ITEM table.
-- --------------------------------------------------------
 
-- Insert step #4 statements here.
 
-- Close log file.
SPOOL OFF
 
-- Make all changes permanent.
COMMIT;

You should complete the following four steps:

  1. [8 points] Using the query you developed in Step #4 of Lab #7, add the necessary columns to insert the values directly into the price table. You will need to add values for the price_id, created_by, creation_date, last_updated_by, and last_update_date columns.

    If you did not create a price_s sequence in Lab #6, you should create the price_id sequence with a starting value of 1001. You modify the query from the last lab by adding the following:

    • A price_s.nextval call to populate the price_id column of the price table.
    • A a subquery like the following to populate the created_by and last_updated_by columns. 

      SELECT su.system_user_id
FROM   system_user su
WHERE  su.system_user_name = 'Some valid string for a system_name value'

    • A TRUNC(SYSDATE) call for the creation_date and last_update_date columns.

    Given the following definition of the price table:

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

You should use an INSERT statement like the following (where you complete the missing components in Lab #7 or Lab #8):

INSERT INTO price
( price_id
, item_id
, price_type
, active_flag
, start_date
, end_date
, amount
, created_by
, creation_date
, last_updated_by
, last_update_date )
( SELECT price_s.NEXTVAL
  ,        item_id
  ,        price_type
  ,        active_flag
  ,        start_date
  ,        end_date
  ,        amount
  ,        created_by
  ,        creation_date
  ,        last_updated_by
  ,        last_update_date
  FROM 
    (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" FROM Lab #7... THEN ...result VALUE...
                ELSE  ...result VALUE ...
              END AS start_date
     ,        CASE
                WHEN  ...implement logic "C" FROM Lab #7... THEN ...result VALUE...
              END AS end_date
     ,        CASE
                WHEN  ...implement logic "D" FROM Lab #7... THEN ...result VALUE...
                ELSE  ...result VALUE ...
            END AS amount
     ,        ( ... subquery ... ) AS created_by
     ,        ( ... truncated CURRENT DATE ...) AS creation_date
     ,        ( ... subquery ... ) AS last_updated_by
     ,        ( ... truncated CURRENT DATE ...) AS last_update_date
     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" FROM Lab #7...)));

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

SELECT  'OLD Y' AS "Type"
,        COUNT(CASE WHEN amount = 1 THEN 1 END) AS "1-Day"
,        COUNT(CASE WHEN amount = 3 THEN 1 END) AS "3-Day"
,        COUNT(CASE WHEN amount = 5 THEN 1 END) AS "5-Day"
,        COUNT(*) AS "TOTAL"
FROM     price p , item i
WHERE    active_flag = 'Y' AND i.item_id = p.item_id
AND     (TRUNC(SYSDATE) - TRUNC(i.release_date)) > 30
AND      end_date IS NULL
UNION ALL
SELECT  'OLD N' AS "Type"
,        COUNT(CASE WHEN amount =  3 THEN 1 END) AS "1-Day"
,        COUNT(CASE WHEN amount = 10 THEN 1 END) AS "3-Day"
,        COUNT(CASE WHEN amount = 15 THEN 1 END) AS "5-Day"
,        COUNT(*) AS "TOTAL"
FROM     price p , item i
WHERE    active_flag = 'N' AND i.item_id = p.item_id
AND     (TRUNC(SYSDATE) - TRUNC(i.release_date)) > 30
AND NOT end_date IS NULL
UNION ALL
SELECT  'NEW Y' AS "Type"
,        COUNT(CASE WHEN amount =  3 THEN 1 END) AS "1-Day"
,        COUNT(CASE WHEN amount = 10 THEN 1 END) AS "3-Day"
,        COUNT(CASE WHEN amount = 15 THEN 1 END) AS "5-Day"
,        COUNT(*) AS "TOTAL"
FROM     price p , item i
WHERE    active_flag = 'Y' AND i.item_id = p.item_id
AND     (TRUNC(SYSDATE) - TRUNC(i.release_date)) < 31
AND      end_date IS NULL
UNION ALL
SELECT  'NEW N' AS "Type"
,        COUNT(CASE WHEN amount = 1 THEN 1 END) AS "1-Day"
,        COUNT(CASE WHEN amount = 3 THEN 1 END) AS "3-Day"
,        COUNT(CASE WHEN amount = 5 THEN 1 END) AS "5-Day"
,        COUNT(*) AS "TOTAL"
FROM     price p , item i
WHERE    active_flag = 'N' AND i.item_id = p.item_id
AND     (TRUNC(SYSDATE) - TRUNC(i.release_date)) < 31
AND      NOT (end_date IS NULL);

It should display the following results:

Type       1-Day      3-Day      5-Day      TOTAL
----- ---------- ---------- ---------- ----------
OLD Y         21         21         21         63
OLD N         21         21         21         63
NEW Y          3          3          3          9
NEW N          0          0          0          0
 
4 rows selected.

  1. [4 points] You should add the NOT NULL constraint to the PRICE_TYPE column of the PRICE table.

You should use the following query to verify that the PRICE_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 = 'PRICE'
AND      column_name = 'PRICE_TYPE';

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

TABLE_NAME     COLUMN_NAME            CONSTRAINT
-------------- ---------------------- ----------
PRICE          PRICE_TYPE             NOT NULL
 
1 row selected.

  1. [6 points] The following query should update the RENTAL_ITEM_PRICE column for thirteen rows in the RENTAL_ITEM table. It should return nine values of 5, and two values of 3, and one value of 1, 10 and 15 each. If it returns anything else, you’ve encountered a failure. Like the other DML statements, this one contains a couple errors. You need to fix it before completing this step, and you can find the missing components in the diagnostic query below the UPDATE statement.

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

UPDATE   rental_item ri
SET      rental_item_price =
          (SELECT   p.amount
           FROM     price p INNER JOIN common_lookup cl1
           ON       p.price_type = cl1.common_lookup_id CROSS JOIN rental r
                    CROSS JOIN common_lookup cl2 
           WHERE    p.item_id = ri.item_id
           AND      ri.rental_id = r.rental_id
           AND      ri.rental_item_type = cl2.common_lookup_id
           AND      cl1.common_lookup_code = cl2.common_lookup_code
           AND      r.check_out_date
                      BETWEEN p.start_date AND p.end_date);

You can confirm you’ve updated the correct rows with the following query, which implements the complete logic required of a successful UPDATE statement:

-- Widen the display console.
SET LINESIZE 110
 
-- Set the column display values.
COL customer_name          FORMAT A20  HEADING "Contact|--------|Customer Name"
COL contact_id             FORMAT 9999 HEADING "Contact|--------|Contact|ID #"
COL customer_id            FORMAT 9999 HEADING "Rental|--------|Customer|ID #"
COL r_rental_id            FORMAT 9999 HEADING "Rental|------|Rental|ID #"
COL ri_rental_id           FORMAT 9999 HEADING "Rental|Item|------|Rental|ID #"
COL rental_item_id         FORMAT 9999 HEADING "Rental|Item|------||ID #"
COL price_item_id          FORMAT 9999 HEADING "Price|------|Item|ID #"
COL rental_item_item_id    FORMAT 9999 HEADING "Rental|Item|------|Item|ID #"
COL rental_item_price      FORMAT 9999 HEADING "Rental|Item|------||Price"
COL amount                 FORMAT 9999 HEADING "Price|------||Amount"
COL price_type_code        FORMAT 9999 HEADING "Price|------|Type|Code"
COL rental_item_type_code  FORMAT 9999 HEADING "Rental|Item|------|Type|Code"
SELECT   c.last_name||', '||c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' '||c.middle_name
         END AS customer_name
,        c.contact_id
,        r.customer_id
,        r.rental_id AS r_rental_id
,        ri.rental_id AS ri_rental_id
,        ri.rental_item_id
,        p.item_id AS price_item_id
,        ri.item_id AS rental_item_item_id
,        ri.rental_item_price
,        p.amount
,        TO_NUMBER(cl2.common_lookup_code) AS price_type_code
,        TO_NUMBER(cl2.common_lookup_code) AS rental_item_type_code
FROM     price p INNER JOIN common_lookup cl1
ON       p.price_type = cl1.common_lookup_id
AND      cl1.common_lookup_table = 'PRICE'
AND      cl1.common_lookup_column = 'PRICE_TYPE' FULL JOIN rental_item ri 
ON       p.item_id = ri.item_id INNER JOIN common_lookup cl2
ON       ri.rental_item_type = cl2.common_lookup_id
AND      cl2.common_lookup_table = 'RENTAL_ITEM'
AND      cl2.common_lookup_column = 'RENTAL_ITEM_TYPE' RIGHT JOIN rental r
ON       ri.rental_id = r.rental_id FULL JOIN contact c
ON       r.customer_id = c.contact_id
WHERE    cl1.common_lookup_code = cl2.common_lookup_code
AND      r.check_out_date
BETWEEN  p.start_date AND NVL(p.end_date,TRUNC(SYSDATE) + 1)
ORDER BY 2, 3;
 
-- Reset the column display values to their default value.
SET LINESIZE 80

The query should return the following data set. If it doesn’t you’ve made a mistake in one or more prior steps. You need to return and review those steps after you understand the correlated UPDATE statement.

					      Rental Rental	   Rental Rental	       Rental
		      Contact	Rental Rental	Item   Item  Price   Item   Item  Price  Price	 Item
Contact 	     -------- -------- ------ ------ ------ ------ ------ ------ ------ ------ ------
--------	      Contact Customer Rental Rental	      Item   Item		  Type	 Type
Customer Name		 ID #	  ID #	 ID #	ID #   ID #   ID #   ID #  Price Amount   Code	 Code
-------------------- -------- -------- ------ ------ ------ ------ ------ ------ ------ ------ ------
Winn, Brian		 1002	  1002	 1005	1005   1008   1007   1007      5      5      5	    5
Winn, Brian		 1002	  1002	 1005	1005   1009   1001   1001      5      5      5	    5
Vizquel, Oscar		 1003	  1003	 1001	1001   1003   1005   1005      5      5      5	    5
Vizquel, Oscar		 1003	  1003	 1001	1001   1002   1004   1004      5      5      5	    5
Vizquel, Oscar		 1003	  1003	 1001	1001   1001   1002   1002      5      5      5	    5
Vizquel, Doreen 	 1004	  1004	 1002	1002   1005   1021   1021      5      5      5	    5
Vizquel, Doreen 	 1004	  1004	 1002	1002   1004   1016   1016      5      5      5	    5
Sweeney, Meaghan	 1005	  1005	 1003	1003   1006   1019   1019      5      5      5	    5
Sweeney, Ian M		 1007	  1007	 1004	1004   1007   1014   1014      5      5      5	    5
Potter, Harry		 1013	  1013	 1006	1006   1011   1022   1022      1      1      1	    1
Potter, Harry		 1013	  1013	 1006	1006   1010   1022   1022      3      3      1	    1
Potter, Ginny		 1014	  1014	 1007	1007   1012   1023   1023     10     10      3	    3
Potter, Lily Luna	 1015	  1015	 1008	1008   1013   1024   1024     15     15      5	    5

If you didn’t get the results above, you may have an error in several places. You could have an error when you:

  • Inserted the records into the MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables.
  • Inserted the records into the RENTAL and RENTAL_ITEM tables.
  • Inserted the records into the PRICE table.

You click on the “Detailed Problem Solving” link to find the advanced diagnostics to solve problems from the prior labs.

Detailed Problem Solving ↓

The following diagnostics help you determine which of the errors you may have made:

  • You can verify that the correct customers are configured with the following query. If they don’t show up in your environment with the following query, you made a mistake with the insert into the MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables. 

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

    COL customer_name  FORMAT A24  HEADING "Customer Name"
COL city           FORMAT A12  HEADING "City"
COL state          FORMAT A6   HEADING "State"
COL telephone      FORMAT A10  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 customer_name
,        a.city AS city
,        a.state_province AS state
,        t.telephone_number AS telephone
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN address a
ON       c.contact_id = a.contact_id INNER JOIN telephone t
ON       c.contact_id = t.contact_id;

    It should return the following rows:

    Account
Number	   Customer Name	    City	 State	Telephone
---------- ------------------------ ------------ ------ ----------
B293-71445 Winn, Randi		    San Jose	 CA	111-1111
B293-71445 Winn, Brian		    San Jose	 CA	111-1111
B293-71446 Vizquel, Oscar	    San Jose	 CA	222-2222
B293-71446 Vizquel, Doreen	    San Jose	 CA	222-2222
B293-71447 Sweeney, Meaghan	    San Jose	 CA	333-3333
B293-71447 Sweeney, Matthew	    San Jose	 CA	333-3333
B293-71447 Sweeney, Ian M	    San Jose	 CA	333-3333
R11-514-34 Clinton, Goeffrey Ward   Provo	 Utah	423-1234
R11-514-35 Moss, Wendy		    Provo	 Utah	423-1234
R11-514-36 Gretelz, Simon Jonah     Provo	 Utah	423-1234
R11-514-37 Royal, Elizabeth Jane    Provo	 Utah	423-1234
R11-514-38 Smith, Brian Nathan	    Spanish Fork Utah	423-1234
US00011    Potter, Harry	    Provo	 Utah	333-3333
US00011    Potter, Ginny	    Provo	 Utah	333-3333
US00011    Potter, Lily Luna	    Provo	 Utah	333-3333

  • You can verify that the correct rental and rental items are configured with the following query. If they don’t show up in your environment with the following query, you made a mistake with the insert into the RENTALRENTAL_ITEM tables. 

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

    COL account_number  FORMAT A10  HEADING "Account|Number"
COL customer_name   FORMAT A22  HEADING "Customer Name"
COL rental_id       FORMAT 9999 HEADING "Rental|ID #"
COL rental_item_id  FORMAT 9999 HEADING "Rental|Item|ID #"
SELECT   m.account_number
,        c.last_name||', '||c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' '||c.middle_name
         END AS customer_name
,        r.rental_id
,        ri.rental_item_id
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN rental r
ON       c.contact_id = r.customer_id INNER JOIN rental_item ri
ON       r.rental_id = ri.rental_id
ORDER BY 3, 4;

    It should return the following rows:

    					 Rental
Account 			  Rental   Item
Number	   Customer Name	    ID #   ID #
---------- ---------------------- ------ ------
B293-71446 Vizquel, Oscar	    1001   1001
B293-71446 Vizquel, Oscar	    1001   1002
B293-71446 Vizquel, Oscar	    1001   1003
B293-71446 Vizquel, Doreen	    1002   1004
B293-71446 Vizquel, Doreen	    1002   1005
B293-71447 Sweeney, Meaghan	    1003   1006
B293-71447 Sweeney, Ian M	    1004   1007
B293-71445 Winn, Brian		    1005   1008
B293-71445 Winn, Brian		    1005   1009
US00011    Potter, Harry	    1006   1010
US00011    Potter, Harry	    1006   1011
US00011    Potter, Ginny	    1007   1012
US00011    Potter, Lily Luna	    1008   1013

  • You can confirm you’ve updated the correct PRICE_TYPE and RENTAL_ITEM_TYPE foreign keys values with the following query:

    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

    COL common_lookup_table  FORMAT A12 HEADING "Common|Lookup Table"
COL common_lookup_column FORMAT A18 HEADING "Common|Lookup Column"
COL common_lookup_code   FORMAT 999 HEADING "Common|Lookup|Code"
COL total_pk_count       FORMAT 999 HEADING "Foreign|Key|Count"
SELECT   cl.common_lookup_table
,        cl.common_lookup_column
,        TO_NUMBER(cl.common_lookup_code) AS common_lookup_code
,        COUNT(*) AS total_pk_count
FROM     price p INNER JOIN common_lookup cl
ON       p.price_type = cl.common_lookup_id
AND      cl.common_lookup_table = 'PRICE'
AND      cl.common_lookup_column = 'PRICE_TYPE'
GROUP BY cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_code
UNION ALL
SELECT   cl.common_lookup_table
,        cl.common_lookup_column
,        TO_NUMBER(cl.common_lookup_code) AS common_lookup_code
,        COUNT(*) AS total_pk_count
FROM     rental_item ri INNER JOIN common_lookup cl
ON       ri.rental_item_type = cl.common_lookup_id
AND      cl.common_lookup_table = 'RENTAL_ITEM'
AND      cl.common_lookup_column = 'RENTAL_ITEM_TYPE'
GROUP BY cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_code
ORDER BY 1, 2, 3;

    It should return the following values:

    				Common Foreign
Common	     Common		Lookup	   Key
Lookup Table Lookup Column	  Code	 Count
------------ ------------------ ------ -------
PRICE	     PRICE_TYPE 	     1	    45
PRICE	     PRICE_TYPE 	     3	    45
PRICE	     PRICE_TYPE 	     5	    45
RENTAL_ITEM  RENTAL_ITEM_TYPE	     1	     2
RENTAL_ITEM  RENTAL_ITEM_TYPE	     3	     1
RENTAL_ITEM  RENTAL_ITEM_TYPE	     5	    10

  • The most common error occurs when you insert the wrong start date in the PRICE table. The following query will help you see that type of error: 

    COL customer_name          FORMAT A20  HEADING "Contact|--------|Customer Name"
COL r_rental_id            FORMAT 9999 HEADING "Rental|------|Rental|ID #"
COL amount                 FORMAT 9999 HEADING "Price|------||Amount"
COL price_type_code        FORMAT 9999 HEADING "Price|------|Type|Code"
COL rental_item_type_code  FORMAT 9999 HEADING "Rental|Item|------|Type|Code"
COL needle                 FORMAT A11  HEADING "Rental|--------|Check Out|Date"
COL low_haystack           FORMAT A11  HEADING "Price|--------|Start|Date"
COL high_haystack          FORMAT A11  HEADING "Price|--------|End|Date"
SELECT   c.last_name||', '||c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' '||c.middle_name
         END AS customer_name
,        ri.rental_id AS ri_rental_id
,        p.amount
,        TO_NUMBER(cl2.common_lookup_code) AS price_type_code
,        TO_NUMBER(cl2.common_lookup_code) AS rental_item_type_code
,        p.start_date AS low_haystack
,        r.check_out_date AS needle
,        NVL(p.end_date,TRUNC(SYSDATE) + 1) AS high_haystack
FROM     price p INNER JOIN common_lookup cl1
ON       p.price_type = cl1.common_lookup_id
AND      cl1.common_lookup_table = 'PRICE'
AND      cl1.common_lookup_column = 'PRICE_TYPE' FULL JOIN rental_item ri 
ON       p.item_id = ri.item_id INNER JOIN common_lookup cl2
ON       ri.rental_item_type = cl2.common_lookup_id
AND      cl2.common_lookup_table = 'RENTAL_ITEM'
AND      cl2.common_lookup_column = 'RENTAL_ITEM_TYPE' RIGHT JOIN rental r
ON       ri.rental_id = r.rental_id FULL JOIN contact c
ON       r.customer_id = c.contact_id
WHERE    cl1.common_lookup_code = cl2.common_lookup_code
AND      p.active_flag = 'Y'  
AND NOT  r.check_out_date
           BETWEEN  p.start_date AND NVL(p.end_date,TRUNC(SYSDATE) + 1)
ORDER BY 2, 3;

    You made the mistake if the start date occurs after the rental start date, like the following rows for the Harry, Ginny, and Lily Potter:

    		     Rental		  Rental
		       Item  Price  Price   Item Price	     Rental	 Price
Contact 	     ------ ------ ------ ------ --------    --------	 --------
--------	     Rental	     Type   Type Start	     Check Out	 End
Customer Name	       ID # Amount   Code   Code Date	     Date	 Date
-------------------- ------ ------ ------ ------ ----------- ----------- -----------
Potter, Harry	       1006	 1	1      1 01-JUL-18   16-JUN-18	 17-JUN-18
Potter, Harry	       1006	 3	3      1 01-JUL-18   16-JUN-18	 17-JUN-18
Potter, Ginny	       1007	10	3      3 01-JUL-18   16-JUN-18	 17-JUN-18
Potter, Lily Luna      1008	15	5      5 01-JUL-18   16-JUN-18	 17-JUN-18

  1. [4 points] Add a not null constraint to the RENTAL_ITEM_PRICE column of the RENTAL_ITEM table.

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 query to verify that the RENTAL_ITEM_PRICE 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_PRICE';

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

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

Test Case

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

Test Case Instructions →

You should embed the verification queries from these instructions inside your apply_lab8_oracle.sql script file. You should call the apply_lab8_oracle.sql script from the sqlplus command-line utility

@apply_oracle_lab8.sql

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

Written by michaelmclaughlin

August 13th, 2018 at 11:34 am

Posted in