Share Description
In this course, you will learn :
- Analytic Functions
- Extensions to GROUP BY
- The WITH Clause (Oracle's Subquery Factoring Clause/SQL Server's Common Table Expressions)
- The PIVOT and UNPIVOT Features
- The MERGE Statement
- And, finally, the PARTITION BY/RIGHT OUTER JOIN syntax.
Syllabus :
1. Analytic Functions - Part 1
- What are Analytic Functions?
- Motivational Examples
- PARTITION BY Clause
- PARTITION BY Examples
- RATIO_TO_REPORT Function
2. Analytic Functions - Part 2
- ORDER BY Clause
- ROW_NUMBER Function
- LISTAGG Function
- LEAD and LAG Functions
- LEAD and LAG Examples
- RANK and DENSE_RANK Functions
- FIRST_VALUE and LAST_VALUE Functions
3. Analytic Functions - Part 3
- Window Clause
- Window Clause Syntax
- Window Clause Examples
- NTH_VALUE Function
- NTH_VALUE Examples
4. Analytic Functions - Part 4
- KEEP Clause
- KEEP Clause Examples
- Statistics-Related Analytic Functions
- MEDIAN Function and Examples
- NTILE Function and Examples
- CUME_DIST Function and Examples
- PERCENT_RANK Function and Examples
- PERCENTILE_DISC Function and Examples
- PERCENTILE_CONT Function and Examples
5. Extensions to GROUP BY
- A Comment about SQL Server 2005
- A Warning about Temporary Space
- Motivational Example
- GROUPING SETS
- ROLLUP
- CUBE
- Composite Columns
- Using Multiple Extensions
- GROUPING Function
- GROUPING_ID Function
- GROUP_ID Function
6. The WITH Clause - Part 1
- Non-Recursive WITH Clause
- Non-Recursive WITH Syntax
- CREATE TABLE and The WITH Clause
- Using Previously Defined WITH Clauses
7. The WITH Clause - Part 2
- Recursive WITH Clause
- Anchor and Recursive Queries
- Recursive WITH Example
- MAXRECURSION Option
- SEARCH and SET Options
- Cycle Option and Summary
8. The PIVOT and UNPIVOT Features
- Vintage Data Transposition Introduction
- Vintage Data Transposition Columns to Rows
- Vintage Data Transposition Rows to Columns
- Modern Data Transposition Introduction
- Modern Data Transposition Columns to Rows
- Modern Data Transposition Rows to Columns
- Oracle-Specific Extensions
- Multi-Column PIVOT Introduction
- Oracle Extension to IN Function
- Multi-Column PIVOT Examples
9. The MERGE Statement
- Reminder of Old Friends
- Life without The MERGE Statement
- MERGE Syntax
- MERGE Example
- Additional Conditions
- DELETE and Summary
10. PARTITION BY/RIGHT OUTER JOIN
- Why Joins Won't Work
- Tried and True Method
- Syntax
