Data Analysis with SQL: Advanced Oracle SQL Aggregations for Data Warehouse
Learn Oracle Aggregate and Analytic Functions! Six free lessons.
The original SQL language focused on providing standard selection, projection, and sorting of data. Enhancements over the years allow SQL to deliver much more robust analysis of data. This webinar investigates how LISTAGG, FIRST, LAST, Rollup, Cube, Grouping Functions, and Grouping Sets provide these capabilities, typically used in Data Warehouse environments.
After a brief review, we will show how these features allow you to concatenate values of a measure column into a single output column for a GROUP, add subtotals and grand totals to a report of summary data, extend the generation of subtotals across multiple dimensions, provide meaningful labels for subtotals and grand total rows, and limit subtotals to only those that you need for your reporting. These features are particularly useful to generate reports (from a data warehouse) tailored to specific user needs without having to use inefficient workarounds.
Audience: Oracle and APEX developers who want to improve their SQL skills. A basic foundation in SQL is required.
This free SQL training is segmented into several separate lessons:
- Introduction to Oracle LISTAGG Function (4:12) (click on video below)
In part 1, OCA Geoff Wiland introduces the LISTAGG function and provides a brief review of the GROUP BY clause. If you do not need a refresher on GROUP BY, start with Lesson 2.
- Oracle LISTAGG Examples and Demonstration (8:46)
In this lesson Geoff dives into the explanation and demonstration of the LISTAGG aggregate function.
- How to Code FIRST and LAST Aggregate Functions (6:35)
Learn what the FIRST and LAST functions do and see an example of how to code.
- How to use ROLLUP and CUBE (6:11)
Learn how the Oracle Database SQL extensions ROLLUP and CUBE provide additional aggregations for your data warehouse data.
- Using the GROUPING Function (5:53)
Learn how to mask or mark your ROLLUP and CUBE aggregate rows so you can distinguish data rows from “super” aggregate rows, i.e. the rows produced by the ROLLUP or CUBE functions.
- Using GROUPING SETS (2:24)
Oracle Database offers the GROUPING SETS extension to provide an efficient technique for getting just the aggregates you want from your data warehouse.
Date: Aug 1, 2016
NOTE: Some corporate firewalls will not allow videos hosted by YouTube.