Advanced SQL Queries for Oracle 12c Databases

4 Days

Description

Learn the advanced SQL skills necessary to design and code complex queries against Oracle databases. Design, code and test the most efficient query to solve a given business problem. Fully updated for Oracle 11g.

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Advanced SQL Queries for Oracle 12c Databases class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Course Overview

1.Oracle DECODE and CASE - Conditional Logic in SQL

  • Introduction to Oracle DECODE
  • Examples
  • Oracle DECODE and Range Comparisons
  • The Oracle CASE Expression
  • Oracle CASE Syntax
  • CASE Examples
  • Workshop

2.Set Operations

  • UNION, INTERSECT and MINUS Operators
  • UNION Example
  • Key UNION Concepts
  • UNION ALL Example
  • INTERSECT Operation
  • MINUS Operation
  • Example: Compare Schemas
  • Example: Compare Tables
  • Example: Compound Set Operations
  • Workshop

3.Subqueries

  • Oracle Subquery Overview
  • Subquery Examples
  • Restrictions
  • Subquery Gotcha
  • Correlated Subqueries
  • The Need for NOT EXISTS
  • The EXISTS Operator
  • Oracle Top-N Queries - Inline Views
  • Oracle 9i Extended Subquery Support
  • Oracle Subquery Factoring - The WITH Clause
  • Workshop

4.Advanced Joins

  • Join Terminology Review
  • 3,4,5-Way Inner Joins
  • Join and GROUP BY
  • Self-Joins
  • Cartesian Product
  • Outer Joins
  • Workshop
  • Oracle ANSI Compliant Joins
  • Natural Join
  • Oracle USING Clause
  • Oracle ON Clause
  • Oracle ANSI Outer Joins
  • Oracle ANSI Full Outer Joins
  • Oracle ANSI Cross Join
  • Workshop
  • Joining to Views
  • Join Tuning Tips
  • Oracle Partition Outer Join
  • Workshop

5.Coding Queries on Partitioned Data

  • Oracle Partition Concepts
  • Partition-Independent Queries
  • Partition Pruning
  • Coding Partition-Dependent Queries in Oracle
  • Workshop

6.Grouping Data

  • Oracle Aggregate Function Overview
  • GROUP BY Example
  • Syntax Notes
  • Grouping Multiple Columns
  • Golden GROUP BY Rule
  • The HAVING Clause
  • Oracle ROLLUP Operations
  • Oracle CUBE Operations
  • Oracle GROUPING Function
  • Oracle GROUPING Example
  • Oracle GROUPING with DECODE
  • Oracle GROUPING in HAVING
  • Oracle GROUPING SETS
  • Workshop

7.Oracle Analytic Functions

  • Introduction
  • What Do They Do?
  • Getting Started with Oracle Analytic Functions
  • Oracle Partition Clause
  • Oracle Order-By Clause
  • Oracle Windowing Clause
  • Oracle Row Windows
  • Oracle Range Windows
  • Oracle Range Windows: BETWEEN
  • Oracle Range Windows: INTERVAL
  • Oracle Ranking Functions
  • Oracle Top-N Queries
  • Oracle LAG and LEAD Functions
  • Closing Thoughts
  • List of Analytic Functions
  • Resources
  • Workshop

8.Model Queries (Oracle10g)

  • Oracle10g MODEL Clause Concepts
  • Oracle10g MODEL Clause Components and Clauses
  • Oracle10g MODEL Clause Examples
  • Workshop

9.Oracle CONNECT BY - Hierarchical Queries

  • Introduction
  • Oracle CONNECT BY Example
  • LEVEL with LPAD
  • Adding WHERE Clause
  • Sort by LEVEL
  • Oracle 9i SIBLINGS Sorts
  • Oracle 9i Hierarchy Path
  • New Oracle 10g/11g Pseudocolumns
  • Workshop

10.Using Regular Expressions in Oracle SQL

  • Searching with REGEXP_LIKE
  • REGEXP_LIKE Versus LIKE
  • Regular Expression Functions (e.g. REGEXP_REPLACE)
  • Basic Elements of Expressions
  • Using Backreferences
  • Examples
  • Workshop

11.Oracle Date and Time (Temporal) Data

  • Scalar Function Review (New Oracle10g Functions)
  • Oracle Date Conversion Functions
  • Oracle TO_CHAR Examples
  • Oracle TO_DATE Examples
  • Oracle Date Arithmetic & Functions
  • Adding & Subtracting Days in Oracle SQL
  • Oracle ADD_MONTHS Function
  • Oracle LAST_DAY Function
  • Oracle TRUNC Function
  • Workshop
  • Introduction to Oracle Datetime Data
  • Oracle TIMESTAMP Data Type
  • Oracle TIMESTAMP WITH TIME ZONE Data Type
  • Oracle TIME STAMP WITH LOCAL TIME ZONE Data Type
  • Oracle Datetime Conversions
  • More Oracle Time Zone Functions
  • Workshop

12.Oracle XML DB and XMLType

  • XML in the Oracle Database
  • XMLType in Oracle Tables
  • Inserting XML Data
  • Selecting XML Data
  • Oracle XML DB EXTRACT Function
  • Oracle XML DB EXTRACTVALUE Function
  • Updating Oracle XML Data
  • PL/SQL and XML
  • Other Oracle XML Features
  • XML DB Workshop

13.Additional Oracle 10g/11g New SQL Features

  • Case Insensitive Search and Sort
  • Enclosing Quotes
  • Oracle MERGE Statement Enhancements
  • ORA_ROWSCN Pseudocolumn
  • Nested Table Enhancements
  • Temporary Table Enhancements
  • Aggregates in the RETURNING Clause
  • New Datatypes

 

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Advanced SQL Queries for Oracle 12c Databases class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Prerequisites

SQL experience is required. For example, you should be comfortable coding basic SQL SELECT statements, including the use of the WHERE clause with basic filters and simple joins. This can be obtained by attending SkillBuilders' Introduction to Oracle class.

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Advanced SQL Queries for Oracle 12c Databases class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Course Comments

This course is delivered in 4 half day sessions from 8 am to Noon U.S. Central Time Zone

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Advanced SQL Queries for Oracle 12c Databases class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date