Using Oracle SQL to resolve meaningful and slightly less serious challenges is one of my favorite (semi-)professional pastimes. In the last two weeks, I have been presenting on various topics including Oracle SQL to audiences in six cities all across India as part of the OTN Yathra 2013. These presentations and the interaction with the attendees on the various capabilities of SQL have inspired me in several ways. One of the outcomes is this article – also inspired by the fairly long journey home and the many flights within India. In this article I will use several powerful options in Oracle SQL to resolve some simple to ask questions. The SQL functions I am using include:

  • Insert with Multiple Subqueries
  • Insert generating some random data
  • LISTAGG for aggregating strings
  • LEAD to produce the result for one row using information from the next
  • PIVOT to present the data in a matrix format

The statements are straightforward (relatively), the data model is simple. You will like it.

(more…)