Oracle - Analytic SQL for Data Warehousing Training Course and Workshop in Bangalore, Mysore, Chennai, Hyderabad, Pune, Mumbai, Delhi, Noida, Gurgaon, Kolkata

The Oracle - Analytic SQL for Data Warehousing training course 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 will 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. This course will help data warehouse builders and implementers, database administrators, system administrators and database application developers to better design, maintain and use data warehouses.

By attending Oracle - Analytic SQL for Data Warehousing workshop, delegates will learn to:

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

Familiarity with SQL
Knowledge of Data Warehouse design, implementation, and maintenance experience
Good working knowledge of the SQL language
Familiarity with Oracle SQL Developer and SQL*Plus

This Oracle - Analytic SQL for Data Warehousing class is ideal for:

Administrators
Analysts
Architects
Developers

Introduction

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

Grouping and Aggregating Data Using SQL

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

Hierarchical Retrieval

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

Working with Regular Expressions

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

Analyzing and Reporting Data Using SQL

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

Performing Pivoting and Unpivoting Operations

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

Pattern Matching using SQL

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

Modeling Data Using SQL

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

Oracle Business Intelligence Cloud Service Overview

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

Encarta Labs Advantage

One Stop Corporate Training Solution Providers for over 6,000 various courses on a variety of subjects
All courses are delivered by Industry Veterans
Get jumpstarted from newbie to production ready in a matter of few days

Trained more than 50,000 Corporate executives across the Globe
All our trainings are conducted in workshop mode with more focus on hands-on sessions

Oracle - Analytic SQL for Data Warehousing

COURSE AGENDA