Oracle Certification Notes: Part XI

This is a continuation of my notes as I study for the Oracle Certified Associate credential:

Grouping Functions

COUNT(*) is the same as COUNT(ALL column)

Group functions ignore NULL values unless otherwise handled with NVL functions; As an example, this concept is important when using AVG()

Statistical group functions include VARIANCE() and STDDEV()

Group functions may be nested no more than two levels deep: G1(G2(column))

HAVING clause may only be used when a GROUP BY is also used

HAVING clause may contain multiple restrictions by use the AND, OR, & NOT conditions

Table Joins: Natural, Equi, Nonequi, and Self Joins

Equijoin: Matching a row from one table to one or more rows on another table based on equality operators

NATURAL JOIN can be used when the columns of the joining tables have the exact same column name. There are three methods.

As a natural join example: SELECT * FROM TABLE1 NATURAL JOIN TABLE2; is the same as SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.column1 = TABLES2.column1;

Another natural join example: SELECT * FROM TABLE1 JOIN TABLE2 USING (COLUMN1);

Another natural example: SELECT TABLE1.* FROM TABLE1 JOIN TABLE2 ON (TABLE1.column1=TABLE2.column1);

Nonequijoin: Matching a row from one table to one or more rows on another table based on a range of inequality operators

Self-join: Matching a row from one table to one or more rows (usually in a hierarchical manner) on itself

All of the above are also considered to be Inner Joins

Table Joins: Cartesian and Outer Joins

Cartesian join: Also known as a cross-join. Where one row on a table matches to every row on another table. Number of rows returned equals TABLE1 count times TABLE2 count (5x3=15).

A cartesian join example: SELECT COUNT(*) FROM TABLE1 CROSS JOIN TABLE2;

A Cross Join may not contain an ON clause

A cartesian join occurs when no joins or fewer than N-1 (where N=number of tables) are specified in a given query

Outer join: Retrieving orphan records (or one-sided rows) from a table. Can use RIGHT OUTER JOIN ON, LEFT OUTER JOIN ON, or FULL OUTER JOIN ON

As a traditional Oracle example: SELECT TABLE1.* FROM TABLE1 LEFT OUTER JOIN TABLE2 ON (TABLE1.column1=TABLE2.column1); retrieves additional rows from TABLE1 that do not match the join criteria.

As an Oracle example: SELECT TABLE1.* FROM TABLE1, TABLE2 WHERE TABLE1.column1=TABLE2.column1 (+); retrieves additional rows from TABLE1 that do not match the join criteria.

The (+) asks for the additional rows that do match the join criteria on the opposite side of the equals sign

Posted via Woodland Blog (http://dramse01.blogspot.com)