1-800-THE-TREE (1-800-843-8733)
TRAINING YOU CAN TRUST
 
 

Developing SQL Queries for Oracle Databases: Hands-On

An Advanced Workshop
 
Course: 234   Type: Hands-On Training   Duration: 4 Days
 
 

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

Related Courses

 
Oracle is a registered trademark of Oracle Corporation. 		 

Upcoming Dates

Dec 2 - 5, 2008
 Washington, DC (Rockville, MD)
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)
Apr 14 - 17, 2009
 Chicago (Schaumburg)
Jul 14 - 17, 2009
 New York

For complete schedule, please visit www.learningtree.com
 
http://www.lerningtree.com/courses/234pf.htm
 
 
1-800-THE-TREE (1-800-843-8733)
TRAINING YOU CAN TRUST
 
 

Developing SQL Queries for Oracle Databases: Hands-On

An Advanced Workshop
 
Course: 234   Type: Hands-On Training   Duration: 4 Days
 
 
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
 
http://www.lerningtree.com/courses/234pf.htm
 
 
1-800-THE-TREE (1-800-843-8733)
TRAINING YOU CAN TRUST
 
 

Developing SQL Queries for Oracle Databases: Hands-On Tuition

An Advanced Workshop
 
Course: 234   Type: Hands-On Training   Duration: 4 Days
 
 

Course Tuition
$ 2,790 Standard Tuition
Tuition with a Savings Plan
$ 1,800 10-Day Pass
$ 1,670 Training Passport
$ 1,700 Premium-Pass
$ 2,200 Voucher 10-Pack
$ 2,515 Alumni Gold Discount
$ 2,484 Government Discount
 

 

Your Course Tuition Entitles You To...

  • Class participation
  • Team workshops
  • Use of in-class hands-on equipment
  • Comprehensive course materials
  • Morning and afternoon refreshments
  • Course Completion Certificate awarding Continuing Education Units
  • FREE participation in Professional Certification
  • FREE participation in College Credit programs (including related exams)
  
 

Tuition Savings Plans

Training Passport
  • 3 courses in 12 months
  • As little as $1,670 per course
  • Savings as much as 40%
  • Only $5,000
Premium Pass
  • 4 courses in 24 months
  • As little as $1,700 per course
  • Save as much as 45%
10-Day Pass
  • A NEW way to save on training
  • 10 days of training for one person
  • Save as much as $990 per course
  • Only $4,500
Training Vouchers
  • Save as much as $990 per course
  • Fully transferable
  • As low as $2,200 per course
Alumni Gold Discount Attend your first course and you'll receive a personalized Alumni Gold Discount card, entitling you to save as much as $305 on each course you take within the following 12 months. Take just one course each year and you'll be entitled to ongoing discounts...year after year!

Your Guarantee of Satisfaction

Unless you feel 100% satisfied that Learning Tree delivered even more than you expected, there is no fee for your course attendance. Our Guarantee of Quality lets you experience the value of the course--and then pay only if you feel the course was well worth the tuition.

Enrolling is Easy and Flexible!

Enroll by phone or online. If your plans change, just let us know and, without a fee, you can transfer to another course or cancel your enrollment. Pay after you've taken the course, and then only if you are 100% satisfied.
 
http://www.lerningtree.com/courses/234pf.htm