MIS Training

Advanced Excel Course - Overview of the Basics of Excel

Advanced Excel Course - Working with Functions

• Writing conditional expressions (using IF)
• Using logical functions (AND, OR, NOT)
• Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)
• VlookUP with Exact Match, Approximate Match
• Nested VlookUP with Exact Match
• VlookUP with Tables, Dynamic Ranges
• Nested VlookUP with Exact Match
• Using VLookUP to consolidate Data from Multiple Sheets

Advanced Excel Course - Data Validations

• Specifying a valid range of values for a cell
• Specifying a list of valid values for a cell
• Specifying custom validations based on formula for a cell

Advanced Excel Course - Working with Templates

• Designing the structure of a template
• Using templates for standardization of worksheets

Advanced Excel Course - Sorting and Filtering Data

• Sorting tables
• Using multiple-level sorting
• Using custom sorting
• Filtering data for selected view (AutoFilter)
• Using advanced filter options

Advanced Excel Course - Working with Reports

• Creating subtotals
• Multiple-level subtotals
• Creating Pivot tables
• Formatting and customizing Pivot tables
• Using advanced options of Pivot tables
• Pivot charts
• consolidating data from multiple sheets and files using Pivot tables
• Using external data sources
• Using data consolidation feature to consolidate data
• Show Value As ( % of Row, % of Column, Running Total, Compare with Specific Field)
• Viewing Subtotal under Pivot
• Creating Slicers ( Version 2010 & Above)

Advanced Excel Course - More Functions

• Date and time functions
• Text functions
• Database functions
• Power Functions (CountIf, CountIFS, SumIF, SumIfS)

Advanced Excel Course - Formatting

• Using auto formatting option for worksheets
• Using conditional formatting option for rows, columns and cells

Advanced Excel Course - Macros

• Relative & Absolute Macros?
• Editing Macro's

Advanced Excel Course - WhatIf Analysis

• Goal Seek
• Data Tables
• Scenario Manager

Advanced Excel Course - Charts

• Using Charts
• Formatting Charts
• Using 3D Graphs)
• Using Bar and Line Chart together
• Using Secondary Axis in Graphs
• Sharing Charts with PowerPoint / MS Word, Dynamically
• (Data Modified in Excel, Chart would automatically get updated)

Advanced Excel Course - New Features Of Excel

• Sparklines, Inline Charts, data Charts
• Overview of all the new features

Advanced Excel Course - Final Assignment

• The Final Assignment would test contains questions to be solved at the end of the Course

VBA (VISUAL BASIC FOR APPLICATION) & MACROS

Create a Macro:

• Swap Values, Run Code from a Module, Macro Recorder, Use Relative References, FormulaR1C1, Add a Macro to the Toolbar, Macro Security, Protect Macro

MsgBox:

• MsgBox Function, Input Box Function

Workbook and Worksheet Object:

• Path and Full Name, Close and Open, Loop through Books and Sheets, Sales Calculator, Files in a Directory, Import Sheets, Programming Charts

Range Object:

• Current Region, Dynamic Range, Resize, Entire Rows and Columns, Offset, From Active Cell to Last Entry, Union and Intersect, Test a Selection, Possible Football Matches, Font, Background Colors, Areas Collection, Compare Ranges

Variables:

• Option Explicit, Variable Scope, Life of Variables

If Then Statement:

• Logical Operators, Select Case, Tax Rates, Mod Operator, Prime Number Checker, Find Second Highest Value, Sum by Color, Delete Blank Cells

Loop:

• Loop through Defined Range, Loop through Entire Column, Do Until Loop, Step Keyword, Create a Pattern, Sort Numbers, Randomly Sort Data, Remove Duplicates, Complex Calculations, Knapsack Problem

Macro Errors:

• Debugging, Error Handling, Err Object, Interrupt a Macro, Macro Comments

String Manipulation:

• Separate Strings, Reverse Strings, Convert to Proper Case, Count Words

Date and Time:

• Compare Dates and Times, DateDif Function, Weekdays, Delay a Macro, Year Occurrences, Tasks on Schedule, Sort Birthdays

Events:

• Before DoubleClick Event, Highlight Active Cell, Create a Footer Before Printing, Bills and Coins

Array:

• Dynamic Array, Array Function, Month Names, Size of an Array

Function and Sub:

• User Defined Function, Custom Average Function, Volatile Functions, ByRef and ByVal

