Blog

Ø  Reviewing basic architecture concepts, Oracle 11g Architecture

Basic SQL*PLUS commands

Ø  Using script files, START, @, GET , SAVE, LIST, PROMPT, PAUSE, ACCEPT, DEFINE, SPOOL

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

Working with variables 

Ø  Creating and using variables, Command substitution

Pseudo columns and functions

Ø  Pseudo Columns, Rownum, Sysdate, User & UID, the Dual Table

Ø  Working with character functions
UPPER, LOWER, INITCAP,  RPAD(),  RTRIM(), SUBSTR(), INSTR(),  TRANSLATE(), REPLACE(),  GREATEST()

Ø  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,l

Other Database Objects

Ø  Views, Sequences, Synonyms, Indexes, USER_TABLES, USER_TAB_COLUMNS, USER_OBJECTS, USER_IND_COLUMNS, USER_UPDATEABLE_COLUMNS, Materialized Views, Other Data dictionary views

Database Security 

Ø  Object Privileges, Granting access to objects

Improving query performance

Ø  Planning and managing the tuning process, Employing tuning tools, Running EXPLAIN PLAN and autotrace,

PL/SQL Course Content  (15 hrs  10 sessions) 
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

Cursors

Ø  Implicit and Explicit Cursors, Cursor Manipulation statements, OPEN FETCH CLOSE EXIT WHEN

Error Handling

Ø  Predefined Exceptions, Non-Predefined Excecptions, User Defined Exceptions

Printing Values to Screen

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

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

For More Details Contact us