IT Technical & End User
Course Listings

For further information, or to make a telephone booking, or to receive a competitive quotation for an in-house course, you can call Pentland Training now on 0800 328 2766.

All of our training courses can be delivered on your own site, at Pentland Training facilities, or at an off-site venue.

Hot Topics

3-day ITIL Foundation

5-day PRINCE2™ Practitioner

4-day Business Analysis

3-day Project Management Fundamentals

Course Title:
Advanced SQL

Duration: 2 days

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. 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.

Target Audience


Systems developers, DBA’s who need to know more advanced SQL and performance tuning techniques.

Prerequisites


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.

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

Enter your email address to register for our newsletter: