Exploratory-Data-Analysis-With-SQL

🦠 COVID-19 Data Analysis Using SQL

A SQL-based exploratory data analysis project focused on analyzing the global impact of COVID-19 through cases, deaths, population, and vaccination data.

The project uses Microsoft SQL Server to transform raw COVID-19 datasets into meaningful insights through data exploration, aggregation, joins, window functions, CTEs, temporary tables, and SQL views.

📌 Project Overview

The COVID-19 pandemic generated large volumes of data across countries and continents.

This project explores that data to answer important analytical questions around:

COVID-19 cases and deaths
Death rates
Infection rates
Population impact
Country and continent comparisons
Global COVID-19 trends
Vaccination progress
Percentage of population vaccinated

The analysis demonstrates practical SQL skills for data cleaning, exploratory analysis, aggregation, relational joins, window functions, and analytical reporting.

🎯 Project Objectives

The main objectives of this project are to:

Explore global COVID-19 case and death data.
Calculate COVID-19 death percentages.
Analyze the likelihood of death after contracting COVID-19.
Identify countries with the highest infection rates.
Compare COVID-19 death counts across countries.
Analyze COVID-19 deaths by continent.
Calculate global daily cases and deaths.
Examine population size against vaccination progress.
Calculate cumulative vaccinations over time.
Determine the percentage of population vaccinated.
Create reusable SQL views for visualization and reporting.

🗂️ Dataset

The project uses two primary datasets:

`CovidDeaths`

Contains information relating to:

Location
Continent
Date
Population
Total cases
New cases
Total deaths
New deaths

`CovidVaccination`

Contains vaccination-related information, including:

Location
Date
New vaccinations

The datasets are joined using:

ON dea.location = vac.location
AND dea.date = vac.date

🛠️ Technology & SQL Skills

Database

Microsoft SQL Server

SQL Techniques Used

SQL Technique	Application
`SELECT`	Data extraction
`WHERE`	Filtering records
`GROUP BY`	Aggregating data
`ORDER BY`	Sorting results
`MAX()`	Identifying highest values
`SUM()`	Calculating totals
`CAST()` / `CONVERT()`	Data type conversion
`ISNULL()`	Handling missing values
`JOIN`	Combining COVID datasets
Window Functions	Cumulative vaccination calculations
CTE	Structuring complex queries
Temporary Tables	Intermediate analysis
SQL Views	Reusable analytical datasets
`ROUND()`	Formatting calculated percentages

🔍 Analysis Performed

1. 📋 Initial Data Exploration

The project begins by examining the COVID deaths dataset and ordering records by location and date.

SELECT *
FROM CovidAnalysis..CovidDeaths
ORDER BY 3, 4;

A more focused view of the data includes:

SELECT
    Location,
    Date,
    total_cases,
    new_cases,
    total_deaths,
    population
FROM CovidAnalysis..CovidDeaths
ORDER BY 1, 2;

This provides an initial understanding of the available variables and their structure.

2. ⚰️ Total Cases vs. Total Deaths

The analysis calculates the percentage of reported cases that resulted in death.

SELECT
    Location,
    Date,
    total_cases,
    total_deaths,
    (total_deaths / total_cases) * 100 AS DeathPercentage
FROM CovidAnalysis..CovidDeaths
ORDER BY 1, 2;

Key Metric

Death Percentage

Total Deaths / Total Cases × 100

This helps assess the relationship between reported infections and reported deaths over time.

3. 🇺🇸 COVID-19 Death Likelihood in the United States

The project also examines the death percentage specifically for the United States.

SELECT
    Location,
    Date,
    Population,
    total_cases,
    total_deaths,
    (total_deaths / total_cases) * 100 AS DeathPercentage
FROM CovidAnalysis..CovidDeaths
WHERE location LIKE '%states%'
ORDER BY 1, 2;

This provides a time-based view of the reported likelihood of death among confirmed cases.

4. 🌍 Countries With the Highest Infection Rates

The project identifies countries with the highest recorded infection levels relative to their populations.

SELECT
    Location,
    Population,
    MAX(total_cases) AS HighestInfectionCount,
    MAX(total_cases / population) * 100 AS PercentPopulationInfected
FROM CovidAnalysis..CovidDeaths
GROUP BY Location, Population
ORDER BY PercentPopulationInfected DESC;

Key Metrics

Highest infection count
Percentage of population infected

This allows countries to be compared based on the relative population impact of COVID-19.

5. 💀 Countries With the Highest Death Counts

The analysis identifies countries with the highest recorded total deaths.