Application Object:

• Status Bar, Read Data from Text File, Write Data to Text File

ActiveX Controls:

• Text Box, List Box, Combo Box, Check Box, Option Buttons, Spin Button, Loan Calculator

User form:

• User form and Ranges, Currency Converter, Progress Indicator, Multiple List Box Selections, Multicolumn Combo Box, Dependent Combo Boxes, Loop through Controls, Controls Collection, User form with Multiple Pages, Interactive User form

INTRODUCTION TO SQL

Introduction to Oracle Database

• List the features of Oracle Database 12c
• Discuss the basic design of Database
• Categorize the different types of SQL statements
• Describe the data set used by the course
• Log on to the database using SQL Developer environment
• Save queries to files and use script files in SQL Developer

Retrieve Data using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements
• Select Table Data With All Columns
• Select Table Data With Specific Columns
• Use Arithmetic Operators
• Use Concatenation Operators
• Learn the DESCRIBE command to display the table structure

Learn to Restrict and Sort Data

• Use WHERE clause to limit the output retrieved
• List the comparison operators and logical operators
• Describe the rules of precedence for comparison and logical operators
• Use character string literals in the WHERE clause
• Write queries that contain an ORDER BY clause to sort Data
• SQL Row Limiting Clause (Fitch First, Offset and With Ties)

Usage of Single-Row Functions to Customize Output

• Differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC, and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the DATE functions

Invoke Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic in a SELECT statement

Aggregate Data Using the Group Functions

• Use the aggregation functions to produce meaningful reports
• Divide the retrieved data in groups by using the GROUP BY clause
• Exclude groups of data by using the HAVING clause

Display Data From Multiple Tables Using Joins

• Write SELECT statements to access data from more than one table
• View data that does not meet join condition by using out
• Join a table to itself by using a self-join

Use Sub-queries to Solve Queries

• Describe the types of problem that sub-queries can solve
• Define sub-queries
• List the types of sub-queries
• Write single-row and multiple-row sub-queries

The SET Operators

• Describe the SET operators
• UNION and UNION ALL to Combine Result of Multiple Queries
• Use of INTERSECT
• Use of MINUS
• Control the order of rows returned

Data Manipulation Statements

• Describe each DML statement
• Insert rows into a table
• Change rows in a table by the UPDATE statement
• Delete rows from a table with the DELETE statement
• Save and discard changes with the COMMIT and ROLLBACK statements
• Explain read consistency

Use of DDL Statements to Create and Manage Tables

• Create a simple table
• Alter and Truncate Table
• Drop Table and Concept of Recyclebin
• Stop Entry of Invalid Data Through CONSTRAINTS
• Primary Key , Foreign Key
• Not Null , Unique and Check Constraints

Other Schema Objects

• Create a simple and complex view
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

Control User Access

• Create Users
• Grant System Privileges
• Create and Grant Privileges to a Role
• Change Your Password
• Grant Object Privileges
• How to pass on privileges?
• Revoke Object Privileges

Management of Schema Objects

• Add, Modify, and Drop a Column
• Add, Drop, and Defer a Constraint
• How to enable and Disable a Constraint?
• Create an External Table by Using ORACLE_LOADER
• Query External Tables

Manage Objects with Data Dictionary Views

• Explain the data dictionary
• Use the Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• Query the dictionary views for constraint information
• Query the dictionary views for View and Sequence
• Query the dictionary views for Index
• Comment On table and Cloumns

Manipulate Large Data Sets

• List the types of Multitable INSERT Statements
• Use Multitable Unconditional INSERT ALL Statements
• Use Multitable Conditional INSERT ALL Statement
• Use Multitable Conditional INSERT FIRST Statement
• Merge rows in a table
• Track Changes in Data over a period of time

Data Management in different Time Zones

• Time Zones
• CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP
• DBTIMEZONE and SESSIONTIMEZONE
• Difference between DATE and TIMESTAMP
• INTERVAL Data Types
• Use EXTRACT, TZ_OFFSET and FROM_TZ
• Invoke TO_TIMESTAMP, TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Advance Sub-queries

• Multiple-Column Subqueries
• Pairwise and Non-pairwise Comparison
• Solve problems with Correlated Subqueries
• Update and Delete Rows Using Correlated Subqueries
• The EXISTS and NOT EXISTS operators
• Invoke the WITH clause