Lesson 1: Introduction to Data Analysis (Full Course)

🧠 What Is Data Analysis?

Data Analysis is the structured process of examining data sets in order to draw
conclusions or extract insights that inform decision-making.
📍 Example:
Imagine a shop owner has a list of all items sold in the past 6 months. She wants to
know:
 Which products sell the most?
 What day of the week is busiest?
 Why sales are lower in certain months?
👉 By analyzing her sales data, she can discover patterns, understand customer
behavior, and improve her business decisions.

🌍 Real-World Applications of Data Analysis

Field Application Example

Business Track sales performance and identify marketing trends

Healthcare Analyze patient data to improve treatment outcomes

Track student performance and recommend learning

Education
paths

Sports Evaluate player performance and game strategies

Governme Monitor population growth, unemployment, and national

nt budgeting

📊 Types of Data Analysis

Let’s go deeper into the four major types of data analysis.
1. Descriptive Analysis
 Goal: Summarize past data
 Question: "What happened?"
 Tools: Totals, Averages, Charts
✅ Example:
“In April, 2,000 items were sold, generating ₦800,000 in revenue.”

2. Diagnostic Analysis
 Goal: Understand why something happened
 Question: "Why did it happen?"
 Tools: Comparisons, Trend analysis
✅ Example:
“Sales dropped in May because the online payment portal was down for 4 days.”

3. Predictive Analysis
 Goal: Forecast future outcomes
 Question: "What might happen?"
 Tools: Regression, Forecasting Models
✅ Example:
“If customer growth continues at 10% monthly, we’ll have 5,000 users by
December.”

4. Prescriptive Analysis
 Goal: Suggest the best action
 Question: "What should we do?"
 Tools: Decision trees, Simulations
✅ Example:
“To improve delivery speed, assign more bikes to Zone B during peak hours.”

🔁 The Data Analysis Process (Workflow)

Let’s break down the steps involved in data analysis:

Step Description Example

1. Ask a Start with a clear goal or “Which product sells best each
Question hypothesis. week?”

Download sales data from POS or

2. Collect Data Gather raw data from sources.
website.
Step Description Example

Remove errors, fill blanks, Remove duplicates, fix date

3. Clean Data
correct formats. formatting.

4. Explore Summarize with charts and Use Excel to find top-selling

Data tables. categories.

5. Analyze Use statistical or logical Compare sales by location or

Data methods. month.

6. Interpret Understand what the data tells “Product B sells more in urban
Results you. areas.”

7. Share Create reports, dashboards, Build a chart or dashboard in

Insights visuals. Excel.

🛠️Beginner Tools for Data Analysis

Let’s preview tools we'll explore later:
 Excel (or Google Sheets): Best for beginners
 Python (Pandas): Good for automation and large data
 Power BI/Tableau: For beautiful dashboards
 SQL: For database querying
Lesson 2: Understanding Data (Full Course)
This lesson helps you grasp what data is, how it’s classified, and the different forms
it takes. Understanding this foundation is essential before you start working with
any dataset.

🔍 What Is Data?
Data refers to raw facts and figures that are collected for analysis. It becomes
useful information when it is processed, structured, and interpreted.
🧾 Examples of raw data:
 Names, ages, and scores of students
 Daily temperatures in Lagos for June
 Product sales by date

📊 Types of Data
There are two major ways to classify data:
1. By Structure
A. Structured Data
 Organized in rows and columns
 Easy to search and analyze
 Usually found in Excel, CSV, databases
✅ Example:

Nam Ag Scor
e e e

Mary 18 90

John 20 85

B. Unstructured Data
 Not organized in a fixed format
 Harder to analyze without processing
 Found in emails, videos, audio, images
✅ Example:
 Tweets, customer reviews, video recordings

2. By Nature/Type

Data Type Description Example

Qualitative Describes qualities or Gender (Male/Female), Color

(Categorical) categories (Red/Blue)

Quantitative
Measurable and countable Age = 21, Height = 1.7m
(Numerical)

Let’s explore these further:

🟪 Qualitative Data
 Nominal: Categories without order
→ E.g., Car brand: Toyota, Honda, Ford
 Ordinal: Categories with a clear order
→ E.g., Rating: Poor, Average, Good, Excellent
🟦 Quantitative Data
  Discrete: Countable, whole numbers
→ E.g., Number of children: 2, 3
 Continuous: Measurable, decimal values
→ E.g., Height: 1.75m, Weight: 62.3kg

🔢 Levels of Measurement

Level Type Example

Nomin Categoric
Gender (Male, Female), Yes/No
al al

Ordina Categoric Education Level (High, Medium,

l al Low)

Interva Temperature in °C (no true

Numerical
l zero)

Age, Income, Height (has true

Ratio Numerical
zero)

📝 Why This Matters:

Understanding levels of measurement helps you know what analysis or chart is
appropriate.

🗂️Common Data Formats

Format Description Used With

Plain text file with comma-separated

CSV (.csv) Excel, Python
values

Excel Excel, Google

Spreadsheet format
(.xlsx) Sheets

JSON
Structured data in key-value pairs Web APIs, Python
(.json)

SQL Data stored in tables, queried via MySQL,

Database SQL PostgreSQL

📌 Real-Life Scenario: Understanding a Dataset

Imagine this dataset:
Stude Gend Ag Scor Grad
nt er e e e

Femal
Ada 18 90 A
e

Musa Male 19 75 B

Femal
Chioma 20 62 C
e

Let’s classify:
 Structured/Unstructured? → Structured
 Qualitative data? → Gender, Grade
 Quantitative data? → Age, Score
 Levels of measurement?
o Gender → Nominal

o Grade → Ordinal

o Age → Ratio

o Score → Ratio

Lesson 3: Tools for Data Analysis (Full Course)

Now that you understand what data is and how it’s structured, the next step is
learning about the tools used to analyze data. We’ll cover the most important
beginner-friendly tools, and help you choose the best one to start with.

🧰 Why Are Tools Important?

Data analysis tools help you:
 Import and clean data
 Calculate statistics and metrics
 Create charts and dashboards
 Automate repetitive tasks
 Present insights clearly
Choosing the right tool depends on:
 Your current skill level
 Type of data
  Size of data
 Your analysis goals

🔧 Overview of Popular Data Analysis Tools

Here’s a breakdown of key tools and what they’re best for:

Tool Strengths Who Should Use It?

Easy to use, built-in functions, Beginners & Office

Excel
charts Users

Google Sheets Cloud-based Excel alternative Teams & Collaboration

Python Powerful for automation & big Intermediate to

(Pandas) data Advanced

Great for statistics & data Statisticians &

R
visualization Researchers

Excellent for working with Data Analysts &

SQL
databases Engineers

Power BI / Business Intelligence

Drag-and-drop dashboards
Tableau Users

🥇 Recommended Starting Tool: Excel

We’ll focus first on Excel, because:
 It’s widely available and easy to learn
 You can do 80% of basic data analysis tasks
 Skills can easily transfer to Google Sheets or other platforms

🏗️Excel Basics for Data Analysis

✅ Features That Make Excel a Powerful Data Tool:
 Rows & columns make it easy to structure data
 Built-in formulas: SUM, AVERAGE, COUNTIF, IF, VLOOKUP
 Charts: Bar, Line, Pie, Scatter
 Data cleaning tools: Filter, Sort, Remove Duplicates
 Pivot Tables: Summarize large datasets quickly
  Conditional formatting: Highlight trends and errors

✨ Real-Life Example: Basic Analysis in Excel

Imagine you have this table:

Produ Price Quantity

ct (₦) Sold

Rice 4000 200

Soap 250 400

Fan 9500 50

You can calculate:

 Total Sales = Price × Quantity
 Use formula: =B2*C2
 Copy formula down to other rows
You can also:
 Sort by highest-selling product
 Filter for products with sales > ₦500,000
 Create a bar chart to compare sales

