Objectives

Applications are becoming more demanding than before in their use of relational database systems like DB2, Oracle, MySQL, SQL Server... 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?
• Less-known SQL-syntactic features that can make existing SQL code more readable and more efficient.

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

Main topics

• Overview of the reference material for the SQL standard and of the different platforms
• Nested Selects, Common Table Expressions, and correlated references in a nested select
• Combined inner and outer joins
• Where clause: predicates, and focus on unpredictability of the null value
• OLAP functions (ranking) and aggregation functions (like sliding averages)
• The "group by" statements: grouping sets, rollup, cube
• Combining SQL statements: union, except, and intersect
• Features of the Order By clause and Fetch First
• Select From Insert, Update, Delete
• Update and Delete with correlated subqueries
• Recursive SQL