Business Analytics Master's Program

·    Introduction to Excel spreadsheet

·    Learning to enter data

·    Flling of series and custom fill list

·    Editing and deleting fields

·    Practical Exercise

·    Learning about relative and absolute referencing

·    The concept of relative formulae

·    The issues in relative formulae

·    Creating of absolute and mixed references

·    Practical Exercise

·    Creating names range

·    Using names in new formulae

·    Working with the name box

·    Selecting range

·    Names from a selection

·    Pasting names in formulae

·    Selecting names

·    Working with Name Manager

·    Practical Exercise

·    The various logical functions in Excel

·    The If function for calculating values and displaying text

·    Nested If functions

·    VLookUp and IFError functions

·    Practical Exercise

·    Learning about conditional formatting

·    The options for formatting cells

·    Various operations with icon sets

·    Data bars and color scales

·    Creating and modifying sparklines

·    Practical Exercise

·    Multi-level drop down validation

·    Restricting value from list only

·    Learning about error messages and cell drop down

·    Practical Exercise

·    Introduction to the various formulae in Excel

·    Sum, SumIF & SumIFs

·    Count, CountA, CountIF and CountBlank

·    Networkdays, Networkdays International

·    Today & Now function

·    Trim (Eliminating undesirable spaces)

·    Concatenate (Consolidating columns)

·    Practical Exercise

·    Introduction to dynamic table in Excel

·    Data conversion

·    Table conversion

·    Tables for charts

·    VLOOKUP

·    Practical Exercise

·    Sorting in Excel

·    Various types of sorting

·    Alphabetical

·    Numerical

·    Row

·    Multiple column

·    Working with paste special

·    Hyperlinking

·    Using subtotal

·    Practical Exercise

·    The concept of data filtering

·    Understanding compound filter and its creation

·    Removing of filter

·    Using custom filter and multiple value filters

·    Working with wildcards

·    Practical Exercise

·    Creation of Charts in Excel

·    Performing operations in embedded chart

·    Modifying

·    Resizing

·    Dragging of chart

·    Practical Exercise

·    Introduction to the various types of charting techniques

·    Creating titles for charts

·    Axes

·    Learning about data labels

·    Displaying data tables

·    Modifying axes

·    Displaying gridlines and inserting trendlines

·    Textbox insertion in a chart

·    Creating a 2-axis chart

·    Creating combination chart

·    Practical Exercise

·    The concept of Pivot tables in Excel

·    Report filtering

·    Shell creation

·    Working with Pivot for calculations

·    Formatting of reports

·    Dynamic range assigning

·    The slicers

·    Creating of slicers

·    Practical Exercise

·    Data and file security in Excel

·    Protecting row, column, and cell

·    Different safeguarding techniques

·    Practical Exercise

·    Learning about VBA macros in Excel

·    Executing macros in Excel

·    The macro shortcuts

·    Applications

·    The concept of relative reference in macros

·    Practical Exercise

·    In-depth understanding of Visual Basic for Applications

·    The VBA Editor

·    Module insertion and deletion

·    Performing action with Sub

·    Ending Sub if condition not met

·    Practical Exercise

·    Learning about the concepts of workbooks & worksheets in Excel Protection of macro codes

·    Range coding

·    Declaring a variable

·    The concept of Pivot Table in VBA

·    Introduction to arrays

·    User forms

·    Getting to know how to work with databases within Excel

·    Practical Exercise

·    Learning how the If condition works

·    How to apply it in various scenarios

·    Working with multiple Ifs in Macro

·    Practical Exercise

·    Understanding the concept of looping

·    Deploying looping in VBA Macros

·    Practical Exercise

·    Studying about debugging in VBA

·    The various steps of debugging

·    Understanding breakpoints and way to mark it

·    The code for debugging and code commenting

·    Practical Exercise

·    The concept of message box in VBA

·    Learning to create the message box

·    Various types of message boxes

·    The IF condition as related to message boxes

·    Practical Exercise

·    Mastering the various tasks and functions using VBA

·    Understanding data separation

·    Auto filtering

·    Formatting of report

·    Combining multiple sheets into one

·    Merging multiple files together

·    Practical Exercise

·    Introduction to powerful data visualization with Excel Dashboard

·    Loading the data

·    Managing data and linking the data to tables and charts

·    Creating Reports using dashboard features

·    Practical Exercise

·    Learning to create charts in Excel

·    The various charts available

·    The steps to successfully build a chart

·    Personalization of charts

·    Formatting and updating features

·    Various special charts for Excel dashboards

·    Understanding how to choose the right chart for the right data

·    Practical Exercise

·    Creation of Pivot Tables in Excel

·    Learning to change the Pivot Table layout

·    Generating Reports

·    The methodology of grouping and ungrouping of data

·    Practical Exercise

·    Learning to create Dashboards

·    The various rules to follow while creating Dashboards

·    Creation of dynamic dashboards

·    Knowing what is data layout

·    Introduction to thermometer chart and its creation

·    How to use alerts in the Dashboard setup

·    Practical Exercise

·    How to insert a Scroll bar to a data window

·    Concept of Option buttons in a chart

·    Use of combo box drop-down

·    List box control Usage

·    How to use Checkbox Control

·    Practical Exercise

·    Understanding data quality issues in Excel

·    Linking of data

·    Consolidating and merging data

·    Working with dashboards for Excel Pivot Tables

·    Practical Exercise

·    Various types of databases           

·    Introduction to Structured Query Language         

·    Distinction between client server and file server databases        

·    Understanding SQL Server Management Studio

·    SQL Table basics              

·    Data types and functions              

·    Transaction-SQL               

