Covid_19.sql

select * FROM covid_dataset;

-- BASIC SQL
-- How many total patient records are present in the table?--

SELECT count(*) FROM covid_dataset;

-- How many patients tested COVID positive? --

SELECT count(*) as covid_positive FROM covid_dataset
WHERE Has_Covid='Yes';

-- Retrieve all patients with fever greater than 101°F.
SELECT * FROM covid_dataset
WHERE Fever >101;
-- List distinct cities represented in the dataset.

SELECT DISTINCT(City) from covid_dataset;


-- Count the number of patients for each gender.

SELECT Gender,COUNT(*) as total from covid_dataset
Group by Gender;


-- AGGREGATION & GROUP BY
-- Find the average fever for COVID-positive and COVID-negative patients.
SELECT Has_covid,avg(Fever) from covid_dataset
group by Has_covid;


-- Count COVID-positive cases by gender.

SELECT Gender,count(Has_Covid) from covid_dataset
group by Gender;

-- Show the number of patients in each city.

SELECT  City,count(*) from covid_dataset
group by City;

-- Find the maximum and minimum fever recorded in the dataset.

SELECT max(Fever),min(Fever) from covid_dataset;

-- Calculate the average age of COVID-positive patients.

SELECT avg(age) as avg_age_positive from covid_dataset
where Has_Covid='Yes';


-- HEALTHCARE-FOCUSED ANALYTICS

-- How many COVID-positive patients have a strong cough?

SELECT COUNT(*) as total_strong_covid FROM covid_dataset
where Has_Covid='Yes' and Cough='Strong';


-- What percentage of COVID-positive patients have fever ≥ 101°F?

SELECT 
    COUNT(CASE WHEN Fever >= 101 THEN 1 END) * 100.0 / COUNT(*) AS percentage_high_fever
FROM covid_dataset
WHERE Has_Covid = 'Yes';


-- Compare the average fever between patients with mild cough and strong cough.

SELECT Cough,AVG(FEVER) from covid_dataset
group by Cough;

-- Identify cities with more than 50 COVID-positive cases.

Select  City,COUNT(
CASE WHEN Has_Covid='Yes' THEN 1 END) positive_cases from covid_dataset
group by City
Having positive_cases>110;


SELECT 
    FLOOR(Age / 10) * 10 AS age_group,
    COUNT(*) AS covid_cases
FROM covid_dataset
WHERE Has_Covid = 'Yes'
GROUP BY FLOOR(Age / 10)
ORDER BY covid_cases DESC
LIMIT 1;
 

-- DECISION SUPPORT / LOGIC

-- Identify high-risk patients defined as age ≥ 60, fever ≥ 101°F, and strong cough.
select * from covid_dataset
where Age>=60 and Fever>=101 and Cough='Strong';

-- Count how many high-risk patients actually tested COVID positive.
select count(*) from covid_dataset
where Age>=60 and Fever>=101 and Cough='Strong';

-- Compare COVID positivity rates between high-risk and low-risk patients.

Select
  Case
    when Age >= 60 AND Fever >= 101 AND Cough = 'Strong'
      then 'High Risk'
    else 'Low Risk'
  end as risk_group,
  count(case when Has_Covid = 'Yes' then 1 end) * 100.0 / count(*) AS positivity_rate
from covid_dataset
group by risk_group;

-- Create a risk label (High / Low) based on fever and cough severity.
Select *,
  Case
    When Fever >= 101 AND Cough = 'Strong' THEN 'High Risk'
    else'Low Risk'
  end as risk_label
from covid_dataset;

-- Find false-negative cases where patients were high risk but COVID negative.

SELECT * FROM covid_dataset
WHERE Age>=60 and Fever>101 and Cough='Strong' and Has_Covid='No';