Data Analyst Job Oriented Program

2.1 SELECT Statements

2.2 Filtering Data (WHERE)

2.3 Sorting Data (ORDER BY)

2.4 Aliasing

2.5 Basic Subqueries

Hands-On:

• Write a query to retrieve all columns from the employees table.
• Write a query to select only employee_name and department columns from employees and sort by department in descending order.
• Write a query to find employees who earn more than the average salary of all employees (using subquery).

3.1 Aggregate Functions: SUM(), AVG(), COUNT(), MIN(), MAX()

3.2 GROUP BY

3.3 HAVING

Hands-On:

• Write a query to count the number of employees in each department.
• Find the average salary of employees in each department with more than 5 employees.
• Get the highest and lowest salary from the employees table.

4.1 INNER JOIN

4.2 LEFT JOIN

4.3 RIGHT JOIN

4.4 FULL OUTER JOIN

4.5 SELF JOIN

Hands-On:

• Write a query to get a list of all employees and their department names using INNER JOIN.
• Use a LEFT JOIN to find employees and their departments, including those without a department.
• Create a SELF JOIN to find pairs of employees who work in the same department.

5.1 INSERT, UPDATE, DELETE Statements

5.2 Transactions (BEGIN TRANSACTION, COMMIT, ROLLBACK)

5.3 ACID Properties

Hands-On:

• Insert a new employee into the employees table.
• Update the salary of all employees in the “Sales” department by 10%.
• Write a transaction to increase salaries and rollback if an error occurs..

6.1 String Functions: CONCAT(), SUBSTRING(), TRIM()

6.2 Date Functions: DATE(), DATEDIFF()

6.3 Mathematical Functions: ROUND(), MOD()

6.4 Conditional Logic: CASE WHEN

Hands-On:

• Concatenate first and last names of employees into full_name
• Calculate the number of days an employee has worked since hire date.
  
• Use CASE WHEN to categorize employees’ salaries as ‘Low’, ‘Medium’, or ‘High

7.1 ROW_NUMBER(), RANK(), DENSE_RANK()

7.2 LEAD(), LAG()

7.3 OVER() Clause

Hands-On:

• Use ROW_NUMBER() to assign a unique rank to each employee based on salary.
• Write a query using LEAD() to compare each employee’s salary with the next employee’s salary.
• Rank employees within their departments based on salary using RANK().

8.1 CREATE, ALTER, DROP TABLE

8.2 Constraints: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE

8.3 Indexes: Creating and Managing Indexes

Hands-On:

• Create a projects table with appropriate data types and constraints.
• Add a NOT NULL constraint to the project_name column in the projects table.
• Create an index on the employee_name column to speed up search queries.

9.1 Creating and Using Views

9.2 Stored Procedures

9.3 User-Defined Functions

Hands-On:

• Create a view called high_earners that shows employees earning more than $75,000.
• Write a stored procedure to return the average salary for a given department.
• Create a function to calculate an employee’s yearly bonus based on their salary.

10.1 Query Optimization (using EXPLAIN)

10.2 Efficient Use of Indexes

10.3 Handling Large Data Sets (Partitioning, Sharding)

10.4 Data Security: GRANT and REVOKE

Hands-On:

• Analyze the performance of a slow-running query using EXPLAIN and optimize it.
• Partition the orders table by order_date for performance improvement.
• Grant SELECT and UPDATE privileges to a user on the sales table, then revoke DELETE privileges.

11.1 Extracting Data

11.2  Transforming Data

11.3 Loading Data (ETL)

11.4 Handling Semi-Structured Data (JSON, XML)

Hands-On:

• Extract customer data from the customer’s table and load it into an archive table.
• Write a transformation query that cleans and standardizes phone numbers before loading the data into a new table.
• Write a query to extract specific information from a JSON field in a table.

12.1 Python Basics: Syntax, Variables, and Data Types

12.2 Input and Output (e.g., print(), input())

12.3 Basic Operators and Expressions

12.4 Conditional Statements (if, elif, else)

12.5 Loops (for, while)

Hands-On:

• Write a Python script to calculate the total cost of a product based on user input for price and quantity.
• Create a program that checks if a number is even or odd.

13.1 Lists, Tuples, Sets, and Dictionaries

13.2 List Comprehensions and Dictionary Comprehensions

13.3 Common Methods for Lists and Dictionaries

13.4 Iterating over Data Structures with Loops

