Oracle SQL Training

Overview

This 3 day course gives an introduction to SQL Developer, SQL*Plus and to SQL, the Structured Query Language used to access a Relational Database and includes the new features of ORACLE versions 9i, 10g and 11g.

It may be based on ORACLE version 9i, 10g or 11g of the relational database and the principles learnt may be applied to databases as diverse as Microsoft SQL Server, MySQL, Access, Informix and DB2.

Full course notes are provided along with sample database files, example SQL files and free software tools for use in accessing an ORACLE database.

The course is equivalent to the SQL part of the standard ORACLE course and as such it is suitable for part of the Introduction to ORACLE: SQL and PL/SQL ORACLE Certified Professional examination.

Pre-requisites

The course is suitable for those who have some knowledge of SQL as well as those who are using ORACLE for the first time. Previous experience with an interactive computer system is desirable but not essential.

Follow-up

A natural follow-up to this course would be the Introduction to ORACLE PL/SQL course. This describes the ORACLE application development environment which allows the writing of programs and stored procedures.

On-site Requirements

There should be no more than 12 delegates on the course and all delegates should meet the above pre-requisites.

Each delegate should have access to a PC running Microsoft Windows XP/2000 with at least 512MB of memory, 5Gb of free space and a CDROM. The PCs also have to be on a network.

For the trainer, a whiteboard, flipchart and pens are required and an overhead projector that can link to a laptop would be a great help.

Course Length

The course takes the format of a workshop, with a mix of lecture, working examples and practical exercises. Although the content may be customised, at least 2 days are needed to cover the core elements.

Course Content

Introduction

Overview, Aims and Objectives, Sample Data, Schedule, Introductions, Pre-requisites, Bibliography and Web Sites, Responsibilities, Contents

Relational Databases

Objectives, The Database, The Relational Database, Tables, Rows and Columns, Accessing The Database, Exercise, Microsoft Access, ORACLE SQL Developer, Selecting Rows, Supplier Table, Saleord Table, Primary Key Index, Secondary Indexes, Relationships, Analogy, Microsoft Access – Relationships, Foreign Key, Joining Tables, Referential Integrity, Types of Relationship, One to One Relationship, Many to Many Relationship, Resolving a Many-to-Many Relationship, Completing the Design, Resolving Relationships, Microsoft Access – Relationships, Entity Relationship Diagram, Data Modelling, CASE Tools, Sample Diagram, Structured Query Language, DDL – Data Definition Language, DML – Data Manipulation Language, DCL – Data Control Language, Why Use SQL?, Course Tables Handout, Self Study, The RDBMS, Advantages of an RDBMS

Data Retrieval

Objectives, ORACLE Client, SQL*Plus Login, Direct Connection, Using SQL*Plus, Ending the Session, SQL*Plus Commands, SQL*Plus Environment, Finding Information about Tables, Users and Schemas, Graphical Tools, SQL Developer, Where Clause, Character Data, Comments, AND and OR Clause, Using Brackets, Using Dates, The DUAL Table, Order By Clause, IN Operator, BETWEEN Operator, LIKE Operator, Single Quotes, Common Errors, Finding Metacharacters, UPPER Function, Null Values, IS NULL Operator, Accepting User Input, Self Study, Getting Help, Using SQL Files, SQL*Plus Parameters, Global Settings, Changing the Password, Selecting Text, Concatenation, TO_DATE, TRUNC, SQL*Plus Reporting Commands, ACCEPT and PROMPT, Define and Undefine, Creating a Report, Break Command, Compute Command, Saving the Output in a File

Data Definition

Objectives, Creating a Table, Datatypes, Create Example, Constraints, Drop Statement, Data Dictionary, Alter Table, Secondary Indexes, Create Index, Explain Plan, Using Indexes, Drop Statement, Self Study, Naming Tables, Rename, Altering Constraints, Create As Select, Insert As Select

Data Update

Objectives, Insert, Some Values, Insert, All Values, Insert, Date Values, Insert, Default Values, Using Substitution Variables, Transactions, Commit, Rollback, Update, Delete, Truncate, Grant, Create Synonym, Create Public Synonym, Locking, Revoke, Finding Objects, SQL Statement Syntax, Sequences

Multi-table Retrieval

Objectives, Calculations, Precedence, ROUND Function, Date Arithmetic, Column Alias, Using Aliases, Order By Number, CEIL and FLOOR, Cartesian Product, Table Join, Table Alias, Selecting the Join Column, Joining without Selecting, Views, Finding Views, Derived Columns, Dropping Views, Snapshot Views, Flashback Query, Workshop – optional, Self Study, Snapshot Recovery, Flashback Recovery, Self Join, Materialized View, Query Rewrite

Using Functions

Objectives, String Functions, NVL, TO_CHAR, TO_NUMBER, LPAD, RPAD, DISTINCT Option, SUBSTR, INSTR, Date Fields, Date Arithmetic, Aggregate Functions, COUNT, Group By Clause, Rollup and Cube Modifiers, Having Clause, Grouping By Functions, DECODE, CASE with Select, Workshop – optional, Self Study, NVL2 Function, TO_DATE, TRUNC, Date Functions, Date Formats

Sub-query and Union

Objectives, Multiple Row Sub-queries, Single Row Sub-queries, Union, Union – All, Intersect and Minus, Union, Checking Data, Outer Join, Inner Join, Left Join, Right Join, Full Join, Self Study, Regular Expressions, REGEXP_LIKE Operator, Exists and Not Exists, Top N Analysis, Insert ALL, Merge

PL/SQL

Objectives, What is PL/SQL?, Why Use PL/SQL?, Block Structure, Sample Code, SELECT Statement, Using Variables, Accepting User Input, Exceptions, Other DML Statements, Creating Procedures, Showing Errors, Describe a Procedure, Calling Procedures, Creating and Running Functions, Showing Errors, Describe a Function, Calling Functions, Creating Triggers, Showing Errors

Utilities

Objectives, What is a Utility?, Export Utility, Using Parameters, Using a Parameter file, Import Utility, Using Parameters, Using a Parameter file, Unloading Data, Batch Runs, SQL*Loader Utility, Loading Data, The Control File, Running the Utility, Appending Data

Advanced Queries

Objectives, All, Any and Some Operators, Correlated Sub-queries, Correlated Sub-queries with Functions, Correlated Update

Query Optimisation

Objectives, Query Optimisation, Creating The Tables, Timing SQL Statements, Other Timing Statements, Explain Plan, Creating the PLAN_TABLE Table, Using SET AUTOTRACE, Collecting Statistics, Primary Key, Secondary Indexes, The Query Optimizer, Rule Based Optimization, Cost Based Optimization, Choose Keyword, Gathering Statistics, Optimizer Hints, How to Specify Hints, Using Indexes, Index Types, B*tree Indexes, Bitmap Indexes, Index-organized table, When to Create Indexes, Choosing Composite Indexes

Objects and Lobs

Objectives, Object-oriented Database, Object-relational Database, Creating Objects, Creating Tables with Objects, Using Objects in Tables, Large Object Support, LOB Datatypes, Creating Tables with LOBs, Inserting an Empty LOB, Creating Tables with BFILEs, Inserting a BFILE, Creating Directories for BFILEs

Sample Data

ORDER Tables, FILM Tables, EMPLOYEE Tables, The ORDER Tables, The FILM Tables, The ORDER Tables, The FILM Tables

Course duration: 3 Days

Oracle SQL Training Inverness, Aberdeen, Glasgow, Edinburgh, Dunfermline and other sites throughout the UK including onsite closed company courses are available.

Oracle SQL Training Ghana, Nigeria and Qatar is also available.