Oracle10g SQL and SQL*PLUS Part One

Course: OR10SQL1

Duration: 3 Days

Level: I

Course Summary

This course will introduce students to the concept of relational databases and provides an in-depth look at SQL in the SQL*Plus environment in order to retrieve and manipulate data from an Oracle Database. The course may be combined with our Oracle SQL Part Two course, which is an essential pre-requisite before embarking on training for PL/SQL Programming and Developer components such as Forms or Reports. This course is suitable for Oracle10g.

« Hide The Details

Topics Covered In This Course

DATABASE OVERVIEW

INTRODUCTION
DATABASE
(R)DBMS (RELATIONAL) DATABASE MANAGEMENT SYSTEM
RELATIONAL DATABASE THEORY
ENTITY/RELATION/TABLE
ATTRIBUTE/COLUMN/FIELD
TUPLE/ROW/RECORD
RELATIONSHIPS
PRIMARY KEY
FOREIGN KEY
NORMALISATION
RELATIONAL OPERATIONS
RELATIONAL DATABASE PROPERTIES
TABLE PROPERTIES
AN OVERVIEW OF ORACLE
ISO ( Incorporating ANSI and BSI) STANDARDS
ORACLE ARCHITECTURE
ORACLE BACKGROUND PROCESSES

iSQLPlus, SQLPLUS, SQLPlus Worksheet & SQL

INTRODUCTION
SQL*PLUS
SQL*Plus Editor Commands
SQL*Plus Commands (Continued)
SQL*PLUS Worksheet
iSQL*PLUS
PRACTICAL SESSION ON SQL*Plus
SELECT ALL COLUMNS
SELECT CERTAIN COLUMNS
DISTINCT/UNIQUE
SELECT CERTAIN ROWS
BETWEEN
LIKE
IN
NULLS
COMPOUND WHERE
SORT ROWS
PERFORM A CALCULATION
COLUMN ALIAS
NON-COLUMN TEXT
COMMON SQL ERRORS<

JOINS AND FUNCTIONS

INTRODUCTION
ORACLE SQL99 JOINS
OUTER JOINS
ORACLE SQL99 OUTER JOINS
PRACTICAL SESSION ON JOINS
DATES
FUNCTIONS
CONVERSION FUNCTIONS
DATE FUNCTIONS
CHARACTER FUNCTIONS
NUMERIC FUNCTIONS
GROUP FUNCTIONS
GROUP BY
HAVING
CUBE, ROLLUP & GROUPING
HIERARCHICAL QUIERIES
SYSTEM FUNCTIONS

SUB-QUERIES

INTRODUCTION
NESTED SUB-QUERIES
MULTI-COLUMN SUB-QUERIES
CORRELATED SUB-QUERIES
EXISTS
INLINE VIEWS

CASE STATEMENTS

INTRODUCTION
CASE STATEMENT EXAMPLES
DECODE VS. CASE

SET OPERATORS

INTRODUCTION
UNION
INTERSECT
MINUS

DATA MANIPULATION

INSERTING VALUES INTO A TABLE
SELECT CLAUSE WITH INSERT
CHANGING EXISTING VALUES
DELETING RECORDS
CHANGES VIA VIEWS
TRANSACTIONS
LOCKING

SQL*PLUS FORMATTING & REPORTING

INTRODUCTION
SQL*PLUS DEFINE COMMAND
SQL*PLUS AUTOTRACE
SQL*PLUS TIMING
SET OPTIONS
COLUMN HEADINGS
TITLES
BREAK POINTS
CALCULATIONS
SPOOL
STORE
EXIT

COMMAND FILE PARAMETERS

INTRODUCTION
KEYWORD PARAMETERS
POSITIONAL PARAMETERS
ADDITIONAL VARIABLE
SUBSTITUTION FEATURES

APPPENDICES

APPENDIX A: ORACLE DATA DICTIONARY
INTRODUCTION
VIEWS

What You Can Expect

Upon Completion of this course, students will be able to utilize the SQL language to retrieve data from an Oracle Database. Students will also be able to edit, store and recall queries in SQL*Plus format results, and create standard queries with substitution variables.

Who Should Take This Course

Programmers, support staff and powerusers who need to gain a comprehensive understanding of how the Oracle Structured Query Language is used.

Recommended Prerequisites

Students should be computer literate with a good knowledge of the standard editor available on their target hardware platform.

Training Style

This course is highly interactive and consists of theory coupled with plenty of hands-on practical sessions.