Data Analytics Training

1. Excel & Advance Excel

2. SQL

3. Power BI

4. Python

Add-on course with this Package

Live Online Classes

✓ Tableau
✓ Project
✓ Aptitude
✓ Spoken English

Average Salaries: ₹ 5L - ₹ 8L Per Annum

Hiring Companies: TCS, Accenture, Amazon , CISCO, Capgemini Etc

Course Fees: 30000+ Tax = ₹ 35400

Once - Learn Until Jo

ay b
P
Excel and Advanced Excel

Module 1: Basic Excel (Duration: 10Hrs )

• Text to Columns
• Concatenate
• The Concatenate Function
• The Right Function with Concatenation
• Absolute Cell References
• Data Validation
• Time and Date Calculations
• Conditional Formatting
• Exploring Styles and Clearing Formatting
• Using Conditional Formatting to Hide Cells

• Using the IF Function

• Changing the “Value if false” Condition to Text
• Pivot Tables
• Creating a Pivot Table
• Specifying PivotTable Data
• Changing a PivotTables Calculation
• Filtering and Sorting a PivotTable
• Creating a PivotChart
• Grouping Items
• Updating a PivotTable
• Formatting a PivotTable
• Using Slicers
• Charts
• Creating a Simple Chart
• Charting Non-Adjacent Cells
• Creating a Chart Using the Chart Wizard
• Modifying Charts
• Moving an Embedded Chart
• Sizing an Embedded Chart
• Changing the Chart Type
• Chart Types
• Changing the Way Data is Displayed
• Moving the Legend

• Formatting Charts
• Adding Chart Items
• Formatting All Text
• Formatting and Aligning Numbers
• Formatting the Plot Area

• Formatting Data Markers

• Pie Charts
• Creating a Pie Chart
• Moving the Pie Chart to its Own Sheet
• Adding Data Labels
• Exploding a Slice of a Pie Chart
• Data Analysis − Overview
• types of Data Analysis
• Data Analysis Process
• Working with Range Names
• Copying Name using Formula Autocomplete
• Range Name Syntax Rules
• Creating Range Names
• Creating Names for Constants
 • Managing Names
• Scope of a Name
• Editing Names
• Applying Names
• Using Names in a Formula
• Viewing Names in a Workbook
• Copying Formulas with Names
• Difference between Tables and Ranges
• Create Table
• Table Name

• Managing Names in a Table

• Table Headers replacing Column Letters
• Propagation of a Formula in a Table
• Resize Table
• Remove Duplicates

• Convert to Range
• Table Style Options
• Table Styles
• Cleaning Data with Text Functions
• Removing Unwanted Characters from Text
• Extracting Data Values from Text
• Formatting Data with Text Functions

Advance Excel (Duration: 15Hrs)

Module 2: Date Formats (Duration: 2Hrs)

• Converting Dates in Serial Format to Month-Day-Year Format

• Converting Dates in Month-Day-Year Format to Serial Format
• Obtaining Today’s Date
• Finding a Workday after Specified Days
 • Customizing the Definition of a Weekend
• Number of Workdays between two given dates
• Extracting Year, Month, Day from Date
• Extracting Day of the Week from Date
• Obtaining Date from Year, Month and Day
• Calculating Years, Months and Days between two dates

Module 3: Conditional Formatting (Duration: 2Hrs)

• Highlight Cells Rules

• Top / Bottom Rules
• Data Bars
• Color Scales

• Icon Sets
• New Rule
• Clear Rules
• Manage Rules

Module 4: Sorting (Duration: 2Hrs)

• Sort by Text
• Sort by Numbers
• Sort by Dates or Times
• Sort by Cell Color
• Sort by Font Color
• Sort by Cell Icon
• Sort by a Custom List
• Sort by Rows
• Sort by more than one Column or Row

Module 5: Filtering (Duration: 2Hrs)

• Filter by Selected Values

 • Filter by Text
• Filter by Date
• Data Analysis with Excel
• Filter by Numbers
• Filter by Cell Color
• Filter by Font Color
• Filter by Cell Icon
• Clear Filter
• Advanced Filtering
• Filter Using Slicers

Module 6: Other Topics (Duration: 2Hrs)

• Subtotals with Ranges

• Subtotals
• Nested Subtotals
• Quick Analysis
• Quick Analysis with TOTALS
• Sum
• Average
• Count
• %Total
• Running Total
• Sum of Columns

Module 7: Lookup Functions (Duration: 3Hrs)

• Using the VLOOKUP Function

• Using VLOOKUP Function with range lookup TRUE
• Using VLOOKUP Function with range lookup FALSE
• Using the HLOOKUP Function
• Using HLOOKUP Function with range lookup FALSE
 • Using HLOOKUP Function with range lookup TRUE
