Advanced Oracle SQL Training on Oracle 10g (4 days)

Advanced Oracle SQL Overview

The primary objective of this course is to consider advanced subjects and techniques pertaining to the SQL database language. Even professionals experienced in other implementations of the industry-standard SQL language will benefit from the advanced and Oracle-specific features of SQL discussed in this course.

Advanced Oracle SQL Course Goals

• Learn to use the SQL99 join syntax. Also, performing advanced SQL queries using techniques such as grouping and cube operations.
• Move beyond basic table definitions, consideration of defining the full range of database objects, such as advanced table definitions, indexes, sequences, views and others.
• Understand basic database security and object privileges.
• Learn to use the powerful set of built-in SQL functions.
• Learn to protect the database and extending the basic data model using declarative constraints.
• Learn to develop complex SQL*Plus reports and using SQL*Plus scripts.

Advanced Oracle SQL Course Prerequisites

Experience in the following areas is required:

• Experience with Oracle SQL as taught in our Introduction to Oracle SQL course

Advanced Oracle SQL Course Outline

1. SQL99 Join Techniques
   1. About Ansi/Iso SQL99
   2. Cross Joins
   3. Natural Joins
   4. Inner Joins
   5. Outer Joins
   6. Antijoins
   7. Using Named Subqueries
2. Enhancing Groups With Rollup & Cube
   1. About Enhanced Group Processing
   2. Using Rollup
   3. Using Cube
3. Using The Case Expression
4. SQL Functions (Character)
   1. What Are The SQL Functions?
   2. Character Functions
5. SQL Functions (Non-Character)
   1. Numeric Functions
   2. Date Format Functions
   3. Date Arithmetic Functions
   4. Null Value Functions
6. Database Objects: Relational Views
   1. About Database Objects
   2. About Relational Views
   3. Updating View Data
   4. Maintaining View Definitions
7. Database Objects: Data Dictionary Storage
   1. About The Data Dictionary
   2. Object-Specific Dictionary Views
   3. Understanding The Data Dictionary Structure
8. Database Objects: Indexes
9. Database Objects: Other Objects
   1. Creating An Application Schema
   2. More About Creating Tables
   3. Defining Sequences
   4. About Synonyms
   5. Create Schema Authorization
10. Database Security
    1. About Database Security
    2. User Id Passwords
    3. Object Security
11. Data Integrity Using Constraints
    1. About Constraints
    2. Not Null Constraint
    3. Check Constraint
    4. Unique Constraint
    5. Primary Key Constraint
    6. References Constraint
    7. Defining Constraints On Existing Tables
12. Maintaining Constraint Definitions
    1. Maintaining Constraints
    2. Renaming & Dropping Constraints
    3. Enabling & Disabling Constraints
    4. Validate New Transactions Only
    5. Deferred Enforcement
    6. Managing Constraint Exceptions
    7. Constraints And Views
    8. Data Dictionary Storage
13. Advanced SQL*Plus Reports
    1. About The Report Commands
    2. Break
    3. Compute
    4. Column
    5. Ttitle & Btitle
14. Building SQL*Plus Scripts
    1. About Script Files
    2. Using SQL*Plus Variables
    3. Accept
    4. Prompt
    5. Pause
15. Advanced SQL & SQL*Plus Features

Advanced Oracle SQL Course Materials

Each student will receive a comprehensive set of materials, including course notes and all the programming examples.

Advanced Oracle SQL Technical Requirements

1. Oracle 10g
   • Download - - Oracle Database 10g Express Edition
   1. The file you want to download is Oracle Database 10g Express Edition (Western European) - OracleXE.exe.
   2. You will need to register for a free account.
   3. Run through the Installer accepting all the defaults. Make sure to write down your password.
   4. Follow steps 1-3 of the Getting Started Guide available at the Oracle Web site
2. Editor Options
   • (Recommended) Toad for Oracle
     • Download
     • Run through the Installer accepting all the defaults.
   • SQL*Plus Instant Client
     • Download

Class Files for Advanced Oracle SQL

The class files for this course will be sent by email before the class.