Oracle Database: Analytic SQL for Data Warehousing Ed 1

Oracle Database: Analytic SQL for Data Warehousing Ed 1 Certification Training Course Overview

Enroll for the 2-day Oracle Database: Analytic SQL for Data Warehousing Ed 1 from Koenig Solutions accredited by Oracle. In this course will learn 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.

Through a blend of hands-on labs and interactive lectures you will also learn:

  • 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.
  • Gain an understanding of the Oracle Business Intelligence Cloud Service.

Target Audience:

  • Administrator
  • Analyst
  • Architect
  • Developer

Learning 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

 

Oracle Database: Analytic SQL for Data Warehousing Ed 1 (16 Hours) Download Course Contents

Live Virtual Classroom Fee On Request
Group Training
01 - 02 Nov GTR 09:00 AM - 05:00 PM CST
(8 Hours/Day)

06 - 07 Dec 09:00 AM - 05:00 PM CST
(8 Hours/Day)

1-on-1 Training (GTR)
4 Hours
8 Hours
Week Days
Weekend

Start Time : At any time

12 AM
12 PM

GTR=Guaranteed to Run
Classroom Training (Available: London, Dubai, India, Sydney, Vancouver)
Duration : On Request
Fee : On Request
On Request
Special Solutions for Corporate Clients! Click here
Hire Our Trainers! Click here

Course Modules

Module 1: 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
  • Overview of Analytic SQL
Module 2: 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
Module 3: 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
Module 4: 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
Module 5: Analyzing and Reporting Data Using SQL
  • Overview of SQL for Analysis and Reporting Functions
  • Using Analytic Functions
  • Using the Ranking Functions
  • Using Reporting Functions
Module 6: 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
Module 7: 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
Module 8: 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
Download Course Contents

Request More Information

Course Prerequisites

Suggested Prerequisite:

  • Oracle Database 11g: Administer a Data Warehouse Ed 2
  • Oracle Database 12c: Introduction for Experienced SQL Users Ed 1
  • Using Java - for PL/SQL and Database Developers Ed 1
  • Conceptual experience designing data warehouses
  • Practical experience implementing data warehouses
  • Good understanding of relational technology

Required Prerequisite:

  • Oracle Database 11g: Data Warehousing Fundamentals Ed 1
  • Familiarity with SQL
  • Data Warehouse design, implementation, and maintenance experience
  • Good working knowledge of the SQL language
  • Familiarity with Oracle SQL Developer and SQL*Plus