Intermediate

7-10.

When these commands are executed in SQL*Plus or a similar interface, what is the output?

CREATE TYPE temperature_t AS TABLE OF NUMBER;
/

CREATE TABLE hourly_temperatures
(temperature_date DATE,
 temperatures temperature_t)
NESTED TABLE temperatures STORE AS temperature_store;

SELECT COUNT(*)
  FROM hourly_temperatures
/

SELECT COUNT(*)
  FROM temperature_store
/

7-11.

Assuming the same types and declarations shown in 7-10, what happens when a user issues the following SELECT statement?

SELECT COUNT(*)
  FROM temperature_store
/

7-12.

Knowitall Andthensome passed his MCSE exam by answering question number 1 as 10, question 2 as 20, question 3 as 30, question 4 as 40, and question 5 as 50. Given the following type definitions, create a single SQL statement that inserts Knowitall’s responses into the student_answers table:

CREATE TYPE answer_t AS OBJECT ( question_no NUMBER(10),
                                 answer_no   NUMBER(10));

CREATE TYPE answer_list_t AS TABLE OF answer_t;

CREATE TABLE student_answers (
   student_id INTEGER,
   exam_date DATE,
   exam_id INTEGER,
   student_answer_values answer_list_t)
NESTED TABLE student_answer_values
STORE AS student_answer_store;

7-13.

Now that there is an entry in the student_answers, what syntax is required to query all fields in the student_answers table? All fields in the nested table?

7-14.

Continuing with the student_answers example, what SQL syntax is required to add another answer?

7-15.

What is the SQL syntax to delete the entry just added?

7-16.

An Oracle error is lurking within ...

Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial