23.09.20. SQL Developer 예제 56 ~ 66번

56. 출력되는 행 제한하기 ① ROWNUM

-- 예제 56. 사원 테이블에서 맨 위의 5개의 행만 아래와 같이 출력하세요. 
select rownum, empno, ename, job, sal
    from emp 
    where rownum <= 5 ;

-- 예제 56_ 문제 1. 직업이 SALESMAN 인 사원들의 이름과 월급과 직업을 출력하는데 맨위의 행 2개만 출력하세요.
select rownum, ename, job, sal
    from emp 
    where job = 'SALESMAN' and rownum <= 2 ;

57. 출력되는 행 제한하기 ② Simple TOP-n Queries

-- 예제 57. 월급이 높은 사원순으로 사원번호, 이름, 직업, 월급을 출력하는데 다음과 같이 맨 위의 4개의 행만 출력하세요. 
select empno, ename, job, sal
    from emp 
    order by sal desc fetch first 4 rows only ; 

-- 예제 57_문제1. 최근에 입사한 사원순으로 이름, 입사일과 월급을 출력하는데 맨위의 5명만 출력하세요.
select ename, hiredate, sal
    from emp 
    order by hiredate desc fetch first 5 rows only ;

 

58. 여러 테이블의 데이터를 조인해서 출력하기 ① EQUI JOIN

-- 예제58. 사원 테이블(emp table)과 부서 테이블(dept table)을 조인해서 이름과 부서 위치를 출력하세요. 
select *   
    from dept ;
    
select ename, loc
    from emp, dept ; -- 사원, 부서 테이블이 조회가 되어서 모두 출력이 되었다.
    
select ename, loc
    from emp, dept 
    where emp.deptno = dept.deptno ; -- 사원, 부서 테이블 에서의 공통된 컬럼명을 기재해서 출력한다.
    
select e.ename, d.loc -- 사용될 컬럼명 앞에 테이블명을 작성해주는 것이 원칙이다.
    from emp e, dept  d -- 테이블 별칭 사용해주므로, 코딩을 심플하게 작성해준다.
    where e.deptno = d.deptno ; 
    
-- 예제58_문제1. 직업 SALESMAN 인 사원들의 이름과 직업과 부서위치를 출력하세요.
select e.ename, e.job, d.loc
    from emp e, dept d
    where e.job = 'SALESMAN' and e.deptno = d.deptno ;

-- 예제58_문제2. DALLAS 에서 근무하는 사원들의 이름과 월급과 부서위치를 출력하세요.
select e.ename, e.sal, d.loc
    from emp e, dept d
    where d.loc = 'DALLAS' and e.deptno = d.deptno ;

 

59. 여러 테이블의 데이터를 조인해서 출력하기 ② NON EQUI JOIN

-- 예제59. 사원테이블과 급여테이블과 조인하여 이름과 월급과 월급에 대한 등급을 출력하세요.
drop  table  salgrade;
 
create table salgrade
( grade   number(10),
  losal   number(10),
  hisal   number(10) );
 
insert into salgrade  values(1,700,1200);
insert into salgrade  values(2,1201,1400);
insert into salgrade  values(3,1401,2000);
insert into salgrade  values(4,2001,3000);
insert into salgrade  values(5,3001,9999);
 
commit;

select * 
    from salgrade ;
    
select e.ename, e.sal, s.grade
    from emp e, salgrade s
    where e.sal between s.losal and s.hisal ;

-- 예제59_문제1. 급여등급이 4등급인 사원들의 이름과 월급을 출력하는데 월급이 높은 사원부터 출력하세요
select e.ename, e.sal, s.grade
    from emp e, salgrade s
    where s.grade = 4  and e.sal between s.losal and s.hisal 
    order by e.sal desc ;

 

60. 여러 테이블의 데이터를 조인해서 출력하기 ③ OUTER JOIN

-- 예제60. 이름과 부서위치를 출력하는데 다음과 같이 BOSTON 도 출력되게 하세요.
select * 
    from dept ;

select e.ename, d.loc
    from emp e, dept d
    where e.deptno (+) = d.deptno ; -- 데이터가 없는 쪽에 (+)을 작성한다.

-- 예제60_문제1. 사원 테이블 전체에 이름과 부서위치를 출력하는 데 JACK 도 출력되게 하세요.
insert into emp(empno, ename, sal, deptno)
    values(7122, 'JACK', 3000, 70) ;
commit;
select * 
    from emp ;

select e.ename, d.loc
    from emp e, dept d
    where e.deptno = d.deptno (+) ;

 

61. 여러 테이블의 데이터를 조인해서 출력하기 ④ SELF JOIN

-- 예제61. 직업이 SALESMAN 인 사원들의 사원이름과 직업을 출력하고 관리자 이름과 관리자 직업을 출력하세요.
select  사원.ename as 사원, 사원.job as 직업, 관리자.ename as 관리자, 관리자.job as 직업
    from emp 사원, emp 관리자 
    where 사원.mgr = 관리자.empno ;

