Introduction to Oracle 11g and SQL for Developers and DBAs

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
• SQL Concepts and Examples
• SQL Terminology
• Common SQL Statements
• Why More than One Table? Relational Database Design
• Entities Defined
• Attributes Defined
• Relationships Defined
• Many-to-Many Relationship
• Normalizing Data
• Normal Forms
• Workshop

Introduction to the Oracle 11g Database

• What is the Oracle Database?
• Principal Features
• Enterprise Edition Overview
• Oracle Express (XE), Standard (SE), Enterprise (EE) & Personal Edition Overview
• Related Products
• Summary
• Workshop
• Oracle Architecture
• Common Schema Objects
• Introduction to the Data Dictionary
• Workshop

Introduction to SQL*Plus

• Introduction (And Why SQL*Plus is Still Important)
• Accessing the Windows and DOS Versions
• Connect to SQL*Plus
• SQL*Plus Describe Command
• SQL*Plus Connect Command
• Customizing Your Environment (LOGIN.SQL and Predefined Variables)
• Replacing gLogin with Executable Defaults (11g Feature)
• 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?
• SQL*Plus Error Logging (11g Feature)
• New BLOB Support (11g Feature)
• Workshop

Introduction to SQL Developer

• Downloading and Installing SQL Developer
• Getting to Know the Interface
• Making Database Connections
• Using the SQL Worksheet
  • Creating a Table
  • Creating Some Data
  • Creating Reports
  • Exporting Data
  • Workshop

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
• Comparison Operators
• Available Comparison Operators
• Quoting Text Strings
• Logical Operator AND
• Available Logical Operators
• Accessing Remote Tables
• Querying the Data Dictionary to Answer Your Questions
• Workshop

Datatypes and Functions

• Datatypes Overview
• Datetime
• 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
• Nesting Functions
• DECODE Function
• TRANSLATE Function
• Numeric Functions
• ROUND and TRUNC Functions
• NVL, NULLIF, and COALESCE functions
• 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 [NOT] NULL Operator
• ANSI Compliant CASE
• Simple CASE Example
• Workshop

Regular Expressions

• Introduction
• Pattern Matching with REGEXP_LIKE
• Using REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR
• 11g Enhancements
• Workshop

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
• ANSI cross join
• Workshop

Querying the Database, Part IV – Set Operators

• UNION, INTERSECT and MINUS Operators
• UNION Example
• INTERSECT Example
• MINUS Example
• Workshop

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

Querying the Database, Part VII Hierarchical Retrieval

• Understanding hierarchical data within a relational database
• Coding hierarchical queries with the CONNECT BY clause
• Identifying levels with the LEVEL clause
• Formatting the result set with LEVEL and the LPAD function
• Pruning branches with the WHERE and CONNECT BY clauses

Data Manipulation and Transaction Control

• Inserting Rows
• Rounding on Insert
• Returning Values from DML
• Returning Aggregates
• Inserting Multiple Rows
• Multi-Table INSERT
• INSERT IGNORE_ROW_ON_DUPKEY_INDEX (11g Feature)
• Deleting Rows
• TRUNCATE Command
• Updating a Single Row
• Updating Multiple Rows
• MERGE statement
• Workshop

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

Where do I go from here?

• Certification
• Getting Help
• Other Topics
• Congratulations!

Appendices

• Table Descriptions