Complete Practical, Real-time Job Oriented Training

Data Analyst Training

What is Data Analysis?
Data Analysis is an art to understand the data, identify hidden trends of data. This involves Data
Presentation, Data Classification and Detailed understanding of data.

Who can join this course?

Anyone. We start the classes from scratch, right from Basics of Data, Database, Analysis.
Then proceed for Scenario based activities and job orientation with Realtime Project.

How to become a Data Analyst?

Step 1: Learn SQL Server TSQL (MSSQL) Queries. 400+ Queries with concept wise FAQs
Step 2: Learn Power BI. Concept wise FAQs, Assignments, Case Studies
Step 3: Learn Python. Optional, Highly Recommended.
Step 4: Involve in a Real-time Project for your resume with Complete Solution, FAQs

What are the Certifications from Microsoft for Data Analyst ?

Our course includes Data Analyst Certification Exam Guidance (PL 300)
Ref: https://learn.microsoft.com/en-us/credentials/certifications/data-analyst-associate/

What about the Lab? What are the System Requirements?

Window OS; 6 GB RAM; Any Processor. We shall guide you for Software Installation, Practice.

How to join the course?

For latest schedules, Free (LIVE) Demo or Video Demo : pls call us or visit: www.sqlschool.com

www.sqlschool.com For Free Demo: Reach us on +91 966644 0801 or +91 995144 0801
 Module 1: MSSQL & TSQL - Curriculum Overview

Module 1: MSSQL & TSQL - Detailed Curriculum

Chapter 1: SQL SERVER INTRODUCTION

Database Introduction; Need for Databases; Database Types : OLTP, DWH, OLAP; DBMS & RDBMS; SQL:
Purpose and Real-time Use; BI (Business Intelligence) Work Flow : Basic Level; Microsoft SQL Server
Advantages; Versions & Editions; SQL Versus MSSQL; Job Roles and Career Options;

Chapter 2: SQL SERVER INSTALLATIONS

SQL Server: Advantages; Features & Components of SQL Server; System Configuration Checker Tool; SQL
Server 2022, 2019 Installation; SSMS Tool Installation; Instance Concept, Default & Named Instances;
Authentication Modes: Windows & SQL; Logins; File Stream & Collation; Connection Tests;

Chapter 3: SSMS TOOL, SQL BASICS - 1

SQL Server Management Studio (SSMS) Tool; System Databases; Creating Databases : Files [MDF, LDF];
Creating Tables in GUI; Data Insertion, Storage; SQL : DDL, DML, SELECT, DCL & TCL Statements; SELECT;

Chapter 4: SQL BASICS - 2

Databases & Tables using SQL; Data Insertions; SELECT; WHERE Conditions; Operators: OR, IN, NOT IN,
BETWEEN, NOT BETWEEN, LIKE, NOT LIKE; UPDATE, DELETE & TRUNCATE; ADD, ALTER & DROP; Aliases;

Chapter 5: DATA IMPORTS, SCHEMAS

Data Imports with Excel; Bulk Operations; Basic Sub Queries; TOP, OFFSET, ORDER BY; Database Objects :
Tables & Schemas; Using Schemas with Tables; Table Migrations; Schemas For Security Management;

Chapter 6: CONSTRAINTS & INDEXES BASICS

Constraints and Keys; NULL, NOT NULL; UNIQUE KEY; PRIMARY KEY; FOREIGN KEY, References; CHECK &
DEFAULT; Identity; DB Diagrams; Indexes Basics: Clustered, Non Clustered; Basic Query Tuning Concepts;

Chapter 7: JOINS BASICS & GROUP BY QUERIES

www.sqlschool.com For Free Demo: Reach us on +91 966644 0801 or +91 995144 0801
JOINS - TSQL Queries; INNER JOINS; OUTER JOINS; Left & Right Outer Joins; FULL Outer Joins; CROSS JOIN
and CROSS APPLY; GROUP BY with Joins, Aggregations; Having Clause; Query Tuning with Indexes;

