Create VARRAY in Oracle - Exercise

Create Oracle VARRAY

Objective: Create a VARRAY.

Scoring

You will receive 10 points for this exercise. The exercise is auto-scored. When you have completed the exercise, click the Submit button to receive full credit.

Background | Overview

You have been asked to design a STUDENT table for a university. The STUDENT table must store:

student_name
student_no
student_address
Up to five college test scores (for example, ACT or SAT).

Each test score must store test_name, test_date, and test_score. Model the test scores as a VARRAY column in the STUDENT table.

Instructions

Using the template below, do the following:

Create an object type that represents one test score row (test_name, test_date, test_score).
Create a VARRAY type with a maximum size of 5 using that object type.
Create the STUDENT table and include a VARRAY column to hold the test scores.

-- Step 1: Object type for one test score
CREATE TYPE test_score_ot AS OBJECT (
  test_name  VARCHAR2(20),
  test_date  DATE,
  test_score NUMBER(3)
);
/

-- Step 2: VARRAY type (up to five test scores)
CREATE TYPE test_score_va AS VARRAY(5) OF test_score_ot;
/

-- Step 3: STUDENT table that stores the VARRAY column
CREATE TABLE student (
  student_no      NUMBER       PRIMARY KEY,
  student_name    VARCHAR2(60) NOT NULL,
  student_address VARCHAR2(120),
  test_scores     test_score_va
);