SQL 코딩테스트 연습

 

오랜만에 SQL 코딩 테스트가 하고 싶어졌다. 

 

https://school.programmers.co.kr/

프로그래머스

 

간단한 문제 풀이를 하려고 한다.

 

이번 목표는 SQL의 가독성을 신경 써서 작성하는 연습이다.

 

1. 강원도에 위치한 생산공장 목록 출력

 

FOOD_FACTORY 테이블에서 강원도에 위치한 식품공장의 공장 ID, 공장 이름, 주소를 조회하는 SQL문을 작성해주세요. 이때 결과는 공장 ID를 기준으로 오름차순 정렬해주세요.

 

 SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
   FROM FOOD_FACTORY
  WHERE ADDRESS LIKE '강원도%'
   ORDER BY FACTORY_ID;

 

 

2.  재구매가 일어난 회원과 상품 리스트 구하기

 

 하나의 테이블에서 재구매를 확인하는 방법을 찾아야 한다. 처음에는 셀프 조인으로 헛짓을 했지만 다시 생각해보면 단순 그룹을 짓고 조건으로 처리할 수 있는 문제였다.

 

ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

 

select a.user_id, a.product_id
  from online_sale a
 group by a.user_id, a.product_id
 having count(*) >=2
 order by a.user_id, a.product_id desc

 

 

3. 오프라인/온라인 판매 데이터 통합하기

 

 

문제의 의도는 집합 연산자를 알고 활용할 수 있느냐를 물어보는 것 같았다.

 

각각 중복되어도 행이 출력되어야 한다는 점에서 UNION ALL이 적합하다는 생각이 들었고

3월이라는 조건절에서 약간의 고민을 하였는데, 만약 날짜에 대해 인덱스가 설정된 경우 YEAR(SALES_DATE)와 같이 변형시키면 인덱스가 적용되지 않기 때문에 조금 복잡해도 문자를 날짜로 변환시켜 비교하였다.

 

 

 

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품 ID, 유저 ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품 ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.

 

SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT 
  FROM ONLINE_SALE
 WHERE SALES_DATE >= STR_TO_DATE('2022-03-01', '%Y-%m-%d') 
   AND SALES_DATE <= STR_TO_DATE('2022-03-31', '%Y-%m-%d')
 UNION ALL
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, NULL, SALES_AMOUNT 
  FROM OFFLINE_SALE
 WHERE SALES_DATE >= STR_TO_DATE('2022-03-01', '%Y-%m-%d') 
   AND SALES_DATE <= STR_TO_DATE('2022-03-31', '%Y-%m-%d')
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

 

 

4.  아픈 동물 찾기

 

단순 조회이다.

 

select animal_id, name
  from animal_ins
 where intake_condition = 'Sick'

 

 

5. 3월에 태어난 여성 회원 목록 출력

 

NULL 처리와 다중 조건문을 묻는 문제 같다. 

 

이번에는 날짜 처리를 조금 간편하게 진행했다.

 

 

MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력 대상에서 제외시켜 주시고, 결과는 회원 ID를 기준으로 오름차순 정렬해주세요.

 

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') as DATE_OF_BIRTH
  FROM MEMBER_PROFILE 
 WHERE MONTH(DATE_OF_BIRTH) = '03' 
   AND TLNO IS NOT NULL
   AND GENDER = 'W'
ORDER BY MEMBER_ID

 

6. 가격이 제일 비싼 식품의 정보 출력하기

 

해당 문제는 집계 함수의 사용법에 대해 묻는 문제로 보인다. 

 

집계 함수를 사용할 수 있는 위치와 조건절에 넣기 위해 사용되는 스칼라 서브 쿼리를 아는지 묻는 질문

FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.

 

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
  FROM FOOD_PRODUCT
 WHERE PRICE = ( SELECT MAX(PRICE) FROM FOOD_PRODUCT);

 

 

7. 중복 제거하기

 

인라인 뷰와 중복을 제거하는 DISTINCT의 사용법을 묻는 문제로 보입니다.

 

동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.

 

SELECT COUNT(AI.NAME) AS NAME
  FROM (SELECT DISTINCT NAME FROM ANIMAL_INS) AI;

 

8. 식품분류별 가장 비싼 식품 조회하기

 

이 문제는 GROUP BY와 집계 함수의 관계에 대해 묻는 문제로 보인다. 

 

GROUP BY절에 존재하지 않는 칼럼을 SELECT 절에 작성하게 되면 보통 오류가 발생하는데, 여기선 오류가 발생하지 않아 헤맬 수 있다. 

 

분류, 가격, 이름에서 이름에 주목하여 이름은 그룹 하여 집계 함수로 처리하지 못하는 것을 깨닫고 다른 방법을 추구하는 것이 키포인트로 보인다.

 

1. 카테고리 별 가장 비싼 가격을 추출, 문제 조건인 과자, 국, 김치 , 식용유로 조건을 거른다.

     결과 셋은 카테리고 별 가장 비싼 가격을 가지고 카테고리가 과자, 국, 김치, 식용유인 레코드만 남게 되어 문제가 해결된다.

 

FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.

 

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
  FROM FOOD_PRODUCT
 WHERE PRICE IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY)
   AND CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY MAX_PRICE DESC

 

9. 년, 월, 성별 별 상품 구매 회원 수 구하기

 

Group by에 여러 칼럼을 사용 시 도출되는 결과와 유저 수를 구하기 위한 Distinct가 키워드로 보인다.

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, GENDER, COUNT(DISTINCT UI.USER_ID) AS USER
  FROM USER_INFO UI JOIN ONLINE_SALE OS ON UI.USER_ID = OS.USER_ID
 WHERE GENDER IS NOT NULL
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE), GENDER
ORDER BY YEAR(SALES_DATE), MONTH(SALES_DATE), GENDER