Oracle Database 12c: Analytic SQL for Data Warehousing

Suggested Prerequisite

• Familiarity with Oracle SQL Developer and SQL*Plus
• Good working knowledge of the SQL language
• Familiarity with SQL
• Data Warehouse design, implementation, and maintenance experience
• Practical experience implementing data warehouses
• Good understanding of relational technology
• Conceptual experience designing data warehouses

Required Prerequisite

• Oracle Database 11g: Data Warehousing Fundamentals
• Oracle Database 12c: Introduction to SQL Ed 1.1
• Oracle Database 11g: Administer a Data Warehouse
• Oracle Database 12c: Introduction for Experienced SQL Users
• Using Java – for PL/SQL and Database Developers

Audience

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

Coure Topics

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

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

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

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

5. Analyzing and Reporting Data Using SQL

• Using the Ranking Functions
• Using Reporting Functions
• Overview of SQL for Analysis and Reporting Functions
• Using Analytic Functions

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

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

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

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