⚙️Setting Up Your Excel Workspace

1. Install or Open Excel (or use Google Sheets)
2. Create a new workbook
3. Label your columns clearly
4. Use keyboard shortcuts like:
o Ctrl + Arrow Keys – Navigate data quickly

o Ctrl + Shift + L – Turn filters on/off

o Alt + = – AutoSum

Lesson 4: Collecting and Importing Data (Full Course)

Before you can analyze anything, you need data to work with. This lesson shows
you how to find, collect, and bring data into Excel for analysis. Whether you're
getting data from the web, a survey, or a local file, you’ll learn how to start the right
way.
🔍 Why This Step Is Crucial
Bad or poorly collected data will give bad results — no matter how good your
analysis skills are.
“Garbage in, garbage out” — meaning the quality of your insights depends on the
quality of your data.

🧭 Where Does Data Come From?

🔹 1. Primary Data Sources
You collect it yourself.

Method Example

Google Forms for student

Surveys
feedback

Interviews Sales calls or customer feedback

Observatio Manually tracking foot traffic in

ns a shop

🔹 2. Secondary Data Sources

Data already collected by others.

Source Example

Government
data.gov.ng
websites

Online databases Kaggle, World Bank, WHO

Weather, stock prices, sports

Public APIs
stats

Sales, HR, inventory

Company records
spreadsheets

🗂️Common File Types for Data

Form
Description Where It's Used
at

Plain text, comma-separated Web data,

.CSV
values exports
Form
Description Where It's Used
at

.XLSX Excel workbook Business records

Logs, simple
.TXT Raw text
reports

.JSON Hierarchical key–value pairs APIs

.SQL Database tables Data warehouses

📥 How to Import Data into Excel

✅ A. Importing from a Local CSV or Excel File
1. Open Excel
2. Click File → Open → Browse
3. Select your .csv or .xlsx file
4. Excel will automatically load the data
📌 Tip: When importing a CSV, Excel may place all data in one column — fix this by
selecting the column, going to:
Data → Text to Columns → Delimited → Comma

✅ B. Importing Web Data into Excel

