Alpha Technical College
25 Years of expertisation
(100% placement Records since 2006 onwards )

Database Design

• Database Models
• Beginnings
• WHAT IS DATA BASE MANAGEMENT SYSTEMS (DBMS)
• Benefits of DBMS
• WHAT IS A RELATIONAL DATA BASE MANAGEMENT SYSTEM (RDBMS)
• DBMS VS/ RDBMS
• NORMALIZATION
• First normal Form
• Second Normal Form
• Third Normal form
• Higher Order Normal Form

Oracle Standard Interfaces

• INTRODUCTION TO STRUCTURED QUERY LANGUAGE (SQL)
• Features of SQL
• Features of SQL *PLUS
• SQL V/s SQL *PLUS
• Rules for SQL
• Components of SQL
• Example of DDL, DML and DCL Commands
• SQL*Plus
• Oracle Architecture
• Viewing a Sample Table
• The SQL Buffer
• The Web-Based Interface

The Sample Database

• The Entity Relationship Model
• Entity Relationship Diagrams
• The Sample Database
• ER Diagram for Sample Database
• Creating the Sample Data
• Viewing Sample Data
• Data Types of the Sample Data
• TABLE FUNDAMENTALS
• Oracle Data Types
• Basic Data Types

Data Definition Language

• Categories of SQL Statements
• The CREATE Statement
• Rules for Creating Tables
• Brief Checklist When Creating Tables
• Inserting Data Into Tables
• The DROP Command
• The ALTER Command
• Integrity Constraints
• Entity Integrity Constraints
• Referential Integrity Constraints
• Modifying Table to Use Constraints
• Checking Constraints
• The Data Dictionary

Data Manipulation Language

• The SELECT Statement - VIEWING DATA IN THE TABLES
• All Rows and All Columns
• Filtering Table Data
• -Selected Columns and All Rows
• Selected Rows and All Column
• Selected Columns And All Selected Rows
• The INSERT Statement
• insertion of a data set into Table from Another Table
• The DELETE Statement
• Removal of all Rows
• Removal Of Specific Row
• Removal of Specific Row Based on the data held the other table
• The UPDATE Statement
• Updating All Rows
• Updating Records Conditionally
• MODIFYING THE STRUCTURE OF TABLES
• Adding New Columns
• Dropping a Column From a table
• Modifying Existing Column
• RENAMING TABLES
• TRUNCATE TABLES
• DESTROYING TABLES

MODULE 8

SQL FUNCTIONS

• Introduction
• The DISTINCT Keyword
• Mathematical Functions
• String Functions
• MANIPULATING DATES IN SQL USING THE DATE ()
• TO_SIZE
• TO_DATE
• Conversion Functions

MODULE 9

Joining Tables

• Joins
• Cartesian Product
• Inner Joins
• Equi-Join
• Table Aliases
• Non-Equi Join
• Non-Key Join
• Natural Join
• Outer Joins
• Right Outer Join
• Left Outer Join
• Full Outer Join

MODULE 10

Set Operators

• Introduction
• Selection Criteria
• Union
• Union All
• Intersect
• Minus

MODULE 14

Introduction

• SQL vs. PL/SQL
• ADVANTAGES OF PL/SQL
• THE GENERIC PL/SQL BLOCK
• The Declare section
• The Begin section
• The Exception section
• The End section
• THE PL/SQL EXECUTION ENVIRONMENT
• PL/SQL
• The Character set
• Literals
• PL/SQL DATA TYPE
• Variables
• Constant
• Logical Comparisons
• comments
• CONTROL STRUCTURE
• Condition control
• Iterative control
• Sequential control

MODULE 15

Declarations and Data Types

• Declarations
• Standard Data Types
• Initialization
• Variable Names
• Specialized Data Types - %TYPE
• Specialized Data Types - %ROWTYPE
• Building Your Own Data Types - Records
• A Quick look at Loops
• Arrays
• Tables
• Nested Blocks

MODULE 15

Language Components

• Introduction
• Assignments
• Decision Making Statements
• Simple Loops
• Loops - for
• Loops - indefinite
• Loops - while
• Simple Loops
• Nested Loops
• Boolean Variables
• PL/SQL Relational Operators
• PL/SQL Logical Operators
• The CASE Construct

MODULE 16

Cursors

• Introduction
• WHAT IS A CURSE?
• Types of cursor
• Implicit cursor
• Explicit cursor
• Cursor Manipulation
• Using the Cursor
• For Loops Cursors
• Cursor Attributes
• Cursor Parameters
• Nested Cursors

MODULE 17

