Course Duration in Hours
60
60
ORACLE SQL PL/ SQL Course
Content
DBMS CONCEPTS
What is Database
What is Database
Management System
Different Types of
Database Models
Normalization
RDBMS
ORDBMS
Difference Between
DBMS, RDBMS &
ORDBMS
INTRODUCING 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, Savepoint
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)
Functions
SQL Functions
Two Types of SQL Functions
Single-Row Functions
Single-Row Functions
Character 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
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 MultipleRow
Subqueries
Using the ALL Operator in MultipleRow
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
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
Constraints
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
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
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 NonEquijoins
Outer Joins
Outer Joins Syntax
Using Outer Joins
Self Joins
Joining a Table to Itself
Practice 4, Part One: Overview
Joining Tables Using SQL: 1999
Syntax
Creating Cross Joins
Using the WITH CHECK OPTION
Clause
Denying DML Operations
Removing a View
Inline Views
Sequence
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
PL/SQL
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
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
o BLOB
o CLOB
o PL/SQL Table
o VARRAY
o BFILE
o Nested Tables
B.Tech/B.E
M.tech/M.E
MCA
Degree
QMinds Technologies, Marathahalli (Bangalore),Bangalore,IN