Module 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

Module 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

Module 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

Module 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

Module 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

Module 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

Module 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

Module 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

Module 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