Database 10g: Introduction

Course Overview

Organisations have come to depend upon the strength of Oracle databases for managing information. Oracle Database 10 g offers the same reliability of previous versions, along with features for developing high-performance databases.

In this course, you gain the skills to utilise the Oracle Database 10 server. Hands-on exercises provide experience in applying relational database concepts, leveraging the SQL language and programming with PL/SQL.

Audience

This course benefits those who are, or will be, working with Oracle 10. Relational database experience and familiarity with basic programming concepts are useful. Oracle experience is not required.

Skills Gained

• Build, query and manipulate Oracle 10 g databases to create powerful applications
• Retrieve and manipulate data efficiently using powerful SQL features
• Create and manage database tables, views and sequences
• Maintain data integrity and enforce security with privileges and roles
• Compose reusable structured PL/SQL code with cursors and procedures
• Enhance performance through indexes and PL/SQL optimisation
• Program with packages and triggers

Course Outline

Oracle 10 gTechnology 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
• Constructing outer joins and self joins
• Grouping and aggregating data
• Simple and correlated subqueries
• Comparing CASE and DECODE functions

Manipulating data

• Inserting, updating and deleting 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
• Creating scripts against the data dictionary

Developing views and sequences

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

Maintaining integrity

• Determining referential integrity with primary, unique and foreign keys
• Imposing business rules with CHECK constraints
• Handling transactions and 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