This Oracle Database 12c: Analytic SQL for Data Warehousing training teaches you how to interpret the concept of a hierarchical query, create a tree-structured report, format hierarchical data and exclude branches from the tree structure. You'll also learn to use regular expressions and sub-expressions to search for, match, and replace strings. In this course, you will be introduced to Oracle Business Intelligence Cloud Service.

○ Group and aggregate data using the ROLLUP and CUBE operators
○ Analyze and report data using Ranking, LAG/LEAD, and FIRST/LAST functions
○ Use the MODEL clause to create a multidimensional array from query results
○ Use Analytic SQL to aggregation, Analyze and Reporting, and Model Data
○ Interpret the concept of a hierarchical query, create a tree-structured report, format hierarchical data, and exclude branches from the tree structure
○ Gain an understanding of the Oracle Business Intelligence Cloud Service
○ Use regular expressions to search for, match, and replace strings
○ Perform pattern matching using the MATCH_RECOGNIZE clause

Application Developers / Database Designers / Support Engineer / Data Warehouse Analyst / Data Warehouse Developer

 

 

□ Course Topics

 

○ Introduction
Course Objectives, Course Agenda and Class Account Information
Overview of Analytic SQL
Overview of SQL*Plus Environment
Describe the Schemas and Appendices used in the Lesson
Overview of SQL Developer
Oracle Database SQL and Data Warehousing Documentation

○ Grouping and Aggregating Data Using SQL
Using the ROLLUP and CUBE Operators
Using Concatenated Groupings with Example
Review of Group Functions
Working with GROUPING SET Operators and Composite Columns
Generating Reports by Grouping Related Data
Using the GROUPING Function
Reviewing GROUP BY and HAVING Clause

○ Hierarchical Retrieval
Using the WITH Clause
Hierarchical Query Example: Using the CONNECT BY Clause
Walking the Tree: Specifying the Direction of the Query
Hierarchical Queries: Syntax
Using Hierarchical Queries
Walking the Tree: Specifying the Starting Point
Natural Tree Structure
Sample Data from the EMPLOYEES Table

○ Working with Regular Expressions
Extracting Substrings Using the REGEXP_SUBSTR Function
Introducing Metacharacters
Performing a Basic Search Using the REGEXP_LIKE Condition
Finding Patterns Using the REGEXP_INSTR Function
Using the Regular Expressions Functions and Conditions in SQL and PL/SQL
Regular Expressions Functions and Conditions: Syntax
Using Metacharacters with Regular Expressions
Introducing Regular Expressions

○ Analyzing and Reporting Data Using SQL
Using the Ranking Functions
Using Reporting Functions
Overview of SQL for Analysis and Reporting Functions
Using Analytic Functions

○ Performing Pivoting and Unpivoting Operations
Creating a New Pivot Table: Example
Performing Pivoting Operations
Pivoting on the QUARTER Column: Conceptual Example
Performing Unpivoting Operations
Using the PIVOT and UNPIVOT Clauses
Using the UNPIVOT Clause Columns in an UNPIVOT Operation

○ Pattern Matching using SQL
Expressing All Permutations
Rules and Restrictions in Pattern Matching
Examples of Pattern Matching
Row Pattern Navigation Operations
Excluding Portions of the Pattern from the Output
Handling Empty Matches or Unmatched Rows

○ Modeling Data Using SQL
Using the CV Function
Using Analytic Functions in the SQL MODEL Clause
Using the MODEL clause
Using FOR Construct with IN List Operator, incremental values and Subqueries
Distinguishing Missing Cells from NULLs
Using the UPDATE, UPSERT and UPSERT ALL Options
Demonstrating Cell and Range References
Reference Models

○ Oracle Business Intelligence Cloud Service Overview
Oracle BI Cloud Service
BICS Can Integrate Any Data Source Quickly
Enterprise-Grade Service Reliability
Guidance Through Exploratory Analysis & Deep Discovery through Rich Feature Set
Speed, Flexibility and Economy of Cloud
BICS Makes Any Time The Right Time For New Insights
Immediate Access to New Functionality
Introducing Oracle Business Intelligence Cloud Service