Course Name

Oracle Database: Analytic SQL for Data Warehousing Ed 1

  • Code: D80174GC24
  • Duration: 3 Day(s)
  • Date: 25 Sep, 2024
  • Price: R19, 500.00 (excl. VAT)
Call me back
Get Overview
Book now!

Course Introduction

This course is a comprehensive training program designed to equip learners with the knowledge and skills necessary to effectively use Analytic SQL features and functions for data warehousing solutions.

The course covers a wide range of topics, starting with an introduction to the SQL*Plus and SQL Developer environments and foundational concepts of Analytic SQL. It then delves into more advanced data manipulation techniques such as grouping and aggregating data, hierarchical retrieval, working with regular expressions, and analyzing and reporting data.

Learners will also explore pivotal SQL features for transforming data, such as pivoting and unpivoting operations, and the cutting-edge capabilities of SQL for pattern matching and data modeling using the MODEL clause. This course is invaluable for professionals who aim to create sophisticated reports, perform complex analytics tasks, and enhance their data warehousing expertise.

By mastering these advanced SQL techniques, learners can derive meaningful insights from their organization’s data, leading to more informed decision-making processes.

Course Delivery Method

Our courses have flexible delivery options:

  • In-person classroom training at the Impactful training
    facilities

  • Virtual instructor-led training
  • Nationally: on-site at the client

Course Intended Audience

This course is intended for:

  • Database Administrators
  • Data Analysts
  • Data Scientists
  • Data Warehouse Specialists
  • BI (Business Intelligence) Professionals
  • SQL Programmers
  • Database Developers
  • IT Managers overseeing data management teams
  • Report Developers
  • Technical Consultants with a focus on database solutions
  • Professionals preparing for Oracle Certification exams related to SQL and data warehousing

Course Prerequisites

Learners should start this course already having the following skills:

  • 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
  • Suggested Prerequisites
    • Conceptual experience designing data warehouses
    • Good understanding of relational technology
    • Oracle Database 11g: Administer a Data Warehouse
    • Oracle Database 12c: Introduction for Experienced SQL Users
    • Practical experience implementing data warehouses
    • Using Java – for PL/SQL and Database Developers

Course Objectives

By the end of this course, you will be able to:

  • Understand the objectives and structure of the Oracle Database: Analytic SQL for Data Warehousing course.
  • Utilize SQL*Plus, SQL Developer, and understand the principles of Analytic SQL to manage and manipulate data.
  • Group and aggregate data using SQL functions such as ROLLUP, CUBE, and GROUPING SETS for sophisticated reporting.
  • Construct and execute hierarchical queries to retrieve data organized in natural tree structures.
  • Apply regular expressions in SQL through functions like REGEXP_LIKE, REGEXP_INSTR, and REGEXP_SUBSTR for pattern matching and data extraction.
  • Analyze and report data using SQL analytic functions, including ranking and reporting functions for comprehensive data analysis.
  • Perform advanced data transformation using PIVOT and UNPIVOT operations to reshape data sets.
  • Implement pattern matching with SQL to identify complex data sequences and extract insightful information.
  • Model data using the SQL MODEL clause for multi-dimensional analysis, managing cell and range references, and utilizing analytic functions within data models.
  • Gain practical experience and confidence in using Oracle’s Analytic SQL features to address real-world data warehousing problems.

Course Content

  • 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
L❤ ve this. Share it now!

Need additional information?

We are here to support your growth every step of the way

Please call me

Get in touch

Contact the Impactful team if you need any assistance.
Email: impactful@lrmg.co.za
Tel: +27 87 941 5764

Course Introduction

This course is a comprehensive training program designed to equip learners with the knowledge and skills necessary to effectively use Analytic SQL features and functions for data warehousing solutions.

The course covers a wide range of topics, starting with an introduction to the SQL*Plus and SQL Developer environments and foundational concepts of Analytic SQL. It then delves into more advanced data manipulation techniques such as grouping and aggregating data, hierarchical retrieval, working with regular expressions, and analyzing and reporting data.

Learners will also explore pivotal SQL features for transforming data, such as pivoting and unpivoting operations, and the cutting-edge capabilities of SQL for pattern matching and data modeling using the MODEL clause. This course is invaluable for professionals who aim to create sophisticated reports, perform complex analytics tasks, and enhance their data warehousing expertise.

By mastering these advanced SQL techniques, learners can derive meaningful insights from their organization’s data, leading to more informed decision-making processes.

Course Delivery Method

Our courses have flexible delivery options:

  • In-person classroom training at the Impactful training
    facilities

  • Virtual instructor-led training
  • Nationally: on-site at the client

Course Intended Audience

This course is intended for:

  • Database Administrators
  • Data Analysts
  • Data Scientists
  • Data Warehouse Specialists
  • BI (Business Intelligence) Professionals
  • SQL Programmers
  • Database Developers
  • IT Managers overseeing data management teams
  • Report Developers
  • Technical Consultants with a focus on database solutions
  • Professionals preparing for Oracle Certification exams related to SQL and data warehousing

Course Prerequisites

Learners should start this course already having the following skills:

  • 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
  • Suggested Prerequisites
    • Conceptual experience designing data warehouses
    • Good understanding of relational technology
    • Oracle Database 11g: Administer a Data Warehouse
    • Oracle Database 12c: Introduction for Experienced SQL Users
    • Practical experience implementing data warehouses
    • Using Java – for PL/SQL and Database Developers

Course Objectives

By the end of this course, you will be able to:

  • Understand the objectives and structure of the Oracle Database: Analytic SQL for Data Warehousing course.
  • Utilize SQL*Plus, SQL Developer, and understand the principles of Analytic SQL to manage and manipulate data.
  • Group and aggregate data using SQL functions such as ROLLUP, CUBE, and GROUPING SETS for sophisticated reporting.
  • Construct and execute hierarchical queries to retrieve data organized in natural tree structures.
  • Apply regular expressions in SQL through functions like REGEXP_LIKE, REGEXP_INSTR, and REGEXP_SUBSTR for pattern matching and data extraction.
  • Analyze and report data using SQL analytic functions, including ranking and reporting functions for comprehensive data analysis.
  • Perform advanced data transformation using PIVOT and UNPIVOT operations to reshape data sets.
  • Implement pattern matching with SQL to identify complex data sequences and extract insightful information.
  • Model data using the SQL MODEL clause for multi-dimensional analysis, managing cell and range references, and utilizing analytic functions within data models.
  • Gain practical experience and confidence in using Oracle’s Analytic SQL features to address real-world data warehousing problems.

Course Content

  • 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

Are you ready to start?

Sign Up
Download Overview

Certified global best practices in the new technologies…

Amazon Web Services Impactful Partner
PeopleCert Logo
Impactful is an Accredited Oracle University Partner
Impactful Partner Microsoft logo

Get ahead with your IT and Digital Talent development

Please complete the form with your information and one of our experts will get back to you soon.

Get in touch

Contact the Impactful team if you need any assistance.

Email: impactful@lrmg.co.za
Tel: +27 87 941 5764

About LRMG

Testing Elementor conditions

Testing Elementor conditions

Testing Elementor conditions

Testing Elementor conditions

CONTACT US