Oracle 10g SQL Programming - ORA506
Duration: 5 days
Audience: Application developers, database administrators, system administrators and users who write applications and procedures that access an Oracle 10g database.
Prerequisites: None
Topics:
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
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
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
Scalar Functions
- SQL Functions
- Using SQL Functions
- String Functions
- Numeric Functions
- Date Functions
- Conversion Functions
- Date Formats
- Oracle Pseudocolumns
SQL Queries - Joins
- Selecting from Multiple Tables
- Joining Tables
- Self Joins
- Outer Joins
- Types of Outer Joins
Aggregate Functions and Advanced Techniques
- Subqueries
- Correlated Subqueries
- The EXISTS Operator
- The Aggregate Functions
- Grouping Rows
- Combining SELECT Statements
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
Data Definition and Control Statements
- Standard Datatypes
- Defining Tables
- Constraints
- Inline Constraints
- Modifying Table Definitions
- Deleting a Table Definition
- Controlling Access to Your Tables
Other Database Objects
- Views
- Creating Views
- Updateable Views
- Sequences
- Synonyms
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
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
Hierarchical Queries
- Hierarchical Data
- Hierarchical Terminology
- Hierarchical Query
- Hierarchical Pseudocolumns
- SYS_CONNECT_BY_PATH
- Processing Hierarchical Queries
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
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
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
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
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
- Indexes
Indexes
- B-tree and Composite Indexes
- Reverse Key and Unique Indexes
- Function-Based Indexes
- Bitmap Indexes
- Index-Organized Tables
- Managing Indexes
Oracle Analytic Functions
- Analytic Functions
- OVER, PARTITION BY, and ORDER BY
- Windowing
- ROLLUP
- CUBE
- Grouping Sets
- RANK
- Modeling
- Model Clauses
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
Formatting Reports with SQL*Plus
- Page Formatting
- Computations
- SQL*Plus Options for Formatting
- Saving the Output
- Data Extraction with SQL*Plus
Appendix A - The Data Dictionary
- Introducing the Data Dictionary
- DBA, ALL, and USER Data Dictionary Views
- Some Useful Data Dictionary Queries