[Oracle] Oracle 연습문제 1

Oracle 연습문제 1

1. 덧셈 연산자를 이용하여 모든 사원에 대해서 $300의 급여 인상을 계산한 후 사원의 이름, 급여, 인상된 급여를 출력하시오.

select ename, sal, sal+300
from emp;

 

2. 사원의 이름, 급여, 연간 총수입을 총수입이 많은 것부터 작은 순으로 출력하시오. 연간 총수입은 월급에 12를 곱한 후 $100의 상여금을 더해서 계산하시오.

select ename, sal, sal*12+100
from emp
order by sal*12+100 desc;

 

3. 급여가 2000을 넘는 사원의 이름과 급여를 급여가 많은 것부터 작은 순으로 출력하시오.

select ename, sal
from emp
where sal > 2000
order by sal desc;

 

4. 사원번호가 7788인 사원의 이름과 부서번호를 출력하시오.

select ename, deptno
from emp
where empno = 7788;

 

5. 급여가 2000에서 3000 사이에 포함되지 않은 사원의 이름과 급여를 출력하시오.

select ename, sal
from emp
where not sal between 2000 and 3000;

 

6. 1981년2월20일부터 1981년 5월 1일 사이에 입사한 사원의 이름, 담당 업무, 입사일을 출력하시오.

select ename, job, hiredate
from emp
where hiredate between ‘81/02/20’ and ‘81/05/01’;

 

7. 부서번호가 20및30에 속한 사원의 이름과 부서번호를 출력하되 이름을 기준(내림차순)으로 영문자 순으로 출력하시오.

select ename, deptno
from emp
where deptno in (20, 30)
order by ename desc;

 

8. 사원의 급여가 2000에서 3000사이에 포함되고 부서번호가 20 또는 30인 사원의 이름, 급여와 부서번호를 출력하되 이름 순(오름차순)으로 출력하시오.

select ename, sal, deptno
from emp
where sal between 2000 and 3000 
and deptno in (20, 30)
order by ename;

 

9. 1981년도에 입사한 사원의 이름과 입사일을 출력하시오(LIKE 연산자 와일드 카드 사용).

select ename, hiredate
from emp
where hiredate like ‘81%’;

 

10. 관리자가 없는 사원의 이름과 담당 업무를 출력하시오.

select ename, job
from emp
where mgr is null; 

 

11. 커미션을 받을 수 있는 자격이 되는 사원의 이름, 급여, 커미션을 출력하되 급여 및 커미션을 기준으로 내림차순 정렬하여 표시하시오.

select ename, sal, comm
from emp
order by sal, comm desc;

 

12. 이름의 세 번째 문자가 R인 사원의 이름을 표시하시오.

select ename
from emp
where ename like ‘__R%’;

 

13. 이름에 A와 E를 모두 포함하고 있는 사원의 이름을 표시하시오

select ename
from emp
where ename like ‘%A%’
and ename like ‘%E%’;

 

14. 담당 업무가 사무원(CLERK) 또는 영업사원(SALESMAN)이면서 급여가 $1600, $960 또는 $1300이 아닌 사원의 이름, 담당업무, 급여를 출력하시오.

select ename, job, sal
from emp
where job in(‘CLERK’, ‘SALESMAN’)
and sal not in(1600, 960, 1300);

 

15. 커미션이 $500 이상인 사원의 이름과 급여 및 커미션을 출력하시오.

select ename, sal, comm
from emp
where comm >= 500;

 

---

1. SUBSTR함수를 사용하여 사원들의 입사한 년도와 입사한 달만 출력하시오.

 

select substr(hiredate, 1,2) as 년도, substr(hiredate,4 ,2) as 달
from emp;

 

2. SUBSTR함수를 사용하여 4월에 입사한 사원을 출력하시오.

select *
from emp
where substr(hiredate,4,2) = '04'; 

 

3. MOD함수를 사용하여 사원번호가 짝수인 사람만 출력하시오.

select *
from emp
where mod(empno, 2) = 0;

 

