CovidSQL

SELECT *
From PortfolioProject1..CovidDeath
order by 3,4

-- Select *
-- From PortfolioProject1..CovidVaccination
-- order by 3,4

SELECT [location], [date], total_cases, new_cases, total_deaths, population
From PortfolioProject1..CovidDeath
order by 1,2

-- Comparing total cases vs total deaths
-- Shows likelihood of death (%) if covid positive in the UK

SELECT [location], [date], total_cases, total_deaths, (total_deaths/total_cases)*100 as DeathPercentage
From PortfolioProject1..CovidDeath
Where location like '%kingdom%'
order by 1,2

-- Comparing total cases vs population
-- Shows percentage of population that got covid

SELECT location, date, total_cases, population, (total_cases/population)*100 as PositivePercentage
From PortfolioProject1..CovidDeath
where location like '%kingdom%'
order by 1,2

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

SELECT location, population, MAX(total_cases) as HighestInfectionCount, Max((total_cases/population))*100 as PositivePercentage
From PortfolioProject1..CovidDeath
Group by [location], population
order by PositivePercentage DESC

-- Showing countries with highest death count per population

SELECT location, MAX(cast(total_deaths as int)) as TotalDeathCount
From PortfolioProject1..CovidDeath
Where continent is not NULL
Group by [location]
order by TotalDeathCount DESC

-- Same but broken down into continents

SELECT continent, MAX(cast(total_deaths as int)) as TotalDeathCount
From PortfolioProject1..CovidDeath
Where continent is not NULL
Group by continent
order by TotalDeathCount DESC

 -- Global Scale

SELECT [date], SUM(new_cases) as TotalCases, SUM(cast(new_deaths as int)) as TotalDeaths, SUM(cast(new_deaths as int))/SUM(new_cases)*100 as DeathPercentage
From PortfolioProject1..CovidDeath
Where continent is not NULL
group by [date]
order by 1,2

-- Death Percentage presently globally

SELECT SUM(new_cases) as TotalCases, SUM(cast(new_deaths as int)) as TotalDeaths, SUM(cast(new_deaths as int))/SUM(new_cases)*100 as DeathPercentage
From PortfolioProject1..CovidDeath
Where continent is not NULL
order by 1,2

-- Comparing total population vs vaccination

SELECT CovidDeath.continent, CovidDeath.location, CovidDeath.date, CovidDeath.population, CovidVaccination.new_vaccinations
, SUM(CovidVaccination.new_vaccinations) OVER (Partition by CovidDeath.location order by CovidDeath.location, Cast(CovidDeath.date as nvarchar(30))) as RollingPeopleVaccinated
FROM PortfolioProject1..CovidDeath
JOIN PortfolioProject1..CovidVaccination
    On CovidDeath.location = CovidVaccination.[location]
    and CovidDeath.date = CovidVaccination.[date]
where CovidDeath.continent is not null
Order by 2,3

-- Use CTE

With PopVsVac (Continent, location, date, population, new_vaccinations, RollingPeopleVaccinated)
AS
(
SELECT CovidDeath.continent, CovidDeath.location, CovidDeath.date, CovidDeath.population, CovidVaccination.new_vaccinations
, SUM(CovidVaccination.new_vaccinations) OVER (Partition by CovidDeath.location order by CovidDeath.location, Cast(CovidDeath.date as nvarchar(30))) as RollingPeopleVaccinated
FROM PortfolioProject1..CovidDeath
JOIN PortfolioProject1..CovidVaccination
    On CovidDeath.location = CovidVaccination.[location]
    and CovidDeath.date = CovidVaccination.[date]
where CovidDeath.continent is not null
)
Select*, (RollingPeopleVaccinated/population)*100
From PopVsVac


-- Temp Table

DROP TABLE if EXISTS #PercentVaccinatedPopulation
Create TABLE #PercentVaccinatedPopulation
(
Continent NVARCHAR(255),
Location NVARCHAR(255),
date nvarchar(50),
Population NUMERIC,
new_vaccinations numeric,
RollingPeopleVaccinated numeric
)

Insert into #PercentVaccinatedPopulation
SELECT CovidDeath.continent, CovidDeath.location, CovidDeath.date, CovidDeath.population, CovidVaccination.new_vaccinations
, SUM(CovidVaccination.new_vaccinations) OVER (Partition by CovidDeath.location order by CovidDeath.location, Cast(CovidDeath.date as nvarchar(30))) as RollingPeopleVaccinated
FROM PortfolioProject1..CovidDeath
JOIN PortfolioProject1..CovidVaccination
    On CovidDeath.location = CovidVaccination.[location]
    and CovidDeath.date = CovidVaccination.[date]
where CovidDeath.continent is not null

Select*, (RollingPeopleVaccinated/population)*100
From #PercentVaccinatedPopulation


-- Create View for future visualisation

CREATE VIEW PercentVaccinatedPopulation AS
SELECT CovidDeath.continent, CovidDeath.location, CovidDeath.date, CovidDeath.population, CovidVaccination.new_vaccinations
, SUM(CovidVaccination.new_vaccinations) OVER (Partition by CovidDeath.location order by CovidDeath.location, Cast(CovidDeath.date as nvarchar(30))) as RollingPeopleVaccinated
FROM PortfolioProject1..CovidDeath
JOIN PortfolioProject1..CovidVaccination
    On CovidDeath.location = CovidVaccination.[location]
    and CovidDeath.date = CovidVaccination.[date]
where CovidDeath.continent is not null

SELECT*
FROM PercentVaccinatedPopulation