|
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.
Select here for related courses
|
Select here for a list of all courses
|
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 CODE |
OA1 |
TUTORS |
email |
| DURATION |
3 days |
VENUE |
Newark Training Centre ** |
| PRICE |
£875 plus VAT * |
BOOKING CONTACT |
Mick
Hosegood email |
| EXPERIENCE LEVEL |
Beginner |
TECHNICAL CONTACT |
Mick
Hosegood email |
*see note at bottom for special savings!
for pricing for a course run especially for your organisation, please
use our worksheet **Also available on your site for groups of four to
ten.
COURSE DATES:
Mar 2010
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct |
Nov |
Dec |
Jan 2011
|
|
|
|
|
12-N
|
9-N
|
|
|
8-N
|
|
|
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
|