Introducing a free, self-paced, quick and official course, one of of Oracle's Dev Gym quizzes, about the concepts and syntax behind SQL's Analytic functions.

Oracle's Dev Gym quizzes, a collection of workouts and classes, offer a form of "active learning" recognizing the idea that to get better at writing code, you actually need to write code - and also read and understand code.So these quizzes are a mix of video and code tutorials that incorporate an interactive playground where you can tweak and run the tutorial's attached code in order to strengthen your code comprehension skills and learn in depth the subject at hand.To join you just need an Oracle account, which is free too.

Analytic functions should be the next step of every SQL developer's career; it's the natural progression after you've managed joins, sub-queries, aggregate functions, inline views, CTE's and such.

It's also true that Analytic SQL is surrounded by a veil of mystery, and often seems something too difficult to get your head around. If that's the case with you, fear not! Connor McDonald, former Oracle ACE Director, does his best to demystify the analytics part of the SQL in this Dev Quiz series. And he does that with two minute sessions that do not simply present the  syntax, but actually solve real-world problems.

The scenario is that you've got a manager who asks you to fetch him all kinds of information from your database, and you have to use your analytics skills to do that. Of course, you could arrive at the same results without using the analytics, but the code you'd write would be gruesome!

Example request:

Solution without analytics :

Solution with analytics :

The schema, data to enter and the analytic query related to the request are available as a Live SQL package, that is as a playground (like SQL Fiddle) that runs inside your browser where you can run and experiment with the example code. At the end of each module, there's also a follow up with  multiple choice questions which aim to make the comprehension of the material better.

Now let's get to the modules:

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

Module 2: 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.

Module 3: 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

Module 4: 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.

Module 5: 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.

Module 6: 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.

In brief, Module 1 goes over ranking rows with Rank,Dense Rank,Row_Number etc, Module 2 goes over the Partition clause, Module 5 brings all together  while Module 6 is on esoteric features targeting Oracle Data Warehousing.

To wrap up, Analytic SQL for Developers was an easy-going course that went over a difficult subject. I can confirm that it succeeded in its aim, that is revealing the "dark" secrets of SQL's Analytic functions.

More Information

Analytic SQL for Developers

Related Articles

Connecting To The Outside World with Perl and Database Events

SQL Workshop – Having Clause With NULLs

SQL Workshop - Removing Duplicate Rows

SQL Workshop - Subselects And Join

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info