oracle

Oracle Course Details:

  • Introduction

  • Relational and Object Relational
  • Database Management Systems

  • Relational Database Concept
  • Definition of a Relational Database
  • Data Models
  • Entity Relationship Model
  • Entity Relationship Modeling Conventions
  • Relating Multiple Tables
  • Relational Database Terminology & Properties
  • Communicating with an RDBMS Using SQL
  • SQL Statements
  • Retrieving Data Using the SQL SELECT Statement

  • 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
  • Defining a Null Value
  • Null Values in Arithmetic Expressions
  • Defining a Column Alias
  • Using Column Aliases
  • Concatenation Operator
  • Using Literal Character Strings
  • Duplicate Rows
  • Restricting and Sorting Data

  • Limiting Rows Using a Selection
  • Using the WHERE Clause
  • Character Strings and Dates
  • Using Comparison Conditions
  • Using the BETWEEN Condition
  • Using the IN Condition
  • Using the LIKE Condition
  • Using the NULL Conditions
  • Using the AND Operator
  • Using the OR Operator
  • Using the NOT Operator
  • Rules of Precedence
  • Using the ORDER BY Clause
  • Sorting
  • Substitution Variables
  • Using the & and && Substitution Variable
  • Using the VERIFY Command
  • Using Single-Row Functions to Customize Output

  • Single-Row Functions
  • Character Functions
  • Case-Manipulation Functions
  • Character-Manipulation Functions
  • Number Functions
  • Arithmetic with Dates
  • Using Arithmetic Operators with Dates
  • Using Date Functions
  • Conversion Functions
  • Using the TO_CHAR Function with Dates
  • Using the TO_CHAR Function with Dates
  • Using the TO_CHAR Function with Numbers
  • Using the TO_NUMBER and TO_DATE Functions
  • RR Date Format
  • Nesting Functions
  • Using the NVL Function
  • Using the NVL2 Function
  • Using the NULLIF Function
  • Using the COALESCE Function
  • CASE Expression
  • DECODE Function
  • Regular Expression Functions
  • The REGEXP Function Syntax
  • Using Meta Characters
  • Reporting Aggregated Data Using the Group Functions

  • Types of Group Functions
  • Using the AVG and SUM Functions
  • Using the MIN and MAX Functions
  • Using the COUNT Function
  • Using the DISTINCT Keyword
  • Group Functions and Null Values
  • Nesting Group Functions
  • GROUP BY with ROLLUP and CUBE Operators
  • Displaying Data from Multiple Tables

  • Obtaining Data from Multiple Tables
  • Types of Joins
  • Creating Natural Joins
  • Retrieving Records with Natural Joins
  • Creating Joins with the USING Clause
  • Joining Column Names
  • Qualifying Ambiguous Column Names
  • Using Table Aliases
  • Retrieving Records with the ON Clause
  • Self-Joins Using the ON Clause
  • Applying Additional Conditions to a Join
  • Creating Three-Way Joins with the ON Clause
  • Non-Equijoins
  • Outer Joins
  • INNER Versus OUTER Joins
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
  • Cartesian Products
  • Generating a Cartesian Product
  • Creating Cross Joins
  • Using Subqueries to Solve Queries

  • Using a Subquery
  • Guidelines for Using Subqueries
  • Types of Subqueries
  • Single-Row Subqueries
  • Using Group Functions in a Subquery
  • The HAVING Clause with Subqueries
  • Multiple-Row Subqueries
  • Using the IN/ANY/ALL Operator in
  • Multiple-Row Subqueries
  • Null Values in a Subquery
  • Using the Set Operators

  • Set Operators
  • UNION Operator
  • UNION ALL Operator
  • INTERSECT Operator
  • MINUS Operator
  • Set Operator Guidelines
  • Matching the SELECT Statements
  • Manipulating Data

  • Data Manipulation Language
  • Adding a New Row to a Table
  • Inserting Rows with Null Values
  • Inserting Specific Date Values
  • Copying Rows from Another Table
  • Updating Rows in a Table
  • Updating Two Columns with a Subquery
  • Updating Rows Based on Another Table
  • DELETE Statement
  • Deleting Rows Based on Another Table
  • TRUNCATE Statement
  • Database Transaction

  • Controlling Transactions
  • Rolling Back Changes to a Marker
  • Implicit Transaction Processing
  • State of the Data Before COMMIT or ROLLBACK
  • State of the Data After COMMIT
  • State of the Data After ROLLBACK
  • Statement-Level Rollback
  • Read Consistency
  • Implementation of Read Consistency
  • ACID Transaction Properties
  • Locking mechanism
  • Deadlock situations
  • Using DDL Statements to Create and Manage Tables

  • Database Objects
  • Naming Rules
  • CREATE TABLE Statement
  • Referencing Another User's Tables
  • DEFAULT Option
  • Data Types
  • Defining Constraints
  • ALTER TABLE Statement
  • Dropping a Table
  • Creating Other Schema Objects

  • Creating a View
  • Literal Character Strings
  • Advantages of Views
  • Defining a Null Value
  • Retrieving Data from a View
  • Modifying a View
  • Inline View
  • Removing a View
  • Creating a Sequence
  • NEXTVAL and CURRVAL Pseudocolumns
  • Using a Sequence
  • Caching Sequence Values
  • Modifying a Sequence
  • Guidelines for Modifying a Sequence
  • Creating an Index
  • Index Creation Guidelines
  • Removing an Index
  • Synonyms
  • Creating and Removing Synonyms
  • Controlling User Access

  • Privileges
  • System Privileges
  • Creating Users
  • User System Privileges
  • Granting System Privileges
  • Creating and Granting Privileges to a Role
  • Changing Your Password
  • Object Privileges
  • Granting Object Privileges
  • Passing On Your Privileges
  • Confirming Privileges Granted
  • Revoking Object Privileges
  • Retrieving Data Using Subqueries

  • Multiple-Column Subqueries
  • Column Comparisons
  • Pairwise Comparison Subquery
  • Nonpairwise Comparison Subquery
  • Correlated Subqueries
  • Using the EXISTS Operator
  • Correlated UPDATE
  • Correlated DELETE
  • Hierarchical Retrieval

  • Natural Tree Structure
  • Hierarchical Queries
  • Walking the Tree From the Bottom Up
  • Walking the Tree From the Top Down
  • Formatting Hierarchical Reports Using LEVEL
  • LPAD
  • Pruning Branches
  • Oracle PL/SQL Course Details

    Introduction to PL/SQL

  • About PL/SQL
  • PL/SQL Environment
  • Benefits of PL/SQL
  • PL/SQL Block Structure
  • Block Types
  • Programs Constructs
  • Create an Anonymous Block
  • Execute an Anonymous Block
  • Test the Output of a PL/SQL Block
  • Declaring PL/SQL Variables
  • Use of Variables
  • Identifiers
  • Handling Variables in PL/SQL
  • Declaring and Initializing PL/SQL Variables
  • Delimiters in String Literals
  • Types of Variables
  • Guidelines for Declaring and Initializing PL/SQL Variables
  • Guidelines for Declaring PL/SQL Variables
  • Scalar Data Types
  • Base Scalar Data Types
  • Declaring Variables with the %TYPE Attribute
  • Declaring Boolean Variables
  • Bind Variables
  • Substitution Variables
  • Using DEFINE for User Variable
  • Composite Data Types
  • LOB Data Type Variables
  • Writing Executable Statements
  • PL/SQL Block Syntax and Guidelines
  • Commenting Code
  • SQL Functions in PL/SQL
  • Data Type Conversion
  • Nested Blocks
  • Variable Scope and Visibility
  • Qualify an Identifier
  • Determining Variable Scope
  • Operators in PL/SQL
  • Programming Guidelines
  • Interacting with the Oracle Server

  • SELECT Statements in PL/SQL
  • Retrieving Data in PL/SQL
  • Naming Conventions
  • Manipulating Data Using PL/SQL
  • Inserting Data
  • Updating Data
  • Deleting Data
  • SQL Cursor Attributes for Implicit Cursors
  • Writing Control Structures

  • Controlling Flow of Execution
  • Simple IF Statement
  • IF THEN ELSE Statement
  • IF ELSIF ELSE Clause
  • NULL Values in IF Statements
  • Handling Nulls
  • Logic Tables
  • Boolean Conditions
  • Iterative Control Basic Loops , WHILE Loops,
  • FOR Loops
  • Nested Loops and Labels 5-27
  • Working with Composite Data Types
  • Composite Data Types
  • Creating a PL/SQL Record
  • %ROWTYPE Attribute
  • Advantages of Using %ROWTYPE
  • Inserting a Record by Using %ROWTYPE
  • Updating a Row in a Table by Using a Record
  • Using Explicit Cursors
  • Explicit Cursor Operations
  • Controlling Explicit Cursors
  • Declaring the Cursor
  • Opening the Cursor
  • Fetching Data from the Cursor
  • Closing the Cursor
  • Cursors and Records
  • Cursor FOR Loops
  • %ISOPEN Attribute
  • %ROWCOUNT and %NOTFOUND
  • Handling Exceptions

  • Handling Exceptions with PL/SQL
  • Exception Types
  • Trapping Exceptions
  • Guidelines for Trapping Exceptions
  • Creating Stored Procedures and Functions

  • Procedures and Functions
  • Differences Between Anonymous Blocks and Subprograms Procedure Syntax
  • Invoking the Procedure
  • Function Syntax
  • Invoking the Function
  • Passing Parameter to the Function
  • Invoking the Function with a Parameter
  • Collections

  • INDEX BY Tables or Associative Arrays
  • Creating an INDEX BY Table
  • INDEX BY Table Structure
  • Using INDEX BY Table Methods
  • INDEX BY Table of Records
  • Nested Tables
  • VARRAY
  • Bulk Collect
  • Creating Packages

  • PL/SQL Packages Overview
  • Components of a PL/SQL Package
  • Visibility of Package Components
  • Developing PL/SQL Packages
  • Creating the Package Specification
  • Creating the Package Body
  • Invoking Package Subprograms
  • Creating and Using Bodiless Packages
  • Removing Packages
  • Viewing Packages in the Data Dictionary
  • Advantages of Using Packages
  • Persistent State of Packages
  • Overloading Subprograms
  • Creating Triggers

  • Types of Triggers
  • Types of DML Triggers
  • Trigger Timing
  • Using Conditional Predicates
  • Creating a DML Row Trigger
  • Using OLD and NEW Qualifiers
  • Creating an INSTEAD OF Trigger