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”

For

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

                                                           

 

Day

Date

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)

1

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

2

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)

3

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

4

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

 

5

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

6

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

7

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

 

8

25 August 2011 (Thursday)

Record Extractions, Field summarization, Field Manipulation

Duplicate and Gap detection, Stratification

9

26 August 2011 (Friday)

File join, append

Ageing and Sampling, using @ functions

Doubt Clearance

Valediction

 

 

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.