Question 1:
Show first name, last name, and gender of
patients who's gender is 'M'
Solution:
SELECT first_name,last_name,gender FROM patients where gender='M';
Question 2:
Show first name and last name of patients who
does not have allergies. (null)
Solution:
SELECT first_name,last_name FROM patients where allergies is null;
Question 3:
Show first name of patients that start with the
letter 'C'
Solution:
SELECT first_name FROM patients where first_name like 'c%';
Question 4:
Show first name and last name of patients that
weight within the range of 100 to 120 (inclusive)
Solution:
SELECT first_name,last_name FROM patients where weight between 100 and 120
Question 5:
Update the patients table for the allergies
column. If the patient's allergies is null then replace it with 'NKA'
Solution:
update patients set allergies = 'NKA' where allergies is null;
Question 6:
Show first name and last name concatinated into
one column to show their full name.
Solution:
select concat(first_name,' ',last_name) as fullName from patients;
Question 7:
Show first name, last name, and the full province
name of each patient.
Example: 'Ontario' instead of 'ON'
Solution:
select p.first_name,p.last_name,n.province_name from patients as p
join province_names as n on p.province_id = n.province_id
Question 8:
Show how many patients have a birth_date with
2010 as the birth year.
Solution:
select count(*) from patients where year(birth_date)=2010
SELECT count(first_name) AS total_patients
FROM patients
WHERE
birth_date >= '2010-01-01'
AND birth_date <= '2010-12-31'
Question 9:
Show the first_name, last_name, and height of the
patient with the greatest height.
Solution:
select top 1 first_name,last_name,height from patients order by
height desc
Question 10:
Show all columns for patients who have one of the
following patient_ids:
1,45,534,879,1000
Solution:
select * from patients where patient_id in (1,45,534,879,1000)