SQL 어려운 문제 - SQL eolyeoun munje

Oracle SQL Developer - SELECT문을 활용한 SQL 문제 만들기
>> 예전 DB수업 시간에 실습했던 문제를 공유합니다.

🔗 사용할 계정 : Homework
🔗 사용할 TABLE : EMPLOYEE, DEPARTMENT, LOCATION, JOB, NATIONAL, SAL_GRADE
🔗 조건 : SELECT문을 활용한 그 어느 쿼리라도 가능

<<Table 종류 전체 조회>>

SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;
SELECT * FROM LOCATION;
SELECT * FROM JOB;
SELECT * FROM NATIONAL;
SELECT * FROM SAL_GRADE;

🔑 Story를 통한 SQL 문제 만들기

• 모든 테이블 JOIN해서 전체보기

• 수업 때 배운 내용을 적절하게 섞어서 만들기

• 문제 꼬아서 내지 않기 - 내가 어려우면 남들도 어려워

• 1-2 DB_HW(조원이름).sql

📌 JOIN을 통한 전체 사원의 정보 조회해보기

SELECT * FROM EMPLOYEE

JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE)
JOIN NATIONAL USING(NATIONAL_CODE)
LEFT JOIN SAL_GRADE USING(SAL_LEVEL)
ORDER BY EMP_ID, EMP_NAME;

문제 1.

이번 태풍으로 인해 일본 후쿠시마 지역에 있던 방사능 폐기물이 퍼지게 되었습니다.
따라서 본사는 일본 지사에 있는 모든 직원들을 한국으로 귀국 조치 시키고자 합니다.
근무지역 코드가 'L2'인 직원의 사번, 사원명, 부서명, 직급코드, 연락처, 이메일, 근무지역을 조회하시오.


SELECT EMP_ID 사번, EMP_NAME 사원명, DEPT_TITLE 부서명, JOB_CODE 직급코드,
PHONE 연락처, EMAIL 이메일, LOCAL_CODE 위치코드, NATIONAL_CODE 근무지역코드,
NATIONAL_NAME 근무지역

FROM EMPLOYEE

JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN LOCATION ON(LOCAL_CODE = LOCATION_ID)
JOIN NATIONAL USING(NATIONAL_CODE)

WHERE LOCAL_CODE = 'L2';

문제 2.

개발팀의 실수로 사원들의 연락처가 유출되었습니다.
모든 사원들의 연락처 뒤 4자리를 '*'로 채우고 (연락처가 없는 사람들은 고려하지 않음)
사번, 사원이름, 연락처, 부서명을 조회하는 쿼리문을 작성하시오.


SELECT EMP_ID 사번, EMP_NAME 사원명, RPAD(SUBSTR(PHONE, 1, 7), 11,'*') 연락처수정,
DEPT_TITLE 부서명

FROM EMPLOYEE

JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

문제 3.

연락처를 수정하다 보니, 011, 017 번호를 쓰는 직원들을 위해
최신 갤럭시 노트 10+를 회사 복지 차원으로 지급하기로 했다.
연락처가 '011', '017'로 시작하는 직원들의 사번, 사원명, 연락처, 부서명,
직급명을 조회하고 연락처를 '010'으로 시작하는 번호로 수정하는
쿼리문을 작성하시오.



SELECT EMP_ID 사번, EMP_NAME 사원명, PHONE 연락처, DEPT_TITLE 부서명, JOB_NAME 직급명

FROM EMPLOYEE

JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE SUBSTR(PHONE, 1, 3) IN('011', '017');

UPDATE EMPLOYEE
SET PHONE = '01047365678'
WHERE SUBSTR(PHONE, 1, 3) = ('011');

UPDATE EMPLOYEE
SET PHONE = '01012839065'
WHERE SUBSTR(PHONE, 1, 3) = ('017');

ROLLBACK;

SELECT EMP_ID, EMP_NAME, PHONE
FROM EMPLOYEE
WHERE EMP_NAME IN('윤은해', '심봉선');

문제 4.

본사는 근무년수가 5년 이상인 사람들에게 장기 근속 수당(월급의 20%)과 함께
3일간의 휴가를 주기로 했습니다.
근무년수가 5년 이상 29년 미만(대표 제외)인 사원들의 사번, 사원명, 부서코드, 직급코드, 
직급명, 장기근속수당을 조회하시오.


SELECT EMP_ID 사번, EMP_NAME 사원명, DEPT_CODE 부서코드, JOB_CODE 직급코드,JOB_NAME 직급명,
TO_CHAR(TRUNC(SALARY*0.2, -4), 'L999,999,999') 장기근속수당

FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)

WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12) BETWEEN 5 AND 28;

문제 5.