• Using the INDEX Function
• Using MATCH Function

Module 8: Pivoting (Duration: 2Hrs)

• PivotTables
• Creating PivotTable

• Recommended PivotTables
• PivotTable Fields
• PivotTable Areas
• Nesting in the PivotTable
• Filters

• Slicers
• Summarizing Values by other Calculations
• PivotTable Tools
• Using Pictures in Column Charts
• Band Chart
• Thermometer Chart
• Gantt Chart
• Waterfall Chart
• Sparklines
• Pivot Charts
• PivotChart from PivotTable
• PivotChart without a PivotTable
• Working with Multiple Sheets
• Multiple Worksheets with same Structure
• Creating a Formula across Multiple Worksheets
• Summarizing Data in Multiple Worksheets
• What-If Analysis
 SQL Training

Introduction to Oracle Database

• List the features of Oracle Database 11g
• Discuss the basic design, theoretical, and physical aspects of a relational database
• Categorize the different types of SQL statements
• Describe the data set used by the course
• Log on to the database using SQL Developer environment
• Save queries to files and use script files in SQL Developer

Retrieve Data using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements
• Generate a report of data from the output of a basic SELECT statement
• Select All Columns
• Select Specific Columns
• Use Column Heading Defaults
• Use Arithmetic Operators
• Understand Operator Precedence
• Learn the DESCRIBE command to display the table structure

Learn to Restrict and Sort Data

• Write queries that contain a WHERE clause to limit the output retrieved
• List the comparison operators and logical operators that are used in a WHERE
clause
• Describe the rules of precedence for comparison and logical operators
• Use character string literals in the WHERE clause
• Write queries that contain an ORDER BY clause to sort the output of a SELECT
statement
• Sort output in descending and ascending order

Usage of Single-Row Functions to Customize Output

• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC, and MOD functions
• Perform arithmetic with date data
 • Manipulate dates with the DATE functions

Invoke Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic in a SELECT statement

Aggregate Data Using the Group Functions

• Use the aggregation functions in SELECT statements to produce meaningful reports
• Divide the data in groups by using the GROUP BY clause
• Exclude groups of date by using the HAVING clause

Display Data From Multiple Tables Using Joins

• Write SELECT statements to access data from more than one table
• View data that generally does not meet a join condition by using outer joins
• Join a table by using a self join

Use Sub-queries to Solve Queries

• Describe the types of problem that sub-queries can solve
• Define sub-queries
• List the types of sub-queries
• Write single-row and multiple-row sub-queries

The SET Operators

• Describe the SET operators
• Use a SET operator to combine multiple queries into a single query
• Control the order of rows returned

Data Manipulation Statements

• Describe each DML statement
• Insert rows into a table
• Change rows in a table by the UPDATE statement
• Delete rows from a table with the DELETE statement
• Save and discard changes with the COMMIT and ROLLBACK statements
• Explain read consistency
Use of DDL Statements to Create and Manage Tables
• Categorize the main database objects
• Review the table structure
• List the data types available for columns
• Create a simple table
• Decipher how constraints can be created at table creation
• Describe how schema objects work

Other Schema Objects

• Create a simple and complex view
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

Control User Access

• Differentiate system privileges from object privileges
• Create Users
• Grant System Privileges
• Create and Grant Privileges to a Role
• Change Your Password
• Grant Object Privileges
• How to pass on privileges?
• Revoke Object Privileges

Management of Schema Objects

• Add, Modify and Drop a Column
• Add, Drop and Defer a Constraint
• How to enable and disable a Constraint?
• Create and Remove Indexes
• Create a Function-Based Index
• Perform Flashback Operations
• Create an External Table by Using ORACLE_LOADER and by Using
ORACLE_DATAPUMP
• Query External Tables
Manage Objects with Data Dictionary Views
• Explain the data dictionary
• Use the Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• Table and Column Information
• Query the dictionary views for constraint information
• Query the dictionary views for view, sequence, index and synonym information
• Add a comment to a table
• Query the dictionary views for comment information

Manipulate Large Data Sets

• Use Subqueries to Manipulate Data
• Retrieve Data Using a Subquery as Source
• Insert Using a Subquery as a Target
• Usage of the WITH CHECK OPTION Keyword on DML Statements
• List the types of Multitable INSERT Statements
• Use Multitable INSERT Statements
• Merge rows in a table
• Track Changes in Data over a period of time

Data Management in Different Time Zones

