Database Design and Visualization about Covid, Vaccination, Temperature and Economic

SQL Query

1.Extract Covid-19 Case Trends for Specific Countries

SELECT * FROM Covid_Data WHERE Country = 'China' ORDER BY Year, Month;

SELECT * FROM Covid_Data WHERE Country = 'USA' ORDER BY Year, Month;

2.Join Economic Data with Health Data to Analyze Correlations

SELECT e.*, c.CumulativeTotalCases, c.CumulativeTotalDeaths
FROM Economic_Data e
JOIN Covid_Data c ON e.Country = c.Country AND e.Year = c.Year
WHERE e.Country = 'China';

SELECT e.Year, e.GDP, e.Unemployment, c.CumulativeTotalCases, c.CumulativeTotalDeaths
FROM Economic_Data e
JOIN Covid_Data c ON e.Country = c.Country AND e.Year = c.Year
WHERE e.Country = 'China'
ORDER BY e.Year;

3.Connect Covid-19 Case Data with Temperature Data

SELECT c.*, t.AvgTemperature
FROM Covid_Data c
JOIN Temperature_Data t ON c.Country = t.Country AND c.Year = t.Year AND c.Month = t.Month
WHERE c.Country = 'China';

SELECT c.*, t.AvgTemperature
FROM Covid_Data c
JOIN Temperature_Data t ON c.Country = t.Country AND c.Year = t.Year AND c.Month = t.Month
WHERE c.Country = 'India';

4.Connect Covid-19 Cases with Economic Data

SELECT c.*, e.GDP, e.Unemployment
FROM Covid_Data c
JOIN Economic_Data e ON c.Country = e.Country AND c.Year = e.Year
WHERE c.Country = 'China';

SELECT c.*, e.GDP, e.Unemployment
FROM Covid_Data c
JOIN Economic_Data e ON c.Country = e.Country AND c.Year = e.Year
WHERE c.Country = 'India';

5.Create Index and View

CREATE INDEX idx_monthly_new_cases ON Covid_Data(MonthlyNewCases);
CREATE VIEW View_Covid_Monthly_Summary AS
SELECT 
  Country, 
  Year, 
  Month, 
  CumulativeTotalCases, 
  MonthlyNewCases
FROM Covid_Data;

CREATE INDEX idx_gdp_year ON Economic_Data(GDP, Year);
CREATE VIEW View_Economic_Indicators AS
SELECT 
  Country, 
  Year, 
  GDP, 
  InflationRate, 
  Unemployment
FROM Economic_Data;

CREATE INDEX idx_temperature_year_month ON Temperature_Data(Year, Month);
CREATE VIEW View_Avg_Temperature AS
SELECT 
  AreaCode, 
  Country, 
  Year, 
  Month, 
  AVG(AvgTemperature) AS MonthlyAvgTemperature
FROM Temperature_Data
GROUP BY AreaCode, Country, Year, Month;

CREATE INDEX idx_fully_vaccinated ON Vaccination_Data(PeopleFullyVaccinated);
CREATE VIEW View_Vaccination_Progress AS
SELECT 
  Country, 
  Year, 
  Month, 
  PeopleVaccinated, 
  PeopleFullyVaccinated
FROM Vaccination_Data;

To verify index:

SHOW INDEX FROM Covid_Data;

To verify views:

DESCRIBE View_Covid_Monthly_Summary;