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