practiceSQL

use nhs;

SELECT * FROM ukcoviddata LIMIT 10;
WHERE CasesPerDay > 30000 
AND Date LIKE '%2021-10%'; 

SELECT * FROM ukcoviddata;

SELECT CONCAT_WS(" ", CasesPerDay, "cases on", Date) AS "Cases Per Day"
FROM ukcoviddata ORDER BY CasesPerDay DESC;

SELECT CONCAT(SUBSTR(Date, 1, 4)) AS "Year", CasesPerDay FROM ukcoviddata;

SELECT * FROM ukcoviddata;

#how many cases per month in 2021 were there?

SELECT SUBSTR(Date, 6, 2) as 'Month', SUM(CasesPerDay) FROM ukcoviddata 
GROUP by SUBSTR(Date, 6, 2);


SELECT DATE_FORMAT(Date,'%M') AS 'Month', SUM(CasesPerDay) AS 'Cases Per Day' FROM ukcoviddata 
GROUP by DATE_FORMAT(Date,'%M');

SELECT SUM(CasesPerDay) AS 'Total Cases' FROM ukcoviddata;

SELECT DATE_FORMAT(Date, '%M') AS 'Month', FORMAT(SUM(FirstVaccineGiven), 0) AS 'First Vaccine Uptake', FORMAT(SUM(SecondVaccineGiven), 0) AS 'Second Vaccine Uptake' FROM ukcoviddata
GROUP BY DATE_FORMAT(Date, '%M');

SELECT * FROM ukcoviddata
LIMIT 20;

SELECT DATE_FORMAT(Date,'%M') AS 'Month', SUM(DeathsPerDay) AS 'Deaths' FROM ukcoviddata 
GROUP by DATE_FORMAT(Date,'%M');