[Oracle]SQL 기본 예제 - DQL(SELECT, FROM, WHERE)

[Oracle]SQL 기본 예제 - DQL(SELECT, FROM, WHERE)

기본 계정인 scott/tiger로 접속하면 설정되어있는 테이블들로 실습했습니다.

scott계정의 테이블들을 살펴보시고 연습해봐요~~

[오라클 ACE가 해설하는 SQL&PL/SQL에 있는 예제입니다]

1. 조인(join) 예제

FROM emp e, dept d 에서 실제 조회할 테이블이름은 emp와 dept이고

옆에 있는 e, d는 사용편의를 위해 지정한 이름입니다.

SELECT문을 통해 emp테이블의 deptno, ename, sal을 불러오고 

dept테이블에서는 dname을 불러오고 있네요 ㅎ

WHERE을 보시면 emp테이블의 deptno와 dept테이블의 deptno가 같을 경우라는 조건을 주었죠 (JOIN)

결과적으로 사원의 부서번호 부서이름 사원이름 급여를 출력했습니다.

SQL> SELECT e.deptno, d.dname, e.ename, e.sal    2  FROM emp e, dept d   3  WHERE e.deptno = d.deptno   4  ;     DEPTNO DNAME          ENAME             SAL     ---------- -------------- ---------- ----------                                                      20 RESEARCH       SMITH             800                          30 SALES          ALLEN            1600            30 SALES          WARD             1250             20 RESEARCH       JONES            2975          30 SALES          MARTIN           1250            30 SALES          BLAKE            2850            10 ACCOUNTING     CLARK            2450           20 RESEARCH       SCOTT            3000           10 ACCOUNTING     KING             5000          30 SALES          TURNER           150         20 RESEARCH       ADAMS            1100                30 SALES          JAMES             950             20 RESEARCH       FORD             3000           10 ACCOUNTING     MILLER           1300                                                                                                                                                          14 개의 행이 선택되었습니다.

2. 서브쿼리(Sub query) 예제

이번에는 emp테이블에서 이름이 ALLEN인 사람의 부서 이름을 출력하는 쿼리입니다.

WHERE절 오른쪽부분에 '(' 를하고 다시 SELECT를 하고 있죠. 이것을 서브쿼리라고 합니다.

서브쿼리가 먼저 실행되고 메인 부분이 실행됩니다.

서브쿼리부분에서 emp테이블에서 ALLEN을 찾음다음 deptno를 SELECT해서 dept테이블과 조인해서

ALLEN의 부서이름을 불러오고있습니다. ALLEN이 근무하는 부서이름은 SALES네요.

SQL> SELECT d.dname  2  FROM dept d   3  WHERE d.deptno = ( SELECT e.deptno   4   FROM emp e   5   WHERE e.ename = 'ALLEN')   6  / DNAME                                         --------------                                                  SALES

                                                                                                                                                                                    

3. 셀프조인(self join)예제

emp 테이블에는 mgr라고 있죠. 사원당 한명씩의 매니저가 있습니다. 

매니저도 사원이기 때문에 mgr칼럼에 해당사원의 매니저 사원번호가 입력되어있죠.

FROM절에 emp테이블을 하나는 e, 하나는 m으로 해서 셀프조인을 하고있습니다.

사원의 사원번호와 이름, 그리고 그사원 매니저의 사원번호 이름을 출력하고있죠.

SQL> SELECT e.empno, e.ename, m.empno, m.ename  2  FROM emp e, emp m   3  WHERE e.mgr = m.empno;      EMPNO ENAME           EMPNO ENAME                       ---------- ---------- ---------- ----------                               7902 FORD             7566 JONES                                      7788 SCOTT            7566 JONES                        7900 JAMES            7698 BLAKE                           7844 TURNER           7698 BLAKE                           7654 MARTIN           7698 BLAKE                             7521 WARD             7698 BLAKE                               7499 ALLEN            7698 BLAKE                                7934 MILLER           7782 CLARK                               7876 ADAMS            7788 SCOTT                                      7782 CLARK            7839 KING                                   7698 BLAKE            7839 KING                                      7566 JONES            7839 KING                              7369 SMITH            7902 FORD                        13 개의 행이 선택되었습니다.

