✈️ SQL Programmers Level 2 - 36 Solved

001. 3월에 태어난 여성 회원 목록 출력하기 / 002. 재구매가 일어난 상품과 회원 리스트 구하기

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

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC

 

✨

(1) DATE_FORMAT()으로 %Y-%m-%d로 나타내기

(2) MONTH(DATE_OF_BIRTH) = 3으로 MONTH 함수로 3임을 나타내기

 

✨

** FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

: SQL은 위 절 순서로 진행되기 때문에 먼저 FROM 테이블에서 WHERE 조건문으로 알맞는 레코드를 뽑는다. 그 다음, GROUP BY로 USER_ID, PRODUCT_ID 순서로 그룹화 진행 뒤, COUNT(*) > 1인 경우, 즉 동일한 회원이 동일한 상품을 구매한 경우만 뽑는다. 그 다음 SELECT문으로 나타낼 칼럼을 고르고, ORDER BY로 첫 순서부터 칼럼 순서 표현.

---

003. 업그레이드 된 아이템 구하기

SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO
WHERE ITEM_ID IN (SELECT A.ITEM_ID
                  FROM ITEM_INFO I, ITEM_TREE A
                WHERE I.ITEM_ID = A.PARENT_ITEM_ID AND I.RARITY = 'RARE')
ORDER BY ITEM_ID DESC;

 

✨

(1) 먼저 ITEM_INFO와 ITEM_TREE 테이블을 연결해야 하므로 FROM ITEM_INFO I, ITEM_TREE A. WHERE I.ITEM_ID = A.PARENT_ITEM_ID

(2) RARITY 값이 RARE여야 하므로 I.RARITY = 'RARE'

(3) 그 때의 A 테이블의 ITEM_ID를 가져온다.

(4) 해당 ITEM_ID를 중첩해서 큰 WHERE 문의 조건으로 넣고 큰 SQL문을 돌린다.

---

004. 조건에 맞는 개발자 찾기

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python') != 0 OR SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#') != 0
ORDER BY ID;

 

✨

(1) Python을 가진 개발자를 찾는 법은 Python code를 SKILLCODE에서 가져와서 해당 코드와 각 개발자의 SKILL_CODE와 & 연산 진행해서 0이 아닌 결과가 나온다면 Python 지식 보유함을 알 수 있다(프로그래머스 SQL 특정 형질을 가지는 대장균 찾기 문제 풀이 참고). 이 때 0이 아닌 결과는 곧 조건 자체가 True임을 뜻하므로 != 0 없이 써도 된다.

---

005. 특정 물고기를 잡은 총 수 구하기

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE FISH_TYPE IN (SELECT B.FISH_TYPE
                    FROM FISH_INFO A, FISH_NAME_INFO B
                    WHERE A.FISH_TYPE = B.FISH_TYPE AND B.FISH_NAME IN ('BASS', 'SNAPPER'))

 

✨

(1) 두 테이블을 연결해서 BASS, SNAPPER의 개수를 출력. 두 테이블 공통 부분 FISH_TYPE을 WHERE문에 넣고 IN () 안에 SELECT FISH_TYPE을 넣어 두 테이블 연결. SELECT문 중첩으로 두 테이블 연결 가능

---

006. 부모의 형질을 모두 가지는 대장균 찾기

SELECT A.ID, A.GENOTYPE, B.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA A, ECOLI_DATA B
WHERE A.PARENT_ID = B.ID
AND B.GENOTYPE & A.GENOTYPE = B.GENOTYPE
ORDER BY ID

 

✨

(1) 두 동일 테이블을 동시에 연결하기 위해 FROM에 동일 테이블 명에 별칭만 다르게 설정

(2) A를 child, B를 parent table이라 설정하고 두 테이블 연결을 위해 A.PARENT_ID = B.ID

(3) A 입장에서 A의 parent 형질, 즉 B의 GENOTYPE을 모두 A가 가질려면 A.GENOTYPE & B.GENOTYPE 결과 B의 GENOTYPE이 되어야 한다! ex) parent가 0101이고, child가 1101이라면 0101 & 1101 했을 때 0101로 parent의 형질을 모두 갖게 되는 지의 유무는 parent 자체의 결과로 알 수 있다.

---

007. 가격이 제일 비싼 식품의 정보 출력하기 / 008. 최솟값 구하기 / 009. 동물 수 구하기

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