본사에 위기가 닥쳐 급히 구조조정 공문이 내려왔습니다.
입사년도가 2000년 이전인 직원이거나 부서별 가장 높은 월급을
받는 직원들부터 1순위로 명예퇴직 대상자입니다.
해당 조건에 맞는 직원들의 사번, 사원명, 부서코드, 직급코드, 월급,
입사일을 조회하시오.
(단, 월급은 TO_CHAR 이용하여 형식 표기(좌우 공백제거)/ 부서코드가 null일 경우 '없음' 표기)



SELECT EMP_ID 사번, EMP_NAME 사원명, NVL(DEPT_CODE,'없음') 부서코드,
JOB_CODE 직급코드, TRIM(TO_CHAR(SALARY, 'L999,999,999')) 월급, HIRE_DATE 입사일

FROM EMPLOYEE

WHERE (DEPT_CODE, SALARY) IN(SELECT DEPT_CODE, MAX(SALARY)
FROM EMPLOYEE GROUP BY DEPT_CODE)
OR HIRE_DATE < TO_DATE('00/01/01');

카테고리 초보자도 준비하는 SQL 코딩 테스트에 가시면 해당 시리즈를 확인하실 수 있습니다.

• 해당 시리즈를 시작하며
• 기초 이론 다지기
  • 이론 및 실습 환경 세팅 샘플 데이터 및 샘플 쿼리문
  • 데이터베이스와 SQL이란?
    1. SELECT의 WHERE과 ORDER BY
    2. 집계 함수와 GROUP BY
    3. LIMIT와 서브 쿼리에 대하여
    4. 중복과 NULL 다루기
    5. SQL 코테의 꽃 조인
• 초보자도 준비하는 SQL 코딩 테스트 시리즈를 마치며 부제-이제 뭘 해야 하나요?

목차

• 집계 함수
  • AVG, MIN, MAX, COUNT
  • 실습 해보기
  • 혼자 생각해보기
• GROUP BY
  • 기본적인 GROUP BY 형식
  • GROUP BY를 더 깊게 사용하기
  • 혼자 생각해보기
• HAVING
  • 왜 HAVING이 필요할까
  • HAVING절 사용하기

지난 시간에 우리는 SELECT의 기본적인 사용 방법과 SELECT와 함꼐 쓰일 수 있는 WHERE, ORDER BY에 대해서 배웠다.

이번 시간에는 우리가 원하는 데로 데이터를 묶어주는 GROUP BY에 대해서 알아보자.

GROUP BY 절은 SQL 코딩 테스트에서 자주 출제되는 유형중 하나이다.

집계 함수

집계 함수는 특정 값들을 말 그대로 집계 할 때 사용된다.

집계에는 여러 가지 종류가 존재한다.

그 중에 대표적인 4가지를 봐보자.

• AVG : 평균을 구하는 집계
• MIN : 최소를 구하는 집계
• MAX : 최대를 구하는 집계
• COUNT : 총 개수를 구하는 집계

그럼 우리는 다음과 같은 쿼리를 처리할 수 있다.

buytb1 에서 총 얼만큼 주문되었는지 총 주문량을 출력하라

SELECT sum(amount)
FROM buytb1;

usertb1 에서 사용자들의 평균 키를 출력하라

SELECT avg(height)
FROM usertb1;

이런 집계 함수는 사용될 수 있는 쿼리문 영역이 매우 다양하며, 자주 사용될 수 있다.

그 말은 코딩 테스트에서도 기본적으로 이런 집계 함수를 이용하는 문제가 자주 출제될 수 있다는 소리이다.

혼자 생각해보자!

만약 usertb1 에서 키가 가장 큰 사용자와 키가 가장 작은 사용자의 이름을 출력하라! 라고 한다면 어떤 쿼리문이 나올까?

분명 여러분은 할 수 있을 것이라고 생각한다.

못 해도 좋습니다. 이건 꽤나 어려운 문제에요! 아주 어렵지만 여러번 보고 여러 문제를 보면 할 수 있으니 걱정하지 마세요 :)
지금은 몰라도 아마 한 80% 예상하는데, 아마 이론 시리즈만 끝내면 이 문제 스스로 풀 수 있을걸요?
만약 답을 모르겠다면 비밀 댓글도 좋으니 달아주세요 ㅎㅎ

GROUP BY

SELECT 필드명
FROM 테이블명
GROUP BY 필드명 혹은 표현식

우선 GROUP BY에 대해서 알기 전에 어디서 사용되는지 예제를 먼저 접하고 이해해보자.

• 1 buytb1 에서 물품 별로 총 몇 개가 주문되었는지 출력하라
• 2 usertb1 에서 지역 별로 사용자들의 키 평균이 몇인지 출력하라
• 3 usertb1 에서 번호 앞 자리(010인지 011인지) 별로 몇 명이 있는지 출력하라.
• 4 buytb1 에서 물품 별로 가장 많이 주문된 것을 출력하라

GROUP BY를 사용하지 않고 위의 4개 쿼리를 혼자서 한 번 수행해보자.
쉽지 않을 것이다. 이 과정을 거친다면 왜 우리는 GROUP BY 함수를 써야하는지 아주 직관적이게 이해할 수 있을 것이다.

