SkillBuilders: Advanced SQL Queries for Oracle9i Databases Outline

Oracle 9i Advanced SQL Training

Advanced SQL Queries for Oracle9i Databases - 21 Hour Online Class or 3 Day Classroom Workshop

Design and code complex Oracle SQL queries and applications to solve enterprise business problems. Especially for developers, programmers and application designers experienced with Oracle 9i, SQL, SQL*Plus and PL/SQL. Covers DECODE and CASE; Subqueries; Grouping data; CONNECT BY; advanced Joins; Set Operations; Concurrency control; Transaction control; Date and Time functions; alternatives to Tables and Oracle 9i new features.

Online Class Schedule

Request a Class

Request More Info

Buy Course Book

(4.8 out of 5)

Topic Summary | Print Version

Class Description:

This course will provide experienced Oracle developers the advanced skills necessary to design and code the complex queries and applications required to solve enterprise business problems.

You will learn to incorporate many advanced techniques such as the data warehouse functions CUBE and ROLLUP, DECODE, ANSI-compliant inner and outer joins, CONNECT BY (hierarchical queries), SET operations, correlated subqueries and other advanced subquery techniques. Oracle9i features such as flashback query, external tables and MERGE will also be covered.

You will also learn the transaction control rules implemented by Oracle, and how to work within those rules.

Finally, alternative table structures such as views, materialized views and temporary tables will be presented.

Hands-on workshops will reinforce code examples presented.

Books that may help you learn

Audience: Software developers, Programmers, Application Designers.

Prerequisites: Oracle, SQL, SQLPlus and PL/SQL experience are required.* This can be obtained by attending SkillBuilders' Introduction to Oracle for Developers class (5 days).

Objectives: After successfully completing this course, you wil be able to:

Design, code and test the most efficient query for a given business problem
Understand and code for Oracle's transaction control rules
Effectively incorporate the following techniques into your queries:

Multi-level grouping
CUBE and ROLLUP data warehouse functions
Scalar (row) functions DECODE function
CONNECT BY
Advanced join techniques include inner, outer and cross joins
Ranking functions
Complex subqueries

Make use of the new Oracle9i features such as:

External Tables
Flashback Query
Merge ("upsert") operations

Look Inside This Book:

Topic Summary

DECODE Function and the 9i CASE Expression
- Introduction to DECODE
- Simple Example
- Flip Table on Side
- DECODE and Range Comparisons
- The CASE Expression
- CASE Syntax
- CASE Nonsense
- Histograms with CASE.
- Workshop
Subqueries
- Subquery
- Subquery Example
- Subquery Restrictions
- Subquery Rules
- Subquery IN Operator
- Subquery Gotcha
- The Need for NOT EXISTS
- Another Example
- Top-N Queries
- 9i Subquery Support
- Subquery Factoring Clause
- Workshop
Grouping Data
- Aggregate Functions
- GROUP BY Example
- Syntax Notes
- Grouping Multiple Columns
- Golden GROUP BY Rule
- Where Clause Restrictions
- HAVING Clause
- ROLLUP Operations
- Partial ROLLUP
- CUBE Operations
- GROUPING Function
- GROUPING Example
- GROUPING with DECODE
- GROUPING in HAVING
- GROUPING SETS.
- Workshop
Analytic Functions
- Introduction
- What Do They Do?
- Getting Started
- Syntax Notes
- Partition Clause
- Order-By Clause
- Windowing Clause
- Row Windows
- Range Windows.
- Range Windows: BETWEEN.
- Range Windows: INTERVAL.
- Ranking Functions
- Top-N Queries
- LAG and LEAD
- Closing Thoughts
- List of Functions
- Resources
- Workshop
CONNECT BY
- Introduction
- CONNECT BY Example
- LEVEL with LPAD
- Adding WHERE Clause
- Sort by LEVEL
- 9i SIBLINGS Sorts
- 9i Hierarchy Path
- Workshop
Advanced Joins
- Join Terminology
- 3,4,5 Way Joins
- Join and GROUP BY
- Self-Joins
- Cartesian Product
- Outer Joins
- Workshop A
- Oracle9i ANSI Compliant Joins
- Natural Join
- USING Clause
- ON Clause
- ANSI Outer Joins
- Full Outer Joins
- ANSI Cross Join
- Workshop B
- Joining to Views
- Join Tuning Tips
Set Operations
- UNION, INTERSECT and MINUS Operators
- UNION Example
- Key Concepts
- UNION ALL Example
- INTERSECT Operation
- MINUS Operation
- Compare Schemas
- Compare Tables
- Compound Set Operations
- Workshop
Concurrency Control
- Introduction to Locks
- Row Lock Architecture
- Basic Locking Rules
- DML Locks
- DDL Locks
- Locking Issues: Lost Update
- Locking Issues: Blocking
- Locking Issues: Deadlocks
- Deadlocks: Cause and Fix
- Workshop
Transaction Control
- Transaction Review
- Supported Statements
- Statement Level Read Consistency
- Transaction Level Read Consistency
- Oracle Isolation Levels
- Phantom and Non-Repeatable Reads
- Read Committed
- Serializable
- Read Only
- Savepoints
- Implicit Commits
- Workshop
Date and Time Functions
- Scalar Function Review
- Date Conversion Functions
- TO_CHAR Examples:
- character to date
- TO_DATE Examples
- Date Arithmetic & Functions
- Adding & Subtracting Days
- ADD_MONTHS Function
- LAST_DAY Function
- TRUNC Function
- Workshop
- Intro to 9i Datetime
- Data Type: TIMESTAMP
- TIMESTAMP WITH TIME ZONE
- TIME STAMP WITH LOCAL TIME ZONE
- Datetime Conversions
- More Time Zone Functions
- Workshop
Alternatives to Tables
- Temporary Table Concepts
- Creating Temporary Tables
- Temporary Table Limitations
- Workshop A
- What is a Materialized View?
- M-view Concepts
- Creating a Materialized View
- Seeing the Effect
- Indexing M-Views
- Workshop B
Other New Oracle9i New Features
- Flashback Queries - Concepts
- Flashback Queries - Use
- Flashback Tips
- Flashback Limitations
- Workshop A
- MERGE statement
- Workshop B
- External Tables - Concepts
- External Tables - Use
- Workshop C

