210607_1(데이터베이스3, 그룹함수, 조인, 서브쿼리)

그룹함수

*** 부서별 평균, 반별 합계, ... 라는 말이 등장하면그룹함수

count() 행갯수. 조직원수 - 사원수, 학급별인원수 ...

sum(), stddev(), avg(표준편차), max(), min() 등등이 있다

 

판별 요령 : 집계함수이외에 일반컬럼이 select절에 나오면 반드시 그룹화(group by 컬럼) 한다.

 

1. emp에서 최고 급여는 얼마 인가요?

select max(sal) from emp;

 

2. emp에서 최소급여는 얼마 인가요?

select Min(sal) from emp;

 

3. emp에서 최대,최소,평균 급여를 출력하시오

select max(sal), min(sal), avg(sal) from emp;

 

4. emp에서 부서별 평균급여와 부서번호를 출력하라

-- 집계함수이외에 일반컬럼이 select절에 나오면 반드시 일반컬럼으로 그룹화해야한다

select avg(sal), deptno from emp group by deptno;

 

5. emp에서 급여가 3000 이상인 직원의 부서별 평균급여와 부서번호를 출력하라

select avg(sal), deptno from emp  where sal>=3000 group by deptno;

 

6. emp에서 급여가 1500이상인 직원의 부서별 평균급여와 부서번호를 출력하되 평균 급여가 3500이상인것만 출력하라

select avg(sal), deptno from emp where sal>=1500 group by deptno where avg(sal)>=3500; <== having절로 바꿔야한다.

select avg(sal), deptno from emp where sal>=1500 group by deptno having avg(sal)>=3500;

-- having은 그룹화 이후의 조건절이 된다.

 

7. 각 부서별 직원의 인원수와 부서번호를 출력하시오

select count(*), deptno from emp group by deptno;

 

8. emp에서 커미션을 받지않는 직원의 숫자는 몇명인가요? (null인경우 0으로 처리하시오)

select count(*) from emp where comm is null; -- null처리가 없는 경우

select count(*) from emp where nvl(comm,0) =0;

 

9. 부서번호가 10인 부서의 인원은 몇명인가요

select count(*) from emp where deptno=10;

 

10. emp에서 직책의 종류는 몇가지인가요 --중복제거 distinct

select count(job) from emp; --직책이 있는 것을 모두센것.

select count(distinct job) from emp;

---

응용

1. 80년도에 입사한 직원은 모두 몇명인가요

select count(*) from emp where substr(HIREDATE,1,2)=80;

 

2. 

     total       1980       1981       1982
---------- ---------- ---------- ----------
        12          1         10          1

 처럼 출력해보세요

select count(*) as "total", sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) as "1980",

 sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) as "1981",

 sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) as "1982" from emp;

---

조인문 *****

조인문이란, 한개(셀프조인)이상의 테이블로부터 데이터를 선택하는것. 

(관계형 테이블에서 많이 사용됨)

 관계형 DB는 중복을 피하기 위하여 컬럼이 여러테이블에 분산되어 있을수 있다.

그 분산된 컬럼이 필요한 경우 사용되는 문장이 join문이다. 

 

join문을 사용해야 하는 가장 쉬운 판단은 select 절에 컬럼이 하나이상의 테이블에 존재할 경우

ex) select ename, emp.deptno, dname

      from emp, dept

       where emp.deptno = dept.deptno; -- 조인절 

 

ex개선) select ename, e.deptno, dname -- 중복되는 컬럼은 모호성을 해결한다.

            from emp e, dept d -- 테이블 축약

            where e.deptno = d.deptno; -- 축약된조인절 

 

조인문 종류

1. 크로스 조인 cross join

 모든 가능한 매칭을 한다. where절이 없는 조인문

아무런의미가 없는 조인문이다. 거의 사용하지 않는다

select ename, e.deptno, dname from emp.deptno = dept.detpno;

  emp12행 * dept 4행 --48행 

48 개의 행이 선택되었습니다 -- 모든 경우의 조합 선택됨

 

2.equi join =

조인절이 일치하는 것만 선택함. 가장 많이 사용함.

 ex) select ename, e.deptno, dname

      from emp.e, dept d

       where e.deptno = d.deptno; <==같은것

 

