Training Mode

Faculty having 31 years of experience in TOP MNCs on Banking, Insurance, Sales
etc. domains
 In-depth Training with real time use cases
 Real Time projects
 Practical Lab for every concept
 Internship facility
 Interview Preparation
 Work Experience
 100% Placement Support
 Job Support after placement

 

DATABASE FUNDAMENTALS

Data and Information
– Raw Data
– Traditional Data Model
– HDBMS and NDBMS
– Relational Data Model
– RDBMS
– Oracle Database Server
– Oracle Database Working Environment
– Structured Query Language (SQL)
– Types of SQL commands

 

CREATING AND MANAGING A TABLE

– Relation and Table
– CREATE TABLE command
– DESC command
– Naming Rules
– SQL Data Types
– INSERT command
– SELECT command
– UPDATE command
– DELETE command
– COMMIT
– ROLLBACK
– DROP TABLE command

HR Schema

– Understanding Example Schema
– Working with Tables of HR Schema
– SQL Develope

FILTERING DATA

a. WHERE clause
b. Relational Operators
c. IN, BETWEEN AND operators
d. LIKE operator
e. Logical Operators
f. Concatenation Operator
g. Column Alias Names
h. Column Headings

MANAGING NUMERICAL DATA

– NUMBER data type
– Using NUMBER data type in different domains such as SALES, BANKING, HEALTHCARE, FINANCE,
SCIENCE and TECHNOLOGY etc.
– Arithmetic Operators
– Column Heading
– Column Format
– Numerical Calculations
– NULL value arithmetic
– NVL, NVL2, COALSCE etc. functions
– Presenting NUMBER data with Numerical functions

MANAGING CHARACTER DATA

– CHAR data type
– VARCHAR2 data type
– Column Length Semantics
– NLS Character Data types

– CLOB, NCLOB Data Types
– CHARACTER Functions
– Manipulating Character data

MANAGING DATE DATA

– Importance of Date in real time transactions
– Default Date Format
– Creating Table with Date columns
– Inserting Date data
– TO_CHAR function
– TO_DATE function
– Storing Time Data
– Database date and Client Date
– TIMESTAMP data type
– INTERVAL data type
– Date, Timestamp, Interval functions
– Processing Date data

MODIFYING THE STRUCTURE OF A TABLE

– ALTER TABLE command
– Changing Column size
– Changing Column data type
– Adding column(s)
– Removing Column(s)
– Changing name of a column
– Changing name of a table

DATA INTEGRITY CONSTRINTS

Maintaining Valid data in a table
Types of Data Integrity Constraints
NOT NULL constraint
CHECK Constraint
UNIQUE KEY constraint
PRIMARY KEY constraint
FOREIGN KEY Constraint
Enabling/Disabling Constraints
Changing the name of the constraint
Dropping constraints
Misc. Concepts of constraints

SEQUENCES

Creating Sequence
Different options in Sequence
CYCLE Sequences
ORDER Sequences
Negative Sequences
Resetting Sequences
Modifying Sequence Settings

 

RETURNING DATA IN AN ORDER

ORDER BY clause
ASC, DESC Order
Ordering on multiple columns data
Handling NULL values in ordering

GROUPING DATA

GROUP BY clause
Grouping more than one column data
GROUP functions
HAVING Clause
WHERE, GROUP, HAVING, ORDER BY combination
Misc. GROUP BY concepts

TABLE JOINS

Types of table joins
Inner Joins
EQUI Joins
INEQUI Joins
SELF Joins
OUTER JOINS
CROSS JOINS
JOINING 3,4,5,6 and more table joins
Misc. Table Join operations

 

SUBQUERIES

⦁ Single value sub query
⦁ Single Row multiple values sub query
⦁ ALL, SOME, ANY operators
⦁ Multi row single column values sub query
⦁ Multi row multi columns values sub query
⦁ Nested Sub query
⦁ Co-related sub query
⦁ EXISTS operator
⦁ NOT EXISTS operator
⦁ Sub Query at column level
⦁ Sub query at Table level
⦁ Sub query in DML statements
⦁ Sub query in DDL statements
⦁ Misc. concepts in sub query

WINDOW FUNCTIONS

⦁ Limitations of GORUP BY clause
⦁ OVER function
⦁ PARTITION option
⦁ ORDER option
⦁ RANK, DENSE_RANK functions
⦁ Popular Window functions
⦁ Misc. operations

