Preferred method of contact:

Developing SQL Queries for Oracle Databases

COURSE TYPE

Advanced

Course Number

234

Duration

4 Days

PDF Add to WishList

Increase productivity and maximise the potential of SQL to formulate intricate queries for Oracle databases. In this training course, you learn to solve business problems easily, efficiently, and elegantly with only a few lines of SQL code, and gain the practical skills to choose the best query method — ensuring accuracy and avoiding common errors or pitfalls.

You Will Learn How To

  • Maximise 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

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 optimisation
  • 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
  • Utilising 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

Convenient Ways to Attend This Instructor-Led Course

Hassle-Free Enrolment: No advance payment required to reserve your seat.
Tuition Fee due 30 days after you attend your course.

In the Classroom

Live, Online

Private Team Training

In the Classroom — OR — Live, Online

Tuition Fee — Standard: £2095  

4 - 7 Dec (4 Days)
9:00 AM - 4:30 PM GMT
London / Online (AnyWare) London / Online (AnyWare) Reserve Your Seat

23 - 26 Apr (4 Days)
9:00 AM - 4:30 PM BST
London / Online (AnyWare) London / Online (AnyWare) Reserve Your Seat

AFTERNOON START: Attend these live courses online via Anyware

13 - 16 Nov (4 Days)
2:00 PM - 9:30 PM GMT
Herndon, VA / Online (AnyWare) Herndon, VA / Online (AnyWare) Reserve Your Seat

5 - 8 Feb (4 Days)
2:00 PM - 9:30 PM GMT
New York / Online (AnyWare) New York / Online (AnyWare) Reserve Your Seat

5 - 8 Mar (4 Days)
2:00 PM - 9:30 PM GMT
Rockville, MD / Online (AnyWare) Rockville, MD / Online (AnyWare) Reserve Your Seat

14 - 17 May (4 Days)
2:00 PM - 9:30 PM BST
Herndon, VA / Online (AnyWare) Herndon, VA / Online (AnyWare) Reserve Your Seat

6 - 9 Aug (4 Days)
2:00 PM - 9:30 PM BST
New York / Online (AnyWare) New York / Online (AnyWare) Reserve Your Seat

3 - 6 Sep (4 Days)
2:00 PM - 9:30 PM BST
Rockville, MD / Online (AnyWare) Rockville, MD / Online (AnyWare) Reserve Your Seat

Show all dates
Show fewer dates

Guaranteed to Run

When you see the "Guaranteed to Run" icon next to a course event, you can rest assured that your course event — date, time, location — will run. Guaranteed.

Private Team Training

Enroling at least 3 people in this course? Consider bringing this (or any course that can be custom designed) to your preferred location as a private team training.

For details, call 0800 282 353 or Click here »

This event has been added to your cart.

Tuition Fee

Standard

In Classroom or
Online

Standard

£2095

Private Team Training

Contact Us »

Course Tuition Fee 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 Learning Tree course exam on the last day of your course or online any time after class.

Prev
Next

Training Hours

Standard class hours:
9:00 a.m. - 4:30 p.m.

Last day class hours:
9:00 a.m. - 3:30 p.m.

Free Course Exam – Last Day:
3:30 p.m. - 4:30 p.m.

Each class day:
Informal discussion with instructor about your projects or areas of special interest:
4:30 p.m. - 5:30 p.m.

AFTERNOON START class hours:
2:00 p.m. - 9:30 p.m.


Last day class hours:
2:00 p.m. - 8:30 p.m.


Free Course Exam – Last Day:
8:30 p.m. - 9:30 p.m.


Each class day:
Informal discussion with instructor about your projects or areas of special interest
9:30 p.m. - 10:30 p.m.

- ,

Prev
Next
Chat Now

Please Choose a Language

Canada - English

Canada - Français