Projects_SQL/Covid_Portfolio.sql at main · SunoMath/Projects_SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
--Total number of rows in CovidDeath Table
select *
from CovidDeaths


--Looking at Total cases vs Population
select location,date, Population, total_cases, (total_cases/population)* 100 as Death_Percentage
from CovidDeaths
order by 1, 2 DESC

--Looking at countries with highest infection rate
select location, Population, MAX(total_cases) as HighestInfection_Count, MAX((total_cases/population))* 100 as Percentage_PopulationAffected
from CovidDeaths
Group by location, Population
Order by Percentage_PopulationAffected DESC


--Showing location with the highest death count per population
select location, MAX (CAST(Total_deaths as int)) as TotalDeathCount
from CovidDeaths
where continent is  null
Group By location
order by TotalDeathCount DESC

--Showing continents with the highest death count per population
select continent, MAX (CAST(Total_deaths as int)) as TotalDeathCount
from CovidDeaths
where continent is not null
Group By continent
order by TotalDeathCount 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 DeathPercentage
from CovidDeaths
--Group by date
order by 1 DESC


-- Total Population vs Vaccination
select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
SUM(CONVERT(int,vac.new_vaccinations)) OVER (partition by dea.location order by dea.location, dea.date) as Rolling_Count
from PortfolioProject..CovidDeaths dea
Join PortfolioProject..CovidVaccination vac
 On dea.location = vac.location
 and dea.date = vac.date
where dea.continent is not null
--order by 2,3


-- Using CTE
With PopvsVac (continent, location, date, population, New_vaccinations, Rolling_Count)
as
(
select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
SUM(CONVERT(int,vac.new_vaccinations)) OVER (partition by dea.location order by dea.location, dea.date) as Rolling_Count
from PortfolioProject..CovidDeaths dea
Join PortfolioProject..CovidVaccination vac
 On dea.location = vac.location
 and dea.date = vac.date
where dea.continent is not null
--order by 2,3
)
Select *, (Rolling_Count/population) * 100 as Roll_by_Population
from PopvsVac


--TEMP TABLE
DROP Table if exists #PercentPopulationVaccinated
Create Table #PercentPopulationVaccinated
(
continent nvarchar(255),
location nvarchar(255),
Date datetime,
Population numeric,
New_vaccinations numeric,
Rolling_count numeric
)
Insert into #PercentPopulationVaccinated
select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
SUM(CONVERT(int,vac.new_vaccinations)) OVER (partition by dea.location order by dea.location, dea.date) as Rolling_Count
from PortfolioProject..CovidDeaths dea
Join PortfolioProject..CovidVaccination vac
 On dea.location = vac.location
 and dea.date = vac.date
where dea.continent is not null
--order by 2,3

Select *, (Rolling_count/population) * 100 as Roll_by_Population
from #PercentPopulationVaccinated


--Creating View

Create View PercentPopulationVaccinated as
select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
SUM(CONVERT(int,vac.new_vaccinations)) OVER (partition by dea.location order by dea.location, dea.date) as Rolling_Count
from PortfolioProject..CovidDeaths dea
Join PortfolioProject..CovidVaccination vac
 On dea.location = vac.location
 and dea.date = vac.date
where dea.continent is not null
--order by 2,3


select *
from PercentPopulationVaccinated