Topic Summary

• 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