Module 1: Introduction and Overview
The uses of SQL queries
- Why SQL can be both easy and difficult
- Recommendations for thorough testing
Enhancing query performance
- Query optimization
- Choosing the most efficient query method
Module 2: Leveraging SQL Functions to Build Queries
Aggregate functions
- Grouping in several levels
- Grouping and NULLs
- CUBE and ROLLUP
- Building crosstab reports
- Utilizing 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
Module 3: 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
Module 4: Developing Complex Joins
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
Module 5: Building Subqueries
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
Module 6: Employing 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
Module 7: Introducing Oracle 12c
- 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