1-800-THE-TREE (1-800-843-8733)
 

Developing SQL Queries for Oracle Databases: Hands-On

An Advanced Workshop

 
Course: 234     Type: Hands-On Training     Duration: 4 Days

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 11 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 11
  • 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
Non-correlated 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 non-correlated 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

Related Courses
Oracle is a registered trademark of Oracle Corporation.
  
 
Request More Info

Salutation

First Name

Last Name

Company

Zip Code

Country
   Codes
Work Phone

Extension

E-mail

A representative will contact you to follow up your request.
Privacy Statement

Save an EXTRA $200 on a 10-Day Training Pass!

Developing SQL Queries for Oracle Databases: Hands-On
Upcoming Dates
Oct 14 - 17, 2008
 Chicago (Schaumburg)
Nov 4 - 7, 2008
 New York
Nov 11 - 14, 2008
 Atlanta
Dec 2 - 5, 2008
 Washington, DC (Rockville, MD)
Jan 6 - 9, 2009
 New York
Jan 20 - 23, 2009
 Los Angeles
Jan 27 - 30, 2009
 Washington, DC (Reston, VA)
Mar 3 - 6, 2009
 Washington, DC (Alexandria, VA)
Mar 10 - 13, 2009
 New York
Mar 31 - Apr 3, 2009
 Washington, DC (Rockville, MD)

Developing SQL Queries for Oracle Databases: Hands-On
Bring Learning Tree On-Site

Course Tuition
$ 2,650 Standard Tuition
Tuition with a Savings Plan
$ 1,580 10-Day Pass
$ 1,665 Training Passport
$ 1,830 Flex-Pass
$ 2,095 Voucher 10-Pack
$ 2,385 Alumni Gold Discount
$ 2,360 Government Discount
 

 

Developing SQL Queries for Oracle Databases: Hands-On
Developing SQL Queries for Oracle Databases: Hands-On
Course participants developing SQL queries with Oracle SQL.
The most recent 100 evaluations scored this course at:

  (3.76/4.00)


Oracle Today


CPE 23 Credits 2 Hour(s) College Credit
Customer Service or Enroll: 1-800-843-8733