Table Modification   «Prev  Next»

Updating and Deleting Object Table Rows - Exercise

Write two DML statements

Objective: Use Oracle SQL to update an attribute inside an object and delete selected rows from an object table.

Background

Use the object-table concepts from Updating an Object Table and Deleting Rows from an Object Table. In CUSTOMER_OBJ_TABLE, FULL_ADDRESS is an object-valued attribute whose type is ADDRESS_TYPE. That object contains the string attribute STATE. In PRODUCT_OBJ_TABLE, CURRENT_INVENTORY_COUNT is numeric.

For this practice scenario, correct the state code from 'HI' to 'LA' for matching customers, then remove products whose inventory count is exactly 5. These are exercise rules, not general business rules for customer addresses or stock management.

Use the following complete starting datasets to determine the row counts. Only the attributes relevant to this exercise are shown. Assume initialized address objects, sufficient privileges, no concurrent changes, and no triggers or constraints that change or prevent the requested operations.

CUSTOMER_OBJ_TABLE: starting values
CUST_IDFULL_ADDRESS.STATE
1HI
2CA
3HI
4LA
PRODUCT_OBJ_TABLE: starting values
PRODUCT_IDCURRENT_INVENTORY_COUNT
115
128
135
140
155

No download is needed to write your answer. Running the statements requires a practice schema containing the object types, tables, and sample data; the tables above are a scenario, not an installation script.

Instructions

  1. Write an UPDATE statement that sets FULL_ADDRESS.STATE to 'LA' only where its current value is 'HI'. Use a table alias and dot notation.
  2. Write a DELETE statement that removes rows from PRODUCT_OBJ_TABLE only where CURRENT_INVENTORY_COUNT = 5. Keep the table itself.

Explain how your alias, attribute path, and WHERE clauses select the intended data. If you run the statements, use a disposable practice schema with autocommit disabled, inspect the changes, and roll back the practice transaction.

Answer these questions

  1. How many customer rows are updated, and which customer IDs are affected?
  2. How many product rows are deleted, and which product IDs are affected?

Hints

Use the path cot.full_address.state, where cot is the table alias, full_address is the stored object attribute, and state is an attribute inside it. ADDRESS_TYPE is the type name; it does not replace full_address in this path. Use straight single quotes around state codes and the numeric literal 5 for inventory.

Exercise scoring: 15-point self-assessment

  • 5 points: Correct UPDATE using the alias, nested attribute, new state value, and state filter.
  • 5 points: Correct DELETE using the product table and exact inventory filter.
  • 5 points: Correct counts and IDs (2 points), plus an explanation of dot notation and the two predicates (3 points).

After submitting, compare your work with the model answer. This page does not execute SQL or calculate a score.

Submitting your exercise

Enter both statements, the two row-count answers with IDs, and your explanation. Select Submit to view your response alongside the solution.