Oracle SQL / PLSQL Course Content.
 File and File Management System
 Disadvantages of FMS
 Introduction to Data Base Management System
 Data Base and classification of Databases
 Introduction to RDBMS
 Features of RDBMS
 Vendors of different RDBMS packages
 Oracle Versions and Features in different Versions
 Normalization
 Introduction about SQL,SQL*PLUS,I SQL*PLUS
 Sub Languages in SQL
o Data Definition Language(DDL)
o Data Manipulation Language(DML)
o Data Query Language(DQL)
o Transaction Control Language(TCL)
o Data Control Language(DCL)
 Oracle Data types
o Predefined Data types
o Composite Data types
 DDL Commands
Create,Alter,Rename,Drop and Truncate
 DML Commands
Insert,Update,Delete
Difference between Delete and Truncate
 Operators and Select Command
 Arithmetic operators, Comparison operators
 And logical operators
 Retrieving Data from tables using SELECT Command
 Retrieving Data based on conditions
 Arithmetic expressions in select
 Special operators
 IN,BETWEEN
 LIKE,IS NULL
 Built-in Functions
 Numeric,Character,date and conversion functions
 Miscellaneous functions
 Group functions
 Grouping the result of Query
 Select with group functions
 Select with group by, having and order by clauses
 Introduction to set operators
 Union all,union,Intersect and Minus
 Querying multiple tables
 Advantage of joins
 Cross join,Inner join,Equi join,non-equi join
 Left outer join
 Right outer join
 Full outer join,Self join
 Working With Sub queries
 Single row sub queries
 Multi row sub queries
 Special operators- IN,ANY,ALL and
 EXISTS
 Co-related sub queries
 Scalar sub queries
 Integrity Constraints
 Importance of integrity
 Types of Constraints
 Creating tables with Constraints
 Adding Constraints for existing tables
 Self referential integrity
 Introduction to TCL Commands
 Commit
 Rollback
 Save point
 Working with DCL Commands
 Grant, Revoke
 Pseudo columns in Oracle
 Row id, rownum
 Currval,nextval
 Level

 Introduction to views
 Advantage of views
 Simple View
 Complex View
 Creating view without table
 Inline View

 Introduction to materialized views
 Creating materialized view

 Introduction to Synonyms
 Diff between Synonym and view
 Private Synonym
 Public Synonym
 Advantage of materialized view over the Synonym

 Working with Sequences
 Creating, altering and dropping the Sequence
 Working with Indexes
 Importance of index
 Types of indexes

PL/SQL
 Introduction to PL/SQL
 Benefits of PL/SQL over SQL
 Architecture of PL/SQL
 PL/SQL Block Structure
 Variables, Constants
 Comment mechanism
 Writing Executable Statements
 Interact with the Oracle server
 Naming Conventions
 Working with Cursors
 Importance of Cursors
 Implicit, Explicit Cursors
 Why We Use Explicit Cursors
 Conditional IF Statements
 IF-THEN-END IF
 IF-THEN-ELSE-END IF
 IF-THEN-ELSIF-END IF
 Iterative Statements
 Basic loop
 While loop
 for loop
 Working with Composite data types
 PL/SQL tables
 PL/SQL records
 Exceptions in PL/SQL
 Classification of errors in PL/SQL block
 Advantages of exceptions
 Predefined Exceptions
 User defined Exceptions
 Raise_application_error
 Stored Procedure
 Creating Procedures without parameters
 Cursors in Procedure
 Procedures with parameters(IN,OUT,INOUT)
 Actual, formal Parameters
 Positional, named notations
 Sharing procedure
 Parameters with default values
 User defined functions
 Creating functions
 Function returning Boolean values
 Calling functions in SQL statement
 Diff between function and Procedure
 Packages in PL/SQL
o Creating package specification and body
o Advantage of Variables,Cursor,Exception
o specification
o Overloading package subprograms
o Advantage of packages
 Working with triggers
 DML triggers
 Instead of triggers
 Statement and row type triggers
 Usage of old and new qualifiers
 Table mutating errors
 Object Technology
 Creating objects
 Creating tables with objects
 Inserting and retrieving data from object based tables
 Difference between column and row object
 Nested objects
 Object with methods
 Working with Collections
 Advantages of Collections
 Nested tables