About this Course

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.

Audience Profile

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

Prerequisites

OR5450 Oracle SQL or equivalent experience.

Course Outline

1. Next Level Multi-Table Operations

• Joins and Join Methods
• Subqueries
• Subqueries and 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. Analytics 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
• Objects 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