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