151007 - SubQuery / Join - 과제

*subquery

1. blake 와 같은 부서에 있는 모든 직원의 사번, 이름, 입사일자 조회

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

  DEPTNO      EMPNO ENAME      HIREDATE

-------- ---------- ---------- --------

      30       7499 ALLEN      81/02/20

      30       7521 WARD       81/02/22

      30       7654 MARTIN     81/09/28

      30       7698 BLAKE      81/05/01

      30       7844 TURNER     81/09/08

      30       7900 JAMES      81/12/03

  

2. select empno, ename, deptno, sal, comm from emp

where (sal, nvl(comm,0)) in (select sal, nvl(comm,0) from emp where deptno=30);

쿼리를 수정하여 보너스가 null 인 직원들도 출력될 수 있도록 하시오.

  EMPNO ENAME          DEPTNO        SAL       COMM

------- ---------- ---------- ---------- ----------

   7499 ALLEN              30       1600        300

   7521 WARD               30       1250        500

   7654 MARTIN             30       1250       1400

   7698 BLAKE              30       2850

   7844 TURNER             30       1500          0

   7900 JAMES              30        950

3. 평균 급여 이상을 받는 직원들의 사번, 이름을 조회. 단, 급여가 많은 순으로 정렬

select empno, ename, sal from emp where 

sal >=(select avg(sal) from emp) order by sal desc;

  EMPNO ENAME             SAL

------- ---------- ----------

   7839 KING             5000

   7902 FORD             3000

   7788 SCOTT            3000

   7566 JONES            2975

   7698 BLAKE            2850

   7782 CLARK            2450

select empno, ename, avg(sal), sal from emp having

sal >=(select min(sal) from emp) group by avg(sal);

4. 이름에 T 자가 들어가는 직원이 근무하는 부서에서 근무하는 직원의 사번, 이름 급여 조회

select empno, ename, sal, deptno from emp where deptno in( select deptno from emp where ename like '%T%');

   EMPNO ENAME             SAL     DEPTNO

-------- ---------- ---------- ----------

    7902 FORD             3000         20

    7876 ADAMS            1100         20

    7788 SCOTT            3000         20

    7566 JONES            2975         20

    7369 SMITH             800         20

    7900 JAMES             950         30

    7844 TURNER           1500         30

    7698 BLAKE            2850         30

    7654 MARTIN           1250         30

    7521 WARD             1250         30

    7499 ALLEN            1600         30

5. 부서의 위치가 dallas 인 모든 직원에 대해 사번, 이름, 급여, 업무 조회

select empno, ename, sal, job from emp where deptno in( select deptno from dept where loc like 'DALLAS');

    EMPNO ENAME             SAL JOB

--------- ---------- ---------- --------

     7369 SMITH             800 CLERK

     7566 JONES            2975 MANAGER

     7788 SCOTT            3000 ANALYST

     7876 ADAMS            1100 CLERK

     7902 FORD             3000 ANALYST

 

 

6. King 에게 보고하는 모든 직원의 이름과 부서, 업무, 급여를 조회

select ename, deptno, job, sal from emp where mgr in(select empno from emp where ename ='KING');

ENAME          DEPTNO JOB              SAL

---------- ---------- --------- ----------

JONES              20 MANAGER         2975

BLAKE              30 MANAGER         2850

CLARK              10 MANAGER         2450

7. 월급이 30번 부서의 최저급여보다 높은 직원의 사번, 이름, 급여를 조회

select empno, ename, sal from emp 

where sal>(select min(sal) from emp where deptno='30');

     EMPNO ENAME             SAL

---------- ---------- ----------

      7499 ALLEN            1600

      7521 WARD             1250

      7566 JONES            2975

      7654 MARTIN           1250

      7698 BLAKE            2850

      7782 CLARK            2450

      7788 SCOTT            3000

      7839 KING             5000

      7844 TURNER           1500

      7876 ADAMS            1100

      7902 FORD             3000

     EMPNO ENAME             SAL

---------- ---------- ----------

      7934 MILLER           1300

  

  

8. 10번부서에서 30번 부서의 직원과 같은 업무를 하는 직원의 이름과 업무 조회

select ename, job from emp where deptno ='10'and job in(select job from emp where deptno='30'); 

ENAME      JOB

---------- ---------

CLARK      MANAGER

MILLER     CLERK

***JOIN

9. Newyork 에서 근무하는 직원의 사번, 이름, 업무, 부서명 을 조회

