MySQL Codes

All examples on this page use data from the website ourworldindata.org

inflation project

CHECKING THE SUM OF INFLATION IN EACH YEAR FROM 2017 TO 2022

SELECT SUM(Y2017) AS totalY2017, SUM(Y2018) AS totalY2018, SUM(Y2019) AS totalY2019, SUM(Y2020) AS totalY2020, SUM(Y2021) AS totalY2021, SUM(Y2022) AS totalY2022
FROM portfolio_project.global_inflation
WHERE Country IS NOT NULL
GROUP BY country
ORDER BY country ASC;

AVERAGE INFLATION RATES FROM 2017 AND 2022

Shows likelihood of increasing inflation

SELECT country, AVG(Y2017) AS AvgInflationPercentage2017, AVG(Y2022) AS AvgInflationrate2022
FROM portfolio_project.global_inflation
WHERE Y2022 IS NOT NULL
GROUP BY Country
ORDER BY 2 DESC
LIMIT 20;

Results of the statement: Data grid

sum of inflation in 2022

SELECT country, SUM(Y2022) AS TOTALY2022
FROM portfolio_project.global_inflation
GROUP BY Country
ORDER BY TOTALY2022;

Results of the statement: Data grid


	-- CHECKING THE AVERAGE INFLATION RATE  OF EACH YEAR

	-- COMMAND ----------
	SELECT country, AVG(Y2017) as AverageInfaltion2017
	FROM portfolio_project.global_inflation
	WHERE Country IS NOT NULL
	GROUP BY country
	ORDER BY country ASC;
	-- COMMAND ----------
	SELECT country, AVG(Y2018) as AverageInfaltion2018
	FROM portfolio_project.global_inflation
	WHERE Country IS NOT NULL
	GROUP BY country
	ORDER BY country ASC;
	-- COMMAND ----------
	SELECT country, AVG(Y2019) as AverageInfaltion019
	FROM portfolio_project.global_inflation
	WHERE Country IS NOT NULL
	GROUP BY country
	ORDER BY country ASC;
	-- COMAND -----------
	SELECT country, AVG(Y2020) as AverageInfaltion2020
	FROM portfolio_project.global_inflation
	WHERE Country IS NOT NULL
	GROUP BY country
	ORDER BY country ASC;
	-- COMMAND ----------
	SELECT country, AVG(Y2021) as AverageInfaltion2021
	FROM portfolio_project.global_inflation
	WHERE Country IS NOT NULL
	GROUP BY country
	ORDER BY country ASC;
	-- COMMAND ----------
	SELECT country, AVG(Y2022) as AverageInfaltion2022
	FROM portfolio_project.global_inflation
	WHERE Country IS NOT NULL
	GROUP BY country
	ORDER BY country ASC;
	
	-- COMPARING AVERAGE GDP OF 2021 TO 2022
	
	-- CHECKING THE SUM OF INFLATION IN EACH YEAR FROM 2017 TO 2022
	-- COMMAND ----------
	
	SELECT country, SUM(Y2017) AS TotalY2017, SUM(Y2018) AS TotalY2018, SUM(Y2019) AS TotalY2019
	FROM portfolio_project.global_inflation
	WHERE Country IS NOT NULL
	GROUP BY country
	ORDER BY country ASC;
	
	-- COMMAND ----------
	
	SELECT country, SUM(Y2021) AS Y2021, SUM(Y2022) AS Y2022
	FROM portfolio_project.global_inflation
	WHERE Country IS NOT NULL
	GROUP BY country
	ORDER BY country ASC;
	
	-- COMMAND ----------
	-- THIS SECTION WE'RE TRYING TO FIND THE COUNRTIES WITH THE HIGHEST INFLATION IN WESTERN COUNTRIES
	SELECT country, SUM(Y2017) AS TotalY2017
	FROM portfolio_project.global_inflation
	WHERE country IN ('china', 'United States','Japan', 'Germany','United Kingdom', 'france', 'Italy', 'South Korea', 'Canada')
	GROUP BY country
	ORDER BY TotalY2017 DESC;
	-- COMMAND ---------
	SELECT country, SUM(Y2022) AS TotalY2022
	FROM portfolio_project.global_inflation
	WHERE country IN ('china', 'United States','Japan', 'Germany','United Kingdom', 'france', 'Italy', 'South Korea', 'Canada')
	GROUP BY country
	ORDER BY TotalY2022 DESC;
	
	-- COMMAND ----------
	
	SELECT country, SUM(Y2017/Y2022)*100 AS TotalPercentage2017to2022
	FROM portfolio_project.global_inflation
	WHERE country IN  ('china', 'United States','Japan', 'Germany','United Kingdom', 'france', 'Italy', 'South Korea', 'Canada') 
	GROUP BY country
	ORDER BY TotalPercentage2017to2022 desc;
	
	-- COMMAND ----------
	
	SELECT country, MAX(Y2017) AS max_Y2017
	FROM portfolio_project.global_inflation
	WHERE country IN  ('china', 'United States','Japan', 'Germany','United Kingdom', 'france', 'Italy', 'South Korea', 'Canada')
	GROUP BY country
	ORDER BY max_Y2017 desc;
	
	-- COMMAND ----------
	
	SELECT country, MAX(Y2018) AS max_Y2018
	FROM portfolio_project.global_inflation
	WHERE country IN  ('china', 'United States','Japan', 'Germany','United Kingdom', 'france', 'Italy', 'South Korea', 'Canada')
	GROUP BY country
	ORDER BY max_Y2018 desc;
	
	-- COMMAND ----------
	
	SELECT country, MAX(Y2019) AS max_Y2019
	FROM portfolio_project.world_population
	WHERE country IN  ('china', 'United States','Japan', 'Germany','United Kingdom', 'france', 'Italy', 'South Korea', 'Canada')
	GROUP BY country
	ORDER BY max_Y2019 desc;
	
	-- COMMAND ----------
	
	SELECT country, MAX(Y2020) AS max_2020
	FROM portfolio_project.global_inflation
	WHERE country IN ('Iraq','Afghanistan','Poland','Canada','Morocco','Saudi Arabia','Ukraine','Angola','Uzbekistan','Yemen','Peru','Malaysia','Ghana','Mozambique','Nepal','Madagascar','Ivory Coast','Venezuela','Cameroon')
	GROUP BY country
	ORDER BY max_pop1980 desc
	LIMIT 20;
	
	-- COMMAND ----------
	
	SELECT country, MAX(Y2021) AS max_2021
	FROM portfolio_project.global_inflation
	WHERE country IN ('china', 'United States','Japan', 'Germany','United Kingdom', 'france', 'Italy', 'South Korea', 'Canada')
	GROUP BY country
	ORDER BY max_2021 desc;

