Oracle 11g SQL

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

Retrieving Multiple Table Data

• 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 / Group By Functions

• Group Function Examples
• Group Function 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 Lang (DML)

• Insert /Update/Delete
• OPS Commands (Commit, Rollback and Savepoints)

Data Control Language (DCL)

• Data Security
• Grant and Revoke

LOBs

• LOB Migration
• LOB SQL Features

Datawarehousing and OLAP

• Materialised views
• Dimensions
• ROLLUP functions
• The ROLLUP operator
• The CUBE operator
• Client side caching

Oracle 9i/10g/11g 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
• The Pivot command (11g only)

10g/11g SQL Improvements

• Improvements review
• Regular Expressions
• Native Floating Point Datatypes

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