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

개요

• SQL 문법 체득을 위해 sql-practice의 각 문제 풀이 과정을 기록함
• 해당 쿼리들은 MySQL / SQL Server / SQLite 기준으로 작성함

출처

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

난이도

•  HARD 

스키마

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

목차

• 문제
  • 지문
  • 풀이
  • 정답
  • 노트

---

문제

•  지문
  • Show all of the patients grouped into weight groups.
  • Show the total amount of patients in each weight group.
  • Order the list by the weight group decending.
  • For example, if they weight 100 to 109 they are placed in the 100 weight group, 110-119 = 110 weight group, etc.
• 풀이

SELECT
	COUNT(weight) as patients_in_group,
    TRUNCATE(weight, -1) AS weight_group
FROM patients
group by weight_group
order by weight_group DESC;

• 정답

-- Solutions (1/3)
SELECT
  COUNT(*) AS patients_in_group,
  FLOOR(weight / 10) * 10 AS weight_group
FROM patients
GROUP BY weight_group
ORDER BY weight_group DESC;

-- Solutions (2/3)
SELECT
  TRUNCATE(weight, -1) AS weight_group,
  count(*)
FROM patients
GROUP BY weight_group
ORDER BY weight_group DESC;

-- Solutions (3/3)
SELECT
  count(patient_id),
  weight - weight % 10 AS weight_group
FROM patients
GROUP BY weight_group
ORDER BY weight_group DESC

---

•  지문
  • Show patient_id, weight, height, isObese from the patients table.
  • Display isObese as a boolean 0 or 1.
  • Obese is defined as weight(kg)/(height(m)2) >= 30.
  • weight is in units kg.
  • height is in units cm.
• 풀이

SELECT
	patient_id,
    weight,
    height,
    CASE WHEN weight/(height*0.01*height*0.01) >= 30 THEN 1 ELSE 0 END as isObese
FROM patients
order by patient_id;

• 정답

-- Solutions (1/2)
SELECT patient_id, weight, height, 
  (CASE 
      WHEN weight/(POWER(height/100.0,2)) >= 30 THEN
          1
      ELSE
          0
      END) AS isObese
FROM patients;
-- If you divide an int by an int you will get an int. Dividing an int by a float will return a float.
-- That's why you have to divide by 100.0 and not 100.
-- Use CAST(variable_name AS FLOAT) function if you are dividing by two variables.

-- Solutions (2/2)
SELECT
  patient_id,
  weight,
  height,
  weight / power(CAST(height AS float) / 100, 2) >= 30 AS obese
FROM patients

---

•  지문
  • Show patient_id, first_name, last_name, and attending doctor's specialty.
  • Show only the patients who has a diagnosis as 'Epilepsy' and the doctor's first name is 'Lisa'
  • Check patients, admissions, and doctors tables for required information.
• 풀이

SELECT
	p.patient_id,
    p.first_name,
    p.last_name,
    d.specialty
FROM patients p
join admissions a On a.patient_id = p.patient_id
join doctors d ON d.doctor_id = a.attending_doctor_id
where a.diagnosis = 'Epilepsy' AND d.first_name = 'Lisa';

• 정답

-- Solutions (1/4)
SELECT
  p.patient_id,
  p.first_name AS patient_first_name,
  p.last_name AS patient_last_name,
  ph.specialty AS attending_doctor_specialty
FROM patients p
  JOIN admissions a ON a.patient_id = p.patient_id
  JOIN doctors ph ON ph.doctor_id = a.attending_doctor_id
WHERE
  ph.first_name = 'Lisa' and
  a.diagnosis = 'Epilepsy'

-- Solutions (2/4)
SELECT
  pa.patient_id,
  pa.first_name,
  pa.last_name,
  ph1.specialty
FROM patients AS pa
  JOIN (
    SELECT *
    FROM admissions AS a
      JOIN doctors AS ph ON a.attending_doctor_id = ph.doctor_id
  ) AS ph1 USING (patient_id)
WHERE
  ph1.diagnosis = 'Epilepsy'
  AND ph1.first_name = 'Lisa'

-- Solutions (3/4)
SELECT
  a.patient_id,
  a.first_name,
  a.last_name,
  b.specialty
FROM
  patients a,
  doctors b,
  admissions c
WHERE
  a.patient_id = c.patient_id
  AND c.attending_doctor_id = b.doctor_id
  AND c.diagnosis = 'Epilepsy'
  AND b.first_name = 'Lisa';

-- Solutions (4/4)
with patient_table as (
    SELECT
      patients.patient_id,
      patients.first_name,
      patients.last_name,
      admissions.attending_doctor_id
    FROM patients
      JOIN admissions ON patients.patient_id = admissions.patient_id
    where
      admissions.diagnosis = 'Epilepsy'
  )
select
  patient_table.patient_id,
  patient_table.first_name,
  patient_table.last_name,
  doctors.specialty
from patient_table
  JOIN doctors ON patient_table.attending_doctor_id = doctors.doctor_id
WHERE doctors.first_name = 'Lisa';

---

