Oracle - 연습문제 #5

1. EMP 테이블에서 모든 사원에 대한 이름, 부서번호, 부서명을 출력하는 SELECT 문장을 작성하여라.

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

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select emp.ename, emp.deptno, dept.dname
  2  from emp, dept
  3* where emp.deptno=dept.deptno
SQL> /

ENAME                    DEPTNO DNAME
-------------------- ---------- ---------------
SMITH                        20 RESEARCH
ALLEN                        30 SALES
WARD                         30 SALES
JONES                        20 RESEARCH
MARTIN                       30 SALES
BLAKE                        30 SALES
CLARK                        10 ACCOUNTING
SCOTT                        20 RESEARCH
KING                         10 ACCOUNTING
TURNER                       30 SALES
ADAMS                        20 RESEARCH

ENAME                    DEPTNO DNAME
-------------------- ---------- ---------------
JAMES                        30 SALES
FORD                         20 RESEARCH
MILLER                       10 ACCOUNTING

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

SQL>


2. EMP 테이블에서 NEW YORK 에서 근문하고 있는 사원에 대하여 이름, 업무, 급여, 부서명을 출력하는 SELECT 문장을 작성하여라.

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

SQL> col DNAME format a15
SQL> col NAME format a10
SQL> col JOB format a10
SQL> col Location format a15
SQL> r
  1  select emp.ename as "NAME", emp.job as "JOB",
  2  emp.sal as "Salary", dept.dname as "DNAME", dept.loc as "Location"
  3  from emp, dept
  4* where emp.deptno=dept.deptno and loc='NEW YORK'

NAME       JOB            Salary DNAME           Location
---------- ---------- ---------- --------------- ---------------
CLARK      MANAGER          2450 ACCOUNTING      NEW YORK
KING       PRESIDENT        5000 ACCOUNTING      NEW YORK
MILLER     CLERK            1300 ACCOUNTING      NEW YORK

SQL>

3. EMP 테이블에서 보너스를 받는 사원에 대하여 이름, 부서명, 위치를 출력하는 SELECT 문장을 작성하여라.

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

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select emp.ename, emp.comm, dept.dname, dept.loc
  2  from emp,dept
  3* where emp.deptno=dept.deptno and nvl(comm,0) > 0
SQL> /

ENAME                      COMM DNAME           LOC
-------------------- ---------- --------------- --------------------------
ALLEN                       300 SALES           CHICAGO
WARD                        500 SALES           CHICAGO
MARTIN                     1400 SALES           CHICAGO

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

or

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select emp.ename, emp.comm, dept.dname, dept.loc
  2  from emp, dept
  3* where emp.deptno = dept.deptno and emp.comm is not null and emp.comm not in(0)
SQL> /

ENAME                      COMM DNAME           LOC
-------------------- ---------- --------------- --------------------------
ALLEN                       300 SALES           CHICAGO
WARD                        500 SALES           CHICAGO
MARTIN                     1400 SALES           CHICAGO

SQL>

4. EMP 테이블에서 이름 중 L자가 있는 사원에 대하여 이름, 업무, 부서명, 위치를 출력하는 SELECT 문장을 작성하여라.

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

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select ename, job, dname, loc
  2  from emp,dept
  3* where emp.deptno = dept.deptno and emp.ename like '%L%'
SQL> /

ENAME                JOB        DNAME           LOC
-------------------- ---------- --------------- --------------------------
ALLEN                SALESMAN   SALES           CHICAGO
BLAKE                MANAGER    SALES           CHICAGO
CLARK                MANAGER    ACCOUNTING      NEW YORK
MILLER               CLERK      ACCOUNTING      NEW YORK

SQL>

5. 아래의 결과를 출력하는 SELECT 문장을 작성하여라. (관리자가 없는 KING 을 포함하여 모든 사원을 출력)

Employee     Emp#     Manager     MGR# -------------------------------------------- KING             7839 BLAKE           7698       KING          7839 CLARK           7782       KING          7839 ................................................ 14 rows selectd

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

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select worker.ename as "Employee", worker.empno as "Emp#",
  2  manager.ename as "Manager", manager.empno as "Mgr#"
  3  from emp worker join emp manager
  4  on (manager.empno(+)=worker.mgr)
  5* order by manager.empno desc
SQL> /

Employee                   Emp# Manager                    Mgr#
-------------------- ---------- -------------------- ----------
KING                       7839
SMITH                      7369 FORD                       7902
CLARK                      7782 KING                       7839
BLAKE                      7698 KING                       7839
JONES                      7566 KING                       7839
ADAMS                      7876 SCOTT                      7788
MILLER                     7934 CLARK                      7782
TURNER                     7844 BLAKE                      7698
JAMES                      7900 BLAKE                      7698
ALLEN                      7499 BLAKE                      7698
WARD                       7521 BLAKE                      7698

Employee                   Emp# Manager                    Mgr#
-------------------- ---------- -------------------- ----------
MARTIN                     7654 BLAKE                      7698
FORD                       7902 JONES                      7566
SCOTT                      7788 JONES                      7566

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

SQL>

or

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select worker.ename as "Employee", worker.empno as "Emp#",
  2  manager.ename as "Manager", manager.empno as "Mgr#"
  3  from emp worker, emp manager
  4  where manager.empno(+)=worker.mgr
  5* order by manager.empno desc
SQL> /

Employee                   Emp# Manager                    Mgr#
-------------------- ---------- -------------------- ----------
KING                       7839
SMITH                      7369 FORD                       7902
CLARK                      7782 KING                       7839
BLAKE                      7698 KING                       7839
JONES                      7566 KING                       7839
ADAMS                      7876 SCOTT                      7788
MILLER                     7934 CLARK                      7782
TURNER                     7844 BLAKE                      7698
JAMES                      7900 BLAKE                      7698
ALLEN                      7499 BLAKE                      7698
WARD                       7521 BLAKE                      7698

Employee                   Emp# Manager                    Mgr#
-------------------- ---------- -------------------- ----------
MARTIN                     7654 BLAKE                      7698
FORD                       7902 JONES                      7566
SCOTT                      7788 JONES                      7566

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

SQL>

6. EMP 테이블에서 그들의 관리자 보다 먼저 입사한 사원에 대하여 이름, 입사일, 관리자 이름, 관리자 입사일을 출력하는 SELECT 문장을 작성하여라.

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

SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select worker.ename, worker.empno, worker.hiredate,
  2  manager.ename, worker.mgr, manager.hiredate
  3  from emp worker, emp manager
  4* where worker.mgr=manager.empno and worker.hiredate < manager.hiredate
SQL> /

ENAME           EMPNO HIREDATE ENAME             MGR HIREDATE
---------- ---------- -------- ---------- ---------- --------
SMITH            7369 80/12/17 FORD             7902 81/12/03
ALLEN            7499 81/02/20 BLAKE            7698 81/05/01
WARD             7521 81/02/22 BLAKE            7698 81/05/01
JONES            7566 81/04/02 KING             7839 81/11/17
BLAKE            7698 81/05/01 KING             7839 81/11/17
CLARK            7782 81/06/09 KING             7839 81/11/17

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

SQL>