Advanced SQL Queries in Oracle and SQL Server
By Scott Hecht
This course details recent additions to the standard SQL language and includes topics such as Analytic Functions and Extensions to GROUP BY, to name a few.
Course info
Rating
Level
Intermediate

Updated
Oct 31, 2013

Duration
4h 25m

Table of contents
Analytic Functions - Part 1
Course Introduction
3m
Introduction to Part I
1m
What are Analytic Functions?
5m
Motivational Examples
6m
PARTITION BY Clause
2m
PARTITION BY Examples
5m
RATIO_TO_REPORT Function
4m
Summary
0m
Analytic Functions - Part 2
Introduction to Part II
1m
ORDER BY Clause
2m
ROW_NUMBER Function
3m
LISTAGG Function
3m
LEAD and LAG Functions
3m
LEAD and LAG Examples 1
3m
LEAD and LAG Examples 2
3m
RANK and DENSE_RANK Functions
4m
FIRST_VALUE and LAST_VALUE Functions
3m
Summary
0m
Analytic Functions - Part 3
Introduction to Part III
1m
Window Clause
6m
Window Clause Syntax
8m
Window Clause Examples 1
2m
Window Clause Examples 2
5m
NTH_VALUE Function
2m
NTH_VALUE Examples
2m
Summary
0m
Analytic Functions - Part 4
Introduction to Part IV
1m
KEEP Clause
5m
KEEP Clause Examples
4m
Statistics-Related Analytic Functions
3m
MEDIAN Function and Examples
1m
NTILE Function and Examples
4m
CUME_DIST Function and Examples
3m
PERCENT_RANK Function and Examples
2m
PERCENTILE_DISC Function and Examples
4m
PERCENTILE_CONT Function and Examples
2m
Summary
0m
Extensions to GROUP BY
Introduction
3m
A Comment about SQL Server 2005
1m
A Warning about Temporary Space
2m
Motivational Example
3m
GROUPING SETS
3m
ROLLUP
4m
CUBE
3m
Composite Columns
2m
Using Multiple Extensions
3m
GROUPING Function
5m
GROUPING_ID Function
3m
GROUP_ID Function
3m
Summary
1m
The WITH Clause - Part 1
Introduction to Part I
2m
Non-Recursive WITH Clause
6m
Non-Recursive WITH Syntax
1m
CREATE TABLE and The WITH Clause
1m
Using Previously Defined WITH Clauses
2m
Summary
0m
The WITH Clause - Part 2
Introduction to Part II
4m
Recursive WITH Clause
8m
Anchor and Recursive Queries
5m
Recursive WITH Example #1
9m
Recursive WITH Example #2
6m
Recursive WITH Example #3
4m
MAXRECURSION Option
2m
SEARCH and SET Options
6m
Cycle Option and Summary
4m
The PIVOT and UNPIVOT Features
Introduction
2m
Vintage Data Transposition Introduction
1m
Vintage Data Transposition Columns to Rows
5m
Vintage Data Transposition Rows to Columns
2m
Modern Data Transposition Introduction
0m
Modern Data Transposition Columns to Rows
7m
Modern Data Transposition Rows to Columns
5m
Oracle-Specific Extensions
2m
Multi-Column PIVOT Introduction
1m
Oracle Extension to IN Function
2m
Multi-Column PIVOT Examples
2m
Summary
0m
The MERGE Statement
Introduction
5m
Reminder of Old Friends
2m
Life without The MERGE Statement
1m
MERGE Syntax
4m
MERGE Example
2m
Additional Conditions
1m
DELETE and Summary
2m
PARTITION BY/RIGHT OUTER JOIN
Description
Course info
Rating
Level
Intermediate

Updated
Oct 31, 2013

Duration
4h 25m

Description
This Oracle advanced SQL course provides an overview of the recent additions to the SQL language that have been added over the last few years, but that may have slipped under your radar.
We will describe;
- 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.
About the author