Oracle 9i Advanced SQL Training

Advanced SQL Queries for Oracle9i Databases - 21 Hour Online Class or 3 Day Classroom Workshop

Online Class Schedule

Request a Class

Request More Info

Buy Course Book

Topic Summary | Print Version

Class Description:

This course will provide experienced Oracle developers the advanced skills necessary to design and code the complex queries and applications required to solve enterprise business problems.

You will also learn the transaction control rules implemented by Oracle, and how to work within those rules.

Finally, alternative table structures such as views, materialized views and temporary tables will be presented.

Hands-on workshops will reinforce code examples presented.

Books that may help you learn

Audience: Software developers, Programmers, Application Designers.

Prerequisites: Oracle, SQL, SQLPlus and PL/SQL experience are required.* This can be obtained by attending SkillBuilders' Introduction to Oracle for Developers class (5 days).

Objectives: After successfully completing this course, you wil be able to:

Look Inside This Book:

Topic Summary

To Top | Topic Summary

Account Login:

©2003-2009
SkillBuilders.com
888.803.5607

Oracle 9i Advanced SQL Training

Advanced SQL Queries for Oracle9i Databases - 21 Hour Online Class or 3 Day Classroom Workshop

Online Class Schedule

Request a Class

Request More Info

Buy Course Book

Topic Summary | Print Version

Class Description:

This course will provide experienced Oracle developers the advanced skills necessary to design and code the complex queries and applications required to solve enterprise business problems.

You will also learn the transaction control rules implemented by Oracle, and how to work within those rules.

Finally, alternative table structures such as views, materialized views and temporary tables will be presented.

Hands-on workshops will reinforce code examples presented.

Books that may help you learn

Audience: Software developers, Programmers, Application Designers.

Prerequisites: Oracle, SQL, SQL*Plus and PL/SQL experience are required. This can be obtained by attending SkillBuilders' Introduction to Oracle for Developers class (5 days).

Objectives: After successfully completing this course, you wil be able to:

Look Inside This Book:

Topic Summary

To Top | Topic Summary

Account Login:

©2003-2009 SkillBuilders.com 888.803.5607

Prerequisites: Oracle, SQL, SQLPlus and PL/SQL experience are required.* This can be obtained by attending SkillBuilders' Introduction to Oracle for Developers class (5 days).

©2003-2009
SkillBuilders.com
888.803.5607