Oracle SQL

THE COURSE
The course will allow delegates to understand and use Oracle SQL. They will learn how to enter SQL, use the "select" statement, understand and use joins, sub-queries and create and manipulate data tables. This course will explain and use the features of Oracle 11g SQL. It is also suitable for users of Oracle 10g, Oracle 9i and earlier versions. The course includes practical exercises to help the delegates put the features covered into practice.

WHO SHOULD ATTEND
IT professionals who need to know about and use Oracle SQL. This course is ideal for potential Oracle developers, database administrators and support technicians. The course is also popular with report developers.

PRE-REQUISITES
Delegates should have a basic understanding of relational database concepts. This knowledge can be gained from attending our Oracle Introduction course or equivalent.

COURSE TOPICS
DATABASE OVERVIEW

• Introduction
• Relational Database Theory
• An Overview of Oracle
• Oracle Architecture
• Oracle Background Processes

STRUCTURED QUERY LANGUAGE

• Introduction
• SQL*Plus
• SQL*Plus Worksheet
• iSQL*Plus
• SQL Developer
• Structured Query Language (SQL)

JOINS AND FUNCTIONS

• Introduction to Joins
• SQL:1999 Joins
• Dates
• Functions

COMPLEX QUERIES

• Why Nested Queries
• Nested Sub-Queries
• Correlated Sub-Queries
• Hierarchical Queries
• The WITH Clause
• GROUP BY Enhancements
• PIVOT and UNPIVOT Clause

SET OPERATORS

• Introduction
• The ‘Union' Statement
• The ‘Intersect' Statement
• The ‘Minus' Statement

SQL*PLUS FORMATTING

• SQL *Plus Formatting Overview
• Environment Settings
• Column Headings
• Adding Titles
• Break Points
• Performing Calculations
• File Management
• COMMAND FILE PARAMETERS
• Substitution Variables
• Keyword Parameters
• Positional Parameters
• Additional Features

DATABASE OBJECT DEFINITION

• Database Objects
• Creating Tables
• Temporary Tables
• External Tables
• Btree Indices in Oracle
• Index Organized Tables (IOT)
• Views
• Synonyms
• Sequences
• Database Links
• Removing Objects
• Data Dictionary

DATA MANIPULATION

• Inserting Values into a Table
• Insert SELECT
• Multi-table Insert
• Changing Existing Values
• Merging Records
• Deleting Records
• Changes via Views
• Transactions
• Locking

ORACLE SECURITY FEATURES

• Introduction
• System Privileges
• Access to Database Objects