SELECT DATETIME AS '시간'
FROM ANIMAL_INS
WHERE DATETIME IN (SELECT MIN(DATETIME) FROM ANIMAL_INS)

SELECT COUNT(*)
FROM ANIMAL_INS;

 

✨

(1) SELECT문 중첩으로 WHERE PRICE IN () 내부에 SELECT문을 또 넣어서 MAX(PRICE)와 WHERE의 PRICE 같게 한 뒤 SELECT

 

(2) 두번째 문제도 마찬가지. 가장 먼저 들어온 시간은 MIN(DATETIME) 돌리기

---

010. 중복 제거하기 / 011. 조건에 맞는 아이템들의 가격의 총합 구하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

SELECT SUM(PRICE) AS TOTAL_PRICE
FROM ITEM_INFO
WHERE RARITY = 'LEGEND'

 

✨

(1) 중복되는 이름은 하나로 치므로 DISTINCT 키워드 사용해서 중복 제거

---

012. 연도별 대장균 크기의 편차 구하기

SELECT
    YEAR(DIFFERENTIATION_DATE) AS YEAR,
    (
        SELECT MAX(ED.SIZE_OF_COLONY)
        FROM ECOLI_DATA AS ED
        WHERE YEAR = YEAR(ED.DIFFERENTIATION_DATE)
    ) - SIZE_OF_COLONY AS YEAR_DEV,
    ID
FROM ECOLI_DATA
ORDER BY YEAR, YEAR_DEV

 

✨

(1) 먼저 편차를 구하기 위해서는 각 year의 최댓값을 구해야 하므로 WHERE문으로 먼저 동일 YEAR 데이터 분류한 다음, 해당 row들 중 MAX(ED.SIZE_OF_COLONY)를 선택, 그리고 해당 최댓값에서

(2) 현재 SIZE_OF_COLONY를 뺀 결과를 YEAR_DEV로 설정

(3) YEAR로 오름차순 한 뒤, 동일 YEAR의 경우 YEAR_DEV로 오름차순 시 위와 같이 ORDER_BY에 두 컬럼 이름 연속해서 ,으로 연결

---

013. 자동차 종류별 특정 옵션이 포함된 자동차 수 구하기 

SELECT CAR_TYPE, COUNT(CAR_ID) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%가죽시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%통풍시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE

 

✨

** IN 연산자가 아니라 LIKE를 사용해야 한다. 가죽시트, 열선시트, 통풍시트 이렇게 3개 중 적어도 1개 이상이 문자열로 '포함'되어 있는 지의 여부를 묻는 것이기 때문 (주의)

* GROUP BY로 묶은 다음, COUNT() 집계 함수로 해당 차 종류별 조건을 만족하는 차의 대수를 CARS 칼럼으로 출력

---

014. 성분으로 구분한 아이스크림 총 주문량 / 015. 진료과별 총 예약 횟수 출력하기

SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF AS A JOIN ICECREAM_INFO AS B ON A.FLAVOR = B.FLAVOR
GROUP BY B.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC

SELECT MCDP_CD AS '진료과코드', COUNT(*) AS '5월예약건수'
FROM APPOINTMENT
WHERE YEAR(APNT_YMD) = '2022' AND MONTH(APNT_YMD) = '5'
GROUP BY MCDP_CD
ORDER BY COUNT(*), MCDP_CD

 

✨

(1) 먼저 두 테이블 JOIN 진행하고

(2) INGREDIENT_TYPE으로 GROUP BY해서 총 주문량이므로 SUM()으로 표시하고,

(3) 마지막으로 ORDER BY로 SELECT로 만든 TOTAL_ORDER 기준 정렬 진행

 

✨

(1) 먼저 WHERE문으로 2022년 5월인 데이터만 추출

(2) GROUP BY문으로 MCDP_CD로 그룹화

(3) 그룹화한 내의 데이터 개수로 COUNT(*) 및 MCDP_CD SELECT

(4) 마지막 ORDER BY까지 완료

---

016. 고양이와 개는 몇 마리 있을까

SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
WHERE ANIMAL_TYPE IN ('Cat', 'Dog')
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC

 

✨

(1) where문으로 Cat과 Dog만 필터링

(2) 그 다음 Cat별, Dog 별로 GROUP BY

(3) SELECT로 COUNT(*) 활용하여 각 그룹별 데이터 개수 출력

