이 5문제는 풀기 어려웠고 오답을 가장 많이 냈던 문제들이다. LEVEL 2이지만 난이도가 있는만큼 SQL도 많은 구문을 학습하고 문제를 풀어야겠다 싶다. 특히 서브쿼리 ! 제대로 숙지하자

🚩오늘의 문제는?
1.자동차 평균 대여 기간 구하기
2.조건에 맞는 사원 정보 조회하기
3.특정 물고기를 잡은 총 수 구하기
4.분기별 분화된 대장균의 개체 수 구하기
5.노선별 평균 역 사이 거리 조회하기

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

문제 정의

문제 풀이

SELECT CAR_ID,AVG(END_DATE - START_DATE +1) AS DATE_DIFF
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY;
GROUP BY CAR_ID;

오답

SELECT CAR_ID,ROUND(AVG(END_DATE - START_DATE +1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

->평균 대여기간이 7일 이상이어야한다.

오답

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

->틀렸다고 나온다.
-> 왜지?

DATEDIFF로 바꿔보기
기존
END_DATE - START_DATE +1
이후
DATEDIFF(END_DATE,START_DATE)

오답

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

->어디서 틀린건지 모르겠음
-> 질문하기에서 정답 찾아보기...!

정답

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 AVERAGE_DURATION >= 7.00
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

-> DATEDIFF(END_DATE, START_DATE) + 1
이걸로 고쳐줬더니 정답으로 처리 되었다.
무조건 기억하자! DATEDIFF(끝난날짜, 시작날짜) +1

주의할 점

• Unknown column 'AVERAGE_DURATION' in 'where clause'
  :에러 원인 : 있지도 않은 컬럼을 where 조건 절에 사용 했다
• DATEDIFF(끝난날짜컬럼,시작날짜컬럼) + 1 : DAY 차이
• WHERE 구문 + GROUP BY 구문이 아니라, GROUP BY HAVING 절을 이용해서
  조건안에 있는 값을 출력하도록 하기

⚠2.조건에 맞는 사원 정보 조회하기

문제 정의

문제 풀이

1.멀티플 조인해보기(3개의 테이블을 합쳐보기)

SELECT *
FROM HR_DEPARTMENT A,HR_EMPLOYEES B,HR_GRADE C
WHERE A.DEPT_ID= B.DEPT_ID AND B.EMP_NO = C.EMP_NO;

2.조건 추가하기(2022년도 평가 점수 가장 높은 사원 정보)

SELECT C.SCORE,C.EMP_NO,B.EMP_NAME,B.POSITION,B.EMAIL
FROM HR_DEPARTMENT A,HR_EMPLOYEES B,HR_GRADE C
WHERE A.DEPT_ID= B.DEPT_ID AND B.EMP_NO = C.EMP_NO AND C.YEAR = 2022 
ORDER BY C.SCORE DESC LIMIT 1;

-> 실패라고 뜬다

3. GROUP BY 추가하기

오답

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

-> 실패라고 뜬다

4. 최종(사번도 같이 GROUP으로 묶기)

정답

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

-> 성공! 단순히 GROUP BY 절을 할 때 YEAR로 묶는게 아니라 사번(EMP_NO)을 같이 묶었다.
-> WHY? "2022년 평가 점수가 가장 높은 사람" 이니까
-> 사람도 중복되면 안되므로 같이 묶었음

주의할 점

• 3개,4개 이상의 테이블을 JOIN하는 경우 : multiple join
  https://blog.naver.com/PostView.nhn?blogId=regenesis90&logNo=222190687396
• GROUP BY HAVING절에서 무조건 조건 COLUMN만 묶어야한다고 생각하지 말자..
  : 중복되면 안되는건 사람이기 때문에 EMP_NO도 GROUP BY도 같이 묶어야함

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

문제 정의

문제 풀이

1.테이블 조인

SELECT *
FROM FISH_INFO A, FISH_NAME_INFO B
WHERE A.FISH_TYPE=B.FISH_TYPE;

2.BASS & SNAPPER 인 점 + COUNTING해서 최종값구하기

정답

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

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

문제 정의

문제 풀이

오답

SELECT 
CASE 
WHEN MONTH(DIFFERENTIATION_DATE) IN (1,2,3) THEN '1Q'
WHEN MONTH(DIFFERENTIATION_DATE) IN (4,5,6) THEN '2Q'
WHEN MONTH(DIFFERENTIATION_DATE) IN (7,8,9) THEN '3Q'
ELSE '4Q'
END AS QUARTER,
COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATE;

정답

SELECT 
CASE 
WHEN MONTH(DIFFERENTIATION_DATE) IN (1,2,3) THEN '1Q'
WHEN MONTH(DIFFERENTIATION_DATE) IN (4,5,6) THEN '2Q'
WHEN MONTH(DIFFERENTIATION_DATE) IN (7,8,9) THEN '3Q'
ELSE '4Q'
END AS QUARTER,COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER;

⚠5.노선별 평균 역 사이 거리 조회하기

문제 정의

문제 풀이

1.노선별 총합과 평균값을 구하기

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

2.sql 컬럼에 문자값 추가하기

오답

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;

-> 원인을 모르겠음..질문하기 통해서 정답 찾아보기
-> ORDER BY TOTAL_DISTANCE : 해당 경우 정렬의 기준이 숫자가 아니라 문자로 들어가게 된다.
:ASCII기준으로 정렬이 되며
10KM와 9KM을 정렬하면 ? 1과 9를 비교하게 되는 문제점이 발생한다.
-> ORDER BY SUM(D_BETWEEN_DIST) 로 변경해야한다.

정답

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 SUM(D_BETWEEN_DIST) DESC;

주의할 점

• ORDER BY 정렬 기준
  : 문자와 숫자가 결합되어있는 경우 해당 값을 기준으로 정렬을 하면, 'ASCII'기준으로 정렬이 된다.
  : 이때 숫자가 있어도 숫자 전체로 비교되는게 아니라 숫자의 각 첫째자리,둘째자리 값으로
  비교가 된다.
  : 따라서 컬럼 전체를 ORDER BY로 가져오지 않고, 숫자 값만 정렬 기준으로 가져와야한다.