solutions_medium

#Show unique birth years from patients and order them by ascending.
SELECT distinct YEAR(birth_date) AS birth_year
FROM patients
ORDER BY birth_date;

#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(first_name) = 1;

#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 LENGTH(first_name) >= 6
  AND first_name LIKE 's%s';

#Show patient_id, first_name, last_name from patients whos diagnosis is 'Dementia'.
#Primary diagnosis is stored in the admissions table.
SELECT p.patient_id, p.first_name, p.last_name
FROM patients As p
JOIN admissions AS a ON a.patient_id=p.patient_id
WHERE diagnosis = 'Dementia';

#Display every patient's first_name.
#Order the list by the length of each name and then by alphbetically.
SELECT first_name
FROM patients 
ORDER BY Length (first_name), first_name;

#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
    SUM(gender = 'M') AS male_count,
    SUM(gender = 'F') AS female_count
FROM patients;

#Show first and last name, allergies from patients which have allergies to either 'Penicillin' or 'Morphine'. 
#Show results ordered ascending by allergies then by first_name then by last_name.
SELECT first_name, last_name, allergies
FROM patients
WHERE allergies IN ('Penicillin', 'Morphine')
ORDER BY allergies ASC, first_name ASC, last_name ASC;

#Show patient_id, diagnosis from admissions. Find patients admitted multiple times for the same diagnosis.
SELECT patient_id, diagnosis
FROM admissions
GROUP BY patient_id, diagnosis
HAVING COUNT(*) > 1;

#Show the city and the total number of patients in the city. Order from most to least patients and then by city name ascending.
SELECT city, COUNT(patient_id) as num_patients
FROM patients
GROUP BY city
ORDER BY num_patients DESC, city Asc;

#Show first name, last name and role of every person that is either patient or doctor. The roles are either "Patient" or "Doctor"
SELECT first_name, last_name, 'Patient' AS role
FROM patients
WHERE role = 'Patient'
UNION ALL
SELECT first_name, last_name, 'Doctor' AS role
FROM doctors
WHERE role = 'Doctor';

#Show all allergies ordered by popularity. Remove NULL values from query.
SELECT allergies, COUNT(*) AS total_diagnosis
FROM patients
WHERE allergies IS NOT NULL
GROUP BY allergies
ORDER BY total_diagnosis DESC;

#Show all patient's first_name, last_name, and birth_date who were born in the 1970s decade. 
#Sort the list starting from the earliest birth_date.
SELECT first_name, last_name, birth_date
FROM patients
WHERE YEAR (birth_date) between 1970 AND 1979
ORDER BY birth_date ASC;

#We want to display each patient's full name in a single column. 
#Their last_name in all upper letters must appear first, then first_name in all lower case letters. 
#Separate the last_name and first_name with a comma. Order the list by the first_name in decending order. EX: SMITH,jane
SELECT CONCAT(UPPER(last_name), ',', LOWER(first_name)) AS full_name
FROM patients
ORDER BY first_name DESC;

#Show the province_id(s), sum of height; where the total sum of its patient's height is greater than or equal to 7,000.
SELECT province_id, CEIL(SUM(height)) AS sum_height
FROM patients
GROUP BY province_id
HAVING sum_height >= 7000
ORDER BY sum_height DESC;

#Show the difference between the largest weight and smallest weight for patients with the last name 'Maroni'
SELECT (MAX(weight) - MIN(weight)) AS weight_delta
FROM patients
WHERE last_name = 'Maroni';

#Show all of the days of the month (1-31) and how many admission_dates occurred on that day. 
#Sort by the day with most admissions to least admissions.
SELECT DAY(admission_date) AS day_number, COUNT(*) AS number_of_admissions
FROM admissions
WHERE MONTH(admission_date) BETWEEN 1 and 31
GROUP BY day_number
ORDER BY number_of_admissions DESC;

#Show all columns for patient_id 542's most recent admission_date.
SELECT *
FROM admissions
WHERE patient_id = 542
GROUP BY admission_date
ORDER BY admission_date DESC
LIMIT 1;

#Show patient_id, attending_doctor_id, and diagnosis for admissions that match one of the two criteria:
#1. patient_id is an odd number and attending_doctor_id is either 1, 5, or 19.
#2. attending_doctor_id contains a 2 and the length of patient_id is 3 characters.
SELECT patient_id, attending_doctor_id, diagnosis
FROM admissions
WHERE
  (patient_id % 2 = 1 AND attending_doctor_id IN (1, 5, 19))
  OR
  (attending_doctor_id LIKE '%2%' AND LEN(patient_id) = 3);

#Show first_name, last_name, and the total number of admissions attended for each doctor.
#Every admission has been attended by a doctor.
SELECT d.first_name, d.last_name, COUNT(a.patient_id) AS admissions_total
FROM doctors as d
JOIN admissions AS a ON d.doctor_id = a.attending_doctor_id
GROUP BY d.doctor_id, d.first_name, d.last_name;

#For each doctor, display their id, full name, and the first and last admission date they attended.
SELECT d.doctor_id AS doctor_id, 
CONCAT(d.first_name, ' ', d.last_name) AS full_name,
    MIN(a.admission_date) AS first_admission_date,
    MAX(a.admission_date) AS last_admission_date
FROM doctors as d
JOIN admissions AS a ON d.doctor_id = a.attending_doctor_id
GROUP BY d.doctor_id, full_name;

#Display the total amount of patients for each province. Order by descending.
SELECT COUNT(p.patient_id) AS patient_count, n.province_name 
FROM patients as p 
JOIN province_names AS n ON p.province_id = n.province_id
GROUP BY n.province_name 
ORDER BY patient_count DESC;

#For every admission, display the patient's full name, their admission diagnosis, and their doctor's full name who diagnosed their problem.
SELECT
    CONCAT(p.first_name, ' ', p.last_name) AS patient_full_name,
    a.diagnosis AS admission_diagnosis,
    CONCAT(d.first_name, ' ', d.last_name) AS doctor_full_name
FROM admissions a
JOIN patients p ON a.patient_id = p.patient_id
JOIN doctors d ON a.attending_doctor_id = d.doctor_id;

#Display the number of duplicate patients based on their first_name and last_name.
SELECT first_name, last_name, COUNT(*) AS duplicate_count
FROM patients
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

#Display patient's full name,
#height in the units feet rounded to 1 decimal,
#weight in the unit pounds rounded to 0 decimals,
#birth_date,
#gender non abbreviated.
#Convert CM to feet by dividing by 30.48.
#Convert KG to pounds by multiplying by 2.205.
SELECT
    CONCAT(first_name, ' ', last_name) AS patient_name,
    ROUND(height / 30.48, 1) AS "height 'Feet'",
    ROUND(weight * 2.205, 0) AS "weight 'Pounds'",
    birth_date,
    CASE
        WHEN gender = 'M' THEN 'Male'
        WHEN gender = 'F' THEN 'Female'
        ELSE 'Other'
    END AS gender_type
FROM patients;

#Show patient_id, first_name, last_name from patients whose does not have any records in the admissions table. 
#(Their patient_id does not exist in any admissions.patient_id rows.)
SELECT p.patient_id, p.first_name, p.last_name
FROM patients AS p
LEFT JOIN admissions AS a ON p.patient_id = a.patient_id
WHERE a.patient_id IS NULL;

#End of Code