3.non equi join >= , <=

 조인절이 일치하지 않는 것

 ex) select ename, e.deptno, dname

      from emp.e, dept d

       where e.deptno >= d.deptno; <==같은것

 

 

1) emp와 salgrade테이블로부터 이름, 급여, 급여등급을 출력하시오

select ename, sal, grade

from emp e, salgrade s

where e.sal >= s.losal and e.sal<=s.hisal ;<==같은기능(급여)을 하는 데이타형(number)을 조인해야 한다.

 

2) ALLEN의 매니저는 BLAKE 입니다. 처럼 출력이 가능하도록 조인하시오 --self join

MGR 컬럼의 매니저의 사번을 말한다. 즉 공통컬럼의 empno와 mgr이 된다.

그런데 두 컬럼이 모두 한개의 테이블에 존재하므로 셀프조인을 해야한다.

select rpad(e.ename,20,'의매니저는') , rpad(m.ename,20,'입니다')  from emp e, emp m where e.mgr=m.empno;

select s.ename || '의 매니저는', t.ename || '입니다' --셀프조인은 무조건 모호성이 발생한다.

from emp s, emp t -- 반드시 테이블명을 치환한다

where s.mgr = t.empno; --셀프조인

 

select s.ename || '의 매니저는', t.ename || '입니다' from emp s, emp t where s.mgr = t.empno;

 

outer join (+)

위에는 모두 inner join 이다. 조인절을 만족 시키는 것을 선택하는것.

outer join은 조인절에서 제외 되었던 것도 출력하는 것임

 

--KING은 매니저가 없으므로 제외 되었음 하지만 사번은 가지고 있다.

 

select s.ename || '의 매니저는', t.ename || '입니다' from emp s, emp t where s.mgr = t.empno(+);--사번은 모두 가지고있음 출력하라.

 

inner join : creoss join, equi join, non equi join, self join : 조인절 만족 출력

outer join : 조인절 불만족 출력

 

3) 사원테이블과 부서테이블에서 사원이름과 부서명을 출력하시오

select ename, dname, from emp e, dept d where e.deptno=d.deptno;

 

4) 부서번호가 30인 사원들의 이름, 직급, 부서번호, 부서위치를 출력해보시오

select ename, job, emp.deptno, loc from emp,dept where emp.deptno=dept.deptno and emp.deptno=30;

 

5) 커미션을 받는 사원의 이름, 커미션, 부서명을 출력하되 커미션이 null이면 0으로 처리하시오

select ename,nvl(comm,0),dname from emp e, dept d where e.deptno=d.deptno and comm>0;

select ename,comm,dname from emp e, dept d where e.deptno=d.deptno and nvl(comm,0)>0;

 

6) Dallas에서 근무하는 사원의 이름, 직급, 부서번호, 부서명을 출력하시오

select ename, job, e.deptno, dname from emp e , dept d where e.deptno=d.deptno and LOC='DALLAS';

 

7) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하시오

select ename, dname from emp e, dept d where e.deptno=d.deptno and ename like'%A%';

 

8) 사원 이름과 직급, 급여, 급여등급을 출력해 보시오

select ename, job, sal, grade from emp e, salgrade s where e.sal>= s.losal and e.sal <= s.hisal;

 

9) 사원이름, 부서번호 해당사원과 같은 부서에서 근무하는 사원의 이름을 출력하시오(self)

               이름,부서,'동료:', 동료명

select s.ename, s.deptno, '동료:', t.ename 

from emp s , emp t 

where s.deptno = t.deptno and s.ename<>t.ename order by s.ename asc; --같은 이름 제외

 

---

서브쿼리 subquery

1. 서브쿼리란 쿼리문안의 쿼리문이다.

2. 반드시 ( )로 감싼다.

3. 서브쿼리부터 실행된다.

4. 서브쿼리의 결과는 바깥쿼리의 인자로사용된다.

5. 서브쿼리만으로도 실행이된다.

6. 대부분의 서브쿼리는 조인문으로 만들수 있다.

 

위치는 where 절, from절 -inline view, select절에 올수도있다

---

1. JONES의 부서명을 출력하세요.

