Before going through the Oracle9i SQL New Features Hands-On experiences, lets take note of the following important definitions to remember:  

The Oracle9i ANSI standard JOIN syntax

You can use Oracle9i ANSI standard JOIN syntax to join the contents of two or more tables together in a single result according to the following syntax.

Syntax:

SELECT col1, col2

    FROM table1 JOIN table2

    ON condition;

The ANSI standard NATURAL JOIN syntax

A natural join is a join between two or more tables where Oracle joins the tables according to the column(s) in the two or more tables sharing the same name with the following syntax.

Syntax:

SELECT col1, col2

     FROM table1 NATURAL JOIN table2;

The USING clause

You can use Oracle9i ANSI standard JOIN syntax to join the contents of two or more tables together in a single result according to the columns in the two tables sharing the same name and be used in the USING clause with the following syntax.

Syntax:

SELECT col1, col2

     FROM table1 JOIN table2

     USING (col);

The ANSI standard CROSS JOIN syntax

A cross-join is produced when you use the CROSS keyword in your ANSI/ISO –compliant join query. You use it when you want to retrieve a Cartesian product.

Syntax:

SELECT col1, col2

     FROM table1 CROSS JOIN table2;

The OUTER JOIN clause

A OUTER JOIN is a join between two tables where you want to see information from tables even when no corresponding records exist in the common column. You can have RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN.

Syntax:

SELECT col1, col2

     FROM table1 RIGHT/LEFT/FULL OUTER JOIN table2;

RIGHT OUTER JOIN

A RIGHT OUTER JOIN is a join between two tables where you want to see information from table on the right side even when no corresponding records exist in the common column.

Syntax:

SELECT col1, col2

     FROM table1 RIGHT OUTER JOIN table2;

LEFT OUTER JOIN

A LEFT OUTER JOIN is a join between two tables where you want to see information from table on the left side even when no corresponding records exist in the common column.

Syntax:

SELECT col1, col2

     FROM table1 LEFT OUTER JOIN table2;

FULL OUTER JOIN

A FULL OUTER JOIN is a join between two tables where you want to see information from both tables on the left and right sides even when no corresponding records exist in the common column.

Syntax:

SELECT col1, col2

     FROM table1 FULL OUTER JOIN table2;

The (WITH name AS) statement

Oracle9i provides you with the WITH clause that lets you factor out the sub-query, give it a name, then reference that name multiple times within the original complex query.

The (inline view)

A sub-query that appears in the FROM clause is called an inline view. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later.

The MERGE statement

Oracle9i provides you with the MERGE statement so that you can identify a table into which you would like to update data in an existing row or add new data if the row does not already exist.

Materialized View

Unlike an ordinary view, which only contains an SQL statement, a materialized view contains the rows of data resulting from an SQL query against one or more base tables. The materialized view can be set up to automatically keep itself in synch with those base tables.

Materialized view log

Whenever a change is made to one of the underlying base tables, the database stores a log on each change.

Using DBMS_SNAPSHOT package

You can use the REFRESH procedure of the DBMS_SNAPSHOT package to refresh periodically a snapshot manually.

UNION

The UNION set operator combines the results of two queries into a single result with no record duplication.

INTERSECT

The INTERSECT set operator returns all the rows contained in both tables.

MINUS

The MINUS set operator returns all the rows in one table minus the rows contained in other table.

Tablespace

A tablespace is a logical database structure that is designed to store other logical database structures. Oracle sees a tablespace as a large area of space into which Oracle can place new objects. Space in tablespace is allocated in segments.

Partitioned table

Partitioned tables are just like regular tables except for an important small feature-they enable you to reference the individual segments that might support larger tables directly.

The ROLLUP function

It is simple extension to the SELECT statement’s GROUP BY clause. It creates subtotals at any level of aggregation needed, from the most detailed up to a grand total.

The CUBE function

It is simple extension to the SELECT statement’s GROUP BY clause. It calculates subtotals for all the combinations of a group of dimensions. It also calculates a grand total.

JAVA

JAVA is a language that is portable, due to the fact that it is run within your environment. This environment may be a web browser, a database, or an application server.

Nested Table

If an object is in an object, it is a nested object. A nested table is a table that is nested in an other table.

The “THE” sub-query

The "THE" sub-query is used to identify the nested table to insert into.  Note only one row may be inserted into the nested table at once using this method-as would be the case if you were inserting into any table.

The REF Cursor

REF cursors hold cursors in the same way that VARCHAR2 variables hold strings. This is an added feature that comes with PL/SQL v2.2.  A REF cursor allows a cursor to be opened on the server and passed to the client as a unit rather than one row at a time. One can use a Ref cursor as a target of assignments and can be passed as parameters to the Program Units. Ref cursors are opened with an OPEN FOR statement and in all other ways, they are the same as regular cursors.

Table of Records

A table of records is a new feature added in PL/SQL v2.3. It is the equivalent of a database table in memory.  If you structure the PL/SQL table of records with a primary key (an index) you can have array-like access to the rows. 

Table of records differ from arrays in that they are not bound by a fixed lower or higher limit.  Nor do they require consecutive index numbers as arrays do. Consult a PL/SQL reference manual (version 2.3 or higher) for further explanation. There are three steps involved in creating a table of records.

Now, you should first read your case study, and try to solve the questions.  Then play the Hands-On training movie until you become familiar with the subject.  For more information about the subject, you are encouraged to read from a wide selection of available books.

Good Luck.