Exceptions

• Errors in Programs
• Run Time Exceptions
• ERROR HANDLING IN PL/SQL
• ORACLE’S NAMED EXCEPTION HANDLERS
• USER-Named Exception Handlers
• User Defined Exception Hand ling (For I/O Validations)
• User Defined Exception Handling (For Business Rule validations)

Transaction Control

• Transactions
• Command Classification
• Savepoints
• The SET TRANSACTION Command

MODULE 7

SQL Operators

• Simple Selects
• COMPUTATIONS DONE ON TABLE DATA
• Arithmetic Operators
• Renaming Column used with Expression lists
• Logical Operators
• Range Searching
• Pattern Matching
• Comparison Operators
• BETWEEN Operator
• The LIKE Operator
• Logical Operators
• IS NULL and IS NOT NULL

MODULE 11

SQL Subqueries

• Introduction
• Using sub-query in the FROM Clause
• Using Correlated Sub-Queries
• Using Multi Column Sub-Queries
• Using Sub-Query in CASE Expressions
• Using Sub-Query in An ORDER BY clause
• Using EXITS / NOT EXISTS Operator

MODULE 12

Groups

• SQL Statements
• GROUP BY Clause And HAVING Clause
• GROUPING DATA FROM TABLES IN SQL
• The Concept of Grouping
• GROUP BY Clause
• HAVING Clause
• Determining Whether Values are Unique
• Group By Using the ROLLUP Operator
• Grouping By Using the CUBE Operator
• Order of a SELECT Statement

MODULE 13

More Database Objects

• VIEWS
• Creating View
• Renaming the Columns of a View
• Updateable Views
• Views Defined From Multiple Tables
• Common Restrictions on Updateable Views
• Destroying a View
• Relational Views
• Updating a View
• Create or Replace
• Indexes
• Address Field in the index
• Duplicate / Unique index
• Creation of an Index
• Creating Simple Index
• Creating Composite Index
• Creation of Unique Index
• Reverse Key Indexes
• Bitmap Indexes
• Function Based Index
• Key-Compressed Index
• Dropping Index
• MULTIPLE INDEXES ON A TABLE
• Instances when the oracle Engine Uses an Index for Data Extraction
• Instances when the oracle Engine Does Uses an Index for Data Extraction
• USING ROWID TO DELETE DUPLICATE ROWS FROM A TABLE
• Inner Select Statement
• Parent SQL Statement

MODULE 18

PL/SQL SECURITY

• LOCKS

• Oracle’s Default Locking Strategy- Implicit Locking
• Types of Locks
• Levels of Locks
• Explicit Locking
• The SELECT…..FOR UPDATE statement
• Using Lock Table statement
• Releasing – Locks
• Explicit Locking Using SQL and the Behavior of the Oracle Engine
• Explicit Locking Using PL/SQL and the Oracle Engine
• Deadlock

MODULE 19

Functions and Procedures

• What Are Procedures / Function?
• Declarative Part
• Executable Part
• Exception Handling Part
• WHERE DO STORED PROCEDURES AND FUNCTION RESIDE?
• How Does The Oracle Engine Create A Stored Procedure / Function?
• How Does The Oracle Engine Execute Procedure / Function?
• ADVANTAGES OF USING A PROCEDURE OR FUNCTION
• PROCEDURES VERSUS FUNCTIONS
• Creating Stored Procedure
• Keywords and Parameters
• Creating A Functions
• Keywords and Parameters
• Using A Function
• Using A Procedure
• FINALLY A COMMIT IS FIRED TO REGISTER THE CHANGES
• DELETING A STORED PROCEDURE OR FUNCTION
• ORACLE PACKAGES
• Components Of An Oracle Package
• Why Use Packages?
• Packages Specification
• Creating Packages
• Package Objects – Private V/S Public
• Variables, Cursors And Constants
• Package State
• Package Dependency
• DATA BASE TRIGERS
• -Inroduction
• Use Of Data Base Triggers
• Data Base Triggers V/S Procedures
• Data Base Triggers V/S Declarative Integrity Constraints
• How To Apply Database Triggers
• TYPES OF TRIGGERS
• Row Triggers
• Statement Triggers
• Before Triggers
• After triggers
• Combination Triggers
• DELETING A TRIGGER
• Application Using Database Triggers
• GENERATING A PRIMARY KEY USING A DATA BASE TRIGGER
• Introduction
• Automatic Primary Key Generation
• Generating Primary Key Using Sequences
• Generating Primary Key Using The MAX FUNCTION
• Generating Primary Key Using A Lookup Table