231208 / SQL&Python 예제 풀이

SQL 예제 풀이

 

- 동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 SQL문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.

select ANIMAL_ID
from ANIMAL_INS
where NAME is null
order by ANIMAL_ID

 

 

- USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력하는 SQL문을 작성해주세요.

 

2021년에 가입한 회원을 어떻게 필터링할지 고민하다가 LIKE 연산자를 사용해 2021을 포함하는 결과만 조건문으로 필터링해 주었다. 이 방법 외에도 BETWEEN, SUBSTR, DATE_FORMAT, YEAR 등 다양한 함수를 이용해 2021년이라는 조건을 필터링해 줄 수 있다.

다른 사람의 풀이 중 TO CHAR이라는 함수를 이용한 풀이를 봐서 구글링 해봤는데 오라클에서 사용되는 함수로 DATE_FORMAT과 비슷한 기능을 하는 것 같다.

select count(*) USERS
from USER_INFO
where JOINED like '2021%' and AGE between 20 and 29

 

 

- 보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요.

select ANIMAL_ID, NAME,
          if(SEX_UPON_INTAKE like '%Neutered%' or SEX_UPON_INTAKE like '%Spayed%', 'O', 'X') "중성화"
from ANIMAL_INS
order by ANIMAL_ID

 

 

- PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

 

SUBSTR을 사용해 카테고리 코드 두 자리를 필터링해 준 뒤 서브쿼리로 묶고 COUNT 함수와 GROUP BY 절을 입력해 문제를 풀었는데

select CATEGORY, count(CATEGORY) PRODUCTS
from
(
select substr(PRODUCT_CODE, 1, 2) CATEGORY
from PRODUCT
) a
group by CATEGORY
order by CATEGORY

 

굳이 서브쿼리로 묶지 않아도 간결하게 풀이 가능한 문제였다..

너무 복잡하게 생각하다 보니 서브쿼리를 사용한 것 같다 ㅎㅎ;;

select substr(PRODUCT_CODE, 1, 2) CATEGORY, count(*) PRODUCT
from PRODUCT
group by CATEGORY
order by CATEGORY

 

 

- 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

 

이번 문제에선 ANIMAL_TYPE이 고양이와 개 두 종류만 있어서 따로 조건문을 입력하지 않았는데 만약 다른 종류의 동물들이 더 있고 그중에서 고양이와 개만 출력해야 하면 GROUP BY 문 뒤에 having ANIMAL_TYPE in ('Cat', 'Dog')를 입력하여 필터링해 주면 된다.

select ANIMAL_TYPE, count(*) COUNT
from ANIMAL_INS
group by ANIMAL_TYPE
order by ANIMAL_TYPE

 

 

- 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

 

날짜 함수에서 필요한 값만 출력하는 방식엔 여러 가지가 있는데 HOUR 함수를 이용해 '시' 만 필터링해 주었다. DATE_FORMAT 함수를 이용하면 date_format(DATETIME, '%H')로 입력해 줄 수 있다.

select hour(DATETIME) HOUR, count(*) COUNT
from ANIMAL_OUTS
where hour(DATETIME) between 9 and 19
group by 1
order by 1

 

 

- APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성해주세요. 이때, 컬럼명은 '진료과 코드', '5월예약건수'로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬해주세요.

 

아래와 같이 5월 예약 건수만 필터링해서 COUNT 해줘도 채점을 통과하긴 하는데

만약 예약 취소건을 포함하면 WHERE 절에 APNT_CNCL_YN='N' 코드를 추가해주면 된다.

select MCDP_CD "진료과 코드", count(*) "5월예약건수"
from APPOINTMENT
where date_format(APNT_YMD, '%Y-%m') = '2022-05'
group by 1
order by 2, 1

 

 

- PATIEN 테이블에서 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 ㅈ가성해주세요. 이때 전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이가 같다면 환자이름을 기준으로 오름차순 정렬해주세요.

 

NULL의 대체값 입력을 위해 IF 함수를 사용했는데 IFNULL 함수나 COALESCE 함수를 사용하면 조금 더 짧은 코드를 쓸 수 있다.

* ifnull(TLNO, 'NONE') 또는 coalesce(TLNO, 'NONE')

 

IFNULL은 MySQL에만 제공되는 함수이며 두 가지 값만 입력할 수 있지만

COALESCE는 표준 SQL 함수이며 여러개의 값을 입력할 수 있다는 차이점이 있다.

select PT_NAME, PT_NO, GEND_CD, AGE, if(TLNO is null, 'NONE', TLNO) TLNO
from PATIENT
where AGE<=12 and GEND_CD='W'
order by AGE desc, PT_NAME

 

 

- 상반기에 판매된 아이스크림의 맛을 총주문량을 기준으로 내림차순 정렬하고 총주문량이 같다면 출하 번호를 기준으로 오름차순 정렬하여 조회하는 SQL문을 작성해주세요.

select FLAVOR
from FIRST_HALF
order by TOTAL_ORDER desc, SHIPMENT_ID

 

 

- CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.

 

조건절의 길이를 줄이고싶어서 LIKE와 IN을 같이 적용해봤는데 오류가 발생하거나 원하는 결과가 출력되지 않았다. 다른 사람들의 풀이를 보니 정규표현식 REGEXP을 활용하면 LIKE 함수보다 짧게 풀이가 가능했다. 수직선 | 은 or 에 해당한다.

* where OPTIONS regexp '통풍시트|열선시트|가죽시트'

select CAR_TYPE, count(*) CARS
from CAR_RENTAL_COMPANY_CAR
where OPTIONS like '%통풍시트%' or OPTIONS like '%열선시트%' or OPTIONS like '%가죽시트%'
group by 1
order by 1

 

 

Python 예제 풀이

 

- 정수 배열 numbers가 매개변수로 주어집니다. numbers의 원소의 평균값을 return하도록 solution 함수를 완성해주세요.

 

sum() 함수를 이용해 리스트의 합을 구해주고 len() 함수로 나눠 평균값을 구해준다.

len() 함수는 리스트에 들어있는 원소의 개수, 즉 리스트의 크기를 알려주는 함수이다.

def solution(numbers):
      return sum(numbers)/len(numbers)

 

Numpy 라이브러리를 이용하면 계산식을 입력할 필요 없이 평균을 구하는 함수 mean을 이용해 풀 수도 있다.

import numpy as np
def solution(numbers):
      return np.mean(numbers)