Advanced Oracle SQL Training
Course
© 2007-2016
by Burleson Corporation
• Understand advanced Oracle SQL analytics
• Learn how to traverse complex hierarchical relationships
• Expand
your understanding of Oracle SQL to solve complex business queries
•
Download working examples of Oracle analytic functions
• See how to quickly format Oracle data
with XML for seamless publication
• Learn expert tips and tricks for writing
high-quality SQL
* See how initialization parameters
affect SQL optimization.
* Understand how to read
an execution plan.
* Learn the importance of schema statistics.
* Learn how to apply "global" changes to tune
many SQL statements at once.
* See how to locate missing indexes.
* See how to use the library cache to find
SQL opportunities.
* Understand how to prioritize your SQL
efforts.
|
|
Our advanced Oracle SQL
training is an intensive three-day, four-day, or five-day course
designed to provide Oracle professionals with an in-depth
understanding of Oracle SQL and how to formulate and tune
SQL statements for optimal performance. Burleson instructors offer decades of real world DBA
experience in Oracle features, and they will share their secrets in
this intense Oracle SQL training.
This SQL course is designed for
practicing Oracle DBA's and Systems Administrators professionals who have basic experience with SQL
statements. Prior experience with Oracle
is not required, but experience using SQL with a relational
database is highly desirable.
The goal of the SQL class is to
provide a comprehensive toolkit to allow the DBA to quickly locate and tune a
database workload.
|
This advanced SQL course was designed by Donald K.
Burleson, an acknowledged leader in Oracle database
administration. Author of more than 20 database books,
Burleson was chosen by Oracle Press to write five authorized
editions, including Oracle High-Performance SQL
. Burleson Corporation instructors offer decades of
real world DBA experience in Oracle features, and they will share their secrets
in this intense Oracle SQL training.
Learning
Objectives
By the end of this advanced SQL course the student
will be able to tune advanced SQL queries including correlated
subqueries and outer joins. The student will also become familiar
with all of the major SQL techniques for Oracle, including
global parameter and statistics changes, adding missing indexes and adjusting
optimizer statistics. The student will also see the internals of the Oracle optimizers, and
see proven techniques for Oracle SQL statements for optimal
performance.
Advanced Oracle SQL training class
Three Day Syllabus
© 2016 by Donald K.
Burleson
Note: You choose the specific
SQL topics based on your specific requirements.
|
Day 1: Introduction to SQL
Programming
Class 1:
Oracle SQL Query Overview
Introduction
Syntax Review
Subquery Inline view
Nested subquery
Joins
Sets
SQL expressions
Exercises
Solutions
Class 2: Oracle SQL Functions
Oracle SQL functions
Mathematic functions
Binary
functions
Signs functions
Rounding and truncating functions
Modulo functions
Functions to search and modify strings
Regular expression functions
Conversion functions
NLS
functions
National character set
Logical functions with true or false values
Null functions
Exercises
Solutions
Class 3: SQL Aggregate Functions
Aggregate functions
Standard
Aggregate Functions
Distinct
Keep
Nested Aggregates
Subtotals
PIVOT and UNPIVOT
Exercises
Solutions
Class 4: Oracle SQL Analytics
Analytics
OVER
PARTITION
Ranking functions
Window
Aggregation
FIRST_VALUE and LAST_VALUE
Exercises
Solutions
Class 5: Regular Expressions in SQL
|
|
DAY
3 – Advanced SQL Topics
Class 6: XML
XML
XML Instance
XMLTYPE
XMLELEMENT
XMLCONCAT and
XMLFOREST
XPATH
XMLSEQUENCE
XQuery
Aggregation
XMLSERIALIZE
XMLCAST
Exercises
Solutions
Class 7: Oracle Hierarchies
Hierarchies
Hierarchical Queries
CONNECT BY, PRIOR and START WITH
LEVEL
ORDER
SIBLINGS BY
PRIOR
SYS_CONNECT_BY_PATH
CONNECT_BY_ROOT
CONNECT BY LOOP
CONNECT BY NOCYCLE and CONNECT_BY_ISCYCLE
CONNECT BY without PRIOR
CONNECT_BY_ISLEAF
WHERE and JOIN
Aggregation
Exercises
Solutions
Class 8: SQL For Modeling
SQL for Modeling
Partitions, dimensions and measures
Update
Upsert
CV
Conditions
FOR loops
Iterations
Reference Model
Aggregation
Analytics
Ordered Rows
Exercises
Solutions
Class 9:
SQL*Plus
Starting SQL*Plus
SQL*Plus Statements
Formatting
Substitution Variables
HTML
Index
|
Please
note that while the knowledge gained from this Oracle SQL training may
be valuable when preparing for OCP exams, the
content of this SQL course is not for Oracle Certification, including
the Certified Professional (OCP) or Oracle Certified Associate
(OCA) programs.
This is a BC Oracle SQL training course (c) 2016
|
