Oracle - 연습문제 #7

SQL*PLUS 명령어

1. EMP 테이블에서 이름과 업무는 comma(,)로 구분하여 출력하고, 입사일자는 YYYY년 MM월 DD일 X요일 형태로 출력하는 SELECT 문장을 기술하시오.

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

SQL>


2. EMP 테이블에서 실행시 부서위치를 입력 받아 사원이름, 업무, 부서명을 출력하는 SCRIPT를 작성하시오.

p_name의 값을 입력하십시오: dallas 구   3: where emp.deptno=dept.deptno and loc=upper('&p_name') 신   3: where emp.deptno=dept.deptno and loc=upper('dallas')   Employee             JOB                Dept_name -------------------- ------------------ ---------------------------- SMITH                CLERK              RESEARCH JONES                MANAGER            RESEARCH SCOTT                ANALYST            RESEARCH ADAMS                CLERK              RESEARCH FORD                 ANALYST            RESEARCH

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

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

  1  select ename as "Employee", job, dname as "Dept_name"
  2  from emp, dept
  3* where emp.deptno=dept.deptno and loc=upper('&p_name')
SQL> /
p_name의 값을 입력하십시오: dallas
구   3: where emp.deptno=dept.deptno and loc=upper('&p_name')
신   3: where emp.deptno=dept.deptno and loc=upper('dallas')

Employee             JOB                Dept_name
-------------------- ------------------ ----------------------------
SMITH                CLERK              RESEARCH
JONES                MANAGER            RESEARCH
SCOTT                ANALYST            RESEARCH
ADAMS                CLERK              RESEARCH
FORD                 ANALYST            RESEARCH

SQL>

3. EMP테이블에서 입력되는 지역에 대해서 부서명, 사원이름, 입사일, 급여, 총급여를 출력하는 SCRIPT를 작성하시오.

p_name의 값을 입력하십시오: chicago 구   3: where emp.deptno=dept.deptno and loc=upper('&p_name') 신   3: where emp.deptno=dept.deptno and loc=upper('chicago') Department           Employee   Start name                 name       date             Salary Annual salary -------------------- ---------- ---------- ------------ ------------- SALES                ALLEN      81/02/20      $1,600.00    $19,200.00 SALES                WARD       81/02/22      $1,250.00    $15,000.00 SALES                MARTIN     81/09/28      $1,250.00    $15,000.00 SALES                BLAKE      81/05/01      $2,850.00    $34,200.00 SALES                TURNER     81/09/08      $1,500.00    $18,000.00 SALES                JAMES      81/12/03        $950.00    $11,400.00 6 개의 행이 선택되었습니다.

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

SQL> col dname heading 'Department|name' format a20
SQL> col ename heading 'Employee|name' format a10
SQL> col hiredate heading 'Start|date' format a10
SQL> col sal heading 'Salary' format $999,999.00
SQL> col sal*12 heading 'Annual salary' format $999,999.00
SQL> ed
file afiedt.buf(이)가 기록되었습니다

  1  select dept.dname, ename, hiredate, sal, sal*12
  2  from emp, dept
  3* where emp.deptno=dept.deptno and loc=upper('&p_name')
SQL> /
p_name의 값을 입력하십시오: chicago
구   3: where emp.deptno=dept.deptno and loc=upper('&p_name')
신   3: where emp.deptno=dept.deptno and loc=upper('chicago')

Department           Employee   Start
name                 name       date             Salary Annual salary
-------------------- ---------- ---------- ------------ -------------
SALES                ALLEN      81/02/20      $1,600.00    $19,200.00
SALES                WARD       81/02/22      $1,250.00    $15,000.00
SALES                MARTIN     81/09/28      $1,250.00    $15,000.00
SALES                BLAKE      81/05/01      $2,850.00    $34,200.00
SALES                TURNER     81/09/08      $1,500.00    $18,000.00
SALES                JAMES      81/12/03        $950.00    $11,400.00

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

SQL>