Topic summary

• Welcome to Introduction to Oracle9i: SQL and SQL*Plus Workshop
  • Target Audience
  • Course Objectives
  • Course Book Layout
  • Conventions Used
  • Preparing for Certification
  • Getting Help
  • Additional Learning
• Introduction to Oracle9i
  • What is the Oracle Database?
  • Principal Features: SQL Support
  • Principal Features: Oracle Objects
  • Principal Features: Concurrency & Transaction Support
  • Principal Features: Security
  • Principal Features: High Availability
  • More Principal Features
  • Principal Features for XML and Large Objects
  • Principal Features: Utilities
  • DDL Statements
  • DML Statements
  • Enterprise Edition
  • Standard & Personal Editions
  • Oracle8 i Releases
  • Oracle9i Releases
  • Oracle9i New Features
  • Oracle 10g
  • Oracle 10g New Features
  • Related Products
  • Intro to Oracle9i Workshop A
  • Oracle Architecture
  • Common Objects
  • Intro to Oracle9i Workshop B
• Introduction to SQL*Plus
  • Development Environment
  • Connect to SQL*Plus
  • SQL*Plus Describe Command
  • SQL*Plus Connect Command
  • SQL*Plus Host Command
  • SQL SELECT Statement
  • Dual: The Oracle Work Table
  • 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
  • SQL*Plus Workshop
• Data Access Basics
  • 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
  • Comparison Operator =
  • Comparison Operator >
  • Available Comparison Operators
  • Logical Operator AND
  • Available Logical Operators
  • Data Access Basics Workshop
• Single Row Functions & Pseudo Columns
  • Intro to Oracle Functions
  • Analytic Functions
  • Single-Row Functions
  • Character Functions
  • LOWER Function
  • CONCAT Function
  • TRIM Function
  • SUBSTR/INSTR Functions
  • SOUNDEX Function
  • TRANSLATE Function
  • Numeric Functions
  • ROUND Function
  • TRUNC Function
  • Datetime Functions
  • Time Zones
  • CURRENT_x Functions
  • TRUNC Function
  • Date Arithmetic
  • DATE +/- NUMBER
  • DATE
  • INTERVAL Types
  • ADD_MONTHS Function
  • Conversion Functions
  • TO_CHAR Function
  • Datetime Conversion
  • TO_CHAR Function Example
  • TO_CHAR Example
  • TO_DATE Function
  • TO_TIMESTAMP Function
  • USER and SYSDATE
  • NULLIF Function
  • NVL Function
  • DECODE Function
  • Pseudo Columns
  • Functions Workshop
• Advanced Data Access
  • Conditional Operators
  • Search Lists: IN
  • Search Range : BETWEEN
  • Search Patterns: LIKE
  • LIKE Operator Using Multiple Wildcards
  • LIKE Operator with Escape
  • Introduction to Nulls
  • Selecting Rows with Null Values
  • IS NULL Operator
  • CASE Expression
  • Joining Tables
  • Join Diagram
  • Join Example
  • Unqualified Names in Joins
  • Table Aliases in Joins
  • 4-Table Join
  • Outer Joins
  • ANSI Compliant Joins
  • ANSI Natural Join
  • ANSI Inner Join
  • ANSI 4-Table Join
  • ANSI outer joins
  • Full outer joins
  • ANSI cross join
  • Subquery
  • Subquery Example
  • Subquery Restrictions
  • Subquery Rules
  • Subquery IN Operator
  • Subquery EXISTS Operator
  • NOT EXISTS Example
  • Subquery in FROM Clause
  • Extended Subquery Support
  • UNION , INTERSECT and MINUS Operators
  • UNION Example
  • INTERSECT Example
  • MINUS Example
  • Advanced Data Access Workshop
