Writing Advanced SQL Queries

SQL fundamentals

• Why SQL can be both easy and difficult
• Recommendations for thorough testing

Retrieving data with SELECT

• Expressions
• Literals
• Handling NULLs properly

Executing queries

• Analysing query plans
• Enhancing query performance
• Retrieving partly results with FETCH and OFFSET
• Selecting the best alternatives
• Avoiding errors and pitfalls

Implementing various types of joins

• Inner joins
• Cross joins
• Left, right and full outer joins
• Equijoins vs. theta joins
• The performance implications of joins
• Adding filter conditions to outer joins

Writing self joins

• Joining a table to itself
• Chaining self joins
• Solving time-interval problems

Combining queries with set operators

• UNION
• UNION ALL
• INTERSECT
• EXCEPT

Summarising data with aggregate functions

• COUNT
• SUM
• AVG
• MIN
• MAX
• Managing NULLs
• identifying duplicates

Grouping data

• GROUP B
• Applying conditions with HAVING
• Calculating moving averages
• Building crosstab reports

Extending group queries

• Nesting grouped aggregates
• Joins and grouping
• Introducing subtotals with CUBE and ROLLUP

The OVER clause

• Specifying the ordering before applying the function
• Splitting the result set into logical partitions

Calculating ranks

• RANK and DENSE_RANK
• ROW_NUMBER with ordered sets
• Calculating percentiles

Extending the use of aggregates

• Partitioning in multiple levels
• Computing running totals
• Comparing row and aggregate values
• Top-N queries
• Defining sliding window boundaries

Self-contained subqueries

• Subqueries in conditions and column expressions
• Creating multilevel subqueries
• Avoiding problems when subqueries return NULLs
• Handling multirow subquery results
• Finding gaps in number series

Correlated subqueries

• Accessing values from the outer query
• EXISTS vs. IN
• Identifying duplicates
• Avoiding accidental correlation

Common table expressions

• Reusable subqueries
• Recursive subqueries
• Traversing hierarchies

• Overcoming SQL limitations
• Reducing complexity and improving performance