solutions_easy

#Show first name, last name, and gender of patients whose gender is 'M'.
Select first_name, last_name, gender
From patients
Where gender = 'M';

#Show first name and last name of patients who does not have allergies (null).
Select first_name, last_name
From patients
Where allergies IS NULL;

#Show first name of patients that start with the letter 'C'.
Select first_name
From patients
Where first_name LIKE 'C%';

#Show first name and last name of patients that weight within the range of 100 to 120 (inclusive).
Select first_name, last_name
From patients
Where weight between 100 and 120;

#Update the patients table for the allergies column. If the patient's allergies is null then replace it with 'NKA'.
UPDATE patients
set allergies = 'NKA'
Where allergies IS NULL;

#Show first name and last name concatinated into one column to show their full name.
SELECT concat (first_name, ' ', last_name) AS full_name
FROM patients;

#Show first name, last name, and the full province name of each patient. Example: 'Ontario' instead of 'ON'.
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;

#Show how many patients have a birth_date with 2010 as the birth year.
SELECT Count ()
From patients
Where year(birth_date) = 2010;

#Show the first_name, last_name, and height of the patient with the greatest height.
SELECT first_name, last_name, Max(height)
From patients;

#Show all columns for patients who have one of the following patient_ids: 1,45,534,879,1000.
SELECT *
From patients
Where patient_id IN (1,45,534,879,1000);

#Show the total number of admissions.
SELECT COUNT (*)
From admissions;

#Show all the columns from admissions where the patient was admitted and discharged on the same day.
SELECT * 
FROM admissions
Where admission_date = discharge_date;

#Show the patient id and the total number of admissions for patient_id 579.
SELECT patient_id, COUNT (patient_id) AS total_admissions
FROM admissions
Where  patient_id =  579;

#Based on the cities that our patients live in, show unique cities that are in province_id 'NS'?
SELECT DISTINCT (city) AS unique_cities
FROM patients
Where  province_id = 'NS';

#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.
SELECT first_name, last_name, birth_date
FROM patients
Where  height > 160 and weight > 70;

#Write a query to find list of patients first_name, last_name, and allergies from Hamilton where allergies are not null.
SELECT first_name, last_name, allergies
FROM patients
Where city = "Hamilton" AND allergies IS NOT NULL;

#End of Code