1. The Oracle database environment

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

2. Basic SELECT Statement

  ·         Writing the statement in SQL Plus, Running the SELECT statement              

3. Ordering the output

·        Single Column, Descending order, Multiple column sort 

4. Conditional retrieval of data

·         Working with complex conditions, AND OR NOT ,,,, LIKE BETWEEN 

5.Pseudo columns and functions

·         Pseudo Columns, Row num, Sys date, User & UID, the Dual Table

·          Working with character functions UPPER, LOWER, INITCAP, RPAD(), TRIM(), SUBSTR(), INSTR(), TRANSLATE(), REPLACE(), GREATEST(), LEAST(),

·          Working with date functions TO_CHAR(), TO_DATE(), MONTHS_BETWEEN(), ADD_MONTHS(), LAST_DAY(), NEXT_DAY() 

6.Using non-character function

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

7.Using the SET operators

·             Union, Intersect, Minus  

8. Aggregating data using group functions

·          GROUP BY, HAVING 

9. Creating Sub Queries

·         Single Row sub queries, Multiple row Sub queries 

10. Enhancing groups function

·         ROLLUP, CUBE 

11. Transaction Control Language

·         Rollback, Commit, Save point 

12.  Processing hierarchies

·         Creating the Tree structure, LEVEL, CONNECT BY 

13.  Data Manipulation Language

·         INSERT, UPDATE, DELETE, Data Definition Language, CREATE, TRUNCATE, ALTER, DROP, RENAME, DESCRIBE 

14.   Using Declarative Constraints

·   Not Null Constraint, Check Constraint, Unique Constraint, Primary Key Constraint, References   Constraint, On Delete Cascade, On Delete Set Null 

15.  Other Database Objects

·            Views, Sequences, Synonyms, Indexes

·            USER_TABLES, USER_TAB_COLUMNS, USER_OBJECTS 

16.  Database Security

·         Object Privileges, Granting access to objects

17.  Blocks

·         Structure, Writing Anonymous Blocks

18. Variables

·         Oracle Data types, TYPE and ROWTYPE declarations, Value assignments 

 19.   Control structures

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

 20.  Cursors

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

 21. Error Handling

·         Predefined Exceptions, Non-Predefined Excecptions, User Defined Exceptions 

 22.  Procedures

·         Creating and Calling Procedures 

 23.  Functions

·         Creating and Calling Functions 

 24.  Packages

·         Package Header, Package Body 

 25.  Design Tips and Techniques

·         Format of standard packages in an application, Spec and Body, NOCOPY hint ————- theory 

 26. Triggers

·         Database triggers, Calling procedures from triggers 

 27. Dynamic SQL

·         EXECUTE IMMEDIATE, DBMS_SQL package 

 28. Oracle Supplied packages

·          DBMS_OUTPUT, UTL_FILE 

 29.  Collection data types

·         Associative Arrays, Nested tables, VARRAYs