Oracle SQL Advanced (STA_OSA)

Oracle SQL Advanced Training Course

Course Contents - DAY 1

Course Introduction

• • Administration and Course Materials
• • Course Structure and Agenda
• • Delegate and Trainer Introductions

Session 1: RETRIEVE DATA USING SUBQUERIES

• • Correlated Subqueries
• • In-Line Views
• • The Exists Operator
• • The WITH Clause
• • Multi-Column Subqueries
• • Insert and Update Using a Query
• • Correlated Update and Delete

Session 2: HIERARCHICAL QUERIES

• • Hierarchical data
• • The START WITH and CONNECT BY clauses
• • The LEVEL pseudo-column
• • Sequencing the output
• • Eliminating nodes and branches

Session 3: ENHANCED GROUPING FEATURES

• • Review of basic grouping concepts
• • The ROLLUP and CUBE extensions
• • The GROUPING SETS extension
• • Using the GROUPING and GROUPING_ID functions

Session 4: ANALYTIC QUERIES

• • Ranking functions
• • The NTILE and WIDTH_BUCKET functions
• • Analytic Aggregate functions
• • The FIRST and LAST Value functions
• • The LEAG and LAG Functions
• • The LISTAGG Function

Session 5: MANAGE LARGE DATA SETS

• • Multi-table Inserts
• • Conditional and Unconditional Inserts
• • Merging Data into a Table
• • Table and View Based Merge
• • A Sub Query Based Merge

Oracle SQL Advanced Training Course

Course Contents - DAY 2

Session 6: FLASHBACK TECHNOLOGY

• • Flashback Query
• • The AS Clause
• • Flashback Table
• • Flashback Drop

Session 7: REGULAR EXPRESSION SUPPORT

• • Regular Expression Notation
• • Character matching
• • Repetition operators
• • Sub expression grouping
• • Regular expression functions

Session 8: VIRTUAL COLUMNS AND EXTERNAL TABLES

• • Virtual columns
• • Creating and Using External Tables

Session 9: DATE,TIME AND TIMEZONE SUPPORT

• • Date,Timestamp and Interval datatypes
• • Handling dates and times
• • Handling intervals
• • Date,timestamp and interval functions and literals
• • Related NLS parameters

Session 10: ACCESS CONTROL

• • System Privileges and Roles
• • Create Users
• • Alter User Details
• • Grant and Revoke Object Privileges

Session 11:MANAGE OBJECTS WITH THE DATA DICTIONARY

• • The Data Dictionary
• • Useful Data Dictionary Tables
• • Using the Data Dictionary