Oracle SQL Advanced

Course Contents - DAY 1

Course Introduction

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

Session 1: RETRIEVE DATA USING SUBQUERIES

• ANY, ALL or SOME Operators
• 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: 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

Session 5: FLASHBACK TECHNOLOGY

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

Session 6: 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 7: REGULAR EXPRESSION SUPPORT

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

Session 8: MANAGE OBJECTS WITH THE DATA DICTIONARY

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

Session 9: MANAGE SCHEMA OBJECTS

• Column Operations
• Constraint Operations
• Creating and Dropping Indexes
• B-Tree Indexes
• Function Based Indexes
• Descending Indexes
• Creating and Using External Tables

Session 10: ACCESS CONTROL

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