Untitled

#1SELECT FLOOR(weight/10)*10 AS weight_group, COUNT(*) AS num_patients
FROM patients
GROUP BY FLOOR(weight/10)*10
ORDER BY weight_group DESC;
#2SELECT patient_id, weight, height, 
Case
when (weight / power(height/100.0,2)) >= 30 then 1
else 0 
end
as isObese
FROM patients
#3SELECT patients.patient_id, patients.first_name, patients.last_name, doctors.specialty
FROM patients
join admissions
on admissions.patient_id= patients.patient_id
join doctors
on admissions.attending_doctor_id= doctors.doctor_id
where diagnosis = 'Epilepsy' and doctors.first_name= 'Lisa'
#4 SELECT DIStinct(patients.patient_id), concat(patients.patient_id,len(patients.last_name), year(patients.birth_date)) as temp_pass
from patients
join admissions
on admissions.patient_id= patients.patient_id
#5 SELECT 
CASE
WHEN (patient_id % 2 = 0) THEN 'YES'
ELSE 'NO' 
END
AS insurance,
sum(case 
when (patient_id % 2 = 0) then 10
else 50
end)
as total_cost
from admissions
group by insurance
#6 SELECT province_names.province_name 
FROM patients
JOIN province_names ON patients.province_id = province_names.province_id
WHERE gender IN ('M', 'F')
GROUP BY province_names.province_name, patients.gender
HAVING 
  gender = 'M' AND COUNT(*) > (SELECT COUNT(*) FROM patients p2 
                               JOIN province_names p 
                               ON p2.province_id = p.province_id 
                               WHERE p.province_name = province_names.province_name AND p2.gender = 'F')
#7 SELECT * from patients
where first_name like '__r%' 
and gender = 'F' 
and month(birth_date) in (02,05,12) 
and weight between 60 and 80 
and (patient_id % 2 =1) 
and city= 'Kingston'
#8SELECT CONCAT(ROUND(COUNT(CASE WHEN gender = 'M' THEN 1 END) * 100.0 / COUNT(*), 2), '%') AS percent_male
FROM patients
#9SELECT 
  admission_date, 
  COUNT(*) AS total_admissions, 
  COUNT(*) - LAG(COUNT(admission_date)) OVER (ORDER BY admission_date) AS admissions_change
FROM admissions
GROUP BY admission_date
ORDER BY admission_date
#10
SELECT province_name
FROM province_names
ORDER BY 
  CASE WHEN province_name = 'Ontario' THEN 0 ELSE 1 END,
  province_name ASC