Chapter 8: VIEWS & FUNCTIONS

Views: Types, Usage in Real-time; Creating, Executing & Verifying Views; Views for Data Analytics; views for
Row Level Security (RLS); System Views & Audits; Functions: Creation, Usage in Real-time; Using table Data
Type with Functions; Functions for Dynamic, Dynamic Joins; Date Time Functions; String Functions;

Chapter 9: STORED PROCEDURES

Procedures: Usage in Real-time; Creating & Executing Stored Procs; Using Parameters in SQL Server; SPs for
SELECT, DML; Data validations; sp_helpdb; sp_help, sp_helptext, sp_helpindex; sp_rename, sp_recompile;

Chapter 10: TRIGGERS, TRANSACTIONS

Triggers – Real-world Purpose; FOR / AFTER & INSTEAD OF; Inserted, Deleted Memory Tables; Enable /
Disable; Transactions : ACID; Auto, Explicit, Implicit; Commit & Rollback; Query Blocking & Lock Hints;

Chapter 11: DB ARCHITECTURE, CTEs

Database Architecture: Data & Log Files; Primary Data File (mdf); Secondary Data Files (ndf); Filegroups:
Data Mapping; File Size. Log Files (ldf); CTE: Common Table Expressions; Real-time Scenarios with CTEs;

Chapter 12: DATA TYPES & CURSORS

Integer & Character Data Types; Decimal Data Types; SQL_Variant; Bit & Binary; Varchar(max), Array
Handling in SQL; Cursors : Declaration & Real-time Use. Fetch, Absolute and Scroll / Dynamic Cursors.

Chapter 13: TEMP TABLES & SYNONYMS

Temporary Tables : Real-time Use; Local and Global Temporary Tables; Real-time Use; SELECT INTO; Batch;
Go; Multi Row Inserts; 2 Part, 3 Part Naming; Synonyms : Creation and Real-time Use; 2,3, 4 Part Naming;

Chapter 14: MERGE, RANK & WINDOW FUNCTIONS

Merge and UPSERT Statement in TSQL; MATCHED, NON MATCHED data with DML; IIF(), CASE Statement;
Row Number, Rank, Dense Rank, Partition By. Identify and Removing Duplicates; Using Views with DML;

Chapter 15: CUBE, ROLLUP & GROUPING & EXCEL INTEGRATION

Group By with Joins; Using Group By with ROLLUP & CUBE; HAVING and GROUPING Queries; UNION ALL; CAST &
CONVERT; Date Formatting & Aggregate Functions; TSQL Queries with Excel Analytics; Excel ODC Connections;

THREE Case Studies

www.sqlschool.com For Free Demo: Reach us on +91 966644 0801 or +91 995144 0801
 Module 2: Power BI - Curriculum Overview
Module 1 [Visualizations] Module 2 [Power Query, DAX] Module 3 [Cloud Service,
Chapters 1 to 6 Chapters 7 to 12 Server]
Chapters 13 to 18
Power BI Report Design Power Query For Data Modelling [M Lang] Power BI Cloud Service, App
Publish
Visual Interactions, Visual Sync Power Query For ETL Operations & Models Power BI Gateways, Data Flows
Grouping, Binning, Hierarchies Power Query For Data Cleansing, Shaping Alerts, Subscriptions, Workbooks
Drill-down & Drill-thru Actions Power Query For Dynamic Data Extractions Dashboards, REST API, Excel Online
Buttons, Bookmarks, Filters Power Query For Dynamic Data Sources Excel Analyzer and Excel Publisher
Visual Properties, Analytics DAX For Calculated Columns & Measures Power BI App Publish and Security
Basic Data Modelling Options DAX For Dynamic Data Reporting, Big Data Report Server and Web Portal URL
Interactions and Navigations DAX for Data Formatting and Joins Power BI Admin, Mobile Reports
Custom Visualizations DAX For Data Highlighting, End User Analysis Power BI Report Security Options
Cloud Data Sets & Data Flows DAX For Row Level Security, Azure Sources Power BI Tuning and Management
50 Concepts & Scenarios 50 Examples @ Power Query 50 Work Items @ Hosting, Data,
50 Examples @ DAX Refresh, Power BI Administration,
Dashboards, Apps & Scorecards
End to End Real-time Project with complete Solution, Project Work, Project FAQs & Resume Support

