• SQL Overview
• RDBMS Concepts
• The Foundation Statements of T-SQL
• Started with SELECT Statement
• Adding Data with the INSERT Statement
• SQL Commands
  • DDL
  • DML
  • DCL
  • DQL
• SQL Data Types

• Basic Commands
  • Create Statement
  • Create Database
  • Create Table
  • Alter Statement
  • Alter Database
  • Alter Table
  • Drop Statement
  • Delete Statement
  • Update Statement
• SQL Operators
  • Arithmetic Operators
  • Comparison Operators
  • Logical Operators

·        NORMALIZATION

• 1NF
• 2NF
• 3NF

·        JOINS

• INNER Join
• LEFT Join
• RIGHT Join
• FULL Join
• SELF Join
• CARTESIAN Join

• SQL Constraints
  • PRIMARY KEY Constraints
  • FOREIGN KEY Constraints
  • UNIQUE Constraints
  • CHECK Constraints
  • DEFAULT Constraints
  • NULL Constraints
• SQL Clauses
  • WHERE Clause
  • ORDER BY Clause
  • GROUP BY Clause
  • HAVING Clause
  • TOP Clause
  • LIKE Clause
  • UNION Clause
  • UNION ALL Clause

• Adding More to Our Queries
  • What Is a Sub-Query?
  • Building a Nested Sub-Query
  • Correlated Sub-Queries
  • How Correlated Sub-Queries Work Correlated Sub-Queries in the WHERE Clause
• The EXISTS Operator

• Using EXISTS in Other Ways
• System Defined Functions:
  • AVERAGE
  • COUNT
  • MAX
    • MIN

• SUM
• SQRT
• RAND
• CONCAT
• GROUP BY
• HAVING
• TRIM
• LENGTH
• UPPER
• LOWER
• REPLACE
• REVERSE
• SUBSTRING
• STUFF
• DATEADD(day,10,@date)
• DATEDIFF (day,exp1,exp2)
• DATEPART(day,exp)
• GETDATE
• CONVERT
• FORMAT
• ISNULL
• Top

·        UDF (USER DEFINED FUNCTIONS)

• Scalar Functions
• Table-valued Functions

·        UNDERSTANDING INDEXES

• How Data Is Accessed in SQL Server
• Creating, Altering, and Dropping Indexes
• The CREATE INDEX Statement

·        VIEWS

• Simple Views
• Views as Filters
• More Complex Views
• Dropping Views

·        STORED PROCEDURES

• Basic Syntax
• Changing Stored Procedures with ALTER
  • Dropping Stored Procedures

• Handling Errors

• Window Functions
  • Row_Over()
  • Partition By
  • Rank
  • Dense Rank

• USING Sequences
  • Auto Increment (Identity Field)
• Triggers

Auto Generated Tables (INSERTED / DELETED)

Day 1.

MODULE: PYTHON ESSENTIALS INTRODUCTION

 What is Python…?

 A Brief history of Python

 Why Should I learn Python…?

Career opportunities after learning Python

Installing Python

Overview of Anaconda and different IDEs

 Day 2.

 How to execute Python program

 Write your first program

 Variables

Different data types

Different Operator

Immutable and Mutable

Object Referencing and Identity

Day 3.

Strings and escape char

String Formatting ( f string)

formats() for string formatting

Input Statement

Indexing, Slicing & Dicing

List

Day 4

Tuple

Dictionary

Sets

Operators in details

Day 5

IF_ELSE

Basic for loops

for loops

Day 6

while loops

nested loops

infinite loops

loops deep dive

Break statement

Continue statement

Day 7

List

List methods

List Comprehension

List Comprehension with If & Else

Day 8

String, String methods

Tuple, Tuple Methods

Dictionary, Dictionary methods

Tuple, Dictionary Comprehension

Day 9

Function in python

Lambda

MAP

Filter

Reduce

Zip

Accumulate

Decorators

Day 10

math_stats_lib

Datetime

Classes in Date time

RegEx

Day 11

Iterable and Iterator

Generator

Generator vs List

Enumerate

Modules

Day 12

OOPS Concept

Class & Objects

__init__

__str__

Abstraction

Inheritance

Super()

Encapsulation

Polymorphism

__init__ module

Day 13

File Handling in Python

File objects and Modes of file operations

Reading, writing and use of ‘with’ keyword

read(), readline(), readlines(),write(),writeline()

Day 14

⦁    Exception Handling

⦁      Understanding exceptions

⦁      try, except, else and finally

⦁      raising exceptions with: raise, assert

Day 15

logging and debugging

Python Connection with SQL Server

