Oracle 10g SQL Programming Training in Prairie Village, Kansas

1.  Relational Database and SQL

Overview

Review of Relational Database

Terminology

Relational Database Management

Systems

Introduction to SQL

Oracle Versioning and History

Logical and Physical Storage Structures

Connecting to a SQL Database

Datatypes

Sample Database

2. Using Oracle SQL*Plus

SQL*Plus

The SQL Buffer

Buffer Manipulation Commands

Running SQL*Plus Scripts

Tailoring Your SQL*Plus Environment

Viewing Table Characteristics

SQL*Plus Substitution Variables

Interactive SQL*Plus Scripts

Using iSQL*Plus

3. SQL Queries - The SELECT Statement

The SELECT Statement

The CASE...WHEN Statement

Choosing Rows with the WHERE

Clause

NULL Values

Compound Expressions

IN and BETWEEN

The LIKE Operator

Creating Some Order

4. Scalar Functions

SQL Functions

Using SQL Functions

String Functions

Numeric Functions

Date Functions

Conversion Functions

Date Formats

Oracle Pseudocolumns

5. SQL Queries - Joins

Selecting from Multiple Tables

Joining Tables

Self Joins

Outer Joins

Types of Outer Joins

6. Aggregate Functions and Advanced Techniques

Subqueries

Correlated Subqueries

The EXISTS Operator

The Aggregate Functions

Grouping Rows

Combining SELECT Statements

7. Data Manipulation and Transactions

The INSERT Statement

The UPDATE Statement

The DELETE Statement

Transaction Management

Concurrency

Explicit Locking

Data Inconsistencies

Loading Tables From External Sources

8. Data Definition and Control Statements

Standard Datatypes

Defining Tables

Constraints

Inline Constraints

Modifying Table Definitions

Deleting a Table Definition

Controlling Access to Your Tables

9. Other Database Objects

Views

Creating Views

Updateable Views

Sequences

Synonyms

10. Database Design Concepts

Relational Databases

The Relational Model

Relational Operations

The Database Design Process

Normalization

Second and Third Normal Forms

Other Normal Forms

Applications for Relational Databases

11. SQL Subqueries

Overview of Subqueries

Inline Views

Correlated Subqueries

EXISTS Clause vs. IN Clause

Group Comparisons: ANY and ALL

Scalar Subquery Expression

Subqueries and DML Statements

Subquery Factoring: The WITH Clause

Top-N and Bottom-N analysis

CREATE TABLE and Subqueries

12. Hierarchical Queries

Hierarchical Data

Hierarchical Terminology

Hierarchical Query

Hierarchical Pseudocolumns

SYS_CONNECT_BY_PATH

Processing Hierarchical Queries

13. Object Types

Object-Oriented Programming

Oracle's Object Relational Model

Creating Object Types

Querying Object Types

DML with Object Types

Object Methods

Object Views

VARRAYs

Nested Tables

14. Times, Dates, and Strings

Datetime Fields

Dates and Timestamps

Intervals

Date and Interval Literals

Date Arithmetic

Date Functions

Character Types

Session and Database Parameters

REGEXP Functions

Regular Expressions Supported by

REGEXP

Applying REGEXP Functions

15. Temporary Tables

Undo and Redo

Temporary Tables Defined

Data Lifetime — Transaction vs. Session

Creating Temporary Tables

Managing Temporary Tables

Storage of Temporary Tables

Effects of DML and TRUNCATE

16. SQL Tuning Tools

Automated Statistics Gathering

The DBMS_STATS Package

SQL Tuning Advisor

SQL Tuning Sets

SQL Access Advisor

Retrieving Execution Plans

EXPLAIN PLAN

Using DBMS_XPLAN

Interpreting Explain Plan Results

SQL Trace

TKPROF

17. SQL Tuning

Tuning Goals

The Optimizer

Optimizer Statistics

Identifying SQL to Tune

Optimizer Hints

Optimizer Goal Hints

Access Path Hints

Join Hints

Additional Hints

Plan Stability

Creating Stored Outlines

18. Indexes

Indexes

B-tree and Composite Indexes

Reverse Key and Unique Indexes

Function-Based Indexes

Bitmap Indexes

Index-Organized Tables

Managing Indexes

19. Oracle Analytic Functions

Analytic Functions

OVER, PARTITION BY, and ORDER

BY

Windowing

ROLLUP

CUBE

Grouping Sets

RANK

Modeling

Model Clauses

20. Data Warehouse Features

Partitioned Tables

Partitioning Methods

Partition Pruning and Partition-wise

Joins

Bitmap Indexes

Materialized Views

Creating Materialized Views

Refreshing Materialized Views

The MERGE Statement

Multi-table INSERT Statements

Parallel Statements

21. Formatting Reports with SQL*Plus

Page Formatting

Computations

SQL*Plus Options for Formatting

Saving the Output

Data Extraction with SQL*Plus

22. Appendix A - The Data Dictionary

Introducing the Data Dictionary

DBA, ALL, and USER Data Dictionary

Views

Some Useful Data Dictionary Queries