Module 2: Power BI – Detailed Curriculum

Chapter 1 : POWER BI INTRO, JOB ROLES

Need for Power BI; End to End BI Process; Report Types & Power BI Eco System (Lab Plan); Job Roles: Data
Analyst, BI Developer, Data Engineer, Data Scientist Job Roles & Certifications; PL 300 Exam Guidance;

Chapter 2 : POWER BI BASIC REPORT DESIGN

Understanding Power BI Complete Eco System; Power BI Desktop Tool; Visual Types; Canvas, Visualizations
and Fields; In-Memory Database; Format, Labels, Legend, Data Points, Spotlight & Focus Mode; PBIX, PIBT;
Chapter 3 : VISUAL INTERACTION, GROUPING
Visual Interaction; Disabling Visual Interactions; Spotlight; CSV and PDF Exports; Tooltips; Grouping : Real-
time Use; List Grouping and Binning; Grouping Static / Fixed Data; Binning; Bin Type and Bin Count Usage;
Chapter 4 : HIERARCHIES, VISUAL SYNC
Hierarchies : Real-time Use; Independent, Dependant & Conditional Drilldowns, Drill Up; Dynamic Drills;
Visual Sync : Single & Multi Select; Number, Text, Date Slicers; Static and Dynamic Dropdown Reports;
Chapter 5 : FILTERS, BOOKMARKS
Filters : Visual, Page, Report Filters; Basic, Advanced and Top N; Drill Thru Filters, CrossReport Filters,
Include, Exclude; Page Navigation; Bookmarks : Buttons, Images & Actions, Text URLs; Page Navigations;

www.sqlschool.com For Free Demo: Reach us on +91 966644 0801 or +91 995144 0801
Chapter 6 : Big Data Modelling & Visualizations
OLTP Databases, Big Data Access in Power BI; Import &Direct Query; Enter Data; Basic Data Modelling :
Currency, Relations, Date Formatting; Visualizations: Charts, Lines, Maps, Q & A; Scatter Chart; Water Fall;
Infographics; Axis & Labels; Cell & Conditional Formatting;

Chapter 7 : POWER QUERY LEVEL 1

Power Query and M Language; Data Types, Transformation Types; Table & Column; Text & Number; Date,
Time; let, source, in; Edit; Combine : Merge & Append; Basic ETL Expressions; Advanced Editor & Edits;

Chapter 8 : POWER QUERY LEVEL 2

Duplicate, Reference; Group By & Advanced Options; Aggregations; Transpose, Header Row Promotion;
Reverse; Data Type Changes; Replace Columns: Text, NonText; Replace Nulls: Fill Up, Fill Down; Pivot,
UnPivot; Split Column; Date & Time; Custom Formatting; Step Edits; Step Rollbacks, Step ReOrder;

Chapter 9 : POWER QUERY LEVEL 3

Using Parameters; Static/Dynamic Lists; Convert Tables to Lists; Linking Parameters in Canvas; Multi-Valued
Parameters; Advanced Edits; Columns From Examples; Conditional Columns; Table to List; List Queries;

Chapter 10 : POWER BI CLOUD - 1

Power BI Service Architecture; Cloud Components: Workspaces, Reports, Semantic Models, Dashboards,
Apps; Report Publish; Lineage; Report Edits, Report Creation; Report Reuse; Favourites and Cloud Access;

