0345 4506120

Developing SQL Queries for Oracle Databases

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.

Reset

Learning Objectives

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

Pre-Requisites

Requirements:

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

Software:

This course is currently running on Oracle 12c

Course Content

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

Privacy Notice

In order to provide you with the service requested we will need to retain and use your contact information in accordance with our Privacy Notice. If you choose to provide us with this information you explicitly consent to us using the information as necessary to provide the request service to you. If you do not agree please do not proceed to request the service from us.

Marketing Permissions

Would you like to receive our newsletter and other information on products and services which we think will be of interest to you by email. We will always treat your information with care and in accordance with our Privacy Notice. You are free to withdraw this permission at any time.

 

Online Courses

You may prefer an online course if you are looking for a flexible and cost-effective solution. Online courses allow you to study at your own pace, at a time that suits you.

We have the following eLearning options available:

Virtual Classroom

Virtual classrooms provide all the benefits of attending a classroom course without the need to arrange travel and accomodation. Please note that virtual courses are attended in real-time, commencing on a specified date.

Virtual Course Dates

Our Customers Include