SELECT
    Location,
    MAX(CAST(total_deaths AS INT)) AS TotalDeathCount
FROM CovidAnalysis..CovidDeaths
WHERE continent IS NOT NULL
GROUP BY Location
ORDER BY TotalDeathCount DESC;

This focuses on countries with valid continent classifications to avoid non-country aggregate records.

6. 🌎 COVID-19 Deaths by Continent

The project also aggregates the maximum recorded death count by continent.

SELECT
    Continent,
    MAX(CAST(total_deaths AS INT)) AS TotalDeathCount
FROM CovidAnalysis..CovidDeaths
WHERE continent IS NOT NULL
GROUP BY Continent
ORDER BY TotalDeathCount DESC;

This provides a high-level comparison of COVID-19 mortality across continents.

7. 📈 Global COVID-19 Numbers

Global daily cases and deaths are calculated by aggregating new cases and new deaths.

SELECT
    Date,
    SUM(new_cases) AS TotalCases,
    SUM(CAST(new_deaths AS INT)) AS Total_Deaths,
    SUM(CAST(new_deaths AS INT))
        / SUM(new_cases) * 100 AS DeathPercentage
FROM CovidAnalysis..CovidDeaths
WHERE continent IS NOT NULL
GROUP BY Date
ORDER BY 1, 2;

This creates a global time series showing how COVID-19 cases and deaths evolved.

8. 💉 Population vs. Vaccination

The project combines the COVID deaths and vaccination datasets to analyze vaccination progress.

SELECT
    dea.continent,
    dea.location,
    dea.date,
    dea.population,
    vac.new_vaccinations
FROM CovidAnalysis..CovidDeaths dea
JOIN CovidAnalysis..CovidVaccination$ vac
    ON dea.location = vac.location
    AND dea.date = vac.date
WHERE dea.continent IS NOT NULL
ORDER BY 1, 2, 3;

This demonstrates the use of a relational JOIN to combine datasets using both location and date.

9. 📊 Rolling Vaccination Analysis

A SQL window function is used to calculate cumulative vaccinations for each country.

SUM(
    CONVERT(BIGINT, ISNULL(vac.new_vaccinations, 0))
) OVER (
    PARTITION BY dea.location
    ORDER BY dea.date
    ROWS UNBOUNDED PRECEDING
) AS RollingPeopleVaccinated

This produces a running total of vaccinations over time.

10. 📐 Percentage of Population Vaccinated

The cumulative vaccination figure is compared against population size to calculate vaccination coverage.

ROUND(
    100.0 * RollingPeopleVaccinated / Population,
    2
) AS PercentVaccinated

The resulting metric provides an estimate of the cumulative vaccination percentage by country and date.

11. 🧩 Common Table Expression (CTE)

A Common Table Expression (CTE) is used to organize the rolling vaccination calculation.

WITH PopvsVac AS
(
    SELECT
        dea.continent,
        dea.location,
        dea.date,
        dea.population,
        vac.new_vaccinations,

        SUM(
            CONVERT(BIGINT, ISNULL(vac.new_vaccinations, 0))
        ) OVER (
            PARTITION BY dea.location
            ORDER BY dea.date
            ROWS UNBOUNDED PRECEDING
        ) AS RollingPeopleVaccinated

    FROM CovidAnalysis..CovidDeaths dea

    JOIN CovidAnalysis..CovidVaccination$ vac
        ON dea.location = vac.location
        AND dea.date = vac.date

    WHERE dea.continent IS NOT NULL
)

SELECT
    *,
    ROUND(
        100.0 * RollingPeopleVaccinated / Population,
        2
    ) AS PercentVaccinated
FROM PopvsVac
ORDER BY Location, Date;

The CTE makes the query easier to structure and allows the rolling calculation to be reused in the final query.

12. 🗃️ Temporary Table

The project also demonstrates the use of a SQL Server temporary table:

DROP TABLE IF EXISTS #PercentPopulationVaccinated;

CREATE TABLE #PercentPopulationVaccinated
(
    Continent NVARCHAR(255),
    Location NVARCHAR(255),
    Date DATETIME,
    Population NUMERIC,
    RollingPeopleVaccinated BIGINT
);

The calculated vaccination data is inserted into the temporary table and subsequently queried.

This demonstrates how intermediate analytical results can be stored and processed within a SQL session.

13. 👁️ SQL View for Visualization

A reusable SQL view is created to store the vaccination analysis.

CREATE VIEW PercentPopulationVaccinated AS

