Covid-Dataset.sql

create database PortfolioProject;
use PortfolioProject;

/*
Covid 19 Data Exploration 

Skills used: Joins, CTE's, Temp Tables, Windows Functions, Aggregate Functions, Creating Views, Converting Data Types

*/

Select * From portfolioproject.coviddeaths
Where continent is not null 
order by 3,4;


-- Select Data that we are going to be starting with

Select Location, date, total_cases, new_cases, total_deaths, population
From portfolioproject.coviddeaths
Where continent is not null 
order by 1,2;


-- Total Cases vs Total Deaths
-- Shows likelihood of dying if you contract covid in your country

Select Location, date, total_cases,total_deaths, (total_deaths/total_cases)*100 as DeathPercentage
From portfolioproject.coviddeaths
Where location like '%states%'
and continent is not null 
order by 1,2;


-- Total Cases vs Population
-- Shows what percentage of population infected with Covid

Select Location, date, Population, total_cases,  (total_cases/population)*100 as PercentPopulationInfected
From portfolioproject.coviddeaths
#Where location like '%states%'
order by 1,2;


-- Countries with Highest Infection Rate compared to Population

Select Location, Population, MAX(total_cases) as HighestInfectionCount,  Max((total_cases/population))*100 as PercentPopulationInfected
From portfolioproject.coviddeaths
#Where location like '%states%'
Group by Location, Population
order by PercentPopulationInfected desc;


-- Countries with Highest Death Count per Population

Select Location, max(Total_deaths) TotalDeathCount
From portfolioproject.coviddeaths
#Where location like '%states%'
Where continent is not null 
Group by Location
order by TotalDeathCount desc;



-- BREAKING THINGS DOWN BY CONTINENT

-- Showing contintents with the highest death count per population

Select continent, MAX(Total_deaths) TotalDeathCount
From portfolioproject.coviddeaths
#Where location like '%states%'
Where continent is not null 
Group by continent
order by TotalDeathCount desc;



-- GLOBAL NUMBERS

Select date, SUM(new_cases) total_cases, SUM(new_deaths) total_deaths, SUM(new_deaths)/SUM(New_Cases)*100 DeathPercentage
From portfolioproject.coviddeaths
#Where location like '%states%'
where continent is not null 
Group By date
order by 1,2;



-- Total Population vs Vaccinations
-- Shows Percentage of Population that has recieved at least one Covid Vaccine

select dea.continent, dea.location, dea.date, dea.population, 
vac.new_vaccinations, sum(vac.new_vaccinations) 
OVER (Partition by dea.Location Order by dea.location, dea.Date) as RollingPeopleVaccinated
from
portfolioproject.coviddeaths dea
join
portfolioproject.covidvaccinations vac
on dea.location=vac.location
and dea.date=vac.date
where dea.continent is not null
order by 2,3;



-- Using CTE to perform Calculation on Partition By in previous query

With PopvsVac (Continent, Location, Date, Population, New_Vaccinations, RollingPeopleVaccinated)
as
(
select dea.continent, dea.location, dea.date, dea.population, 
vac.new_vaccinations, sum(vac.new_vaccinations) 
OVER (Partition by dea.Location Order by dea.location, dea.Date) as RollingPeopleVaccinated
from
portfolioproject.coviddeaths dea
join
portfolioproject.covidvaccinations vac
on dea.location=vac.location
and dea.date=vac.date
where dea.continent is not null
#order by 2,3
)


Select *, (RollingPeopleVaccinated/Population)*100
From PopvsVac;



-- Using Temp Table to perform Calculation on Partition By in previous query

DROP Table if exists PercentPopulationVaccinated;
Create Temporary Table PercentPopulationVaccinated
(
Continent nvarchar(255),
Location nvarchar(255),
Date datetime,
Population numeric,
New_vaccinations numeric,
RollingPeopleVaccinated numeric
);

Insert into PercentPopulationVaccinated
select dea.continent, dea.location, dea.date, dea.population, 
vac.new_vaccinations, sum(vac.new_vaccinations) 
OVER (Partition by dea.Location Order by dea.location, dea.Date) as RollingPeopleVaccinated
from
portfolioproject.coviddeaths dea
join
portfolioproject.covidvaccinations vac
on dea.location=vac.location
and dea.date=vac.date
where dea.continent is not null
#order by 2,3
;

Select *, (RollingPeopleVaccinated/Population)*100
From PercentPopulationVaccinated;



-- Creating View to store data for later visualizations

Create View PercentPopulationVaccinated as
select dea.continent, dea.location, dea.date, dea.population, 
vac.new_vaccinations, sum(vac.new_vaccinations) 
OVER (Partition by dea.Location Order by dea.location, dea.Date) as RollingPeopleVaccinated
from
portfolioproject.coviddeaths dea
join
portfolioproject.covidvaccinations vac
on dea.location=vac.location
and dea.date=vac.date
where dea.continent is not null
#order by 2,3
;