PROJ_01_COVID 19 DATA EXPLORATION.sql

/*

COVID 19 DATA EXPLORATION 
SkillS USED: JOINS, CTE's, TEMP TABLES, WINDOWS FUNCTIONS, AGGREGATE FUNCTIONS, VIEWS, STORED PROCEDURES, DATA TYPES MODIFICATION

*/



-- EXPLORING THE CONTENTS OF THE TABLE DATA

SELECT * FROM [dbo].[coviddeaths]
WHERE CONTINENT IS NOT NULL
ORDER BY LOCATION, DATE

SELECT * FROM [dbo].[covidvaccinations]
WHERE CONTINENT IS NOT NULL
ORDER BY LOCATION, DATE



-- SELECTING ALL THE NECESSARY COLUMNS

SELECT LOCATION, DATE, TOTAL_CASES, NEW_CASES, TOTAL_DEATHS, POPULATION FROM [dbo].[coviddeaths]
WHERE CONTINENT IS NOT NULL
ORDER BY LOCATION, DATE



-- SHOWING THE CHANCE OF GETTING AFFECTED BY COVID IN MY COUNTRY (NEW_CASES vs TOTAL_CASES)

SELECT LOCATION, DATE, TOTAL_CASES, NEW_CASES, (NEW_CASES/TOTAL_CASES)*100 AS [P.O.CHANCES] FROM [dbo].[coviddeaths]
WHERE LOCATION LIKE 'BANGLADESH' 
ORDER BY LOCATION, DATE



-- SHOWING THE PERCENTAGE OF PEOPLE AFFECTED BY COVID AROUND THE WORLD UNTIL TODAY (TOTAL CASES VS POPULATION)

SELECT LOCATION, DATE, POPULATION, TOTAL_CASES, (TOTAL_CASES/POPULATION)*100 AS [P.O.AFFECTED] FROM [dbo].[coviddeaths]
ORDER BY LOCATION, DATE



-- COUNTRIES WITH HIGHEST INFECTED CASES COMPARED TO POPULATION

SELECT LOCATION, POPULATION, MAX(TOTAL_CASES) AS HIGHEST_INF_COUNT, MAX((TOTAL_CASES/POPULATION))*100 AS [P.O.INFECTED] FROM [dbo].[coviddeaths]
GROUP BY LOCATION, POPULATION
ORDER BY [P.O.INFECTED] DESC



-- COUNTRIES WITH HIGHEST DEATH COUNT PER POPULATION

SELECT LOCATION, MAX(CAST(TOTAL_DEATHS AS INT)) AS TOTALDEATHS
FROM [dbo].[coviddeaths]
WHERE CONTINENT IS NOT NULL
GROUP BY LOCATION
ORDER BY TOTALDEATHS DESC



-- SHOWING CONTINENTS WITH HIGHEST DEATH COUNT PER POPULATION

SELECT CONTINENT, MAX(CAST(TOTAL_DEATHS AS INT)) AS TOTALDEATHS
FROM [dbo].[coviddeaths]
WHERE CONTINENT IS NOT NULL
GROUP BY CONTINENT
ORDER BY TOTALDEATHS DESC



-- SHOWING TOTAL CASES, DEATHS AND DEATHS COMPARED TO CASES IN GLOBAL NUMBERS

SELECT SUM(NEW_CASES) as TOTAL_CASES, SUM(CAST(NEW_DEATHS AS INT)) AS TOTALDEATHS, SUM(CAST(NEW_DEATHS AS INT))/SUM(NEW_CASES)*100 as [P.O.DEATHS]
FROM [dbo].[coviddeaths]
WHERE CONTINENT IS NOT NULL



-- SHOWING THE PERCENTAGE OF VACCINES GIVEN TO PEOPLE COMPARED TO EACH POPULTION COUNTRYWISE

