Question 21:
Show
patient_id, first_name, last_name from patients whos diagnosis is 'Dementia'.
Primary diagnosis is stored in the admissions
table.
Solution:
select p.patient_id,p.first_name,p.last_name from patients as p
join admissions as a on p.patient_id = a.patient_id
where a.diagnosis = 'Dementia'
Question 22:
Display every patient's first_name.
Order the list by the length of each name and
then by alphbetically
Solution:
select first_name from patients order by len(first_name),first_name
Question 23:
Show the total amount of male patients and the
total amount of female patients in the patients table.
Display the two results in the same row.
Solution:
select sum(case when gender='M' then 1 else 0 end) as male_count,
sum(case when gender='F' then 1 else 0 end) as female_count
from patients
Question 24:
Show first and last name, allergies from patients
which have allergies to either 'Penicillin' or 'Morphine'. Show results ordered
ascending by allergies then by first_name then by last_name.
Solution:
select first_name,last_name,allergies from patients where
allergies = 'Penicillin' or allergies='Morphine'
order by allergies,first_name,last_name
Question 25:
Show patient_id, diagnosis from admissions. Find
patients admitted multiple times for the same diagnosis.
Solution:
select p.patient_id,a.diagnosis from patients as p
join admissions as a on p.patient_id = a.patient_id
group by p.patient_id,a.diagnosis
having count(a.diagnosis)>1
Question 26:
Show the city and the total number of patients in
the city.
Order from most to least patients and then by
city name ascending.
Solution:
select city,count(patient_id) as patient_count from patients
group by city
order by patient_count desc,city
Question 27:
Show first name, last name and role of every
person that is either patient or doctor.
The roles are either "Patient" or
"Doctor
Solution:
select first_name,last_name, 'Patient' AS ROLE from patients
union ALL
select first_name,last_name,'Doctor' AS ROLE from doctors
Question 28:
Show all allergies ordered by popularity. Remove
NULL values from query.
Solution:
select allergies,count(patient_id) as total_dia FROM patients
where allergies is not null
group by allergies
order by total_dia desc
Question 29:
Show all patient's first_name, last_name, and
birth_date who were born in the 1970s decade. Sort the list starting from the
earliest birth_date.
Solution:
SELECT
first_name,
last_name,birth_date FROM patients WHERE
birth_date >= '1970-01-01'
AND birth_date < '1980-01-01'
ORDER BY birth_date ASC
Question 30:
We want to display each patient's full name in a
single column. Their last_name in all upper letters must appear first, then
first_name in all lower case letters. Separate the last_name and first_name
with a comma. Order the list by the first_name in decending order
EX: SMITH,jane
Solution:
select concat(upper(last_name),',',lower(first_name)) from patients
order by first_name desc