Course Introduction
Course Introduction
Introduction to Oracle & SQL*PLUS
Introduction to Oracle & SQL*PLUS
Section Objectives
Basic Components of the SGA
Logging Into SQL*PLUS
A Simple Select
Demo - Correcting an Incorrect SQL
Oracle File Extensions
Saving A SQL Statement To A File
Demo - Running Scripts
CRT Files and Spool Files
Oracle SQL*PLUS Editing Commands
Demo - Using the Oracle Editor
SQL Scripts
Dictionary Tables For Users
Demo - Oracle Data Dictionary Views
Future Directions
Programmer Responsibilities
Benefits of Database Development
Introduction to Lab 1
Lab - Running Oracle & Building Tables
SQL*PLUS Commands
Programming Commands
Demo - Creating Variables in SQL*PLUS
Programming Commands Cont.
Reporting Commands
Demo - Report Commands in SQL*PLUS
More Programming Commands
Demo - Compute Statements with Break
Other Commands
SQL*Plus Enhancements in Oracle 10g
Append, Create and Replace Extensions
Show Recyclebin
Introduction to Lab 2
Lab - Commands Using SQL*Plus
SQL*Plus Commands
Chapter 1 Review
Structured Query Language
Structured Query Language
SQL Components
SQL Data Manipulation Language (DML)
SQL Basic Retrieval
Demo - Basic SQL Commands
Retrieval of Computed Values
Demo - SQL Retrieval
Order By Clause
Ordering By Descending
The Distinct Operand
Selecting On Conditions
Multiple Conditions
The Between Clause
The Like Clause
Demo - Showing Like Clause
Negative Conditions
Built In Functions
Group By Clause
The Having Clause
Demo - Review of Having Clause
The Analytical Commands
Cube
Retrieval Involving Null
Treatment of Nulls
Null Values Are Considered In:
Case Expression
Introduction to Lab 3
Lab - More About Selects
Demo - Review of Exercises
Chapter 2 Review
Advanced Structured Query Language
Advanced Structured Query Language
Advanced DML Functions
Qualified Names
Correlation Variables
Advanced DML Functions Cont.
Demo - Oracle's Natural and Outer Joins
ANSI/ISO Join Statements
Oracle Join Statements
Multiple Table Join
More about Aliases
Demo - Joins
Subqueries
Demo - Single & Multi-Value Subqueries
Multivalued Subqueries
The Any Parameter
In-Line Views
The Exists Parameter
Demo - How Exists Help Performance
Set Operators (Conjunctions)
Union
Demo - Unions
Set Operator Intersect
The Minus Conjunction
The Model Clause
Modify Functions
Update a Single Row
Complex Update
Merging Data In Oracle Tables
SQL Delete
Introduction to Lab 4
Lab - Select Rows from Multiple Tables
Chapter 3 Review
Non ANSI SQL Statements
Non ANSI SQL Statements
Oracle SQL Plus Commands
Number Formats
Numeric Functions
Character
Date Formats
Demo - Date Formats and Usage
Date Formats
The Date Functions
Demo - Date Functions
Syntax Operators & Parameter Subs.
Demo - Identify How Users Input Data
The Conversion Functions
The Sample Clause
Introduction to Lab 5
Lab - SQL*Plus Commands for Reporting
Chapter 4 Review
Data Definition Language Commands
Data Definition Language Commands
Base Tables
DDL Commands
Logical Definition - Tables
Create Table Mystaff
Demo - Create a Table
Logical Definition - System Datatypes
Column Data Types
Creating Table and
Creating Check Constraints
Demo - Creating Constraints
Creating Unique Constraints
Creating Referential Integrity Constraints
Demo - Create Primary and Foreign Keys
Non-Unique Indexes
Composite Indexes
Rebuilding Indexes
Bitmap Indexes
Creating Reverse Key Indexes
Demo - Creating Reverse Key Indexes
Index-Organized Tables
IOT Row-Overflow Strategy
Index Before or After Loading Data
Demo - Altering Tables after Creation
Synonyms
Deleting Catalog Items
Views
Column Names
Join Views
Views of Views
View Retrieval Queries
Introduction to Lab 6
Lab - Creating Tables and Indexes
Chapter 5 Review
PL/SQL Programming
PL/SQL Programming
Overview of PL/SQL
The PL/SQL Block Structure
PL/SQL In The Oracle RDBMS
PL/SQL Variables
Referencing Variables
Using Composite Variables
Demo - Creating Composite Variables
Using Composite Variables Cont.
PL/SQL Records Processing
Assigning Values To Variables
Demo - Local and Global Variables
Assigning Values To Variables Cont.
Defining Composite Variables - Tables
Datatype Conversions
Input and Output Values For Variables
Demo - Creating Global Variables
Using The Select Clause
PL/SQL Insert Statements
PL/SQL Update Processing
PL/SQL Delete Processing
Decision Statements!
Use CASE Statements in PL/SQL
CASE Statement and Expression
CASE_NOT_FOUND Exception
PL/SQL Iterative Processing
Developing A Continue Statement
PL/SQL Iterative Processing Cont.
PL/SQL Exceptions
Error Processing
PL/SQL Exceptions Cont.
Demo - Exception Handling
PL/SQL Error Processing
Demo - Exception Block Processing
Raising User-Defined Exceptions
Demo - User-Defined Exceptions
PL/SQL Error Processing Cont.
Propagating Exceptions
PL/SQL Cursor Processing
Declaring The Cursor
Cursor Attributes With Explicit Cursors
Demo - How CURSORS are Handled
Cursor Attributes With Explicit Cursors
Demo - Cursor Looping
Update The Current Row
Demo - Update Processing via Cursors
Storing Rows In a PL/SQL Table
Passing in the Declare Cursor Command
PL/SQL Subprograms
PL/SQL Subprograms Viewing Code
Demo - Creating Procedures
PL/SQL Subprograms Rebuilding
Procedures
PL/SQL Stored Procedures
Returning Values
Demo - Parameters
Returning Values
The In/Out Argument in a Procedure
Demo - OUTPUT Parameters
Displaying Parameters
Executing a Stored Procedure in SQL
Creating Functions
Create Functions in a Declarative Section
Database Packages
A Sample Package
Reading/Writing Operating System Files
Reading Operating System
Introduction to Lab 7
Lab - Creating Procedures & Functions
Introduction to Lab 8
Lab - Creating Packages
Chapter 6 Review
Course Closure
Course Closure