본문 바로가기
728x90
320x100

💻 하나씩 차곡차곡/프로그래머스 (SQL)105

[GROUP BY/LEVEL4] 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기 (MySQL) MySQLSELECT A.EMP_NO , A.EMP_NAME , CASE WHEN AVG(B.SCORE) >= 96 THEN 'S' WHEN AVG(B.SCORE) >= 90 THEN 'A' WHEN AVG(B.SCORE) >= 80 THEN 'B' ELSE 'C' END AS GRADE , CASE WHEN AVG(B.SCORE) >= 96 THEN A.SAL * 0.2 WHEN AVG(B.SCORE) >= 90 THEN A.SAL * 0.15 WHEN AVG(B.SCORE) >= 80 THEN A.SAL * 0.1 ELSE 0 END AS BONUSFROM HR_.. 2024. 12. 9.
[GROUP BY/LEVEL4] 저자 별 카테고리 별 매출액 집계하기 (MySQL/Oracle) MySQLSELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(A.PRICE * C.SALES) AS TOTAL_SALESFROM BOOK A, AUTHOR B, BOOK_SALES CWHERE A.AUTHOR_ID = B.AUTHOR_IDAND A.BOOK_ID = C.BOOK_IDAND YEAR(C.SALES_DATE) = 2022 AND MONTH(C.SALES_DATE) = 1GROUP BY A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORYORDER BY A.AUTHOR_ID, A.CATEGORY DESC OracleSELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(PRICE * SALES) AS T.. 2024. 12. 5.
[GROUP BY/LEVEL3] 부서별 평균 연봉 조회하기 (MySQL) MySQLSELECT A.DEPT_ID, DEPT_NAME_EN, ROUND(AVG(SAL),0) AS AVG_SALFROM HR_DEPARTMENT A, HR_EMPLOYEES BWHERE A.DEPT_ID = B.DEPT_IDGROUP BY DEPT_ID, DEPT_NAME_ENORDER BY AVG_SAL DESC 2024. 12. 4.
[GROUP BY/LEVEL3] 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 (MySQL) MySQLSELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDSFROM CAR_RENTAL_COMPANY_RENTAL_HISTORYWHERE MONTH(START_DATE) BETWEEN 8 AND 10AND (CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31' GROUP BY CAR_ID HAVING COUNT(CAR_ID) >= 5))GROUP BY MONTH, CAR_IDORDE.. 2024. 12. 3.
[GROUP BY/LEVEL2] 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기 (MySQL) MySQLSELECT COUNT(*) AS FISH_COUNT , MAX(IFNULL(LENGTH, 10)) AS MAX_LENGTH , FISH_TYPEFROM FISH_INFOGROUP BY FISH_TYPEHAVING AVG(IFNULL(LENGTH, 10)) >= 33ORDER BY FISH_TYPE 2024. 12. 2.
[GROUP BY/LEVEL3] 카테고리 별 도서 판매량 집계하기 (MySQL) MySQLSELECT CATEGORY, SUM(SALES) AS TOTAL_SALESFROM BOOK A, BOOK_SALES B WHERE A.BOOK_ID = B.BOOK_IDAND YEAR(SALES_DATE) = 2022AND MONTH(SALES_DATE) = 1GROUP BY CATEGORYORDER BY CATEGORY 2024. 11. 29.
728x90
320x100

티스토리툴바