Chapter 11 : POWER BI CLOUD – 2

Dashboard Creation; Pins & Tiles; Image / Video Tiles; Report Actions : Share, Secure, QR Code, Report
Permissions; Report Downloads, Subscribe, Teams; Dashboard Actions: Share, Secure; Q & A [Cortana],
Dashboard Themes; Favourite, Insights, Embed; Metrics, Insights & Subscription Options in Cloud;

Chapter 12 : POWER BI CLOUD – 3

Gateways Configuration, PBI Service; Gateway Types; Gateway Clusters, Data Refresh : Manual, Automatic;
PBIEngw Service; DataFlows, Power Query Expressions; Adding Entities, JSON Files; Power BI Apps in Cloud;
Sections & Alerts in Power BI Apps; Power BI Apps : Updates, Security;

Chapter 13 : POWER BI REPORT SERVER, PAGINATED REPORTS

Report Server : Install, Configure; Report Server & Tempdb; URLs usage; Hybrid Cloud; Power BI Desktop RS
& Report Builder Tools: Interactive Reports to Cloud & Report Server; Paginated Reports to Report Server;

Chapter 14 : DAX Functions - Level 1

DAX: Fundamentals, Data Types & Operators; Expression Syntax; DAX Measures, DAX Columns in Real Time.
Creating, Testing DAX Entities; Entities and Fields; IF, ISBLANK, SUM, AVG, COUNT Functions in DAX;

Chapter 15 : DAX FUNCTIONS - Level 2

Data Modelling : Dimension & Fact Tables; Identify Measures, Attributes; STAR, SNOWFLAKE; Missing
Relation; 1:1, 1:M, M:1 Relations; Quick Measures; Aggregations; Data Analytics; Filtering & Slicing Data;
Star Rating Calculations; Running Totals; Joins with DAX Expressions; Time Intelligence Functions in DAX;

www.sqlschool.com For Free Demo: Reach us on +91 966644 0801 or +91 995144 0801
Chapter 16 : DAX FUNCTIONS - Level 3
Variables & Functions with DAX; DAX Expressions & Joins; CALCULATE, RELATED, COUNTROWS; RETURN,
EARLIER, AVERAGEX and AVERAGE, KEEPFILTERS, SELECTEDVALUE, FORMAT, TEXT; String Functions in DAX;

Chapter 17 : DAX FUNCTIONS - Level 4

DAX Time Intelligence Functions: DATEDADD; PREVIOUSMONTH; TOTALYTD, MTD, TOTALQTD; RLS : Row
Level Security; PARALLELPERIOD; Analytical Reports with Excel (Cloud) & DAX;

Chapter 18: Real-time Project [Sales & Retail] + Resume Guidance

Module 3: Python Detailed Curriculum

Chapter 1 : Python Introduction

What is Python Programming? Characteristics of Python; History of Python; Python Versions; Python in Real-
time Use; Job Roles; Python Installation and Implementation Options; Install Python with Diff IDEs ; Features
of Python; Limitations of Python;

Chapter 2 : Python Implementation Modes

Python Basics; Python Scripting Concepts; Interactive & Script Mode ; Python File Extensions - Script Mode;
SETTING PATH IN Windows - Clear Screen ; Learn Python Main Function ; Quit the Python Shell - Simple
Calculator ; Order of operations - Multiline Statements; Python Path Testing ; Python Packages;

Chapter 3 : Python IDEs

PyCharm IDE - Components & Debugging; Anaconda - Coding Environments - Spyder Components ; General
Spyder Features - Spyder Shortcut Keys ; Jupyter Notebook - What is Conda? ; Conda List - Jupyter and
Kernals Environment ; Python PIP - Mutable Versus Immutable Objects ;

Chapter 4: Python Variables

What is Variable? - Variables and Constants; Variable names - Mnemonic Variable Names; Values and Types -
What Does "Type" Mean? ; Multiple Assignment - Python Numerical types ;