SELECT
    dea.continent,
    dea.location,
    dea.date,
    dea.population,

    SUM(
        CONVERT(BIGINT, ISNULL(vac.new_vaccinations, 0))
    ) OVER (
        PARTITION BY dea.location
        ORDER BY dea.date
        ROWS UNBOUNDED PRECEDING
    ) AS RollingPeopleVaccinated

FROM CovidAnalysis..CovidDeaths dea

JOIN CovidAnalysis..CovidVaccination$ vac
    ON dea.location = vac.location
    AND dea.date = vac.date

WHERE dea.continent IS NOT NULL;

The view can then be queried directly:

SELECT
    *,
    ROUND(
        100.0 * RollingPeopleVaccinated / Population,
        2
    ) AS PercentPopulationVaccinated
FROM PercentPopulationVaccinated
ORDER BY Location, Date;

This creates a reusable analytical dataset that can be connected to visualization or reporting tools.

📊 Key Analytical Areas

The project focuses on several major areas:

Analysis	Key Metric
Case Fatality	Death Percentage
Infection Impact	% Population Infected
Country Mortality	Total Death Count
Continental Mortality	Death Count by Continent
Global Trends	Daily Cases & Deaths
Vaccination	New Vaccinations
Cumulative Vaccination	Rolling Vaccinations
Vaccination Coverage	% Population Vaccinated

💡 Business & Analytical Questions

This analysis can be used to investigate questions such as:

What percentage of reported COVID-19 cases resulted in death?
Which countries experienced the highest infection rates relative to population?
Which countries recorded the highest death counts?
Which continents experienced the highest mortality?
How did global COVID-19 cases change over time?
How did vaccination programs progress across countries?
What percentage of a country’s population had received vaccinations over time?
How can SQL window functions be used to calculate cumulative metrics?
How can SQL views support downstream visualization and reporting?

🧠 SQL Concepts Demonstrated

This project demonstrates practical SQL Server skills including:

Data Exploration

SELECT
WHERE
ORDER BY

Aggregation

SUM()
MAX()
GROUP BY

Data Transformation

CAST()
CONVERT()
ISNULL()
ROUND()

Relational Analysis

JOIN

Advanced SQL

WITH ... AS
OVER()
PARTITION BY
ROWS UNBOUNDED PRECEDING

Database Objects

CREATE TABLE
CREATE VIEW
DROP TABLE
DROP VIEW

⚠️ Important Analytical Considerations

The calculated metrics should be interpreted carefully.

Reported Cases and Deaths

The analysis is based on reported COVID-19 data. Differences in testing, reporting practices, definitions, and data completeness can affect comparisons between countries.

Death Percentage

The calculated:

Deaths / Cases × 100

is a reported case-fatality-style measure and should not automatically be interpreted as the true probability that an infected individual will die.

Vaccination Percentage

The cumulative vaccination calculation is based on the available new_vaccinations field. Depending on the source data, vaccination counts may represent doses rather than unique individuals.

📁 Recommended Project Structure

COVID-19-SQL-Analysis/
│
├── README.md
│
├── sql/
│   └── covid_analysis.sql
│
├── data/
│   ├── CovidDeaths.csv
│   └── CovidVaccinations.csv
│
└── outputs/
    └── visualization_data.csv

🚀 How to Run

1. Install SQL Server

Use Microsoft SQL Server and a SQL client such as SQL Server Management Studio (SSMS).

2. Create or Import the Database

Create a database named:

CovidAnalysis

Import the COVID deaths and vaccination datasets into the database.

The queries expect tables similar to:

CovidAnalysis..CovidDeaths
CovidAnalysis..CovidVaccination$

3. Open the SQL Script

Open the project SQL file in SQL Server Management Studio.

4. Run the Queries

Execute the queries sequentially to:

Explore the data.
Analyze cases and deaths.
Calculate infection rates.
Compare countries and continents.
Analyze global trends.
Join vaccination data.
Calculate rolling vaccinations.
Calculate vaccination percentages.
Create temporary analytical tables.
Create the final visualization view.

🔮 Future Improvements

Potential extensions include:

🏁 Conclusion

This project demonstrates how SQL Server can be used to transform raw COVID-19 datasets into meaningful analytical insights.

Through a combination of data exploration, aggregation, joins, window functions, CTEs, temporary tables, and SQL views, the project analyzes the global impact of COVID-19 and vaccination progress.

The project also provides a strong foundation for connecting SQL-based analysis to tools such as Power BI or Tableau for interactive data visualization and business intelligence.

👨‍💻 Author

Kingsley Agbo

Tools: SQL Server · SSMS · SQL · CTEs · Window Functions · Temporary Tables · Views · Data Analysis