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 (5×3=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)

  • Leave a Comment

    Your email address will not be published.

    This site uses Akismet to reduce spam. Learn how your comment data is processed.