Training
Contact

SQL language for data analysts in an Oracle environment

Advanced level
    Duration
    Start hour
    Level

      SQL language for data analysts in an Oracle environment - advanced level

      Optimize queries by selecting appropriate query constructs and creating materialized views!

      The “SQL Language for Data Analysts in Oracle Environment - advanced level” course is dedicated to advanced data analysts who want to take their data analysis skills to an even higher level. With the expertise of Comarch Training Center experts, you will learn techniques for extracting information and learn how to perform complex data analysis.

      Full program

      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. 
      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

      Training delivery method

      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.

      Duration

      3 days, 24 training hours

      Theoretical and practical knowledge
      1. Introducing the objectives and scope of the training
      2. Multidimensional clustering
      3. Rollup and Cube
      4. Advanced result analysis
      5. Complex filtering of results
      6. Analytical functions:
        • Application of analytical functions
        • Advanced examples
        • Nesting of results
      7. The With clause (Subquery Factoring)
        • Concept of usage
        • Applications
      8. New extensions in joining tables
        • Cross Apply
        • Outer Apply
        • Lateral
      9. Data rearrangement - Pivot
        • The idea of data pivoting
        • Pivoting based on multiple columns
        • Using several aggregate functions in pivoting
        • Using the UNPIVOT clause
      10. Querying a specific number of rows
        • Using the Fetch First clause
        • Accessing sequential data sets
        • Using the Percent clause
        • Using the With Ties clause
      11. Data modeling (Model clause)
        • Retrieving the current value of a dimension
        • Modifying existing cells
        • Accessing cells using loops
        • Modifying existing cells
      12. Regular expressions
        • Functions of regular expressions
        • Regexp_like
        • Special symbols
        • Classes of characters
        • Applications of functions
        • Finding and substitution
        • Cutting
        • Counting
      13. Pattern Matching
        • Idea and usage
        • Query structure
        • Construction of the Match_recognize clause
        • Operators
        • Finding patterns with different characteristics
        • Use of regular expressions
      14. Views
        • Idea and applications
        • Command syntax
        • Examples of use
        • Materialized views
        • Idea and applications
        • Command syntax
        • Examples of use
      15. Query performance
        • Syntax selection
        • Best practices
      16. Recapping the material on key operations in SQL
      17. Independent exercise across different areas
      18. Additional questions from participants
      19. A summary of the training
      20. Test
      21. Survey
      Development path

      Similar trainings

      Consultant:

      Barbara Nitwinko
      Barbara Nitwinko

      training@comarch.com

      +48 12 646 14 41

      +48 734 131 012