Day 16

NumPy Overview

Properties, Purpose, and Types of ndarray

Class and Attributes of ndarray Object

Basic Operations: Concept and Examples

Initializing arrays: random, ones, zeros

Accessing Array Elements: Indexing, Slicing, Iteration, Indexing with Boolean Arrays

Shape Manipulation

Day 17

Pandas

Day 18

Pandas

Day 19

Data analysis – Visualization using Pandas, Matplotlib, Plotly & Seaborne

Day 20

Python EDA project

Day 21

Getting to Know APIs

SOAP vs REST

Calling Your First API Using Python

We’re going to create a API

Introduction to Git

Useful Git Commands

Day 22

Introduction to Big Data

Apache Spark Introduction

PySpark Introduction

Platforms to use PySpark

Day 23

PySpark Data frame

Reading The Dataset

Checking the Data types of the Column(Schema)

Selecting Columns And Indexing

Check Describe option similar to Pandas

Adding Columns

Dropping columns

Renaming Columns

Dropping Columns

Dropping Rows

Various Parameters in dropping functionalities

Handling Missing values by Mean, Median and Mode

Filter Operation

&,|,==

~

PySpark Group By And Aggregate Functions

Day 24

Pyspark Use Case

End of Course

Thanks

1. Introduction to Statistics
   • Why stats is required???
   • Types of statistics
     • Descriptive
     • inferential
   • Types of data
   • Ways to measure the data
   • Sampling technique

    

   2. Data Representation
      • Different ways to represent data
      • Normal distribution curve
      • Skewness, kurtosis

    

   3. Regression and correlation
      • Different methods of regression
      • With case studies
      • Correlation analysis
      • Types of correlation
      • Measurement steps
   4. Hypothesis
      • Steps of hypothesis testing
      • Stages of hypothesis
      • Components
      • Null and alternative hypothesis
      • Type1 and type2 error

    

    

Module 1: INTRODUCTION TO POWER BI

• Introduction to Power BI – Need, Importance
• Power BI – Advantages and Scalable Options
• History – Power View, Power Query, Power Pivot
• Power BI Data Source Library and DW Files
• Cloud Collaboration and Usage Scope
• Business Analyst Tools, MS Cloud Tools
• Power BI Installation and Cloud Account
• Power BI Cloud and Power BI Service
• Power BI Architecture and Data Access
• On-premise Data Access and Microsoft On Drive
• Power BI Desktop – Installation, Usage
• Sample Reports and Visualization Controls
• Power BI Cloud Account Configuration
• Understanding Desktop & Mobile Editions
• Report Rendering Options and End User Access
• Power View and Power Map. Power BI Licenses
• Course Plan – Power BI Online Training

Module 2: CREATING POWER BI REPORTS, AUTO FILTERS

• Report Design with Legacy & .DAT Files
• Report Design with Database Tables
• Understanding Power BI Report Designer
• Report Canvas, Report Pages: Creation, Renames
• Report Visuals, Fields and UI Options
• Experimenting Visual Interactions, Advantages
• Reports with Multiple Pages and Advantages
• Pages with Multiple Visualizations. Data Access
• PUBLISH Options and Report Verification in Cloud
• “GET DATA” Options and Report Fields, Filters
• Report View Options: Full, Fit Page, Width Scale
• Report Design using Databases & Queries
• Query Settings and Data Preloads
• Navigation Options and Report Refresh
• Stacked bar chart, Stacked column chart
• Clustered bar chart, Clustered column chart
• Adding Report Titles. Report Format Options
• Focus Mode, Explore and Export Settings

Module 3: REPORT VISUALIZATIONS and PROPERTIES

• Power BI Design: Canvas, Visualizations and Fields

• Import Data Options with Power BI Model, Advantages
• Direct Query Options and Real-time (LIVE) Data Access
• Data Fields and Filters with Visualizations
• Visualization Filters, Page Filters, Report Filters
• Conditional Filters and Clearing. Testing Sets
• Creating Customized Tables with Power BI Editor
• General Properties, Sizing, Dimensions, and Positions
• Alternate Text and Tiles. Header (Column, Row) Properties
• Grid Properties (Vertical, Horizontal) and Styles
• Table Styles & Alternate Row Colors – Static, Dynamic
• Sparse, Flashy Rows, Condensed Table Reports. Focus Mode
• Totals Computations, Background. Boarders Properties
• Column Headers, Column Formatting, Value Properties
• Conditional Formatting Options – Color Scale
• Page Level Filters and Report Level Filters
• Visual-Level Filters and Format Options
• Report Fields, Formats and Analytics
• Page-Level Filters and Column Formatting, Filters
• Background Properties, Borders and Lock Aspect Class

