Get to Know your Local Predefined Function Library
There are a lot of interesting things you can do with straight SQL, to be certain, but Oracle (as well as SQL Server and others) also comes with a rich library of predefined functions that help extend what you can do with a single SQL statement. So, the first step toward upping your SQL game would be to familiarize yourself with your database’s predefined function library.
As a quick example, ranking is an interesting problem that comes up fairly often and can be substantially simplified with the use of a predefined function. Let’s say you want to display a list of the administrators with the top ten salaries. Some subqueries and complex SQL would get you there, certainly, but Oracle provides two ranking functions (RANK and DENSE_RANK), which can make this process much easier. Here is the syntax for the two functions:
RANK() OVER (PARTITION BY <…> ORDER BY <…>)
DENSE_RANK() OVER (PARTITION BY <…> ORDER BY <…>)
You can use either of these functions to generate the ranked list you need. The difference between RANK and DENSE_RANK has to do with the handle ties. When a list generated with RANK has two records that are tied in the ranking, the list will include a gap in the numbering to indicate the tie (e.g. if two administrators are tied for second, the list will be ranked 1,2,2,4,5,6…).If the list is generated with DENSE RANK, on the other hand, it will not include that gap in numbering (the list would be 1,2,2,3,4,5…) .
The table below demonstrates the difference:
RANK and DENSE_RANK, along with many others, are called “window functions” thanks to another incredibly handy feature they boast. In addition the standard syntax I showed you above, you can add another clause to these functions, like so:
RANK() OVER ( [PARTITION BY <…>] [ORDER BY <….>] [<window_clause>] )
The window clause allows you to use a range or condition to define a “window” within the dataset from which the function operates. In other word, you can limit the function to draw from just a subset of the data you’re dealing with. For more information about window functions and how to use them, you’ll find lots of information here.
The moral of the story here is to remember that SQL offers a lot of power and flexibility and that it’s definitely worth checking into your database’s predefined function library .
Learn PL/SQL and Build a Custom Function Library
In addition to using predefined functions, I would also recommend learning the procedural extension to whatever database you’re dealing with (PL/SQL for Oracle). Procedural extensions are full-fledged programming languages that can be leveraged to simplify complex SQL. I have been doing this a lot lately, to support projects like the Evisions Transcript Solution, our Metrics Toolkit, PowerCampus Analytics, and others.
PL/SQL is incredibly powerful, and building a library of functions will enable you to more quickly and easily modify your report output without actually modifying any reports. For example, a common problem is to determine a student’s age. In Banner, the query might look something like this:
Where spbpers_pidm = :in_pidm;
Now, you could copy and paste this snippet into all the reports that needed to calculate age. You could also rewrite all of your existing queries to incorporate this logic. However, you’re then facing an epic maintenance issue if that logic ever changes—you would have to change it, manually, everywhere it occurs.
User-defined function to the rescue! Imagine instead, that you can encapsulate the above logic in a single function that sits in your own custom function library. Now, anytime you need a student’s age, you call the function instead of including the written-out logic. If the logic changes (say you want to ROUND the age result instead of TRUNCATE), all you have to do is change it in the library and everything that uses that function is automatically updated.
As you can imagine, building up a custom function library can really make or break your institution’s reporting initiative. Another advantage is it can allow less technical users to access data elements and write reports they otherwise wouldn’t be able to, since the “difficult” SQL statements are encapsulated inside of the user-friendly functions you’ve provided them with.
Practice, as You Might Expect, Makes Perfect
The final step in your journey toward loving SQL is both the most predictable and the most important: practice, practice, practice. There are many books and websites that offer SQL training—use them. Get in touch with other developers who have been doing this for a while and ask them questions, pick their brains. Learn as much as you can, start thinking creatively about how to solve your problems and don’t give up!
How did you learn to stop worrying and love SQL? We’d love to hear all about it in the comments section below.
Like this blog?
You might also like this On Demand webinar:
Latest posts by Zach Heath (see all)
- Best Practices for Designing OLAP Cubes - 05/01/2014
- Configuration Tips for Improving your Data Security - 08/15/2013
- Getting Your Banner Black Belt, Lesson Three: Learn to Love SQL - 06/12/2013