Day - 34 [Maria DB] Sub Query

Sub Query

• 서브쿼리는 "쿼리 안의 쿼리" 라는 뜻이다.
• 서브쿼리는 사전에 추출된 내용에서 재 검색 하거나, 검색된 내용을 가상 컬럼을 만들어 주가 할 수 있다.
• 즉, 서브쿼리를 사용하는 이유는 가져온 데이터를 재 정제 하기 위함이라 볼 수 있다.(데이터 재 가공)

---

-- 부서테이블

-- 직원테이블

-- 부서 테이블
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
);

 

 

-- 이미 만들어진 테이블에 추가

 

 

참조제약조건 : foreign key 
다른 외부 테이블의 key 를 가져와 (참조) 사용한다.
-- 2) 이미 만들어진 테이블에 추가
-- ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건 타입] (컬럼) references [참조테이블] (참조컬럼)
alter table child_table add constraint foreign key (user_id) references parent_table(user_id);
-- ^ 참고

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,'personnel','newyork');
insert into dept(deptno,deptname,loc) values(4,'delevery','boston');

select * from dept;

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;

 

---

 

문제 1)

-- 문제 1> han 이 일하는 근무 부서는?
-- han 은 누구지?
select deptno from emp where ename = 'han';
-- dept = 1 인 부서는?
select deptname from dept where deptno = 1;
-- 서브쿼리
select deptname from dept where deptno = (select deptno from emp where ename = 'han');

 

문제 2)

-- 문제 2> 부서 위치가 LA 나 boston 인 부서에 속한 사람들의 이름과 직책
-- dept 에서 loc 가 LA 거나 boston 인 데이터(in 사용)
select deptno from dept where loc in ('LA' , 'boston');
-- deptno 가 2 이거나 4 인 정보를 emp 에 찾는다.
select * from emp where deptno in (2 , 4);
-- 서브쿼리
select * from emp where deptno in (select deptno from dept where loc in ('LA' , 'boston'));

 

문제 3)

-- 문제 3> sales 부서에서 일하는 사원의 전체 데이터
-- 부서명이 sales 인 deptno 찾기
select deptno from dept where deptname = 'sales';
-- deptno 를 이용해 정보 가져오기
select * from emp where deptno = 1 ;
-- 서브쿼리
select * from emp where deptno = (select deptno from dept where deptname = 'sales');

 

문제 4)

 

-- 문제 4> 직책(job)이 manager 인 사원들(여러명일 경우 가장 빠른 날짜 기준)보다 입사일이 빠른 직원은?
-- 직책이 job 이 manager 인 사원들 중 가장 입사일이 빠른 날짜
select min(hiredate) from emp where job = 'manager'; -- 2015-08-12
-- 2015-08-12 보다 입사일이 빠른 사람들 찾기
select * from emp where hiredate < '2015-08-12' order by hiredate;
-- 서브쿼리
select * from emp 
	where hiredate < (select min(hiredate) from emp where job = 'manager') 
	order by hiredate;

select * from dept;
select * from emp;

 

문제 5)

-- 문제 5> 부서별로 직원이 몇명인지 데이터 가져오기
-- deptno 별로 몇명인지 확인
select deptno,count(deptno) as dept_cnt from emp group by deptno;
-- deptno 별(1,2,4)로 이름이 뭔지 확인
select deptname from dept where deptno in (1,2,4);
-- 서브쿼리
-- 서브쿼리가 메인쿼리의 결과물에 일부분으로 소속되는 것을 상하관계 쿼리 라고 부른다.
select 
	(select d.deptname from dept d where deptno = e.deptno) as dept_name
	,count(deptno) as dept_cnt 
from emp e group by deptno;

 

문제 5) group by 쓰지 않고 풀어 쓰기

-- group by 쓰지 않고...
-- deptno 가 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;

-- deptno 별 정보
select deptname from dept where deptno = 1;
select deptname from dept where deptno = 2;
select deptname from dept where deptno = 4;

-- 상하관계 쿼리 
select 
	deptname
	, (select count(deptno) from emp where deptno = 1) as cnt
from dept where deptno = 1;

select 
	deptname
	, (select count(deptno) from emp where deptno = 2) as cnt
from dept where deptno = 2;

select 
	deptname
	, (select count(deptno) from emp where deptno = 1) as cnt
from dept where deptno = 1;

-- 단순화
select 
	deptname
	, (select count(deptno) from emp where deptno = d.deptno) as cnt
from dept d;
-- from dept d , dept 의 d 가 deptno = d.deptno 여기로 가서 count 해서 세라