Understanding Concepts of Excel

• Creation of Excel Sheet Data

• Range Name, Format Painter

• Conditional Formatting, Wrap Text, Merge & Centre

• Sort, Filter, Advance Filter

• Different type of Chart Creations

• Auditing, (Trace Precedents, Trace Dependents)Print Area

• Data Validations, Consolidate, Subtotal

• What if Analysis (Data Table, Goal Seek, Scenario)

• Solver, Freeze Panes

• Various Simple Functions in Excel(Sum, Average, Max, Min)

• Real Life Assignment work

Ms Excel Advance

• Advance Data Sorting

• Multi-level sorting

• Restoring data to original order after performing sorting

• Sort by icons

• Sort by colours

• Lookup Functions

• Subtotal, Multi-Level Subtotal

• Grouping Features

• Consolidation With Several Worksheets

• Filter

• Printing of Raw & Column Heading on Each Page

• Workbook Protection and Worksheet Protection

• Specified Range Protection in Worksheet

• Excel Data Analysis

• Data Table

• Pivot Table

• Generating MIS Report In Excel

• Text Functions

• Lookup & Reference Function

• Logical Functions & Date and Time Functions

• Database Functions

• Statistical Functions

• Financial Functions

• Functions for Calculation Depreciation

SQL Server Fundamentals

• SQL Server 2019 Installation

• Service Accounts & Use, Authentication Modes & Usage, Instance Congurations

• SQL Server Features & Purpose

• Using Management Studio (SSMS)

• Conguration Tools & SQLCMD

• Conventions & Collation

SQL Server 2019 Database Design

• SQL Database Architecture

• Database Creation using GUI

• Database Creation using T-SQL scripts

• DB Design using Files and File Groups

• File locations and Size parameters

• Database Structure modications

SQL Tables in MS SQL Server

• SQL Server Database Tables

• Table creation using T-SQL Scripts

• Naming Conventions for Columns

• Single Row and Multi-Row Inserts

• Table Aliases

• Column Aliases & Usage

• Table creation using Schemas

• Basic INSERT

• UPDATE

• DELETE

• SELECT queries and Schemas

• Use of WHERE, IN and BETWEEN

• Variants of SELECT statement

• ORDER BY

• GROUPING

• HAVING

• ROWCOUNT and CUBE Functions

Data Validation and Constraints

• Table creation using Constraints

• NULL and IDENTITY properties

• UNIQUE KEY Constraint and NOT NULL

• PRIMARY KEY Constraint & Usage

• CHECK and DEFAULT Constraints

• Naming Composite Primary Keys

• Disabling Constraints & Other Options

Views and Row Data Security

• Benets of Views in SQL Database

• Views on Tables and Views

• SCHEMA BINDING and ENCRYPTION

• Issues with Views and ALTER TABLE

• Common System Views and Metadata

• Common Dynamic Management views

• Working with JOINS inside views

Indexes and Query tuning

• Need for Indexes & Usage

• Indexing Table & View Columns

• Index SCAN and SEEK

• INCLUDED Indexes & Usage

• Materializing Views (storage level)

• Composite Indexed Columns & Keys

• Indexes and Table Constraints

• Primary Keys & Non-Clustered Indexes

Stored Procedures and Benets

• Why to use Stored Procedures

• Types of Stored Procedures

• Use of Variables and parameters

• SCHEMABINDING and ENCRYPTION

• INPUT and OUTPUT parameters

• System level Stored Procedures

• Dynamic SQL and parameterization

System functions and Usage

• Scalar Valued Functions

• Types of Table Valued Functions

• SCHEMABINDING and ENCRYPTION

• System Functions and usage

• Date Functions

• Time Functions

• String and Operational Functions

• ROW_COUNT

• GROUPING Functions

Triggers, cursors, memory limitations

• Why to use Triggers

• DML Triggers and Performance impact

• INSERTED and DELETED memory tables

• Data Audit operations & Sampling

• Database Triggers and Server Triggers

• Bulk Operations with Triggers

Cursors and Memory Limitations

• Cursor declaration and Life cycle

• STATIC

• DYNAMIC

• SCROLL Cursors

• FORWARD_ONLY and LOCAL Cursors

• KEYSET Cursors with Complex SPs

