COURSE HIGHLIGHTS :

  • Faculty : All faculty members are having rich IT industry experience, in a number of technical and managerial roles at senior positions.
  • Extensive discussions and experience sharing by trainers.
  • The Course material is been developed in accordance with Oracle University pattern.
  • Complete theoretical and practical knowledge will be delivered.
  • Students will get opportunities to work on live projects and get acquainted with real time industry scenario.


ORACLE COURSE CONTENT

Introduction

  • Objectives
  • oracle9i
  • Oracle 9i Application Server
  • Oracle 9i Database
  • Relational and Object Relational Dababase Management System
  • Oracle Internet Platform
  • System Development Life Cycle
  • Data storage on different Media
  • Relation Database Concept
  • Definition of Relational Database
  • Data Models
  • Entity Relationship Model
  • Entity Relationship Modeling Conventions
  • Relating Multiple Tables
  • Relational Database Terminology
  • Relational Database Properties
  • Communicating with RDBMS using SQL
  • Relational Dababase Management System
  • SQL Statements


Writing Basic SQL Select statements

  • Objectives
  • Capabilities of SQL SELECT Statements
  • Basic Select statement
  • Selecting all columns
  • Selecting specific columns
  • Writing SQL Statements
  • Column Heading Defaults
  • Arithmeting Expressions
  • Operator Precedence
  • Using Parentheses
  • Defining a NULL value
  • Null values in Arithmetic Expressions
  • Defining a Column Alias
  • Using Column Alias
  • Concatenation Operator
  • Using the Concatenation Operator
  • Literal Character Strings
  • Using Literal Character Strings
  • Duplicat Rows
  • EliminatingDuplicat Rows
  • SQL and iSQL*PLUS interaction
  • SQL statements versus iSql *plus commands
  • Overview of iSQL*PLUS
  • Logging In to iSql*plus
  • The isql*plus Environment
  • Displaying Table Structure
  • Interacting with scripts files


Restricting and Sorting Data

  • Objectives
  • Limiting Rows using a selection
  • Limiting the Rows selected
  • Using the Where Clause
  • Character String and Date
  • Comparisions Conditions
  • Using Comparisions Conditions
  • Other Comparisions Conditions
  • Using the Between Condition
  • Using the InCondition
  • Using the Like Condition
  • Using the NULL Condition
  • Logicals Conditions
  • Using the And Operator
  • Using the And Operator
  • Using the NOT Operator
  • Rules of Precedence
  • Order by Clause
  • Sorting in Descending Order
  • Sorting by Column Alias
  • Sorting by Multiple Columns


Single Row Functions

  • Objectives
  • SQL Functions
  • Two Types of Sql Functions
  • Single Row Functions
  • Character Functions
  • Case Manipulation Functions
  • using Case Manipulation Functions
  • Character Manipulation Functions
  • Usiing Character Manipulation Functions
  • Number Functions
  • Using The ROUND Function
  • Using the Trunc Function
  • Using the MOD Function
  • Working with Dates
  • Arithmetic with Dates
  • Using Arithmetic Operator with Dates
  • Date Functions
  • Using Date Functions
  • Conversion Functions
  • Implicit Data type Conversion
  • Explicit Data type Conversion
  • Using the TO_CHAR Function with Dates
  • Using the TO_CHAR Function with Numbers
  • Using TO_NUMBER and TO_DATE functions
  • RR Date Format
  • Example of RR Date Format
  • Nesting Functions
  • General functions
  • NVL Function
  • Using the NVL Functon
  • Using the NVL2 Functon
  • Using the NULLIF Functon
  • Using the COALESCE Functon
  • Conditional Expressions
  • The Case Expression
  • Usiing the Case Expression
  • The Decode Function
  • Using the Decode Function


Displaying Data from multiple tables

  • Objectives
  • Obtaining Data from multiple Tables
  • Cartesian Products
  • Generating a Cartesian Product
  • Types of JOINs
  • Joining Tables using Oracle Syntax
  • What is an Equijoin?
  • Retriving Records with Equijoins
  • Additional search conditions using AND operator
  • Qualifying Ambiguous Column Names
  • Using Tables Aliases
  • Joining More than two tables
  • Non-Equijoins
  • Retriving Records with Non-Equijoins
  • Outer Joins
  • Outer Joins Syntax
  • Using Outer Joins
  • Self Joins
  • Joining a table to it self
  • Joining a table using Sql
  • Creating Cross Joins
  • Creating Natural Joins
  • Retriving Records with Natural Joins
  • Creating Joins with the using Clause
  • Retriving Records with the Using Clause
  • Creating Joins with the ON Clause
  • Retriving Records with the ON Clause
  • Creating Three -way Joins with the ON Clause
  • Inner versus outer joins
  • LEFT outer Join
  • RIGHT outer Join
  • FULL outer Join
  • Additional Conditions


