Oracle Database 12c: Analytic SQL for Data Warehousing

Duration: 2 Days

What you will learn

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.

Learn To:

Use SQL with aggregation operators, SQL for Analysis and Reporting functions.
Group and aggregate data using the ROLLUP and CUBE operators, the GROUPING function, Composite Columns and
the concatenated Groupings.
Analyze and report data using Ranking functions, the LAG/LEAD Functions and the PIVOT and UNPIVOT clauses.
Perform advanced pattern matching.
Use regular expressions to search for, match and replace strings.

Benefits to You

Enrolling in 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. Through
working with expert Oracle University instructors in a hands-on classroom environment, you’ll deepen your knowledge
so you can perform better on the job.
Before Attending this Course
Before attending this course, you should be familiar with the following: relational database concepts, data warehouse
theory and implementation, Oracle server concepts )including application and server tuning) and the operating system
environment on which the Oracle Database Server is running. You’ll use Oracle SQL Developer to develop program
units. SQL*Plus is introduced as an optional tool.

Audience

Application Developers
Data Warehouse Analyst
Data Warehouse Developer
Database Administrators
Database Designers
Support Engineer

Related Training

Required Prerequisites
Data Warehouse design, implementation, and maintenance experience
Familiarity with Oracle SQL Developer and SQL*Plus
Familiarity with SQL
Good working knowledge of the SQL language
Oracle Database 11g: Data Warehousing Fundamentals
Oracle Database: Introduction to SQL Ed 1.1
Suggested Prerequisites
Conceptual experience designing data warehouses
Good understanding of relational technology
Oracle Database 11g : Administrer un data warehouse Nouveau
Oracle Database 12c: Introduction for Experienced SQL Users
Practical experience implementing data warehouses
Using Java – for PL/SQL and Database Developers

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
Use regular expressions to search for, match, and replace strings
Perform pattern matching using the MATCH_RECOGNIZE clause

Course Topics

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