Hands-On:

• Create a list of customer names and sort them alphabetically.
• Create a dictionary mapping products to prices, and write a program to retrieve a product’s price based on user input.

14.1 Reading and Writing Files (Text and CSV)

14.2 File Handling (open(), read(), write(), with)

14.3 Parsing CSV Files with the csv Module

14.4 Handling Errors with Try/Except Blocks

Hands-On:

• Write a script that reads customer data from a text file and prints it to the console.
• Write a program that reads sales data from a CSV file, calculates total sales per product, and writes the results to a new CSV file.

15.1 Introduction to Pandas DataFrames and Series

15.2 Loading Data from CSV, Excel, or SQL Databases

15.3 Basic DataFrame Operations (Selection, Filtering, Sorting, Grouping)

15.4 Handling Missing Data with Pandas

Hands-On:

• Load a sales dataset into a Pandas DataFrame and perform operations such as filtering for specific products, sorting by sales, and handling missing values.
• Write a script to group sales data by region and calculate total sales for each region.

16.1 Data Cleaning Techniques (Dealing with Missing Data, Duplicates, Outliers)

16.2 Data Transformation: Renaming Columns, Changing Data Types, Applying Functions

16.3 Handling Dates and Times with pd.to_datetime()

16.4 Combining and Merging DataFrames

Hands-On:

• Write a script to clean a dataset by removing duplicates and filling missing values, then transform date columns into a standard format.
• Merge two DataFrames containing customer and sales data, and calculate total sales per customer.

17.1 Descriptive Statistics with Pandas (mean(), median(), mode(), std())

17.2 Data Visualization using Matplotlib and Seaborn (Bar Plots, Histograms, Box Plots)

17.3 Correlation Analysis and Pairwise Relationships

17.4 Detecting and Visualizing Outliers

Hands-On:

• Perform an EDA on a dataset, calculate basic statistics, and visualize key relationships using bar plots and histograms.
• Visualize correlations between numeric variables in a dataset and create a heatmap to show these relationships.

18.1 Advanced Visualization Techniques with Matplotlib and Seaborn

18.2 Line Plots, Scatter Plots, and Pie Charts

18.3 Customizing Plots (Titles, Labels, Legends, Colors)

18.4 Creating Subplots and Multi-Panel Figures

Hands-On:

• Create a multi-panel visualization showing sales trends over time and sales distribution by product.
• Write a script to generate a pie chart showing the market share of different products.

19.1 Connecting Python to Databases with SQLAlchemy

19.2 Writing SQL Queries from Python Using SQLAlchemy ORM

19.3 Loading Data from SQL Databases into Pandas

19.4 Writing Data Back to SQL Databases from Pandas

Hands-On:

• Write a Python script that connects to a SQL database, extracts sales data, and loads it into a Pandas DataFrame for analysis.
• Perform analysis on the data (e.g., find the top customers by revenue) and write the results back to the database.

20.1 Introduction to Numpy Arrays and Array Operations

20.2 Reshaping, Indexing, and Slicing Numpy Arrays

20.3 Applying Mathematical Functions and Aggregations to Numpy Arrays

20.4 Working with Multi-Dimensional Data

Hands-On:

• Create a Numpy array from a dataset and perform basic statistical operations (sum, mean, variance).
• Reshape a multi-dimensional array and apply mathematical transformations (e.g., logarithmic, square root) to the data.

21.1 Predictive Modelling : Linear Regression

21.2 Sales Predictions and Forecasting

21.3 Classification using Logistics Regression

21.4 Churn | Fraud Analysis

21.5 Univariate Analysis

21.6 Bi Variate Analysis

21.7 Multi Variate Analysis

21.8 Central Tendencies Mean, Median, Mode, Variance Standard Deviation,

21.9 Distribution Probability Distribution

21.10 Normal Distribution

21.11 Hypothesis Testing

21.12 Confidence Interval

21.13 Correlation Vs Covariance

21.14 Central Limit Theorm P value/ Alpha

21.15 Intro to Advance/ Inferential Statistics Hypothesis Testing

21.16 T test Z test Chi Square Anova

22.1 Excel Interface Overview: Ribbon, Formulas, and Sheets

22.2 Basic Formulas and Functions (e.g., SUM(), AVERAGE(), COUNT())

22.3 Cell Referencing: Absolute and Relative References

22.4 Sorting and Filtering Data