Aggregating Data Using Group Functions

  • Objectives
  • What are GROUP Functions ?
  • Types of GROUP Functions
  • Group funtions Syntax
  • Using the AVG and SUM Funtions
  • Using the MIN and MAX Funtions
  • Using the COUNTFuntions
  • Using the DISTINCT keyword
  • Group funtions and Null values
  • Using the NVL Function with the Group Functions
  • Creating Groups of Data
  • Creating Groups of Data :-The Group by Clause Syntax
  • Using the Group by Clause
  • Grouping by more than one Column
  • Using the Group by Clause on Multiple Columns
  • Illegal Queries using Grooup Function
  • Excluding Group Results
  • Excluding Group Results : The Having Clause
  • Using the Having Clasue
  • Nesting Group Functions


Subquries

  • Objectives
  • Using a subquries to solve a problem
  • Subquery Syntax
  • Using a subqury
  • Guide Lines for Using Subqueries
  • Types of Subqueries
  • Single Row Subqueries
  • Executing Single Row Subqueries
  • Using Group Functions in a Subquery
  • The Having Clause with the Subqueris
  • What is wrong with this Statement ?
  • Will this statement returns
  • Rows?
  • Multiple Row Subqueries
  • Using the ANY operator in Multiple-Row Subqueries
  • Using the ALL operator in Multiple-Row Subqueries
  • NULL values in Subquery


Producing Readable Output with the iSql*Plus

  • Objectives
  • Substitution Variables
  • Using the & Substitution Variables
  • Character and Date Values with Substitution Variables
  • Specifying Column Names, Expressions and Texts
  • Defining Substitution Variables
  • DEFINE and UNDEFINE Commands
  • Using the DEFINE Command with &Substitution Variables
  • Using the && Substitution Variables
  • Using the VERIFY Command
  • Customizing the iSql*Plus Environment
  • SET Command Variables
  • iSql*plus Format Command
  • The Column Command
  • Using the Column Command
  • COLUMN Format Models
  • Using The BREAK Command
  • Using the TITLE and BTITLE Command
  • Creating a Script File to run a Report
  • Sample Report


Manipulatind Data

  • Objectives
  • Data Manipulation Language
  • Adding a New Row to the Table
  • The INSERT Statement Syntax
  • Inserting rows with NULL values
  • Inserting Special Values
  • Inseriting specific date values
  • Creating a Script
  • Copying Rows from another Table
  • Changing Data in a Table
  • The UPDATE statement Syntax
  • Updating Rows in a Table
  • Updating tow columns with a subquery
  • Updating Rows Based on Another Table
  • Updating Rows : Intigrating Constriaint Error
  • Removing a row from a Table
  • The DELEtE statement Syntax
  • Deleting Rows in a Table
  • Deleting Rows Based on Another Table
  • Deleting Rows : Intigrating Constriaint Error
  • Using a subquery in a INSERT Statement
  • Using the WITH CHECK OPTION Keyword on DML Statements
  • Overview of the Explicit Default Feature
  • Using the Explicit Default Values
  • The MERGE Statements
  • The MERGE Statements Syntax
  • Merging Rows
  • Database Transactions
  • Advantage of COMMIT and ROLLBACK Statements
  • Controlling Transactions
  • Rolling Back Changes to a Marker
  • Implicit Transactions Processing
  • State of data before COMMIT and ROLLBACK
  • State of data after COMMIT
  • Commiting data
  • State od data after ROLLBACK
  • Statement Level Rollback
  • Read Consistency
  • Implementation of Read Consistency
  • Locking
  • Implicit Locking


Creating and managing table

  • Objectives
  • Database objects
  • Naming rules
  • The create table statement
  • Referencing another usre's table
  • The default option
  • Creating table
  • Table in the oracle database
  • Querying the data dictionary
  • Data type
  • Data time and data type
  • Time stamp with time Zone Data type
  • Time stamp with local time data type
  • Interval year to month Data type
  • Interval day to second Data type
  • Creating a table by using subquery syntax
  • Creating table by using subquery
  • The table Statemant
  • Adding a column
  • Modifying acolumn
  • Dropping a column
  • Changing a name of an object
  • Truncating a table
  • Adding a comment to a table


