DB/oracle

[oracle] scott 계정 예제 (3) 21번 ~ 33번

힘히힘 2021. 2. 17. 15:10
반응형

--21. 10번 부서 사원들 중에서 20번 부서의 사원과 같은 직무를 갖는 사원의 사원 번호 ,이름,부서 이름, 입사일, 부서 위치를 출력하여라.

 


SELECT empno "사원번호", ename "이름", To_char(hiredate, 'yyyy-mm-dd') "입사일", loc "부서위치"
FROM emp JOIN dept ON (dept.deptno=emp.deptno)
WHERE emp.deptno=10 AND job in (SELECT job FROM emp WHERE deptno=20);

 

--22. 'ALLEN'의 급여보다 많고 'SCOTT'의 급여보다 적은 사원의 사원번호,이름,부서이름,부서위치,급여를 급여가 많은 순으로 출력하여라.

 


SELECT e.empno "사원번호", e.ename "이름", d.dname "부서이름", d.loc "위치", e.sal "급여"
FROM emp e JOIN dept d ON (d.deptno=e.deptno)
WHERE e.sal >(SELECT e.sal FROM emp e WHERE e.ename='ALLEN')
and sal <(SELECT sal FROM emp WHERE ename='SCOTT')
ORDER BY sal DESC;

 

--23. 30번 부서의 최고급여보다 급여가 많은 사원의 사원 번호 ,이름,급여를 출력하여라.

 


SELECT empno 사원번호, ename 이름, sal 급여
FROM emp
WHERE sal > (SELECT max(sal) FROM emp WHERE deptno=30);


--24. 사원 이름의 두 번째 글자가 'A'인 사원들의 수를 출력하여라.

 


SELECT ename 사원이름 , count(*)
FROM emp
WHERE ename LIKE '_A%';


--count는 그룹함수임. 그룹함수 사용하고 group by 사용하지 않아서 발생하는.

 

SELECT count(empno)
FROM emp
WHERE ename LIKE '_A%';

 

 


--25. 사원들의 평균 급여,총 급여, 최고급여액,최저 급여액을 구하여라.
SELECT avg(sal) "평균 급여", sum(sal) "총 급여", max(sal) "최고급여액", min(sal) "최저 급여액"
FROM emp;

 

--26 평균급여가 1500이 넘는 직무와 평균 급여를 구하여라.
SELECT job 직무, avg(sal) "평균 급여"
FROM emp
GROUP BY job
having avg(sal) > 1500;


--27. 사원들의 이름,부서 이름, 급여를 출력하여라.
SELECT ename 이름, dname 부서, sal 급여
FROM emp JOIN dept ON dept.deptno=emp.deptno;


SELECT ename 이름, dname 부서, sal 급여
FROM emp natural JOIN dept;

 

--28. 각 부서별 부서 번호(모든 부서에 대해서),부서 이름,부서 위치,사원의 수를 출력하여라.
SELECT dept.deptno 부서번호, dname 부서이름, loc 부서위치, count(ename) "사원의 수"
FROM emp JOIN dept ON dept.deptno=emp.deptno

--?

 

--29. 모든 사원들의 평균 급여보다 많이 받는 사원들의 사원 번호와 이름을 출력하여라.
SELECT empno 사원번호, ename 이름
FROM emp
WHERE sal > (SELECT avg(sal) FROM emp);

 

--30. 'FORD'와 부서가 같은 사원들의 이름,부서 이름, 직무, 급여를 출력하여라.
SELECT ename 이름, dname "부서 이름", job 직무, sal 급여
FROM emp JOIN dept ON dept.deptno=emp.deptno
WHERE emp.deptno =(SELECT deptno FROM emp WHERE ename ='FORD');


--31. 부서이름이 'SALES'인 사원들의 평균 급여보다 많고, 부서 이름이 'RESEARCH'인 사원들의 평균 급여보다 적은 사원들의 이름, 부서번호, 급여, 직무를 출력하여라.
SELECT ename 이름, emp.deptno 부서번호, sal 급여, job 직무
FROM emp JOIN dept ON dept.deptno=emp.deptno
WHERE sal > (SELECT avg(sal) FROM emp, dept WHERE dname ='SALES')
OR sal< (SELECT avg(sal) FROM emp, dept WHERE dname='RESEARCH');

--이상함.


--32.부서별 평균 급여가 1000보다 적은 부서 사원들의 부서 번호를 출력하여라.
SELECT deptno
FROM emp
GROUP BY deptno having avg(sal) <1000;


--새로운
SELECT DEPTNO "부서 번호", AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING 1000 > CEIL(AVG(SAL)) ORDER BY DEPTNO
--???


--33. 직무가 'ANALYST'인 사원보다 급여가 적으면서, 직무가 'ANALYST'가 아닌 사원들의 사원번호,이름, 직무, 급여를 출력하여라.
--보류 오류남
SELECT empno 사원번호, ename 이름, job 직무, sal 급여
FROM emp
WHERE sal < (SELECT sal FROM emp WHERE job = 'ANALYST' AND NOT job = 'ANALYST');


----????