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.
Audience: Software developers, Programmers, Application Designers.
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