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.
No Responses