디비1 - 4월27일

중간고사 시험문제를 카페에 올렸으니 다운을 받아서 괴롭겠지만

답을 달아서 몇개 틀렸는지 확인해보고 요번주 토요일까지 메일을 보내면 10점 가산점

90점이면 100점이 되는거고 !

*조인의 종류

1. 오라클 조인문법

-equi join

조인하려는 테이블 사이의 연결고리가 = 조건인 경우

-non equi join

조인하려는 테이블 사이의 연결고리 =이 아닌 경우

-outer join

eui join으로 조인 안된 결과를 볼때 사용하는 조인

-self join

자기 자신의 테이블과 조인하는 조인

문제94.부서번호가 30번인 사원들의 이름,부서위치,월급,부서번호를 출력하시오.

그냥 스킵~~

문제95.이름,월급,grade(급여등급)을 출력하시오.

*non equi join

select e.ename,e.sal,s.grade

from emp e , salgrade s 

where e.sal between s.losal and s.hisal;

emp 테이블의 월급은 salgrade losal hisal 사이에 있다.이걸 영작하면 된다.

문제96.위의 결과에서 급여등급이 1등급인 사원만 출력하시오.

select e.ename,e.sal,s.grade

from emp e , salgrade s 

where e.sal between s.losal and s.hisal and s.grade = 1 ;

문제97.이름,부서위치를 출력하는데 부서 테이블에서 안나온 부서위치가 무엇인지 

확인하시오.

select e.ename,d.loc

from emp e , dept d

where e.deptno=d.deptno ;

보스턴이네...?

문제98.왜 boston이 안나왔을까?

일하는 사람이 없는 것. 그러면 emp 테이블에 누가 없어? 40번 부서번호가..

문제99.어느 부서위치에 사원이 배치되지 않았는지 아래 처럼 확인하고자 한다면?

보스턴까지 뜨게 해서 사원의 이름이 없게 나오게 하고 싶은데..뭐 어떻게 하냐면

답은 outer join이다.

select e.ename,d.loc

from emp e ,dept d

where e.deptno (+) = d.deptno ;     

※ (+) : outer join sign (이것은 모자란쪽에다가 붙여주면 된다.이 경우 이름이 모자르니까 왼쪽에 붙여주는 것이다.)

(        insert into emp(empno,ename,deptno) values(2934,'JACK',70) ; 넣어      )

(문제 100번 문제는 아우터 조인을 오른쪽 변에 쓰는 문제.)

문제101.부서위치,부서위치별 토탈월급을 출력하시오.

select d.loc,sum(e.sal)

from emp e , dept d

where e.deptno = d.deptno

group by d.loc;

문제 102.그런데 아래와 같이 다시 출력하시오.

select d.loc,nvl(sum(e.sal),0)

from emp e , dept d

where e.deptno (+) = d.deptno

group by d.loc;

문제103. 이렇게 나올려면

select e.ename , d.loc

from emp e , dept d

where e.deptno (+) = d.deptno(+) 이렇게 안되... 그래서 밑에...

1999 assi 문법 (American national Standard Institute)

이게 오라클에서 지원안했던 문법인데, 늦게서야 이걸 지원.

mysql,sql 이런데서 지원하는 문법 같은..

- full outer join 문법

select e.ename,d.loc

from emp e full outer join dept d on (e.deptno = d.deptno ); 

문제104. 부서위치,부서위치별 인원수를 출력하는데 부서위치별 인원수가 3명 이상인 것만 출력하시오.

select d.loc, count(*)

from emp e , dept d 

where e.deptno = d.deptno

group by d.loc

having count(*) >=3;

문제 105.급여등급(grade) , 급여등급별 인원수를 출력하시오.

select s.grade,count(*)

from emp e , salgrade s

where e.sal between s.losal and s.hisal

group by s.grade;

문제 106.사원번호,이름,mgr를 출력하시오.

mgr -> 자기의 관리자,자기의 직속상사의 사원번호

                                    KING (MGR이 없음, 예가 보스)

                          BLAKE   7839

                JONES   7698

        FORD   7566

SMITH  7902

문제107. 사원이름 ,자기의 직속상사(자기의 관리자의 이름을 출력하시오.)

select 사원.name, 관리자.ename

from emp 사원, emp 관리자

where 사원.mgr = 관리자.empno ;

 

사원 관리자를 ==> e , m 으로 해두됨

select e.ename, m.ename

from emp e, emp m

where e.mgr = m.empno ;

문제 108. 위의 결과에서 king이 출력되게 하시오.

가만보면, 원래 14개에서 cmd 결과가 13개 rows . 즉 king이 빠진거.

select e.ename, m.ename

from emp e, emp m

where e.mgr = m.empno  (+) ;

1,2,3,4, 조인 그거 하고 1999 asni 문법  기억해라~~~~

---

2개의 테이블을 조인하려면 연결고리가 한개는 있어야 한다.

emp  --------------------------------- dept

                       연결고리

3개의 테이블을 조인하려면 연결고리가 몇개가 있어야할까?

 dept --------------------emp ------------------- salgrade

            연결고리                         연결고리

n개의 테이블을 조인하려면 n-1개가 필요.

문제 109.사원이름,월급,부서위치,급여등급(grade)을 출력하시오.

select e.ename , e.sal , d.loc , s.grade

from emp e , dept d , salgrade s

where e.deptno = d.deptno and e.sal between s.losal and s.hisal ; 

문제 110.

급여등급이 3등급인 사원들의 부서위치,부서위치별 최대월급을 출력하는데 부서위치별 

최대월급이 1000이상인것만 출력하시오.

select d.loc , sum(e.sal)

from emp e , dept  d, salgrade s 

where e.deptno = d.deptno and e.sal between s.losal and s.hisal and s.grade = 3

group by d.loc

having sum(e.sal) >= 1000 ;