Introduction to Relational Databases
Relational Model of Data
Key Concepts
Data Structure: Two dimensional tables
What is a join?
Data Integrity
Entity Integrity
Primary Keys
Referential Integrity
Domain Integrity
A Word About Nulls
SQL Concepts
SQL Examples
SQL Terminology
Partial List of DDL Statements
Partial List of DML Statements
Relational Database Design
Entities Defined
Attributes Defined
Relationships Defined
Many-to-Many Relationship
Normalizing Data
Normal Forms
|
Introduction to Oracle10g
What is the Oracle Database?
Principal Features
DDL Statements
DML Statements
Enterprise Edition
Standard & Personal Editions
8i Releases
9i Releases
10g Release Overview
Related Products
Summary???
Workshop
Oracle Architecture
Common Schema Objects
Workshop
|
Introduction to SQL*Plus
Development Environment
Connect to SQL*Plus
SQL*Plus Describe Command
SQL*Plus Connect Command
Customizing Your Environment (LOGIN.SQL and Predefined Variables) (10g)
SQL*Plus Host Command
Executing Queries in SQL*Plus
Spooling the Output
Editing in SQL*Plus
Listing the Buffer Contents
Editing the Buffer Contents
Editor Commands
SQL*Plus Edit Command
Related SQL*Plus Commands
Editing: A Better Way
Running SQL*Plus Scripts
Exit from SQL*Plus
What's in my Recyclebin? (10g)
Workshop
|
Introducing iSQL*Plus
Starting the iSQL*Plus Application Server
Launching iSQL*Plus in Your Browser
Using the Workspace
Accessing History
Setting Preferences
|
Querying the Database, Simple SELECT, Part I
Simplified SELECT Statement
SELECT Column List
SELECT DISTINCT
Calculated Columns
Column Aliases
Concatenated Columns
Sorting: Order By
Sorting by Calculated Columns
Sorting by Column Alias
Sorting by Multiple Columns
Case (and Accent) Insensitive Sort (10g)
Comparison Operators
Available Comparison Operators
Quoting Text Strings (10g)
Logical Operator AND
Available Logical Operators
Accessing Remote Tables
Workshop
|
Datatypes and Functions
Datatypes
Datetime Example
Datetime Summary
Workshop
New XML data type
Miscellaneous Data Types
Datatype Conversion
Dual: The Oracle Work Table
Pseudo Columns: user, sysdate, uid, null
Pseudo Columns: rowid and rownum
Functions Overview
String Manipulation Functions
Case Conversion Functions
Concatenation Function
TRIM Function
substr Function
instr Function
DECODE Function
TRANSLATE Function
Numeric Functions
ROUND Function
TRUNC Function
NVL Function
Character Conversion Function
TO_CHAR Example: number to character
New 9i built-in Functions
Date Conversion Functions
TO_CHAR Examples: character to date
TO_DATE Examples
Datetime Conversions
Time Zone Functions
Date Arithmetic & Functions
Adding & Subtracting Days
ADD_MONTHS Function
LAST_DAY Function
TRUNC Function
Workshop
|
Querying the Database, Part II - Advanced Filters
Conditional Operators
Search Lists: IN
Search Range: BETWEEN
Search Patterns: LIKE
Introduction to Nulls
Selecting Rows with Null Values
IS NULL Operator
ANSI Compliant CASE
Simple CASE Example
|
Querying the Database, Part III - Joins
Simple Joins
Unqualified Names in Joins
Table Aliases in Joins
Outer Joins
ANSI Compliant Joins
ANSI Natural Join
ANSI Inner Join
ANSI Outer Joins
Full outer joins
Partition Outer Join (10g)
ANSI cross join
|
Querying the Database, Part IV - Set Operators
UNION, INTERSECT and MINUS Operators
UNION Example
INTERSECT Example
MINUS Example
|
Querying the Database, Part V - Subqueries
Subquery Concepts
Subquery Example
Subquery Restrictions
Subquery Rules
Subquery IN Operator
Subquery EXISTS Operator
NOT EXISTS Example
Subquery in FROM Clause
Extended Subquery Support
Named Subqueries
Workshop
|
Querying the Database, Part VI - Grouping Data
Group Functions
MIN, MAX, SUM, COUNT, AVG
Group Functions with Nulls
Summary Grouping
GROUP BY Clause
Grouping Multiple Columns
Golden GROUP BY Rule
Where Clause Restrictions
HAVING Clause
HAVING Clause Example
ROLLUP
ROLLUP Example
CUBE
CUBE Example
GROUPING Function
GROUPING Example
DECODE and GROUPING
Workshop
|
Regular Expressions (10g)
Introduction
Pattern Matching with REGEXP_LIKE
Using REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR
|
Data Manipulation and Transaction Control
Inserting Rows
Rounding on Insert
Returning Values from DML
Returning Aggregates (10g)
Inserting Multiple Rows
9i Multi-Table INSERT???
Deleting Rows
TRUNCATE Command
Updating a Single Row
Updating Multiple Rows
MERGE statement???
10g MERGE Enhancements (10g)
|
Concurrency Control
Introduction to Locks
Row Lock Architecture
Basic Locking Rules
DML Locks
DDL Locks
Locking Issues: Lost Update???
Locking Issues: Blocking
Locking Issues: Deadlocks
Deadlocks: Cause and Fix
Workshop
|
Transaction Control
Transaction Review
Supported Statements???
Statement Level Read Consistency???
Transaction Level Read Consistency
Oracle Isolation Levels
Phantom and Non-Repeatable Reads
Read Committed
Serializable
Read Only
Savepoints
Implicit Commits
Workshop
|
Tables and Indexes
Oracle Objects
Naming Rules
Listing Oracle Objects using the Data Dictionary
Creating Tables
Creating Tables Example
Naming Constraints Example
Creating Tables from Other Tables (CTAS)
Introducing Constraints
Disabling Constraints
Enabling Constraints
Listing Constraints using the Data Dictionary
Altering Table Structure
ALTER TABLE Adding Columns
ALTER TABLE Adding Constraints
DROP COLUMN
Dropping Tables
Introduction to Indexes???
B-Tree Indexes
Index Diagram
ROWID Structure
Index Creation
Index Use - Who Decides?
Workshop
|
Views and Synonyms
What is a View?
Views Concept Diagram
What Are Views Used For?
Creating Views: Hiding Sensitive Columns
Creating Views Simplify Table Access
Creating Views: Using Column Aliases
Updateable Views
Read Only Views
WITH CHECK OPTION
WITH CHECK OPTION Example
Views & The Data Dictionary
What is a Synonym?
Private Synonyms
Public Synonyms
CREATE SYNONYM Examples
Synonym Search Sequence
Synonyms & The Data Dictionary
Workshop
|
Other Database Objects
Privileges and Roles
Roles - Concepts
Using Roles
Determining Privileges
Sequences
Caching of Sequences
Sequences - Uses
Referencing Sequences
Using a Sequence to Generate a Primary Key
Sequences & The Data Dictionary
Workshop
|
Introduction to SQL Tuning
SQL Tuning Basics
Tuning - The Process
The Plan Table
SQL*Plus AUTOTRACE
Workshop
|
Where do I go from here?
Certification
Getting Help
Other Topics
Congratulations!
|
Appendices
Table Descriptions
Oracle Data Dictionary
Advanced SQL*Plus Scripting
|