🚩오늘의 문제는?

SQL - level 3

1.조건에 맞는 사용자와 총 거래금액 조회
2.💥즐겨찾기가 가장 많은 식당 정보 출력하기
3.없어진 기록 찾기
4.조건에 맞는 사용자 정보 조회하기
5.부서별 평균 연봉 조회하기
6.대장균의 크기에 따라 분류하기 1

1.조건에 맞는 사용자와 총 거래금액 조회(GROUP BY)

문제 정의

문제 풀이

SELECT USER_ID,NICKNAME,SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD A JOIN USED_GOODS_USER B
ON A.WRITER_ID=B.USER_ID
WHERE STATUS='DONE'
GROUP BY USER_ID
HAVING SUM(PRICE)>=700000
ORDER BY TOTAL_SALES ASC;

💥2.즐겨찾기가 가장 많은 식당 정보 출력하기(GROUP BY)

문제 정의

문제 풀이

정답 1 : WHERE 서브쿼리를 활용해서 구하기

• 서브쿼리 안) 음식 종류별 즐겨찾기 수 최대값을 구하기 + WHERE 절에서 음식종류를 묶어주기!!
• 서브쿼리 밖 : 즐겨찾기 수를 묶기

SELECT FOOD_TYPE,REST_ID,REST_NAME,FAVORITES 
FROM REST_INFO R
WHERE FAVORITES = 
(
SELECT MAX(FAVORITES)
FROM REST_INFO R2
WHERE R.FOOD_TYPE = R2.FOOD_TYPE)
ORDER BY FOOD_TYPE DESC
;

정답 2 : 윈도우 함수 ROW_NUMBER() 을 활용 하기

SELECT R2.FOOD_TYPE,R2.REST_ID,R2.REST_NAME, R2.FAVORITES 
FROM(
SELECT R.FOOD_TYPE, R.REST_ID, R.REST_NAME , R.FAVORITES, ROW_NUMBER() OVER (
PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RN
FROM REST_INFO R) AS R2
WHERE R2.RN=1
ORDER BY FOOD_TYPE DESC;

주의할 점

• 윈도우 함수 이용해서 구하기 : ROW_NUMBER() OVER 함수를 따로 써서 해당 값을 WHERE 절에
  조건으로 걸어야한다.

• WHERE 절 안 서브쿼리 활용해서 구하기 : 즐겨찾기를 종류별로 묶어야한다.
  https://moonpiechoi.tistory.com/128

3.없어진 기록 찾기(JOIN)

문제 정의

문제 풀이

SELECT B.ANIMAL_ID, B.NAME
FROM ANIMAL_INS A RIGHT JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.ANIMAL_ID IS NULL;

4.조건에 맞는 사용자 정보 조회하기(String, Date)

문제 정의

문제 풀이

SELECT USER_ID,NICKNAME, CONCAT(CITY,' ',STREET_ADDRESS1,' ',STREET_ADDRESS2) AS 전체주소, 
CONCAT(SUBSTR(TLNO,1,3),'-',SUBSTR(TLNO,4,4),'-',SUBSTR(TLNO,8,4)) AS 전화번호
FROM USED_GOODS_BOARD A  JOIN USED_GOODS_USER B
ON A.WRITER_ID = B.USER_ID
GROUP BY A.WRITER_ID 
HAVING COUNT(A.WRITER_ID )>=3 
ORDER BY B.USER_ID DESC ;

주의할 점

• 전체주소 클릭할 때 많은 CITY을 안 더한 상태로 SQL을 실행해 오류가 떴다. 자세히보자!
• 다른 답안 : 서브쿼리 사용 (WHERE절 활용)

1. 서브쿼리 안에서 값을 조인하기

SELECT USER_ID, NICKNAME,
CONCAT(CITY,' ',STREET_ADDRESS1,' ',STREET_ADDRESS2) AS 전체주소,
CONCAT(SUBSTR(TLNO, 1, 3), '-', SUBSTR(TLNO, 4, 4), '-', SUBSTR(TLNO, 8, 4)) AS 전화번호
FROM USED_GOODS_USER
WHERE (
    SELECT COUNT(*) FROM USED_GOODS_BOARD
    WHERE USER_ID = WRITER_ID) >= 3
ORDER BY USER_ID DESC

2. 조인하지 않고 값을 구하기: WHERE 구문 안에 다른 테이블 값을 넣기

SELECT
    USER_ID,
    NICKNAME,
    CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) '전체주소',
    CONCAT(SUBSTRING(TLNO , 1 , 3) , '-' , SUBSTRING(TLNO , 4 , 4) , '-' , SUBSTRING(TLNO , 8 , 4)) '전화번호'
FROM
    USED_GOODS_USER
WHERE 
    USER_ID IN (SELECT WRITER_ID AS USER_ID
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    HAVING COUNT(*) >= 3)
ORDER BY
    USER_ID DESC

5.부서별 평균 연봉 조회하기(GROUP BY)

문제 정의

문제 풀이

활용하기

SELECT AVG(SAL) AS AVG_SAL
FROM HR_DEPARTMENT D JOIN HR_EMPLOYEES E
ON D.DEPT_ID= E.DEPT_ID
GROUP BY DEPT_NAME_KR

오답1

SELECT DEPT_ID, DEPT_NAME_EN,
(SELECT ROUND(AVG(SAL),0) 
FROM  HR_EMPLOYEES E
WHERE E.DEPT_ID=D.DEPT_ID
GROUP BY DEPT_NAME_KR) AS AVG_SAL
FROM HR_DEPARTMENT D
ORDER BY AVG_SAL DESC;

정답

SELECT A.DEPT_ID, A.DEPT_NAME_EN,ROUND(AVG(SAL),0) AS AVG_SAL
FROM HR_DEPARTMENT A JOIN HR_EMPLOYEES B
ON A.DEPT_ID= B.DEPT_ID
GROUP BY A.DEPT_ID
ORDER BY AVG_SAL DESC;

6.대장균의 크기에 따라 분류하기 1(SELECT)

문제 정의

문제 풀이

SELECT ID,
CASE WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
WHEN SIZE_OF_COLONY>1000 THEN 'HIGH'
ELSE 'MEDIUM' 
END AS SIZE
FROM ECOLI_DATA;

+참고자료
https://suy379.tistory.com/106