Oracle 10g SQL (O011) Training Course

Training Course Objectives

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

Who Should Attend

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

Training Course Prerequisites

• PC Fundamentals
• View Learning Path

Training Course Duration

5 Days

Training Course Contents

Oracle Overview

• Introducing Oracle
• The Elements of the Oracle System
• Relational Concepts

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

SQL Functions

• The Concatenation Operator
• Elements of the SQL Query: Arithmetic
• Column Aliases
• String Functions
• Arithmetic Functions
• Date Functions
• Mixed Functions

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)

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

Next Available Dates

• 13th Sep 2010  Leeds
• 20th Sep 2010  Manchester
• 27th Sep 2010  Edinburgh
• 27th Sep 2010  Swindon