[sql-practice] SQL 문제 풀이 (4)

개요

• SQL 문법 체득을 위해 sql-practice의 각 문제 풀이 과정을 기록함

출처

• https://www.sql-practice.com/

난이도

•  MEDIUM 

스키마

schema of hospital.db from https://www.sql-practice.com/

목차

• 문제
  • 지문
  • 난이도
  • 출처
  • 현황
  • 풀이
  • 정답
  • 개선할 점

---

문제

•  지문
  • Show all columns for patient_id 542's most recent admission_date.
• 풀이

select
	 *
from admissions
where patient_id = 542
group by patient_id
having admission_date = MAX(admission_date);

• 정답

-- Solutions (1/4)
SELECT *
FROM admissions
WHERE patient_id = 542
GROUP BY patient_id
HAVING
  admission_date = MAX(admission_date);
  
-- Solutions (2/4)
SELECT *
FROM admissions
WHERE
  patient_id = '542'
  AND admission_date = (
    SELECT MAX(admission_date)
    FROM admissions
    WHERE patient_id = '542'
  )

-- Solutions (3/4)
SELECT *
FROM admissions
WHERE patient_id = 542
ORDER BY admission_date DESC
LIMIT 1

-- Solutions (4/4)
SELECT *
FROM admissions
GROUP BY patient_id
HAVING
  patient_id = 542
  AND max(admission_date)

---

• 지문
  • 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);

• 정답

SELECT
  patient_id,
  attending_doctor_id,
  diagnosis
FROM admissions
WHERE
  (
    attending_doctor_id IN (1, 5, 19)
    AND patient_id % 2 != 0
  )
  OR 
  (
    attending_doctor_id LIKE '%2%'
    AND len(patient_id) = 3
  )

• 개선할 점

문자열 비교에는  LIKE  사용,  =  사용 불가!

---

• 지문
  • Show first_name, last_name, and the total number of admissions attended for each doctor.
  • Every admission has been attended by a doctor.
• 풀이

select
	 doctors.first_name,
     doctors.last_name,
     COUNT(admission_date) as admissions_total
FROM admissions
join doctors ON admissions.attending_doctor_id = doctors.doctor_id
group by doctor_id;

• 정답

-- Solutions (1/2)
SELECT
  first_name,
  last_name,
  count(*) as admissions_total
from admissions a
  join doctors ph on ph.doctor_id = a.attending_doctor_id
group by attending_doctor_id

-- Solutions (2/2)
SELECT
  first_name,
  last_name,
  count(*)
from
  doctors p,
  admissions a
where
  a.attending_doctor_id = p.doctor_id
group by p.doctor_id;

---

• 지문
  • For each doctor, display their id, full name, and the first and last admission date they attended.
• 풀이

select
	 doctors.doctor_id,
     doctors.first_name || ' ' || doctors.last_name,
     MIN(admission_date) as first_admission_date,
     MAX(admission_date) AS last_admission_date
FROM admissions
join doctors ON admissions.attending_doctor_id = doctors.doctor_id
group by doctor_id;

• 정답

select
  doctor_id,
  first_name || ' ' || last_name as full_name,
  min(admission_date) as first_admission_date,
  max(admission_date) as last_admission_date
from admissions a
  join doctors ph on a.attending_doctor_id = ph.doctor_id
group by doctor_id;

---

• 지문
  • Display the total amount of patients for each province. Order by descending.
• 풀이

select
	province_name,
	 COUNT(patient_id) as patient_count
FROM patients
join province_names ON patients.province_id = province_names.province_id
group by province_name
order by patient_count DESC;

• 정답

SELECT
  province_name,
  COUNT(*) as patient_count
FROM patients pa
  join province_names pr on pr.province_id = pa.province_id
group by pr.province_id
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
	patients.first_name || ' ' || patients.last_name as patient_name,
	diagnosis,
    doctors.first_name || ' ' || doctors.last_name AS doctor_name
FROM patients
join admissions ON patients.patient_id = admissions.patient_id
join doctors ON doctors.doctor_id = admissions.attending_doctor_id;

• 정답

SELECT
  CONCAT(patients.first_name, ' ', patients.last_name) as patient_name,
  diagnosis,
  CONCAT(doctors.first_name,' ',doctors.last_name) as doctor_name
FROM patients
  JOIN admissions ON admissions.patient_id = patients.patient_id
  JOIN doctors ON doctors.doctor_id = admissions.attending_doctor_id;

---

• 지문
  • display the first name, last name and number of duplicate patients based on their first name and last name.
  • Ex: A patient with an identical name can be considered a duplicate.
• 풀이

select
	first_name,
    last_name,
    COUNT(concat(first_name, last_name)) as number_of_duplicates
FROM patients
group by first_name, last_name
having number_of_duplicates > 1;

• 정답

select
  first_name,
  last_name,
  count(*) as num_of_duplicates
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,
    round(weight * 2.205, 0) As weight,
    birth_date,
    (CASE WHEN gender = 'M' THen 'MALE' ELSE 'FEMALE' END) as gender_type
FROM patients

• 정답

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' 
  ELSE 'FEMALE' 
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
	patient_id,
    first_name,
    last_name
FROM patients
WHERE patients.patient_id NOt In (SELECT patient_id FROm admissions)

• 정답 

-- Solutions (1/2)
SELECT
  patients.patient_id,
  first_name,
  last_name
from patients
where patients.patient_id not in (
    select admissions.patient_id
    from admissions
  )

-- Solutions (2/2)
SELECT
  patients.patient_id,
  first_name,
  last_name
from patients
  left join admissions on patients.patient_id = admissions.patient_id
where admissions.patient_id is NULL

---

• 지문
  • Display a single row with max_visits, min_visits, average_visits where the maximum, minimum and average number of admissions per day is calculated. Average is rounded to 2 decimal places.
• 풀이

select
	Max(visits) as max_visits,
    MIN(visits) as min_visits,
    ROUND(AVG(visits),2) as average_visits
FROM (select
	COUNT(admission_date) as visits
      FROM admissions
	GROup by admission_date) as admissions

• 정답

select 
	max(number_of_visits) as max_visits, 
	min(number_of_visits) as min_visits, 
  round(avg(number_of_visits),2) as average_visits 
from (
  select admission_date, count(*) as number_of_visits
  from admissions 
  group by admission_date
)

---

• 지문
  • Display every patient that has at least one admission and show their most recent admission along with the patient and doctor's full name.
• 풀이

select
	CONCAT(p.first_name, ' ', p.last_name) as patient_name,
    admission_date,
    concat(d.first_name, ' ', d.last_name) as doctor_name
FROM patients p
join admissions a ON p.patient_id = a.patient_id
join doctors d ON a.attending_doctor_id = d.doctor_id
group by p.patient_id
having admission_date = max(admission_date)

• 정답

SELECT 
    p.first_name || ' ' || p.last_name AS patient_name,
    a.admission_date,
    d.first_name || ' ' || d.last_name AS doctor_name
FROM patients p
JOIN admissions a ON p.patient_id = a.patient_id
JOIN doctors d ON a.attending_doctor_id = d.doctor_id
WHERE a.admission_date = (
    SELECT MAX(a2.admission_date)
    FROM admissions a2
    WHERE a2.patient_id = p.patient_id
);

---