Transactions Management

• ACID Properties and Scope

• EXPLICIT Transaction types

• IMPLICIT Transactions and options

• AUTOCOMMIT Transaction and usage

Introduction to Power BI

• Overview of BI concepts

• Why we need BI

• Introduction to SSBI

• SSBI Tools

• Why Power BI

• What is Power BI

• Building Blocks of Power BI

• Getting started with Power BI Desktop

• Get Power BI Tools

• Introduction to Tools and Terminology

• Dashboard in Minutes

• Interacting with your Dashboards

• Sharing Dashboards and Reports

Power BI Desktop

• Power BI Desktop

• Extracting data from various sources

• Workspaces in Power BI

Power BI Data Transformation

• Data Transformation

• Query Editor

• Connecting Power BI Desktop to our Data Sources

• Editing Rows

• Understanding Append Queries

• Editing Columns

• Replacing Values

• Formatting Data

• Pivoting and Unpivoting Columns

• Splitting Columns

• Creating a New Group for our Queries

• Introducing the Star Schema

• Duplicating and Referencing Queries

• Creating the Dimension Tables

• Entering Data Manually

• Merging Queries

• Finishing the Dimension Table

• Introducing the another DimensionTable

• Creating an Index Column

• Duplicating Columns and Extracting Information

• Creating Conditional Columns

• Creating the FACT Table

• Performing Basic Mathematical Operations

• Improving Performance and Loading Data into the Data Model

Modelling with Power BI

• Introduction to Modelling

• Modelling Data

• Manage Data Relationship

• Optimize Data Models

• Cardinality and Cross Filtering

• Default Summarization & Sort by

• Creating Calculated Columns

• Creating Measures & Quick Measures

Data Analysis Expressions (DAX)

• What is DAX

• Data Types in DAX

• Calculation Types

• Syntax, Functions, Context Options

• DAX Functions

• Measures in DAX

• Measures and Calculated Columns

• ROW Context and Filter Context in DAX

• Operators in DAX - Real-time Usage

• Quick Measures in DAX - Auto validations

• In-Memory Processing DAX Performance

Power BI Desktop Visualisations

• How to use Visual in Power BI

• What Are Custom Visuals

• Creating Visualisations and Colour Formatting

• Setting Sort Order

• Scatter & Bubble Charts & Play Axis

• Tooltips and Slicers, Timeline Slicers & Sync Slicers

• Cross Filtering and Highlighting

• Visual, Page and Report Level Filters

• Drill Down/Up

• Hierarchies and Reference/Constant Lines

• Tables, Matrices & Conditional Formatting

• KPI's, Cards & Gauges

• Map Visualizations

• Custom Visuals

• Managing and Arranging

• Drill through and Custom Report Themes

• Grouping and Binning and Selection Pane, Bookmarks & Buttons

• Data Binding and Power BI Report Server

Introduction to Power BI Dashboard and Data Insights

• Why Dashboard and Dashboard vs Reports

• Creating Dashboards

• Conguring a Dashboard Dashboard Tiles, Pinning Tiles

• Power BI Q&A

• Quick Insights in Power BI

Direct Connectivity

• Custom Data Gateways

• Exploring live connections to data with Power BI

• Connecting directly to SQL Server

• Connectivity with CSV & Text Files

• Excel with Power BI Connect Excel to Power BI, Power BI Publisher for Excel

• Content packs

• Update content packs

Publishing and Sharing

• Introduction and Sharing Options Overview

• Publish from Power BI Desktop and Publish to Web

• Share Dashboard with Power BI Service

• Workspaces (Power BI Pro) and Content Packs (Power BI Pro)

• Print or Save as PDF and Row Level Security (Power BI Pro)

• Export Data from a Visualization

• Export to PowerPoint and Sharing Options Summary

Refreshing Datasets

• Understanding Data Refresh

• Personal Gateway (Power BI Pro and 64-bit Windows)

• Replacing a Dataset and Troubleshooting Refreshing

Introduction To Python

• Installation and Working with Python

• Understanding Python variables

• Python basic Operators

• Understanding the Python blocks.

Python Keyword and Identiers

• Python Comments, Multiline Comments.

• Python Indentation

• Understating the concepts of Operators

