오라클 연습 문제(1)

1. research 부서에서 일하는 사원들 중 급여가 2000 이하인 사원들을 구하는 쿼리 작성 

 

>>select ename, sal from emp
 where deptno=(select deptno from dept where dname='RESEARCH') and sal<=2000;

 

2.사원들 중 급여등급이 5에 속하면서 comm이 500미만인 사원들의 이름, 급여, 수당을 구하고 연봉을 계산하라. 

단 수당을 모를 경우 0으로 간주한다. (non-equi , NVL 이용)

>>select ename, sal , nvl(comm,0) , sal*12+nvl(comm,0) 연봉
 from emp e , salgrade s
where s.grade=5
and e.sal between losal and hisal
and( e.comm <500 or e.comm is null);

>> select ename , nvl(comm,0) , sal, sal*12+nvl(comm,0) from emp where sal =(select sal from emp, salgrade where sal between losal and hisal and grade=5) and NVL(comm,0) <500 ;

3. 각 부서에서 같은 업무를 수행하는 사원들을 분류하여 부서번호, 업무명, 인원수, 평균급여를 출력하시오 

select deptno,job , count(*) , avg(sal) from emp group by deptno, job;

 

4. 사원이 2명 이상 있는 부서들에 대해 부서명과 평균 급여를 구하시오 

>> select d.dname, avg(sal) from emp e , dept d  where d.deptno = e.deptno group by d.dname having count(empno) >=2;

>>select dname, count(ename), avg(sal) from emp, dept having count(ename)>=2 group by dname;

 

5.각 사원에 대해 같은 부서에 근무하는 동료 사원들의 이름을 구하시오 

select e.ename ,f.ename 동료 , e.deptno from emp e, emp f 
where f.deptno = e. deptno and f.deptno in (select deptno from emp e);

 

6. salse 부서에 속한 사원들에 대해 사원번호, 이름 , 소속부서명, 급여, 입사일을 검색하여 새로운 테이블을 만드시오.

create table emptest
as
select e.empno, e.ename, d.dname, e.sal, e.hiredate from emp e,dept d 
where d.deptno = e.deptno and dname ='SALES';

 

 

7. RESEARCH 부서에 속한 사원들으 모든 데이터도 emptest 테이블에 추가하기 

INSERT INTO emptest select e.empno, e.ename, d.dname , e.sal, e.hiredate 
from emp e, dept d
 where e.deptno = d.deptno and dname='RESEARCH';

 

8. emp 테이블에서 20,30번 부서에서 근무하는 사원중 급여가 2000 초과인 사원의 사원번호, 이름, 급여, 부서번호 출력

select empno,ename,sal,deptno from emp where deptno in (20,30) and sal>2000;

9. 이름이 E 가 포함되어있는 30번 부서 사람중 급여가 1000-2000  사이가 아닌 사원의 이름, 사원번호, 급여, 부서번호 출력하기

select ename , empno,sal,deptno from emp where ename like '%E%' and sal not BETWEEN 1000 and 2000;

 

10 추가수당이 존재하지 않고 상급자가 있고, 직책이 MANAGER,CLERK 인 사원 중에서 사원이름의 두번째 글자가 L이 아닌 사원의 정보를 출력하시오. 

>> select * from emp where comm is null and job in ('MANAGER','CLERK') and instr(ename,'L')<>2;

>> select * from emp where comm is null and job in ('MANAGER','CLERK') and ename not like ('_L%') ;