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
(513)
Level
Intermediate
Updated
Oct 31, 2013
Duration
4h 25m
Table of contents
Analytic Functions - Part 1
Introduction to Part I 1m PARTITION BY Examples 5m Summary 0m What are Analytic Functions? 5m Course Introduction 3m Motivational Examples 6m RATIO_TO_REPORT Function 4m PARTITION BY Clause 2m
Analytic Functions - Part 2
FIRST_VALUE and LAST_VALUE Functions 3m ROW_NUMBER Function 3m LEAD and LAG Functions 3m LISTAGG Function 3m ORDER BY Clause 2m Introduction to Part II 1m RANK and DENSE_RANK Functions 4m LEAD and LAG Examples 2 3m LEAD and LAG Examples 1 3m Summary 0m
Analytic Functions - Part 3
NTH_VALUE Examples 2m Window Clause Syntax 8m NTH_VALUE Function 2m Summary 0m Window Clause Examples 2 5m Window Clause 6m Window Clause Examples 1 2m Introduction to Part III 1m
Analytic Functions - Part 4
Introduction to Part IV 1m PERCENTILE_CONT Function and Examples 2m MEDIAN Function and Examples 1m PERCENTILE_DISC Function and Examples 4m CUME_DIST Function and Examples 3m Summary 0m KEEP Clause Examples 4m NTILE Function and Examples 4m Statistics-Related Analytic Functions 3m PERCENT_RANK Function and Examples 2m KEEP Clause 5m
Extensions to GROUP BY
GROUP_ID Function 3m Introduction 3m A Warning about Temporary Space 2m ROLLUP 4m Composite Columns 2m GROUPING SETS 3m A Comment about SQL Server 2005 1m GROUPING Function 5m GROUPING_ID Function 3m CUBE 3m Using Multiple Extensions 3m Motivational Example 3m Summary 1m
The WITH Clause - Part 1
Summary 0m Non-Recursive WITH Clause 6m Introduction to Part I 2m CREATE TABLE and The WITH Clause 1m Using Previously Defined WITH Clauses 2m Non-Recursive WITH Syntax 1m
The WITH Clause - Part 2
MAXRECURSION Option 2m Cycle Option and Summary 4m Recursive WITH Example #3 4m SEARCH and SET Options 6m Recursive WITH Example #2 6m Introduction to Part II 4m Recursive WITH Clause 8m Recursive WITH Example #1 9m Anchor and Recursive Queries 5m
The PIVOT and UNPIVOT Features
Vintage Data Transposition Rows to Columns 2m Modern Data Transposition Rows to Columns 5m Multi-Column PIVOT Examples 2m Vintage Data Transposition Columns to Rows 5m Multi-Column PIVOT Introduction 1m Modern Data Transposition Columns to Rows 7m Introduction 2m Modern Data Transposition Introduction 0m Summary 0m Vintage Data Transposition Introduction 1m Oracle-Specific Extensions 2m Oracle Extension to IN Function 2m
The MERGE Statement
MERGE Syntax 4m Life without The MERGE Statement 1m Introduction 5m Additional Conditions 1m DELETE and Summary 2m MERGE Example 2m Reminder of Old Friends 2m
PARTITION BY/RIGHT OUTER JOIN
Introduction 5m Tried and True Method 2m Why Joins Won't Work 3m Syntax 2m Examples and Summary 4m
Description
Course info
Rating
(513)
Level
Intermediate
Updated
Oct 31, 2013
Duration
4h 25m
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 16m
Nov 27, 2014
Optimizing SQL Queries in Oracle
Intermediate
3h 45m
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