Introduction To Variables

• Variables, expression condition and function

• Global and Local Variables in Python

• Packing and Unpacking Arguments

• Type Casting in Python

• Byte objects vs. string in Python

• Variable Scope

Python Data Type

• Declaring and using Numeric data types

• Using string data type and string operations

• Understanding Non-numeric data types

• Understanding the concept of Casting and Boolean.

• Strings

• List

• Tuples

• Dictionary

• Sets

Control Structure & Flow

• Statements if, else, elif

• How to use nested IF and Else in Python

• Loops

• Loops and Control Statements.

• Jumping Statements Break, Continue, pass

• Looping techniques in Python

• How to use Range function in Loop

• Programs for printing Patterns in Python

• How to use if and else with Loop

• Use of Switch Function in Loop

• Elegant way of Python Iteration

• Generator in Python

• How to use nested Loop in Python

• Use If and Else in for and While Loop

• Examples of Looping with Break and Continue Statement

• How to use IN or NOT IN keyword in Python Loop.

Python Function, Modules and Packages

• Python Syntax

• Function Call

• Return Statement

• Arguments in a function Required, Default, Positional, Variable-length

• Write an Empty Function in Python pass statement.

• Lamda/ Anonymous Function

• *args and **kwargs

• Help function in Python

• Scope and Life Time of Variable in Python Function

• Nested Loop in Python Function

• Recursive Function and Its Advantage and Disadvantage

• Organizing python codes using functions

• Organizing python projects into modules

• Importing own module as well as external modules

• Understanding Packages

• Random functions in python

• Programming using functions, modules & external packages

• Map, Filter and Reduce function with Lambda Function

• More example of Python Function

List

• What is List.

• List Creation

• List Length

• List Append

• List Insert

• List Remove

• List Append & Extend using + and Keyword

• List Delete

• List related Keyword in Python

• List Revers

• List Sorting

• List having Multiple Reference

• String Split to create a List

• List Indexing

• List Slicing

• List count and Looping

• List Comprehension and Nested Comprehension

Tuple

• What is Tuple

• Tuple Creation

• Accessing Elements in Tuple

• Changing a Tuple

• Tuple Deletion

• Tuple Count

• Tuple Index

• Tuple Membership

• TupleBuilt in Function (Length, Sort)

Dictionary

• Dict Creation

• Dict Access (Accessing Dict Values)

• Dict Get Method

• Dict Add or Modify Elements

• Dict Copy

• Dict From Keys.

• Dict Items

• Dict Keys (Updating, Removing and Iterating)

• Dict Values

• Dict Comprehension

• Default Dictionaries

• Ordered Dictionaries

• Looping Dictionaries

• Dict useful methods (Pop, Pop Item, Str , Update etc.)

Sets

• What is Set

• Set Creation

• Add element to a Set

• Remove elements from a Set

• PythonSet Operations

• Frozen Sets

Strings

• What is Set

• Set Creation

• Add element to a Set

• Remove elements from a Set

• PythonSet Operations

Python Exception Handling

• Python Errors and Built-in-Exceptions

• Exception handing Try, Except and Finally

• Catching Exceptions in Python

• Catching Specic Exception in Python

• Raising Exception

• Try and Finally

Python File Handling

• Opening a File

• Python File Modes

• Closing File

• Writing to a File

• Reading from a File

• Renaming and Deleting Files in Python

• Python Directory and File Management

• List Directories and Files

• Making New Directory

• Changing Directory

Python Database Interaction

• SQL Database connection using

• Creating and searching tables

• Reading and Storing cong information on database

• Programming using database connections

Reading an excel

• Working With Excel

• Reading an excel le using Python

• Writing to an excel sheet using Python

• Python| Reading an excel le

• Python | Writing an excel le

• Adjusting Rows and Column using Python

• ArithmeticOperation in Excel le.

• Play with Workbook, Sheets and Cells in Excel using Python

• Creating and Removing Sheets

• Formatting the Excel File Data

• More example of Python Function

Complete Understanding of OS Module of Python

• Check Dirs. (exist or not)

• How to split path and extension

• How to get user prole detail

• Get the path of Desktop, Documents, Downloads etc.

• Handle the File System Organization using OS

• How to get any les and folders details using OS