BESTWAY TECHNOLOGIES LOGO

+91 - 9000567126, +91 - 9848686581

bestwaytechnologies@gmail.com

ORACLE 11G Training


Oracle 11g Course Content

Introduction to RDBMS:

 What is data and database?

 Feature of DATABASE

 Database activities

 Advantages of RDBMS over FMS and DBMS

ORACLE DATATYPES

Number

Char

 Varchar2

 Raw

 Long

 Longraw

 Date

 LOB(clob,blob,bfile)

 Timestamp

STRUCTURED QUERY LANGUAGE

 Data Definition Language(DDL) CREATE ALTER(Add, Modify, Rename, Drop)

TRUNCATE, RENAME, DROP
 Data Manipulation Language(DML) INSERT, UPDATE, DELETE, MERGE, INSERT ALL
 Data Retrieval Language (DRL) SELECT Column alias, table alias

Working With DCL, TCL Commands:

 Grant, Revoke

 Commit , Rollback, Savepoint

 SQL Editor Commands

 SQL Environment settings

 Database Security and Privileges(DCL)

Working with Operators:

 NUMERIC OPERATORS/ * + –

 RELATIONAL OPERATORS <, <=, >, >=, =, !=, <>, any
 LOGICAL OPERATORS And, or, not, in, between, like(%,_)

Working Oracle Defined Functions:

 SINGLE ROW FUNCTIONS

 CHARACTER FUNCTIONS

 NUMERIC FUNCTIONS

 DATE FUNCTIONS

 CONVERSION FUNCTIONS

 ANALYTICAL FUNCTIONS

 GROUP FUNCTIONS MIN, MAX, AVG, SUM, COUNT

Working with “CLAUSES”

Distinct, Where, Group by, Having, Order by

 Using Group by and Having Clause of DRL Statements

 Using Order by Clause

Data Integrity:

 Importance of Data integrity

 Support of integrity constraints for Relating table in RDMS

 Working with different types of integrity constraints

 Entity Integrity

  • o Primary key constraint
  • o Unique constraint

 Domain Integrity

  • o Check constraint
  • o Not Null constraint

 Referential Integrity

  • o Foreign key

REFERENTIAL constraint

• Understanding ON DELETE CASCADE and ON DELETE SET NULL clause in Referential integrity constraint

• Working with composite constraint

• Applying DEFAULT option to columns

• Working with multiple constraints upon a column

• Adding constraints on existing table

• Dropping Constraints

• Querying for constraints information

• USER_CONSTRAINTS

• USER_CONS_COLUMNS

• Limitations of constraints

Understanding about Normalization

 What is normalization?

 Applying normal forms

 1st Normal form

 2nd Normal Form

 3rd Normal form

Data Merging

VERTICAL DATA MERGING WITH JOINS

 SIMPLE JOIN

 SELEF JOIN

 OUTER JOIN

 TYPES OF JOINS

 Equal join

 Non-Equal Join

 Inner join

 Natural join

 Cross Join

 JOIN( in, using)

 Self Join

 Left outer join or left join

 Right outer join or right join

 Full outer join or full join

HORIZONTAL DATA MERGING

 SET OPERATORS

 UNION

 UNION ALL

 INTERSECTION

 MINUS

Working with Sub Queries:

 Understanding the sub queries

 What is the purpose of a sub query?

 Sub query Principle and usage

 Type of sub queries

  • • Single Row
  • • Multiple Row
  • • Multiple column

 Applying Group Functions in Sub Queries

 The Impact of Having Clause in Sub Queries

 IN,ANY/SOME,ALL Operators in Sub Queries

 GROUP WISE and NON GROUP WISE Comparison in Sub Queries
 Working with NULL values in sub-queries

 Correlated Sub Queries

 Handling Data Retrieval with EXISTS and NOT EXISTS Operators

Maintaining Database Objects:

 Working with Synonyms

 VIEWS IN Oracle:

 Understanding the Standards of VIEWS in Oracle

 Types of VIEWS

 Prerequisites to work with views

 SIMPLE VIEWS and COMPLEX VIEWS

 Keeping CHECK Constraint upon VIEWS

 Creation of READ ONLY VIEWS

 Understanding the IN LINE VIEWS

 About Materialized Views

 Working with Sequences

 Working with Index

Pseudo Columns in Oracle:

 Understanding Pseudo Columns in Oracle

 Type of Pseudo Columns in Oracle

 CURRVAL and NEXTVAL

 LEVEL

 ROWID

 ROWNUM

Data Partitions & Parallel Process:

 Types of Partitions

 Range Partitions

 Hash Partitions

 List Partitions

 Composite Partition

PL-SQL (Procedure Language-SQL):

 Introduction to programming Languages

 Introduction to PL/SQL

 The Advantages of PL/SQL

 PL/SQL Data types

 Variable and Constants

 Using Built-in Functions

 Conditional and Unconditional Statements

  • • Simple if, if…else, nested if…else, if…else Ladder
  • • Selection case, simple case, GOTO Label and EXIT

 Iterations in PL/SQL

  • • Simple LOOP,WHILE LOOP,FOR LOOP and NESTED LOOPS

 SQL Within PL/SQL

 Composite Data Types(Complete)

 Cursor Management in PL/SQL

  • • Implicit Cursor
  • • Explicit Cursor
  • • Cursor Attributes
  • • Cursor with Parameters
  • • Cursor with Loops
  • • Nested Cursors
  • • Cursors with Sub Queries
  • • Ref. Cursors

 Record and PL/SQL Table Types

Working with Procedures in PL/SQL:

Procedures in PL/SQL:

  • • STORED PROCEDURES
  • • PROCEDURE with Parameters(IN, OUT and IN OUT)
  • • Procedure with Cursors
  • • Dropping a Procedure

Working with Functions in PL/SQL:

Functions in PL/SQL:

  • • Differences between Procedures and Functions
  • • User Defined Functions
  • • Nested Functions
  • • Using stored functions in SQL statements

EXCEPTIONs in PL/SQL:

  • •  Types of Exceptions:
    •  User Defined Exceptions
  • • Pre Defined Exceptions
  • • RAISE_APPLICATION_ERROR
  • • SQL Error Code Values

 Packages in PL/SQL:

  • • Creating PACKAGE Specification and PACKAGE Body
  • • Private and Public Objects in PACKAGE

 Database Triggers in PL/SQL:

 Types of Triggers

• Row Level Triggers

• Statement level Triggers
 DDL Triggers

 Trigger Auditing

  • Request Demo

  • Add Your Review