Developing SQL Queries for Oracle^® Databases: Hands-On

Introduction and Overview

The uses of SQL queries

• SQL's central role
• Why SQL can be both easy and difficult
• Recommendations for thorough testing

Enhancing query performance

• Query optimisation
• Choosing the best query method

Using Advanced SQL Functions to Build Queries

Aggregate functions

• Grouping in several levels
• Grouping and NULLs
• CUBE and ROLLUP
• Building crosstab reports
• Utilising the PIVOT operator in Oracle 11g
• Calculating percentiles
• Performance and grouping

Single-row functions

• String manipulation functions
• Functions for date and time manipulation
• Simulating IF...THEN...ELSE with functions
• Handling regular expressions with Oracle 10g functions

Performing Extensive Analysis with Analytical Functions

Calculating ranks

• RANK and DENSE_RANK
• ROW_NUMBER depending on ORDER BY

Extending the use of aggregates

• Partitioning in multiple levels
• Computing running totals
• Comparing row and aggregate values

Defining sliding window boundaries

• By row number
• By value
• By time interval

Developing Complex Joins

Using inner and outer joins

• Building multiple table joins
• When to use theta joins
• Grouping and joins
• Joins and performance

How and when to use self-joins

• Joining a table to itself
• Implementing recursive self-joins with CONNECT BY
• CONNECT BY and join simultaneously
• Oracle 10g enhancements to CONNECT BY

Applying the ANSI standard join syntax

• INNER JOIN
• CROSS JOIN
• LEFT, RIGHT and FULL OUTER JOIN
• Overcoming OUTER JOIN limitations
• Subtle differences between new ANSI and old Oracle style

Using the set operators

• UNION and UNION ALL
• INTERSECT
• MINUS

Building Subqueries

Noncorrelated subqueries

• Subqueries in several levels
• Subqueries that return NULL
• Multiple-row subqueries
• Multiple-column subqueries

Using correlated subqueries

• Fetching main query values
• The EXISTS operator
• Avoiding accidental correlation

Subqueries in the FROM clause

• Breaking up a complex problem into manageable pieces
• Factoring subqueries for reusability
• An alternative to views

Subqueries as expressions

• Subqueries in the column list
• Subqueries as parameters to functions
• Correlated and noncorrelated subqueries in expressions

Using Views and Temporary Tables

Overcoming obstacles with views

• Multiple group levels in one query
• How views impact performance

Temporary tables as alternatives to views

• Avoiding interference from other users
• Tailoring temporary tables