Oracle Database 12c: Analytic SQL for Data Warehousing

Course code

Orcl_Db12W

Duration

2 Days, 16 Acad. Hours

Download pdf

Course Overview

Objectives

Prerequisites

Course Outline

Course Overview

Course overview

This Oracle Database 12c: Analytic SQL for Data Warehousing training 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’ll 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.

Objectives

Course Objectives

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

Prerequisites

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

Course Outline

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

Back to trainings (Oracle)

Request the training

Oracle Database 12c: Analytic SQL for Data Warehousing

Course code:

Orcl_Db12W

Duration:

2 Days, 16 Acad. Hours

Apply

Download pdf