• Time Zones
• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• Compare Date and Time in a Session’s Time Zone
• DBTIMEZONE and SESSIONTIMEZONE
• Difference between DATE and TIMESTAMP
• INTERVAL Data Types
• Use EXTRACT, TZ_OFFSET and FROM_TZ
• Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Sub-queries

• Multiple-Column Subqueries
• Pairwise and Nonpairwise Comparison
• Scalar Subquery Expressions
• Solve problems with Correlated Subqueries
 • Update and Delete Rows Using Correlated Subqueries
• The EXISTS and NOT EXISTS operators
• Invoke the WITH clause
• The Recursive WITH clause

Regular Expression Support

• Use the Regular Expressions Functions and Conditions in SQL
• Use Meta Characters with Regular Expressions
• Perform a Basic Search using the REGEXP_LIKE function
• Find patterns using the REGEXP_INSTR function
• Extract Substrings using the REGEXP_SUBSTR function
• Replace Patterns Using the REGEXP_REPLACE function
• Usage of Sub-Expressions with Regular Expression Support
• Implement the REGEXP_COUNT function

Power BI

Introduction to Power BI
Learning Objectives: In this module, you will be introduced to what Power BI is, why to
choose power BI, its building blocks, other various fundamental concepts of Power BI
Topics Covered:
• What is Power BI?
• Why Power BI?
• Benefits of Power BI
• Building Blocks of Power BI
• Fundamental Concepts of Power BI
Power BI Desktop
Learning Objectives:
In this module you will be introduced to Power BI Desktop, software installation and
overview of software
Topics Covered:
• Software Installation Procedures and Guidelines
• Overview of Software
• Power BI Sign UP
 • Introduction to Tools and Terminologies
• Refreshing Power BI Service Data
• What is a Dashboard?
• What is a Report?
• How to share Dashboard and Report?
Data Source Connection and Data Transforming
Learning Objective: In this module you will be introduced to how to connect to a data
source, perform cleaning and transformation of data
Topics Covered:
• Connecting to a Data Source
• Upload a local CSV File
• Connect to Excel Data
• What is a Query Editor?
• Import Data vs Direct Query
• Data Cleaning and Data Transformation
• Merging and Appending
Hands-on:
Datasets will be provided for practicing how to load and perform cleaning and data
transformation.
Modelling using Power BI
Learning Objective: In this module you will be introduced to data modelling, how to
manage relationships, creating calculated columns, measures and quick measures
Topics Covered:
• Modelling of Data
• Manage Data Relationship
• Cardinality, Cross Filtering
• Default Summarization and Sort By
• Creating Calculated Columns
• Creating Quick Measures and Measures
Hands-on:
In this you will be given hands-on to work on datasets, create calculated columns,
manage relationships etc.,
Learning Objective:
In this module you will be introduced to DAX
Topics Covered:
• What is DAX?
• Why is DAX Important?
• DAX Syntax
• Data Types in DAX
• Functions in DAX
• Measures in DAX
• Operators in DAX
• Tables and Filtering in DAX
• Queries in DAX
• Parameter Naming in DAX
Hands-on:
In this module hands-on will be given on various DAX functions
Data Visualization
Learning Objective: In this module you will be introduced to what is data visualization,
importance of data visualization, creating charts using Power BI
Topics Covered:
• Creating Visualizations
• Colour Formatting
• Sort Order
• Scatter plot and Bubble Charts
• Tooltips
• Slicers
• Cross Filtering, Highlighting
• Report Level Filters
• Drill Down and Drill Up
• Hierarchies
 • Conditional Formatting, Tables, and Matrices
• KPI’s
• Cards and Gauges
• Map Visualizations
• Custom Visuals
• Grouping and Binning
• Selection Pane, Bookmarks & Buttons
• Z-Order
Hands-on:
In this module hands-on will be given on visualization, colour formatting, slicers, filters,
grouping, selection pane etc.,
Reports and Dashboards
Learning Objective: In this module you will be introduced to modify reports and
dashboards, ask questions to Power BI Q&A
Topics Covered:
• Modify and Print a Report
• Rename and delete report pages
• Add a filter to a page or report
• Set visualization interactions
• Print a report page
• Send a report to PowerPoint
• Create a Dashboard
• Create and manage dashboards
• Pin a report tile to a dashboard
• Pin a live report page to a dashboard
• Pin a tile from another dashboard
• Pin an Excel element to a dashboard
• Manage pinned elements in Excel
• Add a tile to a dashboard
• Build a dashboard with Quick Insights
• Set a Featured (default) dashboard
 • Ask Questions about Your Data