Module 4 : CHART AND MAP REPORT PROPERTIES

• chart report types and properties
• stacked bar chart, stacked column chart
• clustered bar chart, clustered column chart
• 100% stacked bar chart, 100% stacked column chart
• line charts, area charts, stacked area charts
• line and stacked row charts
• line and stacked column charts
• waterfall chart, scatter chart, pie chart
• Field Properties: Axis, Legend, Value, Tooltip
• Field Properties: Color Saturation, Filters Types
• Formats: Legend, Axis, Data Labels, Plot Area
• Data Labels: Visibility, Color and Display Units
• Data Labels: Precision, Position, Text Options
• Analytics: Constant Line, Position, Labels
• Working with Waterfall Charts and Default Values
• Modifying Legends and Visual Filters – Options
• Map Reports: Working with Map Reports
• Hierarchies: Grouping Multiple Report Fields
• Hierarchy Levels and Usages in Visualizations
• Preordered Attribute Collection – Advantages
• Using Field Hierarchies with Chart Reports
• Advanced Query Mode @ Connection Settings – Options
• Direct Import and In-memory Loads, Advantages

Module 5 : HIERARCHIES and DRILLDOWN REPORTS

• Hierarchies and Drilldown Options
• Hierarchy Levels and Drill Modes – Usage
• Drill-thru Options with Tree Map and Pie Chart
• Higher Levels and Next Level Navigation Options
• Aggregates with Bottom/Up Navigations. Rules
• Multi Field Aggregations and Hierarchies in Power BI
• DRILLDOWN, SHOWNEXTLEVEL, EXPANDTONEXTLEVEL
• SEE DATA and SEE RECORDS Options. Differences
• Toggle Options with Tabular Data. Filters
• Drilldown Buttons and Mouse Hover Options @ Visuals
• Dependent Aggregations, Independent Aggregations
• Automated Records Selection with Tabular Data
• Report Parameters: Creation and Data Type
• Available Values and Default values. Member Values
• Parameters for Column Data and Table / Query Filters
• Parameters Creation – Query Mode, UI Option
• Linking Parameters to Query Columns – Options
• Edit Query Options and Parameter Manage Entries
• Connection Parameters and Dynamic Data Sources
• Synonyms – Creation and Usage Options Class

Module 6 : POWER QUERY & M LANGUAGE – Part 1

• Understanding Power Query Editor – Options
• Power BI Interface and Query / Dataset Edits
• Working with Empty Tables and Load / Edits
• Empty Table Names and Header Row Promotions
• Undo Headers Options. Blank Columns Detection
• Data Imports and Query Marking in Query Editor
• JSON Files & Binary Formats with Power Query
• JavaScript Object Notation – Usage with M Lang.
• Applied Steps and Usage Options. Revert Options
• creating Query Groups and Query References. Usage
• Query Rename, Load Enable and Data Refresh Options
• Combine Queries – Merge Join and Anti-Join Options
• Combine Queries – Union and Union All as New Dataset
• M Language: Nested Join and Join Kind Functions
• REPLACE, REMOVE ROWS, REMOVE COL, BLANK – M Lang
• Column Splits and Filled Up / Filled Down Options
• Query Hide and Change Type Options. Code Generation Class

Module 7 : POWER QUERY & M LANGUAGE – Part 2

• Invoke Function and Freezing Columns
• Creating Reference Tables and Queries
• Detection and Removal of Query Datasets
• Custom Columns with Power Query
• Power Query Expressions and Usage
• Blank Queries and Enumeration Value Generation
• M Language Semitics and Syntax. Transform Types
• IF..ELSE Conditions, Transform Column() Types
• Remove Columns (), Split Columns (), Replace Value ()
• Table.Distinct Options and GROUP BY Options
• Table.Group(), Table.Sort() with Type Conversions
• PIVOT Operation and Table.Pivot(). List Functions
• Using Parameters with M Language (Power Query Editor)
• Advanced Query Editor and Parameter Scripts
• List Generation and Table Conversion Options
• Aggregations using PowerQuery & Usage in Reports
• Report Generation using Web Pages & HTML Tables
• Reports from Page collection with Power Query
• Aggregate and Evaluate Options with M Language
• Creating high-density reports, ArcGIS Maps, ESRI Files
• Generating QR Codes for Reports
• Table Bars and Drill Thru Filters Class

Module 8 : DAX EXPRESSIONS – Level 1

