COURSE OBJECTIVE:
Course Objectives To provide the skills needed to use the more advanced features of Oracle SQL.
TARGET AUDIENCE:
Who will the Course Benefit? Anyone who needs to use the more advanced features of SQL to improve their productivity in querying and updating an Oracle database.
COURSE PREREQUISITES:
Requirements Delegates should have attended the Oracle SQL course or have a similar level of experience.
COURSE CONTENT:
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
FOLLOW ON COURSES:
Further Learning Oracle 19c SQL Performance Tuning Oracle PL/SQL Oracle Database 19c Administration