Oracle 10g Technology Overview

• Relational database concepts
• Logical data modelling
• Specifying attributes, entities, relationships and candidate keys
• Installation, configuration and architecture
• Defining SGA, processes and database files

Retrieving and Controlling Data

Working with SQL*Plus and iSQL*Plus

• Configuring environment settings
• Defining connections using login.sql
• Implementing case-insensitive queries

Assessing SQL fundamentals

• Selecting, filtering and ordering results
• Avoiding pitfalls in null values
• Pattern matching with regular expressions
• Combining result sets with set operators

Exploiting SQL techniques

• Oracle joins vs. ANSI joins
• Outer joins and self joins
• Grouping and aggregating data
• Simple and correlated subqueries
• Comparing CASE and DECODE functions

Manipulating data

• Streamlining DML with MERGE
• Locking data and managing transactions

Designing and Formulating the Database

Applying the physical design

• Mapping logical model to physical design
• Establishing a storage framework
• Creating users and schemas
• Defining LOB and TIMESTAMP data

Constructing and managing tables

• Building tables with CREATE TABLE
• Altering and dropping tables
• Restoring data with Flashback
• Accessing OS files with external tables
• Leveraging the data dictionary

Developing views and sequences

• Assembling views to filter data
• Generating unique IDs using sequences

Maintaining integrity

• Determining referential integrity
• Imposing business rules with CHECK constraints
• Handling data with deferred and enforced constraints

Optimising Server Programming

Invoking PL/SQL fundamentals

• Declaring variables and constants
• Defining stored procedures and functions
• Writing structured code with %TYPE and %ROWTYPE

Establishing program control

• IF...THEN...ELSE vs. CASE statements
• Creating WHILE LOOPs and FOR LOOPs
• Error handling with predefined exceptions

Operating with cursor types

• Controlling implicit and explicit cursors
• Passing parameters to cursors
• Simplifying cursors with FOR LOOPs
• Improving performance with ROWID
• Handling concurrency with FOR UPDATE

Implementing Development Techniques

Assembling triggers

• Distinguishing statement and row triggers
• Regulating triggers with predicates
• Building triggers to validate, derive and audit data

Constructing procedures and packages

• Choosing best parameter types
• Creating and administering packages
• Advantages of employing packages
• Debugging programs with DBMS_OUTPUT

Advancing Security and Performance

Enforcing security

• Verifying with password aging
• Classifying system and object privileges
• Controlling access with roles
• Accessing schemas via synonyms

Enriching performance

• Guidelines for defining optimal indexes
• Creating unique, non-unique and composite indexes
• Monitoring index usage