Developing SQL Queries for Oracle Databases

Aggregate functions

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

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

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

Creating inner and outer joins

• Building multiple table joins
• Grouping and joins

How and when to use self-joins

• 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
• Adding filter conditions to OUTER JOINS
• Differences between new ANSI and old Oracle style

Manipulating the set operators

• UNION and UNION ALL
• INTERSECT
• MINUS

Noncorrelated subqueries

• Subqueries that return NULL
• Multiple row subqueries
• Multiple column subqueries

Handling 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
• Applying recursive factored subqueries in Oracle 11g R2

Subqueries as expressions

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

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

• Limiting the number of rows returned with FETCH
• Retrieving partly results with OFFSET
• Simulating joins with lateral views and APPLY
• Declaring local functions in the WITH clause