Database Tutorial

Lab #4: Insert Patterns

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

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

• 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 ten parts, which you find after clicking on the Lab Instructions link. The steps are:

• Step 1: Copy, edit, and test a two-member group account set of INSERT statements.
• Step 2: Copy, edit, and test a two-member group account set of INSERT statements.
• Step 3: Copy, edit, and test a two-member group account set of INSERT statements.
• Step 4: Copy, edit, and test a series of INSERT statements to the ITEM table.
• Step 5: Copy, edit, and test a stored procedure that inserts a single member creation in the MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables.
• Step 6: Call your modified stored procedure to insert five single member accounts.
• Step 7: Copy, edit, and test a script that updates the MEMBER_TYPE column of the MEMBER table.
• Step 8: Copy, edit, and test a script that inserts nine related record sets into the RENTAL and dependent RENTAL_ITEM tables.
• Step 9: Copy, edit, and test the creation of a view across three tables.
• Step 10: Copy, edit, and test the integration script.