SQL & PL-SQL Online Training.

Enquire.Join and get training by Well Experienced & Certified tutors for your professional Courses.

Watch Sample Session!

SQL Course Content- 20 hours

The 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 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(), 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

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,

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,

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/SQL Course Content : 20 hours 

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

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