Type to search LearningTree.com

Do you mean "{{response.correctedQuery}}" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.

 

Oracle Databases Training









Preferred method of contact?

Developing SQL Queries for Oracle Databases

COURSE TYPE

Advanced

Course Number

234

Duration

4 Days

Enroll

About This Course: 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 to ensure accuracy and avoid common errors or pitfalls.

You Will Learn How To

  • Maximize the potential of SQL to formulate complex queries
  • Thoroughly test SQL queries and avoid common errors
  • Select the most efficient solution to complex SQL problems
  • Leverage the analytical functions of Oracle
  • Employ inner joins, outer joins, cross joins and self joins
  • Apply the full range of complex query types

Important Course Information

  • Requirements:

    SQL and Oracle RDBMS knowledge at the level of: Course 925, SQL Programming Language Introduction, or Course 2226, Oracle Database 12c Introduction

    Software:

    This course is currently running on Oracle 12c

Course Outline

  • Introduction and Overview

The uses of SQL queries

  • Why SQL can be both easy and difficult
  • Recommendations for thorough testing

Enhancing query performance

  • Query optimization
  • Choosing the most efficient query method
  • Leveraging 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

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
  • Developing Complex Joins

Creating inner and outer joins

  • Building multiple table joins
  • Grouping and joins

How and when to use self-joins

  • 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
  • Adding filter conditions to OUTER JOINS
  • Differences between new ANSI and old Oracle style

Manipulating the set operators

  • UNION and UNION ALL
  • INTERSECT
  • MINUS
  • Building Subqueries

Noncorrelated subqueries

  • Subqueries that return NULL
  • Multiple row subqueries
  • Multiple column subqueries

Handling 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
  • Applying recursive factored subqueries in Oracle 11g R2

Subqueries as expressions

  • Subqueries as parameters to functions
  • Correlated and noncorrelated subqueries in expressions
  • Employing 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
  • Introducing Oracle 12
  • Limiting the number of rows returned with FETCH
  • Retrieving partly results with OFFSET
  • Simulating joins with lateral views and APPLY
  • Declaring local functions in the WITH clause
Show complete outline
Show Less

Course Schedule

Attend this live, instructor-led course In-Class or Online via AnyWare.

Hassle-Free Enrollment: No advance payment required.
Tuition due 30 days after your course.

Jan 24 - 27 Ottawa/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Feb 7 - 10 Herndon, VA/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Feb 21 - 24 New York/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Mar 7 - 10 Rockville, MD/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Apr 18 - 21 AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare

Jun 6 - 9 Herndon, VA/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Jun 20 - 23 New York/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Jul 25 - 28 Ottawa/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Sep 5 - 8 Rockville, MD/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Oct 3 - 6 Herndon, VA/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Oct 10 - 13 AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare

Oct 17 - 20 New York/AnyWare Enroll Now

How would you like to attend?

Live, Online via AnyWare
In-Class

Guaranteed to Run

Show all dates
Show fewer dates

Bring this Course to Your Organization and Train Your Entire Team
For more information, call 1-888-843-8733 or click here

Tuition

Standard

$2990

Government

$2659

Course Tuition Includes:

After-Course Instructor Coaching
When you return to work, you are entitled to schedule a free coaching session with your instructor for help and guidance as you apply your new skills.

Free Course Exam
You can take your course exam on the last day of your course and receive a Certificate of Achievement with the designation "Awarded with Distinction."

Prev
Next

Questions

Call 1-888-843-8733 or click here »

An experienced training advisor will happily answer any questions you may have and alert you to any tuition savings to
which you or your organization may be entitled.

Training Hours

Standard Course Hours: 9:00 am – 4:30 pm
*Informal discussion with instructor about your projects or areas of special interest: 4:30 pm – 5:30 pm


FREE Online Course Exam (if applicable) – Last Day: 3:30 pm – 4:30 pm
By successfully completing your FREE online course exam, you will:

  • Have a record of your growth and learning results.
  • Bring proof of your progress back to your organization
  • Earn credits toward industry certifications (if applicable)
  • Make progress toward one or more Learning Tree Specialist & Expert Certifications (if applicable)

Enhance Your Credentials with Professional Certification

Learning Tree's comprehensive training and exam preparation guarantees that you will gain the knowledge and confidence to achieve professional certification and advance your career.

Earn 23 Credits from NASBA

This course qualifies for 23 CPE credits from the National Association of State Boards of Accountancy CPE program. Read more ...

“I am extremely satisfied with my experience with the Learning Tree instructor and course content. It couldn’t have been better!”

- A. Fey, Business Analyst
Credit Suisse

Prev
Next
s