University Project


	SELECT * FROM portfolio_project.collegeliving;
	SELECT COUNT(*)
	FROM portfolio_project.collegeliving
	WHERE state = 'alabama';
	
	SELECT Type, State, expense, year, MAX(value) AS tuitionfees
	FROM portfolio_project.COllegeliving
	WHERE expense like '%tuition%'
	AND type like '%private%'
	GROUP BY state, state, expense, type, year
	ORDER BY year;
	
	SELECT year, value, type
	FROM portfolio_project.collegeliving
	WHERE type like '%private%';
	
	SELECT COUNT(*)
	FROM portfolio_project.collegeliving
	WHERE state = 'alaska';
	
	SELECT COUNT(*)
	FROM portfolio_project.collegeliving
	WHERE state = 'alabama';
	
	SELECT Type, State, expense, SUM(value) as totalfees
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas', 'alabama') 
	and year = '2014'
	GROUP BY state, state, expense, type
	ORDER BY totalfees;
	
	SELECT Type, State, expense, SUM(value) as totalfees
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	and year = '2015'
	GROUP BY state, state, expense, type
	ORDER BY totalfees;
	
	SELECT Type, State, expense, SUM(value) as totalfees
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	and year = '2016'
	GROUP BY state, state, expense, type
	ORDER BY totalfees;
	
	SELECT Type, State, expense, SUM(value) as totalfees
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	and year = '2017'
	GROUP BY state, state, expense, type
	ORDER BY totalfees;
	
	SELECT Type, State, expense, SUM(value) as totalfees
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	and year = '2018'
	GROUP BY state, state, expense, type
	ORDER BY totalfees;
	
	SELECT Type, State, expense, SUM(value) as totalfees
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	and year = '2019'
	GROUP BY state, state, expense, type
	ORDER BY totalfees;
	
	SELECT Type, State, expense, SUM(value) as totalfees
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	and year = '2020'
	GROUP BY state, state, expense, type
	ORDER BY totalfees;
	
	SELECT Type, State, expense, SUM(value) as totalfees
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	and year = '2021'
	GROUP BY state, state, expense, type
	ORDER BY totalfees;
	
	
	
	SELECT SUM(value)
	FROM portfolio_project.collegeliving;
	
	SELECT AVG(value) Average_Tuition
	FROM portfolio_project.collegeliving;
	
	SELECT AVG(value) AS AVG_public
	FROM portfolio_project.collegeliving
	WHERE expense like '%public%'
	order by AVG_public;
	
	SELECT Type, State, expense, MAX(value) AS MAXfees
	FROM portfolio_project.COllegeliving
	WHERE expense like '%tuition%'
	GROUP BY state, state, expense, type
	ORDER BY MAXfees;
	
	SELECT year, Type, State, expense, MAX(value) AS MAXfees
	FROM portfolio_project.COllegeliving
	WHERE expense like '%tuition%'
	GROUP BY state, state, expense, type
	ORDER BY MAXfees;
	
	-- AVG INCREASE IN TUITION FROM 2014 TO 2021
	
	SELECT Type, State, expense, AVG(value) as avg_tuition2014
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	AND year = '2014' 
	AND expense like '%tuition%' 
	GROUP BY state, state, expense, type
	ORDER BY avg_tuition2014;
	
	SELECT Type, State, expense, AVG(value) as avg_tuition2021
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	AND year = '2021' 
	AND expense like '%tuition%' 
	GROUP BY state, state, expense, type
	ORDER BY avg_tuition2021;
	
	-- AVG INCREASE WITH ROOM AND BOAORD FROM 2014 TO 2021
	
	SELECT Type, State, expense, AVG(value) as room_expense
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	AND year = '2014' 
	AND expense like '%room%' 
	GROUP BY state, state, expense, type
	ORDER BY room_expense;
	
	SELECT Type, State, expense, AVG(value) as room_fees
	FROM portfolio_project.COllegeliving
	WHERE state IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	AND year = '2021' 
	AND expense like '%room%' 
	GROUP BY state, state, expense, type
	ORDER BY room_fees;
	
	-- TUITION BETWENN PRIVATE SCHOOLS AND PUBLILC  IN 2021
	
	SELECT Type, State, expense, SUM(value) as private_tuition
	FROM portfolio_project.COllegeliving
	WHERE state NOT IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	AND year = '2021' 
	AND type like '%private%' 
	AND expense like '%tuition%'
	GROUP BY state, state, expense, type
	ORDER BY private_tuition 
	LIMIT 20;
	
	SELECT Type, State, expense, SUM(value) as public_tuition
	FROM portfolio_project.COllegeliving
	WHERE state NOT IN  ('nebraska', 'utah','virginia', 'wyoming','california', 'arkansas', 'florida', 'georgia', 'kansas') 
	AND year = '2021' 
	AND type like '%public%' 
	AND expense like '%tuition%'
	GROUP BY state, state, expense, type
	ORDER BY public_tuition 
	LIMIT 20;