4. 입사일을 연도는 2자리(YY), 월은 숫자(MON)로 표시하고요일은 약어(DY)로 지정하여 출력하시오.

select hiredate, to_char(hiredate, 'YY/MON/DD DY')
from emp

 

5. 올해 며칠이 지났는지 출력하시오. 현재 날짜에서 올해 1월1일을 뺀 결과를 출력하고 TO_DATE함수를 사용하여 데이터 형을 일치시키시오.

select trunc(sysdate-to_date('2018/01/01','yyyy/mm/dd')) days
from dual; 

 

6. 사원들의 상관 사번을 출력하되 상관이 없는 사원에 대해서는 NULL값 대신 O으로 출력하시오.

select eno, ename, nvl2(mgr, mgr, 0)
from emp;

 

7. DECODE 함수로 직급에 따라 급여를 인상하도록 하시오. 직급이 ‘ANALYST’인 사원은 200, ‘SALESMAN’인 사원은 180, ‘MANAGER’인 사원은 150, ‘CLERK’인 사원은 100을 인상하시오

select eno, ename, job ,sal
decode(job, ‘ANALYST’, sal+200, ‘SALESMAN’, sal+180, ‘MANAGER’, sal+150, ‘CLERK’, sal+100)
from emp;

 

---

1. 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오. 칼럼의 별칭은 결과 화면과 동일하게 지정하고 평균에 대해서는 정수로 반올림하시오.

select max(sal) Maximum, min(sal) Minimum, sum(sal) Sum, round(avg(sal)) average
from emp

 

2. 각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균액을 출력하시오. 칼럼의 별칭은 결과 화면과 동일하게 지정하고 평균에 대해서는 정수로 반올림하시오.

select max(sal) Maximum, min(sal) Minimum, sum(sal) Sum, avg(sal) Average
from emp
group by job;

 

3. COUNT(*) 함수를 이용하여 담당 업무가 동일한 사원 수를 출력하시오.

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

 

4. 관리자 수를 나열하시오. 칼럼의 별칭은 결과 화면과 동일하게 지정하시오.

select count(mgr)
from emp

 

5. 급여 최고액, 급여 최저액의 차액을 출력하시오. 칼럼의 별칭은 결과 화면과 동일하게 지정하시오.

select max(sal)-min(sal) DIFFERENCE
from emp;

 

6. 직급별 사원의 최저 급여를 출력하시오. 관리자를 알 수 없는 사원 및 최저 급여가 2000 미만인 그룹은 제외시키고 결과를 급여에 대한 내림차순으로 정렬하여 출력하시오.

select job, min(sal)
from emp
where mgr is not null
group by job
having min(sal) > 2000
order by min(sal) desc;

 

7. 각 부서에 대해 부서번호, 사원수, 부서 내의 모든 사원의 평균 급여를 출력하시오. 칼럼의 별칭은 결과 화면과 동일하게 지정히고 평균 급여는 소수점 둘째 자리로 반올림하시오.

select deptno, count(*) ”number of people”, round(avg(sal), 2) Salary
from emp
group by deptno;

 

8. 각 부서에 대해 부서번호 이름, 지역명, 사원수, 부서 내의 모든 사원의 평균 급여를 출력하시오. 칼럼의 별칭은 결과 화면과 동일하게 지정하고 평균 급여는 정수로 반올림하시오.

select 
decode(deptno, 10, ‘accounting’, 20, ‘research’, 30, ‘sales’, 40 ‘operations”) as dname,
decode (deptno, 10, ‘new york’, 20, ‘dallas’, 30, ‘chicago’, 40, ‘boston’) as loc,
count(*) “Number of People”, round(avg(sal)) Salary
from emp
group by(deptno);

 

9. 업무를 표시한 다음 해당 업무에 대해 부서번호별 급여 및 부서 10, 20, 30의 급여 총액을 각각 출력하시오. 각 컬럼의 별칭은 각각 JOB, 부서 10, 부서 20, 부서 30, 총액으로 지정하시오.