Chapter 5: Python Operators and Data Types

Arithmetic Operators - Relational Operators ; Comparison Operators - Assignment Operator; Logical
Operators or Bitwise Operators ; Membership Operators; Operator precedence; Python expressions in string
; Standard Datatypes - Operands ; Swap Variables ; Types Conversion - Python Math. ; Mutable Versus
Immutable Objects ; Operator precedence - Evaluating Exp. ; Evaluating expressions inpython ;

www.sqlschool.com For Free Demo: Reach us on +91 966644 0801 or +91 995144 0801
Chapter 6 : String Handling
what is String ? - String Operations - String indices ; String Functions - len , upper, lower,join,Split ;
SwapCase(), Title(),find(), isupper(), islower(); Delete a string - Python Keywords ; String Multiplication and
concatenation ; Python Identifiers - Python Literals ; string formatting operator in python ; Built-in String
Methods - Data Structures; Indentation; Data Structure in Python Language; Reverse words;

Chapter 7 : Python Conditions

Control Structures - Sequential Control Structure ; Selective and Repetitive Control Structure ; How to use
“if condition” in conditional ; control Structures in python ; if statement (One-Way Decisions) ; if .. else
statement (Two-way Decisions) ; How to use “else condition” ; if .. elif .. else statement (Multi-way) ; When
“else condition” does not work ; How to use “elif” condition ; How to execute conditional statement with
; minimal code - Nested IF Statement ; Nested IF Statement in python ;

Chapter: 8 Python Loops & Sequences

How to use While loop and For loop ; Break and Continue Statements in For loop ; Python Enumerate
function for For Loop ; Strings - Unicode Strings ; Lists - Tuples - Sets - Dictionary - Xrange ; Accessing the List ;
Updating a List - Deleting a List ; List indices - Traversal; List Operations - List Slices - List Methods ; Map,
filter and reduce; Python Lists; List Function & Operations;

Chapter 9 : Python Tuple & Python Sets

Advantages of Tuple over List ; Packing and Unpacking - Tuples ; Creating Nested tuple - Examples ; Deleting
Tuples - Slicing of Tuples ; Tuple Membership Test ; Built in Functions, Dotted Charts; Create/declare a set
in python ; Python Methods ; Set Operations; Built-in Functions; python frozenset;

Chapter 10 : Python Dictionary

How to create a dictionary? ; PYTHON HASHING - Dictinary Methods ; Copying dictionary - Updating
Dictionary ; Delete Keys; Sorting Dictionary - Dictionary len() ; Python Dictionary in-built Functions ; Variable
Types - python List Cmp() ; Python List cmp() Method ; Python Dictionary Str(dict) ;

Chapter 11 : Python Functions

Python Function; Function Calls; Function Return Value; Types of Arguments; Default Arguments - Non
Default Arg; Keyword Arguments; Arbitrary Arguments; Various Forms of Function Arguments; Scope &
Lifetime of variables - Nested Fun; Call By Value, Call by Reference in Python; Anonymous
Functions/Lambda functions ;

Chapter 12 : Python Modules

Python Module? The import Statement - The from… import st; import * Statement ; The Dir() Function in
python ; User defined Modules ; Command line Arguments; Getting Python Module Search Path; What are
modules and packages in Python? ; Python import statement? ; import <module_name> string python
; from <module_name> import <name(s)> ; from <module> import <name> as <name> ;

www.sqlschool.com For Free Demo: Reach us on +91 966644 0801 or +91 995144 0801
Chapter 13 : Packages in Python
What is a Package in Python? ; Introduction to Packages? ; py file - Creating a package ; Importing module
from a package ; Creating Sub Package in Python ; Importing from Sub- Packages ; Most Popular Python
Packages ; Libraries in Python? ; What is the difference between NumPy & SciPy? ; SciPy Concept; NumPy
used? ; Python what is Seaborn? - Examples ; Is NumPy a Python framework? ;

