Introduction to DBMS

• DBMS, Data, Information, Database
Data models – FMS/ HDBMS/
NDBMS/ RDBMS/ ORDBMS
• E-R Diagram, Normalization, Codd Rules
• RDBMS Packages • Versions

SQL (Structured Query Language)

• SQL and SQL*Plus Commands
• Types of commands in SQL
• How to login into SQL
• HR user, locking & unlocking user

Retrieving data

• Select statement usage
• Alias names – columns & expressions
• Applying filters using operators
• Operators – Relational/ Logical / Special
• Distinct clause, Order by clause
• Sorting NULL values

Working with tables

• Datatypes in Oracle
• Naming conventions; Rules – tables/columns
• Creating Tables in different methods

DML operations
• Insert data in different methods
• Update / Delete data

DDL commands
• Alter / Truncate / Rename / Drop table
• Recyclebin concept • Flashback / Purge

Table Constraints

• Data Integrity and importance
• Not Null/Unique/Primary Key/ Check
• References/Foreign Key
• Add/enable/disable/drop constraints
• Composite primary key/unique key
• About ‘User_constraints’ table

TCL commands (Transaction Handling)

• About login session • Redo log files
• Commit / Rollback / Savepoint
• Working with multiple savepoints

DCL Commands

• User management, SYSTEM user
• Create user, Grant basic privileges
• Change password, lock/unlock user
• Grant/Revoke a user as DBA
• Data sharing between users
• Grant and Revoke commands
• Accessing another user data
• Dead lock situations
About ‘User_tab_privs_made’, ‘User_tab_privs_recd’

Database functions

• Group / Row / Numeric / String
• Conversion functions
• Dates & date formats
• Date functions, Misc. functions
• Pseudo columns

Data grouping

• Group by / Having clause
• ‘where’ versus ‘having’ clause
• Using expressions in group by
Set operators
• Union/ Intersect/ Minus/ Union All

Subqueries

• Simple / Multiple row subquery
• Nested / Co-related subquery

Joins tables

• Simple join (equi and non-equi)
• Self/ inner / Outer join (Left/Right/Full)
• Cartesian/Cross join
• ANSI Standard of joins

Oracle Database objects

• Synonyms • Views
• Sequences • Indexes

PLSQL

PL/SQL architecture

• PL/SQL engine
• Structure of a PL/SQL program
• Different section of a program
• Data types in PL/SQL
• Operators, I/O operations
• Write expressions, simple program
• Extracting data from tables

Control statements

• If-Then-Else – Simple, Nested
• Compound conditions (and/or/not)
• Case – End Case
• Loops – For/ While/ Simple/ ForALL

Exception Handling

• Introduction • Types of Exceptions
• Runtime errors vs. Exceptions
• System / User defined exceptions
• Using multiple exceptions
• Raise_Application_Error()

Cursor management

• Cursor types – Implicit / Explicit
• Cursor attributes
• Declaring, opening, closing
• Fetching, checking End of cursor
• Parameterized cursor
• for loops in cursor processing
• DML operations using cursors
• Cursor using joins

Sub programs

Stored Procedures

• Creating a simple procedure
• Compiling, verifying errors
• Executing procedure from SQL prompt
• Calling procedure in another PL/SQL program
• Procedure including DML operations
• Procedures with parameters
• Types of parameters (IN, OUT & IN OUT)
• Cursors in a procedure

User defined Functions

• Writing a user’s function
• About ‘Return’ statement
• Compiling, verifying errors
• Executing from SQL prompt
• Functions with parameters (IN, OUT & IN OUT)

Packages

• Create package specification / body
• Calling the elements of a package
• Adv. of package over procedure/function
• Implementing Polymorphism
• Function overloading
• Procedure overloading

Database Triggers

• Types of triggers
• Creating before/after triggers
• OLD, NEW reference for data references
• DML operations using triggers
• ‘Instead of’ triggers • Triggers Vs. constraints
• Transaction auditing using triggers