•  지문
  • All patients who have gone through admissions, can see their medical documents on our site. Those patients are given a temporary password after their first admission. Show the patient_id and temp_password.
  • The password must be the following, in order:
    • patient_id
    • the numerical length of patient's last_name
    • year of patient's birth_date
• 풀이

SELECT
	p.patient_id,
    CONCAT(p.patient_id, LEN(last_name), year(birth_date)) as temp_password
FROM patients p
join admissions a ON a.patient_id = p.patient_id
group by p.patient_id;

• 정답

-- Solutions (1/3)
SELECT
  DISTINCT P.patient_id,
  CONCAT(
    P.patient_id,
    LEN(last_name),
    YEAR(birth_date)
  ) AS temp_password
FROM patients P
  JOIN admissions A ON A.patient_id = P.patient_id

-- Solutions (2/3)
select
  distinct p.patient_id,
  p.patient_id || floor(len(last_name)) || floor(year(birth_date)) as temp_password
from patients p
  join admissions a on p.patient_id = a.patient_id

-- Solutions (3/3)
select
  pa.patient_id,
  ad.patient_id || floor(len(pa.last_name)) || floor(year(pa.birth_date)) as temp_password
from patients pa
  join admissions ad on pa.patient_id = ad.patient_id
group by pa.patient_id;

---

•  지문
  • Each admission costs $50 for patients without insurance, and $10 for patients with insurance. All patients with an even patient_id have insurance.
  • Give each patient a 'Yes' if they have insurance, and a 'No' if they don't have insurance. Add up the admission_total cost for each has_insurance group.
• 풀이

SELECT
	case when patient_id%2 = 0 then "Yes" ELSE "No" END as has_insurance,
    case When patient_id%2 = 0 THen COUNT(admission_date)*10 ELse count(admission_date)*50 END as cost_after_insurance
FROM admissions
group by has_insurance;

• 정답

-- Solutions (1/4)
SELECT 
CASE WHEN patient_id % 2 = 0 Then 
    'Yes'
ELSE 
    'No' 
END as has_insurance,
SUM(CASE WHEN patient_id % 2 = 0 Then 
    10
ELSE 
    50 
END) as cost_after_insurance
FROM admissions 
GROUP BY has_insurance;

-- Solutions (2/4)
select 'No' as has_insurance, count(*) * 50 as cost
from admissions where patient_id % 2 = 1 group by has_insurance
union
select 'Yes' as has_insurance, count(*) * 10 as cost
from admissions where patient_id % 2 = 0 group by has_insurance

-- Solutions (3/4)
SELECT
  has_insurance,
  CASE
    WHEN has_insurance = 'Yes' THEN COUNT(has_insurance) * 10
    ELSE count(has_insurance) * 50
  END AS cost_after_insurance
FROM (
    SELECT
      CASE
        WHEN patient_id % 2 = 0 THEN 'Yes'
        ELSE 'No'
      END AS has_insurance
    FROM admissions
  )
GROUP BY has_insurance

-- Solutions (4/4)
select has_insurance,sum(admission_cost) as admission_total
from
(
   select patient_id,
   case when patient_id % 2 = 0 then 'Yes' else 'No' end as has_insurance,
   case when patient_id % 2 = 0 then 10 else 50 end as admission_cost
   from admissions
)
group by has_insurance

---

•  지문
  • Show the provinces that has more patients identified as 'M' than 'F'. Must only show full province_name
• 풀이

SELECT
	province_name
FROM province_names pv
JOIN patients p ON pv.province_id = p.province_id
group by province_name
having COUNT(CAse when gender='M' THEN 1 END) > count(CAse When gender='F' THEN 1 END)

• 정답

-- Solutions (1/6)
SELECT pr.province_name
FROM patients AS pa
  JOIN province_names AS pr ON pa.province_id = pr.province_id
GROUP BY pr.province_name
HAVING
  COUNT( CASE WHEN gender = 'M' THEN 1 END) > COUNT( CASE WHEN gender = 'F' THEN 1 END);
  
-- Solutions (2/6)
SELECT province_name
FROM (
    SELECT
      province_name,
      SUM(gender = 'M') AS n_male,
      SUM(gender = 'F') AS n_female
    FROM patients pa
      JOIN province_names pr ON pa.province_id = pr.province_id
    GROUP BY province_name
  )
WHERE n_male > n_female

-- Solutions (3/6)
SELECT pr.province_name
FROM patients AS pa
  JOIN province_names AS pr ON pa.province_id = pr.province_id
GROUP BY pr.province_name
HAVING
  SUM(gender = 'M') > SUM(gender = 'F');
  
-- Solutions (4/6)
SELECT province_name
FROM patients p
  JOIN province_names r ON p.province_id = r.province_id
GROUP BY province_name
HAVING
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE -1 END) > 0

-- Solutions (5/6)
SELECT pr.province_name
FROM patients AS pa
  JOIN province_names AS pr ON pa.province_id = pr.province_id
GROUP BY pr.province_name
HAVING
  COUNT( CASE WHEN gender = 'M' THEN 1 END) > COUNT(*) * 0.5;

