Syllabus
Syllabus
- MySQL Server Installation
- MySQL Workbench Installation
- MySQL Basics
- Database models
- ER Model Overview
- Data types
- Understanding Test Database
- Basics Queries
- DDL
- Tables - Create, Alter, Drop
- Constraints - Not Null, Primary Key, Unique Key, Foreign Key
- Indexes - Types, Create, Drop
- DML operations - Insert, Update & Delete
- Insert, Update, Delete & Truncate
- Common Operations
- Writing Queries
- Filters
- DISTINCT
- BETWEEN
- IN
- LIKE
- NOT
- NULL
- AND, OR, NOT etc
- Using Complex Data Filtering Techniques
- Limit, offset
- Sorting
- Ascending Order
- Descending Order
- Complex Sorting
- Functions
- String Functions - lower case, uppercase, substring, instring, trim, group_concat etc.
- Number Functions - round and trunc
- Date Functions - datepart etc
- Null Functions - NULLIF
- Case
- Groups
- Basics
- Grouping functions - AVG, MIN, MAX, COUNT, DISTINCT COUNT etc
- Grouping Filters - HAVING
- Rollups & Cubes
- Analytic (Window) Functions (Bonus)
- Over() with summary functions
- Partition By Queries
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
- Joins
- Cartesian Product
- Equi and Non-Equi Joins
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Self Join
- Complex Queries - Joins & Groups Integration
- SET Operators
- Union
- Union All
- Intersect
- Minus
- Subquery
- Single Valued Sub Query
- Multi Valued Sub Query
- Views
- Inline Views
- TOP N Analysis
- Bulk Operations
- Required to create test data:
- Creating Tables using queries
- Bulk Data Inserts using Queries
- Updating Data using Queries
- Deleting Data using Queries
- Transactions ( T SQL)
- Commit
- Rollback
- Savepoint
- Procedures and Functions basics
- Project
- Introduction & Installation
- Introdution
- Installing Python
- Installing Pycharm
- Configurations
- Programming Basics
- Variables
- Data Types
- User Inputs
- Unpacking
- Boolean Practice
- What If, Else and If, Making Decisions
- For Loops
- Break Continue Pass
- Break Vs Continue Vs Pass
- Data Structures
- List
- Tuple
- Set
- Logic Building & Programming
- Pattern Programs
- Prime Number
- Fibonacci Sequence
- Factorial
- Recursion
- Swap 2 Variables
- Functions
- Creating Functions
- Functions Arguments
- Type of Arguments
- Keyworded Variable Length Arguments
- Using Built in Functions
- Global Keywords
- Passing List to Functions
- Anonymous Functions / Lambda
- Filter / Map Reduce
- Decorators
- Modules
- OOPs Programming
- Modules
- Special variable _name_
- Class & Object
- _init_ method
- Constructor, Self and Comparing Objects
- Inheritance
- Polymorphism:
- Method Overloading
- Method Overriding
- Exception Handling
- File handling
- MySQL
- Installation
- CRUD Operations (create, read, insert, update, delete)
- Database Integration with Python
- Connection to Database from Python
- Writing functions to handle database operations
- CRUD Operations in python
- Web Scrapping
- Beautiful SOUP library
- Parsing HTML files
- Getting Data from Any Website
- Python for Data Science -
- NumPy & Pandas
- Installing & Understanding Analytics Packages
- NumPy Array Operations
- Pandas Data Frame Operations
- Data Acquisition (Import & Export)
- Indexing
- Selection
- Sorting
- Filtering
- Group By
- Binning
- Concatenation
- Merge
- Data Visualization
- Installing Matplotlib
- Charts & Plots
- Histogram
- Scatter Plots
- Box Plots
- Line Chart
- Bar Chart
- Pie Chart etc.
- Mean, Median, Mode, Variance, Standard Deviation
- Skewness, Kurtosis, Correlation, Covariance
- Probability Distributions: Normal, Binomial, Poisson
- Sampling, Central Limit Theorem
- Hypothesis Testing: t-test, z-test, ANOVA, chi-square
- Confidence Intervals, p-values, Type I & II errors
- Note – Statistics part will be taken along with ML & AI as and when required
- Data Preprocessing & EDA Overview
- Handling missing values, outliers, duplicates
- Encoding (One-hot, Label), Scaling (Standard, MinMax)
- Feature engineering, selection techniques
- EDA: Univariate, Bivariate, Multivariate analysis
- Visuals: Heatmaps, Pairplots, Boxplots, Violin plots
- Supervised Learning
- Linear & Logistic Regression
- Decision Tree, Random Forest, KNN
- SVM with kernel functions
- Naive Bayes, AdaBoost, XGBoost
- Project 1: House Price Prediction
- Project 2: Email Spam Detection
- Unsupervised Learning
- K-Means, DBSCAN, Hierarchical Clustering
- Dimensionality Reduction: PCA
- Project 3: Employee Role Segmentation
- Model Evaluation & Optimization
- Metrics: Accuracy, Precision, Recall, F1, AUC, R2, RMSE
- Cross Validation, Stratified K-Fold
- Hyperparameter tuning: GridSearchCV, RandomizedSearchCV
- Imbalanced Data Handling: SMOTE, NearMiss
- Time Series Analysis Basics
- Trend, Seasonality, Noise, Stationarity
- ADF Test, ARIMA, SARIMA
- Facebook Prophet, Exponential Smoothing
- Project 4: Sales Forecasting
- NLP Basics
- Text preprocessing: Tokenization, Lemmatization, Stopwords
- BoW, TF-IDF, Word2Vec
- Text Classification, Sentiment Analysis
- Project 5: Sentiment Analysis of Product Reviews
- Artificial Neural Networks (ANN)
- Perceptron, Multilayer Perceptron
- Forward & Backward Propagation
- Activation, Loss functions, Optimizers
- Overfitting, Regularization, Batch Normalization
- Project 6: Iris Flower Classification using ANN
- Computer Vision (CNNs for Image Processing)
- Convolution, Pooling, Padding
- Transfer Learning (VGG, ResNet)
- R-CNN, Faster R-CNN
- Project 7: Handwritten Digit Recognition
- RNNs & LSTMs
- RNN, LSTM, GRU, Bidirectional RNN
- Sequence Modeling, Time Series, Text Generation
- Project 8: Next Word Prediction using RNN & LSTM
- Attention Mechanism, Transformer Architecture
- Using Pre-trained Models (BERT, RoBERTa)
- Embedding generation & fine-tuning
- Project 9: Sentiment Analysis using BERT (Transformers)
- GenAI
- What is GenAI? Prompt Engineering
- Use cases: Q&A, Summarization, Image Generation
- Ethical concerns & safety
- LLM Apps with OpenAI & LangChain
- Using ollama
- LangChain Pipelines, FAISS, ChromaDB
- Chat with PDF, CSV, Excel
- Project 10: Flow Stack Machine Learning Project
- Web Deployment: Streamlit or Flask
- Create AWS Instance
- Model Deployment on AWS
- Note - this module will be mostly online as trainers aare rare.
- Introduction to Power BI
- What is Power BI and Why Power BI
- Installing Power BI Desktop
- Exploring the Power BI Workflow
- Adjusting the settings of the Power BI Desktop
- Comparison of Power BI vs Other Reporting Tools
- Getting and Transforming Data with Power BI Desktop
- Connecting to different sources
- Connecting to multiple sources
- Different connecting Options(DirectQuery vs Import Data Vs Live Connection)
- Shaping and transforming data with Power Query
- Editing,Merging,Appending queries etc
- Advance Importing techniques without coding
- Data cleaning & Advance data cleaning
- Modeling with Power BI
- Introduction to Modeling
- Building Relational Models(Setup and Manage Relationships)
- Creating table relationships
- Understanding the filter flow
- Cardinality and Cross filtering
- DAX
- Understanding Dax Syntax
- Calculated Columns vs Measures
- Common Dax functions and formulae
- Understanding the evaluation context in DAX
- Visualising Data with Reports
- Creating Visualisations
- Color & Conditional Formatting
- Setting Sort Order
- Scatter & Bubble Charts & Play Axis
- Tooltips
- Slicers, Timeline Slicers & Sync Slicers
- Cross Filtering and Highlighting
- Visual, Page and Report Level Filters
- Drill Down/Up
- Hierarchies
- Constant Lines
- Tables, Matrices & Table Conditional Formatting
- KPI's, Cards & Gauges
- Map Visualisations
- Custom Visuals
- Managing and Arranging
- Drillthrough
- Custom Report Themes
- Grouping and Binning
- Bookmarks & Buttons
- Introduction to Power BI Service
- Introduction to the Power BI Service
- Quick Tour of Power BI Service
- Connecting to Data from Power BI service
- Building Blocks of Power BI Service
- Sharing and Collaboration Tools in Power BI Service
- Sharing and Collaboration Options Overview
- Publish from Power BI Desktop
- Publish Reports to Web
- Printing and Exporting from Power BI Service
- Sharing Reports & Dashboards
- Power BI Refreshing Datasets
- What is refreshing?
- Implementation
- A.I. in Power BI
- Advance Report techniques
- Power Automate (Latest)
- Introduction
- installation
- Types of flows
- Actions
- Copy multiple excel, pdf files from one folder
- Merge PDF files together, Rename etc.
- Extract phones name and price from amazon
- Best Practices
- Installation and Configuration
- Introduction to Tableau
- Architecture
- Data Model
- Data Connection with single data sources
- Data Joins
- Live vs extract connection
- Working with multiple data sources
- Data Blending
- Difference between joining and blending data
- Tableau Interface
- Measures and dimension
- Shelves
- Show me
- Data Types
- Default Properties
- Marks Card
- Page Shelf
- Saving and sharing your work-overview
- Difference between .twb and .twbx
- Discrete vs Continuous
- Date Aggregations and Date parts
- Cross tab
- Totals & Subtotals
- Chart Types
- Bar Charts & Stacked Bars
- Pie Chart
- Line Graph with Date
- Line Graph without Date
- Area charts
- Tree Map
- Word Cloud
- Packed Bubble
- Box & Whiskers Plot
- Scatter Plots
- Highlight tables
- Heat maps
- Data Visualization
- Geographical fields
- Map options
- Run Time Columns
- Calculated Fields
- String Calculation
- Data Calculation
- Logical Calculation
- Numeric Calculation
- Parameters
- Table Calculations
- Level of detail
- Data Formatting
- Legends
- Highlights
- Labelling
- Number Formats
- Alignment
- Advanced Functionalities
- Sorting
- Groups
- Sets
- Hierarchy
- Reference and Trend Lines
- Edit Axis
- Bins
- Filters
- Dimension Filter
- Measure Filter
- Filter - Display Option
- Context Filters
- Relevant Filters
- Sets in filters
- Conditional Filters
- Advanced Charts
- Pareto
- Waterfall
- Funnel
- Waffel Chart
- Predictive Analysis
- Forecasting
- Clusters
- Dashboards
- Dashboard Objects
- Dashboard Actions
- URL
- Highlight
- Filter
- Advanced Interactivity
- Publish to Web
- Story (Adv)
- Report designing & exporting
- Case Studies / Project
- Data Visualization & Analytics
-
- Data Sources
- from SQL (MySQL)
- from Flat Files (CSV / Excel etc.)
- from Python
- Understand the Excel interface.
- Learn basic navigation and data entry.
- Excel Interface Overview
- Workbook vs. Worksheet
- Ribbon, Tabs, and Menus
- Cells, Rows, Columns
- Name Box and Formula Bar
- Basic Navigation
- Moving around the worksheet
- Selecting cells, rows, and columns
- Using the Go To command
- Data Entry and Basic Formatting
- Entering text, numbers, and dates
- Editing cell contents
- Basic formatting (font, size, color, cell alignment)
- Saving and Managing Workbooks
- Saving, opening, and closing workbookss
- Save As options (different formats)
- Summary Functions: SUM, AVERAGE, MIN, MAX, SUMIF, COUNTIF, AVERAGEIF, SUMIFS, COUNTIFS, AVERAGEIFS
- Using AutoSum
- Cell Referencing
- Relative vs. Absolute References
- Mixed References
- Organize and manage data efficiently
- Sorting and Filtering Data
- Sorting by one or multiple columns
- Applying filters to display specific data
- Using Filters using effectively
- Using Tables
- Creating and formatting tables
- Table styles and features (Total Row, Header Row)
- Data Validation:
- Creating drop-down lists
- Setting data entry rules
- Conditional formatting:
- Conditional formatting using ifs function, greater than, lower than and equal between
- Conditional formatting using colours
- Conditional formatting using colour scales, data bars & Icon sets
- Use advanced formulas and functions
- Logical Functions
- IF, AND, OR, NOT
- ** Loopkup Functions **
- VLOOKUP, VLOOKUP WITH MATCH FUNCTION, VLOOKUP WITH COLUMN & ROW FUNCTION, VLOOKUP WITH SEQUENCE FUNCTION, VLOOKUP WITH CURLY PARANTHESIS FUNCTION, VLOOKUP WITH SUMIF, SUMIFS, COUNTIF & COUNTIFS FUNCTIONS, VLOOKUP WITH MATCH FUNCTION, INDEX & MATCH, XLOOKUP, XLOOKUP WITH CONCATENATE FUNCTIONS
- Text Functions
- CONCATENATE, LEFT, RIGHT, MID, LEN
- Date and Time Functions
- TODAY, NOW, DATE, TIME, YEAR, MONTH, DAY, EOMONTH
- ** Office 365 function **
- Transpose
- Textjoin
- Sequence
- Filter
- Xlookup
- Vstack
- Hstack
- Aggregate and many more
- ** Pivoting - Analyze data using built-in Excel tools**
- Introduction to PivotTables
- What is a PivotTable?
- Benefits and uses of PivotTables
- Creating a PivotTable
- Preparing your data
- Inserting a PivotTable from various data sources
- Understanding PivotTable Components
- Rows, Columns, Values, and Filters areas
- Field List and Field Settings
- Basic PivotTable Operations
- Adding and arranging fields
- Sorting and filtering data within PivotTables
- Grouping data (by dates, numbers, etc.)
- Customizing PivotTables
- Changing the layout and style
- Using different PivotTable layouts (Compact, Outline, Tabular)
- Formatting numbers and cells
- Calculations in PivotTables
- Adding calculated fields and items
- Using summary functions (SUM, COUNT, AVERAGE, etc.)
- Showing values as percentages, differences, rankings
- PivotTable Options and Settings
- Understanding PivotTable options
- Refreshing data in PivotTables
- Changing data source
- PivotCharts
- Creating PivotCharts from PivotTables
- Customizing and formatting PivotCharts
- Understanding the relationship between PivotTables and PivotCharts
- Advanced PivotTable Techniques
- Creating dynamic PivotTables with named ranges
- Using Power Pivot to manage large data sets
- Connecting PivotTables to external data sources
- ** Create and customize advanced charts and graphs **
- Creating Charts
- Column, Bar, Line, Pie, and Combo charts
- Customizing chart elements (titles, axes, legends)
- Using Sparklines
- Creating line, column, and win/loss sparklines
- Advanced Chart Techniques
- Dynamic charts with named ranges
- Using secondary axes
- ** Automate tasks using Macros and VBA **
- Introduction to Macros
- Recording and running macros
- Macro security settings
- Basics of VBA (Recording):
- Starting with the recording Macros
- Recording of Macros
- Saving and running of Macros
- Creating Custom Functions
- Writing and using custom functions in VBA
- ** Collaborative and security features **
- Sharing and Collaboration
- Sharing workbooks
- Co-authoring and tracking changes
- Comments and notes
- Protecting Data
- Worksheet and workbook protection
- Setting permissions
- Excel Online and Mobile
- Using Excel in Office 365
- ** Power Query **
- Introduction to Power Query
- What is Power Query?
- Importance and benefits of using Power Query
- Getting Started with Power Query
- Installing and accessing Power Query
- Power Query interface and key components
- Connecting to Data Sources
- Types of data sources (Excel, databases, web, etc.)
- How to connect to different data sources
- Data Import and Shaping
- Loading data into Power Query
- Basic data transformation (filtering, sorting, removing duplicates)
- Transforming Data
- Using the Query Editor
- Applying common transformations (merging, appending, splitting columns)
- Pivoting and unpivoting columns
- Grouping data
- Data Cleaning
- Handling missing values
- Data type conversion
- Text transformations (trim, clean, replace, etc.)
- Working with Functions
- Using built-in functions
- Creating custom functions
- Parameters in Power Query
- Advanced Data Transformations
- Merging queries
- Append queries
- Using conditional columns
- Calculating and adding new columns
- ** Scenario manager**
- ** Dashboards (Project) **
- What are dashboards
- Getting data from differnt worksheets
- Display data in various charts on same sheet etc.
- ** Copilot (A.I. in Adv Excel)**
- Introduction & Benefits
- Enabling Copilot & Copilot Interface
- Data Analysis Using Copilot
- Formula & Function Assistance
- Generate formulas using AI
- Explain and fix formulas
- Optimize existing formulas
- Data Cleaning
- Identify missing or incorrect data
- Remove duplicates
- Standardize text, dates, and numbers
- Tables, PivotTables & Charts
- Create tables
- Generate PivotTables with prompts
- Create and customize charts
- Mini Project
- Copilot-based data analysis
- Dashboard with AI
- 10 to 12 months, includes Gen AI addon topics
(course can be completed little earlier, please contact with our counsellors for more info) - Upcoming Batches
- Get Fees
