Course
Oracle SQL Advanced
Course Duration
2 Days
The Oracle SQL Advanced course is designed to give delegates 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.
Private Courses
This course is available as a private event. A private event can be held at one of our venues, your location, or somewhere else if you prefer.
Prerequisites
-
- Delegates should have attended the Oracle SQL course or have a similar level of experience.
Objectives
-
- To provide the skills needed to use the more advanced features of Oracle SQL.
Target Audience
-
- Anyone who needs to use the more advanced features of SQL to improve their productivity in querying and updating an Oracle database.
Skills Gained
-
- 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
- The delegate will learn and acquire skills as follows:
What's included
-
- Comprehensive course materials and exercises
Course Content
-
DAY 1Session 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 2Session 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
Related Courses
Oracle PL-SQL The Oracle PL/SQL course is designed to introduce Oracle DBAs, software development personnel and database support staff to the primary features and constructs of the PL/SQL Programming Language.
Oracle PL-SQL Stored Program Units The Oracle PL/SQL Stored Program Units course provides practical experience in developing and writing triggers, functions, procedures and packages. It also introduces some of the Oracle-supplied packages.
Oracle SQL Fundamentals The Oracle SQL Fundamentals course is designed to give delegates practical experience in writing Oracle SQL statements and scripts. A wide range of SQL functions and data manipulation statements are introduced.
Your SQL course can be delivered online or in-person. You can choose your learning path and delivery style. Learn in-person or online with our virtual classrooms. Your course can be delivered online, at your office or at one of our training venues, it's your choice. Small class sizes. Course materials included. Flexible course content. Expert trainers and tried & tested training methods. Course materials available online using your personal training account.
You can master SQL, please contact us to book a place or request a quote.
-
Course Options
Please contact us to discuss options for this coursePrivatePrivateFlexible content, flexible date, flexible location, variable length, private to your organisation - Courses can be held at one of our training venues or at another location convenient to you. Training can be set over a number of days or weeks to allow for practice time between training sessions.Public ScheduledPublic ScheduledFixed content, fixed date, fixed location, fixed length, multiple organisations - All you need to do is choose which course you would like to attend and contact us to book!VirtualVirtualThese courses can be attended from anywhere.
- Virtual Training
IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:4/27/2024 1:36:47 PM
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:4/27/2024 1:36:47 PM