Attend Free demo of Oracle on 23rd Mar,2015 @9:30 AM @KPHB

Batch Date: Mar 23rd @ 9:30AM

Faculty: Mr. Dinesh Kumar

Duration: 45 days

Fee: 1500/- INR + Reg Fee 100/-INR

Location : Kphb, Hyderabad.

Venue:
3rd floor, above HDFC bank
Near JNTU signal, Kukatplly
Hyderabad-500085

Syllabus:

Course pattern:

• Hand Notes will be provided
• Daily assignments
• Advanced features in Oracle
• Programs as per coding standards followed in CMM LEVEL 5 companies
• Using TOAD (Tool For Oracle Application Development) in training
• Discussions on FAQ’s

Introduction to DBMS:

• Approach to data management
• Introduction to prerequisites
• File and file system
• Disadvantages of file
• Review of database management terminology
• Database models

Hierarchal model
Network model
Relational model
Introduction to RDBMS:

• Feature of RDBMS
• Advantages of RDBMS over FMS ad DBMS
• The 12 rules (E.F codd’s Rules – RDBMS)
• Need for database design
• Support of normalization process for data management

Client server technology
Oracle corporation products
Oracle versions
• About SQL&SQL*PLUS

Sub language commands:

• Data definition language (DDL)
• Data retrieval language (DRL)
• Data manipulation language (DML)
• Transaction control language (TCL)
• Database security and privileges (DCL)

Introduction to SQL Database Object:

• Oracle predefined data types
• DDL Commands

Create, alter (add,modify,rename,drop)
columns, drop
• Working with DML,DRL Commands
• Operators support
DML-Insert,update,delete
DQL-SELECT statements sing WHERE Clause
Comparison and conditional operations
Arithmetic and logical operations
Set operators (UNION, UNION ALL, INTERSECT, MINUS)
Special operators – IN (NOT IN),
BETWEEN (NOT BETWEEN), LIKE (NOT LIKE), IS NULL (IS NOT NULL)
Built in functions:

• Arithmetic functions, character functions, date functions
• Aggregate functions, OLAP functions & general functions

Grouping the result of a query:

• Using group by and having clause of DRL statement
• Using order by clause

Working with integrity constraints:

• Importance of data integrity
• Support of integrity constraints for relating table in RDBMS
• Working with different types of integrity constraints

NOT NULL constraint
UNIQUE constraint
PRIMARY KEY constraint
FOREIGN KEY constraint
CHECK constraint
REF constraint
Understanding ON DELETE clause in referential integrity constraint
Working with composite constraint
Applying DEFAULT option to columns
Working with mujltiple constraints upon a colume
Adding constraints to a table
Dropping of constraints
Enabling for constraints
Querying for constraint information
Querying multiple table (Joins):

• Equi join/inner join/simple join
• Cartesian join
• Non-equi join
• Outer joins
• Self join

Working with sub queries:

• Understanding the practical approach to sub queries/nested select/sub select/inner
select/outer select
• 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
• PAIR WISE and NON PAIR WISE comparison in sub queries
• Be … aware of NULL’s
• Correlated sub queries
• Handling data retrieval with EXISTS and NOT EXISTS operators

Working with DCL,TCL commands:

• Grant, revoke
• Commit, rollback, savepoint
• SQL Editor commands
• SQL Environment settings

Maintaining database objects:

VIEWS in oracle:

• Understanding the standards of VIEWS in oracle
• Types of VIEWS

Relational views
Object views
• Prerequisites to work with views
• Practical approach of SIMPLE VIEWS and COMPLES VIEWS
• Column definitions in VIEWS
• Using VIEWS for DML operations
• In-line view
• Forced views
• Putting CHECK constraint upon VIEWS
• Creation of READ ONLY VIEWS
• Understanding the IN LINE VIEWS
• About materialized views
• View triggers
• Working with sequences
• Working with synonyms
• Working with index and clusters
• Creating cluster tables, implementing locks

Pseudo columns in oracle:

• Understanding pseudo columns in oracle
• Types of pseudo columns in oracle

CURRVAL and NEXTVAL
LEVEL
ROWID
ROWNUM

Data partitions & parallels process:
• Types of partitions

Range partitions
Hash partitions
List partition
Composite partition
Parallel query process
• Locks
Row level locks
Table level locks
Shared lock
Exclusive lock
Dead lock
• SQL*Loader:
SQL*Loader architecture
Data file (Input datafiles)
Control file
Bad file
Discard file
Log file
.txt to bse table
.csv to base table
From more than one file to single table

PL-SQL

• Introduction to programming languages
• Introduction to PL/SQL
• PL/SQL Architecture
• PL/SQL Data types
• Variable and constants
• Using built_in functions
• Conditional and unconditional statements

Simple IF,ELSIF, ELSE…IF
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 cursors
Explicit cursors
Cursor attributes
Cursor with parameters
Cursors with LOOPs
Cursors with sub queries
Ref.cursors
• Record and PL/SQL Table types

Advanced PL/SQL

Procedures in PL/SQL:

• STORED PROCEDURES
• PROCEDURE with prameters (IN,OUT and IN OUT)
• POSITIONAL Notation and NAMED Notation
• Procedure with cursors
• Dropping a procedure

Functions in PL/SQL

• Difference between procedures and functions
• User defined functions
• Nested functions
• Using stored function in SQL statements

Packages in PL/SQL:

• Creating PACKAGE specification and PACKAGE body
• Private and public objects in PACKAGE

EXCEPTIONS in PL/SQL:

Types of exceptions:

• User defined exceptions
• Pre defined exceptions
• RAISE_APPLICATION_ERROR
• PRAGMA_AUTONOMOUS_TRANSACTION
• SQL Error code values

Data base triggers in PL/SQL:

Types of triggers

• Row level triggers
• Statement level triggers
• DDL Triggers
• Trigger auditing

File input/output:

• PL/SQL file I/O (input/output) using UTL_FILE package

Implementing object technology

• What is object technology?
• OOPS-object instances
• Creation of objects
• Creating user defined data types
• Creating object tables
• Inserting rown in a table using objects
• Retrieving data from object based tables
• Calling a method
• Indexing abstract data type attributes

Using LOBS

• Large objects (LOBS)
• Creting tables-LOB
• Working with LOB values
• Inserting, updating & Deleting values in LOBs
• Populating lobis DBMS_LOB routines
• Using B-FILE

Using collections

• Advantages of collection
• Ref cursor (dynamic cursor)
• Weak ref cursor
• Strong ref cursor
• Nested tables VARRAYS or VARYING arrays
• Creating tables using nested tables
• Inserting, updating & deleting nested table records
• Nested table in PL/SQL

Oracle data base architecture

• Introduction to oracle database architecture
• Physical structures logical structures
• DB Memory structures background process
• 2tire, 3tire, N-tier architecture

Advanced features

• 9i joines
• New date function
• Rename column
• Inner join/natural join
• Left outer join/right outer join
• Full outer join
• Multiple inserts
• Insert all command
• Merge statement
• NVL2(), NULLIF(), COALESCE()
• CASE expression of select command
• Temporary tables/global tables
• New function EXTRACT()
• Autonomous traction
• Pragma_autonomous_transaction()
• Returning into clause
• Bulk collect
• About flash back queries
• Dynamic SQL
• New 11g features

DBA CONCEPTS

• Data base
• Table space
• Types of tablespaces
• Datafiles/se