A Dev Gym Class

Analytic SQL for Developers

A free Analytic Functions course

A FREE 6 module boot camp to help you become an expert with Oracle Analytic SQL functions. The course is organized around a series of videos that teach you the concepts and syntax behind analytic functions. Following each video, take a set of quizzes to reinforce what you've learned. Got questions? Log them on the AskTOM site with the tag #AnalyticClass in the subject, and Connor McDonald of the answer team will help you out. You can also ask questions about specific quizzes here at the Dev Gym, once you've submitted your answers.
Join 12,771 students    (489 ratings)

Information

Learn Analytic Functions

Oracle Account Text

You need an Oracle Account to take a class. Click here to sign up.
Already a member of the Oracle Dev Gym? Click here to get back to work.

Course Modules

Introduction

An introduction to the analytic SQL syntax and writing your first queries to rank/sequence data in a result set.

Predicates and Partitioning

Using analytic functions as a predicate (eg, "Show me the top 5 highest salary earners") requires care to ensure the results are logically consistent.   The Partitioning clause in Analytics (not to be confused with table partitioning) is the means via which analytic expressions can be applied to logical groupings of data within a result set.

Windows

Perhaps the most powerful construct in Analytic SQL, and the most complicated to understand, is the windowing clause.  This define how broadly the set of rows and/or values that will be acted upon for an analytic expression

More on Windows, and the LAG/LEAD functions

Windows can be dynamic, have automatic defaults, and the presence of nulls can alter how a window expression will be applied. The LAG/LEAD functions are perhaps the most commonly used Analytic functions, making queries such as "compare today's sales to yesterday's sales" easy to code without unnecessary self-joins.

Problem Solving with Analytics

Now that the syntax components and function usage have been covered, we move on to applying those skills to solving specific problems using analytic functions.

Additional features and wrap up

There are several other features that fall under the umbrella of Analytical SQL within the Oracle Data Warehousing Documentation set.  The course will wrap up by covering these more esoteric features to complete your knowledge base on Analytics.

Course Reviews

"The videos are short and get the main points over. I like the way you can download the sample scripts and have a play with the code."

"Concise but comprehensive. Very useful overview of an incredible yet overlooked feature of the Oracle database that saves you hours of coding and allows you to write cleaner queries. The examples are perfect to illustrate the use cases."

"Before this I used to get scared as soon as I saw syntax like, ROW_NUMBER, OVER, PARTITION etc. Now it's safe to say I have a good practical knowledge of them."

Frequently Asked Questions

How much does this cost?

Nothing. Nada. Nil. Zilch. That's right, it's 100% FREE!

What will I learn on this course?

This course will teach you the complete suite of Analytic SQL functions, also known as Windows functions within the Oracle Database

How much time will it take?

We broke the course into 6 modules on the estimate that you could 1 module per week and it this would take up to 30 minutes per week. But you can do the modules as quickly or as slowly as you like.

What if I fall behind?

This is a self-paced course. Once registered, you have lifetime access to these materials to review at any time. So you can complete it at your leisure.

What is the format of this course?

The course is a combination of videos designed to teach you about database concepts and quizzes to test your knowledge

Do I need access to an Oracle Database?

No! This is a fully online course. The materials are designed so you can complete the course without a database.

I don't have access to an Oracle Database. Where can I practice what I've learned?

Head to LiveSQL. This is a free, browser based tool you can use to write SQL.

Will I get a certificate for completing this course?

Yes! If you complete all the exercises then we'll send you a certificate of your achivement.

Do you have SQL questions to help me practice?

Yes! Oracle Dev Gym has hundreds of SQL quizzes in its library. All free and available for you to take anytime.

Every Saturday we release a new Oracle Database quiz. This weekly database quiz is a ranked tournament, where you compete against other developers to see who can give the fastest, most accurate answers. Enter each week to see if you can take the top spot!

I already have a good working knowledge of SQL and Oracle Database. Is this course right for me?

This course is for you! Assuming nothing more than a basic knowledge of SQL, this course will super-charge your SQL skillset.
Join Analytic SQL for Developers

Instructor

Instructor

About Connor McDonald

Connor McDonald is a former Oracle ACE Director who has joined the Oracle Developer Advocates Team. He has co-authored three books, is a popular speaker at Oracle conferences around the world, and is best known as a member of the AskTOM team.

More from Connor McDonald