Oracle Database 12c: Analytic SQL for Data Warehousing Training

Live Online & Classroom Enterprise Certification Training

This Oracle Database 12c: Analytic SQL for Data Warehousing training teaches you how to use Analytic SQL to aggregate, analyze, report and model data. Interact with expert Oracle University instructors. In this course, you will be introduced to Oracle Business Intelligence Cloud Service.

Looking for a private batch ?

REQUEST A CALLBACK

Certified Trainer
Authorized Courseware
Completion Certificate from ATP

Enterprise Reporting
Lifetime Access
CloudLabs
24x7 Support
Real-time code analysis and feedback

What is 12c: Analytic SQL for Data Warehousing Certification Training about?

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.

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, 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.

What are the objectives of 12c: Analytic SQL for Data Warehousing Certification Training ?

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

Who is 12c: Analytic SQL for Data Warehousing Certification Training for?

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

What are the prerequisites for 12c: Analytic SQL for Data Warehousing Certification Training?

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

Available Training Modes

Live Online Training

16 Hours

Classroom Training

2 Days

Course Outline Expand All

Expand All

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

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

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

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

Analyzing and Reporting Data Using SQL

Using the Ranking Functions

Using Reporting Functions

Overview of SQL for Analysis and Reporting Functions

Using Analytic Functions

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

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

Modelling 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

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

Who is the instructor for this training?

The Trainer is Oracle certified Instructor with extensive domain experience, including years of experience training & mentoring professionals in the industry.

12c: Analytic SQL for Data Warehousing Certification Training - Certification & Exam

SpringPeople is the Authorized Training Partner of Oracle.
The training fees is exclusive of exam cost.
For any queries; feel free to reach us at oracle@springpeople.com

Reviews

My outlook on training changed completely after attending SpringPeople BPC training. The content, the trainer and infrastructure at SpringPeople were top notch and perfectly in tune with the industry requirements. Regardless to say, training is now something that I look forward to to. Kudos to everyone at SpringPeople!

Shweta Priya

Sony

I attended the 3-day AngularJs training at SpringPeople. The trainer was an industry veteran with vast experience in the subject. Notably, the hands-on training, and the Q&A session stood out. Overall, I found SpringPeople a great place to learn with excellent facilities and great trainers. Would recommend SpringPeople to my colleagues and friends.

Swati Singh

I attended the training on API Design for Mulesoft. The sessions were well planned and value-laden. I benefited immensely from the hands-on experience enabled through virtual labs. I would like to specifically commend the efficiency of the support team who were always available to resolve my concerns.

Nikhil Kohli

Stryker

I attended the jQuery training batch, conducted by Mr. Vijay, an SME who did a thorough coverage of all the essentials. He took us through concepts such as jQuery animations, event handlers, plugins, and jQuery-UI by small programs, very easily. The sessions were useful and well structured. By the end of the training, I was well equipped to develop a SPA on Product Management System. Overall, the learning experience at SpringPeople was great!

Heena Rajan

Mindtree