|
(703) 246-9100 Northern Virginia (804) 285-5830 Central Virginia (757) 420-3810 Tidewater Area (800) 294-7497 Toll Free (804) 285-3722 Fax |
Oracle 9i Foundations: SQL & SQL*Plus
Duration: 5 days
|
(703) 246-9100 Northern Virginia (804) 285-5830 Central Virginia (757) 420-3810 Tidewater Area (800) 294-7497 Toll Free (804) 285-3722 Fax |
Oracle 9i Foundations: SQL & SQL*Plus
Duration: 5 days
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.
You will be able to…
Converse in relational database terminology
Understand Structured Query Language basics:
Format reports using SQL*Plus commands
Extract and organize information from the database
Insert, update & delete information in database tables
Create and drop tables, views, synonyms and indexes
Who Needs to Attend:
Application Designers, Developers, & Database Administrators
Prerequisites:
An understanding of relational database design concepts
Course
Content:
In the Beginning…Database Management System Overview
What is a Database?
Types of DBMSs
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
What is an RDBMS?
History of SQL
Physical Data Storage
Relationships between Tables
Domains - Data Integrity Constraint
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
Understanding the iSQL*Plus Environment and Commands
WHERE, ORDER BY Clause
Using the Data Dictionary
Functions, Date and Numeric Format Models/Data Conversion
Control Statements
Course Labs:
|
Lab 1: Entity Identification |
Lab 12: Single Row Functions |
|
Lab 2: Identifying / Resolving Relationships |
Lab 13: Joining Multiple Tables |
|
Lab 3: Data Normalization |
Lab 14: Set Operations |
|
Lab 4: RDBMS Principles |
Lab 15: Subqueries |
|
Lab 5: Basic Database Design |
Lab 16: Grouping And Group Functions |
|
Lab 6: Basic Data Definition Language |
Lab 17: Advanced Data Analysis |
|
Lab 7: Creating Tables And Constraints |
Lab 18: SQL*Plus Reporting |
|
Lab 8: Creating Views |
Lab 19: Advanced DDL |
|
Lab 9: Data Manipulation |
Lab 20: Advanced DML |
|
Lab 10: SQL Queries |
Lab 21: Privileges And Roles |
|
Lab 11: Filtering And Sorting Data |
|
The JOIN Condition / The Cartesian Product
Outer JOINs
SET Operators
ANSI SQL99 Joins
Group Functions
Distinct Operator in Group Functions
GROUP BY, WITH Clause, ROLLUP, CUBE
GROUPING SETS
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
Single Row Analytic Functions
Aggregating Analytic Functions
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
INSERT, UPDATE, DELETE, MERGE Command
Use of Subqueries
Transaction Control Commands / Read Consistency / Locking
The MERGE Command
Flashback Queries
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
SQL*Loader Basics
Importing Oracle Data Into Excel
Direct Database Queries Using ODBC