·    Authentication for Windows                    

·    Data control language                   

·    The identification of the keywords in T-SQL, such as Drop Table

·    Practical Exercise

·    Data Anomalies                

·    Update Anomalies            

·    Insertion Anomalies                      

·    Deletion Anomalies                      

·    Types of Dependencies                 

·    Functional Dependency                

·    Fully functional dependency                    

·    Partial functional dependency                  

·    Transitive functional dependency            

·    Multi-valued functional dependency                   

·    Decomposition of tables               

·    Lossy decomposition                    

·    Lossless decomposition                

·    What is Normalization?                

·    First Normal Form            

·    Second Normal Form                    

·    Third Normal Form                       

·    Boyce-Codd Normal Form(BCNF)                      

·    Fourth Normal Form

·    Practical Exercise

·    Entity-Relationship Model                       

·    Entity and Entity Set                    

·    Attributes and types of Attributes            

·    Entity Sets             

·    Relationship Sets              

·    Degree of Relationship                 

·    Mapping Cardinalities, One-to-One, One-to-Many, Many-to-one, Many-to-many                 

·    Symbols used in E-R Notation

·    Practical Exercise

·    Introduction to relational databases                     

·    Fundamental concepts of relational rows, tables, and columns   

·    Several operators (such as logical and relational), constraints, domains, indexes, stored procedures, primary and foreign keys     

·    Understanding group functions                

·    The unique key

·    Practical Exercise

·    Join,                       

·    Tables                    

·    Variables

·    Practical Exercise

·    SQL functions                   

·    Operators & queries                      

·    Table creation                   

·    Data retrieval from tables             

·    Combining rows from tables using inner, outer, cross, and self joins             

·    Deploying operators such as ‘intersect,’ ‘except,’ ‘union,’         

·    Temporary table creation             

·    Set operator rules              

·    Table variables

·    Practical Exercise

·    Understanding SQL functions                  

·    Scalar functions                

·    Aggregate functions                      

·    Functions that can be used on different datasets, such as numbers, characters, strings, and dates

·    Inline SQL functions                     

·    General functions              

·    Duplicate functions

·    Practical Exercise

·    Understanding SQL subqueries, their rules          

·    Statements and operators with which subqueries can be used     

·    Using the set clause to modify subqueries

·    Understanding different types of subqueries, such as where, select, insert, update, delete, etc 

·    Methods to create and view subqueries

·    Practical Exercise

·    Learning SQL views                     

·    Methods of creating, using, altering, renaming, dropping, and modifying views                     

·    Understanding stored procedures and their key benefits 

·    Working with stored procedures              

·    Studying user-defined functions               

·    Error handling

·    Practical Exercise

·    User-defined functions                 

·    Types of UDFs, such as scalar                  

·    Inline table value              

·    Multi-statement table                    

·    Stored procedures and when to deploy them        

·    What is rank function?                 

·    Triggers, and when to execute triggers?

·    Practical Exercise       

·    SQL Server Management Studio              

·    Using pivot in MS Excel and MS SQL Server     

·    Differentiating between Char, Varchar, and NVarchar   

·    XL path, indexes and their creation                     

·    Records grouping, advantages, searching, sorting, modifying data

·    Clustered indexes creation           

·    Use of indexes to cover queries                

·    Common table expressions                       

·    Index guidelines

·    Practical Exercise

·    Creating Transact-SQL queries                

·    Querying multiple tables using joins                    

·    Implementing functions and aggregating data     

·    Modifying data                 

·    Determining the results of DDL statements on supplied tables and data       

·    Constructing DML statements using the output statement

·    Practical Exercise

·    Querying data using subqueries and APPLY       

·    Querying data using table expressions                 

·    Grouping and pivoting data using queries

·    Querying temporal data and non-relational data

·    Constructing recursive table expressions to meet business requirements       

·    Using windowing functions to group                   

·    Rank the results of a query

·    Practical Exercise       

·    Creating database programmability objects by using T-SQL      

·    Implementing error handling and transactions

·    Implementing transaction control in conjunction with error handling in stored procedures  

·    Implementing data types and NULL

·    Practical Exercise

·    Designing and implementing relational database schema           

·    Designing and implementing indexes                  

·    Learning to compare between indexed and included columns    

·    Implementing clustered index                  

·    Designing and deploying views                

·    Column store views

·    Practical Exercise

·    Explaining foreign key constraints                       

·    Using T-SQL statements               

·    Usage of Data Manipulation Language (DML)

·    Designing the components of stored procedures

·    Implementing input and output parameters          

·    Applying error handling               

·    Executing control logic in stored procedures       

·    Designing trigger logic, DDL triggers, etc

·    Practical Exercise

·    Applying transactions                   

·    Using the transaction behavior to identify DML statements       

·    Learning about implicit and explicit transactions

·    Isolation levels management                    

·    Understanding concurrency and locking behaviour

·    Using memory-optimized tables   

·    Practical Exercise

·    Accuracy of statistics                    

·    Formulating statistics maintenance tasks 

·    Dynamic management objects management        

·    Identifying missing indexes                      

·    Examining and troubleshooting query plans        

·    Consolidating the overlapping indexes                

·    The performance management of database instances

·    SQL server performance monitoring

·    Practical Exercise

·    Correlated Subquery, Grouping Sets, Rollup, Cube

·    Implementing Correlated Subqueries                   

·    Using EXISTS with a Correlated subquery          

·    Using Union Query                       

·    Using Grouping Set Query           

·    Using Rollup                     

·    Using CUBE to generate four grouping sets        

·    Perform a partial CUBE

·    Practical Exercise