Course Name      :  SQL-PL-SQL online training

Course Duration :  40 Hours

SQL-PL SQL

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