Question 31:
Show the province_id(s), sum of height; where the
total sum of its patient's height is greater than or equal to 7,000.
Solution:
select province_id,sum(height) as sum_height from patients
group by province_id
having sum(height)>=7000
Question 32:
Show the difference between the largest weight
and smallest weight for patients with the last name 'Maroni'
Solution:
select max(weight)-min(weight) as weight_delta
from patients where last_name='Maroni'
Question 33:
Show all of the days of the month (1-31) and how
many admission_dates occurred on that day. Sort by the day with most admissions
to least admissions.
Solution:
select day(admission_date) as day_number,
count(patient_id) as total_admission
from admissions
group by day(admission_date)
order by total_admission desc
Question 34:
Show all columns for patient_id 542's most recent
admission_date.
Solution:
select top 1 * from admissions where patient_id=542
order by admission_date desc
Question 35:
Show patient_id, attending_doctor_id, and
diagnosis for admissions that match one of the two criteria:
1. patient_id is an odd number and
attending_doctor_id is either 1, 5, or 19.
2. attending_doctor_id contains a 2 and the
length of patient_id is 3 characters.
Solution:
select patient_id,attending_doctor_id,diagnosis from admissions
where
(patient_id %2 >0 and attending_doctor_id in (1,5,19))
or
(attending_doctor_id like '%2%' and len(patient_id) =3)
Question 36:
Show first_name, last_name, and the total number
of admissions attended for each doctor.
Every admission has been attended by a doctor.
Solution:
select d.first_name,d.last_name,count(a.patient_id) as total_patients from admissions as a
join doctors as d on a.attending_doctor_id = d.doctor_id
group by d.first_name,d.last_name
Question 37:
For each doctor, display their id, full name, and
the first and last admission date they attended.
Solution:
select d.doctor_id,CONCAT(d.first_name,' ',d.last_name) as full_name, min(a.admission_date) as first_admission,
max(a.admission_date) as last_admission
from doctors as d
join admissions as a on d.doctor_id = a.attending_doctor_id
group by d.doctor_id,d.first_name,d.last_name
Question 38:
Display the total amount of patients for each
province. Order by descending.
Solution:
select n.province_name, count(p.patient_id) as total_patient from patients as p
join province_names as n on p.province_id = n.province_id
group by n.province_name
order by total_patient desc
Question 40:
For every admission, display the patient's full
name, their admission diagnosis, and their doctor's full name who diagnosed
their problem.
Solution:
select concat(p.first_name,' ',p.last_name) as full_name,
a.diagnosis,concat(d.first_name,' ',d.last_name) as doctor_name
from admissions as a
join patients as p on a.patient_id = p.patient_id
join doctors as d on a.attending_doctor_id = d.doctor_id