select empno, ename, job, dname, loc from emp inner join dept on emp.deptno = dept.deptno and dept.loc = 'NEW YORK';

 EMPNO ENAME      JOB       DNAME          LOC

------ ---------- --------- -------------- ----------

  7782 CLARK      MANAGER   ACCOUNTING     NEW YORK

  7839 KING       PRESIDENT ACCOUNTING     NEW YORK

  7934 MILLER     CLERK     ACCOUNTING     NEW YORK

  

  

10. 커미션을 받는 직원에 대해 이름, 부서명, 근무지를 조회

select ename, dname, loc, comm from emp 

inner join dept on emp.deptno = dept.deptno 

and emp.comm is not null;

ENAME      DNAME          LOC                 COMM

---------- -------------- ------------- ----------

TURNER     SALES          CHICAGO                0

MARTIN     SALES          CHICAGO             1400

WARD       SALES          CHICAGO              500

ALLEN      SALES          CHICAGO              300

11. 이름 중간에 L 자가 있는 직원의 이름, 업무, 부서명, 근무지 조회

select ename, job, dname, loc from emp inner join dept on emp.deptno = dept.deptno  where ename like '%L%';

ENAME      JOB       DNAME          LOC

---------- --------- -------------- --------

MILLER     CLERK     ACCOUNTING     NEW YORK

CLARK      MANAGER   ACCOUNTING     NEW YORK

BLAKE      MANAGER   SALES          CHICAGO

ALLEN      SALESMAN  SALES          CHICAGO

12. 각 직원들에 대해 그들의 관리자보다 먼저 입사한 직원의 이름, 입사일, 관리자이름, 관리자입사일 을 조회

select e.ename, e.hiredate, m.ename, m.hiredate from emp e, emp m

 where e.mgr = m.empno and e.hiredate<m.hiredate;

 ENAME      HIREDATE ENAME      HIREDATE

---------- -------- ---------- --------

WARD       81/02/22 BLAKE      81/05/01

ALLEN      81/02/20 BLAKE      81/05/01

CLARK      81/06/09 KING       81/11/17

BLAKE      81/05/01 KING       81/11/17

JONES      81/04/02 KING       81/11/17

SMITH      80/12/17 FORD       81/12/03

13. 말단사원의 사번, 이름, 업무, 부서번호, 근무지를 조회

select empno, ename, job, emp.deptno, loc 

from emp right outer join dept on emp.deptno= dept.deptno 

where empno not in( select mgr from emp where mgr is not null);

select empno, ename, job, emp.deptno, loc 

from emp right outer join dept on emp.deptno= dept.deptno 

where empno not in(select nvl(mgr,-1) from emp);

Create table tblbook(

author varchar2(20),

title varchar2(20)

);

insert into tblbook values('최주현', '하늘과 땅');

insert into tblbook values('최주현', '바다');

insert into tblbook values('유은정', '바다');

insert into tblbook values('박성우', '문');

insert into tblbook values('최주현', '문');

insert into tblbook values('박성우', '천국');

insert into tblbook values('최주현', '천국');

insert into tblbook values('최지은', '천국');

insert into tblbook values('박성우', '고슴도치');

insert into tblbook values('서금동', '나');

하나의 잡에 두명의 임프넘버 

14. 한권의 책에 대해 두명 이상의 작가가 쓴 책을 검색 하시오

책이름 작가명 작가명 

바다 문 천국

select title, author from tblbook

UNION all

select title, author from tblbook;

select a.title, a.author, b.author from tblbook a, tblbook b where a.title = b.title and a.author < b.author;

TITLE                AUTHOR               AUTHOR

-------------------- -------------------- -------

바다                 최주현               유은정

문                   최주현               박성우

천국                 최지은               박성우

천국                 최주현               박성우

천국                 최지은               최주현

15. 한권의 책에 대해 세명의 작가가 쓴 책을 검색하시오

책이름 작가명 작가명 

천국

select a.title, a.author, b.author, c.author from tblbook a, tblbook b, tblbook c where a.title = b.title and b.title = c.title and a.author < b.author and b.author < c.author;

TITLE                AUTHOR               AUTHOR

-------------------- -------------------- --------------------

AUTHOR

--------------------

천국                 박성우               최주현

최지은

151007 - Join, Transaction, 조인, 트랜잭션

join

1. 여러 개의 테이블을 병합하여 하나의 결과를 도출하기 위한 방법

