Data Analysis 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

·      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

·      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

·      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

·      Join,                       

·      Tables                  

·      Variables

·      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          

·      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

·      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

·      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

·      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?

·      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

·      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

·      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

·      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

·      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

·      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

·      Applying transactions                 

·      Using the transaction behavior to identify DML statements  

·      Learning about implicit and explicit transactions

·      Isolation levels management                  

·      Understanding concurrency and locking behavior

·      Using memory-optimized tables

·      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

·      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