Verhoef Training USA

Training Courses and On-Line Classes Preferred By IT Pros

Visit us at:   http://www.verhoef-training.com

103 Hillside Ave., PO Box 1515
West Caldwell, NJ 07006
Main Office:  800.631.0410
Sales Office:  800.533.3893

Oracle 11g New and Advanced SQL Features For Developers

Course:   ORSQLA
Duration:   3 Days
Level:   III
On our website at:   http://www.verhoef-training.com/courses/ORSQLA.html
 
Course Summary

This course presents some of the advanced features and capabilities of the Oracle 11g database. The content is geared towards senior, experienced SQL developers and database administrators who wish to make the most of the latest, most powerful elements of Oracle 11g.

Oracle?s powerful and multi-faceted Flashback technology which provides developer self-service recovery from erroneous commits is described along with alternatives for backing out bogus data changes. The potent elements of the latest SQL enhancements are covered along with extensive coverage of the SQL92 join syntax. Oracle?s recent extensions to it?s hierarchical SQL processing syntax are presented. Options and uses for new and complex data types and support for persistent object storage is offered. The many types of indexing made available in recent Oracle releases are compared. Alternative table organization methods are reviewed.

Throughout the course there is comprehensive coverage of Oracle?s data dictionary to review and monitor the usage of features.

Topics Covered In This Course

Oracle 11g concepts

  • Grid computing
  • Real application clusters (RAC)
  • The Oracle application server

The PetSaver database

Flashback technology

  • Flashback concepts
  • Flashback query
  • Flashback table
  • Flashback drop
  • Flashback versions
  • Flashback transaction

Advanced data types

  • Timestamps and intervals
  • The CAST function
  • Collections

Working with objects

  • Defining a type
  • Instantiating and storing an object
  • Manipulating objects using SQL

Working with LOBs

  • Understanding LOBs
  • Creating LOBs
  • Manipulating LOBs with DBMS_LOB
  • LOBs and NULL values

SQL92 join syntax

  • NATURAL joins
  • CROSS joins
  • INNER joins
  • OUTER joins

Advanced multi-table SELECT statements

  • Sub-query predicates
  • Correlated sub-queries
  • Using set operators

Advanced hierarchical queries

  • Hierarchical data concepts
  • Querying hierarchical data
  • Hierarchical pseudocolumns
  • Resolving hierarchical data errors

Enhancements to SQL

  • New NULL functions
  • The CASE statement
  • The MERGE statement
  • Multi-table INSERTs

Materialized views

  • Creating materialized views
  • Refresh strategies

Alternative index types

  • B-tree index review
  • Monitoring index usage
  • Bitmap indexes
  • Function-based indexes
  • Deterministic functions
  • Reverse key indexes
  • Bitmap join indexes
Who Should Take This Course

This course is appropriate for experienced Oracle SQL developers and database administrators.

Recommended Prerequisites

Skill with GUI interfaces, and 12 months of experience writing SQL

Note: Attendees without a strong SQL background will probably not have a successful class experience.

Training Style

Lecture with frequent hands on labs.

Related Courses
Code Course Title Duration Level
OR11D1
Oracle 11g/12c Developer: Introduction to PL/SQL
2 Days
I
Details
OR11F
Oracle 11g and 12c Foundations: SQL and SQL*Plus
5 Days
I
Details
OR11E
Oracle 11g/12c Essentials: SQL, SQL*Plus and PL/SQL
5 Days
I
Details
OR11D
Oracle 11g/12c Developer: PL/SQL Complete
5 Days
I
Details
OR11D2
Oracle 11g/12c Developer: Intermediate PL/SQL
3 Days
II
Details
ORPLSA
Oracle 11g Advanced PL/SQL
3 Days
III
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.