Oracle 9i Advanced SQL Training
Advanced SQL Queries for Oracle9i Databases - 3 Day 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 ScheduleRequest a Class |
Request More Info |
Buy Course Book |
Downloads | Online Books | Topic Summary | Print Version
Class Description:
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
