✍🏻 programmers Lv2. MySQL 7문제(24.10.22)

programmers Lv2. MySQL 7문제(24.10.22)

57) 조건에 맞는 사원 정보 조회하기

• HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블에서 2022년도 한해 평가 점수가 가장 높은 사원 정보를 조회하려 한다. 2022년도 평가 점수가 가장 높은 사원들의 점수, 사번, 성명, 직책, 이메일을 조회하는 SQL문을 작성. 2022년도의 평가 점수는 상,하반기 점수의 합을 의미하고, 평가 점수를 나타내는 컬럼의 이름은 SCORE로.

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

---

58) 자동차 평균 대여 기간 구하기

• ROUND는 반올림을 하기 위해 사용

• DATEDIFF는 날짜를 이용해 대여 기간을 구하기 위해 사용

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(END_DATE, START_DATE)+1 여기서 +1을 하는 이유는 예를 들어 2024-10-01이 대여 시작일, 2024-10-03이 대여 종료일이라고 할 때 3-1=2이다. 하지만 총 대여일은 3이 되어야하므로 +1을 하는 것이다.

---

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

SELECT COUNT(A.FISH_TYPE) AS 'FISH_COUNT'
FROM FISH_INFO A 
JOIN FISH_NAME_INFO B ON A.FISH_TYPE = B.FISH_TYPE
WHERE B.FISH_NAME LIKE 'BASS' OR B.FISH_NAME LIKE 'SNAPPER'

• 어려운 부분 없었음.

---

60) 노선별 평균 역 사이 거리 조회하기

// 틀린 코드
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 TOTAL_DISTANCE DESC;      


// 맞은 코드
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;                                                                        

• CONCAT() : 문자열 붙이는 메서드

  • 저번에 CONnect + CAT : 고양이가 귀엽게 연결돼서 문자를 붙인다로 외웠는데 생각이 안 나서 RIGHT랑 혼동함.

  • RIGHT는 오른쪽 몇 글자 추출할 때 쓰임.

• CONCAT()을 이용해 문자로 변경되었으므로 정렬이 알맞게 되지 않는다. 따라서 CONCAT()을 뺀 상태로 정렬 해야된다.

---

61) 분기별 분화된 대장균의 개체 수 구하기

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

• QUARTER() 메서드는 주어진 날짜가 속하는 분기를 반환한다. 분기는 1년을 네 개의 구간으로 나눈 것이며

• 1분기 (Q1): 1월 1일 ~ 3월 31일
• 2분기 (Q2): 4월 1일 ~ 6월 30일
• 3분기 (Q3): 7월 1일 ~ 9월 30일

• 4분기 (Q4): 10월 1일 ~ 12월 31일

• 로 나타낸다.

---

62) 업그레이드 된 아이템 구하기

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

• 먼저 문제가 이해가 안 돼서 한참을 고민했다.

• 아이템의 희귀도가 ‘RARE’인 아이템들의 모든 다음 업그레이드 아이템의 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력하는 것인데

ITEM_ID	PARENT_ITEM_ID
0	NULL
1	0
2	0
3	1
4	1

ITEM_ID	ITEM_NAME	RARITY	PRICE
0	ITEM_A	RARE	10000
1	ITEM_B	RARE	9000
2	ITEM_C	LEGEND	11000
3	ITEM_D	RARE	10000
4	ITEM_E	RARE	12000

• 해당 표는 ITEM_TREE 테이블의 PARENT_ITEM_ID 필드가 0인 것은 ITEM_ID가 1, 2 이고 이것은 ITEM_A가 ITEM_B와 ITEM_C로 업그레이드 가능하다는 것이고 ITEM_B가 ITEM_D와 ITEM_E로 업그레이드 가능하다는 것이다.

• 이것은 업그레이드 하기 전의 ITEM_ID의 RARITY가 ‘RARE’어야 되고 즉, 다시 말해 부모 아이템이 희귀도가 ‘RARE’인 아이템이어야 된다. 따라서 서브쿼리를 통해 업그레이드 전 RARITY가 ‘RARE’인 것을 표시했다.

---

63) 조건에 맞는 개발자 찾기

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

• SKILL_CODE & 연산은 비트wise AND 연산자로, 두 값의 비트가 모두 1인 경우에만 1을 반환한다.

• 과연 서브쿼리를 사용하는 상황은 언제일까?

  • 필터링 조건에 필요한 값이 다른 테이블에 있을 때

    • 특정 조건을 만족하는 값을 다른 테이블에서 가져와야 할 때 서브쿼리를 사용한다.