Data Exploration with SQL: Covid 19

Data source: Our World in Data

SQL data exploration using SSMS

-- Requirements --
SQL Server Management Studio Management Studio 19

After downloading data. Split the data into Covid Deaths and Covid Vaccinations. This helps us ease the sql query writing.

select population from dbo.coviddeath
select population from dbo.covidvacination

-- select only important info from coviddeath table --
select location, date, total_cases, new_cases,total_deaths, population 
from dbo.coviddeath
order by 1,2

-- Deathpercentage --
select 
  location, 
  date, 
  total_cases, 
  total_deaths, 
  population , 
  (total_deaths/total_cases)*100 as deathpercentage
from dbo.coviddeath 
order by 1,2

-- Death vs Population percentage --
select 
  location,
  date, 
  total_cases, 
  total_deaths, 
  population, 
  (total_deaths/population)*100 as deathpercentagepopulation
from dbo.coviddeath 
order by 1,2 

-- Total Infections, Total Deaths, Infection Rate, Death Rate vs Population
select 
  location, 
  population,
  max(total_cases) as highestinfectioncount, 
  max(total_deaths) as highestdeathcount,
  max((total_cases/population))*100  as casespercentagepopulation,
  max((total_deaths/population))*100  as deathspercentagepopulation
from dbo.coviddeath where continent is not null 
group by location, population
order by 3 desc

Sorted by total Infection

Sorted by Infection Rate

--Death On Daily Basis by Coutry--
select 
  location,
  date,
  sum(new_cases) as total_cases, 
  sum(new_deaths) as Totatdeaths,
  sum(new_deaths)/sum(new_cases)*100 as deathpercentage
from dbo.coviddeath 
where continent is not null and new_cases!=0 
group by location,date
order by 1,2 asc

-- Create a View to utilise view in visualisation --
CREATE VIEW dbo.coviddatabylocationanddate
AS
select 
  location,
  date,
  sum(new_cases) as total_cases, 
  sum(new_deaths) as Totatdeaths,
  sum(new_deaths)/sum(new_cases)*100 as deathpercentage
from dbo.coviddeath 
where continent is not null and new_cases!=0 
group by location,date

select * from dbo.coviddeathbylocationanddate order by 1,2 asc

-- results same as above image -- 

-- Join Covid Death and Covid Vaccination Tables --
select * 
from coviddeath cd
join covidvacination cv
on cd.date=cv.date and cd.location=cv.location
order by 3, 4

-- Create View for Covid Vaccination by Location and date --
CREATE VIEW dbo.covidvacinationbylocationanddate
AS
select 
  cd.continent,
  cd.location,
  cd.date, 
  cd.population, 
  cv.new_vaccinations ,
  sum(cv.new_vaccinations) 
    over 
    (partition by cd.location order by cd.location,cd.date)
    as vacinationcount,
  sum(cv.new_vaccinations) 
    over 
    (partition by cd.location order by cd.location,cd.date)/cd.population*100 
    as vacinationpercentage
from coviddeath cd
join covidvaccination cv
on cd.date=cv.date and cd.location=cv.location
where cd.continent is not null 

------------------------------------------------------------
select * from dbo.covidvaccinationbylocationanddate 

-- Results in incremental increase in vaccination count