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

개요

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

출처

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

난이도

•  MEDIUM 

스키마

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

목차

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

---

문제

• 지문
  • Show unique birth years from patients and order them by ascending.
• 현황
  •  COMPLETED 
• 풀이

SELECT distinct(YEAR(birth_date))
FROM patients
order by birth_date;

• 정답

-- Solution (1/2)
SELECT
  DISTINCT YEAR(birth_date) AS birth_year
FROM patients
ORDER BY birth_year;

-- Solution (2/2)
SELECT year(birth_date)
FROM patients
GROUP BY year(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 
(SELECT first_name, COUNT(first_name) AS cnt 
 FROM patients
  GROUP BY first_name
  having cnt = 1);

• 정답

-- Solution (1/2)
SELECT first_name
FROM patients
GROUP BY first_name
HAVING COUNT(first_name) = 1;

-- Solution (2/2)
SELECT first_name
FROM (
    SELECT
      first_name,
      count(first_name) AS occurrencies
    FROM patients
    GROUP BY first_name
  )
WHERE occurrencies = 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 first_name LIKE 's%s' AND LEN(first_name) >= 6;

• 정답

-- Solutions (1/3)
SELECT
  patient_id,
  first_name
FROM patients
WHERE first_name LIKE 's____%s';

-- Solutions (2/3)
SELECT
  patient_id,
  first_name
FROM patients
WHERE
  first_name LIKE 's%s'
  AND len(first_name) >= 6;
  
-- Solutions (3/3)
SELECT
  patient_id,
  first_name
FROM patients
where
  first_name like 's%'
  and first_name like '%s'
  and len(first_name) >= 6;

---

• 지문
  • 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 admissions.diagnosis = 'Dementia';

• 정답

-- Solutions (1/3)
SELECT
  patients.patient_id,
  first_name,
  last_name
FROM patients
  JOIN admissions ON admissions.patient_id = patients.patient_id
WHERE diagnosis = 'Dementia';

-- Solutions (2/3)
SELECT
  patient_id,
  first_name,
  last_name
FROM patients
WHERE patient_id IN (
    SELECT patient_id
    FROM admissions
    WHERE diagnosis = 'Dementia'
  );
  
-- Solutions (3/3)
SELECT
  patient_id,
  first_name,
  last_name
FROM patients p
WHERE 'Dementia' IN (
    SELECT diagnosis
    FROM admissions
    WHERE admissions.patient_id = p.patient_id
  );

---

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

• 정답

SELECT first_name
FROM patients
ORDER BY
  len(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(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) as male_count,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM patients

• 정답

-- Solutions (1/3)
SELECT 
  (SELECT count(*) FROM patients WHERE gender='M') AS male_count, 
  (SELECT count(*) FROM patients WHERE gender='F') AS female_count;
  
-- Solutions (2/3)
SELECT 
  SUM(Gender = 'M') as male_count, 
  SUM(Gender = 'F') AS female_count
FROM patients

-- Solutions (3/3)
select 
  sum(case when gender = 'M' then 1 end) as male_count,
  sum(case when gender = 'F' then 1 end) 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, first_name, last_name;

• 정답

-- Solutions (1/2)
SELECT
  first_name,
  last_name,
  allergies
FROM patients
WHERE
  allergies IN ('Penicillin', 'Morphine')
ORDER BY
  allergies,
  first_name,
  last_name;
  
-- Solutions (2/2)
SELECT
  first_name,
  last_name,
  allergies
FROM
  patients
WHERE
  allergies = 'Penicillin'
  OR allergies = '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(patient_id) > 1;

• 정답

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(*) as num_patients
FROM patients
group by city
order by
	num_patients desc,
    city;

• 정답

SELECT
  city,
  COUNT(*) 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,
    CASe WHEN patient_id THEN 'Patient' ELSE 0 END as role
from patients
union ALL
select
	first_name,
    last_name,
    case when doctor_id Then 'Doctor' ELSE 0 END AS role
from doctors

• 정답

SELECT first_name, last_name, 'Patient' as role FROM patients
    union all
select first_name, last_name, 'Doctor' from doctors;

---

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

• 정답

-- Solutions (1/3)
SELECT
  allergies,
  COUNT(*) AS total_diagnosis
FROM patients
WHERE
  allergies IS NOT NULL
GROUP BY allergies
ORDER BY total_diagnosis DESC

-- Solutions (2/3)
SELECT
  allergies,
  count(*)
FROM patients
WHERE allergies NOT NULL
GROUP BY allergies
ORDER BY count(*) DESC

-- Solutions (3/3)
SELECT
  allergies,
  count(allergies) AS total_diagnosis
FROM patients
GROUP BY allergies
HAVING
  allergies IS NOT NULL
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;

• 정답

-- Solutions (1/3)
SELECT
  first_name,
  last_name,
  birth_date
FROM patients
WHERE
  YEAR(birth_date) BETWEEN 1970 AND 1979
ORDER BY birth_date ASC;

-- Solutions (2/3)
SELECT
  first_name,
  last_name,
  birth_date
FROM patients
WHERE
  birth_date >= '1970-01-01'
  AND birth_date < '1980-01-01'
ORDER BY birth_date ASC

-- Solutions (3/3)
SELECT
  first_name,
  last_name,
  birth_date
FROM patients
WHERE year(birth_date) LIKE '197%'
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
	UPPER(last_name) || ',' || LOWER(first_name)
from patients
order by first_name desc

• 정답

-- Solutions (1/2)
SELECT
  CONCAT(UPPER(last_name), ',', LOWER(first_name)) AS new_name_format
FROM patients
ORDER BY first_name DESC;

-- Solutions (2/2)
SELECT
  UPPER(last_name) || ',' || LOWER(first_name) AS new_name_format
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,
    SUM(height)
from patients
group by province_id
having SUM(height) >= 7000;

• 정답

-- Solutions (1/2)
SELECT
  province_id,
  SUM(height) AS sum_height
FROM patients
GROUP BY province_id
HAVING sum_height >= 7000

-- Solutions (2/2)
select * from (select province_id, SUM(height) as sum_height FROM patients group by province_id) where sum_height >= 7000;

---

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

• 정답

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
group by day(admission_date)
order by number_of_admissions desc;

• 정답

SELECT
  DAY(admission_date) AS day_number,
  COUNT(*) AS number_of_admissions
FROM admissions
GROUP BY day_number
ORDER BY number_of_admissions DESC

---