• Purpose of Data Analysis Expressions (DAX)
• Scope of Usage with DAX. Usability Options
• DAX Context : Row Context and Filter Context
• DAX Entities : Calculated Columns and Measures
• DAX Data Types : Numeric, Boolean, Variant, Currency
• Datetime Data Tye with DAX. Comparison with Excel
• DAX Operators & Symbols. Usage. Operator Priority
• Parenthesis, Comparison, Arithmetic, Text, Logic
• DAX Functions and Types: Table Valued Functions
• Filter, Aggregation and Time Intelligence Functions
• Information Functions, Logical, Parent-Child Functions
• Statistical and Text Functions. Formulas and Queries
• Syntax Requirements with DAX. Differences with Excel
• Naming Conventions and DAX Format Representation
• Working with Special Characters in Table Names
• Attribute / Column Scope with DAX – Examples
• Measure / Column Scope with DAX – Examples C

Module 9 : DAX EXPRESSIONS – Level 2

• YTD, QTD, MTD Calculations with DAX
• DAX Calculations and Measures
• Using TOPN, RANKX, RANK.EQ
• Computations using STDEV & VAR
• SAMPLE Function, COUNTALL, ISERROR
• ISTEXT, DATEFORMAT, TIMEFORMAT
• Time Intelligence Functions with DAX
• Data Analysis Expressions and Functions
• DATESYTD, DATESQTD, DATESMTD
• ENDOFYEAR, ENDOFQUARTER,ENDOFMONTH
• FIRSTDATE, LASTDATE, DATESBETWEEN
• CLOSINGBALANCEYEAR,CLOSINGBALANCEQTR
• SAMEPERIOD and PREVIOUSMONTH,QUARTER
• KPIs with DAX. Vertipaq Queries in DAX
• IF..ELSEIF.. Conditions with DAX
• Slicing and Dicing Options with Columns, Measures
• DAX for Query Extraction, Data Mashup Operations
• Calculated Columns and Calculated Measures with DAX

Module 10 : POWERBI DEPLOYMENT & CLOUD

• PowerBI Report Validation and Publish
• Understanding PowerBI Cloud Architecture
• PowerBI Cloud Account and Workspace
• Reports and DataSet Items Validation
• Dashboards and Pins – Real-time Usage
• Dynamic Data Sources and Encryptions
• Personal and Organizational Content Packs
• Gateways, Subscriptions, Mobile Reports
• Data Refresh with Power BI Architecture
• PBIX and PBIT Files with Power BI – Usage
• Visual Data Imports and Visual Schemas
• Cloud and On-Premise Data Sources
• How PowerBI Supports Data Model?
• Relation between Dashboards to Reports
• Relation between Datasets to Reports
• Relation between Datasets to Dashboards
• Page to Report – Mapping Options
• Publish Options and Data Import Options
• Need for PINS @ Visuals and PINS @ Reports
• Need for Data Streams and Cloud Integration

Module 11 : POWER BI CLOUD OPERATIONS

• Report Publish Options and Verifications
• Working with Power BI Cloud Interface & Options
• Navigation Paths with “My Workspace” Screens
• FILE, VIEW, EDIT REPORTS, ACCESS, DRILLDOWN
• Saving Reports into pdf, pptx, etc. Report Embed
• Report Rendering and EDIT, SAVE, Print Options
• Report PIN and individual Visual PIN Options
• Create and Use Dashboards. Menu Options
• Goto Dashboard and Goto LIVE Page Options
• Operations on Pinned Reports and Visuals
• TITLE, MEDIA, USAGE METRICS & FAVOURITES
• SUBSCRIPTION Options and Reports with Mobile View
• Options with Report Page: Print and Subscribe
• Report Actions: USAGE METRICS, ANALYSE IN EXCEL
• Report Actions: RELATED ITEMS, RENAME, DELETE
• Dashboard Actions: METRICS, RELATED ITEMS
• Dashboard Actions: SETTINGS FOR Q & A, DELETE
• PIN Actions: METRICS, SHARE, RELATED ITEMS
• PIN Actions: SETTINGS FOR Q & A, DELETE
• EDIT DASHBOARD (CLOUD), On-The-Fly Reports
• Dataset Actions: CREATE REPORT, REFRESH
• SCHEDULED REFRESH & RELATED ITEMS
• Dashboard Integration with Apps in Power BI

Module 12 : IMPROVING POWER BI REPORTS

