Hur föredrar du att bli kontaktad:

Developing SQL Queries for Oracle Databases

KURSNIVå

Advanced

Kursnummer

234

Längd

4 dagar

PDF Lägg till på önskelista

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.

Du får lära dig att

  • 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
  • Apply the full range of complex query types

Viktig information om kursen

    • 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

Kursinnehåll

  • 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
Visa detaljerat kursinnehåll
Visa mindre

Alternativa sätt att delta på denna Lärarledda Kurs

Delta på denna lärarledda kurs i klassrummet eller online via AnyWare.

Smidig bokning: Du behöver inte betala kursen när du bokar, utan först 7 dagar efter kursstart.

I klassrum

Live, Online

Anpassad utbildning

I klassrum — ELLER — Live, Online

Pris — Ordinarie : 27950 SEK  

Kurser i USA/Kanada, när du deltar online via AnyWare går kursen 15:00-22:30 CET (svensk tid)

25 - 28 jun ( 4 dagar)
9:00 - 4:30 EDT
Ottawa / Online (AnyWare) Ottawa / Online (AnyWare) Boka Din Kursplats

3 - 6 sep ( 4 dagar)
9:00 - 4:30 EDT
Rockville, MD / Online (AnyWare) Rockville, MD / Online (AnyWare) Boka Din Kursplats

12 - 15 nov ( 4 dagar)
9:00 - 4:30 EST
Herndon, VA / Online (AnyWare) Herndon, VA / Online (AnyWare) Boka Din Kursplats

17 - 20 dec ( 4 dagar)
9:00 - 4:30 EST
Ottawa / Online (AnyWare) Ottawa / Online (AnyWare) Boka Din Kursplats

3 - 6 mar ( 4 dagar)
9:00 - 4:30 EST
Rockville, MD / Online (AnyWare) Rockville, MD / Online (AnyWare) Boka Din Kursplats

12 - 15 maj ( 4 dagar)
9:00 - 4:30 EDT
Herndon, VA / Online (AnyWare) Herndon, VA / Online (AnyWare) Boka Din Kursplats

Kurs med startgaranti

När du ser symbolen för “Guaranteed to Run” vid ett kurstillfälle vet du att kursen blir av. Garanterat.

Anpassad utbildning

Är ni ett team på 4 eller fler som behöver samma kurs? Ett alternativ är att genomföra den på plats hos er, utifrån era behov.

For details, call 08-506 668 00 or Click here »

This event has been added to your cart.

Pris

Ordinarie

I Klassrum eller
Online

Ordinarie

27950 SEK

Anpassad utbildning

Kontakta oss »

Kurspris inkluderar:

COACHNING AV LÄRAREN EFTER KURSEN
När du kommer tillbaka till jobbet kan du boka tid för kostnadsfri coachning för att få vägledning i hur du använder dina nya kunskaper i ditt dagliga arbete.

LEARNING TREES KUNSKAPSTEST
Learning Trees kunskapstest som erbjuds i slutet av kurserna ingår i kurspriset. För certifieringstest från tredje part (ex. ITIL) tillkommer en avgift.

Prev
Next

Kurstider

Kurstid:
09:00-16:30

Tid för frivilligt kunskapstest sista kursdagen:
15:30-16:30

Övrigt:
Efter varje kursdag finns det tid för samtal med instruktören gällande frågor som är extra intressanta för just dig.

- ,

Prev
Next

Please Choose a Language

Canada - English

Canada - Français