[Oracle] SQL Class 2 - 과제 1

<EMP> Table
1. emp table의 모든 열을 하나의 열로 출력하라.(ok)
 (단, 각 열은 쉼표로 구분하며 표시하고, 열의 이름은 THE_OUTPUT으로 지정하라)
SQL> select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno as THE_OUTPUT from emp;

2. 급여가 1500 ~ 2850 사이의 범위에 속하지 않는 모든 사원의 이름 및 급여를 표시하라.(ok)
SQL> select ename, sal from emp
          where sal<1500 and sal>2850;

3. 1981년 2월 20일 ~ 1981년 5월 1일에 입사한 사원의 이름, 직위 및 입사일을 표시하라.(ok)
 (입사일을 기준으로 오름차순 정렬할 것!)
SQL> select ename, mgr, hiredate from emp
           where hiredate>='81/02/20' and hiredate<='81/05/01' 
           order by hiredate desc;

4. 부서가 10, 30에 속하는 사원 중 급여가 1500을 넘는 사원의 이름 및 급여를 표시하라.(ok)
SQL> select ename, sal, deptno from emp
           where (deptno=10 or deptno=30) and sal>1500;

5. 1982년에 입사한 모든 사원의 이름과 입사일을 표시하라.(ok)
SQL> select ename, hiredate from emp
          where hiredate>='82/01/01' and hiredate<='83/01/01';

6. 이름에 L이 두 번 들어가며 부서 30에 속하거나 관리자 번호가 7782인 모든 사원의 이름을 표시하라.(ok)
SQL> select ename, job, deptno, empno from emp
where ename like '%L%L%' and (deptnolike' =30' or empno='7782'); order by ename;

7. 직위가 CLREK, ANALYST 이면서 급여가 1000, 3000, 5000가 아닌 모든 사원의 이름, 직위 및 급여를 표시하라.(ok)
SQL> select ename, job, sal from emp
where (job='CLERK' or job='ANALYST') and not sal in(1000, 3000, 5000) (sal!=1000 or sal!=3000 or sal!=5000);
order by ename, job, sal;

8. 사원 번호, 이름, 급여 및 15% 인상된 급여를 함께 추출하고 열 레이블(별칭)을 NEW SALARY로 하라.(ok)
select empno, ename, sal, sal+(sal*1.15) "NEW SALARY" from emp;

9. 8번 문제에 인상분만을 표시하는 열을 추가하여 추출하고, 열 이름을 INCREASE 로 하라.(ok)
select empno, ename, sal, sal+(sal*1.15) "NEW SALARY", sal*0.15 "INCARESE" from emp;

10. 사원 이름 및 커미션을 표시하는 질의를 작성한다.
    커미션을 받지 않는 사원일 경우 ‘No Commision’을 표시하고 열 이름을 COMM으로 지정한다.(ok)
select ename, nvl(to_char(comm), 'No Commission') "COMM" from emp;

11. 이름에 L이 두번만 포함하는 사람을 추출-instr함수(ok)
select ename from emp
where instr(ename,'L',1,2)!=0 and instr(ename,'L',1,3)=0;