Сhat now
Training Center MUKКурсыOracleDatabaseOracle Database 12c: Analytic SQL for Data Warehousing

Oracle Database 12c: Analytic SQL for Data Warehousing

Course code
Orcl_Db12W
Duration
2 Days, 16 Acad. Hours
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
Request the training
Oracle Database 12c: Analytic SQL for Data Warehousing
Course code:
Orcl_Db12W
Duration:
2 Days, 16 Acad. Hours
Apply
Сhat now
Свяжитесь со мной
Сhat now
Отправить заявку
Registration for the webinar
Отправить заявку
Your application has been received! We will contact you soon.