Objectives

Applications are becoming more demanding than before in their use of relational database systems likeDB2, Oracle, SQLServer... Also the SQL used by datawarehouse BI tools (e.g. Business Objects) and ERP solutions (e.g. SAP, Siebel) is becoming more advanced. Questions that arise in this context are:

• How will we treat these new - often dynamic - SQL statements?
• How to write SQL queries which are as readable and as maintainable as possible?
• Can we take advantage of new SQL features?
• Are there new features that can make existing SQL code more readable and more efficient?

To start with, this course will give an overview of the new standard SQL functionalities. We will show that using them may have an important positive impact on your system.

Main topics

• Overview of reference material for the SQL standard
• Nested Select, Correlated references in nested select
• Inner and outer joins
• Where clause: predicates, and focus on unpredictability of the null value
• OLAP functions and new group by statements: grouping sets, rollup, cube
• Pivoting tables
• Combining SQL statements: Union, except, and intersect
• Features of the Order By clause
• Nested Selects and Common Table Expressions
• Select From Insert, Update, Delete
• Recursive SQL