  
                     
|
| |
 |
ORACLE 10G SQL PROGRAMMING
| Course Description |
|
In this class, students will learn how to create, retrieve, and manipulate objects in Oracle10g Structured Query Language (SQL). Students will also be introduced to Oracle10g database features and tools. Students will go on to 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.
Course Length: 5 Days
Course Tuition: $1890 (US)
|
• 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
Contact us for course schedules or more
information.
|
|
|
|
|
|
|
