-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQLQuery2.sql
151 lines (119 loc) · 4.99 KB
/
SQLQuery2.sql
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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
select *
from Portofolio_project..Covid_death
order by 3,4
--select *
--from Portofolio_project..Covid_vaccination
--order by 3,4
--Data selection
select Location, date, total_cases, new_cases, total_deaths, population
from Portofolio_project..Covid_death
order by 1,2
--Total cases vs Total deaths
--Data Type convertion...
alter table Portofolio_project..Covid_death alter column total_cases FLOAT
alter table Portofolio_project..Covid_death alter column total_deaths FLOAT
select Location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 as DeathPercentage
from Portofolio_project..Covid_death
where location='Cameroon'
order by 1,2
--Total cases vs Population
select Location, date, population, total_cases, (total_deaths/population)*100 as InfectionPercentage
from Portofolio_project..Covid_death
where location='Cameroon'
order by 1,2
--Highest infection rate with respect to each country
select Location, population, max(total_cases) as HighestInfectionCount, max((total_deaths/population)*100) as HighestInfectionRate
from Portofolio_project..Covid_death
--where location='Cameroon'
group by Location, population
order by HighestInfectionRate desc
--Highest death rate with respect to pop
select Location, max(total_deaths) as HighestDeathCount
from Portofolio_project..Covid_death
--where location='Cameroon'
group by Location
order by HighestDeathCount desc
-- By continent
-- showing continents with the highest death count per population
select continent, max(total_deaths) as HighestDeathCount
from Portofolio_project..Covid_death
where continent is not null
group by continent
order by HighestDeathCount desc
-- GLOBAL NUMBERS
--select date, sum(new_cases) as GlobalCasesNumber, sum(cast(new_deaths as int)) as GlobalDeathsNumber
--(sum(new_cases)/sum(cast(new_deaths as int)))*100 as GlobalDeathsPercentage
--from Portofolio_project..Covid_death
--where new_deaths is not null and continent is not null
----where continent is not null
--group by date
--order by 2,3
select date, GlobalCasesNumber, GlobalDeathsNumber, (GlobalDeathsNumber/GlobalCasesNumber)*100 as GlobalDeathsPercentage
from (
select date, sum(new_cases) as GlobalCasesNumber, sum(cast(new_deaths as int)) as GlobalDeathsNumber
from Portofolio_project..Covid_death
--where new_deaths is not null and continent is not null
where continent is not null
group by date
) as TempTable
where GlobalCasesNumber is not null
--order by GlobalDeathsPercentage desc
alter table Portofolio_project..Covid_vaccination alter column new_vaccinations FLOAT
-- Total population vs Vaccination
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 RolliingPeopleVaccinated
from Portofolio_project..Covid_death as dea
join Portofolio_project..Covid_vaccination as vac
on dea.location = vac.location
and dea.date = vac.date
where dea.continent is not null
order by 2,3
--USE CTE
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 Portofolio_project..Covid_death as dea
join Portofolio_project..Covid_vaccination as 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
-- TEMP TABLE
Drop table if exists #PercentPopulationVaccinated
create 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 Portofolio_project..Covid_death as dea
join Portofolio_project..Covid_vaccination as 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
-- View creation for later usage
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 Portofolio_project..Covid_death as dea
join Portofolio_project..Covid_vaccination as vac
on dea.location = vac.location
and dea.date = vac.date
where dea.continent is not null
--order by 2,3
select *
from PercentPopulationVaccinated