Chapter 14 : Python Date and Time

How to Use Date & DateTime Class ; How to Format Time Output ; How to use Timedelta Objects ; Calendar
in Python ; datetime classes in Python ; How to Format Time Output? ; Python Calendar Module,Time
Module ; Python Text Calendar ; Python HTML Calendar Class; Unix Date and Time Commands ; Python
strftime(); How strftime() works? ;

Chapter 15: Python OS Module & Python Exception Handling

Shell Script Commands in OS Modules ; Various OS operations in Python ; Python File System Shell Methods
; Different Python Modules ; os - math - cmd -csv - random ; Numpy ( numerical python ) ; Pandas - sys -
Matplotlib - Datetime ; Common RunTime Errors; Abnormal termination, Python Custom Exception;
Exception, Assertions ; Exception Handling, Try…Except…else,Try…finally ;

Chapter 16: Python-Data Base Communication

What is Big Data? Types of data? ; Oracle - SQLSERVER - MYSQL - DB2 ; Postgre SQL - DataBase Sample ;
Executing the Queries - Bind Variables ; Executing DML Operations..!! ; Connecting to the Database ; Create
a connection object. ; Create a cursor object to read/write. ;

Chapter 17: Data Analytics

Introduction to data Big Data? ;Introduction to NumPY and SciPY ; Introduction to Pandas and MatPlotLib ;
Data Science ; What is Data Science in Python ; Data Science Life Cycle in python ; Data Analysis using
Python ; what is Data Mining in Python ; Analytics vs Data Science in Python ;

Chapter 18: Python Class and Objects

Define Classes - Built in Class Attributes ; Creating Objects - Constructors ; Class variables and Instance
Variables ; Object & Classes ; Access Modifiers - Self Varible; Python classes ; Python Namespace - Garbage
Collections ; Single Inheritance - Hierarchical Inheritance ; Multilevel, Multiple, Hybrid Inheritance ;
Overloading and Over Riding ; Polymorphism - Abstraction – Encapsulation ;

Chapter 19: Python Regular Expressions

Regular Expression? ; Regular Expression Patterns ; Literal characters - Repetition Cases ; Groups and
Grouping; Example of w+ and ^ Expression ; Example of \s expression in re.split function ; Using regular
expression methods ; re.match() in Regular Exp. ; Text Pattern (re.search()) ; Using re.findall for Text ;

Chapter 20: Multi-Threading

Multi-Threading ; Threading Module in python; Thread Synchronization ; Multiprocessing Vs
Multithreading ; Python Gil? ; Thread Control Block (TCB): ; Thread Identifier in MultiThreading ; Stack

www.sqlschool.com For Free Demo: Reach us on +91 966644 0801 or +91 995144 0801
pointerin multi-Threading ; Program counter, Thread State ;

Chapter 21: Python -Tkinter

Tkinter GUI Program ; Components and Events; Adding Controls in Tkinter form ; Entry Widget, Text Widget,
Radio Button, ; Check Button in Tkinter forms ; List Boxes, Menus, ComboBox, Canvas, Entry ; What is Tk () in
Python? ; What is Mainloop ()? ; What is the best GUI for Python ? Messagebox,simpledialog ; messagebox
tkinter methods ; showinfo(),showerror(), showwarning() ;

Chapter 22: Introduction to Python Web & IoT

Python Web Frameworks ; Django ; Advantages of Django Web Framework ; MVC and MVT - Django ; Web
Pages using python ; HTML5,CSS3; PYTHON Bottle & pyramid - Falcon ; smart_open in python;

Resume, Mock Interview + Project FAQs and Solutions

Reach Us Now, for Free demo ! contact@sqlschool.com

Reach Us : +91 995144 0801, +91 966644 0801 Next Schedules: www.sqlschool.com/Register

www.sqlschool.com For Free Demo: Reach us on +91 966644 0801 or +91 995144 0801