select job, deptno
decode(deptno, 10, sal(sum)) as “부서10”,
decode(deptno, 20, sal(sum)) as “부서 20”,
decode(deptno, 30, sal(sum)) as “부서 30”,
sum(sal) as 총액
groub by job, deptno
order by deptno;

---

1. 사원번호가 7788인 사원과 담당 업무가 같은 사원을 표시(사원 이름과 담당 업무)하시오.

select ename, job
from emp
where job = (select job from emp where empno = 7788);

 

2. 사원번호가 7499인 사원보다 급여가 많은 사원을 표시(사원이름과 담당 업무)하시오.

select ename, job
from emp
where sal > (select sal from emp where empno = 7499);

 

3. 최소 급여를 받는 사원의 이름, 담당 업무 및 급여를 표시하시오(그룹 함수 사용).

select ename, job, sal
from emp
where sal = (select min(sal) from emp);

 

4. 평균 급여가 가장 적은 사원의 담당 업무를 찾아 직급과 평균 급여를 표시하시오.

select job, avg(sal)
from emp
group by job
having avg(sal) = (select min(avg(sal)) from emp group by job);

 

5. 각 부서의 최소 급여를 받는 사원의 이름, 급여, 부서번호를 표시하시오.

select ename, sal, deptno
from emp
where min(sal) in (select min(sal) from emp group by deptno)

 

6. 담당 업무가 분석가(ANALYST)인 사원보다 급여가 적으면서 업무가 분석가가 아닌 사원들을 표시(사원번호, 이름, 담당 업무, 급여)하시오.

select empno, ename, job, sal
from emp
where sal < any (select sal from emp where job = ‘ANALYST’)
and job <> ‘ANALYST’;

 

7. 부하직원이 없는 사원의 이름을 표시하시오.

 

 

8. 부하직원이 있는 사원의 이름을 표시하시오.

 

 

9. BLAKE와 동일한 부서에 속한 사원의 이름과 입사일을 표시하는 질의를 작성하시오.(단, BLAKE는 제외).

select ename, hiredate
from emp
where deptno = (select deptno from emp where ename = ‘BLAKE’)
and ename <> ‘BLAKE’;

 

10. 급여가 평균 급여보다 많은 사원번호와 이름을 표시하되 결과를 급여에 대해서 오름차순으로 정렬하시오.

select empno, ename, sal
from emp
where sal > (select avg(sal) from emp)
order by sal;

 

11. 이름에 K가 포함된 사원과 같은 부서에서 일하는 사원의 사원번호와 이름을 표시하는 질의를 작성하시오.

select empno, ename
from emp
where deptno in (select deptno from emp where ename like ‘%K%’);

 

12. 부서 위치가 DALLAS인 사원의 이름과 부서번호 및 담당 업무를 표시하시오.

select empno, ename, job
from emp
where deptno = (select deptno from dept where loc = ‘DALLAS’);

 

13. KING에게 보고하는 사원의 이름과 급여를 표시하시오.

select ename, sal
from emp
where mgr = (select empno from emp where ename = ‘KING’);

 

14. RESEARCH부서의 사원에 대한 부서번호, 사원이름 및 담당 업무를 표시하시오.

select deptno, ename, job
from emp
where deptno = (select deptno from dept where dname = ‘RESEARCH’);

 

15. 평균 급여보다 많은 급여를 받고 이름에 M이 포함된 사원과 같은 부서에서 근무하는 사원의 사원번호, 이름, 급여를 표시하시오.

select empno, ename, sal
from emp
where sal > (select avg(sal) from emp)
and select deptno in (select deptno from emp where ename like ‘%M%’);

 

16. 평균 급여가 가장 적은 업무를 찾으시오.

select job, avg(sal)
from emp
group by job
having avg(sal) = (select min(avg(sal)) from emp group by job);

 

17. 담당 업무가 MANAGER인 사원이 소속된 부서와 동일한 부서의 자원을 표시하시오.

select ename
from emp
where deptno = (select deptno from emp where job = ‘MANAGER’);