Course 264:
Advanced SQL Queries for Oracle Databases

(4 days)

 

Course Description

This course is designed for those who have basic SQL language skills. SQL is the foundation language of all relational database operations. An in-depth understanding of the language is essential in order to take full advantage of its power. In this course, you learn to use the full potential of SQL to formulate complex queries with Oracle SQL. You gain the practical skills to choose the best query method for each application to ensure accuracy and avoid common errors and pitfalls. You will apply the full range of complex query types, use the analytic OLAP functions of Oracle, and choose between Oracle and ANSI style joins.

Learning Objectives

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

Who Should Attend

Consultants, engineers, developers, analysts and others who are developing systems using Oracle databases.

Prerequisites

Course 267: Oracle Database: A Comprehensive Hands-On Introduction, or equivalent SQL knowledge is assumed.

Course Outline

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 Optimization
  • Choosing the Best Query Method

Using Advanced SQL Functions to Build Queries

  • Aggregate Functions
  • Grouping in Several Levels
  • Grouping and NULLs
  • CUBE and ROLLUP
  • 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

Performing Extensive Analysis with Analytical Functions

  • Calculating Ranks
  • RANK and DENSE_RANK
  • Partitioning in Multiple Levels
  • Computing Running Totals
  • Comparing Row and Aggregate Values
  • Defining Sliding Window Boundaries

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
  • Implementing Recursive Self-Joins with CONNECT BY
  • Applying the ANSI Standard Join Syntax
  • INNER JOIN, CROSS JOIN, LEFT, RIGHT, and FULL OUTER JOIN

Using the Set Operators

  • UNION and UNION ALL
  • INTERSECT
  • MINUS

Building Subqueries

  • Non-Correlated Subqueries
  • Multiple Row Subqueries
  • Using Correlated Subqueries
  • The EXISTS Operator
  • Subqueries in the FROM Clause
  • Factoring Subqueries for Reusability
  • Subqueries as Expressions and in the Column List

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

Please Contact Your ROI Representative to Discuss Course Tailoring!