Advanced SQL Queries in Oracle and SQL Server

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 

Show more