In this exercise, you will demonstrate your understanding of the object-relational features of Oracle.

Directions: Complete the tasks that follow.

  • Take screenshots to show the successful execution of SQL statements for the tasks indicated. Place the screenshots for the tasks indicated after the words, Place screenshot here.
  • Save all SQL statements to one text file named Name the file Additional_Lab_Assignment.txt.

Add comments to identify the task. Include a description of the SQL statement.


The deliverables for this assignment are:

  1. This document that contains the screenshots.
  2. The file named Additional_Lab_Assignment.txt.


  1. Read the lecture notes for Week 5.
  2. Review the on the object-relational features of Oracle
  3. Make a copy of the STUDENT table and name it STUDENT1.

One way to make a copy of the STUDENT table is to use a construction like:


Place screenshot here:

Define a user-defined object type data type named phone_type with attributes COUNTRY_CODE, AREA_CODE and PHONE_NUMBER. Use datatypes varchar2(3), varchar2(3), and varchar2(12), respectively.

Place screenshot here:

  • Define a user-defined VARRAY data type named Phone_List_type as an array of size three of the type phone_type.

Place screenshot here:

  • Modify the table STUDENT1 by adding the attribute PHONE2 is of data type Phone_List_type.

Place screenshot here:

  • Populate the new PHONE2 column:  Use ‘001’ for the value of COUNTRY_CODE. Use the values of AREA_CODE, PHONE_NUMBER from PHONE. The following SQL will populate the PHONE2 with one of phone number.

UPDATE STUDENT1 S SET S.PHONE2 = (phone_list_type(phone_type((‘001’), (substr(,1,3)), (substr(,5,8)))));

Place screenshot here:

  • Drop the column PHONE and rename PHONE2 as PHONE.

Place screenshot here:

  • Write a query to display the values of the attributes student_ID and phone.

select s.student_ID, v.* from student1 s, table ( v;

Place screenshot here:

  • Create a type named course_T that has attributes named COURSE_NO, DESCRIPTION, ENROLL_DATE and FINAL_GRADE.

create or replace type course_T as object(Course_No number(8,0), Description varchar2(50), Enroll_Date date, Final_Grade number(3,0));

Place screenshot here:

  1. Create an object table named course_Table_T as a table of course_T .

create or replace type course_Table_T is table of course_T;

Place screenshot here:

  1. Create a table named transcript that contains the student_id and a column whose type is the table course_Table_T.

create table transcript(Student_ID number(8,0), Transcript_attrib course_Table_T) nested table Transcript_attrib store as Transcript_tab;

Place screenshot here:

  1. Determine what courses have grades and update the transcript table accordingly.

/* Using the following SQL, I determined the students that successfully completed the course. I also obtained the variables needed to populate the nested table and they included student_ID, course_no, Description, Enroll_Date, Final_Grade*/

select e.student_id, c.course_no, c.Description, e.enroll_date, e.final_grade from course c inner join section sc on c.course_NO = sc.course_NO inner join enrollment e on sc.section_ID = e.section_ID inner join student1 s on e.student_id=s.student_id where s.student_id =102 and e.final_grade is not null;

/* The above query yielded only data from only one student who had successfully completed the course. This student’s information was inserted in the nested table as follows */

insert into transcript values(102, course_Table_T(Course_T(25,’Intro to Programming’,’30-JAN-07′,92)));

–The following SQL statement retrieves data from transcript

select t.student_ID, c.* from transcript t, table(t.transcript_attrib) c;                      

Place screenshots here:

  1. Create and populate a table named Current_Enrollment that contains the student_id and a column whose type is the table course_Table_T. Include only those courses with no final grade.


      course_tbl course_table_t;

      counter number;


      FOR i IN (SELECT s.student_id FROM student1 s)


          course_tbl := course_table_t();

          counter := 1;

          FOR k IN (SELECT course_t(c.course_no, c.Description, e.enroll_date, e.final_grade) AS course_temp

                      FROM course c

                      INNER JOIN section sc ON c.course_NO = sc.course_NO

                      INNER JOIN enrollment e ON sc.section_ID = e.section_ID

                      INNER JOIN student1 s ON e.student_id = s.student_id

                      WHERE e.final_grade IS NULL AND e.student_id = i.student_id)



              course_tbl(counter) := k.course_temp;

              counter := counter + 1;

          END LOOP;

          INSERT INTO current_enrollment VALUES(i.student_id, course_tbl);

      END LOOP;



SELECT c.student_ID, t.* FROM current_enrollment c, table(c.current_enrollment_attrib) t;

Place screenshots here:

  1. The attribute PHONE is of type PHONE_LIST TYPE where PHONE_LIST_TYPE  is a VARRAY(3. There is just one phone number in the VARRAY. Modify the database such that the attribute PHONE contains three phone numbers: A primary phone, a home phone number, and a mobile number.


    phone_list phone_list_type;


    FOR i IN (SELECT s.student_id FROM student1 s)


        SELECT INTO phone_list FROM student1 s WHERE s.student_id = i.student_id;


        phone_list (2) := phone_type((‘001’), (‘222’), (‘555-1111’));


        phone_list (3) := phone_type((‘001’), (‘333’), (‘555-4444’));

        UPDATE student1 s SET = phone_list WHERE s.student_id = i.student_id;




SELECT s.student_id, p.* FROM student1 s, table( p;

Place screenshots here:

  1. Describe the logic of the PL/SQL statements in tasks 13 and 14..  

Figure 1 Student Schema

Figure 2

Resources from UMGC Library

Chapters 14 and 15 cover topics for this assignment.

Code Samples for Chapters 14 and 15 may be found at

A zip file with the code samples for chapters 14 and 15 are contained in the zip file that is embedded in this document:

Other Resources:

A Sample Application using Object-Relational Features

Using PL/SQL With Object Types

Introduction to Object-Relational Mapping

Adding Custom Datatypes to Oracle SQL Developer Data Modeler (SDDM)

Object-Relational Features of Oracle

Object Relational Database

Viewing Complex Types in Oracle SQL Developer Data Modeler

PL/SQL with Object Types

Nested Table Example

PL/SQL nested table tips

Object Views and Nested Tables

Another Nested Table Example

A pdf of the Student Database Schema is embedded in this document:

All papers are written by ENL (US, UK, AUSTRALIA) writers with vast experience in the field. We perform a quality assessment on all orders before submitting them.

Do you have an urgent order?  We have more than enough writers who will ensure that your order is delivered on time. 

We provide plagiarism reports for all our custom written papers. All papers are written from scratch.

24/7 Customer Support

Contact us anytime, any day, via any means if you need any help. You can use the Live Chat, email, or our provided phone number anytime.

We will not disclose the nature of our services or any information you provide to a third party.

Assignment Help Services
Money-Back Guarantee

Get your money back if your paper is not delivered on time or if your instructions are not followed.

We Guarantee the Best Grades
Assignment Help Services