SQL language for data analysts in an Oracle environment

– advanced level
    Czas trwania
    Godzina rozpoczęcia
    Stopień trudności

      SQL language for data analysts in an Oracle environment

      advanced level

      About training

      During the training, the participant will acquire knowledge of the operation of advanced mechanisms available in the Oracle environment responsible for searching and analyzing data. The participant will master skills in extensions of multidimensional aggregations, cases of the complex use of analytical functions, division of complex queries, regular expressions, data rearrangement and data modeling. 

      View the training programme

      What will you gain from the training?

      Training objectives 

      During the training, the participant will acquire knowledge of the operation of advanced mechanisms available in the Oracle environment responsible for searching and analyzing data. The participant will master skills in extensions of multidimensional aggregations, cases of the complex use of analytical functions, division of complex queries, regular expressions, data rearrangement and data modeling. The knowledge and skills imparted during the training on the new options and solutions included in Oracle Server 12c, including pattern matching, determining the scope of data presented, as well as new options for joining tables, are particularly important. There is also valuable information on increasing command performance, including the use of materialized views.

      Skills

      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.
      Is this training for you?

      Audience profile

      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.

      Requirements 

      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.

      Duration

      Duration:

      • 3 days, 24 training hours
      Detailed training program

      Detailed training program

      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

      DOWNLOAD FILE

      Development path

      After the training, it is recommended to attend:

      • Training: "Performance Tuning in Oracle SQL".
      • Training: "Oracle PL/SQL programming language"
      • https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/index.html


      Podobne tematy