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
(545)
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
Introduction 5m Why Joins Won't Work 3m Tried and True Method 2m Syntax 2m Examples and Summary 4m
Description
Course info
Rating
(545)
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.

    • If you aren't familiar with Oracle or Oracle databases, you may want to take this course beforehand.

Course FAQ
Course FAQ
What are advanced SQL queries used for?

These advanced queries will help you to manipulate, clean, and analyze your data more effieiencly than ever before.

Who is this advanced SQL course for?

This course is ideal for advanced Oracle and SQL users who want to learn about all the newest functionality. Beginners may be more interested in our Oracle Database Fundamentals course.

What advanced SQL queries will I learn in this course?

This course will describe Analytics Functions, GROUP BY, WITH, PIVOT and UNPIVOT, MERGE, and many more.

What software is required for this advanced SQL course?

You will want to have the latest version of Oracle Database and a client software such as Toad or the Oracle SQL developer tool. You will also want to have the latest version of Microsoft SQL Server Database and its client tool - SQL Servers Management Studio.

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