Course Description :

In an Introduction to Oracle 9i for Experienced SQL Users, students build on their knowledge of basic SQL. They learn to use advanced SQL commands incorporating the SQL command, functions and operators supported by Oracle as extensions to standard SQL. Students learn to create and maintain database objects such as tables, indexes, views, constraints, and sequences. SQL Plus and iSQL Plus commands and scripts are also explored.

Who Should Attend :

This course is designed for data processing professionals who need to learn to use powerful SQL commands from a query manager or embedded in other programs.

Pre Requisite from candidates.

Prior experience with SQL.

Course Structure

Oracle Overview

* Oracle Tools
* Terminology
* Oracle Architectural Components
* Database Objects

Structured Query Language

* Types of Statements
* SQL Syntax
* SQL Plus
* iSQL Plus
* SQL Plus and iSQL Plus Commands

Oracle SELECT statement

* Review of syntax

Single Value Functions

* Numeric Functions
* Character Functions
* Date Functions
* Conversion Functions
* Miscellaneous Functions
* Nesting Functions

Group Functions

* Review of GROUP BY and HAVING clause
* ROLLUP Operator
* CUBE Operator
* Grouping Functions
* Grouping Sets
* Composite Columns
* Concatenated Groupings

Joins

* Equi-Joins
* Non Equi-Joins
* Outer Joins
* Self Joins
* ANSI Standard Join Syntax in Oracle 9i

Subqueries

* Review of basic subqueries
* Correlated Subqueries
* Correlated Update and Delete
* Multiple Column Subqueries
* Subqueries in the FROM clause
* Scalar Subqueries
* WITH clause

Combining Results Tables using Set Operators

* UNION
* UNION ALL
* INTERSECT
* MINUS

Hierarchical Queries

* CONNECT BY and START WITH
* Using the Level Pseudo column
* Formatting Hierarchical Queries using LEVEL and LPAD
* Removing Branches with WHERE clause

Creating Database Objects

* Tables
* Constraints
* Adding Comments to Tables
* Indexes
* Views
* Synonyms

Transaction Control Statements

* COMMIT
* ROLLBACK
* Save Points
* Read Consistency
* Record Locking

Data Dictionary

* Understanding
* DBA and v$ Views
* Public Access Views
* All Views
* Users Views

SQL Plus and iSQL Plus Commands and Scripts

* Using SQL Plus and iSQL Plus Commands
* Writing SQL Plus and iSQL Plus Scripts

Security

* Overview
* Creating Users
* System Privileges
* Object Privileges
* GRANT and REVOKE