Oracle 11g New and Advanced SQL Features For Developers

Course:  ORSQLA
Duration:  3 Days
Level:  III
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.

« Hide The Details
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.

« Hide The Details
Related Courses
Code Course Title Duration Level
Oracle 11g/12c Developer: Introduction to PL/SQL
2 Days
Oracle 11g and 12c Foundations: SQL and SQL*Plus
5 Days
Oracle 11g/12c Essentials: SQL, SQL*Plus and PL/SQL
5 Days
Oracle 11g/12c Developer: PL/SQL Complete
5 Days
Oracle 11g/12c Developer: Intermediate PL/SQL
3 Days
Oracle 11g Advanced PL/SQL
3 Days

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.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]