sql중급 / 1999 ANSI join

ㅁ sql의 핵심

1. 조인

2. 서브쿼리

 

ㅁ조인문법의 종류 

1. 오라클 조인문법

 - equi join

 - non equi join

 - outer join

 - self join

2. 1999 ANSI(American National Standard Institute) 표준문법

 - on절을 사용한 조인

 - using절을 사용한 조인

 - natural join

 - left/right/full outer join

 - cross join

 

ㅁ복습

문제303. 직업이 SALESMAN인 사원들의 이름, 직업, 부서위치, 부서명(dname)을 출력하시오

select e.ename, e.job, d.loc, d.dname
 from emp e, dept d
 where e.deptno = d.deptno and e.job = 'SALESMAN';
 
-- 오라클 조인문법의 equi join

문제304. 급여등급이 3등급인 사원들의 이름, 월급, 급여등급을 출력하시오

select e.ename, e.sal, s.grade
 from emp e, salgrade s
 where e.sal between s.losal and s.hisal and
      s.grade = 3;
      
-- 오라클 조인문법 no equi join

문제305. KING이 근무하는 부서위치가 어디인가?

select d.loc
 from emp e, dept d
 where e.deptno = d.deptno and
    e.ename = 'KING';

문제306. 이름, 부서위치를 출력하는데 emp테이블에는 있는 부서번호인데 dept테이블에는 없는 부서번호에 대한 정보도 출력될 수 있게 조인문법을 작성하시오

select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno (+);
 
-- 오라클 조인문법 outer join

 

 

062 여러 테이블의 데이터를 조인해서 출력하기 5(ON절)

from절에 콤마(,) 대신 join을 입력, where 절이 아닌 on절 사용해서 조인조건 입력. where 절은 검색조건을 줄 때만 사용

-- ON절을 사용한 조인문법
select e.ename, e.job, e.sal, d.loc
 from emp e join dept d
 on ( e.deptno = d.deptno);

문제307. 직업이 SALESMAN인 사원들의 이름, 직업, 월급, 부서위치를 출력하는데 on절을 사용한 조인문법으로 작성하시오

select e.ename, e.job, e.sal, d.loc
 from emp e join dept d
 on (e.deptno = d.deptno)
 where e.job = 'SALESMAN';

문제308. 오라클 조인문법으로 emp, dept, sal테이블을 조인해서 이름, 월급, 부서위치, 급여등급을 출력하시오

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;

문제309. 위의 결과를 on절을 사용한 조인문법으로 작성하시오

select e.ename, e.sal, d.loc, s.grade
 from emp e join dept d 
            on (e.deptno = d.deptno)
            join salgrade s
            on (e.sal between s.losal and s.hisal);

 

문제310. 위의 결과에서 월급이 2500이상인 사원들만 출력하시오

select e.ename, e.sal, d.loc, s.grade
 from emp e join dept d 
            on (e.deptno = d.deptno)
            join salgrade s
            on (e.sal between s.losal and s.hisal)
 where e.sal >= 2500;

문제311. on절을 사용한 조인문법으로 부서위치, 부서위치별 토탈월급을 출력하시오

select d.loc, sum(e.sal)
 from emp e join dept d
 on (e.deptno = d.deptno)
 group by d.loc;

문제312. 위의 결과에서 맨아래에 전체 토탈월급이 출력되게 하시오

select d.loc, sum(e.sal)
 from emp e join dept d
 on (e.deptno = d.deptno)
 group by rollup(d.loc);

문제313. DALLAS에서 근무하는 사원들의 이름, 부서위치를 출력하시오

select e.ename, d.loc
 from emp e join dept d
 on (e.deptno = d.deptno)
 where d.loc = 'DALLAS';

063 여러 테이블의 데이터를 조인해서 출력하기 5(USING절)

using 절에 조인하려는 테이블의 공통된 컬럼을 테이블 별칭없이 기입하면 됨(테이블 별칭을 사용할 경우 에러나서 수행되지 않음)

-- using절을 사용한 조인문법
select e.ename, e.job, e.sal, d.loc
 from emp e join dept d
 using ( deptno );

