Oracle Training Overview
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, 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.
Oracle Training Prerequisites
Oracle, SQL, SQL*Plus and PL/SQL experience are required. This can be obtained by attending Introduction to Oracle for Developers class (5 days).
Oracle Training Course duration
3 Days
Oracle Training Objectives
After successfully completing this course, you will 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
Oracle Training Course outline
- 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
- EXISTS Operator
- 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
- Intro to 9i Datetime
- Data Type: TIMESTAMP
- TIMESTAMP WITH TIME ZONE
- TIME STAMP WITH LOCAL TIME ZONE
- Datetime Conversions
- More Time Zone Functions
- Date Arithmetic & Functions
- Adding & Subtracting Days
- ADD_MONTHS Function
- LAST_DAY Function
- TRUNC Function
- New SQL built-in 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
Hardware/Software Requirements
- Hardware Requirements
- Minimal Processor: Pentium 166 or Pentium 200
- Recommended Processor: Pentium 233 or Pentium 266
- RAM: 128 MB (256 MB recommended)
- Oracle Corporation also recommends increasing virtual memory to:
- Minimum 200 MB, Maximum 400 MB
- Modify it in the Performance tab of System Properties in the Control
Panel
- Available Disk Space
- FAT 4.5 GB for HOME Directory, 140MB for System Drive
- NTFS 2.75 GB for HOME Directory, 140MB for System Drive
- Temp Space - 400 MB
- Video: 256 color
- A connection to the Internet is desirable (but not absolutely necessary)
to show students various Oracle-related sites.
- Software Requirements
- Windows NT 4.0 (SP5 or Higher) or Windows 2000 (SP 1 or higher)
- Oracle9i Enterprise Edition. Oracle8i can be used, but some workshops
will be omitted from the class.
- A new install is strongly recommended if the existing Oracle install is
on a freely accessible machine, such as classroom machine.
- Microsoft Internet Explorer 5 (or later) or Netscape Navigator 4.5
- Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)
- Student Files
- Text Editor*
|