-- 예제61_문제1. 예제61번 결과를 다시 출력하는데 관리자인 사원들보다 더 많은 월급을 받는 사원들의 데이터만 출력하세요
select  사원.ename as 사원, 사원.sal as 사원월급, 관리자.ename as 관리자, 관리자.sal as 관리자월급
    from emp 사원, emp 관리자 
    where 사원.mgr = 관리자.empno and 사원.sal > 관리자.sal ;

 

62. 여러 테이블의 데이터를 조인해서 출력하기 ⑤ ON절

-- 예제62. on절을 사용한 조인문법으로 다음의 결과을 출력하세요
select e.ename, e.sal, e.job, d.loc
    from emp e join dept d
    on ( e.deptno = d.deptno ) -- 조인조건
    where e.job = 'SALESMAN' ; -- 검색조건

-- 예제62_문제1. 월급이 1000 에서 3000 사이인 사원들의 이름과 월급과 부서위치를 on 절을 사용한 조인문법으로 출력하세요
select e.ename, e.sal, d.loc
    from emp e join dept d
    on ( e.deptno = d.deptno) -- 조인조건
    where e.sal between 1000 and 3000 ; -- 검색조건

 

63. 여러 테이블의 데이터를 조인해서 출력하기 ⑤  USING절

-- 예제63. using절을 사용한 조인문법으로 다음의 결과를 출력하세요.
select e.ename, e.job, e.sal, d.loc
    from emp e join dept d
    using ( deptno ) -- 테이블 별칭을 쓰지 말것.
    where e.job = 'SALESMAN' ;

-- 예제63_문제1. using절을 사용한 조인문법으로 부서위치가 DALLAS 인 사원들의 이름과 월급과 부서위치를 출력하세요
select e.ename, e.sal, d.loc
    from emp e join dept d
    using ( deptno ) -- 조인조건
    where d.loc = 'DALLAS' ; -- 검색조건

 

64. 여러 테이블의 데이터를 조인해서 출력하기 ⑥ NATURAL JOIN

-- 예제64. natural join 으로 다음의 결과를 출력하세요
select e.ename as 이름, e.job as 직업, e.sal as 월급, d.loc as 부서위치   
    from emp e natural join dept d 
    where e.job = 'SALESMAN' ;

-- 예제64_문제1. 직업이 SALESMAN 이고 부서번호가 30번인 사원들의 이름과 직업과 월급과 부서위치를 출력하세요.
select e.ename as 이름, e.job as 직업, e.sal as 월급, d.loc as 부서위치
    from emp e natural join dept d
    where e.job = 'SALESMAN' and deptno = 30 ; -- 공통된 컬럼일 경우 테이블 별칭을 기재하면 에러발생

 

65. 여러 테이블의 데이터를 조인해서 출력하기 ⑦ LEFTRIGHT OUTER JOIN

-- 예제65. 1999 ansi 조인문법의 outer join 으로 다음의 결과를 출력하세요
select e.ename, d.loc
    from emp e, dept d
    where e.deptno (+) = d.deptno ;
    
select e.ename, e.job, e.sal, d.loc
    from emp e right outer join dept d
    on (e.deptno = d.deptno) ;
-- 예제65_문제1. 다음의 데이터를 사원 테이블에 입력하고 1999 ansi 조인문법을 사용하여 이름과 직업, 월급과 부서위치를 출력하는데 사원 테이블에 JACK 도 출력될 수 있도록 하세요
insert into emp (empno, ename, sal, job, deptno) 
    values(8282, 'JACK', 3000, 'ANALYST', 50) ;

commit;

select * 
    from emp ;
--1999 ansi 작성법
select e.ename, e.job, e.sal, d.loc
    from emp e left outer join dept d
    on (e.deptno = d.deptno) ;
-- 오라클 작성법
select e.ename, e.job, e.sal, d.loc
    from emp e , dept d
    where e.deptno = d.deptno(+) ;

 

66. 여러 테이블의 데이터를 조인해서 출력하기 ⑧ FULL OUTER JOIN

-- 예제66. 다음과 JACK도 출력되고 부서위치도 BOSTON도 출력되게 하세요.
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 full outer join dept d
    on (e.deptno = d.deptno ) ;
    
-- 예제66_문제1. 직업이 ANALYST 이거나 부서위치가 BOSTON 인 사원들의 이름과 직업과 월급과 부서위치를 출력하는데 full outer 조인을 사용하여 출력하세요.
select e.ename, e.job, d.loc
    from emp e full outer join dept d
    on ( e.deptno = d.deptno )
    where e.job = 'ANALYST' or d.loc = 'BOSTON' ;

   

조인문법 
        1) 오라클 조인 문법 
            1. equi join
            2. non equi jon
            3. outer join
            4. self join
        2) 1999 ansi 인문법(미국 국립 표준 협회)
            1. on절 join

            2.  using join

            3. left/right outer join 

            4. full outer join