Introduction to DBMS
- Approach to Data Management
- Introduction to prerequisites
- File and File system
- Disadvantages of file
- Database Models
|
- Review of Database Management Terminology
- 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 Pre Defined Data types
- DDL Commands.
- Create, Alter (add, modify, rename, drop) Columns, Rename, truncate, drop
- DML-Insert, update, delete
- DQL-SELECT Statements using WHEREclause
- Comparison and Conditional Operators
|
- Arithmetic and Logical Operators
- Set Operators (UNION, UNION ALL, INTERSECT, MINUS)
- Special Operators – IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOTLIKE), IS NULL (IS NOT NULL).
- Working with DML, DRL Commands
- Operators Support
|
|
Built in Functions :
- Arithmetic Functions, Character Functions, Date Functions, Conversion 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
- NOT NULL constraint
- UNIQUE constraint
|
- PRIMARY KEY constraint
- FOREIGN KEY constraint.
- CHECK constraint
- Working with different types of integrity Constraints
|
|
REF constraint:
- Understanding ON DELETE clause in referential integrity constraint
- Working with composite constraint
- Applying DEFAULT option to columns
- Working with multiple constraints upon a column
|
- Adding constraints to a table.
- Dropping of constraints.
- Enabling / Disable constraints
- Querying for constraints information
|
|
Querying Multiple Tables (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 :
- 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 COMPLEX VIEWS
- Column definitions in VIEWS
- Using VIEWS for DML Operations
- In-Line View
- Forced Views
- Putting CHECK Constraint upon VIEWS
|
- 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, working with roles
|
|
Pseudo Columns in Oracle:
- Understanding Pseudo Columns in Oracle
- Types of Pseudo Columns in Oracle
- CURRVAL and NEXTVAL
|
|
|
Data Partitions & Parallel Process :
- Types of Partitions
- Range Partitions
- Hash Partitions
|
- List Partition
- Composite Partition
- Parallel Query Process
|
|
Locks :
- Row level Locks
- Table Level Locks
- Shared Lock
|
|
|
SQL * Loader :
- SQL * Loader Architecture.
- Data file (Input Datafiles)
- Control file
- Bad file
|
- Discard file
- Log file
- .txt to base table
- .csv to base table
- From more than one file to single table
|
|
PL-SQL (Procedure Language – SQL) :
- Introduction to Programming Languages
- Introduction to PL/SQL
- The Advantages of PL/SQL
- PL/SQL Architecture
- 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 Cursors
- Explicit Cursors
- Cursor Attributes
- Cursor with Parameters
- Cursors with LOOPs Nested Cursors
- Cursors with Sub Queries
- Ref. Cursors
- Record and PL/SQL Table Types
|
|
Advanced PL/SQL :
- Procedures in PL/SQL
- STORED PROCEDURES
- PROCEDURE with Parameters (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
|
|
Database 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
|
|
Oracle Database Architecture :
- Introduction to Oracle Database Architecture
- Physical structures Logical structures
|
- DB Memory Structures Background Process
- 2 Tire, 3 Tire, 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()
- Bulk Collect
- About Flash Back Queries
- Dynamic SQL
- New data types, Flash back Command
- Purge Command, Recyclebin
- Regular expressions, DML Error Logging
- Data Pump, Virtual Columns
- Read only tables, Cross tab Views using
- Pivot/Unpivot operators, Follows Clause
- Compound triggers, New data types
|
|
DBA CONCEPTS :
|
|
- Types of Tablespaces
- Data files /Se
|
|
www.webdesignersmalaysia.com
+91-09032803895 
