Oracle 9i Advanced SQL

This hands-on course provides the experienced Oracle developer with the advanced SQL skills necessary to design and code complex queries.   Students will learn to provide OLAP function by incorporating analytic functions, and group by clause enhancements.

 

Class Agenda:

  • DECODE function and CASE expression
  • Pivoting tables and matrix reports
  • Dynamic SQL
  • Subquery review:
    • Single row subqueries
    • Multiple row subqueries
    • Multiple column subqueries
    • Correlated subqueries
    • EXISTS and NOT EXISTS operators
    • Subqueries in the SELECT, FROM and ORDER BY clauses
  • Top-N or Bottom-N queries (Ranking)
  • OLAP enhancements to the GROUP BY clause
  • Analytic functions
  • JOIN review
  • ANSI standard JOIN syntax
  • CONNECT BY clause to retrieve hierarchical information
  • SET operator review
  • Oracle 9i datetime datatypes
  • Other Oracle 9i new features
    • Flashback queries
    • External tables
    • Merge statement

Prerequisites:

  • Oracle 9i SQL Fundamentals or equivalent experience

Audience:

  • Oracle application developers

Course Benefits:

  • Students will expand their knowledge of basic Oracle SQL and be able to design and code complex queries.  Students will learn to incorporate many advanced techniques such as the data warehouse functions CUBE and ROLLUP, DECODE, ANSI-compliant inner and outer joins, CONNECT BY (hierarchical queries), SET operations, correlated subqueries and other advanced subquery techniques.  Orancle 9i features such as flashback query, external tables and MERGE will also be covered.

Materials Furnished:

  • Student Manual

Recommended Follow-On Course:

  • Oracle 9i PL/SQL Fundamentals
  • Oracle 9i Advanced PL/SQL
  • Oracle 9i Performance Tuning for Developers

Course Length: Two Days

List Price: $990