Oracle 9i Foundations: SQL & SQL *Plus

This course has been superseded

We suggest the following instead:
Oracle Database - SQL I Introduction

Course Description

You will receive a hands-on introduction to SQL Basics and SQL *Plus including a basic knowledge of relational databases and how to access them through SQL and SQl *Plus. Topics necessary to the development and administration of an Oracle 9i database are covered.
5 Days
Contact us for pricing
 

Prerequisites

An understanding of relational database design concepts.

In the Beginning...Database Management System Overview

What is a Database?
Types of DBMSs

Introduction to Entity Relationship Modeling

What is a Data Model
Why Use an Entity Relationship Model?
Input and Components of an Entity Relationship Model
Reading the Entity Relationship Model
Guidelines for a Model Review

Fundamentals of Relational Database Management Systems

What is an RDBMS?
History of SQL
Physical Data Storage
Relationships between Tables
Domains - Data Integrity Constraint

SQL Basics and SQL*Plus

Understanding the Difference Between SQL, SQL*Plus and PL/SQL
Invoking and Leaving SQL*Plus
Entering and Executing Commands
The SQL Buffer, SQL*Plus Default Output
LOGIN.SQL
The SELECT Command & Mandatory Clauses

Using iSQLPlus

Understanding the iSQL*Plus Environment and Commands

The WHERE and ORDER BY Clause

WHERE, ORDER BY Clause
Using the Data Dictionary

Single Row Functions

Functions, Date and Numeric Format Models/Data Conversion
Control Statements

Joining Data from Multiple Tables in Queries

The JOIN Condition / The Cartesian Product
Outer JOINs
SET Operators
ANSI SQL99 Joins

Group Functions and the GROUP BY Clause

Group Functions
Distinct Operator in Group Functions
GROUP BY, WITH Clause, ROLLUP, CUBE
GROUPING SETS

Using Subqueries and CASE Clauses

Single-Row, Multi-Row, Multi-Column Subqueries
Subqueries in a WHERE Clause
Subqueries in the FROM Clause – In-Line Views
Subqueries In the SELECT Clause – Scalar Subqueries
Correlated Subqueries
CASE Clause

Analytic Functions (Optional)

Single Row Analytic Functions
Aggregating Analytic Functions

SQL*Plus Reporting

SQL*Plus Report Writing Commands
COLUMN Command / Titles, System Variables
Master-Detail Report with TITLE and COLUMN Commands
Control Breaks, Computing Aggregate Amounts
Using SET, Variables in SQL*Plus, SQL to Generate SQL

Data Manipulation Language (DML) Commands

INSERT, UPDATE, DELETE, MERGE Command
Use of Subqueries
Transaction Control Commands / Read Consistency / Locking
The MERGE Command
Flashback Queries

Data Definition Language (DDL) Commands

DDL Commands, Object Naming, Dropping Objects
TRUNCATE, COMMENT, RENAME Command
Creating Tables / Integrity Constraints
CREATE TABLE, ALTER TABLE Command
Datatypes, Types of Declarative Constraints
Default Values
Creating, Deferrable, Dropping, Disabling/Enabling Constraints / Displaying Constraint Information
Handling Exceptions
Views, Synonyms, Sequences, Indexes
Temporary Tables, External Tables

Security

Users, Privileges, Roles, Changing Passwords

Getting Data In And Out Of Oracle

SQL*Loader Basics
Importing Oracle Data Into Excel
Direct Database Queries Using ODBC
SQL *Plus