The Oracle9i ANSI standard
JOIN syntax vs. the original join syntax; The ANSI standard NATURAL JOIN
syntax; The ANSI standard CROSS JOIN syntax; The USING clause; The ON
clause; The OUTER JOIN clause; RIGHT OUTER JOIN vs. original Oracle
syntax; LEFT OUTER JOIN vs. original Oracle syntax; FULL OUTER JOIN
Introduction 08 (Oracle9i:
SQL New Features)
The (WITH SUMMARY AS)
statement; The use of multiple columns in the WHERE clause; The (inline
view); The MERGE statement
Hands-On 09 (Materialized
Views – The view that stores data)
Creating a materialized view
log; Listing the snapshot logs' view; MLOG$_EMP; Creating a materialized
view; Granting materialized view privilege; Listing the user's
materialized views; Executing the REFRESH procedure; Using DBMS_SNAPSHOT
package; Executing the REMOVE procedure; Listing the materialized view
in the job’s queue; Deleting the materialized view’s job; Dropping the
created Materialized view; Revoking the materialized view privilege
Hands-On 10 (The BREAK ON
clause)
REPHEADER; REPFOOTER; COLUMN
<col> HEADING <hdr> FORMAT a20; BREAK ON <col> SKIP n; COMPUTE SUM OF <col>
ON <col>; SPOOL; HOST; CLEAR BUFFER; CLEAR COLUMNS; CLEAR COMPUTE
Hands-On 11 (Troubleshooting
– UNION; INTERSET; and MINUS statements)
UNION; INTERSECT; MINUS
Hands-On 12 (Troubleshooting
Deleting duplicated records)
Creating a table with lots of
duplicated records; Writing a simple delete (DML) statement to remove
duplicated records; Verifying the removal of the duplicated records;
Dropping the duplicated table
Hands-On 13 (Data
Partitioning)
Creating the OS level
folders; Creating a tablespace; Granting the CREATE TABLESPACE; Granting
the DROP TABLESPACE; Creating a range-based partitioning table;
Inserting records into the partition table; Analyzing the partition
table; Listing the statistics on the partition data table; Compare a
regular table with a partition table; Listing the partition table;
Creating a local partition index; Analyzing the partition table index;
Listing the statistics on the index partition table; Dropping the
partition table and all of the tablespaces; Revoking the CREATE
TABLESPACE privilege; Removing the folders
Hands-On 14 (Data Warehousing
– ROLLUP and CUBE function)
ROLLUP; CUBE
Hands-On 15 (Using Native
Dynamic SQL)
Native Dynamic SQL
Hands-On 16 (JAVA Stream)
Checking the JAVA tool
installation; Checking the JAVA pool memory usage; Creating a java
class; Publishing the JAVA class; Running and testing the JAVA program;
Dropping the JAVA SOURCE; Dropping the FUNCTION
Hands-On 17 (Defining Objects
and Collection Types)
Creating an object type;
Creating a table type using object type; Listing the object types;
Creating a nested table using a table type; INSERT INTO tablename VALUES
(col; table_object_type(record_object_type())); Listing the nested
table; INSERT INTO THE (SELECT col… FROM tablename); SELECT
nested_table_id; col1; col2 FROM THE (SELECT col1 FROM tablename);
UPDATE THE(SELECT col1 FROM tablename); Adding a unique index to the
nested table; Dropping the nested table and types
Good Luck!