My solutions for SQL practice exercises
Easy questions
[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 questions
[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 questions
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]
[Hard]