Oracle9i SQL Programming

Course Length: 5 days, $149 per book

This class will develop database programming skills using SQL. Relational and object concepts will be covered. Students will learn complex SQL programming skills in Oracle9i. Oracle9i database features and tools will be introduced to students.

Audience: Database administrators, application developers, and system administrators.

Prerequisites: Introduction to Oracle8i or Introduction to Oracle9I or at least six months working in an Oracle technical environment. An understanding of relational database concepts, SQL and PL/SQL programming skills are required. A solid understanding of Oracle schema is recommended.

Introduction

What is an Oracle Server?
Oracle9i Architecture
Oracle Versioning
What is SQL?
SQL Statements
SQL Standards
Built-in Data Types

SQL Programming

What is SQL*PLUS?
Starting an Oracle Session
Windows and Browser Verson of SQL*Plus
Logging into Oracle Through SQL*Plus
Using the SELECT Command
SELECT Syntax
Constants and Data Conversion
Pseudo Columns
Expressions and Aliases
DISTINCT
WHERE Clause
Arithmetic Operators
Logical Connectives
Predicates
ORDER BY Clause
GROUP BY Clause
HAVING Clause
Sales Order Entry System
Data Model

Using the Data Dictionary

Metadata and User Data
What is the Oracle Data Dictionary?
Base Tables and Static Views in the Data
Dictionary
Dynamic Views in the Data Dictionary
ALL_Views
DBA_Views
USER Views
Additional Views
V$ Views

Using Oracle's SQL*PLUS

SQL*PLUS Commands
The DESCRIBE Command
Using the SHOW Command
Modifying Session Variables with SET
The SQL Buffer
Editing System Files with SQL*PLUS
Executing SQL*PLUS Commands
Tracing Statements in SQL*Plus
Setting Up a Site or User Profile
SQL*PLUS Reports
Modifying Column Formats
Using Compute Functions
Working with Report Titles
HELP Command
Creating Web Reports Using SQL*Plus

Learning the Data Manipulation Language

The Data Manipulation Language
Modifying Data
How to Insert Data
Multi-row Insert
The Insert Command
Update Command
Deleting Data
Transaction Processing
The COMMIT Command
The ROLLBACK Command
Savepoints
Locks
Using Oracle's SQL Loader

Using SQL Functions

Functions
Using SQL Functions
Types of SQL Functions
NUMERIC Functions
Char Functions
Data Conversion Functions
DATE Functions
Miscellaneous Functions
DECODE Function
Group Functions
New ROLLUP Operators
The GROUPING Function
Analytical Functions
Full List of SQL Functions

Advanced SQL Concepts

Working with Joins
Types of Joins
Basic Join
Equi-Join
Outer Joins
Left and Right Outer Joins
Subqueries
Subquery Rules
Comparison Tests for Subqueries
Using IN and EXISTS
Using the ANY and ALL Operators
DML Commands Using Single-Row
Subqueries
Top-N Analysis
Using Set Operators
NULLS and Logic

Understanding Database Objects

Schemas
The Data Definition Language
Creating Tables
Defining DEFAULT Values
Using Explicit Defaults
The ALTER TABLE Command
Using Indexes
Query Optimization
Creating Indexes
Working with Views
Creating Views
Creating Sequences
Accessing Sequences
Creating Synonyms
Dropping Database Objects
Truncating tables
Constraints
Different Types of Constraints
Building Constraints
Primary Key Constraints
Foreign Key Constraints
Naming Constraints
Unique Constraints
Not Null Constraints
Check Constraints
Constraints can be Deferred
Enabling and Disabling Constraints
Index-Organized Table
Restrictions on Index-Organized Tables
Temporary Tables

Oracle Indexing

Index Types
B*Tree Indexes
Composite Indexes
Creating a Composite Index
Reverse Key Indexes
Bitmap Indexes
Cluster (B*Tree) Indexes
Index Organized Tables
Function-Based Indexes
Domain Indexes
Index Options
Index Maintenance
Analyzing the Index
Rebuilding the Index
Coalescing the Index
Indexes on Primary Keys
Export and Import of Indexes
SQL*Loader and Indexes

ANSI SQL Features

ANSI Join Syntax
The JOIN Statement
Cross Join
Natural Join
The USING Clause
Outer Joins
CR_EMP_SALE_TABLES.SQL Script
Full Outer Join
CASE Expression
Simple CASE Statement
Searched CASE Statement
NULLIF CASE Statement
COALESCE CASE Statement
Scalar Subqueries
Restrictions of Scalar Subqueries
The MERGE Statement
New Analytical Functions
WIDTH_BUCKET
GROUPING SETS
Grouping with Composite Columns
Using Concatenated Groupings
WITH Clause
FIRST and LAST Aggregate Sets
Summary of New SQL Features in 9i

