Oracle Object type Exercises - VARRAY

For this lab you will begin by using the same set of tables that you used for Lab 1 so be sure that you are connected to Oracle as the DBM449_USER user.  The objective of this lab will be to create a series of object-relational tables using the SQL*Plus editor that will allow data to be stored in a more "real-world" format.  Data for your new tables can be found in the file Lab1_initialization.sql which is in the url http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html.  You will need to manipulate the data in various ways, but the file will give you access to the raw data to use.

L A B S T E P S

STEP 1: Create a table with a column data type

Modify the design of the COURSE table created in iLab 1 to incorporate the use of the column abstract data type.

1. Write and execute the SQL to create an object type called COURSE_OBJ1 that contains the attributes course code and course name.  Remember that with abstract objects you must use the / after the CREATE statement to execute it.
2. Next, write and execute the SQL to create a table called NEW_COURSE1 that contains COURSE_OBJ1 and applicable original attributes from the original COURSE table.  Keep in mind what attributes the new object type COURSE_OBJ1 contains.
3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_COURSE1.
4. Run DESCRIBE command to describe structure of table NEW_COURSE1.
5. SET DESCRIBE DEPTH 2 and run DESCRIBE NEW_COURSE1 again.
6. Execute a SELECT statement to query the data from the new table.  Use the COLUMN column_name FORMAT A## session command to format columns within the table to keep the result set data from wrapping around.  Be sure that you properly display data inside the object column. (HINT: When querying attributes of an abstract data type, you must use a correlation variable for the table.)

STEP 2: Create an object table with a row data type

Create a second COURSE table, this time as an object table using the row abstract data type.

1. Write and execute the SQL to create an object called COURSE_OBJ2 that contains the attributes course code, course name, course date, instructor, and location.
2. Write and execute the SQL to create a table called NEW_COURSE2 with each row defined by COURSE_OBJ2.
3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_COURSE2.
4. Execute a SELECT statement to query the data from the new table.

STEP 3: Create a Varying Array

Modify the design of the CLIENT table created in iLab 1 to incorporate the use of the Varying Array.

Write and execute the SQL to create a Varying Array to represent the phone contact information for the client (up to 3 phone numbers). Name the varying array as PHONE_LIST.

Write and execute the SQL to create a table called NEW_CLIENT that contains the attributes that the original CLIENT table contained plus the phone list array.

Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_CLIENT.

Execute a SELECT statement to query the data from the new table.

Related Documents

Basic Oracle Sql Exercise

Practice Exercise for Oracle PL/SQL

Oracle Procedure and Function Practice

Exercise Oracle package and package body

Database design practice for a Movie Company

Oracle Enterprise Manager Excercises

Oracle Database Installation Exercises

Oracle Database Creation Exercises

Database Administration Exercises - Week 3

Temporary Tables and Data Types Exercises

Exercises with Oracle Create Table Add Columns

Practice with User Profiles and User Roles in Oracle

Practice Oracle Joins Examples

Oracle Object type Exercises - VARRAY

Audit and Profile Management Exercises in Oracle