Course Name: SQL-PL-SQL Online Training

Course Duration : 40 Hours

• 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

• 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