SQLPortfoiloProject/Covid Query.sql at main · Adannee/SQLPortfoiloProject · 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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

Select *
From PortfoiloProject ..CovidDeaths
Where continent is not null
order by 3,4

--Select *
--From PortfoiloProject ..['covid-vac$']
--order by 3,4

-- Select Data
Select location, date, total_cases,new_cases, total_deaths, population
From PortfoiloProject ..CovidDeaths
Where continent is not null
order by 1,2

-- Total Cases vs Total Deaths
Select location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 as DeathPercentage
From PortfoiloProject ..CovidDeaths
Where location = 'Nigeria'
and continent is not null
order by 1,2

-- Total Cases vs Population
Select location, date, population, total_cases, (total_cases/population)*100 as CasesPercentage
From PortfoiloProject ..CovidDeaths
Where location = 'Nigeria'
and continent is not null
order by 1,2

-- Countries with Higest Infection Rate compared to Population
Select location, population, MAX(total_cases) as HighestNoOfCases, MAX((total_cases/population))*100 as CasesPercentage
From PortfoiloProject ..CovidDeaths
--Where location = 'Nigeria'
Where continent is not null
Group by location, population
order by CasesPercentage desc;

-- Countries with the Highest Death Count per Population
Select location,  MAX(cast(total_deaths as int)) as HighestNoOfDeaths
From PortfoiloProject ..CovidDeaths
--Where location = 'Nigeria'
Where continent is not null
Group by location
order by HighestNoOfDeaths desc

-- GROUPING BY CONTINENT WITH HIGHEST DEATH
Select continent,  MAX(cast(total_deaths as int)) as HighestNoOfDeaths
From PortfoiloProject ..CovidDeaths
--Where location = 'Nigeria'
Where continent is not null
Group by continent
order by HighestNoOfDeaths 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 PortfoiloProject ..CovidDeaths
--Where location = 'Nigeria'
Where continent is not null
--Group By date
order by 1,2


-- Total Population vs Vaccinations

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 RollingPeopleVaccinated
--(RollingPeopleVaccinated/dea.population)* 100
From PortfoiloProject ..CovidDeaths dea
Join PortfoiloProject ..['covid-vac'] 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(CONVERT(int, vac.new_vaccinations )) OVER (Partition by dea.location order by dea.location, dea.date) as RollingPeopleVaccinated
-- (RollingPeopleVaccinated/dea.population)* 100
From PortfoiloProject ..CovidDeaths dea
Join PortfoiloProject ..['covid-vac'] vac
    On dea.location = vac.location
	and dea.date = vac.date
Where dea.continent is not null
--order by 2,3
)
Select *, (RollingPeopleVaccinated/Population)
From PopvsVac

-- TEMP TABLE

DROP table if exists #PercentPopulatingVaccisoned
Create Table #PercentPopulatingVaccisoned
(continent nvarchar(255),
location nvarchar(255),
Date datetime,
Population numeric,
new_vaccination numeric,
RollingPeopleVaccinated numeric
)


Insert into #PercentPopulatingVaccisoned
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 RollingPeopleVaccinated
--, (RollingPeopleVaccinated/population)* 100
From PortfoiloProject ..CovidDeaths dea
Join PortfoiloProject ..['covid-vac'] 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 #PercentPopulatingVaccisoned

-- Creating View to store data

Create View PercentPopulatingVaccisoned 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 RollingPeopleVaccinated
--, (RollingPeopleVaccinated/population)* 100
From PortfoiloProject ..CovidDeaths dea
Join PortfoiloProject ..['covid-vac'] vac
    On dea.location = vac.location
	and dea.date = vac.date
Where dea.continent is not null
--order by 2,3