sss

Home
/
Training
/
Courses
/
Oracle Courses
/
ORACLE SQL Advanced

Oracle Courses

Enliten IT partner with StayAhead Training to deliver an extensive range of 19c Oracle courses targeted at ensuring delegates, from programmers to administrators, can perform their job roles successfully. We’ll teach you how to administer, configure, secure, backup, manage storage and maintain databases. Oracle SQL reporting courses are also available.

Other, not so popular, Oracle training courses (e.g. Oracle e-Business Suite R12) are also available so if you don’t find what you are looking for in the list below then please contact us.

Note: All Oracle 19c workshops can be modified or tailored for delivery, at your own offices, anywhere within the UK.

ORACLE SQL Advanced

2 days

This Oracle SQL Advanced course is designed to give delegates practical practical experience in using some of the more advanced features of Oracle SQL. Additional datatypes and the use of advanced SQL functions and expressions are covered together with enhanced table handling features.

Delegate will practice:

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

Download PDF

What will you learn?

By the end of this Oracle SQL Advanced course, delegates will have the knowledge and skills needed to use the more advanced features of Oracle SQL.

Audience?

Anyone needing the more advanced features of SQL to improve their productivity in querying and updating an Oracle database.

Course Contents

ACCESS CONTROL

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

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

HIERARCHICAL QUERIES

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

ENHANCED GROUPING FEATURES

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

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

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

FLASHBACK TECHNOLOGY

Flashback Query
The AS Clause
Flashback Table
Flashback Drop

REGULAR EXPRESSION SUPPORT

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

MANAGE OBJECTS WITH THE DATA DICTIONARY

The Data Dictionary
Useful Data Dictionary Tables
Using the Data Dictionary

ADVANCED SCHEMA MANAGEMENT

Virtual columns
Invisible columns
Default values for table columns
Function Based Indexes
Creating and Using External Tables

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

Course Materials

Enliten IT will provide each delegate with a workbook and other useful reference materials where applicable.

“Very well explained – with enthusiasm and good understanding of the classes varying abilities. The day flew, such was the enjoyment.”Very well explained to a class of varying abilities

Additional Information

Enliten IT reserves the right to modify course content and materials as required in the interests of continuous course improvement, at any time, without notification. Note: This Oracle SQL Advanced course is run on a public schedule or can be taken as a private in-house company course UK wide. For pricing and availability just pick up the phone and call us on 0845 1085481.

Contact us to discuss your training needs...