Oracle 10g/11g Course Details
The Oracle database environment

Reviewing basic architecture concepts Oracle 10g Architecture Main features of 9i and 10g

Basic SQL*PLUS commands

Using script files, START, @, GET, SAVE, LIST, PROMPT, PAUSE, ACCEPT, DEFINE, SPOOL

Basic SELECT Statement

Writing the statement in sql plus, Running the SELECT statement

Ordering the output

Single Column, Descending order, multiple column sorts

Conditional retrieval of data

Working with complex conditions, AND OR NOT LIKE BETWEEN

Working with variables

Creating and using variables, Command substitution

Pseudo columns and functions

Pseudo Columns, Row num, Sys date, User & UID, the Dual Table Working with character functions UPPER, LOWER, INITCAP, RPAD(), RTRIM(), SUBSTR(), INSTR(), TRANSLATE(), REPLACE(), GREATEST(), LEAST(), DECODE Working with date functions TO_CHAR(), TO_DATE(), MONTHS_BETWEEN(), ADD_MONTHS(), LAST_DAY(), NEXT_DAY()

Using non-character function

ROUND (), TRUNC (), SIGN (), Working with multiple tables, Different type of Joins, Writing Outer Joins

Using the SET operators

Union, Intersect, minus

Aggregating data using group functions

GROUP BY, HAVING

Creating Sub queries

Single Row sub queries, Multiple row Sub queries

Enhancing groups function

ROLLUP, CUBE

Transaction Control Languag

Rollback, Commit, save point

Processing hierarchies

creating the Tree structure, LEVEL, CONNECT BY

Data Manipulation Language

INSERT, UPDATE, DELETE

Data Definition Language

CREATE, TRUNCATE, ALTER, DROP, RENAME, DESCRIBE

Using Declarative Constraints

Views, Sequences, Synonyms, Indexes, USER_TABLES, USER_TAB_COLUMNS, USER_OBJECTS, USER_IND_COLUMNS, USER_UPDATEABLE_COLUMNS, Materialized Views, Other Data dictionary views

Database Security

Object Privileges, Granting access to objects

Improving query performance

Planning and managing the tuning process, Employing tuning tools, Running EXPLAIN PLAN and auto trace, SQL Trace and TKPROF output, Optimizer concepts, Fundamentals of access paths, Gathering object and system statistics with DBMS_STATS, Utilizing hints and optimizer mode, Specifying first-rows optimization, Determining the driving table Other Concepts

The Relational Database Model, The system Development Lifecycle, Overview of normalization

PL/SQL

Blocks

Structure, Writing Anonymous Blocks

Variables

Oracle Data types, TYPE and ROWTYPE declarations, Value assignments

Control structures

IF ELSE ENDIF statement, IF ELSIF ELSE ENDIF statement, LOOP END , LOOP statement, WHILE condition, FOR condition

Cursors

Implicit and Explicit Cursors, Cursor Manipulation statements, OPEN FETCH CLOSE EXIT WHEN

Printing Values to Screen

Procedures

Creating and Calling Procedures

Functions

Creating and Calling Functions

Package

Package Header, Package Body

Design Tips and Techniques

Format of standard packages in an application, Spec and Body, NOCOPY hint

Triggers

Database triggers, calling procedures from triggers

Dynamic SQL

EXECUTE IMMEDIATE, DBMS_SQL package

Oracle Supplied packages

DBMS_OUTPUT, UTL_FILE

Collection data types

Associative Arrays, Nested tables, VARRAYs