문제314. emp14와 telecom_service 테이블을 조인해서 이름, 통신사, 통신비용을 출력하는데 이름이 김씨인 학생들만 출력하시오(using절을 사용한 조인)

select e.ename, telecom, t.price
 from emp14 e join telecom_service t
 using ( telecom )
 where e.ename like '김%';
 
 -- 연결고리가 되는 using에 사용한 컬럼은 select절에서도 테이블 별칭을 사용하면 안됨

 

 

064 여러 테이블의 데이터를 조인해서 출력하기 6(NATURAL JOIN)

-- natural join
select e.ename, e.job, e.sal, d.loc
 from emp e natural join dept d ;
 
 -- 오라클이 deptno가 연결고리가 되는 컬럼이라는 것을 스스로 알아내서 조인을 해줌.
 -- 단, 동일한 컬럼을 가지고 있어야만 함

 

065 여러 테이블의 데이터를 조인해서 출력하기 7(LEFT/RIGHT OUTER JOIN)

-- 1999ansi 조인문법으로 outer join 결과 출력하기

-- 먼저, 오라클 outer join 문법
select e.ename, e.job, e.sal, d.loc
 from emp e, dept d
 where e.deptno(+) = d.deptno ;
 
-- 1999 ansi join 문법
select e.ename, e.job, e.sal, d.loc
 from emp e right outer join dept d
 on (e.deptno = d.deptno );

문제315. 아래의 오라클 조인문법을 1999 ansi문법으로 수행하시오

select e.ename, e.job, e.sal, d.loc

 from emp e, dept d

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

select e.ename, e.job, e.sal, d.loc
 from emp e left outer join dept d
 on (e.deptno = d.deptno );

문제316. 아래의 오라클 조인문법을 1999 ansi문법으로 구현하시오

select d.loc, sum(e.sal)

 from emp e, dept d

 where e.deptno (+) = d.deptno

 group by d.loc;

select d.loc, sum(e.sal)
 from emp e right outer join dept d
 on ( e.deptno = d.deptno )
 group by d.loc;

066 여러 테이블의 데이터를 조인해서 출력하기 8(FULL OUTER JOIN)

오라클 조인문법으로는 구현이 안되는데 1999 ansi 문법으로 구현되는 유일한 조인문법이 바로 full outer join 

-- JACK도 출력되고 부서위치의 BOSTON도 출력되게 하시오
select e.ename, e.job, e.sal, d.loc
 from emp e full outer join dept d
 on ( e.deptno = d.deptno );
 
 -- 오라클 조인문법으로는 구현안되는 조인문법 = full outer join

 

 

ㅁ우리나라의 직업만족도는 임금근로자, 고용주, 자영업자 중에 어느 직종의 만족도가 더 높은지 순위를 출력하시오 !

1. 데이터 구하기 > 2. 테이블 생성 > 3. SQL작성

문제317. 2009~2020년도의 데이터에서 직업에 대한 만족도가 임금근로자, 고용주, 자영업자 중에서 어느 직종의 만족도가 더 높은지 순위를 출력하시오

select feature2, 만족도, rank() over (order by 만족도 desc) 순위 
  from ( 
         select feature2,extr_satifi + few_satifi as 만족도
          from satisfy_table
          where feature1 = '종사상지위' );

문제318. 남자의 경우 나이가 들수록 주관적 만족도가 높아지는지 satisfy_table의 연령*성별 데이터를 가지고 나이별로 순위를 출력하시오

select feature2, 만족도, rank() over (order by 만족도 desc) 순위
 from (
        select feature2, extr_satifi + few_satifi as 만족도
         from satisfy_table
         where feature2 like '%*남자' );
         
select feature2, extr_satifi + few_satifi as 만족도, rank() over (order by extr_satifi + few_satifi desc) as 순위
         from satisfy_table
         where feature2 like '%*남자' ;

 

 

ㅁ 1999 ansi 조인의 cross join

1. 오라클 조인문법으로 설명하자면,

select e.ename, d.loc

 from emp e, dept d; 

 > 조인 조건 없이 조인하는 것

2. 1999 ansi 문법으로 설명하자면

select e.ename, d.loc

 from emp e cross join dept d;