22.5 Conditional Formatting

Hands-On:

• Create a spreadsheet to track sales data and calculate total revenue, average order value, and the count of transactions.
• Use conditional formatting to highlight sales above a certain threshold.

23.1 Text Functions (LEFT(), RIGHT(), MID(), CONCAT())

23.2 Date Functions (TODAY(), DATEDIF(), TEXT())

23.3 Lookup Functions (VLOOKUP(), HLOOKUP(), INDEX(), MATCH())

23.4 Removing Duplicates and Data Cleaning Techniques

Hands-On:

• Write formulas to extract specific text from product codes and dates from transaction logs.
• Use VLOOKUP to match customer data with sales records and calculate the total sales per customer.

24.1 IF Statements and Nested IFs

24.2 Logical Functions: AND(), OR(), NOT()

24.3 Array Formulas and Dynamic Arrays

24.4 Advanced Lookup Techniques (e.g., XLOOKUP)

Hands-On:

• Create a formula to categorize sales as “High”, “Medium”, or “Low” based on predefined thresholds using nested IF statements.
• Use XLOOKUP to match product prices from a separate table and calculate total sales.

25.1 Creating and Customizing Pivot Tables

25.2 Grouping Data in Pivot Tables (by dates, categories)

25.3 Calculated Fields and Items in Pivot Tables

25.4 Creating Pivot Charts for Data Visualization

Hands-On:

• Create a pivot table to analyze sales by product, region, and time period. Include calculated fields to measure profit margins.
• Generate a pivot chart to visualize the top-selling products over time.

26.1 Data Validation and Custom Input Rules

26.2 What-If Analysis (Goal Seek, Data Tables, Scenario Manager)

26.3 Solver for Optimization Problems

26.4  Advanced Filtering and Slicers for Data Analysis

Hands-On:

• Use Goal Seek to find the necessary sales volume to reach a revenue target.
• Set up a scenario analysis to explore different sales strategies and their impact on profit.

27.1 Managing Large Data Ranges with Tables

27.2 Using Named Ranges for Better Data Management

27.3 Sorting and Filtering Large Datasets

27.4 Techniques for Speeding Up Performance in Large Workbooks

Hands-On:

• Organize a large dataset into an Excel table and create custom filters and sorts to analyze sales by category.
• Write a formula that references a named range to calculate the total sales for a specific product category.

28.1 Power Query for Data Import and Cleaning

28.2 Splitting, Merging, and Transforming Columns

28.3 Removing Errors and Duplicates

28.4 Automating Data Cleaning with Power Query Editor

Hands-On:

• Use Power Query to import and clean a dataset by removing duplicates, correcting errors, and splitting columns into meaningful components (e.g., first name, last name).
• Automate the data cleaning process for future use.

29.1 Introduction to Power Pivot and Data Models

29.2 Creating Relationships Between Tables in Power Pivot

29.3 DAX Formulas for Advanced Calculations

29.4 Creating Measures and KPIs (Key Performance Indicators)

Hands-On:

• Set up a Power Pivot data model with multiple related tables (e.g., sales, products, customers).
• Use DAX to create measures such as year-over-year growth, profit margin, and customer lifetime value.

30.1 Creating Interactive Dashboards in Excel

30.2 Advanced Chart Types: Waterfall, Radar, Sunburst, and Combo Charts

30.3 Using Sparklines to Highlight Trends in Data

30.4 Customizing Charts for Better Insights (using slicers and dynamic ranges)

Hands-On:

• Build an interactive sales performance dashboard that includes multiple chart types and interactive slicers for filtering data.
• Add sparklines to summarize key trends within your dataset.

31.1 Recording and Running Macros to Automate Repetitive Tasks

31.2 Introduction to VBA (Visual Basic for Applications)

31.3 Writing Basic VBA Scripts for Data Analysis

31.4 Automating Data Entry, Data Cleaning, and Report Generation with VBA

Hands-On:

• Write a macro to automate the monthly sales report generation process, including data cleaning and report formatting.
• Create a basic VBA script that updates and refreshes a dataset, generates a pivot table, and saves the result to a new sheet.

32.1 Power BI Overview: Components (Power BI Desktop, Power BI Service, Power BI Mobile)

32.2 Loading Data into Power BI: From Excel, CSV, and Databases

32.3 Power Query Editor Basics: Transforming Data

32.4 Creating Simple Visualizations (Bar Charts, Line Charts, Pie Charts)

