Developing SQL Queries for Oracle Databases: Hands-On

You Will Learn How To

Formulate complex queries with Oracle SQL
Apply the full range of complex query types
Use the analytic OLAP functions of Oracle
Test SQL queries completely
Select the most efficient solution to complex SQL problems
Choose between Oracle and ANSI style joins

Course Benefits

SQL forms the cornerstone of all relational database operations. Taking full advantage of its power requires an in-depth understanding of the language. In this course, you learn to use the full potential of SQL to write complex queries for Oracle databases. You gain the practical skills to choose the best query method for each application to ensure accuracy and avoid common errors or pitfalls.

Who Should Attend

Consultants, engineers, developers, analysts and others who are developing systems using Oracle databases. Course 926, "Oracle Database 11g Comprehensive Introduction", Course 593, "Oracle Database 10g Comprehensive Introduction", or equivalent SQL knowledge is assumed.

Hands-On Training

Exercises provide you with practical experience formulating complex queries, including:

Developing and testing queries with SQL Developer or SQL*Plus
Handling NULL values in expressions and conditions
Simplifying complex expressions with virtual columns in Oracle 11g
Joining multiple tables with ANSI standard or native Oracle syntax
Applying CASE & DECODE to simulate IF...THEN...ELSE
Producing statistics and aggregate results
Embedding subqueries in expressions

Course Content

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

<< Back to Oracle Databases Course List

Related Courses

Oracle is a registered trademark of Oracle Corporation.

Oracle Databases Courses

Developing SQL Queries for Oracle® Databases: Hands-On