Masterclass Advanced SQL – entertaining and intensive


Yesterday I presented the Masterclass Advanced SQL to an audience of very experienced Oracle Database developers – one veteran had started his career on Oracle 5 in the late 1980’s.

In a long, intensive day – 9 – 5 and beyond – we discussed many real life experiences, many practical features that made their appearance in Oracle SQL since Oracle7 was first released and many easier approaches (based on these newer features) to some well known challenges.

It is always difficult to select the real highlights of the day – also because some features were new to some and already known to others and vice versa. In general, some serious eye openers were:

  • inline views and query refactoring
  • scalar subquery
  • recursive querying (the more appropriate name for hierarchical queries)
  • rollup, pivot and unpivot
  • (new) outer join syntax and partition outer join
  • analytical functions (of course), primarily lead and lag
  • Flasback Versions (in addition to Flashback Query)
  • Regular Expressions

It is great fun to enjoy some of the happy surprises to such seasoned SQL developers – as well as learn from all of their experiences. I am looking forward to teaching this Masterclass again – as SQL is almost like a hobby to me (and presenting certainly is).

By the way: all demonstrations during this day were done using the 11gR2 XE Database, that was released last week. The only features I could not demonstrate were the Flashback  Archive and the parallellization of pipelined table functions. All pure SQL functionality is available in 11g XE.

The demo-scripts for this masterclass can be downloaded here: AdvancedSQL_MasterClass_demoscripts.


About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.