-- Solutions (6/6)
SELECT province_name from province_names
WHERE province_id IN 
(SELECT province_id
FROM patients
group by province_id 
having SUM(gender = 'M') > SUM(gender = 'F'))

---

•  지문
  • We are looking for a specific patient. Pull all columns for the patient who matches the following criteria:
    • First_name contains an 'r' after the first two letters.
    • Identifies their gender as 'F'
    • Born in February, May, or December
    • Their weight would be between 60kg and 80kg
    • Their patient_id is an odd number
    • They are from the city 'Kingston'
• 풀이

SELECT
	*
FROM patients
where first_name LIKE '__r%' AND
		month(birth_date) IN (2, 5, 12) AND
        weight between 60 AND 80 ANd
        patient_id % 2 = 1 AND
        city = 'Kingston'

• 정답

SELECT *
FROM patients
WHERE
  first_name LIKE '__r%'
  AND gender = 'F'
  AND MONTH(birth_date) IN (2, 5, 12)
  AND weight BETWEEN 60 AND 80
  AND patient_id % 2 = 1
  AND city = 'Kingston';

 

• 노트

MONTH(·)는 정수를 반환

 

---

• 지문
  • Show the percent of patients that have 'M' as their gender. Round the answer to the nearest hundreth number and in percent form.
• 풀이

SELECT
	ROUND(CAST(SUM(case When gender='M' then 1 ELse 0 END) AS FLOAT) / CAST(COUNT(*) AS FLOAT) * 100, 2) || '%' as percent_of_male_patients
from patients

• 정답

-- Solutions (1/3)
SELECT CONCAT(
    ROUND(
      (
        SELECT COUNT(*)
        FROM patients
        WHERE gender = 'M'
      ) / CAST(COUNT(*) as float),
      4
    ) * 100,
    '%'
  ) as percent_of_male_patients
FROM patients;

-- Solutions (2/3)
SELECT
  round(100 * avg(gender = 'M'), 2) || '%' AS percent_of_male_patients
FROM
  patients;

-- Solutions (3/3)
SELECT 
   CONCAT(ROUND(SUM(gender='M') / CAST(COUNT(*) AS float), 4) * 100, '%')
FROM patients;

• 노트

CAST( * AS *)

형변환!

---

• 지문
  • For each day display the total amount of admissions on that day. Display the amount changed from the previous date.
• 풀이

SELECT
	admission_date,
    COUNT(admission_date),
    COUNT(patient_id) - LAG(COUNT(patient_id)) OVER (order BY admission_date)
FROM
	admissions
group by admission_date

• 정답

# Solutions (1/2)
SELECT
 admission_date,
 count(admission_date) as admission_day,
 count(admission_date) - LAG(count(admission_date)) OVER(ORDER BY admission_date) AS admission_count_change 
FROM admissions
 group by admission_date
 
 # Solutions (2/2)
 WITH admission_counts_table AS (
  SELECT admission_date, COUNT(patient_id) AS admission_count
  FROM admissions
  GROUP BY admission_date
  ORDER BY admission_date DESC
)
select
  admission_date, 
  admission_count, 
  admission_count - LAG(admission_count) OVER(ORDER BY admission_date) AS admission_count_change 
from admission_counts_table

• 노트
  • 예전에 못풀었는데 sqld 공부하면서 함수들을 익히고 최근 다시 보니 쉬운 문제였음

---

• 지문
  • Sort the province names in ascending order in such a way that the province 'Ontario' is always on top.
• 풀이

SELECT province_name
from province_names
order by CASE WHEN province_name="Ontario" THEN 1 ELSe 2 END, province_name

• 정답

-- Solutions (1/4)
select province_name
from province_names
order by
  (case when province_name = 'Ontario' then 0 else 1 end),
  province_name
  
-- Solutions (2/4)
select province_name
from province_names
order by
  (not province_name = 'Ontario'),
  province_name

-- Solutions (3/4)
select province_name
from province_names
order by
  province_name = 'Ontario' desc,
  province_name

-- Solutions (4/4)
SELECT province_name
FROM province_names
ORDER BY
  CASE
    WHEN province_name = 'Ontario' THEN 1
    ELSE province_name
  END

---

•  지문
  • We need a breakdown for the total amount of admissions each doctor has started each year. Show the doctor_id, doctor_full_name, specialty, year, total_admissions for that year.
• 풀이

SELECT
	doctor_id,
    CONCAT(first_name, ' ', last_name) As doctor_name,
    specialty,
    YEAR(admission_date) as selected_year,
    COUNT(YEAR(admission_date)) as total_admissions
FROM admissions a
join doctors d ON a.attending_doctor_id = d.doctor_id
group by doctor_id, doctor_name, specialty, selected_year

• 정답

SELECT
  d.doctor_id as doctor_id,
  CONCAT(d.first_name,' ', d.last_name) as doctor_name,
  d.specialty,
  YEAR(a.admission_date) as selected_year,
  COUNT(*) as total_admissions
FROM doctors as d
  LEFT JOIN admissions as a ON d.doctor_id = a.attending_doctor_id
GROUP BY
  doctor_name,
  selected_year
ORDER BY doctor_id, selected_year

---