SkillBuilders Welcome  |  Log in
All Oracle Classes

Advanced SQL Queries for Oracle 12c Databases:
3 Day Onsite or 21 Hour Online Hands-On Training

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 12c.

This course includes instruction on the Oracle Analytic Functions.

Free online training videos; free registration required: 
Learn to Use Oracle Analytic Functions.

Average Student Rating:

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

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

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

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

Coding Queries on Partitioned Data

  • Oracle Partition Concepts
  • Partition-Independent Queries
  • Partition Pruning
  • Coding Partition-Dependent Queries in Oracle
  • Workshop

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

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

Model Queries (Oracle10g)

  • Oracle10g MODEL Clause Concepts
  • Oracle10g MODEL Clause Components and Clauses
  • Oracle10g MODEL Clause Examples
  • Workshop

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

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

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

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

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
 
SkillBuilders Sample Video