As a result of the training, the participant will:

• Execute complex queries to display data based on various merge options,
• Execute hierarchy-based reports using hierarchical queries,
• Create reports with complex multi-level aggregations, using Rollup and Cube clauses,
• Apply regular expressions in advanced data mining and processing,
• Utilize analytical functions for advanced operation on data sets and windows trailing, neighboring rows
• Apply data modeling using Model clause and dimensions, measures and partitions
• Perform pattern matching queries, searching for specific sets of values
• Optimize queries by selecting appropriate query constructs and creating materialized views.

The course is intended for participants who perform analytical operations or create reports in the Oracle environment. The training is aimed to everyone who needs to perform complex data analysis in databases and make business decisions based on it.

Participants are required to have successfully completed the course "SQL Language for Data Analysts in Oracle Environment" or to know the material covered in this course.

3 days, 24 training hours

The training is conducted in the form of consecutive training sessions divided into modules. The training module includes theoretical part explaining a given issue, collective and individual exercises solved by the participant, as well as a block of questions and answers.

Introducing the objectives and scope of the training
Multidimensional clustering
Rollup and Cube
Advanced result analysis
Complex filtering of results
Analytical functions:

• Application of analytical functions
• Advanced examples
• Nesting of results

The With clause (Subquery Factoring)

• Concept of usage
• Applications

New extensions in joining tables

• Cross Apply
• Outer Apply
• Lateral

Data rearrangement - Pivot

• The idea of data pivoting
• Pivoting based on multiple columns
• Using several aggregate functions in pivoting
• Using the UNPIVOT clause

Querying a specific number of rows

• Using the Fetch First clause
• Accessing sequential data sets
• Using the Percent clause
• Using the With Ties clause

Data modeling (Model clause)

• Retrieving the current value of a dimension
• Modifying existing cells
• Accessing cells using loops
• Modifying existing cells

Regular expressions

• Functions of regular expressions
• Regexp_like
• Special symbols
• Classes of characters
• Applications of functions
• Finding and substitution
• Cutting
• Counting

Pattern Matching

• Idea and usage
• Query structure
• Construction of the Match_recognize clause
• Operators
• Finding patterns with different characteristics
• Use of regular expressions

Views

• Idea and applications
• Command syntax
• Examples of use
• Materialized views
• Idea and applications
• Command syntax
• Examples of use

Query performance

• Syntax selection
• Best practices

Recapping the material on key operations in SQL
Independent exercise across different areas
Additional questions from participants
A summary of the training
Test
Survey

After the training, it is recommended to attend:

• SQL – the language of access to database Oracle
• Oracle SQL language for Data Analysts