International Centre for Information Systems and Audit, (iCISA)

Office of the Comptroller and Auditor General of India,

A-52, Sector 62, Noida – 201301 (UP), India








101-B Special Training Programme on

“Writing SQL statements & Audit of Oracle database Audit”


SAI Uganda

(16 August to 26 August, 2011)


Training Programme on ‘Writing SQL statements & Audit of Oracle database’ for SAI Uganda at iCISA, Noida

(16th -26th August 2011





Session I

(10.00 to 11.15)

Session II

(11.45 to 13.00)

Session III

(14.00 to 15.15)

Session IV

(15.45 to 17.00)


16 August 2011 (Tuesday)

Introduction to RDBMS basics

Writing Basic SQL statements-Basic SELECT Statements, Selecting Columns, Column Heading

Writing Basic SQL statements- Arithmetic Expressions and Operators, Using Column Aliases, Concatenation Operator

Writing Basic SQL statements- Using Literal Character Strings, Eliminating Duplicate Rows, Displaying Table Structure


17 August 2011 (Wednesday)

Restricting and Sorting Data-Limiting Rows using Selection, Character Strings and Dates, Comparison Operators, using the Where clause, using the Between, IN, LIKE, IS NULL operators

Restricting and sorting data-Logical Operators, ORDER BY Clause, Sorting in Descending Order, Sorting by Column Alias, Sorting by Multiple Columns

Single Row Functions-Character Functions(Case Conversion, Character Manipulation), Number Functions(ROUND, TRUNC, MOD)

Single Row Functions- Working with Dates(Using Arithmetic Operators with Dates), Using Date Functions(MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY)


18 August 2011 (Thursday)

Single Row Functions-Data Conversions, DECODE Function, Nesting Functions

Displaying data from Multiple Tables-Cartesian Product, Type of Joins, Using Table Aliases

Displaying data from Multiple Tables-Joining more than two Tables, Using Outer Joins, Self Joins

Aggregating Data Using Group Functions-AVG and SUM Functions, MIN and MAX Functions, COUNT Functions, Using the NVL Function with Group Functions


19 August  2011 (Friday)

Aggregating Data Using Group Functions-GROUP BY clause, Grouping by more than one Column, Nesting Group Functions

Sub queries-Types of Sub queries, Single Row Sub queries, Multiple Row Sub queries

Multiple Column Sub queries-Column Comparisons, Null Values in a Sub query

The ACCEPT Command, DEFINE and UNDEFINE Commands



22 August 2011 (Monday)

Manipulating Data-Data Manipulation Language

Manipulating Data-the Insert Statement- Inserting new Rows, Inserting Rows with Null Values, Special Values, Specific Data Values, Using Substitution Variables

Manipulating Data-the Update Statement-Updating Rows in a Table, Updating with Multiple-Column Sub query, Updating Rows based on Another Table, Integrity Constraints Error

Manipulating Data-the Delete Statement-Deleting Rows from a Table, Deleting Rows Based on Another Table, Integrity Constraint Error


23 August 2011 (Tuesday)

Creating and Managing Tables-Database Objects, Naming onventions, The CREATE TABLE Statement, The DEFAULT Option, Tables in the Oracle Database, Querying the Data Dictionary

Creating and Managing Tables-Datatypes, Creating a Table by Using a Sub query, The ALTER TABLE Statement, Dropping a Table, Truncating a Table

Creating Views-Simple Views and Complex Views, Creating a View, Retrieving Data from a View

Creating Views-Querying a View, Modifying a View, Creating a Complex View


24 August  2011 (Wednesday)

Creating and importing data dump into Oracle; Backup, Restore in SQL Server 2005

Importing ORACLE files in IDEA using ODBC

Addressing Data Integrity of database, Use of Index, Sort Display, Search Find, and Go To features of IDEA



25 August 2011 (Thursday)

Record Extractions, Field summarization, Field Manipulation

Duplicate and Gap detection, Stratification


26 August 2011 (Friday)

File join, append

Ageing and Sampling, using @ functions

Doubt Clearance




Core faculty: Shri R.M Johri, PD (IS&IT Audit) and Shri Deepak Kapoor, Director

Support faculty: Shri K. P. Singh, Shri S. C. Naithani, Shri Anurag Krishna



Tea Break: 1115 hrs. to 1145 hrs             Lunch Break:           1300 hrs. to 1400 hrs.          Coffee Break: 1515 hrs. to 1545 hrs.