Q. 다른 테이블에서 어떤 레코드를 참조할 때, 한 테이블에서 해당 레코드를 삭제하려고 한다.

다음의 테이블을 생성한다.
각 행은 사고가 발생한 부서와 사고 유형을 나타낸다.

/* Oracle */
create table dept_accidents(
		deptno 		  integer,
        accident_name varchar(20)
        );
        
insert into dept_accidents values (10, 'BROKEN FOOT');
insert into dept_accidents values (10, 'FLESH WOUND');
insert into dept_accidents values (20, 'FIRE');
insert into dept_accidents values (20, 'FIRE');
insert into dept_accidents values (20, 'FLOOD');
insert into dept_accidents values (30, 'BRUISED GLUTE');

select * from dept_accidents;
>>
DEPTNO	ACCIDENT_NAME
10		BROKEN FOOT
10		FLESH WOUND
20		FIRE
20		FIRE
20		FLOOD
30		BRUISED GLUTE
6 rows selected.

사고가 세 번 이상 발생한 부서에서 근무하는 사원의 레코드를 'EMP' 테이블에서 삭제하려고 한다.

A. 서브쿼리와 집계함수 COUNT()를 사용하여 세 번 이상 사고가 발생한 부서를 찾는다

이후 해당 부서에 일하는 모든 사원을 삭제한다.

/* 전후 확인을 위해 EMP 레코드 개수 알아두기 */
select count(*) as number_of_records_from_EMP from emp;
>>
NUMBER_OF_RECORDS_FROM_EMP
14

/* 서브쿼리 따로 확인해보기 */
/* count(*)를 넣어주면 더 알기 쉽다 */
select deptno, count(*)
    		from dept_accidents
			group by deptno
			having count(*) >= 3
>>              
DEPTNO	COUNT(*)
20		3

/* 해법 쿼리 */
delete from emp						
	where deptno in ( select deptno
    						from dept_accidents
                            group by deptno
                            having count(*) >= 3
                    );
>>
5 row(s) deleted.

/* 정상적으로 삭제되었는지 확인 */
select * from emp;
>>
EMPNO	ENAME	JOB			 MGR	HIREDATE	SAL		COMM	DEPTNO
7839	KING	PRESIDENT	 - 		17-NOV-81	5000	 - 		10
7698	BLAKE	MANAGER		7839	01-MAY-81	2850	 - 		30
7782	CLARK	MANAGER		7839	09-JUN-81	2450	 - 		10
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300		30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500		30
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0		30
7900	JAMES	CLERK		7698	03-DEC-81	950	 	 - 		30
7934	MILLER	CLERK		7782	23-JAN-82	1300	 - 		10
9 rows selected.

서브쿼리의 GROUP BY는 'DEPTNO'가 동일한 레코드끼리 묶어준다.
FROM -> GROUP BY -> HAVING -> SELECT 순으로 작동한다.
'DEPTNO'로 그룹화했을 때 COUNT(*)이 반환하는 숫자는, 해당 부서에서 일어난 사고의 개수를 의미하게 된다.
마지막 결과셋에서 'DEPTNO'가 20인 레코드는 모두 사라진 것을 확인할 수 있다.

정리

이 책은 장의 제목을 보면 볼 수록 알쏭달쏭하다.
GROUP BY에 많이 편해진 것을 느낀다.
특히, 집계 함수가 GROUP BY에 의한 그룹화가 이루어진 후에 사용되어야 한다는 관계를 알고 나서 더 편해진 것 같다.

이번 장은 조금은 쉬어가면서 여러 가지 함수들을 복습할 수 있어서 유익했다!