Back to Search Results

Training Course Summary:

This 5 day Oracle training course introduces this new version of SQL, covering the SQL*Plus toolkit and Oracle 10g RDBMS contextual operating environment.

It is an essential building block for all users of Oracle's full technology range, including database technology, development tools, and web technologies. SQL*Plus, the primary interface to the Oracle Database server, provides a powerful yet easy-to-use environment for querying, defining, and controlling data.

The course comprehensively introduces the ANSI standard features of the Oracle 10g SQL implementation.

SQL*Plus Enhancements in Oracle Database 10g include:-

Whitespace Support in Windows Path and File Names
New Glogin, Login and Predefined Variables
APPEND, CREATE, and REPLACE extensions to SPOOL and SAVE commands
SHOW RECYCLEBIN and some additional Miscellaneous Enhancements

Pre-Requisites:

PC Fundamentals

Who Should Attend:

Oracle Analyst Programmers
Oracle Technicians
Oracle DBA's
Oracle Developers
ANSI SQL Users

Training Course Overview/Content:

Introducing Oracle
The Elements of the Oracle System
Relational Concepts
back to top
SQL*Plus and SQL
Introduction
Logging on to SQL*Plus and Leaving SQL*Plus
Choosing and Describing Tables
Elements of the SQL Query
The SQL Buffer and Query Layout
Editing SQL Statements
The System Dummy Table
Selecting Columns
Duplicate Information (DISTINCT)
Sorting Information
back to top
SQL Functions
The Concatenation Operator
Elements of the SQL Query: Arithmetic
Column Aliases
String Functions
Arithmetic Functions
Date Functions
Mixed Functions
back to top
Advanced SQL Functions
Nesting Different Functions

SQL Bar Charts
Decode Crosstab
Decode with ">", "<" & "="
Select with Minus Union and Intersect
Connect By Prior

Filtering Data Using Where
Where Operators
Where with Keywords
Where and Logical Operators
Where and Soundex

Retrieve Data: Multiple Tables
Joining Tables (Equi-Joins)
Aliases for Table Names
Thinking behind creating Joins
Joining Tables (Non-Equi-Joins)
Joining Tables (Outer Joins)
Joining Tables (Inner Joins)

Group By Functions
Group By Function Examples
Group By Functions with Having

Object Support
Object - relational database
Oracle Object Type definition
Nested objects
Object constructors
Objects as NULL/ attributes as NULL
Object Tables
Type Hierarchy and Inheritance
Abstract and Final Types
Object Evolution
Propagation of Changes
Sub-Queries
Basic Subqueries
Multiple Column Subqueries
Subqueries with Having
Correlated Subqueries
Subquery Rules and Suggestions

Data Definition Language (DDL)
Create, Drop Alter Keywords
Tables/Partitioning/Column
Views/Synonyms/Sequences

Integrity Constraints
Types of Constraint
Referential Integrity
Defining Constraints
Integrity Constraints and Data Dictionary
Alter table

Indexes
Create Index
Unique Option
When and What to Index
Drop Index
Validate Index
Index Type Overview

Data Manipulation Language
Insert /Update/Delete
OPS Commands (Commit, Rollback and Savepoints)
back to top
Data Control Language
Data Security
Grant and Revoke

Oracle 9i/10g SQL Features
The Returning clause
Drop column
Function based indexes
Temporary tables
Default Column Value enhancements
Overview of Constraint enhancements
For update wait Clause
Constraints on views
External Tables
Multiple Insert Statements
Conditional Insert
Pivoting Insert
Merge Statement
Grouping Sets
WITH Clause
Analytical Functions

10g SQL Language
Improvements
Improvements review
Regular Expressions
Native Floating Point Datatypes

LOBs
LOB Migration
LOB SQL Features

Data warehousing and OLAP
Materialised views
Dimensions
ROLLUP functions
The ROLLUP operator
The CUBE operator
Client side caching

SQL*Plus Reporting
Introducing SQL*Plus for Reporting
Using SQL*Plus
Using the System Editor for SQL*Plus
SQL*Plus Command Variables
Building SQL*Plus Reports
Titles and Headings
Using NEW_VALUE and OLD_VALUE

ANSI Standardisation
Oracle (since 9ir2) Joins improvements
Case Expressions
SQL NULLIF and COALESCE
Scalar Subqueries
Date Datatype
TIMESTAMP Datatype
INTERVAL Datatypes
CURRENT_DATE