select dname from dept where deptno = (select deptno from emp e where ename='JONES');

 

2. 10부서에서 근무하는 사원의 이름과 부서명을 출력하세요.

select e.ename, d.dname from emp e, (select dname, deptno from dept where deptno=10) d --d라는 임의의테이블

where e.deptno = d.deptno; --서브쿼리와 조인문을 둘다 사용

 

3. 평균급여보다 더 많이 받는 직원의 이름과 급여를 출력하시오.

select ename, sal from emp where sal> avg(sal); -- 틀린문장

select ename, sal from emp where sal> (select avg(sal) from emp);

 

4. 10번 부서에서 최대급여를 받는 사원과 동일한 급여를 받는 직원의 사번과 이름을 출력하시오

select empno, ename from emp where sal=(select max(sal) from emp where deptno=10);

 

---

서브쿼리용 다중행 연산자.

지금까지는 서브쿼리의 결과는 단행이었다. 만약에 서브쿼리의 결과가 다중행이면 다중행 연산자를 사용해야 한다. 

 IN (값,값,값,,, ) : 존재하면 참, 

> < ANY , SOME : 부분만족, 일부만족, 

> < ALL : 전체만족 

EXIST : 만족값이 하나라도 존재하면 참 : 서브쿼리의 결과가 존재하면 참

 

1. BLAKE와 같은 부서에서 근무하는 직원의 이름, 입사일자, 부서번호를 출력하시오

select ename, hiredate, deptno from emp where deptno IN (select deptno from emp where ename='BLAKE');

 

2. 급여를 3000 이상 받는 직원이 소속된 부서와 동일한 부서에서 근무하는 직원의 이름, 급여, 부서번호를 출력하시오

select ename, sal, deptno from emp where deptno IN (select deptno from emp where sal>=3000);

 

3. 부서번호가 30번인 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사람의 이름, 급여를 출력하시오 -- 전체 만족

select ename, sal from emp where sal > ALL (select sal from emp where deptno=30);

 

4.부서 번호가 30인 사원들 중에서 급여가 가장 작은 사람보다 많은 급여를 받는 사원의 이름, 급여를 출력하시오

--일부만족

select ename, sal from emp where sal > ANY (select sal from emp where deptno=30);

 

 

https://gent.tistory.com/287

참고

 

마무리연습

1. CLERK의 급여와 동일하거나 더 많이 받는 사원의 이름과 급여를 출력하시오

select ename, sal from emp where sal >= (select sal from emp where ename='CLERK'); --단일행

 

2. 직급이 CLERK인 직원의 부서번호, 지역을 출력하라.

select deptno, LOC from dept where deptno IN (select deptno from emp where job='CLERK'); --다중행

 

3. 이름에 T를 포함하는 직원과 같은 부서에서 근무하는 사원의 사번과 이름을 출력하시오. ***

select empno, ename from emp where deptno in (select deptno from emp where ename like '%T%'); --다중행 

 

4.부서의 위치가 DALLAS인 모든 사원의 이름, 부서번호를 출력하시오 ***

select ename, deptno from emp e where deptno IN (select deptno from dept where LOC='DALLAS');

 

5. SALES 부서의 모든 사원의 이름과 급여를 출력하시오.***

select ename, sal from emp where deptno IN (select deptno from dept where dname='SALES');

 

6. KING에게 보고하는 모든 사원의 이름과 급여를 출력하시오(힌트 매니저가 KING)

select ename, sal from emp where mgr IN (select empno from emp where ename='KING'); 

 

7.자신의 급여가 평균급여보다 많고 이름에 S자가 들어가는 사원과

동일한 부서에서 근무하는 모든 직원의 이름과 급여를 출력하시오 ***

select ename, sal, depno

from emp

where deptno IN

(select deptno from emp where

sal > (select avg(sal) from emp)

and ename like '%S%');

 

SET 연산자

테이블을 집합 처럼 다룸

union : 쿼리 두 결과 합침 -중복제거     select ~~~ union select ~

union all : 쿼리 두 결과 합침 -중복포함

intersect : 워키 두 결과 공통 결과를 뺌

minus : 앞쿼리 결과에서 뒷쿼리 결과는 뻄