Oracle Database 11g: Analytic

Oracle Database 11g: Analytic SQL for Data Warehousing Content Details

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 Sub query
• 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 Pseudo column
• Formatting Hierarchical Reports Using LEVEL and LPAD
• Pruning Branches and Nodes

Analyzing Data Using Regular Expressions

Using the Regular Expressions Functions and Conditions in SQL

• The Benefits of Using Regular Expressions
• Using Met a characters 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 Sub expressions with Regular Expression Support