Request Info

Salutation

First Name

Last Name

Job Title

Department

Mailstop

Company

Address

P.O. Box

City

State

Zip Code

Country
List

Work Phone

Extension

E-mail

Information provided will be used to communicate with you about our products.
Privacy Statement

A representative will contact you to follow up your request.

  

Developing SQL Queries for Oracle® Databases: Hands-On

An Advanced Workshop

Course 234 • 4 Days

 E-mail E-mail  Print Print  Q&A Q&A  PDF PDF  Facebook Facebook  Twitter Twitter

Quick Enroll

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 925, "SQL Programming Language 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 234 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 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
  • Building crosstab reports
  • Utilizing 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 Database Course List
 

Related Courses

Oracle is a registered trademark of Oracle Corporation.
Learning Tree AnyWareTM is a trademark of Learning Tree International, Inc. in the United States and other countries, and is covered by a patent application pending in the United States.
  
Developing SQL Queries for Oracle Databases: Hands-On

Training Dates

Live online classroom training.Most events below can be taken in-class or live, online from your home or office with AnyWare.
Jun 12 - 15Rockville, MD enroll
Jul 31 - Aug 3New York enroll
Aug 14 - 17Reston, VA enroll
Sep 4 - 7New York enroll
Oct 2 - 5Rockville, MD enroll
Nov 27 - 30New York enroll
Dec 11 - 14Reston, VA enroll
Jan 22 - 25Rockville, MD enroll

For AnyWare enrollments, please register at least 7 days prior to the start of the course.

More Dates and Locations.

 

Tuition for Course 234 (4 Days)

Bring this or any Learning Tree Course on-site to your location!

 

 
"I am extremely satisfied with my overall experience with the Learning Tree instructor and course content. I couldn't have expected anything better."

– A. Fey
Business Analyst
Credit Suisse

CPE 23 Credits 2 Hour(s) College Credit