Cervical_Cancer_Analysis.sql

USE [cervicalsqltable]
GO

SELECT [Age]
      ,[Number_of_sexual_partners]
      ,[First_sexual_intercourse]
      ,[Num_of_pregnancies]
      ,[Smokes]
      ,[Smokes_years]
      ,[Smokes_packs_year]
      ,[Hormonal_Contraceptives]
      ,[Hormonal_Contraceptives_years]
      ,[IUD]
      ,[IUD_years]
      ,[STDs]
      ,[STDs_number]
      ,[STDs_condylomatosis]
      ,[STDs_cervical_condylomatosis]
      ,[STDs_vaginal_condylomatosis]
      ,[STDs_vulvo_perineal_condylomatosis]
      ,[STDs_syphilis]
      ,[STDs_pelvic_inflammatory_disease]
      ,[STDs_genital_herpes]
      ,[STDs_molluscum_contagiosum]
      ,[STDs_AIDS]
      ,[STDs_HIV]
      ,[STDs_Hepatitis_B]
      ,[STDs_HPV]
      ,[STDs_Number_of_diagnosis]
      ,[STDs_Time_since_first_diagnosis]
      ,[STDs_Time_since_last_diagnosis]
      ,[Dx_Cancer]
      ,[Dx_CIN]
      ,[Dx_HPV]
      ,[Dx]
      ,[Hinselmann]
      ,[Schiller]
      ,[Citology]
      ,[Biopsy]
  FROM [dbo].[cervicalsqltest1]

GO


SELECT TOP 10 *
FROM cervicalsqltable.dbo.cervicalsqltest1;
GO

SELECT COUNT (*) AS total_patients
FROM cervicalsqltest1;

GO
---- average age of patients
SELECT AVG (Age) AS average_age
FROM cervicalsqltest1;
go

--- Youngest and Oldest Age 
SELECT 
    MIN(Age) AS youngest_patient,
    MAX (Age) AS oldest_patient,
    AVG (Age) AS average_age
FROM cervicalsqltest1;
GO

-- How many patients tested for biopsy 
--(Biopsy = 1 means positive/cancer detected)
SELECT
    Biopsy,
    COUNT(*) AS patient_count 
FROM cervicalsqltable.dbo.cervicalsqltest1
GROUP BY Biopsy;

-- Average Age of Cancer Positive vs Cancer Negative patients

SELECT 
    Biopsy,
    COUNT (*) AS patient_count, 
    AVG (Age) AS average_age ,
    MIN (Age) AS youngest,
    MAX (Age) AS Oldest
FROM cervicalsqltable.dbo.cervicalsqltest1
GROUP BY Biopsy;


SELECT 
    Biopsy,
    Smokes,
    COUNT (*) AS patient_count, 
    AVG (Age) AS average_age 
   
FROM cervicalsqltable.dbo.cervicalsqltest1
GROUP BY Biopsy, Smokes
ORDER BY Biopsy, Smokes;


SELECT
    Biopsy,
    STDs,
    COUNT(*) AS patient_count
FROM cervicalsqltable.dbo.cervicalsqltest1
GROUP BY Biopsy, STDs
ORDER BY Biopsy, STDs;

-- Hormonal Contraceptive 
SELECT
    Biopsy,
    Hormonal_Contraceptives,
    COUNT(*) AS patient_count
FROM cervicalsqltable.dbo.cervicalsqltest1
GROUP BY Biopsy, Hormonal_Contraceptives
ORDER BY Biopsy, Hormonal_Contraceptives;

--IUD EFFECT 
SELECT
    Biopsy,
    IUD,
    COUNT(*) AS patient_count
FROM cervicalsqltable.dbo.cervicalsqltest1
GROUP BY Biopsy, IUD
ORDER BY Biopsy, IUD;



-- CERVICAL CANCER RISK FACTOR ANALYSIS SUMMARY
SELECT 'Total Patients' AS Metric, COUNT(*) AS Value
FROM cervicalsqltable.dbo.cervicalsqltest1

UNION ALL

SELECT 'Cancer Positive', COUNT(*)
FROM cervicalsqltable.dbo.cervicalsqltest1
WHERE Biopsy = 1

UNION ALL

SELECT 'Cancer Negative', COUNT(*)
FROM cervicalsqltable.dbo.cervicalsqltest1
WHERE Biopsy = 0

UNION ALL

SELECT 'Smokers with Cancer', COUNT(*)
FROM cervicalsqltable.dbo.cervicalsqltest1
WHERE Biopsy = 1 AND Smokes = 1

UNION ALL

SELECT 'STD Patients with Cancer', COUNT(*)
FROM cervicalsqltable.dbo.cervicalsqltest1
WHERE Biopsy = 1 AND STDs = 1;