Introduction to Oracle SQL*Plus

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 10g and 11g.

It is suitable for ORACLE version 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.

Our courses are currently run using Micosoft Windows as the platform.

OBJECTIVES OF THE COURSE

To take students with minimal or no SQL knowkedge up to level which will allow them the productively use SQL functions.

COURSE DATES:

INTENSITY: About 40% of the course is practicals, and 60% lectures.

RESOURCE: Each student will have exclusive use of a PC for the duration of the course. Each student will be provided with a full set of training notes relating to the course.

PREREQUISITES:  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 UPS: First Alternative offer an additional 3-day PL/SQL course - Oracle (Part 2) . This describes the ORACLE application development environment which allows the writing of programs and stored procedures.

PRACTICALS / TAKEAWAYS: Each student leaves the course with his own set of training notes for the material covered.

SOFTWARE VERSIONS: This course is based on Oracle version 10g or 11g.

TOPICS COVERED ON THE COURSE

Introduction

Overview, Aims and Objectives, Sample Data, Schedule, Introductions, Pre-requisites, Responsibilities

Relational Databases

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

SQL*Plus

Objectives, SQL*Plus Login, Easy Connect, Using /NOLOG, Using SQL*Plus, Ending the Session, SQL*Plus Commands, SQL*Plus Environment, SQL*Plus Prompt, LOGIN.SQL File, Changing the Password, Finding Information about Tables, Getting Help, Where Clause, Using SQL Files, iSQL*Plus, SQL*Plus Commands

Data Retrieval

Objectives, SQL Developer, SQL Developer - Connection, Viewing Table Information, Using SQL, Where Clause, Using Comments, Character Data, Users and Schemas, AND and OR Clause, Using Brackets, Date Fields, Using Dates, Formatting Dates, Date Formats, TO_DATE, TRUNC, Date Display, Order By Clause, DUAL Table, Concatenation, Selecting Text, IN Operator, BETWEEN Operator, LIKE Operator, Common Errors, UPPER Function, Single Quotes, Finding Metacharacters, Regular Expressions, REGEXP_LIKE Operator, Null Values, IS NULL Operator, NVL, Accepting User Input

Data Definition

Objectives, Creating a Table, Datatypes, Simple Create Example, Naming Tables, Constraints, Not Null, Primary Key, Foreign Key, Check, Unique, Altering Constraints, Full Create Example, Data Dictionary, Alter Table, Secondary Indexes, B-tree Index, Bitmap Index, Create Index, Explain Plan, Using Indexes, Clusters, Partitioned Tables, Creating a Partitioned Table, Rename, Drop Statement, Flashback Table, Managing the Recycle Bin

Data Update

Objectives, Insert, Some Values, Insert, All Values, Insert, Date Values, Insert, TO_DATE, Insert, Default Values, Using Substitution Variables, Transactions, Commit, Rollback, Using Constraints, Update, Date Arithmetic, Update, TO_DATE, Update, TRUNC, Delete, Truncate, Sequences, Grant, Create Synonym, Create Public Synonym, Locking, Revoke, Savepoint, Auto Commit

Multi-Table Retrieval

Objectives, Calculations, Precedence, ROUND Function, Column Alias, Date Arithmetic, Using Aliases, CEIL and FLOOR, Cartesian Product, Table Join, Table Alias, Selecting the Join Column, Joining without Selecting, Views, Dropping Views, Finding Views, Derived Columns, With Check Option, Snapshot Views, Flashback Query

Using Functions

Objectives, TO_CHAR, TO_NUMBER, LPAD, RPAD, NVL, NVL2 Function, DISTINCT Option, SUBSTR, INSTR, Date Functions, Aggregate Functions, COUNT, Group By Clause, Rollup and Cube Modifiers, Having Clause, Grouping By Functions, DECODE, CASE, Workshop

Sub-Query & Union

Objectives, Single Row Sub-queries, Union, Union - All, Intersect and Minus, Multiple Row Sub-queries, Union – Checking Data, Outer Join

More On Joins

Objectives, Joins, Cross Join or Cartesian Product, Inner Join, Implicit Join Notation, Explicit Join Notation, Natural Join, Equi-Join, Cross Join, Outer Joins, Left Outer Join, Right Outer Join, Full Outer Join, Using UNION, Join Algorithms, Nested Loop, Merge Join, Hash Join, Reflexive or Self Join, Single Table Join, Workshop

Advanced Queries

Objectives, ROWNUM and ROWID, Top N Analysis, Inline View, Inline View, Exists and Not Exists, Correlated Sub-queries, Correlated Sub-queries with Functions, Correlated Update, Snapshot Recovery, Flashback Recovery, All, Any and Some Operators, Insert ALL, Merge

Sample Data

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

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

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

Using Objects

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, Creating Directories for BFILEs, Inserting a BFILE, SQL*PLUS REPORTS, Objectives, ACCEPT and PROMPT, Define and Undefine, Creating an SQL*Plus Report, Break Command, Compute Command, Saving the Output in a File

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, Running the Utility, Appending Data

SPECIAL SAVINGS FOR MULTIPLE AND PACKAGE BOOKINGS

Multiple Bookings
If you book more than one place on our courses on the same order, you are entitled to a 10% discount.

Package Bookings
Further discounts are available for certain packages of courses.

Package ORA1

• Introduction to Oracle SQL*Plus (3 days)
• Oracle PL/SQL (3 days)
• Oracle Database Administration (4 days)

The complete package is available for £2400 for the 10 days of training (individually taken, the price would be £2850)