TOP-N QUERIES

⦁ Finding Min and Max values in the given data
⦁ Finding nth Min and Max values in the given data
⦁ Using different methods for TOP-N queries

COMMON TABLE EXPRESSIONS

⦁ SET operators
⦁ WITH Statement
⦁ UNION ALL operator WITH statement
⦁ Nested WITH Statement

HIERACHICAL QUERIES (HQ)

⦁ Use of Hierarchical Queries
⦁ CONNECT BY
⦁ Different functions for HQ
⦁ Displaying data in hierarchy

INDEXES

⦁ Problem with Standard Table Data Access
⦁ Types of Indexes
⦁ B-Tree Indexes
⦁ BITMAP Indexes
⦁ Monitoring Indexes
⦁ Managing Indexes
⦁ Misc. Concepts with Indexes

WORKING WITH REMOTE DATABASE OBJECTS

⦁ Connecting to Remote Database
⦁ Database Link
⦁ Accessing remote tables with Database Link
⦁ Misc. concepts on Remote Database objects

SYNONYMS

⦁ Different types of synonyms
⦁ Private Synonyms
⦁ Public Synonyms

VIEWS

⦁ Different Types of Views
⦁ Simple Views
⦁ Complex Views
⦁ Inline Views

MATERIALIZED VIEWS

⦁ Creating Materialized View
⦁ Different options in creating materialized view
⦁ Different types of refresh on Materialized views
⦁ Complex Materialized views
⦁ Updatable Materialized Views
⦁ Misc. Concepts on Materialized Views

TABLE PARTITIONS

⦁ Necessary of Table Partitions
⦁ Different Types of Table Partitions
⦁ Table Partition by Range
⦁ Table Partition by HASH
⦁ Table Partition by COMPOSITE
⦁ Table Partition by LIST
⦁ Table Partition by INTERVAL
⦁ Misc. Table partition concepts

QUERY MONITORING

⦁ Cost and Time of Query Execution
⦁ EXPLAIN PLAN
⦁ SQL TRACE
⦁ TFPROF
⦁ Mesc. Concepts on Query Monitoring

QUERY TUNING

⦁ Industry standard way of writing Queries
⦁ Tuning with operators
⦁ Tuning on Table Joins
⦁ Tuning on Sub queries
⦁ Misc. Query Tuning

About PLSQL Program

What is PLSQL?
When to use PLSQL?
Where to use PLSQL program 2-tier, 3-tier and n-tier architecture which includes grid and cloud environments?
Structure of PLSQL program
Executing PLSQL program
Internal concepts in executing PLSQL program
Types of PLSQL Blocks
Usage of PLSQL in real time applications with Java/.Net/Python etc.

Writing PLSQL program and running in different client environments

Writing, compiling and executing PLSQL code in SQL *Plus
Writing, compiling and executing PLSQL code in SQL Developer
Writing, compiling and executing PLSQL code in Oracle Developer Forms
Writing, compiling and executing PLSQL code in Enterprise Manager, APEX
Writing, compiling and executing PLSQL code in third party client tools such as TOAD etc.
Setting Environment for developing PLSQL application as per the MNC standards

PLSQL Language Fundamentals

Declaration of variables
Declaration of constants
Scope of identifiers in PLSQL block
PLSQL data types besides SQL data types
PLSQL sub type data types
Composite PLSQL data types such as Anchor data types
Records
Conditional control structures
Looping control structures
CASE statement
DECODE vs CASE
GOTO Statement
NULL statement
MNC application standards for the above topics in respect of declaration, writing and executing the code

Exception Handling

What is exception?
What happens when exception occurs?
How to manage the error situation?
Error Stack
SQLCODE, SQLERRM
Types of exceptions
Managing bulk execptions
RAISE_APPLICATION_ERROR
RAISE Statement
PRAGMA EXCEPTION_INIT
Error Reporting
Error Logging
MNC Application habits in handing exceptions

Procedures

What is a procedure?
How to write a Procedure?
How to execute a procedure?
When to declare a procedure?
Internal execution of a procedure?
Exception Handling
Stored Procedures
MNC Application standards for writing, compiling and running procedures

Functions

What is a function?
How to write a function?
How to execute a function?
When to declare a function?
Internal execution of a function?
Exception Handling
Procedure vs Function?
Real time usage of functions in PLSQL applications

