Oracle® SQL*Plus®

Click for printer-friendly version
Summary
This course gives an introduction to ORACLE® SQL*Plus® and to SQL, the Structured Query Language used to access a Relational Database. It is based on the ORACLE 8 or 8i relational database but the principles learnt may be applied to databases as diverse as Microsoft SQL Server, Access, DB2 and Mysql.

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.

However, if you have extensive experience of another Relational Database, such as SQL Server, or of Structured Query Language, the follow up course described later may be more appropriate.

Next Steps
  • ORACLE PL/SQL® would be a natural follow-up to this introductory course. This describes the ORACLE application development environment which allows the writing of programs and stored procedures.

Length
3 days

Format
Instructor-led course, with many practical computer-based exercises.

Course Outline

Day 1

  • Introduction
    Objectives, Course Schedule, Course Introductions, Pre-requisites, Bibliography, Delegate Responsibilities, Course Content
  • Relational Databases
    Objectives, What is a Database? What is a Relational Database? Types of Relationship, Tables, Rows and Columns, Primary Key, Secondary Indexes, Foreign Key, Referential Integrity, More on Relationships, Completing the Design, What is an ORACLE 8 Database, What is an ORACLE 8 Object-Relational Database, What is an ORACLE 8i Database, What is an ORACLE 9i Database, The ORDERS Tables, Entity Models, What is TOAD?, The FILM Tables, Course Tables Handout
  • Components of SQL
    Objectives, ORACLE System Architecture, Database Versus Instance, The Instance, Process Structures, The Database Files, Initialisation File, Control File, Data File, Redo Log File, The Archiver - ARCH, Noarchivelog Mode, Archivelog Mode, What is Client/Server?, What is Oracle Net8?, What is ODBC?, How Oracle Works, What is an RDBMS?, Structured Query Language, Data Manipulation Language, Data Definition Language, Data Control Language, Why Use SQL?, Tools Available, Designer/2000 and Developer/2000, Enterprise Manager, Personal Edition, Oracle 8i Lite, Third Party Tools, What is SQL*Plus?, Starting SQL*Plus, The Oracle Technology Network, Accessing the Oracle Documentation
  • Data Retrieval
    Objectives, Select Statement, Some SQL*Plus Commands, SQL*Plus Environment, LOGIN.SQL File, Finding Information about Tables, Where Clause, More SQL*Plus Commands, Datatypes, Nulls, Character Data, Column Command, Character Columns, AND and OR Clause, Using Brackets, Using Dates, Default Date Format, Order By Clause, IN Operator, BETWEEN Operator, LIKE Operator, Finding Metacharacters, Common Errors, IS NULL Operator, Comments, Concatenation, UNIX Environment
  • Data Definition
    Objectives, Create As Select, Insert As Select, Datatypes, NULL Values, Create, Rename, Alter, Changing The Password, Create Index, Explain Plan, Drop, Data Update, Objectives, Insert, Update, Commit, Rollback, Delete, Savepoint, Truncate, Grant, Oracle Locks, How Locking Works, Revoke, Create Synonym, Create Public Synonym, Sequences

Day 2

  • Multi-table Retrieval
    Objectives, Calculations, Rounding, Column Alias, Using Aliases, Cartesian Product, Table Join, Table Alias, Selecting the Join Column, Creating a Report, Break Command, Compute Command, Spool Command, Joining without Selecting
  • Views
    Objectives, Views, Dropping Views, Derived Columns, Finding Views, Dictionary View, Accepting User Input, Using the Double Ampersand - &&, Define and Undefine, Command Line Input
  • Functions
    Objectives, Simple Functions, NVL Function, TO_CHAR Function, Distinct Option, SUBSTR Function, INSTR Function, Date Fields, Default Date Format, Year 2000 Dates, Date Functions, System Functions, Aggregate Functions, COUNT Function, Group By Clause, Having Clause
  • Utilities
    Objectives, What is a Utility?, Export, Import, Loading Data, Appending Data
  • Advanced Queries
    Sub Select I, Sub Select II, Union, Intersect and Minus, Outer Join
  • PL/SQL
    Objectives, What is PL/SQL?, Why Use PL/SQL?, Block Structure, Sample Code, Accepting User Input, Variables, SELECT Statement, Exceptions, Creating Procedures, Showing Errors, Describe a Procedure, Calling Procedures in SQL*Plus, Creating and Running Functions,

Day 3

  • Table Management
    Objectives, Create Table, Testing Constraints, Testing Default Values, Testing Check Constraints, Testing Key Constraints, Alter Table, Clusters, Finding Objects, Drop Table, Creating Objects, Creating Tables with Objects, Using Objects in Tables, Large Object Support, LOB Datatypes, Creating Tables with LOBs, Inserting an Empty LOB, Creating Directories for BFILEs, Creating Tables with BFILEs, Inserting a BFILE
  • Advanced Queries
    Objectives, ALL and ANY Operators, Correlated Sub Select, Correlated Update, More on Correlated Sub Select, Unloading Data
Hardware and Software Requirements
Each student should have access to a PC running Windows 95/98/ME or NT Workstation/2000 Professional with at least 500Mb of free space on the C drive and a CDROM. However, the PCs do NOT have to be on a network.
Note to Oracle licensees
Your Oracle license may not allow the use of the software for providing training to people outside your organisation. Contact Oracle if you are planning to provide third-party training and have any doubts about your license in this regard.


Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 3

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 4

Notice: Undefined variable: REMOTE_USER in /var/www/datadeliverance/inc/trademarks.inc on line 7

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 7

Notice: Undefined index: printer in /var/www/datadeliverance/inc/trademarks.inc on line 11

Go to course catalogue

Oracle and Java are registered trademarks of Oracle and/or its affiliates. PL/SQL and SQL*Plus are trademarks or registered trademarks of Oracle Corporation. Microsoft is either a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. MVS is a registered trademark of the International Business Machines Corp. in the U.S. or other countries or both. Motif, OSF/1, UNIX, and the "X Device" are registered trademarks and IT DialTone and The Open Group are trademarks of The Open Group in the U.S. and other countries. All other trademarks are the property of their respective owners.