Sub Query

• 쿼리 안의 쿼리라는 뜻으로 사전에 추출된 내용에서 재 검색 하거나. 검색된 내용을 가상 컬럼을 만들어 추가 할 수 있다. 즉, 서브쿼리를 사용하는 이유는 가져온 데이터를 재정제 하기 위함이다.
• 서브쿼리가 메인쿼리의 결과물에 일부분으로 소속되는 것을 상하관계 쿼리라고 부른다.

SubQuery 실습

테이블 생성

• 부서 테이블
  create table dept(
  deptno varchar(10) primary key
  ,deptname varchar(20)
  ,loc varchar(10)
  );

• 직원 테이블
  create table emp(
  ename varchar(20)
  ,job varchar(50)
  ,deptno varchar(10)
  ,hiredate date
  );

제약조건 추가 및 데이터 삽입

• 부서테이블의 deptno를 직원테이블에서 foreign key로 설정
  alter table emp add constraint foreign key(deptno) references dept(deptno);

• 데이터 추가
  insert into dept(deptno,deptname,loc) values(1,'sales','newyork');
  insert into dept(deptno,deptname,loc) values(2,'dev01','LA');
  insert into dept(deptno,deptname,loc) values(3,'pernonnel','newyork');
  insert into dept(deptno,deptname,loc) values(4,'delevery','boston');
  insert into emp(ename,job,deptno,hiredate) values('kim','manager',1,str_to_date('16/01/02','%Y/%m/%d'));
  insert into emp(ename,job,deptno,hiredate) values('lee','staff',1,str_to_date('15/01/02','%Y/%m/%d'));
  insert into emp(ename,job,deptno,hiredate) values('han','staff',1,str_to_date('16/03/02','%Y/%m/%d'));
  insert into emp(ename,job,deptno,hiredate) values('kim','assistant',1,str_to_date('15/09/22','%Y/%m/%d'));
  insert into emp(ename,job,deptno,hiredate) values('ahn','staff',2,str_to_date('15/11/02','%Y/%m/%d'));
  insert into emp(ename,job,deptno,hiredate) values('hwang','manager',2,str_to_date('15/08/12','%Y/%m/%d'));
  insert into emp(ename,job,deptno,hiredate) values('cha','assistant',2,str_to_date('12/03/02','%Y/%m/%d'));
  insert into emp(ename,job,deptno,hiredate) values('hong','staff',2,str_to_date('14/08/02','%Y/%m/%d'));
  insert into emp(ename,job,deptno,hiredate) values('gang','staff',2,str_to_date('16/01/02','%Y/%m/%d'));
  insert into emp(ename,job,deptno,hiredate) values('nam','leader',4,str_to_date('10/01/02','%Y/%m/%d'));

데이터 확인

select from dept;
select from emp;

ERD

• Q1) 이름이 'han'이 일하는 근무 부서 조회
  - step 1. emp 테이블에서 ename='han' 찾아 deptno 조회 (deptno=1)
  select deptno from emp where ename='han';
  - step 2. dept 테이블에서 deptno=1 인 deptname 조회 (deptname='sales')
  select deptname from dept where deptno=1;
  - A1) SubQuery를 이용하여 하나의 Query로 표현하기
  select deptname from dept where deptno=(select deptno from emp where ename='han');

• Q2) 부서의 위치가 LA나 boston인 부서에 속한 사람들의 이름과 직책 조회
  - step 1. dept 테이블에서 loc='LA' or loc='boston'인 deptname 조회
  select deptno from dept where loc='LA' or loc='boston';
  - step 2. emp 테이블에서 deptno='2' or deptno='4'인 직원의 ename과 job 조회
  select ename, job from emp where deptno in (2,4);
  - A2) SubQuery를 이용하여 하나의 Query로 표현하기
  select ename, job from emp where deptno in (select deptno from dept where loc='LA' or loc='boston');

• Q3) sales 부서에서 일하는 사원의 전체 데이터를 조회
  - step 1. dept 테이블 에서 deptname='sales'인 deptno 조회
  select deptno from dept where deptname='sales';
  - step 2. emp 테이블에서 deptno=1 데이터 조회
  select from emp where deptno=1;
  - A3) SubQuery를 이용하여 하나의 Query로 표현하기
  select from emp where deptno=(select deptno from dept where deptname='sales');

• Q4) 직책(job)이 manager인 사원(여러명일 경우 가장 빠른 날짜 기준)보다 입사일이 빠른 직원 조회
  -- step 1. emp 테이블에서 job='manager'인 사람을 찾아 min(hiredate) 조회
  select min(hiredate) from emp where job='manager';
  - step . emp 테이블에서 hiredate<'min(hiredate)' 인 직원 조회
  select from emp where hiredate<'min(hiredate)' order by hiredate asc;
  - A4) SubQuery를 이용하여 하나의 Query로 표현하기
  select from emp where hiredate<(select min(hiredate) from emp where job='manager') order by hiredate asc

• Q5-1) 부서 별로 직원이 몇명인지 조회 (Group By 사용)
  - step 1. emp 테이블에서 group by deptno 하여 count(deptno) 조회
  select deptno,count(deptno) as dept_cnt from emp group by deptno;
  - step 2. dept 테이블에서 deptno in (1,2,4)인 deptname 조회
  select deptname from dept where deptno in (1,2,4);
  - A5) SubQuery를 이용하여 하나의 Query로 표현하기
  - 서브쿼리가 메인쿼리의 결과물에 일부분으로 소속되는 것을 상하관계 쿼리라고 부른다.
  select (select d.deptname from dept d where d.deptno = e.deptno) as dept_name,count(deptno) as dept_cnt from emp e group by deptno;

• Q5-2) 부서 별로 직원이 몇명인지 조회 (Group By 사용 X)
  - step 1. emp 테이블에서 deptno in (1,2,4)인 데이터의 수를 조회
  select count(deptno) from emp where deptno=1;
  select count(deptno) from emp where deptno=2;
  select count(deptno) from emp where deptno=4;
  - step 2. dept 테이블에서 deptno in (1,2,4)인 deptname 조회
  select deptname from dept where deptno=1;
  select deptname from dept where deptno=2;
  select deptname from dept where deptno=4;
  - step 3. SubQuery를 이용하여 하나의 Query로 표현하기
  select deptname, (select count(deptno) from emp where deptno=1) as dept_cnt from dept where deptno=1;
  select deptname, (select count(deptno) from emp where deptno=2) as dept_cnt from dept where deptno=2;
  select deptname, (select count(deptno) from emp where deptno=4) as dept_cnt from dept where deptno=4;
  -- step 4. SubQuery 단순화
  select deptname, (select count(deptno) from emp where deptno=d.deptno) as dept_cnt from dept d;