Audience
This course is perfect for Oracle developers and data analysts who wish to fully leverage analytic capabilities of Oracle database engine, and get more insight into their data.
Prerequisites
Knowledge of the fundamentals of querying Oracle databases using SQL is required.
Duration
2 days. Hands on.
This course is available on site only. Please call for details.
Course Objectives
This course seeks to familiarise delegates with advanced features for data analysis in the Oracle SQL language for rapid and efficient analysis.
Attendees will practice on a number of examples to learn how easy it is to make complex analyses without using procedural languages such as PL/SQL. The course is designed to maximize the analytical capabilities of the SQL language in Oracle databases.
Upon successful completion of this course, delegates will be able to:
- Use all forms of subqueries
- Implement advanced SQL aggregation
- Use ranking and reporting functions
- Make efficient analyses using the analytic functions of database engine
- Replace cursor-based analyses with analytic functions
- Perform what-if analyses
- Use time series and calculate period-to-period comparisons
- Make full statistical analysis of data
- Fully leverage analytic capabilities of Oracle database engine
Course Content
Basic Techniques
Nested queries in the WHERE and HAVING clause
Nested queries in the FROM clause - inline views
Operators ALL, SOME, ANY, IN and EXISTS in subqueries
NOT IN and the work with NULL values
Subqueries with multiple columns
Correlated subqueries
CASE and DECODE expressions
Subquery factoring and the WITH clause
Hierarchical queries
Advanced Aggregation
GROUPING SETS
Computing subtotals using ROLLUP
Cross-aggregations using CUBE
GROUPING, GROUP_ID and GROUPING_ID functions
Composite column aggregations
Combining groupings
Oracle SQL for Analysis
The benefits of using analytic functions over cursors
The OVER() clause
Processing order of analytic functions
Ranking functions
The ROW_NUMBER function
RANK and DENSE_RANK
The NTILE function
Partitioning of analytical datasets
Cumulative Distribution - CUME_DIST, PERCENT_RANK
The WIDTH_BUCKET function
The RATIO_TO_REPORT function
Inverse percentile - PERCENTILE_CONT and PERCENTILE_DISC functions
Working with a sliding data window (windowing functions)
Reporting mode of aggregation functions of SUM, COUNT, AVG, etc..
Computing running totals, moving averages
Physical, logical and functional data window offset
LAG and LEAD analysis
FIRST_VALUE and LAST_VALUE
NTH_VALUE
Concatenating values using the LISTAGG function
FIRST and LAST
Combining analytical and aggregation functions
Using analytical functions as aggregation functions
Advanced Data Analysis
Hypothetical rank and distribution functions (what-if analysis)
Pivoting and unpivoting
Creating histograms
Partitioned outer join
Filling gaps in data
Time series calculations
Period-to-period comparisons
Regression analysis
Statistical aggregates (median, etc...)
Cross-tab statistics
Hypothesis testing
Linear Algebra