Covid 2020 in SQL

Select *

From CovidPortfolioProject..CovidDeaths

Where continent is not null

order by 3,4

--Select *

--From CovidPortfolioProject..CovidVaccinations

--order by 3,4

--Select data that we will be using

Select location, date, total_cases, new_cases, total_deaths, population

From CovidPortfolioProject..CovidDeaths

Where continent is not null

--Looking at Total Cases vs Total Deaths

--Shows the likely hood of dying if you contract covid in your country

Select location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 AS death_percentage

From CovidPortfolioProject..CovidDeaths

Where location like '%states%'

Order by 1,2

--Looking at the total Cases vs Population

--Shows what percentage of population got covid

Select location, date, population, total_cases, (total_cases/population)*100 AS population_infected_percentage

From CovidPortfolioProject..CovidDeaths

Where continent is not null

Order by 1,2

--Looking at countries with higest infection rate compared to population

Select location, population, MAX(total_cases) AS highest_infection_count, MAX((total_cases/population))*100 as

covid_percentage

From CovidPortfolioProject..CovidDeaths

Where continent is not null

Group By location, population

Order by covid_percentage desc

--Showing countries with highest death count per populatoin

Select location, MAX(cast(total_deaths AS int)) AS total_death_count

From CovidPortfolioProject..CovidDeaths

Where continent is not null

Group By location

Order by total_death_count desc

--Let's break things down by continent

--Showing the continents with higest death counts

Select location, MAX(cast(total_deaths AS int)) AS total_death_count

From CovidPortfolioProject..CovidDeaths

Where continent is null

Group By location

Order by total_death_count desc

--Global numbers

Select SUM(new_cases) AS total_cases, SUM(cast(new_deaths AS int)) AS total_deaths, SUM(cast

(new_deaths AS int))/SUM(new_cases)*100 AS death_percentage

From CovidPortfolioProject..CovidDeaths

--Where location like '%states%'

Where continent is not null

--Group By date

Order by 1,2

--Looking at total population vs vaccination

Select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations

, SUM(Cast(vac.new_vaccinations AS int)) OVER (Partition By dea.location Order By dea.location, dea.date) AS

rolling_people_vaccinated

--, (rolling_people_vaccinated/population)*100

From CovidPortfolioProject..CovidDeaths dea

Join CovidPortfolioProject..CovidVaccinations vac

On dea.location = vac.location

and dea.date = vac.date

Where dea.continent is not null

Order by 2,3

--use CTE

With pop_vs_vac (continent, location, date, population, new_vaccinations, rolling_people_vaccinated)

AS

(

Select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations

, SUM(Cast(vac.new_vaccinations AS int)) OVER (Partition By dea.location Order By dea.location, dea.date) AS

rolling_people_vaccinated

--, (rolling_people_vaccinated/population)*100

From CovidPortfolioProject..CovidDeaths dea

Join CovidPortfolioProject..CovidVaccinations vac

On dea.location = vac.location

and dea.date = vac.date

Where dea.continent is not null

--Order by 2,3

)

Select *, (rolling_people_vaccinated/population)*100

From pop_vs_vac

--temp table

Drop Table if exists #percent_population_vaccinated

Create Table #percent_population_vaccinated

(

continent nvarchar(255),

location nvarchar(255),

date datetime,

population numeric,

new_vaccination numeric,

rolling_people_vaccinated numeric

)

Insert Into #percent_population_vaccinated

Select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations

, SUM(Cast(vac.new_vaccinations AS int)) OVER (Partition By dea.location Order By dea.location, dea.date) AS

rolling_people_vaccinated

--, (rolling_people_vaccinated/population)*100

From CovidPortfolioProject..CovidDeaths dea

Join CovidPortfolioProject..CovidVaccinations vac

On dea.location = vac.location

and dea.date = vac.date

--Where dea.continent is not null

--Order by 2,3

Select *, (rolling_people_vaccinated/population)*100

From #percent_population_vaccinated

--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(Cast(vac.new_vaccinations AS int)) OVER (Partition By dea.location Order By dea.location, dea.date) AS

rolling_people_vaccinated

--, (rolling_people_vaccinated/population)*100

From CovidPortfolioProject..CovidDeaths dea

Join CovidPortfolioProject..CovidVaccinations vac

On dea.location = vac.location

and dea.date = vac.date

Where dea.continent is not null

--Order by 2,3