• Database Administrators
• System Analysts
• Application Developers
• IT/Technical Staff

Required Prerequisites:

• SQL 1 (41021) or Introduction to Oracle: SQL and PL/SQL (41010)

Suggested Prerequisites:

• none

Course Objectives:

• Use advanced techniques to retrieve data using SET operators, correlated subqueries, and hierarchical queries
• Write SQL scripts which use SQL to generate SQL
• Use SQL*Plus to format reports

Combining Queries with Set Operators

• Describing the Set Operators
• Obeying the Set Operators Rules and Guidelines
• Including the Order by Clause

Hierarchical Retrieval

• Discussing the Benefits of the Hierarchical Query
• Ordering the Rows Retrieved by a Query in a Hierarchical Manner
• Formatting Hierarchical Data so That It Is Easy to Read
• Excluding Branches from the Tree Structure

Writing Correlated Subqueries

• Describing the Types of Problems that Can Be Solved with a Correlated Subquery
• Describing a Correlated Subquery
• Writing Correlated Subqueries
• Using the EXISTS and NOT EXISTS Operators
• Updating and Deleting Rows Using Correlated Subqueries

Reporting Using SQL*Plus

• Describing and Using the SQL*Plus SET command
• Formatting in SQL*Plus
• Computing in SQL*Plus

Writing Scripts to Generate Scripts

• Describing the Types of Problems that are Solved by Writing SQL Scripts that Generate Other SQL Scripts
• Writing and Executing a Script that Generates a Script of Drop Table Commands
• Writing and Executing a Script that Generates a Script of Insert Into Commands

• Oracle8: SQL Statement Tuning Workshop

• Oracle SQL and SQL*Plus: Advanced SELECT Statements
• Oracle SQL and SQL*PLUS: SQL and SQL*Plus Reporting