위에 나온 문제들은 모두 GROUP BY 절이 사용되어야 출력할 수 있다.

그럼 공통점을 한 번 찾아보자.

• 총 몇개, 평균이 몇 명, 가장 큰, 몇 명이 있는지와 같은 집계 함수가 사용되었다.
• xx 별로라는 말이 쓰였다.

이 둘을 종합해서 본다면, GROUP BY는 집계 함수를 통해서 xx 별로 그들 만의 그룹을 만들어주는 함수라고 정의할 수 있다.

그럼 각각의 문제에 대해서 쿼리를 진행할 수 있다.

/* 1번 buytb1 에서 **물품 별로** 총 몇 개가 주문되었는지 출력하라 */
SELECT prodname, count(amount)
FROM buytb1
GROUP BY prodname;

/* 2번 usertb1 에서 **지역 별로** 사용자들의 키 평균이 몇인지 출력하라 */
SELECT addr, avg(height)
FROM usertb1
GROUP BY addr;

/* 3번 usertb1 에서 **번호 앞 자리(010인지 011인지) 별로** 몇 명이 있는지 출력하라.
 */
SELECT mobile1, count(*)
FROM usertb1
GROUP BY mobile1;

/* 4번 buytb1 에서 **물품 별로** 가장 많이 주문된 것을 출력하라 */
SELECT prodname, max(amount)
FROM buytb1
GROUP BY prodname;

GROUP BY 함수를 좀 더 깊게 사용해보자.

buytb1 에서 사용자들의 총 구매 금액을 출력하라.

우선 우리는 사용자 별로 구매 금액을 따로 나눠야 하니 사용자를 그룹으로 묶어줘야 한다.

그리고 각각의 총 구매 금액을 계산해보자.

SELECT userid, amount * price
FROM buytb1;

을 수행하면 각각 사용자의 물품별 구매 금액이 나온다.

우리는 이 물품별 구매 금액을 모두 합산해야 하므로 sum을 이용해 집계할 수 있다.

SELECT userid, sum(amount * price)
FROM buytb1;

오 근데 에러가 난다.

왜냐? 집계 함수로 집계를 한 결과가 어떤 그룹에 속해야 할지 모르기 때문에.

userid는 여러 개가 존재하는데, 그 중 어디에 속해야 할지 므로므로 userid로 GROUP 지어주면 된다.

SELECT userid, sum(amount * price)
FROM buytb1
GROUP BY userid;

그럼 우리가 원하는 결과가 아주 아름답게 잘 나오게 된다.

혼자 생각해보자!

만약 buytb1에서 사용자들이 평균적으로 몇 개의 물건을 샀는지 출력하라고 하면 어떻게 해야할까?

이 문제 또한 못 해도 좋습니다. 앞서 말 했듯 여러번 보고 여러 문제를 보면 할 수 있으니 걱정하지 마세요 :)
만약 답을 모르겠다면 비밀 댓글도 좋으니 달아주세요 ㅎㅎ

HAVING

그럼 내친김에 이것도 풀어보자.

buytb1 에서 사용자들의 총 구매 금액이 1500 이상인 사용자를 출력하라.

위에서 우리는 총 구매 금액을 구한적이 있다.

SELECT userid, sum(amount * price)
FROM buytb1
GROUP BY userid;

그럼 이제 1500 이상인 사용자를 구하는 것은 아주 쉽다.

WHERE 을 사용하자.

SELECT userid, sum(amount * price)
FROM buytb1
GROUP BY userid
WHERE sum(amount * price) > 1500;

엥? 안된다.

우리의 논리라면 돼야 하는데 안된다..

이유는 우리의 논리라서 그렇다.

우리의 논리를 좀 확장 시키면 이건 당연히 안되는 것이 된다.

논리를 확장시켜보자.

집계 함수에서 조건을 줄 때

집계 함수에서 조건을 줄 때는 WHERE 대신 HAVING을 사용한다.

즉, HAVING 절은 집계 함수에서 조건을 줄 때 사용한다.

결국 우리는 위에서 조건을 집계함수를 사용한 값으로 줬기 때문에 동작하지 않는 것이다.

이제 HAVING이 왜 필요한지 알게 되었다.

HAVING절 사용 방법

SELECT 필드명
FROM 테이블명
GROUP BY 필드명 혹은 표현식
HAVING where조건

여기서 HAVING은 where 조건식과 동일한 형태의 조건식이 들어갈 수 있다.

그리고 중요한 것이 HAVING은 무조건 GROUP BY 뒤에 위치해야 한다.

이제 우리는 앞서 봤던 문제를 해결할 수 있게 되었다.

SELECT userid, sum(amount * price)
FROM buytb1
GROUP BY userid
HAVING sum(amount * price) > 1500;

카테고리 초보자도 준비하는 SQL 코딩 테스트에 가시면 해당 시리즈를 확인하실 수 있습니다.