Database 11g: Introduction

Scheduled Dates (sort by: location | date)

Course Overview

The Oracle Database 11g server enables high-speed transactions, better business decisions and sophisticated applications. These capabilities provide users the functionality to build high-quality and efficient database applications. In this course, you gain a solid foundation to maximise Oracle 11gand develop robust databases for your organisation.

Audience

Those who are new to Oracle databases as well as those planning to move to an Oracle database environment. Relational database experience and familiarity with basic programming concepts are helpful. Experience with Oracle databases is not required.

Skills Gained

• Design, build and manage Oracle 11g database applications
• Establish a data model and a storage framework
• Retrieve and manipulate data efficiently using SQL Developer
• Create and manage database tables, sequences and synonyms
• Ensure data integrity, enforce security and enhance performance
• Write structured PL/SQL code to develop stored procedures, triggers and packages

Course Outline

Introduction to Oracle 11g Technology

• The Oracle database product offering
• Relational database concepts
• Applying data modeling 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 11 g
• 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

Database 11g: Introduction / 926