Advanced SQL

Training

Inhouse

Price on request

Description

  • Type

    Training

  • Methodology

    Inhouse

  • Duration

    2 Days

The course covers new commands introduced with Oracle8i and Oracle9i. It looks at using Large Objects in the database, new Data Warehouse commands, extensions to partitioned tables and Oracle's object-oriented approach to data storage. The course also focuses on the performance tuning utilities available in the Oracle database and will enable the delegate to identify problems with their code. Suitable for: Systems developers, DBA's who need to know more advanced SQL and performance tuning techniques.

About this course

Attendees should have experience of Oracle databases and preferably experience of using SQL. Ideally delegates will have attended the Fundamentals of SQL and SQL*Plus course

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Course programme

Course Overview

This course is designed specifically for experienced SQL users who wish to use the advanced features of SQL and learn how to performance tune their queries.

Course Content

The DECODE Command

  • Producing Matrix Reports
  • Flipping a Table
  • Using MOD in DECODE
  • Columns and Computations
  • Force Row-by-Row Changes

Dynamic SQL

  • SQL Methods
  • Performing Dynamic SQL with DBMS_SQL Package
  • Using client side dynamic SQL.

Partitioned Tables

  • Range Partitions
  • Hash Partitions
  • Composite Partitions
  • Indexing Partitions
  • List Partitioning
  • Maintenance of Partitions

Data Warehousing Improvements

  • Unconditional INSERT
  • Pivoting INSERT
  • Conditional INSERT
  • Conditional FIRST INSERT
  • UPSERT Statement
  • New Joins
  • USING Clause
  • ON Clause
  • CASE Expressions
  • GROUPING SETS Clause

Oracle Objects

  • Overview of Object type,
  • Object Tables, Object Method,
  • Object Collections,
  • Object Views,
  • Object Operators.

Large Objects (LOBs)

  • Using Large Objects (LOBs)
  • LOBs, CLOBs, and BFILEs
  • Datatypes and Storage
  • Manipulating LOB Values
  • Selecting LOB Values
  • Inserting, Updating and Deleting LOBs

Tuning

  • Joins
  • Joins in General
  • Un-indexed, Indexed and Outer Joins
  • Writing Good SQL Statements
  • Data Conversion
  • LIKE, NOT, OR IN Operators
  • ORDER BY and GROUP BY
  • Query Path Ranking
  • Sub Queries
  • Indexes and MAX/MIN

SQL Trace

  • The Trace Facility
  • Enabling SQL Trace
  • Analysing Trace output

TKPROF

  • Sort Options
  • SQL Trace Statistics

Explain Plan

  • Creating the Plan Table
  • Explain Plan Syntax

Advanced SQL

Price on request