프로그래머스 ‐ 프로그래머스 Lv2 SQL 도장깨기 - dnwls16071/Backend_Study_TIL GitHub Wiki

• 조건에 맞는 사원 정보 조회하기 - GROUP BY

-- 코드를 작성해주세요
SELECT SUM(HG.SCORE) AS SCORE 
     , HE.EMP_NO
     , HE.EMP_NAME
     , HE.POSITION	
     , HE.EMAIL
    FROM HR_DEPARTMENT AS HD
    JOIN HR_EMPLOYEES AS HE ON HD.DEPT_ID = HE.DEPT_ID 
    JOIN HR_GRADE AS HG ON HE.EMP_NO = HG.EMP_NO
    GROUP BY HE.EMP_NO, HE.EMP_NAME, HE.POSITION, HE.EMAIL
    ORDER BY SUM(HG.SCORE) DESC
    LIMIT 1

• 부서별 평균 연봉 조회하기 - GROUP BY

-- 코드를 작성해주세요
SELECT HD.DEPT_ID
     , HD.DEPT_NAME_EN	
     , ROUND(AVG(SAL),0) AS AVG_SAL
    FROM HR_DEPARTMENT AS HD
    JOIN HR_EMPLOYEES AS HE ON HD.DEPT_ID = HE.DEPT_ID
    GROUP BY HD.DEPT_ID
    ORDER BY AVG_SAL DESC

• 가격대별 상품 개수 구하기

-- 코드를 입력하세요
SELECT FLOOR(PRICE/10000)*10000 AS PRICE_GROUP
    , COUNT(*) AS PRODUCTS
     FROM PRODUCT
     GROUP BY PRICE_GROUP
     ORDER BY PRICE_GROUP ASC

• 진료과별 총 예약 횟수 출력하기

-- 코드를 입력하세요
SELECT MCDP_CD AS "진료과코드"
     , COUNT(MCDP_CD) AS "5월예약건수"
     FROM APPOINTMENT
     WHERE APNT_YMD LIKE '2022-05%'
     GROUP BY MCDP_CD
     ORDER BY COUNT(MCDP_CD) ASC, MCDP_CD ASC

• 조건에 맞는 도서와 저자 리스트 출력하기

-- 코드를 입력하세요
SELECT B.BOOK_ID
     , A.AUTHOR_NAME
     , DATE_FORMAT(B.PUBLISHED_DATE, "%Y-%m-%d") AS PUBLISHED_DATE
    FROM BOOK AS B
    JOIN AUTHOR AS A ON A.AUTHOR_ID = B.AUTHOR_ID
    WHERE B.CATEGORY = "경제"
    ORDER BY B.PUBLISHED_DATE ASC

• 조건에 부합하는 중고거래 상태 조회하기 - CASE WHEN THEN 복합 조건

-- 코드를 입력하세요
SELECT BOARD_ID
     , WRITER_ID
     , TITLE
     , PRICE
     , CASE WHEN STATUS = 'DONE' THEN '거래완료' 
            WHEN STATUS = 'RESERVED' THEN '예약중' 
            WHEN STATUS =  "SALE" THEN '판매중' END AS STATUS
     FROM USED_GOODS_BOARD
     WHERE CREATED_DATE = '2022-10-05'
     ORDER BY BOARD_ID DESC

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

-- 코드를 작성해주세요
SELECT COUNT(*) AS FISH_COUNT
    FROM FISH_INFO AS FI
    JOIN FISH_NAME_INFO AS FNI ON FI.FISH_TYPE=FNI.FISH_TYPE
    WHERE FNI.FISH_NAME = 'BASS' OR FNI.FISH_NAME = 'SNAPPER'