• SQL*Plus Reporting
  • Creating SQL*Plus Reports
  • SQL*Plus Sample Report Script
  • Executing SQL*Plus Reports
  • Report Output
  • Spooling Output
  • Controlling the SQL*Plus Environment
  • Set Commands
  • Customizing Your SQL*Plus Environment
  • Page Header: Ttitle
  • Header and Footer System Variables
  • Multi-Line ttitle
  • Reformatting Columns
  • COLUMN Example
  • Displaying Current Column Settings
  • Numeric Format Patterns
  • SQL*Plus BREAK Command
  • SQL*Plus COMPUTE Command
  • SQL*Plus Reporting Workshop
• Data Manipulation & Transaction Control
  • Inserting Rows
  • Rounding on Insert
  • Returning Values from DML
  • Inserting Multiple Rows
  • 9i Multi-Table INSERT
  • Deleting Rows
  • TRUNCATE Command
  • Updating a Single Row
  • Updating Multiple Rows
  • MERGE Statement
  • Transaction Control
  • COMMIT & ROLLBACK
  • Transaction Control Who Sees What?
  • Transaction Control Locking Mechanisms
  • Savepoints
  • Commits in SQL*Plus
  • Data Manipulation Workshop
• Tables and Constraints
  • Object Names
  • Naming Rules
  • Creating Tables: Syntax
  • Creating Tables: Example
  • Creating Tables: Dictionary
  • Creating Tables: DESCRIBE
  • Built-In Datatypes
  • CREATE TABLE AS SELECT
  • CTAS Options
  • Introduction to Constraints
  • Constraint Names
  • Constraints Example
  • Disabling Constraints
  • Enabling Constraints
  • Constraints and the Data Dictionary
  • Altering Table Structure
  • Adding Columns
  • Adding Constraints
  • DROP COLUMN
  • Dropping Tables
  • Tables and Constraints Workshop
• Indexes
  • Introduction to Indexes
  • B-Tree Indexes
  • B-Tree Structure
  • Index Creation
  • Function-Based Indexes
  • Descending Indexes
  • Cost Based Optimization
  • Indexes Workshop
• 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
  • Grouping 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
  • Views and Synonyms Workshop
• Other Database Objects
  • Privileges
  • Roles - Concepts
  • Roles
  • Determining Privileges
  • Sequences
  • Caching of Sequences
  • Sequences - Uses
  • Referencing Sequences
  • Using a Sequence to Generate a Primary Key
  • Sequences & The Data Dictionary
  • Other Database Objects Workshop A
  • Objects
  • Object Example
  • What are Collections?
  • What is a VARRAY?
  • VARRAY Example
  • Multi-level collections
  • What is a Nested Table?
  • Nested Table Example
  • Temporary Table Concepts
  • Creating Temporary Tables
  • Temporary Table Limitations
  • Other Database Objects Workshop B
  • External Tables - Concepts
  • Using External Tables
  • Other Database Objects Workshop C
• Appendix A - Course workshop Table Descriptions
• Appendix B - The Oracle Data Dictionary
  • The Oracle Data Dictionary
  • DICT Table
  • Finding Objects in the Data Dictionary
  • Frequently Used Dictionary Views
• Appendix C - Online Only - Intro to Relational Concepts
  • 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
  • Schema
  • Intro to Relational Concepts Workshop
• Appendix D - Online Only - Advanced SQL*Plus Scripting
  • Scripts and Parameters
  • Positional Substitution Variables Example
  • Using &&
  • Executing Scripts
  • Naming Substitution Variables
  • Named Substitution Variables Example
  • Define/Undefine Substitution Variables
  • Creating Substitution Variables
  • Displaying Substitution Variables
  • Talking to Your Scripts
  • Accept & Prompt Script Example
  • Executing a Script Using Accept & Prompt
  • SQL*Plus COPY Command
  • COPY Command Options
  • COPY Command Transaction Size
  • COPY Example
  • Batch Processing
  • Creating SQL*Plus Batch Scripts
  • Creating a Windows Batch Program
  • SQL Script for UNIX
  • UNIX Shell Script
  • Using SQL*Plus as a Code Generator
  • 5 Steps to Generate Code Using SQL*Plus
  • Advanced SQL*Plus Scripting Workshop