Oracle Apps Functional Online Training
Oracle Apps Functional Online Training
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