[국민취업지원제도 일경험프로그램 13일차] SQL 문제 풀이 및 함수 정리

13일 차 특이사항

1. SQL 2문제 풀기 - SAL 부분을 합쳐서 출력하기, GROUP BY 쓰지 않고 같은 결과가 나오게 하라.  
2. SPRING 게시판 만들기 복습 - INSERT 

3. SQL 함수 정리 - 함수를 많이 알면 좋다. 정리해 두자. 

 

SQL 문제

위 그림처럼 나오도록 SELECT 하시오

1. 풀이 

SELECT B.DEPTNO, B.DNAME, LISTAGG(A.SAL, ',') WITHIN GROUP (ORDER BY A.SAL) AS SALCOUNT
FROM DEPT B, EMP A
WHERE B.DEPTNO = A.DEPTNO(+)
GROUP BY B.DEPTNO, B.DNAME;

: DEPT 테이블을 기준으로 EMP 테이블을 조인해준다. RIGHT JOIN이다. DEPT 테이블의 DEPTNO, DEPT테이블의 DNAME으로 그룹화를 해줘서 중복을 제거한다. 마지막으로 SALCOUNT 칼럼은 LISTAGG WITHIN GROUP 함수를 사용하여 EMP 테이블의 SAL 칼럼의 데이터 값들을 ','로 표시해서 출력했다. 

 

2. 4번 문제에서 GROUP BY를 쓰지 않고 똑같은 결과가 나오도록 하기 

 

[기존 풀이 방법]

SELECT A.DEPTNO, A.DNAME, COUNT(B.DEPTNO) AS EMPLOYEE_COUNT
FROM DEPT A 
LEFT OUTER JOIN EMP B 
ON A.DEPTNO = B.DEPTNO 
group by A.DEPTNO, A.DNAME; 


select A.DEPTNO, A.DNAME, COUNT(B.DEPTNO) AS EMPLOYEE_COUNT 
from dept a, emp b 
where a.deptno=b.deptno(+) 
group by A.DEPTNO, A.DNAME;

 

[새로운 풀이 방법] 

SELECT
    A.DEPTNO,
    A.DNAME,
    (SELECT COUNT(*) FROM emp B WHERE A.DEPTNO = B.DEPTNO) AS "사원수"
FROM dept A;

: 서브쿼리를 사용하여 새로운 컬럼을 만든다. EMP 테이블에서 DEPT 테이블의 DEPTNO와 같은 DPETNO를 조회한다. 그럼 부서별 사원수가 나온다. 

 

SQL 함수 정리 

1. decode() : CASE 표현식의 하나로서, ORACLE에만 존재하는 함수다. DECODE는 등가조건(=)에만 사용이 가능하다. 

SELECT job, DECODE(job, 'PRESIDENT', 'A', 'B') FROM emp;

: EMP 테이블의 job 컬럼에서 job='PRESIDENT'인 사람은 A를 주고, 나머지는 B를 준다. 

 

2. case(): IF문과 비슷하다. 조건식을 만족하는 컬럼의 경우 반환값을 출력한다. 등가조건이 아닌 범위를 주고 싶을 때 사용한다. 

 

CASE WHEN <조건식1> THEN <반환값1>...

END 

 

select job, sal, 
case WHEN SAL>=5000 THEN 'A' ELSE 'B'
END
from emp;

: EMP 테이블에서 칼럼 JOB, SAL에서 연봉이 5000 이상인 경우 A 아닌 경우 B를 준다. 

 

3. over(): 단독으로 사용할 수 없고 집계함수랑 같이 사용해야 한다. GROUP BY + ORDER BY라고 이해하면 쉽다. 

select empno, ename, sal, deptno, rank() over(partition by deptno order by sal desc)
from emp; 

select empno, ename, sal, deptno, sum(sal)over(partition by deptno order by sal desc)
from emp; 

select empno, ename, sal, deptno, sum(sal)over(order by sal desc)
from emp;

: partition by를 빼면 부서별로 정렬되지 않고 sal를 기준으로 내림차순 정렬된다. 

 

4. lpad(), rpad() 

 

LPAD(<문자열1>,<숫자>,<문자열2>) : 문자열1의 좌측으로 문자열2를 추가하여 결과값이 총<숫자>바이트의 문자열을 생성한다. 

 

RPAD(<문자열1>,<숫자>,<문자열2>) : 문자열1의 우측으로 문자열2를 추가하여 결과값이 총<숫자>바이트의 문자열을 생성한다. 

 

SELECT LPAD('안녕하세요',20,'윽윽')
FROM DUAL;

 

SELECT RPAD('안녕하세요',20,'윽윽')
FROM DUAL;