Courses Offered: SCJP SCWCD Design patterns EJB CORE JAVA AJAX Adv. Java XML STRUTS Web services SPRING HIBERNATE  

       

ORACLE 11g Course Details
 

Batch Date: April 21st @ 7:00AM

Faculty: Mr. Krishna Reddy

Duration: 45 days (Daily Two Hours)    
       

Fee:  1000 INR

Location : Maitrivanam, Hyderabad.

Venue :

DURGA SOFTWARE SOLUTIONS at Maitrivanam
Plot No : 202,
IInd Floor ,
HUDA Maitrivanam,
Ameerpet, Hyderabad-500038.
Ph.No : 09246212143

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