[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'

[Easy] 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

[Easy] Show first name of patients that start with the letter 'C'

SELECT first_name
FROM patients
WHERE first_name like "C%"

[Easy] 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 >= 100 and weight <= 120

[Easy] 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;

[Easy] 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

[Easy] Show first name, last name, and the full province name of each patient. Example: 'Ontario' instead of 'ON'

SELECT patients.first_name, patients.last_name, province_names.province_name
FROM patients
JOIN province_names
ON patients.province_id = province_names.province_id

[Easy] Show how many patients have a birth_date with 2010 as the birth year.

SELECT COUNT(birth_date)
FROM patients
WHERE YEAR(birth_date) is 2010

[Easy] Show the first_name, last_name, and height of the patient with the greatest height.

SELECT first_name, last_name, max(height) 
FROM patients

[Easy] 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)

[Easy] Show the total number of admissions

SELECT COUNT(patient_id)
FROM admissions

[Easy] Show all the columns from admissions where the patient was admitted and discharged on the same day.

SELECT *
FROM admissions
WHERE discharge_date = admission_date

[Easy] Show the patient id and the total number of admissions for patient_id 579.

SELECT patient_id, COUNT(patient_id)
FROM admissions
WHERE patient_id = 579

[Easy] Based on the cities that our patients live in, show unique cities that are in province_id 'NS'?

SELECT DISTINCT(city)
FROM patients
WHERE province_id IS "NS"

[Easy] 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

[Easy] Write a query to find list of patients first_name, last_name, and allergies where allergies are not null and are from the city of 'Hamilton'

SELECT first_name, last_name, allergies
FROM patients
WHERE allergies NOT null and city is "Hamilton"

[Medium] Show unique birth years from patients and order them by ascending.

SELECT DISTINCT(YEAR(birth_date)) 
FROM patients
ORDER BY birth_date ASC

[Medium] 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.

SELECT first_name
FROM patients
GROUP BY first_name
HAVING COUNT(*) = 1

[Medium] Show patient_id and first_name from patients where their first_name start and ends with 's' and is at least 6 characters long.

SELECT patient_id, first_name
FROM patients
WHERE first_name LIKE "s%s" AND LEN(first_name) >= 6

[Medium] Show patient_id, first_name, last_name from patients whos diagnosis is 'Dementia'. Primary diagnosis is stored in the admissions table.

SELECT patients.patient_id, patients.first_name, patients.last_name
FROM patients
JOIN admissions
ON patients.patient_id = admissions.patient_id
WHERE diagnosis IS 'Dementia'

[Medium] Display every patient's first_name. Order the list by the length of each name and then by alphabetically.

SELECT first_name
FROM patients
ORDER BY LEN(first_name), first_name ASC

[Medium] 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.

SELECT
(SELECT COUNT(*) FROM patients WHERE gender IS 'M') AS male_count,
(SELECT COUNT(*) FROM patients WHERE gender IS 'F') AS female_count;

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Medium]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]

[Hard]