(4) ORDER BY로 ANIMAL_TYPE ASC 진행하여 Cat 먼저 출력, 그 다음 Dog 출력

---

017. 동명 동물 수 찾기 / 018. 입양 시각 구하기(1)

SELECT NAME, COUNT(*) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*) > 1
ORDER BY NAME

✨

(1) WHERE문으로 이름이 없는 동물은 제외

(2) NAME으로 GROUP BY 진행 후, HAVING절을 사용해 두 번 이상 쓰인 이름에서 그룹화한 결과를 필터링

(3) SELECT로 표현하고, ORDER BY로 정렬

SELECT HOUR(DATETIME), COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE 9 <= HOUR(DATETIME) AND HOUR(DATETIME) <= 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

✨

(1) WHERE문으로 9시부터 19시까지의 데이터 추출

(2) GROUP BY로 HOUR() 기준 그룹화

(3) HOUR(DATETIME), COUNT(*) 그룹 내의 데이터 개수 COUNT로 SELECT

(4) ORDER BY로 HOUR 순 정렬

---

019. 가격대별 상품 개수 구하기 / 020. 조건에 맞는 사원 정보 조회하기

SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY TRUNCATE(PRICE, -4)
ORDER BY PRICE_GROUP

✨

(1) truncate(price, -4)로 15000은 10000으로 맨 앞의 자리 숫자만 남기고 나머지 숫자 모두 0으로 설정.

 

SELECT SUM(SCORE) AS SCORE, B.EMP_NO, B.EMP_NAME, B.POSITION, B.EMAIL
FROM HR_DEPARTMENT AS A JOIN HR_EMPLOYEES AS B JOIN HR_GRADE AS C ON A.DEPT_ID = B.DEPT_ID AND B.EMP_NO = C.EMP_NO
GROUP BY YEAR, EMP_NO
HAVING C.YEAR = '2022'
ORDER BY SUM(SCORE) DESC
LIMIT 1

✨

(1) GROUP BY를 YEAR, EMP_NO로 두 컬럼으로 세부 그룹화

(2) 그 다음 HAVING 절로 2022년인 경우만 추출

(3) ORDER BY SUM(SCORE)로, 나온 SCORE 값의 합을 기준 내림차순 정렬

(4) LIMIT 1으로 맨 위의 최댓값만 추출

---

021. 노선별 평균 역 사이 거리 조회하기 / 022. 물고기 종류 별 잡은 수 구하기

SELECT ROUTE, CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),'km') AS TOTAL_DISTANCE, CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY ROUND(SUM(D_BETWEEN_DIST),1) DESC

✨

(1) 소수 둘째 자리에서 반올림은 ROUND(~,2)  / 소수 셋째 자리에서 반올림은 ROUND(~,3). 그리고 km 붙이기는 CONCAT(,'km') 활용

(2) 숫자 기준 내림차순 진행이므로 위 TOTAL_DISTANCE는 문자열이기 때문에 숫자로만 구한 ROUND() 결과를 가지고 DESC 진행

 

SELECT COUNT(*) AS FISH_COUNT, B.FISH_NAME
FROM FISH_INFO AS A JOIN FISH_NAME_INFO AS B ON A.FISH_TYPE = B.FISH_TYPE
GROUP BY B.FISH_NAME
ORDER BY COUNT(*) DESC

✨

*** GROUP BY가 들어간 경우, SELECT문에서 고르는 COLUMN은 집계함수, GROUP BY로 들어간 COLUMN 이름만 가능하다. 따라서 GROUP BY로 그룹화할 COLUMN이 무엇인지 반드시 확인

---

023. 월별 잡은 물고기 수 구하기 / 024. NULL 처리하기

SELECT COUNT(*) AS FISH_COUNT, MONTH(TIME) AS MONTH
FROM FISH_INFO
GROUP BY MONTH(TIME)
ORDER BY MONTH(TIME) ASC

 

 

