|Analytic SQL for Developers|
|Written by Nikos Vaggalis|
|Tuesday, 29 September 2020|
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!
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
Module 2: Predicates and Partitioning
Module 3: Windows
Module 4: More on Windows, and the LAG/LEAD functions
Module 5: Problem Solving with Analytics
Module 6: Additional features and wrap up
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.
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.
or email your comment to: firstname.lastname@example.org
|Last Updated ( Tuesday, 29 September 2020 )|