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

개요

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

출처

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

난이도

•  EASY 

스키마

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

목차

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

---

문제

• 지문
  • Show first name, last name of patients who does not have allergies. (null)
• 현황
  •  COMPLETED 
• 풀이

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

• 정답

SELECT
  first_name,
  last_name,
  height
FROM patients
WHERE height = (
    SELECT max(height)
    FROM patients
  )

분석

---

• 지문
  • Show all columns for patients who have one of the following patient_ids: 1,45,534,879,1000
• 현황
  •  COMPLETED 
• 풀이

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

• 정답

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

 

---

 

•  지문
  • Show the total number of admissions
• 현황
  •  COMPLETED 
• 풀이

SELECT count(*)
FROM admissions

 

• 정답

SELECT COUNT(*) AS total_admissions
FROM admissions;

 

---

• 지문
  • Show all the columns from admissions where the patient was admitted and discharged on the same day.
•  현황
  •  COMPLETED 
• 풀이

SELECT *
FROM admissions
where admission_date = discharge_date

• 정답

SELECT *
FROM admissions
where admission_date = discharge_date;

 

---

• 지문
  • Show the patient id and the total number of admissions for patient_id 579.

• 현황
  •  COMPLETED 

• 풀이

SELECT
	admissions.patient_id,
	COUNT(admissions.patient_id) AS total_admissions
FROM admissions
JOIN patients ON patients.patient_id = admissions.patient_id
where admissions.patient_id = 579;

• 정답

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

 

---

• 지문
  • Based on the cities that our patients live in, show unique cities that are in province_id 'NS'.

• 현황
  •  COMPLETED 

• 풀이

SELECT
	distinct(city) AS unique_cities
FROM patients
where province_id = 'NS';

• 정답

-- Solution (1/2)
SELECT DISTINCT(city) AS unique_cities
FROM patients
WHERE province_id = 'NS';

-- Solution (2/2)
SELECT city
FROM patients
GROUP BY city
HAVING province_id = 'NS';

 

---

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

• 현황
  •  COMPLETED 

• 풀이

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

• 정답

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

---

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

• 현황
  •  COMPLETED 

• 풀이

SELECT
	first_name,
    last_name,
    allergies
FROM patients
where allergies IS NOT NULL and city = 'Hamilton';

• 정답

SELECT
  first_name,
  last_name,
  allergies
FROM patients
WHERE
  city = 'Hamilton'
  and allergies is not null

---

• 지문
  • Show how many patients have a birth_date with 2010 as the birth year.
• 풀이

SELECT COUNT (*)
FROM patients
where year(birth_date) = 2010;

• 정답

-- Solutions (1/3)
SELECT COUNT(*) AS total_patients
FROM patients
WHERE YEAR(birth_date) = 2010;

-- Solutions (2/3)
SELECT count(first_name) AS total_patients
FROM patients
WHERE
  birth_date >= '2010-01-01'
  AND birth_date <= '2010-12-31'
  
-- Solutions (3/3)
SELECT count(first_name) AS total_patients
FROM patients
WHERE
  birth_date between '2010-01-01' AND '2010-12-31'