Oracle Database: Analytic SQL for Data Warehousing
In this course students use Analytic SQL to aggregate, analyze and report, and model data. Students learn to interpret the concept of a hierarchical query, create a tree-structured report, format hierarchical data, and exclude branches from the tree structure. Students also learn to use regular expressions and subexpressions to search for, match, and replace strings.
Audience:
This course is intended for data warehouse builders and implementers, database administrators, system administrators, and database application developers who design, maintain, and use data warehouses.
Prerequisites:
Before attending this course, students should be familiar with 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.
Course objectives:
Upon completion of this course, the student should be able to:
- Identify the benefits of using Analytic SQL.
- Review the available SQL for aggregation operators, SQL for Analysis and Reporting functions, and the SQL for Modeling using the SQL MODEL clause.
- Group and aggregate data using the ROLLUP and CUBE operators, the GROUPING function, Composite Columns, and the Concatenated Groupings.
- 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.
- Create Analytic Views and ALTER and DROP the Analytic Views.
- Analyze and report data using Ranking functions, the LAG/LEAD Functions, and the PIVOT and UNPIVOT clauses.
- Use the MODEL clause to create a multidimensional array from query results and then apply formulas to this array to calculate new values.
- Use the MATCH_RECOGNIZE clause to detect patterns in the table values.
Course topics
- Introduction
- Grouping and Aggregating Data Using SQL
- Hierarchical Retrieval
- Working with Regular Expressions
- Analyzing and Reporting Data Using SQL
- Analytic Views
- Performing Pivoting and Unpivoting Operations
- Pattern Matching using SQL
- Modeling Data Using SQL