2. 종류

         (1) Cartesian  product join

               -데카르트 곱 조인

         (2) Equi join

                  1) 공통 필드의 레코드를 가져오는 방법(중복)

                  2) INNER JOIN(Natural join) : 중복제외

         (3) Quter join

                     1) Inner join의 확장

                                 - Inner JOIN + 공통되지 않은 레코드도 자져옴

                     2) 종류

                                 - Left Outer join

                                 - Right Outer join

                                 - Full Outer join

         (4) Non Equi JOIN

                  -공통된 필드가 없을 경우 

  

================================

테이블 생성

create TABLE tblA (

id  number,

name number);   

create TABLE tblB(

id number,

name number

);   

create TABLE tblC(

id  number,

name number

);         

데이터 입력

insert into tblA values(1, 10);

insert into tblA values(2, 20);      

insert into tblA values(3, 30);      

insert into tblA values(5, 50);      

insert into tblA values(7, 70);

insert into tblB values(1, 10);      

insert into tblB values(2, 20);      

insert into tblB values(4, 40);

insert into tblB values(5, 50);

insert into tblB values(8, 80);

      

insert into tblC values(1, 10);      

insert into tblC values(2, 20);

insert into tblC values(7, 70);

insert into tblC values(8, 80);      

insert into tblC values(9, 90);      

------------------------------------------

예제

select tblA.id, tblA.name from tblA INNER JOIN tblB ON tblA.id = tblB.id;

select tblA.id, tblA.value from tblA JOIN tblB ON tblA.id = tblB.id;

select a.id, a.value from tblA a INNER JOIN tblB b ON a.id = b.id;

select a.id, a.value from tblA a, tblB b where a.id = b.id;

=========================================================================

***직원의 사번, 이름, 업무, 부서번호, 부서명 을 조회

select empno, ename, job, emp.deptno, dname from emp 

inner join dept on emp.deptno = dept.deptno;

select empno, ename, job, emp.deptno, dname from emp, dept

where emp.deptno = dept.deptno;

==============================================================

***SALESMAN 에 대해서 사번, 이름, 업무, 부서명 을 조회

select empno, ename, job, dname from emp 

inner join dept on emp.deptno = dept.deptno 

and job='SALESMAN';

select empno, ename, job, dname from emp 

inner join dept on emp.deptno = dept.deptno 

where job = 'SALESMAN';

=============================================================

4. Outer Join

select tblA.id, tblA.value, tblB.value

from tblA LEFT OUTER JOIN 

tblB on tblA.id = tblB.id;

select tblA.id, tblA.value, tblB.value 

from tblA RIGHT OUTER JOIN 

tblB on tblA.id = tblB.id;

select tblA.id, tblA.value, tblB.value 

from tblA FULL OUTER JOIN 

tblB on tblA.id = tblB.id;

select tblA.id, tblA.value, tblB.value 

from tblA, tblB where tblA.id = tblB.id(+);

select tblA.id, tblA.value, tblB.value 

from tblA, tblB where tblA.id(+) = tblB.id;

select tblA.id, tblA.value, tblB.value 

from tblA, tblB where tblA.id(+) = tblB.id(+);

=====================================================

*** 이름, 급여, 부서명, 근무지 를 조회하시오.

단, 부서명과 근무지는 모두 출력할 수 있도록 하시오. 

select ename, sal, dname, loc from emp

e right outer join dept d

on e.deptno = d.deptno;

=======================================================

5. 3개의 테이블 조인

select a.id, a.value, b.value, c.value from tblA a inner join tblb

on a.id = b.id inner join tblc c on b.id=c.id;

select a.id, a.value, b.value, c.value 

from tblA a,tblb b, tblc c 

where a.id = b.id and b.id = c.id;

6.Non Equi Join

직원의 사번, 이름, 급여, 급여등급 을 조회

select empno, ename, sal, grade

from emp inner join salgrade

on sal >= losal and sal <= hisal;

7. Self Join

직원의 사번, 이름, 업무, 직속상사 사번, 직속상사 이름 을 조회

select e.empno, e.ename, e.job, e.mgr, m.ename

from emp e inner join emp m

on e.mgr = m.empno;

8. SET 연산자

1) UNION 합집합 - 중복은안가져옴

2) UNION ALL 합집합 - 중복도가져옴

3) INTERSECT 

4) MINUS 차집합

select deptno from dept 

UNION

