Requirement Details
You are tasked to develop some SQL scripts to query the data, as follows:
1. Display a list of total vaccinations per day in Singapore. [source table: country_vaccinations]
2. Display the sum of daily vaccinations among ASEAN countries. [source table: country_vaccinations]
3. Identify the maximum daily vaccinations per million on each country. Sort the list based on daily vaccinations per million in a descending order. [source table: country_vaccinations]
4. Which is the most administrated vaccine? Display a list of total administration (i.e., sum of total vaccinations) per vaccine. [source table: country_vaccinations_by_manufacturer]
5. Italy has commenced administrating various vaccines to its populations as a vaccine becomes available. Identify the first dates of each vaccine being administrated, then compute the difference in days between the earliest date and the 4th date. [source table: country_vaccinations_by_manufacturer]
6. What is the country with the most types of administrated vaccine? [source table: country_vaccinations_by_manufacturer]
7. What are the countries that have fully vaccinated more than 60% of its people? For each country, display the vaccines administrated. [source table: country_vaccinations]
8. Monthly vaccination insight – display the monthly total vaccination amount of each vaccine per month in the United States. [source table: country_vaccinations_by_manufacturer
9. Days to 50 percent. Compute the number of days (i.e., using the first available date on records of a country) that each country takes to go above the 50% threshold of vaccination administration (i.e., total_vaccinations_per_hundred > 50) [source table: country_vaccinations]
10. Compute the global total of vaccinations per vaccine. [source table: country_vaccinations_by_manufacturer]
Database File and solution you can download from here
Database Design
Create Table country_vaccinations
CREATE DATABASE IF NOT EXISTS `country_vaccinations`
USE `country_vaccinations`
DROP TABLE IF EXISTS `country_vaccinations`;
CREATE TABLE `country_vaccinations` (
`country` text,
`iso_code` text,
`date` text,
`total_vaccinations` decimal(20,4) unsigned DEFAULT '0.0000',
`people_vaccinated` decimal(20,4) unsigned DEFAULT '0.0000',
`people_fully_vaccinated` decimal(20,4) unsigned DEFAULT '0.0000',
`daily_vaccinations_raw` decimal(20,4) unsigned DEFAULT '0.0000',
`daily_vaccinations` decimal(20,4) unsigned DEFAULT '0.0000',
`total_vaccinations_per_hundred` decimal(20,4) unsigned DEFAULT '0.0000',
`people_vaccinated_per_hundred` decimal(20,4) unsigned DEFAULT '0.0000',
`people_fully_vaccinated_per_hundred` decimal(20,4) unsigned DEFAULT '0.0000',
`daily_vaccinations_per_million` decimal(20,4) unsigned DEFAULT '0.0000',
`vaccines` text,
`source_name` text,
`source_website` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
Insert Data Into country_vaccinations
INSERT INTO `country_vaccinations` VALUES ('Afghanistan','AFG','2/22/2021',0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,'BBIBP-CorV; Oxford/AstraZeneca; Pfizer/BioNTech','World Health Organization','https://covid19.who.int/\r'),('Afghanistan','AFG','2/23/2021',0.0000,0.0000,0.0000,0.0000,1367.0000,0.0000,0.0000,0.0000,35.0000,'BBIBP-CorV; Oxford/AstraZeneca; Pfizer/BioNTech','World Health Organization','https://covid19.who.int/\r'),('Afghanistan','AFG','2/24/2021',0.0000,0.0000,0.0000,0.0000,1367.0000,0.0000,0.0000,0.0000,35.0000,'BBIBP-CorV; Oxford/AstraZeneca; Pfizer/BioNTech','World Health Organization','https://covid19.who.int/\r'),('Afghanistan','AFG','2/25/2021',0.0000,0.0000,0.0000,0.0000,1367.0000,0.0000,0.0000,0.0000,35.0000,'BBIBP-CorV; Oxford/AstraZeneca; Pfizer/BioNTech','World Health Organization','https://covid19.who.int/\r'),('Afghanistan','AFG','2/26/2021',0.0000,0.0000,0.0000,0.0000,1367.0000,0.0000,0.0000,0.0000,35.0000,'BBIBP-CorV; Oxford/AstraZeneca; Pfizer/BioNTech','World Health Organization','https://covid19.who.int/\r'),('Afghanistan','AFG','2/27/2021',0.0000,0.0000,0.0000,0.0000,1367.0000,0.0000,0.0000,0.0000,35.0000,'BBIBP-CorV; Oxford/AstraZeneca; Pfizer/BioNTech','World Health Organization','https://covid19.who.int/\r'),('Afghanistan','AFG','2/28/2021',8200.0000,8200.0000,0.0000,0.0000,1367.0000,0.0200,0.0200,0.0000,35.0000,'BBIBP-CorV; Oxford/AstraZeneca; Pfizer/BioNTech','World Health Organization','https://covid19.who.int/\r'),('Afghanistan','AFG','3/1/2021',0.0000,0.0000,0.0000,0.0000,1580.0000,0.0000,0.0000,0.0000,41.0000,'BBIBP-CorV; Oxford/AstraZeneca; Pfizer/BioNTech','World Health Organization','https://covid19.who.int/\r'),('Afghanistan','AFG','3/2/2021',0.0000,0.0000,0.0000,0.0000,1794.0000,0.0000,0.0000,0.0000,46.0000,'BBIBP-CorV; Oxford/AstraZeneca; Pfizer/BioNTech','World Health Organization','https://covid19.who.int/\r');
Create Table country_vaccinations_by_manufacturer
DROP TABLE IF EXISTS `country_vaccinations_by_manufacturer`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `country_vaccinations_by_manufacturer` (
`location` text,
`date` text,
`vaccine` text,
`total_vaccinations` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Inserting Data into country_vaccinations_by_manufacturer
INSERT INTO `country_vaccinations_by_manufacturer` VALUES ('Austria','2021-01-08','Johnson&Johnson',0),('Austria','2021-01-08','Moderna',0),('Austria','2021-01-08','Oxford/AstraZeneca',0),('Austria','2021-01-08','Pfizer/BioNTech',30921),('Austria','2021-01-15','Johnson&Johnson',0),('Austria','2021-01-15','Moderna',88),('Austria','2021-01-15','Oxford/AstraZeneca',0),('Austria','2021-01-15','Pfizer/BioNTech',115444),('Austria','2021-01-22','Johnson&Johnson',0),('Austria','2021-01-22','Moderna',298),('Austria','2021-01-22','Oxford/AstraZeneca',0),('Austria','2021-01-22','Pfizer/BioNTech',212610),('Austria','2021-01-29','Johnson&Johnson',0),('Austria','2021-01-29','Moderna',4159),('Austria','2021-01-29','Oxford/AstraZeneca',0),('Austria','2021-01-29','Pfizer/BioNTech',257204),('Austria','2021-02-05','Johnson&Johnson',0),('Austria','2021-02-05','Moderna',5839),('Austria','2021-02-05','Oxford/AstraZeneca',1),('Austria','2021-02-05','Pfizer/BioNTech',352426),('Austria','2021-02-12','Johnson&Johnson',0),('Austria','2021-02-12','Moderna',9904),('Austria','2021-02-12','Oxford/AstraZeneca',10764),('Austria','2021-02-12','Pfizer/BioNTech',453294),('Austria','2021-02-19','Johnson&Johnson',0),('Austria','2021-02-19','Moderna',14023),('Austria','2021-02-19','Oxford/AstraZeneca',43468),('Austria','2021-02-19','Pfizer/BioNTech',518768),('Austria','2021-02-26','Johnson&Johnson',0),('Austria','2021-02-26','Moderna',22288),('Austria','2021-02-26','Oxford/AstraZeneca',101145),('Austria','2021-02-26','Pfizer/BioNTech',579695),('Austria','2021-03-05','Johnson&Johnson',0),('Austria','2021-03-05','Moderna',31531),('Austria','2021-03-05','Oxford/AstraZeneca',155779),('Austria','2021-03-05','Pfizer/BioNTech',683685),('Austria','2021-03-12','Johnson&Johnson',0),('Austria','2021-03-12','Moderna',46828),('Austria','2021-03-12','Oxford/AstraZeneca',233915),('Austria','2021-03-12','Pfizer/BioNTech',846513),('Austria','2021-03-19','Johnson&Johnson',13),('Austria','2021-03-19','Moderna',78532),('Austria','2021-03-19','Oxford/AstraZeneca',306886),('Austria','2021-03-19','Pfizer/BioNTech',978573),('Austria','2021-03-26','Johnson&Johnson',20),('Austria','2021-03-26','Moderna',103837),('Austria','2021-03-26','Oxford/AstraZeneca',381715),('Austria','2021-03-26','Pfizer/BioNTech',1123955),('Austria','2021-04-02','Johnson&Johnson',31),('Austria','2021-04-02','Moderna',140007),('Austria','2021-04-02','Oxford/AstraZeneca',420098),('Austria','2021-04-02','Pfizer/BioNTech',1271168),('Austria','2021-04-09','Johnson&Johnson',53),('Austria','2021-04-09','Moderna',179331),('Austria','2021-04-09','Oxford/AstraZeneca',509988),('Austria','2021-04-09','Pfizer/BioNTech',1491745),('Austria','2021-04-16','Johnson&Johnson',55),('Austria','2021-04-16','Moderna',221571),('Austria','2021-04-16','Oxford/AstraZeneca',605098),('Austria','2021-04-16','Pfizer/BioNTech',1724136),('Austria','2021-04-23','Johnson&Johnson',59),('Austria','2021-04-23','Moderna',258699),('Austria','2021-04-23','Oxford/AstraZeneca',653582),('Austria','2021-04-23','Pfizer/BioNTech',1986500),('Austria','2021-04-30','Johnson&Johnson',306),('Austria','2021-04-30','Moderna',297455),('Austria','2021-04-30','Oxford/AstraZeneca',695480),('Austria','2021-04-30','Pfizer/BioNTech',2258629),('Austria','2021-05-07','Johnson&Johnson',6392),('Austria','2021-05-07','Moderna',346379),('Austria','2021-05-07','Oxford/AstraZeneca',757902),('Austria','2021-05-07','Pfizer/BioNTech',2587398),('Austria','2021-05-14','Johnson&Johnson',13313),('Austria','2021-05-14','Moderna',395832),('Austria','2021-05-14','Oxford/AstraZeneca',821495),('Austria','2021-05-14','Pfizer/BioNTech',2885714),('Austria','2021-05-21','Johnson&Johnson',25501),('Austria','2021-05-21','Moderna',463216),('Austria','2021-05-21','Oxford/AstraZeneca',898515),('Austria','2021-05-21','Pfizer/BioNTech',3261872),('Austria','2021-05-28','Johnson&Johnson',37556);
Solution
Query 1:
select sum(total_vaccinations) as total_vaccinations , date from country_vaccinations where country = "Singapore" group by date;
Query 2:
select vaccines, sum(daily_vaccinations) as
total_administrated from country_vaccinations
where iso_code in ('AFG', 'ARE', 'ARM', 'BGD',
'BHR', 'BRN', 'BTN', 'CCK', 'CHN', 'CXR', 'HKG',
'IDN', 'IND', 'IOT', 'IRN', 'IRQ', 'ISR', 'JOR',
'JPN', 'KGZ', 'KHM', 'KOR', 'KWT', 'LAO', 'LBN',
'LKA', 'MAC', 'MDV', 'MMR', 'MNG', 'MYS', 'NPL',
'OMN', 'PAK', 'PHL', 'PRK', 'PSE', 'QAT', 'SAU',
'SGP', 'SYR', 'THA', 'TJK', 'TKM', 'TLS', 'TWN',
'UZB', 'VNM', 'YEM') group by vaccines;
Query 3:
select country, max(daily_vaccinations_per_million) from country_vaccinations group by country
order by max(daily_vaccinations_per_million) desc;
Query 4:
select vaccine, sum(total_vaccinations) as total_adminstrator from country_vaccinations_by_manufacturer
group by vaccine order by sum(total_vaccinations) desc;
Query 5:
DROP TABLE IF EXISTS date_difference;
Create view date_difference as SELECT vaccine, MIN(date) AS first_Date
FROM country_vaccinations_by_manufacturer
GROUP BY vaccine order by first_date limit 4;
select *from date_difference;
select datediff(first_date, '2020-12-16', '2020-12-27') AS Difference_In_Days FROM date_difference;
Query: 6
SELECT location , vaccine
FROM country_vaccinations_by_manufacturer
GROUP BY vaccine
ORDER BY COUNT(location) DESC;
Query: 7
select country, vaccines, people_fully_vaccinated_per_hundred as
vaccinated_percentage
from country_vaccinations group by country, vaccines
having vaccinated_percentage > 60.00 order by vaccinated_percentage desc ;
Query 8:
SELECT MONTHNAME(date) as month, vaccine, sum(total_vaccinations) as monthly_total_vaccine
FROM country_vaccinations_by_manufacturer
GROUP BY vaccine;
Query 9:
select country, sum(day(STR_TO_DATE(date, '%d/%m/%Y'))) as Days_to_over_50per from country_vaccinations
where total_vaccinations_per_hundred > 50 group by country;
Query 10:
select sum(total_vaccinations) as globel_total, vaccine from country_vaccinations_by_manufacturer
group by vaccine;
If you need any other database assignment help, homework help or need any other database related help then share your requirement details.
Send your request at realcode4you@gmail.com and get instant help with an affordable price.
We are always focus to delivered unique or without plagiarism code which is written by our highly educated professional which provide well structured code within your given time frame.
If you are looking other programming language help like C, C++, Java, Python, PHP, Asp.Net, NodeJs, ReactJs, etc. with the different types of databases like MySQL, MongoDB, SQL Server, Oracle, etc. then also contact us.