4.아우터조인(outer join) 예제

예를 들자면 부서가 신설되어서 아직 사원이 배치가 안된 곳이 있을경우, 이럴때 부서는 있지만 해당하는

사원이 없을 수 있는데요. 이럴때 일반적으로 사원과 부서를 조인한다면 사원이 없는 부서는 나오지않습니다.

이럴때 아우터조인을 사용하는데요

쿼리를 보시면 emp쪽에 (+)를 붙여 주어서 부서에 사원이 없어도 해당부서가 출력된것을 볼 수 있습니다.

출력할때 사원쪽 출력되는 값이 적으니 +를 붙여준다~ 라고 생각하면 되겠죠 ~~

SQL> SELECT e.ename 사원명, d.dname 부서명   2  FROM emp e, dept d   3  WHERE e.deptno(+) = d.deptno; 사원명     부서명                                         ---------- --------------                                               SMITH      RESEARCH                                         ALLEN      SALES                                               WARD       SALES                                         JONES      RESEARCH                                      MARTIN     SALES                                            BLAKE      SALES                                           CLARK      ACCOUNTING                                    SCOTT      RESEARCH                                         KING       ACCOUNTING                                         TURNER     SALES                                             ADAMS      RESEARCH                                           JAMES      SALES                                        FORD       RESEARCH                                      MILLER     ACCOUNTING                                                    OPERATIONS                                                                                                                                                                   15 개의 행이 선택되었습니다.

결과가 나오게 하기위해서 어떻게 쿼리를 짜야할지 고민해보는것이 중요한데요

직접 생각해보면서 쿼리 짜는 연습을 해보세요~

5. allen의 직무와 같은 사람의 이름,부서명, 급여, 직무를 출력하세요

SQL> SELECT e.ename 사원이름, d.dname 부서명, e.sal 급여, e.job 직무

  2  FROM emp e, dept d

  3  WHERE e.deptno = d.deptno AND

  4   e.job = ( SELECT e.job

  5   FROM emp e

  6   WHERE e.ename = 'ALLEN')

  7  /

사원이름   부서명               급여 직무                                   

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

ALLEN      SALES                1600 SALESMAN      

WARD       SALES                1250 SALESMAN    

MARTIN     SALES                1250 SALESMAN 

TURNER     SALES                1500 SALESMAN                                                                                                                                                           

6. jones가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사일자, 급여를 출력하세요

SQL> SELECT e.empno, e.ename, e.hiredate, e.sal

  2  FROM emp e

  3  WHERE e.deptno = ( SELECT e.deptno

  4   FROM emp e

  5   WHERE e.ename = 'JONES');

     EMPNO ENAME      HIREDATE        SAL                   

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

      7369 SMITH      80/12/17        800              

      7566 JONES      81/04/02       2975           

      7788 SCOTT      87/04/19       3000        

      7876 ADAMS      87/05/23       1100            

      7902 FORD       81/12/03       3000                                                                                                                                                               

7.전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 지역, 급여를 출력하세요

SQL> SELECT e.empno 사원번호, e.ename 사원이름, d.dname 부서이름, e.hiredate 입사일, d.loc 지역, e.sal 급여

  2  FROM emp e, dept d

  3  WHERE e.deptno = d.deptno AND

  4   e.sal > ( SELECT AVG(e.sal)

  5   FROM emp e);

  사원번호 사원이름   부서이름       입사일   지역                급여     

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

      7566 JONES      RESEARCH       81/04/02 DALLAS              2975      

      7698 BLAKE      SALES          81/05/01 CHICAGO             2850        

      7782 CLARK      ACCOUNTING     81/06/09 NEW YORK            2450     

      7788 SCOTT      RESEARCH       87/04/19 DALLAS              3000      

      7839 KING       ACCOUNTING     81/11/17 NEW YORK            5000   

      7902 FORD       RESEARCH       81/12/03 DALLAS              3000      

