Question 11:
Show the total number of admissions
Solution:
select count(*) from admissions
Question 12:
Show all the columns from admissions where the
patient was admitted and discharged on the same day.
Solution:
select * from admissions where admission_date = discharge_date
Question 13:
Show the patient id and the total number of
admissions for patient_id 579.
Solution:
SELECT
patient_id,
COUNT(*) AS
total_admissions
FROM admissions
group by patient_id
having patient_id = 579
Question 14:
Based on the cities that our patients live in,
show unique cities that are in province_id 'NS'?
Solution:
select distinct city from patients where province_id='NS'
Question 15:
Write a query to find the first_name, last name
and birth date of patients who has height greater than 160 and weight greater
than 70
Solution:
select first_name,last_name,birth_date from patients where height>160 and weight>70
Question 16:
Write a query to find list of patients
first_name, last_name, and allergies from Hamilton where allergies are not null
Solution:
select first_name,last_name,allergies from patients where allergies is not null and city ='Hamilton'
Question 17:
Based on cities where our patient lives in, write
a query to display the list of unique city starting with a vowel (a, e, i, o,
u). Show the result order in ascending by city.
Solution:
select distinct city from patients where city like 'a%'
or city like 'e%'
or city like 'i%'
or city like 'o%'
or city like 'u%'
order by city
Question 18:
Show unique birth years from patients and order
them by ascending.
Solution:
select distinct year(birth_date) as birth_year from patients order by birth_year
Question 19:
Show unique first names from the patients table
which only occurs once in the list.
For example, if two or more people are named
'John' in the first_name column then don't include their name in the output
list. If only 1 person is named 'Leo' then include them in the output.
Solution:
select
first_name from patients group by first_name having count(first_name) =1
Question 20:
Show patient_id and first_name from patients
where their first_name start and ends with 's' and is at least 6 characters
long.
Solution:
select patient_id,first_name from patients where first_name like 's____%s'