다시 풀어야하는 문제들 모음 (프로그래머스 MYSQL LEVEL 1,2,3)
LEVEL 1
1.특정 형질을 가지는 대장균 찾기💦
LEVEL 2
1.자동차 평균 대여 기간 구하기
2.조건에 맞는 사원 정보 조회하기
3.특정 물고기를 잡은 총 수 구하기
4.분기별 분화된 대장균의 개체 수 구하기
5.노선별 평균 역 사이 거리 조회하기
LEVEL 3
1.자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기💦

<LEVEL 1>

특정 형질을 가지는 대장균 찾기💦

오답

SELECT COUNT(DISTINCT ID) AS COUNT
FROM ECOLI_DATA
WHERE (GENOTYPE & 4 = 0) -- 2번 형질을 보유하지 않음
AND (GENOTYPE & 1 > 0 -- 1번 형질을 보유하거나
OR GENOTYPE & 8 > 0); -- 3번 형질을 보유함

정답

<LEVEL 2>

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

기존에 작성했었던 정답

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함수를 써서 기간 차이를 구하자, 이때 +1을 반드시 추가해야한다.
  (단순히 더하고 빼서 기간 구하는 것 XX)
• 서브쿼리를 통해서 열을 새롭게 구하고, 평균값을 구할 수 있다.
  그러나 더 간소화된 정답이 존재한다.

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

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

기존에 작성했었던 정답

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;

새로 풀어본 답

• MULTIPLE JOIN 하는 방법 :
  https://blog.naver.com/PostView.nhn?blogId=regenesis90&logNo=222190687396

-- 사원별 평가 점수의 합 구하기
SELECT SUM(SCORE) AS SCORE, B.EMP_NO, EMP_NAME, POSITION, EMAIL
-- 1.MULTIPLE JOIN
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
-- 2.2022년도 기준
WHERE YEAR= 2022
GROUP BY EMP_NO
-- 가장 높은 값 출력
ORDER BY 1 DESC LIMIT 1
;

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

기존에 작성했었던 정답

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');

새로 풀어본 답

• BASS와 SNAPPER의 수를 출력하는 SQL 문이다.
• WHERE 구문을 통해서 조건을 먼저 설정하고 값의 개수를 COUNT하여 결과값을 표현한다.
• 이때 LEFT JOIN을 써서 물고기 종류별 어떤 물고기가 있는지를 모두 파악해야한다.(개수 때문에)
  SELECT DISTINCT COUNT(*) AS FISH_COUNT
  FROM FISH_INFO A LEFT JOIN FISH_NAME_INFO B
  ON A.FISH_TYPE = B.FISH_TYPE
  WHERE FISH_NAME = 'BASS' OR FISH_NAME = 'SNAPPER' ;

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

기존에 작성했었던 정답

새로 풀어본 답

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

기존에 작성했었던 정답

새로 풀어본 답

<LEVEL 3>

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 💦

1.오답

SELECT DISTINCT A.CAR_ID,
CASE WHEN A.START_DATE <= '2022-10-16' AND A.END_DATE >= '2022-10-16' THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
WHERE A.END_DATE IN (

SELECT MAX(END_DATE)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID)

ORDER BY A.CAR_ID DESC;

2.오답

-- 새로운 컬럼 만들기 : 대여중,대여가능
/SELECT HISTORY_ID, CAR_ID,
CASE WHEN (START_DATE<='2022-10-16' AND END_DATE >='2022-10-16' ) THEN '대여중'
WHEN START_DATE >= '2022-10-16' THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY/

-- 최근값을 나타내게 하기
/SELECT CAR_ID,MAX(START_DATE) AS START_DATE,MAX(END_DATE) AS END_DATE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
;/

-- 결합하기

SELECT A1.CAR_ID,

CASE WHEN
(A1.START_DATE<='2022-10-16' AND A1.END_DATE >='2022-10-16' ) THEN '대여중'
WHEN A1.START_DATE > '2022-10-16' THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY

FROM (SELECT CAR_ID,MAX(START_DATE) AS START_DATE,MAX(END_DATE) AS END_DATE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC) AS A1