Home About Courses Schedule Services Webinars Contact Search

Oracle Advanced SQL

SEE SCHEDULE

Duration: 2 days

Method: Instructor led, Hands-on workshops

Price: $1125.00

Course Code: OR5451



Audience

Programmers, Analysts, Database Administrators, and Users who require advanced SQL techniques to access data in an Oracle database.

Description

This course is intended to provide experienced Oracle developers with the advanced SQL skills needed to effectively design and code complex queries and applications required to solve enterprise business problems. Coding examples and workshops serve to reinforce the material presented in class.

This course can be taught for any Oracle version.

Prerequisites

OR5450 Oracle SQL or equivalent experience

Topics

  1. Next Level Multi-table Operations
    • Joins and Join Methods
    • Subqueries
    • Subqueries with Multiple Columns
    • Correlated Subqueries
    • EXISTS Keyword
    • Inline SELECT
  2. Grouping Data
    • Aggregate Functions
    • Grouping Multiple Columns
    • HAVING Clause
    • ROLLUP Operations
    • Partial ROLLUP
    • CUBE Operations
    • GROUPING Function
    • GROUPING SETS
    • Cross-Tabulation Queries
    • PIVOT Operation
    • UNPIVOT Operation
  3. Analytic Functions
    • Introduction to Analytic Functions
    • Partition Clause
    • Order-By Clause
    • Windowing Clause
    • Ranking Functions
    • Top-N Queries
    • LAG and LEAD
  4. Hierarchical Queries
    • CONNECT BY Clause
    • START WITH Clause
    • LEVEL Pseudocolumn
    • Sort by LEVEL
    • SIBLINGS
    • SYS_CONNECT_BY_PATH
    • CONNECT_BY_ISLEAF
    • CONNECT_BY_ROOT
    • CONNECT_BY_ISCYCLE
    • NOCYCLE
  5. INSERT and MERGE
    • Multi-table Inserts
    • The MERGE Command
  6. Introduction to SQL Performance
    • SQL Behind the Scenes
    • Object Statistics
    • Using Explain Plan
  7. Flashback Query
    • Flashback Technology Overview
    • Flashback Queries
    • Using Flashback Queries
    • DBMS_FLASHBACK Package
    • Flashback Version Query
    • Flashback Transaction Query
    • Flashback Limitations