6 개의 행이 선택되었습니다.

8. 10번부서 사람들 중에서 20번부서의 사원과 같은 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일, 지역을 출력하세요

SQL> SELECT e.empno 사원번호, e.ename 이름, d.dname 부서명, e.hiredate 입사일, d.loc 지역

  2  FROM dept d, (SELECT *

  3   FROM emp e

  4   WHERE e.deptno = 10) e

  5  WHERE e.deptno = d.deptno

  6   AND e.job IN (SELECT e2.job

  7   FROM emp e2

  8   WHERE e2.deptno = 20)

  9  /

  사원번호 이름       부서명         입사일   지역            

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

      7934 MILLER     ACCOUNTING     82/01/23 NEW YORK     

      7782 CLARK      ACCOUNTING     81/06/09 NEW YORK                                                                                                                                                  

9. 10번부서 중에서 30번부서에는 없는 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일자, 지역을 출력하세요

SQL> SELECT e.empno 사원번호, e.ename 이름, d.dname 부서명, e.hiredate 입사일, d.loc 지역

  2  FROM dept d, (SELECT *

  3   FROM emp e

  4   WHERE e.deptno = 10) e

  5  WHERE e.deptno = d.deptno

  6   AND e.job NOT IN (SELECT e2.job

  7   FROM emp e2

  8   WHERE e2.deptno = 30)

  9  /

  사원번호 이름       부서명         입사일   지역        

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

      7839 KING       ACCOUNTING     81/11/17 NEW YORK                                                                                                                                                  

10. 10번부서에 근무하는 사원의 사원번호, 이름, 부서명, 지역, 급여를 급여가 많은순으로 출력하세요

SQL> SELECT *

  2  FROM (SELECT e.empno 사원번호, e.ename 이름, d.dname 부서명, d.loc 지역, e.sal 급여

  3   FROM emp e, dept d

  4   WHERE e.deptno = d.deptno

  5   AND e.deptno = 10) e

  6  ORDER BY e.급여 DESC

  7  /

  사원번호 이름       부서명         지역                급여          

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

      7839 KING       ACCOUNTING     NEW YORK            5000      

      7782 CLARK      ACCOUNTING     NEW YORK            2450   

      7934 MILLER     ACCOUNTING     NEW YORK            1300                                                                                                                                           

11. martin이나 scott의 급여와 같은 사원의 사원번호, 이름, 급여를 출력하세요

SQL> SELECT e.empno, e.ename, e.sal

  2  FROM emp e

  3  WHERE e.sal IN (SELECT e.sal

  4   FROM emp e

  5   WHERE e.ename = 'MARTIN'

  6   OR e.ename = 'SCOTT')

  7  ;

     EMPNO ENAME             SAL               

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

      7654 MARTIN           1250               

      7521 WARD             1250                

      7902 FORD             3000                 

      7788 SCOTT            3000                                                                                                                                                                        

12. 급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하세요

SQL> SELECT e.empno, e.ename, e.sal

  2  FROM emp e

  3  WHERE e.sal > (SELECT MAX(e2.sal)

  4   FROM emp e2

  5   WHERE e2.deptno = 30);

     EMPNO ENAME             SAL                   

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

      7566 JONES            2975                    

      7788 SCOTT            3000                    

      7839 KING             5000                 

      7902 FORD             3000                                                                                                                                                                        

13. 급여가 30번 부서의 최저 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하세요

SQL> SELECT e.empno, e.ename, e.sal

  2  FROM emp e

  3  WHERE e.sal > (SELECT MIN(e2.sal)

  4   FROM emp e2

  5   WHERE e2.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                  

      7934 MILLER           1300                                                                                                                                                                       

12 개의 행이 선택되었습니다.