Advanced Oracle® SQL Programming




Advanced Oracle SQL Programming Training Class Summary

A Relational Database Management System (RDBMS) is a software system that allows you to create and manage a Relational Database. Minimum requirements for such a system are defined by both ANSI and ISO. The Structured Query Language (SQL) is the international standard language for relational database management systems. SQL is robust enough to be used by users with non-technical backgrounds, as well as by professional developers and administrators.

In this class, students will develop deeper insight into relational database design and RDBMS operation, learn concepts and specific SQL syntax for extended Oracle datatypes, learn analysis and tuning techniques to increase SQL performance, and master advanced features of Oracle SQL for large data sets and data warehouses.

Audience: Application developers, database administrators, system administrators and users who write applications and procedures that access an Oracle 10g database.

Prerequisites: Oracle SQL Programming.

Class Length: 3 days

Advanced Oracle SQL Programming Training Class Objectives
  • Apply the basic theory behind relational database design.
  • Contribute to all phases of database design and development.
  • Use all aspects of subqueries.
  • Apply Oracle's features for querying hierarchical data models.
  • Use Oracle's Object-Relational Model.
  • Create object types.
  • Use Oracle's collection types in SQL.
  • Select appropriate date-related datatypes for your applications.
  • Use Oracle's regular expression SQL functions to perform pattern matching and string manipulation.
  • Create and manage temporary tables.
  • Establish goals in SQL tuning to improve performance.
  • Use Oracle Database 10g's tuning tools.
  • Describe how indexes are used in RDBMSs, and use them effectively.
  • Use the various analytic functions provided by Oracle to perform sophisticated analysis.
  • Use SQL*Plus to format reports and extract data.
Advanced Oracle SQL Programming Training Class Detailed Outline
  1. Course Introduction
    • Course Objectives
    • Overview
    • Suggested References
  2. 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
  3. 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
  4. Hierarchical Queries
    • Hierarchical Data
    • Hierarchical Terminology
    • Hierarchical Query
    • Hierarchical Pseudocolumns
    • Processing Hierarchical Queries
  5. 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
  6. 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
  7. 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
  8. SQL Tuning Tools
    • Automated Statistics Gathering
    • The DBMS_STATS Package
    • SQL Tuning Advisor
    • SQL Tuning Sets
    • SQL Access Advisor
    • Retrieving Execution Plans
    • Using DBMS_XPLAN
    • Interpreting Explain Plan Results
    • SQL Trace
    • TKPROF
  9. 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
  10. Indexes
    • Indexes
    • B-tree and Composite Indexes
    • Reverse Key and Unique Indexes
    • Function-Based Indexes
    • Bitmap Indexes
    • Index-Organized Tables
    • Managing Indexes
  11. Oracle Analytic Functions
    • Analytic Functions
    • Windowing
    • ROLLUP
    • CUBE
    • Grouping Sets
    • RANK
    • Modeling
    • Model Clauses
  12. 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
  13. Formatting Reports with SQL*Plus
    • Page Formatting
    • Computations
    • SQL*Plus Options for Formatting
    • Saving the Output
    • Data Extraction with SQL*Plus