SQL 🠊 Online Practice Website 🠊 Hospital Problem Set (EASY)

Q1. Show first name, last name, and gender of patients whose gender is 'M'

SQL Answer for Q1

SELECT first_name, last_name, gender 
FROM patients 
WHERE gender = 'M';

---

Q2. Show first name and last name of patients who does not have allergies. (null)

SQL Answer for Q2

SELECT first_name, last_name
FROM patients 
WHERE allergies IS NULL;

---

Q3. Show first name of patients that start with the letter 'C'

SQL Answer for Q3

SELECT first_name
FROM patients 
WHERE first_name LIKE 'C%';

---

Q4. Show first name and last name of patients that have weight within the range of 100 to 120 (inclusive)

SQL Answer for Q4

SELECT first_name, last_name
FROM patients 
WHERE weight BETWEEN 100 AND 120;

---

Q5. Update the patients table for the allergies column. If the patient's allergies is null then replace it with 'NKA'

SQL Answer for Q5

UPDATE patients
SET allergies = 'NKA'
WHERE allergies IS NULL;

---

Q6. Show first name and last name concatenated into one column to show their full name.

SQL Answer for Q6

SELECT CONCAT(first_name, " ", last_name) AS full_name
FROM patients;

---

Q7. Show first name, last name, and the full province name of each patient. (Example: 'Ontario' instead of 'ON')

SQL Answer for Q7

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

---

Q8. Show how many patients have a birth_date with 2010 as the birth year.

SQL Answer for Q8

SELECT COUNT(*) 
FROM patients
WHERE YEAR(birth_date) = 2010;

---

Q9. Show the first_name, last_name, and height of the patient with the greatest height.

SQL Answer for Q9

SELECT first_name, last_name, height 
FROM patients
WHERE height = (SELECT MAX(height) FROM patients);

---

Q10. Show all columns for patients who have one of the following patient_ids: 1, 45, 534, 879, 1000

SQL Answer for Q10

SELECT * FROM patients
WHERE patient_id IN (1, 45, 534, 879, 1000);

---

Q11. Show the total number of admissions.

SQL Answer for Q11

SELECT COUNT(*) FROM admissions;

---

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

SQL Answer for Q12

SELECT * FROM admissions
WHERE admission_date = discharge_date;

---

Q13. Show the patient id and the total number of admissions for patient_id 579.

SQL Answer for Q13

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

---

Q14. Based on the cities that our patients live in, show unique cities that are in province_id 'NS'?

SQL Answer for Q14

SELECT distinct(city)
FROM patients 
WHERE province_id = 'NS';

---

Q15. 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.

SQL Answer for Q15

SELECT first_name, last_name, birth_date
FROM patients 
WHERE height > 160 AND weight > 70;

---

Q16. 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'

SQL Answer for Q16

SELECT first_name, last_name, allergies
FROM patients
WHERE allergies IS NOT NULL
AND city = 'Hamilton';

---

Q17. 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'

SQL Answer for Q17

SELECT first_name, last_name, allergies
FROM patients
WHERE allergies IS NOT NULL
AND city = 'Hamilton';

---