Title: Developing Advanced SQL Queries

Subtitle: Everything You Always Wanted to Know About Joins and Subqueries

Abstract: The query capabilities of SQL database servers keep expanding. Especially the features related to specifying subqueries, joins and groupings have been extended dramatically. For example, subqueries in the From and Select clause are now allowed,  new types of joins have been added, and recursive queries can be specified. The capabilities of the Group By clause have also been extended. For reporting many powerful features have been added such as rollup, cube, super-groups, and sequence numbers.

Unfortunately, out of habit, many SQL programmers and DBA's still limit themselves to simple Select statements, and are therefore not exploiting the full capabilities of their database servers. This session teaches how those new features can and should be used.

This workshop can be seen as the sequel to the workshop Introduction to SQL. It can be adapted for specific SQL products, such as Microsoft SQL Server, Oracle, IBM DB2, MySQL, SQLite, and Ingres.

Subjects

1. The Basics of SQL Queries Revisited

• The key clauses of a SQL query: Select, From, Where, Group By, Having, and Order By
• Removing duplicate rows with Distinct
• Exactly when are two rows equal?
• The special behavior of the Null value
• System variables and scalar functions
• Data types and casting of values
• Overview of aggregate functions, such as Count, Min, Max, Avg, and Sum

2. Defining Filters in the Where Clause

• Overview of the various conditions, such as between, in, and like
• The effect of the Null value on conditions
• The Case expression

3. The Group By and Having Clauses: Part 1

• Aggregating data by grouping data on one or more columns
• Group by and aggregate functions
• Grouping on expressions
• Grouping of Null values - one or different groups?
• General Rules for the Group By clause
• Using Having to filter groups

4. Sorting Rows of Query Results

• Features for sorting rows
• Returning the top or bottom X rows of a result
• The value of sorting on expressions

5. Subqueries

• Subqueries with in, any, all, and exists
• Correlated subqueries
• Subqueries in the Select clause
• How subqueries in the Select clause can simplify outer joins
• Subqueries in the From clause
• Nesting aggregate functions using subqueries in the From clause

6. The From Clause and Joins

• Implicit versus explicit joins
• Inner and outer joins
• The role of relationships between tables on
• Self-joins; joining a table with itself
• Rules for joins and the order of joins

7. The Group By and Having Clauses: Part 2

• Complex Examples with Group By
• Grouping rows on data stored in other tables
• Grouping rows like Outlook: today, yesterday, last week, last month
• How does the grouping sets option work?
• Group by with Rollup and Cube for analytics
• Grouping rows on non-existing data

8. Combining Query Results

• The Union, Intersect, and Except operators
• Automatic removing of duplicate rows
• Caculating subtotals and grand totals
• The use of artifical columns when combining query results

9. The Window Functions for Analyzing Data

• Window functions for analytical processing
• The Rownumber and Rank functions
• Partitioning of query results for reporting
• Moving averages and totals
• OLAP aggregation functions

10. The With Clause and Common Table Expressions

• Removing duplicate code from queries with the With clause
• Writing simple recursive queries with
• Recursive processing of hierarchical data structures
• Stopping endless recursive loops
• Simple graph analytics with recursive queries

11. The Scary Double Not Exists

• Using Exists or Any/All?
• The Forall pseudo-condition for developing double Not Exists
• Queries for diehards

12.Tips for Testing SQL Queries

• Verifying the correctness of the primary key of the result
• Dealing with Null Values
• Checking the correctness of joins
• When are suqbueries correct?

13. Closing Remarks