O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required