Oracle SQL Advanced

Course Overview

Prerequisites

• • Delegates should have attended the Oracle SQL course or have a similar level of experience.

Target Audience for this Course

• • Anyone who needs to use the more advanced features of SQL to improve their productivity in querying and updating an Oracle database.

Skills Gained from this Training

• • The delegate will learn and acquire skills as follows: Retrieving Data Using Correlated Subqueries, In-Line Views and Multi-Column Subqueries Using the WITH Clause Inserting and Updating Rows Using Complex Subqueries Creating Hierarchical Queries Creating Analytic Queries Using ROLLUP, CUBE and GROUPING SETS Group By Enhancements Implementing the GROUPING and GROUPING_ID Functions Managing Large Data Sets Inserting Data Conditionally and Unconditionally Merging Rows in a Table Creating TIMESTAMP and INTERVAL Datatypes Using TIMESTAMP, TIMEZONE and INTERVAL Functions and Literals Using Regular Expressions and Implementing Related Functions Managing Objects with the Data Dictionary Managing Schema Objects Performing Flashback Operations Creating and Using External Tables Controlling User Access to Database Objects

Course Content

• DAY 1

  Session 1: ACCESS CONTROL
  System Privileges and Roles
  Create Users
  Alter User Details
  Grant and Revoke Object Privileges
  
  Session 2: 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 3: HIERARCHICAL QUERIES
  Hierarchical data
  The START WITH and CONNECT BY clauses
  The LEVEL pseudo-column
  Sequencing the output
  Eliminating nodes and branches
  
  Session 4: 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 5: 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 6: 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
  

• DAY 2

  Session 7: FLASHBACK TECHNOLOGY Flashback Query The AS Clause Flashback Table Flashback Drop Session 8: REGULAR EXPRESSION SUPPORT Regular Expression Notation Character matching Repetition operators Sub expression grouping Regular expression functions Session 9:MANAGE OBJECTS WITH THE DATA DICTIONARY The Data Dictionary Useful Data Dictionary Tables Using the Data Dictionary Session 10: ADVANCED SCHEMA MANAGEMENT Virtual columns Invisible columns Default values for table columns Function Based Indexes Creating and Using External Tables Session 11: 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

Included with this course

• • Comprehensive course materials and exercises