Globalization: Date and Time Features

Daylight Savings Time Boundaries
Formats
Named Regions
New Datatypes Related to Globalization
Globalization Parameters
TIMESTAMP Literals
Using Interval and Timestamp Datatypes
Date, Time, and Interval Arithmetic
New Date and Time Types
TIMESTAMP Formats
Datetime Functions
Datetime Conversion Functions

Introduction to Security

System Privileges
Roles
Granting Privileges
Viewing System Privileges
Granting Object Privileges
Privileges and the User Group PUBLIC
Revoking System Privileges and Roles
Object Privileges
Database Authentication
Predefined Roles
Setting up a Role
Parent versus Child Roles
Working with Default Roles
Enabling and Disabling Roles
Guidelines and Standards for Roles

Object-Relational Concepts

Object-Relational databases
Object-Relational Features
Oracle Object-Relational Features
Oracle Data Types
Abstract Data Types
Data Dictionary Views
CREATE TYPE Command
Object Tables
Type Methods
The Constructor Method
Working with User-Defined Datatypes
Modifying Data in Object Tables
Working with Object Tables
Object Identifiers
Defining a REF
INSERTING REF Values
Updating and Deleting REF Values
Dangling REFS
The DEREF Operator
ROWIDs
DBMS_ROWID Package
Different Types of Rowids
LOBS

Working with LOBs

LONG and LONG RAW Datatypes
LOB Datatypes
Components of a LOB
LOB Syntax
LOB Segments
LOB Features
Working with LOB Data
Character LOB Data
Binary LOB Data
Directory Database Object
Creating Directory Objects
The DBMS_LOB Package
The Oracle Server Image Cartridge
Working with Internal LOBS
Working with BFILES
Initializing BFILE Values
Deleting LOBS
Writing to a LOB
Reading LOB Data
Temporary LOBS

Collections

Coding Examples
Create a Type Specification
CAST Operator
The TABLE Operator

External Tables

Extraction, Transformation, and Loading
External Tables
Applications of External Tables
Defining External Tables
External Table Syntax Explained
Querying External Tables
External Table Gotchas
Data Dictionary Views for External Tables
Creating External Table Scripts

Advanced Schema Features

Additional Constraint Features
Explicit Index Definition
USING INDEX Syntax
Dropping or Disabling Constraints and Indexes
Reducing Locking on Foreign Key Tables
Caching Primary Key Values
Constraints on Views
Creating a Constrained View
Index Scans and Function-Based Indexes
SELECT FOR UPDATE WAIT
Multitable INSERT Statement
Multitable INSERT Syntax
Restrictions with Multitable Inserts
Migrating LONG to LOB Values
LOB Extensions and Restrictions
Common SQL Parser

Introduction to Tuning SQL Statements

SQL Tuning
Oracle Optimizers
The Rule-Based Optimizer
The Cost-Based Optimizer
Rule-based versus Cost-based Optimization
Setting the Optimizer Mode
SQL Hints
Generating Statistics
The ANALYZE Command
DBMS_STATS
DBMS_STATS Routines
Copying Statistics to Different Databases
Monitoring Tables
Computing Statistics
Additional Cost-based Optimizer
Considerations
Stored Outlines
Creating Stored Outlines
OUTLN_PKG Package

Explain Plan and Autotrace

Explain Plan Utility
Explain Plan Table
PLAN_TABLE Columns
Explain Plan - Syntax
Explain Plan Displaying Output
Explain Plan Operation Definitions
Autotrace - Configuration
AUTOTRACE - GRANTING PLUSTRACE
ROLE
AUTOTRACE - Turning On Autotrace
AUTOTRACE - Customizing the Report

iSQL*Plus

Using iSQL*Plus
Logging in Using iSQL*Plus
Working with iSQL*Plus
The iSQL*Plus Work Screen
Configuring iSQL*Plus
Client Configuration
Proxy Server Configuration
Middle Tier Configuration
Setting up the iSQL*Plus Server
Cascading Style Sheets
Starting and Stopping the HTTP Server
Enabling or Disabling iSQL*Plus
iSQL*Plus Security
Web Browser Globalization Setup
Server and Middle Tier Globalization Setup
Running iSQL*Plus
Substitution Variables
SQL*Plus Command Summary
Unsupported SQL*Plus Commands