Oracle

• ORACLE

This course is meant for everyone who would like to visualize how information gets stored and delivered to end users and a solid understanding of the Oracle products used in professional job roles.

• Application Developer
• Database Developer
• Support Analyst

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

• • Datatypes 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