ORA105 :: Oracle 11g Analytic SQL

  • Pricing: Tier I
  • Duration: 1 Day

Summary:

Attendees will learn to use SQL to aggregate, analyze and report, and model data. Attendees will learn how to use the CUBE and ROLLUP statements to aggregate their data and the RANK function to analyze their data. They will also learn to build and interpret hierarchical queries, create a tree-structured report, format hierarchical data, and exclude branches from the tree structure. Attendees also learn to use regular expressions and subexpressions to search for, match, and replace strings.

Audience:

  • Developers
  • BI Developer
  • BI Analyst

Objectives:

    Topics:

    • Introduction
      • Course Objectives
      • Course Agenda
      • Class Accounts Information
      • Appendices Used in this Course
      • Sample Schemas Used in this Course
      • SQL Environments Available in the Course
      • Overview of Oracle SQL Developer
      • Oracle 11g SQL and Data Warehousing Documentation and Additional Resources
    • Grouping and Aggregating Data Using SQL
      • What is Analytic SQL?
      • Analytic SQL in Data Warehouses Agenda: SQL for Aggregation, SQL for Analysis and Reporting, and SQL for Modeling
      • Generating Reports by Grouping Related Data
      • Using the GROUP BY Clause With the ROLLUP and CUBE Operators
      • Using the ROLLUP and CUBE Operators
      • Using the GROUPING Function
      • Working With GROUPING SETS
      • Working With Composite Columns and Concatenated Groupings
    • Analyzing and Reporting Data Using SQL
      • Overview of SQL for Analysis and Reporting Functions
      • Identifying the SQL Ranking Functions
      • Controlling the Ranking Order
      • Ranking on Multiple Expressions
      • Using the RANK, DENSE_RANK, and PERCENT_RANK Functions
      • Ranking Per CUBE and ROLLUP
      • Using the LAG/LEAD Functions
      • Performing Pivoting Operations Using the PIVOT and UNPIVOT Clauses
    • Modeling Data Using SQL
      • Overview of SQL for Modeling Data
      • Integrating Inter-row Calculations in SQL
      • Working With the SQL MODEL Clause
      • Cell and Range References
      • Using the CV()Function
      • Using the FOR Construct with IN List Operator, Incremental Values, and a Subquery
      • Using Reference Models
      • Cyclic Rules in Models
    • Hierarchical Retrieval
      • Hierarchical Retrieval: Overview
      • Natural Tree Structure
      • Hierarchical Queries
      • Walking the Tree
      • Walking the Tree: From the Bottom Up and From the Top Down
      • Ranking Rows with the LEVEL Pseudocolumn
      • Formatting Hierarchical Reports Using LEVEL and LPAD
      • Pruning Branches and Nodes
    • Analyzing Data Using Regular Expressions
      • The Benefits of Using Regular Expressions
      • Using the Regular Expressions Functions and Conditions in SQL
      • Using Metacharacters with Regular Expressions
      • Performing a Basic Search Using the REGEXP_LIKE Condition
      • Finding Patterns Using the REGEXP_INSTR Function
      • Extracting Substrings Using the REGEXP_SUBSTR Function
      • Replacing Patterns Using the REGEXP_REPLACE Function
      • Using Subexpressions with Regular Expression Support