SELECT ANIMAL_TYPE, ifnull(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

✨

ifnull() 함수를 사용해서, NULL일 경우 대체할 데이터를 두 번째 인자로 쓴다.

---

025. ROOT 아이템 구하기 / 026. 조건에 맞는 저자와 도서 리스트 출력하기

SELECT A.ITEM_ID, A.ITEM_NAME
FROM ITEM_INFO AS A JOIN ITEM_TREE AS B ON A.ITEM_ID = B.ITEM_ID
WHERE B.PARENT_ITEM_ID IS NULL
ORDER BY A.ITEM_ID ASC

✨

** IS NULL이라 해야 실제 데이터 NULL인 행만 SELECT 된다. 만약 = 'NULL'이라 하면 실제 NULL이 아니라 값이 0인 행만 SELECT 된다.

SELECT A.BOOK_ID, B.AUTHOR_NAME, DATE_FORMAT(A.PUBLISHED_DATE, "%Y-%m-%d") AS PUBLISHED_DATE
FROM BOOK AS A INNER JOIN AUTHOR AS B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'
ORDER BY PUBLISHED_DATE ASC

✨

* DATE_FORMAT()을 활용하여 %Y-%m-%d 형태로 표현해야 한다. %M이 아니라 %m, %D가 아니라 %d인 점 주의

---

027. 상품 별 오프라인 매출 구하기

SELECT A.PRODUCT_CODE AS PRODUCT_CODE, SUM(SALES_AMOUNT) * A.PRICE AS SALES
FROM PRODUCT AS A JOIN OFFLINE_SALE AS B ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY A.PRODUCT_CODE
ORDER BY SALES DESC, A.PRODUCT_CODE ASC

✨

* SUM(SALES_AMOUNT) * A.PRICE인, 즉 판매가 * 판매량의 합계를 새롭게 SALES 칼럼으로 생성

* SELECT 문 진행 후 ORDER BY문 진행하므로 ORDER BY SALES라고 새롭게 만든 컬럼 이름 써도 가능함

---

028. 자동차 평균 대여 기간 구하기

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE, START_DATE) + 1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

✨

* DATEDIFF()를 활용하여 두 날짜 사이의 날짜를 구할 수 있다. 다만, 하루 날짜 포함해야만 앞과 뒤의 날짜 모두 포함되므로 + 1 필수.

* HAVING 절 사용해서 AVG(DATEDIFF()) 결과로 7일 이상인 경우만 그룹화 진행

---

029. 조건에 부합하는 중고거래 상태 조회하기

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
    CASE
    WHEN STATUS = 'SALE' THEN '판매중'
    WHEN STATUS = 'RESERVED' THEN '예약중'
    ELSE '거래완료'
    END AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE = '2022-10-05'
ORDER BY BOARD_ID DESC

✨

CASE ~ WHEN THEN ~ ELSE ~ END AS 문으로 SQL IF문을 사용하여 직접 조건별로 칼럼 내용 설정

---

030. 루시와 엘라 찾기 / 031. 이름에 el이 들어가는 동물 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Ella', 'Lucy', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%EL%' AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

---

032. 중성화 여부 파악하기 / 033. 카테고리 별 상품 개수 구하기

SELECT ANIMAL_ID, NAME, 
    CASE WHEN SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%' THEN 'O'
    ELSE 'X'
    END AS '중성화'
FROM ANIMAL_INS

SELECT SUBSTR(PRODUCT_CODE,1,2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY SUBSTR(PRODUCT_CODE,1,2)
ORDER BY CATEGORY ASC

✨

(1) SUBSTR(X,1,2)라면 주어진 X라는 문자열의 첫번째부터 두번째 문자까지 추출. 추출된 두 문자를 CATEGORY로 만든다.

---

034. DATETIME에서 DATE로 형 변환 / 035. 연도 별 평균 미세먼지 농도 조회하기

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d") AS '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

SELECT YEAR(YM) AS 'YEAR', ROUND(AVG(PM_VAL1),2) AS 'PM10', ROUND(AVG(PM_VAL2),2) AS 'PM2.5'
FROM AIR_POLLUTION
WHERE LOCATION2 = '수원'
GROUP BY YEAR(YM)
ORDER BY YEAR(YM) ASC

---

036. 분기별 분화된 대장균의 개체 수 구하기

SELECT CONCAT(CEIL(MONTH(DIFFERENTIATION_DATE) / 3), 'Q') AS QUARTER, COUNT(*) AS ECOLI_COUNT 
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER

✨

* CEIL(MONTH(~)/3)으로 분기 수를 구할 수 있다. 그리고 SELECT로 정한 QUARTER 새로운 칼럼을 가지고 GROUP BY에 적용 가능하다. (반드시 기억!)

---