ONLINE CLASSES

Advanced SQL Queries for Oracle10g and 11g Databases:
14-Hour Online Class and Hands-On Workshop.

Learn the advanced SQL skills necessary to design and code complex queries against Oracle databases. Design, code and test the most efficient query to solve a given business problem. Fully updated for Oracle 11g.

Share |

Instructor: Geoffrey Wiland (more about this instructor)

Cost: $1,000

Upcoming Class Schedule

Dates Times (ET) Location Availability
March 22 - 23 9am-5pm EDT Online Enroll Now

Enrollment includes:

  • 250+ page coursebook (hardcopy)
  • Headset
  • Workshop solutions and sample code
  • Review recordings of your class sessions online, any time*what is this?
  • Certificate of Completion what is this?
  • 100% Satisfaction Guarantee what is this?
  • Bring a colleague and receive a $25 Amazon.com gift certificate what is this?

Watch instructor Geoff Wiland's personal introduction to this course.

  1. Oracle DECODE and CASE - Conditional Logic in SQL
    • Introduction to Oracle DECODE
    • Examples
    • Oracle DECODE and Range Comparisons
    • The Oracle CASE Expression
    • Oracle CASE Syntax
    • CASE Examples
    • Workshop
  2. Set Operations
    • UNION, INTERSECT and MINUS Operators
    • UNION Example
    • Key UNION Concepts
    • UNION ALL Example
    • INTERSECT Operation
    • MINUS Operation
    • Example: Compare Schemas
    • Example: Compare Tables
    • Example: Compound Set Operations
    • Workshop
  3. Subqueries
    • Oracle Subquery Overview
    • Subquery Examples
    • Restrictions
    • Subquery Gotcha
    • Correlated Subqueries
    • The Need for NOT EXISTS
    • The EXISTS Operator
    • Oracle Top-N Queries - Inline Views
    • Oracle 9i Extended Subquery Support
    • Oracle Subquery Factoring - The WITH Clause
    • Workshop
  4. Advanced Joins
    • Join Terminology Review
    • 3,4,5-Way Inner Joins
    • Join and GROUP BY
    • Self-Joins
    • Cartesian Product
    • Outer Joins
    • Workshop
    • Oracle ANSI Compliant Joins
    • Natural Join
    • Oracle USING Clause
    • Oracle ON Clause
    • Oracle ANSI Outer Joins
    • Oracle ANSI Full Outer Joins
    • Oracle ANSI Cross Join
    • Workshop
    • Joining to Views
    • Join Tuning Tips
    • Oracle Partition Outer Join
    • Workshop
  5. Coding Queries on Partitioned Data
    • Oracle Partition Concepts
    • Partition-Independent Queries
    • Partition Pruning
    • Coding Partition-Dependent Queries in Oracle
    • Workshop
  6. Grouping Data
    • Oracle Aggregate Function Overview
    • GROUP BY Example
    • Syntax Notes
    • Grouping Multiple Columns
    • Golden GROUP BY Rule
    • The HAVING Clause
    • Oracle ROLLUP Operations
    • Oracle CUBE Operations
    • Oracle GROUPING Function
    • Oracle GROUPING Example
    • Oracle GROUPING with DECODE
    • Oracle GROUPING in HAVING
    • Oracle GROUPING SETS
    • Workshop
  7. Oracle Analytic Functions
    • Introduction
    • What Do They Do?
    • Getting Started with Oracle Analytic Functions
    • Oracle Partition Clause
    • Oracle Order-By Clause
    • Oracle Windowing Clause
    • Oracle Row Windows
    • Oracle Range Windows
    • Oracle Range Windows: BETWEEN
    • Oracle Range Windows: INTERVAL
    • Oracle Ranking Functions
    • Oracle Top-N Queries
    • Oracle LAG and LEAD Functions
    • Closing Thoughts
    • List of Analytic Functions
    • Resources
    • Workshop
  8. Model Queries (Oracle10g)
    • Oracle10g MODEL Clause Concepts
    • Oracle10g MODEL Clause Components and Clauses
    • Oracle10g MODEL Clause Examples
    • Workshop
  9. Oracle CONNECT BY - Hierarchical Queries
    • Introduction
    • Oracle CONNECT BY Example
    • LEVEL with LPAD
    • Adding WHERE Clause
    • Sort by LEVEL
    • Oracle 9i SIBLINGS Sorts
    • Oracle 9i Hierarchy Path
    • New Oracle 10g/11g Pseudocolumns
    • Workshop
  10. Using Regular Expressions in Oracle SQL
    • Searching with REGEXP_LIKE
    • REGEXP_LIKE Versus LIKE
    • Regular Expression Functions (e.g. REGEXP_REPLACE)
    • Basic Elements of Expressions
    • Using Backreferences
    • Examples
    • Workshop
  11. Oracle Date and Time (Temporal) Data
    • Scalar Function Review (New Oracle10g Functions)
    • Oracle Date Conversion Functions
    • Oracle TO_CHAR Examples
    • Oracle TO_DATE Examples
    • Oracle Date Arithmetic & Functions
    • Adding & Subtracting Days in Oracle SQL
    • Oracle ADD_MONTHS Function
    • Oracle LAST_DAY Function
    • Oracle TRUNC Function
    • Workshop
    • Introduction to Oracle Datetime Data
    • Oracle TIMESTAMP Data Type
    • Oracle TIMESTAMP WITH TIME ZONE Data Type
    • Oracle TIME STAMP WITH LOCAL TIME ZONE Data Type
    • Oracle Datetime Conversions
    • More Oracle Time Zone Functions
    • Workshop
  12. Oracle XML DB and XMLType
    • XML in the Oracle Database
    • XMLType in Oracle Tables
    • Inserting XML Data
    • Selecting XML Data
    • Oracle XML DB EXTRACT Function
    • Oracle XML DB EXTRACTVALUE Function
    • Updating Oracle XML Data
    • PL/SQL and XML
    • Other Oracle XML Features
    • XML DB Workshop
  13. Additional Oracle 10g/11g New SQL Features
    • Case Insensitive Search and Sort
    • Enclosing Quotes
    • Oracle MERGE Statement Enhancements
    • ORA_ROWSCN Pseudocolumn
    • Nested Table Enhancements
    • Temporary Table Enhancements
    • Aggregates in the RETURNING Clause
    • New Datatypes

Prerequisite courses or experience:

Some SQL and SQL*Plus experience is required. For example, you should be comfortable coding basic SQL SELECT statements, including the use of the WHERE clause with basic filters and simple joins. This can be obtained by attending SkillBuilders' Introduction to Oracle class.

Next classes:

Oracle10g SQL Tuning for Developers and DBAs; Oracle10g Database Administration.

"I liked the fact that you spread the course out over 4 afternoons."

—Telcordia

"I was impressed at how easy it was to configure my system for the database and the online class."

—Telcordia

Read reviews of all SkillBuilders classes.

4.8 out of 5

Student Rating