ORACLE ONLINE TRAINING

Oracle 10g/11g – SQL & PL/SQLThe Oracle database environment

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

Basic SQL*PLUS commands

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

Basic SELECT Statement

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

Ordering the output

•  Single Column, Descending order, multiple column sorts

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, Row num, Sys date, User & UID, the Dual Table
•  Working with character functions
•  UPPER, LOWER, INITCAP, RPAD(), RTRIM(), SUBSTR(), INSTR(),
•  TRANSLATE(), REPLACE(), GREATEST(), LEAST(), DECODE
•  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

Creating Sub queries

•  Single Row sub queries, Multiple row Sub queries

Enhancing groups function

•  ROLLUP, CUBE

Transaction Control Language

•  Rollback, Commit, save point

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

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

Improving query performance

• Planning and managing the tuning process, Employing tuning tools,
•  Running EXPLAIN PLAN and auto trace, SQL Trace and TKPROF output,
•  Optimizer concepts, Fundamentals of access paths, Gathering object and system
•  statistics with DBMS_STATS, Utilizing hints and optimizer mode
•  Specifying first-rows optimization, Determining the driving table

Other Concepts

•  The Relational Database Model, The system Development Lifecycle, Overview of normalization

PL/SQLBlocks

•  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

Printing Values to ScreenProcedures

•  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 data types

•  Associative Arrays, Nested tables, VARRAYs

• 8+ years of experience in Oracle PLSQL Development.
• 5+ yrs of exp as an ORACLE trainer.
• Working as Manager for DBA operations.
• Entirely customized to your level.
• Superior training experience skills
• Practical experience with theoretical topics.
• Very approachable and well experienced.
• Empowering students with best conceptual & practical skills
• Providing the Projects on emerging Technologies with Real-time scenarios.