1. Go to Data → Get & Transform Data → From Web
2. Paste a website URL with tabular data (e.g.,
https://www.worldometers.info/coronavirus/)
3. Excel will detect tables → choose one to import
💡 Use case: Real-time COVID stats, currency exchange rates, etc.

✅ C. Importing Data from Google Sheets

1. Open your Google Sheet
2. Click File → Download → Microsoft Excel (.xlsx) or CSV
3. Open the file in Excel

📌 Real-Life Task Example

You want to analyze monthly electricity bills. You create a simple table in a
spreadsheet:

Mont Units Cost

h Used (₦)

Jan 100 5,000

Feb 120 6,000

Mar 95 4,750

You can now:

 Import this table into Excel
 Calculate average usage
 Create a line chart to visualize your spending trend

💡 Common Mistakes When Importing Data

Mistake How to Fix

Use “Text to Columns” with correct

All data in one column
delimiter

Missing headers Add headers manually in row 1

Dates imported as Format cells as Date (Right-click →

text Format)

Incorrect number Ensure ₦ or commas don’t break

formats calculations

Lesson 5: Exploring Data in Excel (Full Course)

Once you’ve imported your data into Excel, the next step is exploration—getting
familiar with your dataset, identifying patterns, and summarizing the information.
This process is key to understanding what the data is telling you before jumping
into deeper analysis or charts.

🧭 Why Explore Your Data?

Data exploration helps you:
 Understand the structure of your data
 Spot errors or outliers
 Find relationships between variables
  Generate questions for deeper analysis

🔍 Key Excel Techniques for Exploring Data

We'll walk through practical features you’ll use all the time in Excel.

✅ 1. Sorting Data
Sorting helps you organize data to see:
 Top-performing items
 Lowest values
 Trends over time
📌 Example:
You have this dataset:

Nam Scor
e e

Mary 75

James 82

Amak
90
a

🔧 To sort from highest to lowest:

1. Select the data
2. Go to Data → Sort
3. Choose “Sort by Score → Largest to Smallest”

✅ 2. Filtering Data
Filters allow you to focus on specific rows:
 Only sales above ₦100,000
 Only students with A grade
 Only East region products
🔧 Steps:
1. Select the data table
 2. Click Data → Filter
3. Use dropdowns to select what you want to see
📌 Pro Tip: Use Number Filters → Greater Than… to isolate values above a
threshold.

✅ 3. Conditional Formatting
This highlights cells based on conditions, making patterns or problems easier to see
visually.
🔧 Example:
Highlight all sales above ₦500,000 in green.
Steps:
1. Select the Sales column
2. Go to Home → Conditional Formatting → Highlight Cell Rules →
Greater Than
3. Enter 500000, then choose a green fill
🟩 Result: You instantly spot high-performing items!

✅ 4. Quick Summary Stats

Use Excel’s Status Bar and formulas to calculate:
 Total (=SUM(range))
 Average (=AVERAGE(range))
 Count (=COUNTA(range))
 Maximum & Minimum (=MAX(), =MIN())
📌 Example:

Ite Units
m Sold

Fan 45

Rice 150

Milk 98

 =SUM(B2:B4) → Total units sold

 =AVERAGE(B2:B4) → Average units
  =MAX(B2:B4) → Highest units sold

✅ 5. Using COUNTIF and SUMIF

These functions allow conditional counting and summing.
COUNTIF:
“How many students scored above 70?”
=COUNTIF(B2:B10, ">70")
SUMIF:
“Total sales from North region?”
=SUMIF(A2:A10, "North", B2:B10)
(Where A has region and B has sales)

🧪 Practice Activity (You can do this in Excel)

Create a table like this:

Produ Regio Sales

ct n (₦)

Rice North 120000

Milk West 95000

Soap East 110000

Fan North 70000

Bread South 125000

Tasks:
1. Sort sales from highest to lowest
2. Filter to show only North region
3. Use =AVERAGE() to find average sales
4. Use =COUNTIF(C2:C6, ">100000") to count how many products sold over
₦100,000
5. Highlight cells with sales > ₦100,000 using conditional formatting
Lesson 6: Data Cleaning and Preparation (Full Course)
Even the best tools can’t give you good insights if the data is messy. Data
cleaning and preparation is about correcting errors, removing irrelevant
information, and structuring your dataset so it's ready for meaningful analysis.
Think of it like preparing ingredients before cooking — if your onions are rotten or
your rice is dirty, the meal won’t turn out well.

✅ Why Data Cleaning Matters

Messy data can lead to:
 Wrong insights
 Broken charts
 Incorrect formulas
 Wasted time and effort
Clean data leads to:
 Accuracy
 Confidence in results
 Smoother analysis

🧼 Common Problems Found in Raw Data

Problem Example

Empty cells in Name or Price

Missing values
columns

Same customer record appears

Duplicates
twice

Inconsistent formats “10k”, “₦10,000”, “10000”

Extra spaces or special

" John ", "#Milk!", "rice "
chars

Incorrect data types Text stored as numbers or dates

“Note” or “Address” in a sales

Irrelevant columns
report

🧰 Excel Techniques for Data Cleaning

✅ 1. Removing Blank Rows or Cells
Remove blank rows:
 Select your data
  Go to Home → Sort & Filter → Filter
 Filter for blank rows and delete them
Or use Go To Special:
 Ctrl + G → Special → Blanks → OK
 Right-click → Delete → Entire Row

✅ 2. Removing Duplicates
 Select your table
 Go to Data → Remove Duplicates
 Choose the columns to check for duplication
✅ Useful when:
 Cleaning survey responses
 Managing inventory lists

✅ 3. Fixing Text Formatting

Remove leading/trailing spaces:
Use: =TRIM(A2)
Change text to lowercase/uppercase:
 =LOWER(A2) → john
 =UPPER(A2) → JOHN
 =PROPER(A2) → John Doe

✅ 4. Splitting or Combining Columns

Split full names:
From "John Smith" into "John" and "Smith":
 Use Text to Columns (under Data tab → Delimited → Space)
Combine names:
Use: =A2 & " " & B2
Or: =CONCATENATE(A2, " ", B2)
✅ 5. Fixing Numbers Stored as Text
Sometimes numbers look correct but won’t calculate.
 Select the column
 Look for a small green triangle (Excel warning)
 Click ⚠️→ Convert to Number
Or:
 Use: =VALUE(A2) to convert text to number

✅ 6. Standardizing Data Entries

Use Find & Replace to fix inconsistent labels:
 "F", "fem", "Female" → all become “Female”
 Ctrl + H → Find: fem → Replace: Female

🔎 Real-World Example
Raw table:

Gend Ag Reven
Name
er e ue

JOHN male 23 ₦50,000

Ada FEM 19 ₦60k

john Male 23 50000

Chiom
female 50,000
a

Issues:
 Repeated "John"
 Inconsistent gender format
 Missing age
 Different currency formats
Fixes:
 Remove duplicate "John"
 Standardize “Gender” column to “Male”/“Female”
  Use =TRIM() and =PROPER() for names
 Clean revenue column (remove ₦, “k”) and convert to number

🧪 Practice Task
Create this table in Excel:

Nam Gend Ag Incom

e er e e

john MALE 25 ₦30k

Amak Femal ₦45,00

22
a e 0

₦30,00
john male 25
0

Ade 29 45000

Grace F 23 ₦50k

Cleaning Steps:
1. Remove duplicate "john"
2. Fill missing gender or age
3. Standardize "Gender" values
4. Clean "Income" column to make it pure numbers (no ₦, commas, or “k”)
5. Convert "Income" column to Number format
Would you like this as a downloadable Excel cleaning challenge?

🧠 Summary: Excel Functions for Cleaning

Task Function

Remove spaces TRIM()

LOWER(), UPPER(),
Change case
PROPER()

CONCATENATE() or =A1
Join columns
& B1

Convert text to
VALUE()
numbers
Task Function

Data → Remove
Remove duplicates
Duplicates

FIND & REPLACE (Ctrl +

Replace text
H)

📘 Lesson 7: Data Visualization Basics (Full Course)

Once your data is clean and ready, visualizing it helps you understand patterns,
spot trends, and communicate insights clearly. This lesson covers the basics of
data visualization, focusing on which charts to use and how to create them in Excel.

🎯 Why Visualize Data?

 Makes complex data easier to understand
 Highlights key trends and outliers
 Helps in decision-making and storytelling
 Engages your audience with clear visuals

📊 Common Types of Charts and When to Use Them

Chart
When to Use Example
Type

Bar Chart Compare categories Sales by product

Line Monthly revenue

Show trends over time
Chart growth

Market share
Pie Chart Show parts of a whole
distribution

Scatter Show relationship between two

Height vs weight
Plot variables

Histogra Frequency of test

Show data distribution
m scores

🛠 Creating Charts in Excel

Step-by-Step: Bar Chart
1. Select your data (e.g., Product names and Sales)
 2. Go to Insert → Charts → Bar Chart
3. Choose a style (Clustered Bar is common)
4. Customize chart title, axis labels, colors

Step-by-Step: Line Chart

1. Select data with time series (e.g., Months and Revenue)
2. Insert → Line or Area Chart → Line
3. Format to improve readability (add data labels, gridlines)

Step-by-Step: Pie Chart

1. Select category data and values (e.g., Market segments and %)
2. Insert → Pie Chart
3. Add labels and percentage values for clarity

💡 Tips for Effective Visualizations

 Keep it simple: Don’t overload charts with too much data
 Use clear labels: Titles, axis labels, legends
 Choose the right chart: Match chart type to your message
 Use color wisely: Highlight key parts, avoid too many colors
 Avoid 3D charts: They can distort data perception

📌 Example Scenario
You have this sales data:

Mont Sales
h (₦)

150,00
Jan
0

180,00
Feb
0

Mar 170,00
Mont Sales
h (₦)

Goal: Show sales trend over the quarter.

 Use a Line Chart for clear month-to-month trends
 Highlight February as the highest sales month with a different color or
annotation

🧪 Practice Activity
1. Create a simple table with your favorite products and sales numbers.
2. Make a Bar Chart comparing sales.
3. Use a Pie Chart to show percentage contribution of each product to total
sales.
4. Experiment with chart titles, colors, and labels to make it clear.

✅ Summary:

Action How to Do it in Excel

Select data → Insert → Choose

Insert Chart
chart

Edit Click chart elements, type or

Titles/Labels format

Change Colors Chart Design → Change Colors

Add Data
Chart Elements → Data Labels
Labels

📘 Lesson 8: Basic Statistical Analysis (Full Course)

Statistics are the backbone of data analysis — they help you summarize, describe,
and make sense of data. In this lesson, we’ll explore essential statistical concepts
and how to calculate them in Excel.

🧠 Why Statistics Matter

 Condense large data sets into understandable summaries
 Measure central tendencies and variability
  Support decision making with quantitative evidence

🔢 Key Statistical Concepts

1. Measures of Central Tendency
These tell you where the center of your data lies.

Measure What It Means How to Calculate in Excel

Mean Sum of all values ÷ number of

=AVERAGE(range)
(Average) values

Middle value when data is

Median =MEDIAN(range)
sorted

=MODE.SNGL(range) (Excel
Mode Most frequent value
2010+)

2. Measures of Spread (Variability)

These tell you how spread out your data is.

Measure What It Means Excel Formula

Difference between max and min

Range =MAX(range) - MIN(range)
values

Average of squared differences from =VAR.S(range) (sample

Variance
mean variance)

Standard Square root of variance; typical =STDEV.S(range) (sample

Deviation distance from mean std. dev.)

3. Frequency Distribution
Shows how often each value or range of values occurs.
 Use Pivot Tables or COUNTIF functions to build frequency tables.

📊 Applying Statistics in Excel

Example Dataset (Test Scores):

Stude Scor
nt e

A 80
Stude Scor
nt e

B 90

C 85

D 90

E 70

Calculate:
 Mean: =AVERAGE(B2:B6) → 83
 Median: =MEDIAN(B2:B6) → 85
 Mode: =MODE.SNGL(B2:B6) → 90
 Range: =MAX(B2:B6) - MIN(B2:B6) → 20
 Standard Deviation: =STDEV.S(B2:B6) → measures spread

🧪 Practice Exercise
Using this data:

Produ Sales
ct (₦)

Rice 150000

Beans 130000

Yam 170000

Plantai
125000
n

Garri 175000

Calculate:
1. Mean sales
2. Median sales
3. Mode (if any)
4. Range of sales
5. Standard deviation
💡 Tips
 Use =COUNT(range) to find the number of data points.
 For frequency tables, use =COUNTIF(range, condition).

✅ Summary Table of Excel Formulas

Task Formula

Mean =AVERAGE(range)

Median =MEDIAN(range)

Mode =MODE.SNGL(range)

=MAX(range)-
Range
MIN(range)

Variance =VAR.S(range)

Standard
=STDEV.S(range)
Deviation

Count =COUNT(range)

📘 Lesson 9: Introduction to Pivot Tables (Full Course)

Pivot tables are one of Excel’s most powerful tools for summarizing, exploring, and
analyzing large datasets quickly without complex formulas.

🧠 What Is a Pivot Table?

A pivot table lets you:
 Summarize data by categories or groups
 Rearrange (or “pivot”) rows and columns dynamically
 Perform calculations like sums, averages, counts
 Filter and drill down into data easily

📊 Why Use Pivot Tables?

 Instantly get insights without writing formulas
 Handle large datasets easily
  Create interactive reports and dashboards

🛠️Creating a Pivot Table: Step-by-Step

Imagine you have this data:

Produ Regio Sales

Date
ct n (₦)

150,00 2024-01-
Rice East
0 12

2024-02-
Fan West 90,000
05

120,00 2024-02-
Milk North
0 20

160,00 2024-03-
Rice East
0 15

To create a pivot table:

1. Select any cell inside your data table.
2. Go to Insert → Pivot Table.
3. Choose to place it in a new worksheet or existing one.
4. The Pivot Table Field List appears on the right.

🔄 Pivot Table Elements

Area Purpose

Rows Categories for grouping (e.g., Product)

Column
Sub-categories (optional, e.g., Region)
s

Numbers to summarize (e.g., Sales Sum,

Values
Count)

Filter data shown in the table (e.g., Date

Filters
Range)

📌 Example Use
To find total sales per product:
 Drag Product to Rows
 Drag Sales (₦) to Values (default: Sum)
To see sales by region:
 Drag Region to Columns
To filter by date:
 Drag Date to Filters and select desired range

✨ Benefits of Pivot Tables

 Automatically summarize thousands of rows
 Rearrange fields with drag and drop
 Easily add filters and slicers
 Update dynamically when data changes

🧪 Practice Activity
Using the data above:
1. Create a pivot table to sum sales by Product.
2. Add Region as columns to compare sales across regions.
3. Use Date as a filter to show sales only from Feb 2024.

✅ Quick Tips
 Refresh your pivot table if source data changes: Right-click pivot table →
Refresh
 Double-click a value cell to see the detailed rows behind that summary
 Use slicers for easy filtering: PivotTable Analyze → Insert Slicer
📘 Lesson 10: Basic Dashboards in Excel (Full Course)
A dashboard is a single page that visually displays key information and insights
from your data — making it easy to understand and share at a glance.

🎯 Why Build Dashboards?

  Combine multiple charts and tables for a full view
 Monitor performance metrics quickly
 Communicate insights to stakeholders clearly
 Save time by having all key info in one place

🛠️Elements of a Basic Dashboard

Element Description

Charts Bar, line, pie, or other charts

Summarized data or key

Tables
numbers

Slicers/ Interactive controls to filter

Filters data

Key numbers like totals,

KPI Metrics
averages

Step-by-Step: Creating a Simple Dashboard

1. Prepare your data: Clean, structured, and summarized (pivot tables are
great)
2. Create individual charts: Sales trends, category breakdown, top products
3. Add key metrics: Total sales, average sales, number of transactions
4. Insert slicers: Add slicers linked to pivot tables for interactivity
5. Arrange everything neatly on one Excel sheet

Example
Imagine you track sales by product and region.
 A line chart shows monthly sales trends
 A bar chart compares sales by region
 A table lists top 5 selling products
 Slicers let you filter by year or product category

📌 Dashboard Design Tips

  Keep it clean and uncluttered
 Use consistent colors and fonts
 Highlight the most important data
 Make interactive elements obvious
 Use titles and labels clearly

🧪 Practice Activity
Using your previous sales data:
1. Create a dashboard sheet in Excel
2. Add a line chart for monthly sales
3. Add a bar chart comparing regional sales
4. Insert slicers to filter by product and region
5. Add a KPI section with total and average sales

✅ Summary

Task Excel Feature

Prepare summarized
Pivot Tables
data

Create charts Insert → Charts

Add interactivity Insert → Slicer

Arrange dashboard Drag and resize charts and

elements tables

Introduction to Power BI

What is Power BI?

Power BI is a Microsoft tool for:
 Connecting to many data sources
 Transforming and modeling data
 Creating interactive reports and dashboards
  Sharing insights easily

Why Use Power BI?

 Handles large datasets easily
 Drag-and-drop interface for creating visuals
 Powerful data transformation with Power Query
 Real-time data updates & sharing via the cloud
 Integrates well with Excel, Azure, and other Microsoft products

Key Components of Power BI

Component Description

Power BI Windows app to build reports and

Desktop models

Power BI Cloud platform to publish & share

Service reports

Power BI
View reports on mobile devices
Mobile

Getting Started: Power BI Desktop

1. Download and install Power BI Desktop from Microsoft’s website (free).
2. Open Power BI Desktop and get familiar with the interface:
o Ribbon with tools

o Fields pane (your data tables)

o Visualizations pane (charts, maps, tables)

o Report canvas (where you build visuals)

Basic Workflow in Power BI

1. Connect to Data Sources
Examples: Excel files, SQL databases, web APIs, CSV files
2. Transform Data (Power Query Editor)
Clean, filter, merge, and shape data before loading it
 3. Create Data Model
Define relationships between tables, create calculated columns/measures
4. Build Visualizations
Drag fields onto the canvas to create charts, slicers, maps, KPIs
5. Publish & Share
Upload reports to Power BI Service to share with your team

Simple Example: Import Excel Sales Data

 Import Excel sales data file
 Use Power Query to remove duplicates and format columns
 Create relationships if multiple tables exist
 Build a bar chart to show sales by product
 Add slicers to filter by region or date
📘 Installing and Setting Up Power BI Desktop

Step 1: Check System Requirements

 Operating System: Windows 10 or later (Power BI Desktop is Windows-only)
 RAM: Minimum 4GB (8GB recommended)
 Disk Space: Around 1 GB free space
 .NET Framework: Version 4.6 or later (usually pre-installed)

Step 2: Download Power BI Desktop

1. Open your web browser and go to the official Microsoft Power BI download
page:
https://powerbi.microsoft.com/en-us/desktop/
2. Click Download free or Get Power BI Desktop.
3. You’ll be redirected to Microsoft Store or a direct download page.
4. If using Microsoft Store (recommended):
o Click Get to install Power BI Desktop automatically.

5. If downloading the installer file (.exe):

o Save the file to your computer.
Step 3: Install Power BI Desktop (If using installer)
1. Locate the downloaded .exe file (usually in your Downloads folder).
2. Double-click the installer file to start installation.
3. Follow the prompts:
o Accept license agreement

o Choose install location (default is fine)

o Click Install

4. When installation finishes, click Finish.

Step 4: Launch Power BI Desktop

 Open Power BI Desktop from the Start menu or desktop shortcut.
 The interface will open with the Welcome Screen showing options like:
o Get Data

o Open other reports

o Learn more

Step 5: Initial Setup and Sign-In (Optional but Recommended)

 You can use Power BI Desktop without signing in, but signing in unlocks
cloud features like publishing reports.
 Click Sign in (top right corner).
 Enter your Microsoft work, school, or personal account credentials.

Step 6: Explore the Interface

 Ribbon at the top: Home, View, Modeling tabs with tools
 Fields Pane on the right: Shows tables and columns from your dataset
 Visualizations Pane: Choose charts, maps, tables, slicers
 Report Canvas: The main area where you create reports
Connecting to an Excel or CSV File in Power BI Desktop
Step-by-step:
1. Open Power BI Desktop.
2. On the Home tab, click Get Data.
3. In the dropdown, select Excel if you have an Excel workbook or Text/CSV for
CSV files, then click Connect.
4. Browse your computer to find your file (Excel .xlsx or CSV .csv), select it, and
click Open.
5. Power BI will show you a Navigator window with available sheets (Excel) or
preview (CSV).
6. Select the table or sheet you want to import.
7. Click Load to import directly, or Transform Data to open Power Query
Editor (recommended for cleaning or shaping data before loading).

2️⃣ Transforming Data with Power Query Editor

What is Power Query Editor?

 It’s a built-in tool for cleaning and shaping your data before loading it into
Power BI.
 You can remove errors, filter rows, rename columns, split data, change data
types, and more.

Common Power Query Tasks:

1. Remove unnecessary columns
2. Filter rows (e.g., only sales > 100,000)
3. Change data types (ensure dates, numbers are correctly formatted)
4. Remove duplicates
5. Split or merge columns

How to transform:
 After clicking Transform Data from the Navigator:
 Use the ribbon and right-click options on columns to:
o Rename columns
 o Remove columns

o Filter rows by clicking the filter icon

o Change data type by clicking the icon left of the column name

o Remove duplicates: Select column → Home → Remove Rows → Remove

Duplicates
 When done, click Close & Apply on the top-left to load the cleaned data into
Power BI.

3️⃣ Building Your First Visualization in Power BI Desktop

Let’s build a simple bar chart to show sales by product:

1. After loading your data, you will see the Fields pane on the right listing your
tables and columns.
2. Drag the Product field to the Axis area in the Visualizations pane.
3. Drag the Sales field to the Values area.
4. A bar chart will appear on the report canvas showing sales per product.

Customize your chart:

 Click the chart to select it.
 Use the Format paint roller icon in Visualizations pane to:
o Change colors

o Add data labels

o Adjust title and axis fonts

Add a slicer for interactivity:

1. From Visualizations, click the Slicer icon.
2. Drag a field like Region or Date to the slicer.
3. You can now filter your chart interactively by clicking slicer options.
🧑‍🏫 Lesson 1: Introduction to SQL and Data Analysis
(Full Course with Deep Explanations & Examples)

✅ What is SQL?
SQL (Structured Query Language) is a programming language used to
communicate with databases. It allows you to:
 Retrieve specific data
  Filter and sort results
 Combine data from multiple tables
 Perform calculations and summaries
 Modify, add, or delete data
SQL is the language behind most data analysis tasks in relational databases.

✅ Why SQL for Data Analysis?

Reason Benefit

Easy to learn Simple English-like syntax

Works with all major databases (MySQL, PostgreSQL,

Highly flexible
SQLite, etc.)

Efficient for big data Designed for millions of rows

Real-world Used in finance, marketing, healthcare, e-commerce,

applications etc.

Integrates with other

Connects easily to Excel, Power BI, Python, Tableau, etc.
tools

✅ What is a Database?
A database is a structured collection of data. You can think of it like a digital filing
cabinet. Inside databases, data is organized into tables — like spreadsheets with
rows and columns.

✅ Example of a Table
Employees Table:

departme salar
id name
nt y

50,00
1 John HR
0

75,00
2 Amaka IT
0

Blessin 60,00
3 Marketing
g 0
Each row = a record (a person),
Each column = a field (attribute like name or salary)

✅ Basic SQL Syntax

SELECT column_name
FROM table_name;
Example:
SELECT name
FROM employees;
👉 This returns all employee names from the table.

✅ Key Terms to Know

Term Meaning

Collection of data arranged in rows and

Table
columns

Row A single record (e.g., one employee)

Column A field or attribute (e.g., salary)

An SQL instruction to get or manipulate

Query
data

Databa
A collection of related tables
se

✅ Real-World Use Case Example

Business Question:
What are the names and salaries of all employees in the IT department earning
more than ₦60,000?
SQL Query:
SELECT name, salary
FROM employees
WHERE department = 'IT' AND salary > 60000;
Explanation:
  SELECT name, salary → Only show names and salaries
 FROM employees → From the employees table
 WHERE department = 'IT' → Only IT staff
 AND salary > 60000 → Who earn more than ₦60,000

🧪 Practice Task:
Table: Students

i gend scor
name
d er e

1 Tunde Male 78

Amin Femal
2 92
a e

3 Seyi Male 65

Fatim Femal
4 88
a e

Try Writing These SQL Queries:

1. Show all student names.
2. SELECT name FROM students;
3. Show names and scores of students who scored more than 70.
4. SELECT name, score FROM students WHERE score > 70;
5. Show names of female students only.
6. SELECT name FROM students WHERE gender = 'Female';

Perfect! Let’s move on to:

📘 Lesson 2: Setting Up Your SQL Environment

(Full Course with Deep Explanations & Examples)

✅ Why Set Up an SQL Environment?

Before you can run SQL queries and analyze data, you need a platform that can:
  Store your data (in a database)
 Let you write and execute SQL code
 Show results in an easy-to-read format
There are two main options:
1. Install a local SQL database (on your PC)
2. Use a browser-based SQL playground (no installation)

🔧 Option 1: Use a Free Online SQL Playground (Easiest to Start)

For absolute beginners, online tools are the fastest way to practice SQL without
installing anything.
🔹 Recommended: DB Fiddle
How to use:
1. Go to https://www.db-fiddle.com
2. Select a database engine: e.g., MySQL 8.0 or PostgreSQL 15
3. Paste your SQL code into the editor
4. Click “Build Schema” to set up your table
5. Click “Run” to test your query
✅ Great for beginners and testing small queries.

💻 Option 2: Install SQL Locally (For full offline experience)

If you want a more powerful setup for real projects or big data:
🖥️Install MySQL on Your PC (Free and widely used)
Step-by-step:
1. Go to the official MySQL download page:
👉 https://dev.mysql.com/downloads/installer
2. Download the MySQL Installer for Windows (Community version is free).
3. During installation, select:
o MySQL Server

o MySQL Workbench (graphical interface to write SQL)

4. Follow the setup wizard to:

 o Set a password (remember this)

o Complete installation

5. Open MySQL Workbench

o Connect to your server

o Use the SQL Editor to create tables and write queries

✅ This gives you full control and experience with a real database.

💡 Option 3: Use SQLite (Lightweight Local Setup)

 No server setup required
 Use with browser or desktop apps like DB Browser for SQLite
Website: https://sqlitebrowser.org

🧪 Practice Task
If using DB Fiddle, paste and run this code:
-- Create a simple table
CREATE TABLE students (
id INT,
name VARCHAR(50),
gender VARCHAR(10),
score INT
);

-- Insert some test data

INSERT INTO students VALUES
(1, 'Tunde', 'Male', 78),
(2, 'Amina', 'Female', 92),
(3, 'Seyi', 'Male', 65),
(4, 'Fatima', 'Female', 88);

-- Query to test
SELECT * FROM students;
You should see a table with 4 student records returned.

🧠 Summary

Task Tool Recommendation

Practice quickly online DB-Fiddle or SQLFiddle

Full local SQL Install MySQL + MySQL

environment Workbench

Lightweight local
Use SQLite + DB Browser
practice

✅ Up Next:
Would you like to continue with:
➡️Lesson 3: Basic SQL Queries – SELECT, WHERE, ORDER BY
(Learn how to fetch and filter data properly)
Let me know and I’ll continue building your SQL course!
Great! Let’s dive into:

📘 Lesson 3: Basic SQL Queries — SELECT, WHERE, ORDER BY

(Full Course with Deep Explanations & Examples)

🎯 Objective:
Learn how to write basic SQL queries to:
 View specific data
 Filter records
 Sort results in a meaningful order

✅ 1. The SELECT Statement

Purpose: Retrieve data from a database table.
📌 Syntax:
SELECT column1, column2
FROM table_name;
📍 Example:
Given this table:
students

i gend scor
name
d er e

1 Tunde Male 78

Amin Femal
2 92
a e

3 Seyi Male 65

Fatim Femal
4 88
a e

✅ Examples:
🔹 Select all columns:
SELECT * FROM students;
🔹 Select only names:
SELECT name FROM students;
🔹 Select names and scores:
SELECT name, score FROM students;

✅ 2. The WHERE Clause

Purpose: Filter records based on specific conditions.
📌 Syntax:
SELECT column1, column2
FROM table_name
WHERE condition;
📍 Examples:
🔸 Select students with scores above 80:
SELECT name, score
FROM students
WHERE score > 80;
🔸 Select female students only:
SELECT name
FROM students
WHERE gender = 'Female';
🔸 Select students with score between 70 and 90:
SELECT name, score
FROM students
WHERE score BETWEEN 70 AND 90;

✅ 3. The ORDER BY Clause

Purpose: Sort query results by one or more columns.
📌 Syntax:
SELECT column1, column2
FROM table_name
ORDER BY column_name ASC|DESC;
 ASC = Ascending (default)
 DESC = Descending
📍 Examples:
🔸 Sort students by score (lowest to highest):
SELECT name, score
FROM students
ORDER BY score ASC;
🔸 Sort students by name in descending order:
SELECT name
FROM students
ORDER BY name DESC;
🧪 Practice Exercise
Use this table:

i gend departme salar

name
d er nt y

7000
1 John Male IT
0

Femal 5500
2 Aisha HR
e 0

8000
3 Musa Male IT
0

Chiom Femal 6000

4 Marketing
a e 0

5300
5 David Male HR
0

Write SQL queries for:

1. All employees in HR:
SELECT name, department
FROM employees
WHERE department = 'HR';
2. Employees earning more than 60,000, sorted by salary (descending):
SELECT name, salary
FROM employees
WHERE salary > 60000
ORDER BY salary DESC;
3. Names of all female employees:
SELECT name
FROM employees
WHERE gender = 'Female';

🧠 Summary:
Clause Function Example

Choose which columns to SELECT name FROM

SELECT
show students;

Filter rows based on

WHERE WHERE score > 80
conditions

ORDER
Sort results ORDER BY score DESC
BY

✅ Up Next:
Would you like to proceed to:
➡️Lesson 4: Working with Multiple Tables — JOINs
(How to combine data from different tables using JOIN)
Let me know and I’ll continue building your course!
Awesome! Let's continue your SQL journey with one of the most powerful concepts:

📘 Lesson 4: Working with Multiple Tables — SQL JOINs

(Full Course with Deep Explanations & Examples)

🎯 Objective:
Learn how to combine data from two or more related tables using SQL JOIN
statements — a critical skill for real-world data analysis.

✅ Why Use JOINs?

In real databases, information is stored across multiple related tables. JOINs let
you:
 Connect these tables using relationships (usually primary & foreign keys)
 Analyze combined data (e.g., products & sales, employees & departments)

🔄 Types of SQL JOINs

JOIN Type Description

INNER JOIN Returns matching records only from both tables

JOIN Type Description

Returns all records from the left table and matching ones
LEFT JOIN
from the right

Returns all records from the right table and matching ones
RIGHT JOIN
from the left

Returns all records from both tables (matching + non-

FULL JOIN
matching)

CROSS Returns all possible combinations of both tables (Cartesian

JOIN product)

🔧 Basic JOIN Syntax

SELECT table1.column1, table2.column2
FROM table1
JOIN table2
ON table1.key = table2.key;

🧩 Example Scenario
Table 1: employees

i dept_i
name
d d

1 John 101

Amak
2 102
a

3 Musa 101

Fatim
4 103
a

Table 2: departments

dept_na
id
me

10
IT
1

10 HR
 dept_na
id
me

10
Marketing
3

🔹 INNER JOIN — Match Only

SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.id;
Result:

dept_na
name
me

John IT

Amak
HR
a

Musa IT

Fatim
Marketing
a

✅ Shows only employees with a matching department.

🔹 LEFT JOIN — All from Left Table

SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments
ON employees.dept_id = departments.id;
✅ Includes all employees, even if department info is missing.

🔹 RIGHT JOIN — All from Right Table

SELECT employees.name, departments.dept_name
FROM employees
RIGHT JOIN departments
ON employees.dept_id = departments.id;
✅ Includes all departments, even if no employees are in them.

🔹 FULL JOIN — All Records

Not supported in MySQL, but works in PostgreSQL or SQL Server
SELECT employees.name, departments.dept_name
FROM employees
FULL JOIN departments
ON employees.dept_id = departments.id;
✅ Combines all records from both tables, matching where possible.

🧪 Practice Exercise
Tables:
students

class_i
id name
d

1 Amina 1

2 James 2

Halim
3 1
a

4 Tunde 3

classes

class_na
id
me

1 JSS1

2 JSS2

3 JSS3
Questions:
1. Show student names and their class names.
SELECT students.name, classes.class_name
FROM students
INNER JOIN classes
ON students.class_id = classes.id;
2. List all students, even if their class info is missing (LEFT JOIN).
3. List all classes, even if no students are enrolled (RIGHT JOIN).

🧠 Summary

JOIN
Use When…
Type

INNER
You want only matching records
JOIN

You want all records from the first

LEFT JOIN
table

RIGHT You want all records from the

JOIN second table

You want everything, matched or

FULL JOIN
not

✅ Up Next:
Would you like to continue with:
➡️Lesson 5: Aggregating Data — COUNT, SUM, AVG, GROUP BY
(A must-have for analyzing totals, averages, and category breakdowns)
Let me know and we’ll continue building!
Great choice! Let’s now explore one of the most important parts of data analysis
with SQL:

📘 Lesson 5: Aggregating Data — COUNT, SUM, AVG, GROUP BY

(Full Course with Deep Explanations & Examples)
🎯 Objective:
Learn how to summarize and group data to answer real-world questions like:
 "How many students are in each class?"
 "What is the total or average sales per region?"

✅ 1. What Is Aggregation?
Aggregation means combining data values to return a single result — such as a
total, count, or average.
SQL has built-in aggregate functions to do this.

✅ 2. Common Aggregate Functions

Functio
Purpose Example
n

COUNT( Count rows or

COUNT(*)
) values

SUM(salar
SUM() Add all values
y)

Calculate average AVG(score

AVG()
value )

MAX(salar
MAX() Find highest value
y)

MIN() Find lowest value MIN(score)

✅ 3. Basic Examples (One Table)

Table: students

i gend scor
name
d er e

1 Tunde Male 78

Amin Femal
2 92
a e

3 Seyi Male 65

4 Fatim Femal 88
i gend scor
name
d er e

a e

📌 Examples:
🔸 Count total students:
SELECT COUNT(*) FROM students;
🔸 Find average score:
SELECT AVG(score) FROM students;
🔸 Total of all scores:
SELECT SUM(score) FROM students;
🔸 Highest score:
SELECT MAX(score) FROM students;

✅ 4. GROUP BY — Group Data Before Aggregating

To summarize data by category (e.g., by gender, class, department), use GROUP
BY.
📌 Syntax:
SELECT column, AGGREGATE_FUNCTION(column)
FROM table
GROUP BY column;

📍 Example: Average score by gender

SELECT gender, AVG(score)
FROM students
GROUP BY gender;
Result:

gend
avg
er

Male 71.
gend
avg
er

Femal 90.
e 0

📍 Example: Total score by student

SELECT name, SUM(score)
FROM students
GROUP BY name;

✅ 5. HAVING — Filter Groups (like WHERE for groups)

You cannot use WHERE to filter aggregate results. Instead, use HAVING.
📍 Example: Show only genders where average score > 75
SELECT gender, AVG(score) AS avg_score
FROM students
GROUP BY gender
HAVING AVG(score) > 75;

🧪 Practice Exercise
Table: sales

produ regio amou

id
ct n nt

15000
1 Rice North
0

12000
2 Beans South
0

10000
3 Rice South
0

13000
4 Beans North
0

5 Yam South 90000

 produ regio amou
id
ct n nt

Write SQL queries to:

1. Count the number of sales records.
SELECT COUNT(*) FROM sales;
2. Total sales for each product.
SELECT product, SUM(amount)
FROM sales
GROUP BY product;
3. Average sales amount per region.
SELECT region, AVG(amount)
FROM sales
GROUP BY region;
4. Show only products with total sales above ₦200,000.
SELECT product, SUM(amount) AS total_sales
FROM sales
GROUP BY product
HAVING SUM(amount) > 200000;

🧠 Summary

Concept Use It For…

COUNT() Count number of rows

SUM() Add all values

AVG() Find average value

GROUP
Summarize data by category
BY

Filter grouped (aggregated)

HAVING
results
✅ Up Next:
Would you like to continue with:
➡️Lesson 6: Filtering and Searching Data — LIKE, IN, BETWEEN, NULL
Let me know and we’ll keep going!
Awesome! Let’s continue your SQL journey with:

📘 Lesson 6: Filtering and Searching Data — LIKE, IN, BETWEEN, IS NULL

(Full Course with Deep Explanations & Examples)

🎯 Objective:
Learn how to search and filter your data with flexible and powerful SQL tools. This
lesson focuses on:
 Pattern matching
 Searching specific values
 Filtering value ranges
 Dealing with missing data

✅ 1. The LIKE Operator (Pattern Matching)

Used to search for a specific pattern in text-based columns.
🔹 Syntax:
SELECT column1
FROM table
WHERE column1 LIKE 'pattern';
🧩 Pattern Wildcards:

Symb
Meaning
ol

Matches any number of characters

%
(including 0)

_ Matches exactly one character

📍 Examples:
Table: employees

departme
id name
nt

1 Tunde IT

2 Amaka HR

Blessin
3 Marketing
g

4 Ayo IT

5 Fatima HR

🔸 Find names that start with “A”:

SELECT name
FROM employees
WHERE name LIKE 'A%';
🔸 Find names ending with “a”:
SELECT name
FROM employees
WHERE name LIKE '%a';
🔸 Find names containing “ess”:
SELECT name
FROM employees
WHERE name LIKE '%ess%';

✅ 2. The IN Operator (List Match)

Used to match multiple values in a column.
🔹 Syntax:
SELECT column1
FROM table
WHERE column1 IN (value1, value2, ...);
📍 Example:
🔸 Find employees in HR or Marketing:
SELECT name, department
FROM employees
WHERE department IN ('HR', 'Marketing');

✅ 3. The BETWEEN Operator (Range Filtering)

Used to filter values within a range (inclusive).
🔹 Syntax:
SELECT column
FROM table
WHERE column BETWEEN value1 AND value2;
📍 Example:
Table: students

i scor
name
d e

Amin
1 92
a

2 Tunde 78

3 Seyi 65

Fatim
4 88
a

🔸 Find students who scored between 70 and 90:

SELECT name, score
FROM students
WHERE score BETWEEN 70 AND 90;

✅ 4. The IS NULL & IS NOT NULL Operators

Used to check for missing or empty values.
📍 Example:
Table: payments
i custom amou
d er nt

1 John 50000

2 Amaka NULL

3 Musa 65000

4 Chioma NULL

🔸 Find customers with missing payment amounts:

SELECT customer
FROM payments
WHERE amount IS NULL;
🔸 Find customers who made a payment:
SELECT customer, amount
FROM payments
WHERE amount IS NOT NULL;

🧪 Practice Exercise
Table: products

i
name category price
d

1 Rice Food 2000

Electronic
2 Fan 7500
s

3 Beans Food 1800

Freez Electronic 5000

4
er s 0

5 Soap Toiletries NULL

Write queries for:

1. Products that belong to Food or Toiletries categories:
SELECT name, category
FROM products
WHERE category IN ('Food', 'Toiletries');
2. Products with price between ₦2,000 and ₦10,000:
SELECT name, price
FROM products
WHERE price BETWEEN 2000 AND 10000;
3. Products with NULL prices:
SELECT name
FROM products
WHERE price IS NULL;
4. Products whose names end with the letter "r":
SELECT name
FROM products
WHERE name LIKE '%r';

🧠 Summary

Keywor
Use For...
d

Pattern matching with %

LIKE
and _

Checking if a value exists in

IN
a list

BETWEE Filtering between two

N values

Checking for missing (null)

IS NULL
data

✅ Up Next:
Would you like to continue with:
➡️Lesson 7: Modifying Data — INSERT, UPDATE, DELETE
Let me know and we’ll keep building your SQL data analysis course!
Great! Let’s move ahead with:
📘 Lesson 7: Modifying Data — INSERT, UPDATE, DELETE
(Full Course with Deep Explanations & Examples)

🎯 Objective:
Learn how to add, change, and remove data in a database — skills essential for
working with real datasets during data cleaning, updates, or correction.

✅ 1. INSERT — Add New Records

Used to insert one or more new rows into a table.
🔹 Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

📍 Example:
Table: students

nam gend scor

id
e er e

Amin Femal
1 92
a e

Tund
2 Male 78
e

🔸 Add a new student:

INSERT INTO students (id, name, gender, score)
VALUES (3, 'Seyi', 'Male', 65);
✅ Now Seyi will be added to the table.

✅ 2. UPDATE — Change Existing Data

Used to modify existing records.
🔹 Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
⚠️Always use a WHERE clause! Otherwise, all rows will be updated.

📍 Example:
🔸 Update Tunde’s score to 80:
UPDATE students
SET score = 80
WHERE name = 'Tunde';
🔸 Increase all scores by 5 points:
UPDATE students
SET score = score + 5;

✅ 3. DELETE — Remove Records

Used to remove one or more rows from a table.
🔹 Syntax:
DELETE FROM table_name
WHERE condition;
⚠️Without a WHERE clause, all rows will be deleted!

📍 Example:
🔸 Delete Seyi from the table:
DELETE FROM students
WHERE name = 'Seyi';

🔒 Bonus Tip: Use SELECT Before UPDATE or DELETE

To avoid accidental data loss, always:
SELECT * FROM table_name WHERE condition;
...before running an UPDATE or DELETE to confirm you're targeting the right rows.
🧪 Practice Exercise
Table: products

nam pric
id category
e e

200
1 Rice Food
0

Electronic 750
2 Fan
s 0

3 Soap Toiletries 500

1. Add a new product: "Toothpaste", "Toiletries", ₦800

INSERT INTO products (id, name, category, price)
VALUES (4, 'Toothpaste', 'Toiletries', 800);
2. Change the price of "Fan" to ₦7000
UPDATE products
SET price = 7000
WHERE name = 'Fan';
3. Remove the product "Soap"
DELETE FROM products
WHERE name = 'Soap';

🧠 Summary

Comman
Action
d

Adds new rows to a

INSERT
table

UPDATE Modifies existing rows

Removes rows from a

DELETE
table

✅ Always test your queries with SELECT first!

✅ Up Next:
Would you like to proceed to:
➡️Lesson 8: Advanced Querying — Subqueries, Aliases, and Calculated
Columns
Let me know and we’ll keep building!
Excellent! You’re moving steadily. Let's now explore:

📘 Lesson 8: Advanced Querying — Subqueries, Aliases, and Calculated

Columns
(Full Course with Deep Explanations & Examples)

🎯 Objective:
Level up your SQL analysis by learning how to:
 Write queries inside other queries (subqueries)
 Rename columns or tables temporarily (aliases)
 Perform calculations in queries (calculated columns)
These tools are essential for writing clean, powerful, and optimized SQL code.

✅ 1. Aliases (AS) — Rename Columns or Tables Temporarily

🔹 Syntax:
SELECT column_name AS alias_name
FROM table_name AS table_alias;

📍 Examples:
🔸 Rename column for clarity:
SELECT name AS student_name, score AS final_score
FROM students;
🔸 Rename table for shorter syntax (useful in JOINs):
SELECT s.name, c.class_name
FROM students AS s
JOIN classes AS c ON s.class_id = c.id;
✅ Aliases help you write cleaner, more readable, and shorter code.

✅ 2. Calculated Columns — Do Math Inside a Query

You can add, subtract, divide, multiply columns right inside a SELECT query.

📍 Examples:
Table: sales

produ pric quanti

id
ct e ty

200
1 Rice 5
0

150
2 Beans 3
0

250
3 Yam 2
0

🔸 Calculate total sales (price × quantity):

SELECT product, price * quantity AS total_sales
FROM sales;
🔸 Add a 5% tax to price:
SELECT product, price, price * 1.05 AS price_with_tax
FROM sales;
✅ Calculated columns are excellent for reporting, forecasting, or dashboards.

✅ 3. Subqueries (Nested Queries) — Query Inside a Query

Subqueries allow you to:
 Get values to use in another query
 Filter based on aggregated or derived data
 Break complex queries into manageable parts

🔹 Syntax:
SELECT column
FROM table
WHERE column = (SELECT column FROM other_table WHERE condition);

📍 Example 1: Find students with the highest score

SELECT name, score
FROM students
WHERE score = (SELECT MAX(score) FROM students);
✅ The inner query gets the highest score.
✅ The outer query finds students with that score.

📍 Example 2: Show products more expensive than the average price

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
✅ Subqueries are great for comparing against summarized values.

🧪 Practice Task
Table: employees

dept_i salar
id name
d y

7000
1 John 101
0

Amak 5500
2 102
a 0

8000
3 Musa 101
0

Fatim 6000
4 103
a 0

Table: departments

dept_na
id
me

10 IT
 dept_na
id
me

10
HR
2

10
Marketing
3

Try These Queries:

1. Show each employee’s name and their salary after a 10% bonus:
SELECT name, salary, salary * 1.1 AS salary_with_bonus
FROM employees;
2. Show employees earning more than the average salary:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
3. Show employee names and their department names using aliases:
SELECT e.name AS employee, d.dept_name AS department
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

🧠 Summary

Feature Purpose

AS (Alias) Rename columns/tables for clarity

Calculated Create new values on the fly in

columns queries

Use one query result inside another

Subqueries
query

✅ Up Next:
Would you like to proceed to:
➡️Lesson 9: Data Cleaning and Preparation in SQL — Trimming, Replacing,
Converting, and Handling Duplicates
Let me know and we’ll keep going!
Fantastic! Let’s dive into a critical skill for data analysts:

📘 Lesson 9: Data Cleaning and Preparation in SQL

(Trimming, Replacing, Converting, and Handling Duplicates)
Full course with deep explanations & practical examples

🎯 Objective:
In this lesson, you’ll learn how to clean raw or messy data using SQL — a crucial
step before any serious analysis.
We’ll cover:
 Removing extra spaces (TRIM)
 Replacing values (REPLACE)
 Changing data types (CAST/CONVERT)
 Handling duplicates (DISTINCT)
 Working with NULLs

✅ 1. Removing Extra Spaces — TRIM, LTRIM, RTRIM

Sometimes, data contains unwanted spaces that mess up filters or groupings.
🔹 Syntax:
SELECT TRIM(column) FROM table;
📍 Example:
Table: customers

id name

' Aisha
1
'

'Tunde
2
'

3 ' Musa
id name

'

🔸 Remove spaces around names:

SELECT TRIM(name) AS clean_name
FROM customers;
🧠 Use LTRIM() to remove only left spaces and RTRIM() for right spaces.

✅ 2. Replacing Characters or Words — REPLACE

Used to replace parts of strings.
🔹 Syntax:
SELECT REPLACE(column, 'old', 'new') FROM table;
📍 Example:
Table: products

id name

'Tooth
1
Brush'

'Tooth
2
Paste'

🔸 Replace "Tooth" with "Mouth":

SELECT REPLACE(name, 'Tooth', 'Mouth') AS updated_name
FROM products;

✅ 3. Converting Data Types — CAST() or CONVERT()

Used to change one data type into another (e.g., text to number).
🔹 Syntax:
SELECT CAST(column AS datatype) FROM table;
or
SELECT CONVERT(datatype, column) FROM table;
📍 Example:
Table: orders
 order_amo
id
unt

1 '2500'

2 '4000'

🔸 Convert order_amount from string to integer:

SELECT CAST(order_amount AS INT) AS amount_int
FROM orders;

✅ 4. Removing Duplicate Records — DISTINCT

Returns only unique values.
🔹 Syntax:
SELECT DISTINCT column FROM table;
📍 Example:
Table: students

nam clas
e s

Amin
JSS1
a

Amin
JSS1
a

Musa JSS2

🔸 Get unique students:

SELECT DISTINCT name, class
FROM students;

✅ 5. Handling Missing Values — IS NULL, COALESCE()

🔹 IS NULL: filter nulls
SELECT name FROM customers WHERE phone IS NULL;
🔹 COALESCE(): replace NULL with a default
SELECT name, COALESCE(phone, 'Not Provided') AS phone_number
FROM customers;
✅ COALESCE() shows a fallback value when a column is NULL.

🧪 Practice Task
Table: staff

salar gend
id name
y er

' John '5000

1 Male
' 0'

'Amin Femal
2 NULL
a' e

' Musa '6000

3 NULL
' 0'

'Amin Femal
4 NULL
a' e

Try writing queries to:

1. Remove spaces around names
SELECT TRIM(name) AS clean_name FROM staff;
2. Convert salary to INT
SELECT CAST(salary AS INT) AS salary_int FROM staff;
3. Replace NULL gender with 'Unknown'
SELECT name, COALESCE(gender, 'Unknown') AS gender_status
FROM staff;
4. Get unique staff names and genders
SELECT DISTINCT name, gender FROM staff;

🧠 Summary

Task Tool/Function

TRIM(), LTRIM(),
Remove spaces
RTRIM()

Replace
REPLACE()
characters

Convert data CAST() / CONVERT()

Task Tool/Function

types

Handle
DISTINCT
duplicates

Manage null IS NULL,

values COALESCE()

You got it! Let’s wrap up this beginner SQL data analysis series with exporting and
reporting essentials:

📘 Lesson 10: Exporting and Reporting — Creating Shareable Results from

SQL
(Full Course with Deep Explanations & Examples)

🎯 Objective:
Learn how to prepare, export, and share your SQL query results so others can use
or visualize the data easily.

✅ 1. Exporting SQL Query Results

After running your SQL queries, you often want to export the results to:
 CSV (Comma Separated Values)
 Excel
 PDF
 JSON

🔹 Common Export Methods:

 Using SQL Client Tools:
Tools like MySQL Workbench, pgAdmin, SQL Server Management Studio have
built-in export options after running a query.
 Using SELECT ... INTO OUTFILE in MySQL:
SELECT *
INTO OUTFILE '/path/to/export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM students;
⚠️You need file system permissions to write files on the server for this to work.

✅ 2. Formatting Query Results for Reporting

Use SQL functions to prepare data for reports:
 Concatenate fields: Combine multiple columns
 SELECT CONCAT(name, ' scored ', score, ' points') AS report_line FROM
students;
 Format numbers or dates:
 SELECT FORMAT(salary, 2) AS formatted_salary FROM employees;

✅ 3. Using Views for Reusable Reports

A view is a saved query you can treat like a table.
CREATE VIEW top_students AS
SELECT name, score
FROM students
WHERE score > 85;
Now, you can simply:
SELECT * FROM top_students;

✅ 4. Sharing Results: Common Practices

 Export results as CSV or Excel for sharing with non-technical users.
 Use BI tools like Power BI or Tableau to connect directly to your database.
 Automate exports with scripts or scheduled tasks (advanced).

🧪 Practice Exercise
1. Write a query to select students scoring above 80.
SELECT name, score
FROM students
WHERE score > 80;
2. Export this data using your SQL client (e.g., save as CSV).
3. Create a view named high_achievers with these students:
CREATE VIEW high_achievers AS
SELECT name, score
FROM students
WHERE score > 80;
4. Query the view:
SELECT * FROM high_achievers;

🧠 Summary

Task How-To/Tip

Use client export options or SELECT INTO

Export query results
OUTFILE

Format data for

Use CONCAT(), FORMAT()
reporting

Create reusable
Use CREATE VIEW
reports

Share data Export to CSV/Excel or connect BI tools

🎉 Congratulations!
You’ve completed the beginner’s SQL data analysis course!
Want to:
 Practice real-world projects?
 Explore intermediate SQL topics like window functions, indexes?
 Learn how to use SQL with Python or R?
Just let me know!