• Publish PowerBI Report Templates
• Import and Export Options with Power BI
• Dataset Navigations and Report Navigations
• Quick Navigation Options with “My Workspace”
• Dashboards, Workbooks, Reports, Datasets
• Working with MY WORK SPACE group
• Installing the Power BI Personal Gateway
• Automatic Refresh – Possible Issues
• Adding images to the dashboards
• Reading & Editing Power BI Views
• Power BI Templates (pbit)- Creation, Usage
• Managing report in Power BI Services
• PowerBI Gateway – Download and Installation
• Personal and Enterprise Gateway Features
• PowerBI Settings : Dataset – Gateway Integration
• Configuring Dataset for Manual Refresh of Data
• Configuring Automatic Refresh and Schedules
• Workbooks and Alerts with Power BI
• Dataset Actions and Refresh Settings with Gateway
• Using natural Language Q&A to data – Cortana

Module 13 : INSIGHTS AND SUBSCRIPTIONS

• Data Navigation Paths and Data Splits
• Getting data from existing systems
• Data Refresh and LIVE Connections
• pbit and pbix : differences. Usage Options
• Quick Insights For Power BI Reports
• Quick Insights For PowerBI Dashboards
• Generating Insights with Cloud Datasets
• Generating Reports with Cloud Datasets
• Using relational databases on-premises
• Using relational databases in the cloud
• Consuming a service content pack
• Creating a custom data set from a service
• Creating a content pack for your organization
• Consuming an organizational content pack
• Updating an organizational content pack
• Adding Tiles : Images, Videos, DataStream
• Creating New Reports from Cortana, Advantages Class

INTRODUCTION TO MACHINE LEARNING

• The origins of machine learning
• Uses and abuses of machine learning
• Machine learning successes
• The limits of machine learning
• Machine learning ethics
• How machines learn
• Data storage
• Abstraction
• Generalization
• Evaluation
• Machine learning in practice
• Types of input data
• Types of machine learning algorithms
• Matching input data to algorithms

Regression Algorithms

The most popular regression algorithms are:

• Ordinary Least Squares Regression (OLSR)
• Linear Regression
• Logistic Regression
• Stepwise Regression

Instance-based Algorithms

The most popular instance-based algorithms are:

• k-Nearest Neighbor (kNN)
• Learning Vector Quantization (LVQ)
• Self-Organizing Map (SOM)
• Locally Weighted Learning (LWL)
• Support Vector Machines (SVM)

Regularization Algorithms

The most popular regularization algorithms are:

• Ridge Regression
• Least Absolute Shrinkage and Selection Operator (LASSO)
• Elastic Net

Decision Tree Algorithms

The most popular decision tree algorithms are:

• Classification and Regression Tree (CART)
• Iterative Dichotomiser 3 (ID3)
• 5 and C5.0 (different versions of a powerful approach)
• Chi-squared Automatic Interaction Detection (CHAID)
• Decision Stump
• M5
• Conditional Decision Trees

Bayesian Algorithms

The most popular Bayesian algorithms are:

• Naive Bayes
• Gaussian Naive Bayes
• Multinomial Naive Bayes
• Averaged One-Dependence Estimators (AODE)
• Bayesian Belief Network (BBN)
• Bayesian Network (BN)

Clustering Algorithms

The most popular clustering algorithms are:

• k-Means
• k-Medians
• Expectation Maximisation (EM)
• Hierarchical Clustering

Divide and Conquer – Classification Using Decision Trees and Rules

• Understanding decision trees
• Divide and conquer
• The decision tree algorithm
• Choosing the best split
• Pruning the decision tree
• Example – identifying risky bank loans using 0 decision trees

2. Forecasting Numeric Data – Regression Methods
   • Understanding regression
   • Simple linear regression
   • Ordinary least squares estimation
   • Correlations
   • Multiple linear regression
   • Example – predicting medical expenses using linear regression

3. Black Box Methods – Neural Networks and Support Vector Machines
   • Understanding neural networks
   • From biological to artificial neurons
   • Activation functions
   • Network topology
   • The number of layers
   • The direction of information travel
   • The number of nodes in each layer
   • Training neural networks with backpropagation
   • Example – Modeling the strength of concrete with ANNs

4. Finding Groups of Data – Clustering with k-means
   • Understanding clustering
   • Clustering as a machine learning task
   • The k-means clustering algorithm
   • Using distance to assign and update clusters
   • Choosing the appropriate number of clusters
   • Example – finding teen market segments using k-means clustering

5. Finding Patterns – Market Basket Analysis Using Association Rules
   • Understanding association rules
   • The Apriori algorithm for association rule learning
   • Measuring rule interest – support and confidence
   • Building a set of rules with the Apriori principle
   • Example – identifying frequently purchased groceries with
   • Association rules

 7-8 months