Oracle Database 11g: Analytic SQL for Data Warehousing

Seminarziel

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. 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. Students use Oracle SQL Developer to develop these program units. SQL*Plus and JDeveloper are introduced as optional tools. 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.
Learn to: - Use Analytic SQL to aggregation, Analyze and Reporting, and Model Data - 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 - 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

Zielgruppe

Data Warehouse-Administrators

Seminarinhalte

o Create a tree-structured report, format hierarchical data, and exclude branches from the tree structure o Identify the benefits of using regular expressions o Use the regular expressions and subexpressions functions o Identify the benefits of using Analytic SQL o Review the available SQL for aggregation operators, SQL for Analysis and Reporting functions, and the SQL for Modeling using the SQL MODEL clause o Group and aggregate data using the ROLLUP and CUBE operators, the GROUPING function, Composite Columns, and the Concatenated Groupings o Analyze and report data using Ranking functions, the LAG/LEAD functions, and the PIVOT and UNPIVOT clauses o Use the MODEL clause to create a multidimensional array from query results and then apply formulas to this array to calculate new values o Interpret the concept of a hierarchical query, create a tree-structured report, format hierarchical data, and exclude branches from the tree structure
Sachgebiete:

- Introduction

- Grouping and Aggregating Data Using SQL

- Analyzing and Reporting Data Using SQL

- Modeling Data Using SQL

- Hierarchical Retrieval

- Analyzing Data Using Regular Expressions

Voraussetzungen

Besuch der Seminare: - "Professioneller Einstieg in Oracle Database 11g SQL"
- "Oracle 10g: Data Warehousing Fundamentals"

Empfohlene Vorkenntnisse:
- "Oracle Database 10g: Data Warehouse Implementierung und Administration"

Hinweis

Oracle University provides a downloadable eKit containing the training materials for this course.