SELECT A.CONTINENT, A.LOCATION, A.DATE, A.POPULATION, B.NEW_VACCINATIONS,
SUM(CAST(B.NEW_VACCINATIONS AS INT)) OVER (PARTITION BY A.LOCATION ORDER BY A.LOCATION, A.DATE) AS TOT_VACC_PER_LOC
FROM [dbo].[coviddeaths] A JOIN [dbo].[covidvaccinations] B 
ON A.LOCATION = B.LOCATION AND A.DATE = B.DATE
WHERE A.CONTINENT IS NOT NULL
ORDER BY A.LOCATION, A.DATE



-- SHOWING THE ABOVE QUERY USING CTE

WITH VACCvsPOP (CONTINENT, LOCATION, DATE, POPULATION, NEW_VACCINATIONS, TOT_VACC_PER_LOC)
as
(
SELECT A.CONTINENT, A.LOCATION, A.DATE, A.POPULATION, B.NEW_VACCINATIONS,
SUM(CONVERT(INT,B.NEW_VACCINATIONS)) OVER (PARTITION BY A.LOCATION ORDER BY A.LOCATION, A.DATE) AS TOT_VACC_PER_LOC
FROM [dbo].[coviddeaths] A JOIN [dbo].[covidvaccinations] B 
ON A.LOCATION = B.LOCATION AND A.DATE = B.DATE
WHERE A.CONTINENT IS NOT NULL
)
SELECT *, (TOT_VACC_PER_LOC/POPULATION)*100 AS PERC_TOT_VACC_PER_LOC
FROM VACCvsPOP




-- SHOWING THE ABOVE QUERY USING TEMP TABLES

DROP TABLE #PREC_POP_VACC
CREATE TABLE #PREC_POP_VACC
(
CONTINENT NVARCHAR(235),
LOCATION NVARCHAR(235),
DATE DATETIME,
POPULATION NUMERIC,
NEW_VACCINATIONS NUMERIC,
TOT_VACC_PER_LOC NUMERIC
)

INSERT INTO #PREC_POP_VACC

SELECT A.CONTINENT, A.LOCATION, A.DATE, A.POPULATION, B.NEW_VACCINATIONS,
SUM(CONVERT(INT,B.NEW_VACCINATIONS)) OVER (PARTITION BY A.LOCATION ORDER BY A.LOCATION, A.DATE) AS TOT_VACC_PER_LOC
FROM [dbo].[coviddeaths] A JOIN [dbo].[covidvaccinations] B 
ON A.LOCATION = B.LOCATION AND A.DATE = B.DATE
WHERE A.CONTINENT IS NOT NULL

Select *, (TOT_VACC_PER_LOC/POPULATION)*100 AS PERC_TOT_VACC_PER_LOC
From #PREC_POP_VACC




-- CREATING A VIEW FOR FUTURE VISUALIZATIONS

CREATE VIEW POP_VACC_PERC 

AS

SELECT A.CONTINENT, A.LOCATION, A.DATE, A.POPULATION, B.NEW_VACCINATIONS,
SUM(CONVERT(INT,B.NEW_VACCINATIONS)) OVER (PARTITION BY A.LOCATION ORDER BY A.LOCATION, A.DATE) AS TOT_VACC_PER_LOC
FROM [dbo].[coviddeaths] A JOIN [dbo].[covidvaccinations] B 
ON A.LOCATION = B.LOCATION AND A.DATE = B.DATE
WHERE A.CONTINENT IS NOT NULL

SELECT * FROM POP_VACC_PERC



-- STORING A QUERY AS A STORED PROCEDURE

CREATE PROCEDURE SP_POP_VACC_PERC

AS

SELECT A.CONTINENT, A.LOCATION, A.DATE, A.POPULATION, B.NEW_VACCINATIONS,
SUM(CONVERT(INT,B.NEW_VACCINATIONS)) OVER (PARTITION BY A.LOCATION ORDER BY A.LOCATION, A.DATE) AS TOT_VACC_PER_LOC
FROM [dbo].[coviddeaths] A JOIN [dbo].[covidvaccinations] B 
ON A.LOCATION = B.LOCATION AND A.DATE = B.DATE
WHERE A.CONTINENT IS NOT NULL
SELECT * FROM POP_VACC_PERC


EXEC SP_POP_VACC_PERC