Introduction to Oracle 11g Technology

• The Oracle database product offering
• Relational database concepts
• Applying data modelling techniques
• Oracle database tools: SQL Developer and SQL*Plus

Database Installation and Administration

Assessing the Oracle database architecture

• Defining memory structures, processes and SGA
• Establishing a storage framework

Managing Oracle databases

• Installing and configuring Oracle Database 11g
• Working with Oracle Enterprise Manager
• Leveraging the Oracle Data Dictionary

Accessing and Manipulating Data

Retrieving data efficiently with SQL Developer

• Selecting, restricting and ordering data
• Avoiding pitfalls in null values
• Exploiting built-in SQL functions

Applying powerful SQL techniques

• Joins, outer joins and ANSI joins
• Grouping data and applying aggregate functions
• Combining result sets with set operators
• Comparing simple and correlated subqueries

Modifying data with SQL statements

• Inserting, updating, deleting and merging data
• Controlling transactions with ROLLBACK and COMMIT
• Locking data to preserve integrity

Creating and Managing Database Objects

Implementing the physical design

• Mapping logical model to physical design
• Creating users and schemas

Constructing and maintaining tables

• Defining column datatypes
• Altering and dropping columns
• Restoring data with Flashback and the recycle bin

Building views, sequences and synonyms

• Filtering data with views
• Generating unique IDs with sequences
• Streamlining access to objects with synonyms

Maintaining Integrity, Security and Performance

Enforcing integrity

• Implementing referential integrity with primary, unique and foreign keys
• Incorporating business rules with check constraints
• Managing transactions and data with deferred and enforced constraints

Securing the data

• Authenticating users with password aging
• Controlling access with system and object privileges
• Simplifying privilege management with roles

Improving performance

• Guidelines for creating indexes
• Indexing the data for optimal access
• Managing unique, non-unique and composite indexes

Programming with PL/SQL

Writing basic programs

• Declaring and initialising variables
• Controlling logic with IF and CASE statements
• Performing iterations with WHILE and FOR LOOPs
• Defining and managing PL/SQL records
• Trapping errors with exception handlers

Processing data with cursors

• Declaring cursors to perform row-level operations
• Passing parameters to cursors to increase flexibility
• Simplifying cursors with FOR LOOPs
• Improving insert and delete performance with CURRENT OF or ROWID
• Terminating loops with cursor attributes

Implementing Server-Side Logic

Modularising code

• Constructing procedures and functions
• Passing parameters to subprograms
• Debugging programs with DBMS_OUTPUT

Creating packages and triggers

• Advantages of deploying packages
• Bundling subprograms in packages
• Defining statement and row-level triggers
• Governing triggers with conditional predicates