Accessing data between procedures, functions and Calling statements

Parameters
Types of parameters
Parameter scope
Parameter passing techniques
Parameter passing notations
NOCOPY Hint
Default parameters
Exception Handling with parameters
Different ways of using parameters in real time application

Packages

What is a package?
How packages are declared
Package body
Packages for exceptions
Packages for procedures and functions
Real time implementation of packages in application

Processing single row Table’s data

Read a row from a table into PLSQL
Processing row data
Writing back the results to Table
Single row DML statements execution
Exception handling
Managing the status of execution

Processing Multi row Table’s data using cursors

Reading multiple rows from Table(s) into PLSQL
What is a cursor
Types of cursors?
Cursor attributes and variables
Implementation of static cursor
Implementation of Dynamic cursors
Cursor parameters
For loop cursors
REF cursors
Cursor variables
SYSREFCURSOR
Exception handling with cursors

Processing Multi row Table’s data using objects and collections

Reading multiple rows from Table(s) into PLSQL
What is a cursor
Types of cursors?
Cursor attributes and variables
Implementation of static cursor
Implementation of Dynamic cursors
Cursor parameters
For loop cursors
REF cursors
Cursor variables
SYSREFCURSOR
Exception handling with cursors

 

Processing Multi row Table’s data using objects and collections

What is an object?
What are the advantages of objects?
When to use Objects?
Where to user objects?
Types of objects
What is a collection?
Types of Collections
INDEX BY ARRAY
NESTED TABLES
VARRAY
Bulk Collect
Bulk Binding
Bulk Exceptions
Cursors with Collections
Limit option
Real time usage of collection in the application

External Files

Creating an external file
Opening External File
Writing data to a file
Reading data from a file
Exceptions with Files

Error Reporting

Importance of error reporting in real time applications
Error reporting to a table
Error reporting to a log file

Database Triggers

What is database trigger
Types of database triggers
Table level and for each triggers
DML Triggers
DDL Triggers
INSTEADOF triggers
TRIGGER MUTATING
Compund Triggers
AUTONOMOUS TRANSACTION

Dynamic SQL

What is dynamic SQL?
Writing dynamic SQL in procedures
Writing dynamic SQL in functions
Native Dynamic SQL
DBMS_SQL package

Advanced concepts of procedures

Procedures with collection parameters
Procedures with cursor parameters
Overloading procedures
Table function
RETURN statement
Single and multiple Transaction management in a procedure
Transaction management in a function

Advanced concepts of functions

Deterministic functions
Recursive functions
Overloading functions
Pipeline functions
Parallel functions
Using different functions in real time PLSQL application

Object oriented concepts with PLSQL

Declaring objects in PLSQL program
MAP functions and procedures
ORDER functions and procedures
Extending an object
Inheritance and polymorphism

Large Objects

Different types of LOB
Working with DBMS_LOB package
Writing code with LOBs in real time

Unit Testing code

Creating Test cases
Executing Test cases
Reporting Test cases

Debugging Code

What is a bug?
How to detect a bug?
Break point
Watch window
Different types of debugging step next, step over, step to end etc

Performance monitoring of PLSQL code

Reason for performance monitoring
Configuring DBMS_PROFILER
Monitoring code of procedures and functions
Different factors in Monitoring PLSQL Code
Cost Vs Time
Optimizing PLSQL performance

Tuning the code

Importance of tuning
Tuning at Identifiers
Tuning at conditional statements
Tuning at Looping statements
Tuning Cursors
Tuning Collections
Tuning Procedures and functions
Tuning Misc. code

Versioning

What is the use of different versions of code
How to writing multi version code
How to use multi version code

Deploying code

Packing the code
Migrating the code
Deploying the code

Production activities in PLSQL

Definer vs Invoker Rights mechanism
Receiving clients requirement
Performing code changes to PLSQL code
How to write maintainable PLSQL code
Finding, validating and describing functions, procedures, packages, triggers etc.
Checking dependencies
Timestamp vs signature

Writing Shell scripts for calling PLSQL code

Writing shell scripts
Calling PLSQL code
Passing parameters

Scheduling PLSQL code

Crobtab
Managing jobs
DBMS_SCHEDULER
Generating reports with scheduling features
Performing maintenance operations with scheduling ETC