select deptno from emp;

select deptno from dept 

UNION ALL

select deptno from emp;

select deptno from dept 

INTERSECT

select deptno from emp;

select deptno from dept 

MINUS

select deptno from emp;

============================================

Transaction

--------------

ALL or Nothilg!

데이터베이스 파일 

-.dbf : 실제 데이터파일

-.log : Transaction Log(dml)

명령어

-Commit - 완전저장

-Rollback - 되돌림

151007 - SubQuery 서브쿼리

***subQuery

1. 다른 query문에 포함된 query문에 포함된 query

2. 반드시 ()를 사용

3. 연산자의 오른쪽에 와야한다.

4. order by 사용금지

5. 종류

      (1) General Subquery

      (2) Relation Subquery

      

6. 유형

      (1) 단일행

      (2) 다중행

      (3) 다중열

            

7. 연산자 

      (1) 단일열

         =, >, <, >=, <=, <>....

      (2) 다중행

         in, any, all, exists, not

         

안에 있는 서브쿼리가 먼저 실행 밖에 있는 쿼리가 나중에 실행된다,

쿼리를 구별할 수 있는 방법 서브쿼리만 실행될 때 Relation Subquery

다중열

위치와 순서 같아야한다.

*MILLER의 데이터 수정

select sal, comm from emp where ename='MILLER';

update emp set sal=1500, comm=300 where ename='MILLER';

=====================================================================================

실습

1. SCOTT 의 급여보다 더 많이 받는 직원의 이름, 업무, 급여 를 조회

SELECT sal FROM emp WHERE ename = 'SCOTT' ; --3000

SELECT ename, job, sal FROM emp WHERE sal>3000;

SELECT ename, job sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SCOTT');

2. 사번이 7521의 업무와 같고, 급여가 7934 보다 많은 직원의 사번, 이름, 업무, 급여를 조회

SELECT job FROM emp WHERE empno=7521;  //SALESMAN

SELECT sal FROM emp WHERE empno=7934;  //1300

select empno, ename, job, sal, from emp where job='SALESMAN' and sal>1300;

select empno, ename, job, sal, from emp where job=(select job from emp where empno=7521;) and sal>(select sal from emp where empno=7934);

3. 업무별로 최소급여를 받는 직원의 사번, 이름, 급여, 부서번호 조회

select empno, ename, sal, deptno from emp where sal in(select min(sal) from emp group by job)

select empno, ename, sal, deptno from emp where sal=800 OR sal=1250 OR sal=5000 OR sal=2450 OR sal=3000;

select min(sal) from emp group by job;

select empno, ename, sal, deptno from emp where sal in(select min(sal) from emp group by job);

4. 업무별로 최소급여보다 많은 급여를 받는 직원의 사번, 이름, 업무, 부서번호를 조회

select empno, ename, job,  sal, deptno from emp where sal > any(select min(sal) from emp group by job);

select min(sal) from emp group by job;

5. 업무별로 최대급여를 받는 직원의 사번, 이름, 업무, 부서번호를 조회

select max(sal) from emp group by job;

select empno, ename, deptno from emp where sal >= all(select max(sal) from emp group by job);

6.급여와 보너스가 30번 부서에 있는 직원의 급여와 보너스가 같은 직원에 대해 사번, 이름,., 부서번호, 급여, 보너스 조회

select empno, ename, deptno, sal, comm from emp where (sal, comm) in(select  sal, comm from emp where deptno=30);

select  sal, from emp where deptno=30 ;

select comm from emp where deptno=30;

7. 상관 서브 쿼리

*적어도 한명의 직원으로부터 보고를 받을 수 있는 직원의 이름, 업무, 입사일자, 급여를 조회

select ename, job, hiredate, sal from emp where empno in(select distinct mgr from emp) order by ename;

select ename, job, hiredate, sal from emp e where empno exists(select distinct mgr from emp where e.empno=mgr) order by ename;

select distinct mgr from emp;

151006 DB 함수 연습과제

1 이름의 첫 글자가 k 보다 크고 y 보다 작은 직원의 이름, 부서, 업무를 조회 / 단 이름순으로 정렬

SELECT ename, deptno, job from emp where ename >'K%' and ename<'Y%' order by ename;

2. 오늘부터 12월 25일 까지 몇일 남았는가

select sysdate - To_Date('2015/12/25') from dual;

3. 모든 직원이 현재까지 근무한 근무 일수를 몇주 몇일로 출력하시오. / 단 근무일수가 많은 사람순으로 조회

