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
(500)
Level
Intermediate
Updated
Oct 31, 2013
Duration
4h 26m
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
Introduction 5m Why Joins Won't Work 3m Tried and True Method 2m Syntax 2m Examples and Summary 4m
Description
Course info
Rating
(500)
Level
Intermediate
Updated
Oct 31, 2013
Duration
4h 26m
Description

This 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
Scott Hecht
About the author

Scott is a statistical computer programmer specializing in SAS and SQL. An admitted dilettante, Scott occupies his time learning various programming languages such as Java, Android, C#, C, PL/SQL, R, SPSS, to name a few. Scott becomes visibly upset if you don't line up your code syntax!

More from the author
SQL Data Wrangling in Oracle: Table Data
Intermediate
5h 18m
Nov 27, 2014
Optimizing SQL Queries in Oracle
Intermediate
3h 47m
Apr 15, 2014
Introduction to Dates and Times in SQL Server
Intermediate
2h 58m
Jan 14, 2014
More courses by Scott Hecht

You're the smartest person in the room

PROVE IT

Your team won't just close the skills gap

THEY'LL HURDLE IT