Oracle SQL / PL SQL Online Training

Home / Oracle SQL / PL SQL Online Training

Oracle SQL / PL SQL Online Training Course Contents

Duration : 4-5 Weeks

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 sqlplus, 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 Subqueries
  • Single Row subqueries, Multiple row Subqueries
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 Anoyomous Blocks
Variables
  • Oracle Datatypes, 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 Excecptions, 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  thoery
Triggers
  • Database triggers, CALLing procedures from triggers
Dynamic SQL
  • EXECUTE IMMEDIATE, DBMS_SQL package
Oracle Supplied packages
  • DBMS_OUTPUT, UTL_FILE
  • Collection datatypes
  • Associative Arrays, Nested tables, VARRAYs