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. Basic topics necessary to the use of an Oracle 9i database are covered.
This course is not available as part of our public schedule but can be provided on a customised client specific basis.
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
The SELECT Command & Mandatory Clauses
Using iSQL Plus:
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
Joining Data from Multiple Tables in Queries:
The JOIN Condition / The Cartesian Product
ANSI SQL99 Join Syntax
Group Functions and the GROUP BY Clause:
Distinct Operator in Group Functions
GROUP BY, WITH Clause, ROLLUP, CUBE
Using Subqueries and CASE Clauses:
Single-Row, Multi-Row, Multi-Column Subqueries
Subqueries in a WHERE Clause, the FROM Clause - In-Line Views, and in the SELECT Clause - Scalar Subqueries
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 / Read Consistency / Locking
Getting Data In and Out of Oracle:
SQL *Loader Basics
Importing Into Excel
Direct Database Queries Using ODBC