• Ask a question with Power BI Q&A
• Tweak your dataset for Q&A
• Enable Cortana for Power BI
Hands-on:
In this module hands-on will be given on how to add filters to reports, set interactions,
create dashboards etc.,
Publishing and Sharing Reports
Learning Objective: In this module you will be introduced to how to publish and share
your dashboards and reports, saving options.
Topics Covered:
• Introduction to Sharing Options
• How to Publish Report From Power BI Desktop?
• How to Publish Report to Web?
• How to Share Dashboards Using Power BI Service?
• What is Content Pack?
• How to Save as PDF?
• What is Row Level Security?
• How to Export Data from Visualization?
Hands-on:
In this module hands-on will be given on how to share dashboard with Power BI service,
how to save as PDF and exporting data from visualization

Tableau

Module 1: Tableau Course Material (Duration – 5 Hours)

• Start Page
• Show Me
• Connecting to Excel Files
• Connecting to Text Files
• Connect to Microsoft SQL Server
 • Connecting to Microsoft Analysis Services
• Creating and Removing Hierarchies
• Bins
• Joining Tables
• Data Blending
Module 2: Learn Tableau Basic Reports (Duration – 5 Hours)
• Parameters
• Grouping Example 1
• Grouping Example 2
• Edit Groups
• Set
• Combined Sets
• Creating a First Report
• Data Labels
• Create Folders
• Sorting Data
• Add Totals, Sub Totals, and Grand Totals to Report
Module 3: Learn Tableau Charts (Duration – 4 Hours)
• Area Chart
• Bar Chart
• Box Plot
• Bubble Chart
• Bump Chart
• Bullet Graph
• Circle Views
• Dual Combination Chart
• Dual Lines Chart
• Funnel Chart
• Traditional Funnel Charts
• Gantt Chart
• Grouped Bar or Side by Side Bars Chart
• Heatmap
 • Highlight Table
• Histogram
• Cumulative Histogram
• Line Chart
• Lollipop Chart
• Pareto Chart
• Pie Chart
• Scatter Plot
• Stacked Bar Chart
• Text Label
• TreeMap
• Word Cloud
• Waterfall Chart
Module 4: Learn Tableau Advanced Reports (Duration – 6 Hours)
• Dual Axis Reports
• Blended Axis
• Individual Axis
• Add Reference Lines
• Reference Bands
• Reference Distributions
• Basic Maps
• Symbol Map
• Use Google Maps
• Mapbox Maps as a Background Map
• WMS Server Map as a Background Map
Module 5: Learn Tableau Calculations & Filters (Duration – 6 Hours)
• Calculated Fields
• Basic Approach to Calculate Rank
• Advanced Approach to Calculate Ra
• Calculating Running Total
• Filters Introduction
• Quick Filters
 • Filters on Dimensions
• Conditional Filters
• Top and Bottom Filters
• Filters on Measures
• Context Filters
• Slicing Filters
• Data Source Filters
• Extract Filters
Module 6: Learn Tableau Dashboards (Duration – 4 Hours)
• Create a Dashboard
• Format Dashboard Layout
• Create a Device Preview of a Dashboard
• Create Filters on Dashboard
• Dashboard Objects
• Create a Story
Module 7: Server (Duration – 5 Hours)
• Tableau online.
• Overview of Tableau Server.
• Publishing Tableau objects and scheduling/subscription.

Python

Introduction to Python:
Objective:
In this module, you will get a basic understanding of python programming, Virtual
environment, Package manager, version differences of python programming and
detailed knowledge about the python installation and environment setup for working
with python.
Topic:
• Python programming history & features
• Python compiler and IDE installation
 • Virtual Environment
• Pip – Package Manager
Hands-on:
How to set up an environment for python development.

Basics of Python:
Objective:
In this module, you will get a basic understanding of python Syntax and a detailed
understanding of Input/Output [I/O] operations, Variables, Operators Datatypes and
Data structure.
Topic:
• Python Syntax Overview, Indentation, comments
• Variable declaration
• Datatypes and data structure
o Primitive
o Non-primitive
• Operators in python
Hands-on:
How to write a basic Python code, variable declaration datatypes, and operators usage.

Program flow/ Data flow of Python:

Objective:
In this module, you will get a detailed understanding of conditional statements, looping,

control statements of python.

Topics:
• Conditional Statements
o if statement
o if … else statement
o if … elif… else statement
• Looping
o for loop
o for with else statement
o while loop
o while with else statement
 • Control Statements
o break
o Continue
o pass
• Assert Statement
Hands-on:
How to use conditional, looping and control statements in python.

