Oracle Database 12c: Analytic SQL for Data Warehousing
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.
Description
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.
What You'll Learn
- Use SQL with aggregation operators, SQL for Analysis and Reporting functions.
- 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.
- Perform advanced pattern matching.
- Use regular expressions to search for, match and replace strings.
- Gain an understanding of the Oracle Business Intelligence Cloud Service.
Prerequisites
- Familiarity with SQL
- Data Warehouse design, implementation, and maintenance experience
- Familiarity with Oracle SQL Developer and SQL*Plus
- 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
Outline
9 modules
•
2 Days total
- 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
- 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
- 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
- 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
- Overview of SQL for Analysis and Reporting Functions
- Using Analytic Functions
- Using the Ranking Functions
- Using Reporting Functions
- 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
- 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
- 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
- Oracle BI Cloud Service
- Introducing Oracle Business Intelligence Cloud Service
- Guidance Through Exploratory Analysis & Deep Discovery through Rich Feature Set
- BICS Can Integrate Any Data Source Quickly
- BICS Makes Any Time The Right Time For New Insights
- Speed, Flexibility and Economy of Cloud
- Immediate Access to New Functionality
- Enterprise-Grade Service Reliability