Oracle Database 11g: 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.
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
Skills Gained
Create a tree-structured report, format hierarchical data, and exclude branches from the tree structure
Identify the benefits of using regular expressions
Use the regular expressions and subexpressions functions
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
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
Interpret the concept of a hierarchical query, create a tree-structured report, format hierarchical data, and exclude branches from the tree structure
Who Can Benefit
Data Warehouse Developer
Application Developers
Support Engineer
Data Warehouse Administrator
Prerequisites
Required Prerequisites:
Oracle Database 11g: Introduction to SQL
Oracle 10g: Data Warehousing Fundamentals
Suggested Prerequisites:
Oracle Database 10g: Implement and Administer a Data Warehouse
Code:
11g-ANALYTIC-SQL
Length:
1 days
Type:
Instructor-Led
Certified By:
Oracle
Tuition:
$600
This course is taught by Certified Oracle instructors. There is a difference. Learn More
This course is in the following categories. Click the categories to find similar courses and topics of interest.
There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links below. We can also contact you when the course is scheduled in your area.
Oracle 11g SQL and Data Warehousing Documentation and Additional Resources
Grouping and Aggregating Data Using SQL
What is Analytic SQL?
Analytic SQL in Data Warehouses Agenda: SQL for Aggregation, SQL for Analysis and Reporting, and SQL for Modeling
Generating Reports by Grouping Related Data
Using the GROUP BY Clause With the ROLLUP and CUBE Operators
Using the ROLLUP and CUBE Operators
Using the GROUPING Function
Working With GROUPING SETS
Working With Composite Columns and Concatenated Groupings
Analyzing and Reporting Data Using SQL
Overview of SQL for Analysis and Reporting Functions
Identifying the SQL Ranking Functions
Controlling the Ranking Order
Ranking on Multiple Expressions
Using the RANK, DENSE_RANK, and PERCENT_RANK Functions
Ranking Per CUBE and ROLLUP
Using the LAG/LEAD Functions
Performing Pivoting Operations Using the PIVOT and UNPIVOT Clauses
Modeling Data Using SQL
Overview of SQL for Modeling Data
Integrating Inter-row Calculations in SQL
Working With the SQL MODEL Clause
Cell and Range References
Using the CV()Function
Using the FOR Construct with IN List Operator, Incremental Values, and a Subquery
Using Reference Models
Cyclic Rules in Models
Hierarchical Retrieval
Hierarchical Retrieval: Overview
Natural Tree Structure
Hierarchical Queries
Walking the Tree
Walking the Tree: From the Bottom Up and From the Top Down
Ranking Rows with the LEVEL Pseudocolumn
Formatting Hierarchical Reports Using LEVEL and LPAD
Pruning Branches and Nodes
Analyzing Data Using Regular Expressions
The Benefits of Using Regular Expressions
Using the Regular Expressions Functions and Conditions in SQL
Using Metacharacters with Regular Expressions
Performing a Basic Search Using the REGEXP_LIKE Condition
Finding Patterns Using the REGEXP_INSTR Function
Extracting Substrings Using the REGEXP_SUBSTR Function
Replacing Patterns Using the REGEXP_REPLACE Function
Using Subexpressions with Regular Expression Support
When you take a certified course with ExitCertified, you are learning from
the creators of the products you use. Our commitment to your IT
community, along with our authorization to deliver certified courses,
ensures you receive a premium training experience.