Function in Python:
Objective:
In this module, you will get a detailed understanding of writing function, the scope of
variables, function with arguments, keyword arguments, lambda functions and Modules
in python.
Topics:
• Syntax of Function
• Function with *args & **kwargs
• Scope of variables
• Lambda function with map, filter, reduce method
• DocString
• Modules and standard Modules
Hands-on:
How to create and use functions and different types of argument, lambda function, and
modules in python.

File Handling in Python:

Objective:
In this module, you will get a detailed understanding of file concepts like create, read,
write, update, delete operation of file with python.
Topic:
• File Opening modes
• Context Manager in python
• File Operations
o Open
o Create
 o Read
o Write
o Update
o Delete
Hands-on:
How to create and use file operation in python.

Exception Handling in Python:

Objective:
In this module, you will get a detailed understanding of exception handling and creating

your own exception classes in python.

Topic:
• Types of Errors in python
• Exception handling with
o try … except
o try … except… finally
o try … except… else
• Multiple Exception
• Raising Exception
• User-defined Exception
Hands-on:
How to create and use built-in and user-defined exception handling in python.

Oops in Python:
Objective:
In this module, you will get a detailed understanding of Object-Oriented Programming
Concepts in python.
Topics:
• Oops Concepts with programming syntax
o Class
o Object
o Polymorphism
o Encapsulation
o Inheritance
 • Types of Methods in python
Hands-on:
How to use Object-oriented programming concepts in python.

Core Concepts in Python:

Objective:
In this module, you will get a detailed understanding of iterators, generators, decorators,
in python.
Topics:
• Iterator
• Generator
• Decorator
Hands-on:
How to use core concepts and application of core concepts in python.

Comprehension in Python:
Objective:
In this module, you will get a detailed understanding of List, Dictionary comprehension

and specialized sorts in python.

Topics:
• Comprehensions
o List
o Nested List
▪ if statement
▪ if … else statement
▪ Nested if … else statement
• Dictionary
• Sorting
o List
o Dictionary
Hands-on:
How to use comprehensions and sorting in python.

Thread and DateTime in Python:

Objective:
In this module, you will get a detailed understanding of multi-threading concepts,
datetime module alone with sleep and execution of code in python.
Topics:
• Terms in threading
o process
o thread
o multithreading
o Time complexity
• Thread Life cycle
• Programming with Threading & Multithreading
• Synchronization
• Sleep and execution time of code
• DateTime module
Hands-on:
How to use threading and data time concepts in python.

Advanced data Structure/ collections in Python:

Objective:
In this module, you will get a detailed understanding of advanced data structures in
python.
Topics:
• Deque
• namedtuple
• ChainMap
• Counter
• Ordered Dictionary
• Default Dictionary
Hands-on:
How to use advanced data structure in python.

MySQL with Python:

Objective:
In this module, you will get a detailed understanding of SQL statements and database
connection along with CRUD operation using python.
Topics:
• SQL statements & Operations
o Create
o Read
o Update
o Delete
• Python – SQL connector package installation
• Python with CRUD Operations
• Commit & Rollback
• SQL Related Exception Handling
Hands-on:
How to use and manipulate data in a database using python.

Network programming with Python:

Objective:
In this module, you will get a detailed understanding of Network programming, Client &
server concepts with python.
Topics:
• Terms and Basics of network programming
• The architecture of data transmission between sender and receiver using python
• Getting data from the remote server
• Client & Server-side programming
Hands-on:
How to use and networking module of python, data transmission between client to
server and server to client python.

Regular Expression with Python:

Objective:
In this module, you will get a detailed understanding of writing Regex with python.
Topics:
• Regex Syntax
o Quantifiers
o Metacharacters
o Special Sequences
 o Sets
• Python re module
• Methods with regex usage
Hands-on:
How to use and write regex in python.

GUI programming with Python:

Objective:
In this module, you will get a detailed understanding of developing the GUI application
using the PyQt5 module with python.
Topics:
• Introduction
• Components and Events
• An Example GUI
• Widgets
• Layout Management
• Signals & Slots
• QMessagesBox, QDialog
• Database Handling
Hands-on:
How to develop a GUI application with PyQt5 and python.

API access with Python:

Objective:
In this module, you will get a detailed understanding of accessing open APIs using
python.
Topics:
• Google Text to Speech
• Google Speech to Text
• OpenWeatherMap
Hands-on:
How to use open APIs using python.

DataScience with Python:

Objective:
In this module, you will get a Basic understanding of data science modules in python.
Topics:
• Pandas – Series and Dataframe
• Numpy
• Matplotlib
Hands-on:
How to use data science modules of python.

The project with Python:

Objective:
In this module, you are going to develop an application for own scenario.
Topics:
• Creating own application with any one of the frameworks
o Django App
o PyQt5 App
o Console oriented Core app