Including constraints

  • Objectives
  • what are the constraints
  • constraits guidelines
  • Defining constraints
  • The not null constraints
  • Unique constraints
  • The primary key constraints
  • The foreign key constraints
  • Foreign key constraints key words
  • Check constraints
  • Adding a constraints syntax
  • Adding a constraints
  • Dropping a constraints
  • Disabling constraints
  • Enabling constraints
  • Cascading a constraints
  • Viewing a constraints
  • Viewing the columns associating with constraints


Creating view

  • Objectives
  • Database objectives
  • What is a view
  • why use view
  • Simple view and complex view
  • Creating a view
  • Retrieving data from a view
  • Querying a view
  • modifying a view
  • Creating a complex view
  • Rules for performing DML operation on a view
  • Using the with check option clause
  • Denying DML operations
  • Removing a view
  • Inline view
  • Top-N analysis
  • Performing Top-N analysis
  • Example of Top-N analysis


Other Database objectives

  • Objectives
  • Database objectives
  • What is a sequence
  • The create sequense statement syntax
  • Creating a sequence
  • Conforming sequence
  • Nextval and Currval psuedocolumn
  • Using a sequence
  • Modifying a sequence
  • Guidelines for modifying a sequence
  • Removing a sequence
  • What is an index
  • How are indexes created
  • Creating an index
  • When to create an index
  • When not to create an index
  • Conforming indexes
  • function based index
  • Removing an index
  • Synonyms
  • Creating and removing an index


Controlling the user access

  • Objectives
  • Controlling user access
  • Privileges
  • System Privileges
  • Creating users
  • User system Privileges
  • Granting system Privileges
  • What is a role ?
  • Creating and granting privilege to a role
  • changing to a password
  • Object Privileges
  • Granting Object privileges
  • Using with grant option and public keywords
  • Confirming privilege granted
  • How to revoke object privilege
  • Revoking object privilege
  • Database links


Using set operator

  • Objectives
  • The SET operator
  • The table used in this lesson
  • The union all operator
  • Using the union all operator
  • Using The intersect operator
  • The minus operator
  • SET operator guidelines
  • The oracle server set operators
  • Matching select statement
  • Controlling the order of the rows


Oracle9i DateTime Functions

  • Objectives
  • TimeZone
  • Oracle9i DateTime Support
  • TZ_OFFSET
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • LOCALTIMESTAMP
  • DBTIMEZONE AND SESSIONTIMEZONE
  • EXTRACT
  • TIMESTAMP Conversion Using FROM_TZ
  • STRING to TIMESTAMP Conversion Using TO_TIMESTAMP and
  • TO_TIMESTAMP_TZ
  • Time Interval Conversion with TO_YMINTERVAL


Enhancement to theGROUP BY CLAUSE

  • Objectives
  • Review of Group Functions
  • Review of Group By Clause
  • Review of Having Clause
  • Group by with ROLLUP and CUBE Operators
  • ROLLUP Operator
  • ROLLUP Operator Example
  • CUBE Operator
  • CUBE Operator Example
  • GROUING FUNCTIONS
  • GROUING FUNCTIONS Examples
  • GROUPING SETS
  • GROUPING SETS Example
  • Composite Columns
  • Composite Columns Example
  • Concatenated Grouping
  • Concatenated Grouping Example


Advanced Subquries

  • Objectives
  • What is a Suquery?
  • Subqueries
  • Using a Subquery
  • Multiple Column Subqueries
  • Column Comparisions
  • Pairwise Comparision Subquery
  • Nonpairwise Comparision Subquery
  • Using a Subquery in the FROM Clause
  • Scalar Suquery Expressions
  • Scalar Subqueries : Example
  • Corelated Subqueries
  • Using Corelated Subqueries
  • Using the EXISTS Operator
  • Corelated Update
  • Corelated Delete
  • The WITH Clause
  • WITH Clause Example


Hierarachical Retrival

  • Objectives
  • Sample Data from the EMPLOYEES Table
  • Natural Tree Structure
  • Hierarchical Queries
  • Walking the tree
  • Walking the tree From the Bottom Up
  • Walking the tree From the Top Down
  • Ranking Rows with the LEVEL Pesudocolumn
  • Formatting Hierarchical Reports using LEVEL and LPAD
  • Pruning Branches


Oracle9i extensions to DML and DDL statement

  • Objectives
  • Review of the insert statement
  • Review of the update Statement
  • Overview multitable insert statement
  • Overview multitable insert statement
  • Types of Overview multitable insert statement
  • Multitable insert statement
  • Unconditional insert all
  • Conditional first insert
  • Pivoting insert
  • External table
  • Creating an external table
  • Example of Creating an external table
  • Querying external tables
  • Create an index with create table statemente


SQL workshop

* All the courses in Vocational training includes live projects.