Database Tutorial

Lab #6: SQL UPDATE Statement

Learn how to use ALTER TABLE and UPDATE statements to modify an existing table with data already inserted into the table.

• Lab
  • Description
  • Help Section
  • Instructions

Lab Description

The lab requires you modify scripts that create and unit test ten tables. The integration script calls the ten unit test programs, which you can find in the following directory:

/home/student/Data/cit225/oracle/lib1

The lab has four parts, which you find after clicking on the Lab Instructions link. The steps are:

• Step 1: Add the RENTAL_ITEM_PRICE and RENTAL_ITEM_TYPE columns to the RENTAL_ITEM table..
• Step 2: Create the PRICE table as per the specification qualified in the lab.
• Step 3: Contains four steps:
  1. Rename the ITEM_RELEASE_DATE column as the RELEASE_DATE column;
  2. Insert three new rows in the ITEM table;
  3. Insert a new member account with three contacts;
  4. Insert two rows into the RENTAL table with a dependent row for each row in the RENTAL_ITEM table; and
  5. Insert one row into the RENTAL table with two dependent rows in the RENTAL_ITEM table.
• Step 4: Contains four steps:
  1. Drop the COMMON_LOOKUP_N1 and COMMON_LOOKUP_U2 indexes;
  2. Add three new columns to the COMMON_LOOKUP table;
  3. Migrate the COMMON_LOOKUP_CONTEXT column values;
  4. Use an INSERT statement to add two rows for the TELEPHONE table’s lookup values to the COMMON_LOOKUP table;
  5. Use the ALTER TABLE or CREATE INDEX statements against the COMMON_LOOKUP table to cleanup the table structure, add NOT NULL constraints, and add a UNIQUE INDEX on the COMMON_LOOKUP table; and
  6. Use an UPDATE statement to change the TELEPHONE_TYPE column values in the TELEPHONE table.