751. Oracle 10/11g - Advanced SQL
This course will give experienced Oracle technologists the advanced SQL skills necessary to design and code complex queries against Oracle databases.
You will learn to use many advanced SQL coding techniques such as coding analytic functions for data warehouse and decision support queries, using partition outer join to "densify" data, the Oracle10g MODEL clause ("spreadsheet-like capability directly from the database"), Oracle10g regular expressions for pattern matching, using the extended aggregate functions CUBE and ROLLUP, CONNECT BY (hierarchical queries), coding SET operations such as INTERSECT, and more.
An overview Oracle10g SQL-related new features is also provided.
As with all of our courses, this course is highly customizable to your specific training requirements.
LEARNING OBJECTIVES
·
Design, code and test the most efficient Oracle query for a given business problem
·
Code efficient inner and outer joins, including the use of the Oracle ANSI-compatible join syntax
·
Use Oracle10g partition outer join to "densify" data (i.e. fill in the gaps)
·
Code complex subqueries including correlated subqueries, inline views, subqueries in the column-list, Oracle subquery factoring (the "WITH" clause) and NOT EXISTS queries
·
Code partition independent and partition-aware queries.
·
Use the set operators UNION , INTERSECT and MINUS to combine the results of two or more queries into a single result set.
·
Query and generally handle Oracle date and datetime (i.e. temporal) data.
·
Use the Oracle10g MODEL clause to produce spreadsheet-like results (e.g. sales forecasting) from a query result set
·
Use Oracle10g regular expressions for text pattern matching (i.e. search and optionally replace data using the POSIX-compatible regular expressions)
·
Use Oracle analytic functions to code efficient solutions to complex decision support problems such as ranking (e.g. top earners by department), percentages within a group (e.g. each employees percent of total payroll), cumulative totals (e.g. cumulative salary by department), lag and lead functions (compare to next / previous row - without a self-join), windowing functions and more.
·
Use the Oracle CUBE and ROLLUP extensions to create super-aggregate rows of output (i.e. add additional dimensions of data to the result).
·
Use the Oracle GROUPING function with CUBE and ROLLUP.
·
Use the Oracle GROUPING SETS extension to the GROUP BY clause to control which dimensions are included in the result set.
·
Store hierarchical data and display data results in hierarchy order (e.g. employees sorted by management chain) with the Oracle CONNECT BY clause
·
Use the Oracle DECODE function and Oracle CASE expression to add IF / THEN / ELSE (conditional) logic to an SQL query.
·
Use basic Oracle XML functions to search and extract XML data from the Oracle database
Course Duration: 4
days
Prerequisites:
Some SQL and SQL*Plus experience is required. For example, you should be comfortable
coding basic SQL SELECT statements, including the use of the WHERE clause with basic filters
and simple joins.
Audience: Anyone who uses SQL to query Oracle databases including developers, analysts, database administrators
and decision support personnel. Persons working in a data warehouse or decision support environment
will benefit greatly from the lessons on analytic functions, grouping with CUBE and ROLLUP,
modeling (the Oracle10g MODEL clause) and coding queries on partitioned data. Further, this course is well suited for non-Oracle professionals already
experienced in the SQL but who wish to become acquainted with the unique and advanced features of Oracle SQL.
1. Oracle DECODE and CASE - Conditional Logic in SQL
Introduction to Oracle DECODE
Oracle DECODE and Range Comparisons
The Oracle CASE Expression
Oracle CASE Syntax
Workshop
2. Set Operations
UNION , INTERSECT and MINUS Operators
Key UNION Concepts
INTERSECT Operation
MINUS Operation
Example: Compare Schemas
Example: Compare Tables
Example: Compound Set Operations
Workshop
3. Subqueries
Oracle Subquery Overview
Restrictions
Subquery Gotcha
Correlated Subqueries
The Need for NOT EXISTS
The EXISTS Operator
Oracle Top-N Queries - Inline Views
Oracle9i Extended Subquery Support
Oracle Subquery Factoring - The WITH Clause
Workshop
4. Advanced Joins
Join Terminology Review
3,4,5 Way Inner Joins
Join and GROUP BY
Self-Joins
Cartesian Product
Outer Joins
Oracle ANSI Compliant Joins
Natural Join
Oracle USING Clause
Oracle ON Clause
Oracle ANSI Outer Joins
Oracle ANSI Full Outer Joins
Oracle ANSI Cross Join
Joining to Views
Join Tuning Tips
Oracle Partition Outer Join (Oracle10g)
Workshop
5. Coding Queries on Partitioned Data
Oracle Partition Concepts
Partition-Independent Queries
Partition Pruning
Coding Partition-Dependent Queries in Oracle
Workshop
6. Grouping Data
Oracle Aggregate Function Overview
Grouping Multiple Columns
Golden GROUP BY Rule
The HAVING Clause
Oracle ROLLUP Operations
Oracle CUBE Operations
Oracle GROUPING Function
Oracle GROUPING with DECODE
Oracle GROUPING in HAVING
Oracle GROUPING SETS
Workshop
7. Oracle Analytic Functions
Introduction
What Do They Do?
Getting Started with Oracle Analytic Functions
Oracle Partition Clause
Oracle Order-By Clause
Oracle Windowing Clause
Oracle Row Windows
Oracle Range Windows
Oracle Range Windows: BETWEEN
Oracle Range Windows: INTERVAL
Oracle Ranking Functions
Oracle Top-N Queries
Oracle LAG and LEAD Functions
Closing Thoughts
List of Analytic Functions
Resources
Workshop
8. Model Queries (Oracle10g)
Oracle10g MODEL Clause Concepts
Oracle10g MODEL Clause Components and Clauses
Workshop
9. Oracle CONNECT BY - Hierarchical Queries
Introduction
Oracle CONNECT BY Example
LEVEL with LPAD
Adding WHERE Clause
Sort by LEVEL
Oracle9i SIBLINGS Sorts
Oracle9i Hierarchy Path
New Oracle10g Pseudocolumns (Oracle10g)
Workshop
10. Using Regular Expressions in Oracle SQL (Oracle10g)
Searching with Oracle10g REGEXP_LIKE
REGEXP_LIKE Versus LIKE
Oracle10g Regular Expression Functions (e.g. REGEXP_REPLACE)
Basic Elements of Expressions
Using Backreferences
Workshop
11. Oracle Date and Time (Temporal) Data
Scalar Function Review (New Oracle10g Functions)
Oracle Date Conversion Functions
Oracle TO_CHAR & TO_DATE Examples
Oracle Date Arithmetic & Functions
Adding & Subtracting Days in Oracle SQL
Oracle ADD_MONTHS Function
Oracle LAST_DAY Function
Oracle TRUNC Function
Introduction to Oracle Datetime Data
Oracle TIMESTAMP Data Type
Oracle TIMESTAMP WITH TIME ZONE Data Type
Oracle TIME STAMP WITH LOCAL TIME ZONE Data Type
Oracle Datetime Conversions
More Oracle Time Zone Functions
Workshop
12. Oracle XML DB and XMLType
XML in the Oracle Database
XMLType in Oracle Tables
Inserting XML Data
Selecting XML Data
Oracle XML DB EXTRACT Function
Oracle XML DB EXTRACTVALUE Function
Updating Oracle XML Data
PL/SQL and XML
Other Oracle XML Features
XML DB Workshop
13. Additional Oracle10g New SQL Features
Case Insensitive Search and Sort in Oracle10g
Enclosing Quotes
Oracle MERGE Statement Enhancements
ORA_ROWSCN Pseudocolumn
Oracle10g Nested Table Enhancements
Oracle10g Temporary Table Enhancements
Aggregates in the Oracle10g RETURNING Clause
New Datatypes in Oracle10g