Oracle 11g SQL-PL/SQL (Duration: 40 Hrs)

SQL

The Oracle database environment

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

Basic SELECT Statement

  • Writing the statement in sql plus, Running the SELECT statement

Ordering the output

  • Single Column, Descending order, Multiple column sort

Conditional retrieval of data

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

Pseudo columns and functions

  • Pseudo Columns, Rownum, Sysdate, 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()

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 Language

  • Rollback, Commit, Savepoint

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

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

Other Database Objects

  • Views, Sequences, Synonyms, Indexes USER_TABLES, USER_TAB_COLUMNS, USER_OBJECTS

Database Security

  • Object Privileges, Granting access to objects

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

Error Handling

  • Predefined Exceptions, Non-Predefined Exceptions, User Defined Exceptions

Procedures

  • Creating and Calling Procedures

Functions

  • Creating and Calling Functions

Packages

  • Package Header, Package Body

Design Tips and Techniques

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

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