select ename, hiredate, trunc((sysdate-hiredate)/7)weeks, round (mod((sysdate-hiredate),7),0) days from emp order by sysdate - hiredate desc;

4. 10번 부서 직원들에 한해서 현재까지의 근무개월수를 조회하시오.

select ename, hiredate, sysdate,trunc(months_between(sysdate,hiredate),0)  from emp where deptno =10 order by months_between(sysdate,hiredate)desc;

5. 20번 부서 직원들에 한해서 입사일자로 부터 5개월이 지난 후의 날짜를 조회

select ename, deptno, add_months(hiredate,5) from emp;

6. 모든 직원에 대해 입사한 달의 근무일수를 조회

select empno, ename, hiredate, last_day(hiredate)-hiredate from emp order by last_day(hiredate)-hiredate desc;

7. 현재 급여에 15%가 증가된 급여를 계산하여 사번, 이름, 업무, 급여, 증가된급여 를 조회

select empno, ename, job, sal, sal*1.15 from emp;

8.이름, 입사일, 입사일로부터 현재까지의 근무개월수, 급여, 급여총계를 조회

select ename, hiredate, sysdate,trunc(months_between(sysdate,hiredate)) TMonths, sal, sal*trunc(months_between(sysdate,hiredate)) Tsal from emp ;

9, 업무가 analyst 면 급여를 10% 증가시키고, clerk 면 15% 증가, manager 면 20% 증가시켜서 이름, 업무, 급여, 증가된 급여를 조회

select ename, job, sal, decode( job , 'ANALYST' , sal*1.10, 'CLERK',sal*1.15,'MANAGER',sal*1.20 ) 증가된급여 from emp;

10. 부서별로 급여평군, 최고급여를 조회하는데, 단 급여평균이 높은 순으로 조회하고 급여 평균이 2000이상인 부서만 조회

select deptno, avg(sal), max(sal) from emp group by deptno having avg(sal)>=2000 order by avg(sal) desc; 

11. 같은 업무 내에서 부서별 평균급여, 최고급여, 인원수를 조회

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

12. 인원수, 보너스에 null 이 아닌 인원수, 보너스의 평균 ( 보너스가 null 이 아닌 평균, 널을 포함한 평균)

등록되어있는 부서의 수 ( 중복제외 ) 를 구하여 조회

select count(distinct(deptno)) 부서수, count(*) 인원수, count(comm) 보너스받는놈수, avg(comm) 받는놈평균보너스, avg(nvl(comm,0)) 안받는놈포함평균보너스  from emp;

13. 부서인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 조회

select deptno, count(empno), sum(sal) from emp having count(empno)>4 group by deptno ;

14. 급여가 최대 2000 이상인 부서에 대해 부서번호, 평균급여, 급여의 합 을 조회

select deptno, avg(sal), sum(sal), max(sal)  from emp having max(sal)>2000 group by deptno;

15. 최고급여와 최소급여의 차이는 얼마인가

select max(sal)-min(sal)&최소급여 from emp ;

select max(sal)-min(sal)급여차이 from emp ;

16 예시)

년도 count min max avg sum

-----------------------------------------------

80 1 800 800 800 800

81 10 950 5000 2282.5 22825

82 2 1300 3000 2150 4300

83 1 1100 1100 1100 1100

select to_char(hiredate,'YY')년도,count(empno)count, min(sal)min, max(sal)max, avg(sal)avg, sum(sal)sum from emp group by to_char(hiredate,'YY') order by to_char(hiredate,'YY');

17 예시)

total 1980 1981 1982 1983

----------------------------------------

14 1 10 2 1

select count(empno)Total, count(decode(to_char(hiredate,'YYYY'),1980,1980)) "1980", count(decode(to_char(hiredate,'YYYY'),1981,1981)) "1981" , count(decode(to_char(hiredate,'YYYY'),1982,1982)) "1982", count(decode(to_char(hiredate,'YYYY'),1983,1983)) "1983" from emp;  

18 예시)

업무  10 20 30 total

analyst 6000 6000

clerk 1300 1900 950 4150

manager 2450 2975 2850 8275

president 5000 5000

saalesman 5600 5600

select job, sum(decode(deptno,10,sal))"10",sum(decode(deptno,20,sal))"20",sum(decode(deptno,30,sal))"30",sum(sal)Total from emp group by job order by job;