SQL & PL/SQL

 Course Syllabus

1. DBMS Concepts
• What is Database?
• What is Database System?
• E.F.CODD’s Rules
• RDBMS
• ORDBMS
• Difference Between DBMS, RDBMS & ORDBMS

2. SQL

• What is SQL?
• DDL Commands
• Create, Alter, Drop
• Truncate, Rename
• DML Command – Insert

SQL SELECT statements

a. Order by Clause
b. Distinct Clause
c. Where Clause
d. Operators
e. Comparison Conditions

Constraints
a. Primary Key Constraint
b. Unique Constraint
c. Not Null Constraint
d. Foreign Key Constraint
e. Check Constraint
f. Cascade Constraints

Joins
a. What is a Join?
b. Cartesian Join
c. Equi Join
d. Non – Equi Join
e. Self Join
f. Outer Join
g. Left Outer Join
h. Right Outer Join
i. Full Outer Join
j. Pseudo Columns

Functions
a. Numeric Functions
b. Character Functions
c. Date Functions
d. Conversion Functions
e. General Functions
f. Aggregating Data Using Group Functions

Sub Queries
a. Simple Sub Queries
b. Multiple Row Sub Queries

Update
a. Data Updating
b. Updating data using Sub Queries

Delete
a. Deleting Data
b. Deleting data using Sub Queries
c. Deleting data using join

Transaction Control Language (TCL)
a. Commit
b. Roll back
c. Save point

Database Objects
a. View
b. Synonym
c. Sequence
d. Index
e. Privileges

View
a. Simple View
b. Complex View
c. In Line View

Synonym
a. Private Synonym
b. Public Synonym

Privileges
a. Grant
b. Revoke

3. PL/SQL

• Block Structure of PL/SQL
• Data Types, Variables & Constants
Flow Control Statements
a. Simple IF
b. IF-THEN-ELSE Statements
c. IF-THEN-ELSIF Statements

Iterative Statements
a. Simple Loop
b. While Loop
c. For Loop

Attribute Data Types
a. %type
b. %Rowtype

Cursor Management
a. What is Cursor?
b. Implicit Cursor
c. Explicit Cursor
d. Cursor with parameters
e. Cursor with For Loop

Exceptions
a. What is an Exception?
b. Predefined Exception
c. User defined Exception

Sub Programs
a. Named PL/SQL Block
b. Stored Procedures
c. Types of Parameters
d. Stored Function
e. Difference between Function and Procedure
Package
a. What is package?
b. Importance of package
Triggers
a. What is a Trigger?
b. Row Level Trigger
c. Table Level Trigger
d. Instead of Trigger
e. DML Triggers
f. System Triggers