Deleting Nested-Table Elements - Exercise

Objective: Delete elements from a stored nested table while preserving its parent row, and distinguish a one-sale operation from clearing all sales' collections.

Example schema and prerequisites

Assume SALE_HEADER has a unique SALE_ID and a nested-table column DETAIL_NEST of SQL type DETAIL_TABLE. Its object elements have a numeric DETAIL_ID, unique within each sale. This identifier is an explicit assumption for this exercise. Sale 34 exists exactly once and its collection is initialized and contains detail 7. The schema permits the operations below.

You can write your answer without a database. To test it, use your assigned account and service in a disposable practice schema with these definitions and sufficient privileges. Disable autocommit, inspect the results, and use ROLLBACK to undo uncommitted practice changes.

Tasks

1. Write a DELETE that removes every element from sale 34's DETAIL_NEST, preserving the sale row and every other sale. Write a SELECT that counts the remaining elements for sale 34 and state the expected count.
2. As an independent variation on the original data, delete only detail 7 from sale 34. Explain the different roles of the inner and outer WHERE clauses.
3. Show a single statement that replaces DETAIL_NEST with an initialized empty collection for every SALE_HEADER row, preserving the parent rows. Explain why simply removing the parent predicate from the first statement is not a general solution.

Hints: Use TABLE() for piecewise nested-table DML and the empty DETAIL_TABLE() constructor for whole-collection replacement. Treat the variations independently; undo the main deletion before testing the single-detail variation.

10-point self-assessment

Submit your exercise

Submit your SQL, verification query, and explanations together. The result page displays a model answer; it does not execute SQL or calculate a score.