Hands-On:

• Load a dataset (e.g., sales or customer data) into Power BI and create basic visualizations such as bar charts and line charts to display trends over time.

33.1 Using Power Query Editor to Clean and Transform Data

33.2 Handling Missing Data, Removing Duplicates, and Filtering Rows

33.3 Data Merging and Appending Queries

33.4 Advanced Data Transformation: Unpivoting, Splitting Columns, Conditional Columns

Hands-On:

• Load raw sales data into Power Query, clean and transform it by removing duplicates, filling in missing values, and merging with other datasets (e.g., product data).

34.1 Creating and Managing Relationships Between Tables

34.2 Understanding Cardinality and Cross-Filter Direction

34.3 Creating Data Models with Multiple Tables

34.4 Best Practices for Data Models (Star Schema, Snowflake Schema)

Hands-On:

• Build a data model using Power BI with multiple tables (e.g., orders, products, and customers).
• Create relationships between these tables and ensure the model follows best practices.

35.1 Introduction to DAX: Calculated Columns and Measures

35.2 Common DAX Functions: SUM(), AVERAGE(), COUNT(), DIVIDE()

35.3 Time Intelligence in DAX: TOTALYTD(), DATEADD(), PARALLELPERIOD()

35.4 Creating KPIs (Key Performance Indicators) with DAX

Hands-On:

• Write DAX formulas to calculate total revenue, year-to-date sales, and customer count.
• Create measures for KPIs such as average order value and monthly active customers.

36.1 Advanced DAX Functions: CALCULATE(), FILTER(), ALL(), REMOVEFILTERS()

36.2 Row-Level and Contextual Filtering with DAX

36.3 Using Variables in DAX for Complex Calculations

36.4 Hierarchies and Ranking with DAX

Hands-On:

• Use CALCULATE() and FILTER() to calculate metrics like sales by region or top 10 customers.
• Create dynamic rankings using DAX for product performance.

37.1 Designing Effective Dashboards and Reports in Power BI

37.2 Creating Advanced Visuals: Matrix, Tree Maps, Waterfall Charts, Gauge Charts

37.3 Custom Visuals from Power BI Marketplace

37.4 Best Practices for Data Storytelling with Visuals

Hands-On:

• Design a Power BI dashboard that includes a variety of visuals (e.g., matrix, waterfall, and tree maps) to display sales performance and customer insights.
• Focus on data storytelling.

38.1 Publishing Reports to Power BI Service

38.2 Managing and Organizing Workspaces

38.3 Sharing Reports and Dashboards with Stakeholders

38.4 Setting Up Row-Level Security (RLS) in Power BI Service

Hands-On:

• Publish a Power BI report to the Power BI Service and set up row-level security for different user groups.
• Share the report with team members and stakeholders via Power BI Service.

39.1 Introduction to Dataflows: Creating and Managing Dataflows

39.2 Automating Data Preparation with Power BI Dataflows

39.3 Extracting, Transforming, and Loading Data (ETL) with Dataflows

39.4 Connecting Dataflows to Power BI Reports

Hands-On:

• Create a dataflow to automate the ETL process for your sales data.
• Connect this dataflow to Power BI and build a report using the cleaned and transformed data.

40.1 Best Practices for Optimizing Power BI Reports

40.2 Optimizing DAX Calculations and Data Models

40.3 Reducing Dataset Size with Data Aggregations and Data Load Strategies

40.4 Using Performance Analyzer in Power BI

Hands-On:

• Optimize a Power BI report by refining the data model, optimizing DAX calculations, and using Performance Analyzer to identify performance bottlenecks.

41:1 Integrating Power BI with Other Tools (Excel, Azure, SQL, etc.)

41:2 AI Features in Power BI: Key Influencers, Q&A, and Decomposition Tree

41:3 Using Python and R Visuals in Power BI

41:4 Power BI and Power Automate Integration for Workflow Automation

Hands-On:

• Integrate Power BI with another tool like Excel or SQL.
• Use AI features like Key Influencers or Q&A to generate insights and build visualizations that highlight key drivers of business performance.

42:1 ChatGPT 40 ( Multipurpose ).

42:2 Microsoft Copilot for Excel

42:3 GIT Hub Copilot ( For Coding )

43:1 CV Preperation

43.2: Interview Preperation

43.3: LinkedIn Profile Update

43.4: Expert Tips & Tricks