아래 문제들은 프로그래머스 SQL 코딩테스트 문제를 사용하였습니다.
1. 조건에 맞는 회원수 구하기 - LIKE, BETWEEN
USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력하는 SQL문을 작성하시오.
2021년에 가입한 : JOINED LIKE '2021 %'
나이 20대 : between 20 and 29
풀이
SELECT COUNT(*) as USERS FROM USER_INFO WHERE JOINED LIKE '2021%' AND AGE BETWEEN 20 AND 29
2. 경기도에 위치한 식품창고 목록 출력하기 - IFNULL
FOOD_WAREHOUSE 테이블에서 경기도에 위치한 창고의 ID, 이름, 주소, 냉동시설 여부를 조회하는 SQL문을 작성해주세요. 이때 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 주시고 결과는 창고 ID를 기준으로 오름차순 정렬해주세요.
- freezer_yn값이 NULL이면 'N'으로 대체 : IFNULL(freezer_yn, 'N') as freezer_yn
- freezer_yn이 NULL인 경우 "N", NULL이 아닌 경우 freezer_yn을 출력
풀이
SELECT warehouse_id, warehouse_name, address, IFNULL(freezer_yn, 'N') as freezer_yn FROM food_warehouse WHERE warehouse_name LIKE '%경기%' ORDER BY warehouse_id
3. 동명 동물 수 찾기 - HAVING, IS NOT NULL
동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.
- 이름이 없는 동물 집계 제외 : WHERE NAME IS NOT NULL
- 두번 이상 쓰인 이름 : HAVING COUNT(*) >= 2 (HAVING은 이미 리스트업 된 데이터 내에서 조건을 추가하는 절로 HAVING절 조건에 해당되는 컬럼들은 GROUP BY절에 이미 사용된 컬럼들만을 사용할 수 있다.)
풀이
SELECT NAME, COUNT(*) as COUNT FROM ANIMAL_INS WHERE NAME IS NOT NULL GROUP BY NAME HAVING COUNT(*) >= 2 ORDER BY NAME
4. 재구매가 일어난 상품과 회원 리스트 구하기
ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요
- 재구매 :
WHERE o1.USER_ID = o2.USER_ID
AND o1.PRODUCT_ID = o2.PRODUCT_ID
AND o1.SALES_DATE != o2.SALES_DATE
풀이
SELECT DISTINCT o1.USER_ID, o1.PRODUCT_ID FROM ONLINE_SALE o1, ONLINE_SALE o2 WHERE o1.USER_ID = o2.USER_ID AND o1.PRODUCT_ID = o2.PRODUCT_ID AND o1.SALES_DATE != o2.SALES_DATE ORDER BY o1.USER_ID, o1.PRODUCT_ID DESC
5. 년, 월, 성별 별 상품 구매 회원 수 구하기 - JOIN
USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.
- 년/월 : SELECT YEAR(sales_date) YEAR, MONTH(sales_date) MONTH
- USER_INFO와 ONLINE_SALE 집계 :
from ONLINE_SALE o JOIN USER_INFO u
ON o.USER_ID = u.USER_ID ( USER_ID 기준으로 JOIN )- 회원수 : count(DISTINCT(o.USER_ID))
풀이
select year(sales_date) YEAR ,month(sales_date) MONTH, GENDER, count(distinct(o.USER_ID)) USERS from ONLINE_SALE o join USER_INFO u on o.USER_ID = u.USER_ID WHERE gender is not null group by year(sales_date), month(sales_date), gender ORDER BY YEAR, MONTH, GENDER
6. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기 - REGEXP
문제 : CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.
CAR_RENTAL_COMPANY_CAR 테이블
| Column | name | Type |
|---|---|---|
| CAR_ID | INTEGER | FALSE |
| CAR_TYPE | VARCHAR(255) | FALSE |
| DAILY_FEE | INTEGER | FALSE |
| OPTIONS | VARCHAR(255) | FALSE |
하나 이상의 옵션 : OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
풀이
SELECT CAR_TYPE, COUNT(*) AS CARS FROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트' GROUP BY CAR_TYPE ORDER BY CAR_TYPE
7. 입양 시각 구하기(2) - RECURSIVE cte
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
- 0시 부터 23 시 : RECURSIVE time as ( SELECT 0 as hour UNION ALL SELECT hour + 1 FROM time WHERE hour < 23)
- 시간대별로 몇 건 : time t left join (SELECT *, hour(DATETIME) hour FROM ANIMAL_OUTS) o on t.hour = o.hour
풀이
with recursive time as ( select 0 as hour union all select hour + 1 from time where hour < 23 ) select t.hour, count(animal_id) from time t left join ( select *, hour(DATETIME) hour from ANIMAL_OUTS ) o on t.hour = o.hour group by t.hour order by t.hour
8. 인덱스 및 쿼리 최적화 (Indexes and Query Optimization)
9. 트랜잭션 및 동시성 제어 (Transactions and Concurrency Control)
10. 저장 프로시저, 함수, 트리거 (Stored Procedures, Functions, Triggers)
11. 뷰 (Views) 학습
12. 윈도우 함수 (Window Functions) 학습
13. 파티셔닝 (Partitioning) 학습
14. 보안 및 사용자 관리 (Security and User Management)
15. 뷰(Views) 및 자료집계 (Materialized Views) 학습
16. 데이터 타입 및 제약조건 (Data Types and Constraints) 학습
