Audience
This course is aimed at those already experienced with SQL for data retrieval. It covers a range of the more advanced Oracle SQL functions required for extensive analysis and reporting.
This course is suitable for all recent versions of Oracle including 12c, 18c, and 19c.
Prerequisites
Delegates should have a good basic understanding of Oracle SQL. Previous attendance of our Oracle SQL course is highly recommended.
Duration
1 day, hands on.
NOTE: This course may be combined with Oracle SQL and run over a total of four days, please contact us for more information.
Course Objectives
This course is highly interactive and consists of theory coupled with plenty of practical sessions. It will introduce delegates to the use of analytic, hierarchical and modelling functions within an Oracle database.
Together with Oracle SQL, this course provides the perfect prerequisite knowledge to embark on training for Oracle PL/SQL Programming, or administrative roles such as Oracle Database Administration.
On completion of the course delegates will be able to:
- Use the Oracle SQL analytic summary functions
- Use Oracle SQL to create and analyse dynamic hierarchical data structures
- Use the set of Oracle SQL MODEL functions to produce spreadsheet-like reports and analyses
Course Content
Analytic Functions
Analytic summary functions
Analytic non-summary functions
Use of the OVER clause
Use of PARTITION clause with the OVER clause
Use of WINDOW clause with the OVER clause
Hierarchical Queries
CONNECT BY, PRIOR and START WITH
LEVEL pseudo column
ORDER SIBLINGS BY PRIOR
CONNECT BY PATH, ROOT, LOOP, ISCYCLE, ISLEAF
Model Function Components
Measures
Dimensions
Partitions
Rules
CV function
Reference models
Iterative models
Ordered rules