Querying Object Tables
Object Oriented Terms
Collections Varrays Oracle
Querying Nested Tables
Querying Complex SQL Object
SQL Query Example
Using Oracle Objects
Querying Related Tables
Understanding Related Object Tables
Analyzing Dangling References
Writing deref Queries
Oracle deref Syntax
Creating PL/SQL Block
Querying Nested Tables
Flattening nested Table
Flattening nested Table Example
Simple varray Query
Query to Flatten varray
Query Flattens Nested Table
Procedure retrieves Nested Table
Varrays Nested Table Conclusion
Object Table Modification
Insert into Table
SQL Insert Rows
Write SQL to Update Object Table
Subquery in Update
Delete Rows from object Table
DML Statements Conclusion
Updating Rows Example
Modifying Nested Tables
Insert Rows into Nested Table
Delete Command for Nested varray
Updating Varray Rows
inserting Data in Nested Table(Example)
Varray insert Command
Deleting Object Table - Exercise
Write DML Statements
Write INSERT and UPDATE statements.
This exercise is worth a total of 15 points. You will receive up to 10 points for writing the two queries (five points for each query). You will receive up to five points for explaining the techniques that you used and why.
In this exercise, you will write two DML statements. First, update the records within the CUSTOMER_OBJ_TABLE table and set STATE within ADDRESS_TYPE to LA, where STATE is HI. Second, delete the records from the
PRODUCT_OBJ_TABLE table, where CURRENT_INVENTORY_COUNT = 5.
Developer: There are no additional files that the student needs to download for this exercise other than the database schema & data, which, I believe, will be part of the standard download.
For the first SQL statement, use dot notation to traverse to the attribute of the object type. The second SQL statement is a simple DELETE statement.
Now Answer this….
How many records are updated by the first SQL statement?
How many records are deleted by the second SQL statement?
To write the first SQL statement, use dot notation and table alias.
Submitting your exercise
After you have defined the SQL statements and added you@r explanation for the techniques that you used and why, place them within the text area below and click the
button to submit them. Remember that you must submit all your responses to this exercise at once.