Question 41:
display the number of duplicate patients based on
their first_name and last_name
Solution:
select first_name,last_name,count(*) as num_of_duplicate from patients
group by first_name,last_name
having count(*) > 1
Question 42:
Display patient's full name,
height in the units feet rounded to 1 decimal,
weight in the unit pounds rounded to 0 decimals,
birth_date,
gender non abbreviated.
Convert CM to feet by dividing by 30.48.
Convert KG to pounds by multiplying by 2.205.
Solution:
select concat(first_name,' ',last_name) as full_name,
round(height/30.48,1) as height_feet, round(weight * 2.205,0) as weight_pound,
birth_date,
(case when gender ='F' then 'Female' else 'Male' end) as gender
from patients
Question 43:
Show all of the patients grouped into weight
groups.
Show the total amount of patients in each weight
group.
Order the list by the weight group decending.
For example, if they weight 100 to 109 they are
placed in the 100 weight group, 110-119 = 110 weight group, etc.
Solution:
select (weight/10*10) as grp,count(weight/10*10) as countt from patients
group by weight/10*10
order by grp desc
Question 44:
Show patient_id, weight, height, isObese from the
patients table.
Display isObese as a boolean 0 or 1.
Obese is defined as weight(kg)/(height(m)2) >=
30.
weight is in units kg.
height is in units cm.
Solution:
select patient_id,weight,height,
(case when (weight/((height*0.01)*height*0.01))>30 then 1 else 0 end) as
isObese
from patients
Question 45:
Show patient_id, first_name, last_name, and
attending doctor's specialty.
Show only the patients who has a diagnosis as
'Epilepsy' and the doctor's first name is 'Lisa'
Check patients, admissions, and doctors tables
for required information.
Solution:
select p.patient_id,p.first_name,p.last_name,
d.specialty
from patients as p
join admissions as a on p.patient_id = a.patient_id
join doctors as d on a.attending_doctor_id = d.doctor_id
where a.diagnosis = 'Epilepsy' and d.first_name ='Lisa'
Question 46:
All patients who have gone through admissions,
can see their medical documents on our site. Those patients are given a
temporary password after their first admission. Show the patient_id and
temp_password.
The password must be the following, in order:
1. patient_id
2. the numerical length of patient's last_name
3. year of patient's birth_date
Solution:
select distinct p.patient_id,
concat(p.patient_id,len(p.last_name),year(p.birth_date)) as password
from patients as p
join admissions as a on p.patient_id = a.patient_id
Question 47:
Show the provinces that has more patients
identified as 'M' than 'F'. Must only show full province_name
Solution:
select pn.province_name
from patients as p
join province_names as pn on p.province_id = pn.province_id
group by pn.province_name
having sum(case when p.gender = 'M' then 1 else 0 end)>
sum(case when p.gender = 'F' then 1 else 0 end)
Question 48:
We are looking for a specific patient. Pull all
columns for the patient who matches the following criteria:
- First_name contains an 'r' after the first two
letters.
- Identifies their gender as 'F'
- Born in February, May, or December
- Their weight would be between 60kg and 80kg
- Their patient_id is an odd number
- They are from the city 'Kingston'
Solution:
select * from patients
where
first_name like '__r%'
and
gender = 'F'
and
month(birth_date) in (2,5,12)
and
weight between 60 and 80
and
patient_id%2>0
and
city = 'Kingston'
Question 49:
Sort the province names in ascending order in
such a way that the province 'Ontario' is always on top.
Solution:
select province_name from province_names where province_name = 'Ontario'
union all
select distinct province_name from province_names where province_name != 'Ontario'
select province_name
from province_names
order by
(case when province_name = 'Ontario' then 0 else 1 end),
province_name
Question 50:
For each day display the total amount of
admissions on that day. Display the amount changed from the previous date.
Solution:
select admission_date, count(*) as total_admission
,(count(*)
- lag(count(*),1) over (order by admission_date
asc) ) as admission_change
from admissions
group by admission_date
order by admission_date