|
|
(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 10g 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 10g Foundations: SQL & SQL*Plus
Duration: 5 days
Develop the skills to effectively interact with an Oracle 10g Database using Oracle SQL and SQL*Plus.
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 10g database are covered.
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
Application Designers, Developers, & Database Administrators
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
Hierarchical Queries
Single Row vs. Group Functions
Date and Numeric Format Models
Data Conversion
Control Statements
Regular Expressions
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
Users, Privileges, Roles, Changing Passwords
SQL*Loader Basics
Importing Oracle Data Into Excel
Direct Database Queries Using ODBC
Course Labs:
Lab 1: Entity Identification
Lab 2: Identifying / Resolving Relationships
Lab 3: Data Normalization
Lab 4: RDBMS Principles
Lab 5: Basic Database Design
Lab 6: Basic Data Definition Language
Lab 7: Creating Tables And Constraints
Lab 8: Creating Views
Lab 9: Data Manipulation
Lab 10: SQL Queries
Lab 11: Filtering And Sorting Data
Lab 12: Single Row Functions
Lab 13: Joining Multiple Tables
Lab 14: Set Operations
Lab 15: Subqueries
Lab 16: Grouping And Group Functions
Lab 17: Advanced Data Analysis
Lab 18: SQL*Plus Reporting
Lab 19: Advanced DDL
Lab 20: Advanced DML
Lab 21: Privileges And Roles