Oracle Course in Guntur, Oracle Institutes in Guntur, Oracle Training in Guntur, Oracle Training Institutes in Guntur – Dream India Technologies

DBMS CONCEPTS

• What is Database
• What is Database Management System
• Different Types of Database Models
• Normalization
• RDBMS
• ORDBMS
• Difference Between DBMS, RDBMS & ORDBMS

Introduction to Oracle

• Different versions of Oracle
• Features of Oracle

SQL

• What is SQL
• Role of SQL in RDBMS
• Data Types of Oracle
• Naming rules in Oracle
• What is SQL*PLUS
• What is Metadata
• What is Schema
• Data Dictionary Tables

SQL 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, merge
• DCL-Grant, Revoke
• TCL-Commit, Rollback, Save point

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
• Arithmetic Expressions
• Using Arithmetic Operators
• Operator Precedence
• Using Parentheses
• Defining a Null Value
• Null Values in Arithmetic Expressions
• Defining a Column Alias
• Using Column Aliases
• Concatenation Operator
• Using the Concatenation Operator
• Literal Character Strings
• Using Literal Character Strings
• Duplicate Rows
• Eliminating Duplicate Rows
• Displaying Table Structure
• Limiting Rows Using a Selection
• Limiting the Rows Selected
• Using the WHERE Clause
• Character Strings and Dates
• Comparison Conditions
• Using Comparison Conditions
• Other Comparison Conditions
• Using the BETWEEN Condition
• Using the IN Condition
• Using the LIKE Condition
• Using the NULL Conditions
• Logical Conditions
• Using the AND Operator
• Using the OR Operator
• Using the NOT Operator
• Rules of Precedence
• ORDER BY Clause
• Sorting in Descending Order
• Sorting by Column Alias
• Sorting by Multiple Columns
• Set Operators (UNION, UNION ALL, INTERSECT, MINUS)

Constraints<>/h3>

• What are Constraints?
• Constraint Guidelines
• Defining Constraints
• The NOT NULL Constraint
• The UNIQUE Constraint
• The PRIMARY KEY Constraint
• The FOREIGN KEY Constraint
• FOREIGN KEY Constraint Keywords
• The CHECK Constraint
• Adding a Constraint Syntax
• Adding a Constraint
• Dropping a Constraint
• Disabling Constraints
• Enabling Constraints
• Cascading Constraints
• Viewing Constraints
• Viewing the Columns Associated with Constraints

Functions

• SQL Functions
• Two Types of SQL Functions
• Single-Row Functions
• Character Functions
• Case Manipulation Functions
• Using Case Manipulation Functions
• Character-Manipulation Functions
• Using the 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 Operators with Dates
• Date Functions
• Using Date Functions
• Conversion Functions
• Implicit Data Type Conversion
• Explicit Data Type Conversion
• Using the TO_CHAR Function with Dates
• Elements of the Date Format Model
• Using the TO_CHAR Function with Dates
• Using the TO_CHAR Function with Numbers
• Using the TO_NUMBER and TO_DATE Functions
• General Functions
• NVL Function
• Using the NVL Function
• Using the NVL2 Function
• Using the NULLIF Function
• Using the COALESCE Function
• Conditional Expressions
• The CASE Expression
• Using the CASE Expression
• The DECODE Function
• Using the DECODE Function
• Aggregate functions

Joins

• Obtaining Data from Multiple Tables
• Cartesian Products
• Generating a Cartesian Product
• Types of Joins
• Joining Tables Using Oracle Syntax
• What is an Equijoin?
• Additional Search Conditions Using the AND Operator
• Qualifying Ambiguous Column Names
• Using Table Aliases
• Joining More than Two Tables
• Non-Equijoins
• Retrieving Records with Non-Equijoins
• Outer Joins
• Outer Joins Syntax
• Using Outer Joins
• Self Joins
• Joining a Table to Itself
• Joining Tables Using SQL: 1999 Syntax
• Creating Cross Joins
• Creating Natural Joins
• Retrieving Records with Natural Joins
• Creating Joins with the USING Clause
• Retrieving Records with the USING Clause
• Creating Joins with the ON Clause
• Retrieving 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

Subqueries

• Using a Subquery to Solve a Problem
• Subquery Syntax
• Using a Subquery
• Guidelines for Using Subqueries
• Types of Subqueries
• Single-Row Subqueries
• Executing Single-Row Subqueries
• Using Group Functions in a Subquery
• The HAVING Clause with Subqueries
• What is Wrong with this Statement?
• Will this Statement Return Rows?
• Multiple-Row Subqueries
• Using the ANY Operator in Multiple-Row Subqueries
• Using the ALL Operator in Multiple-Row Subqueries
• Null Values in a Subquery

Database Objects

• Views
• Why use Views?
• Simple Views and Complex Views
• Creating a View
• Retrieving Data from a View 1
• Querying a View
• Modifying a View
• Creating a Complex View
• Rules for Performing DML Operations on a View
• Using the WITH CHECK OPTION Clause
• Denying DML Operations
• Removing a View
• Inline Views
• Sequence
• The CREATE SEQUENCE Statement Syntax
• Creating a Sequence
• Confirming Sequences
• NEXTVAL and CURRVAL Pseudo columns
• Using a Sequence
• Modifying a Sequence
• Guidelines for Modifying a Sequence
• Removing a Sequence
• Index
• How Are Indexes Created?
• Creating an Index
• When to Create an Index
• When Not to Create an Index
• Confirming Indexes
• Function-Based Indexes
• Removing an Index
• Synonyms
• Creating and Removing Synonyms

• Introduction to PL/SQL
• Advantages of PL/SQL
• The PL/SQL Block
• Basic PL/SQL Block Structure
• The Declaration Section
• The Execution Section
• The Exception Section
• Generation Output

Variables and Constants

• Data types
• Scalar Data types
• Composite Data types
• Variables (%type, %rowtype)
• Variable Assignment
• Scope of Variables
• Constants
• Writing Sample programs demonstrating all above Functionalities

Conditions and Loops

• Conditional Control
• Iterative Control
• Guidelines
• Sample, While, and For Loops
• While Loop
• For Loop

Cursors

• Implicit and Explicit Cursors
• Cursors Actions
• Declaring a Cursors
• Opening a Cursors
• Fetching Data from ad Cursor
• Closing a Cursor
• Cursor Attributes
• Cursors and Loops
• Cursors For Loops

Exception Handling

• Guidelines
• Types of Exceptions
• Named System Exceptions
• Named User-Defined Exceptions
• Unnamed System Exceptions
• Unnamed User-Defined Exceptions
• SQL ERRM and SQL Code

Procedures

• Writing Procedures using Oracle SQL,PLSQL
• Types of Program Units
• Named and Unnamed Block Structures
• Diff between Anonymous Blocks and named Blocks
• Procedures Parameters
• Parameter Modes
• IN
• Out
• IN Out
• Testing Procedure from anonymous blocks
• Calling procedure from another procedure

User Defined Functions

• Using Functions within Oracle
• Difference ways of Callings a function
• Named Notation
• Functions
• Function Parameters
• Referencing a Function

Packages

• Defining and Using Packages
• Advantages of Using Packages
• Overloading
• Dependency
• Abstraction – Information Hiding
• Inheritance
• Defining Global Variables

Database Triggers

• Trigger Types
• Trigger Events
• Trigger Restrictions
• Trigger Body
• Complication of Database Triggers
• Trigger Syntax
• Usage of Instead of Triggers

An Overview of Oracle Large Objects

• BLOB
• CLOB
• PL/SQL Table
• VARRAY
• BFILE
• Nested Tables