오라클 SQL 8강 연습문제

--8강 연습문제

--1번

SELECT A.DEPTNO,A.DNAME,B.EMPNO,B.ENAME,B.SAL

FROM DEPT A,EMP B

WHERE A.DEPTNO = B.DEPTNO AND SAL >= 2000

ORDER BY DEPTNO;

 

--2번

SELECT A.DEPTNO,DNAME,TRUNC(AVG(SAL)) AS AVG_SAL,MAX(SAL) MAX_SAL,MIN(SAL) MIN_SAL,COUNT(*) AS CNT

FROM DEPT A , EMP B

WHERE A.DEPTNO=B.DEPTNO

GROUP BY A.DEPTNO , DNAME;

 

 

 

 

--3번

SELECT A.DEPTNO,DNAME,EMPNO,ENAME,JOB,SAL

FROM DEPT A , EMP B

WHERE A.DEPTNO = B.DEPTNO(+)

ORDER BY DEPTNO;

 

--4번

SELECT A.DEPTNO,A.DNAME,B.EMPNO,B.ENAME,B.MGR,B.SAL,B.DEPTNO,C.EMPNO AS MGR_EMPNO, C.ENAME AS MGR_ENAME,

GRADE,LOSAL,HISAL

FROM DEPT A,EMP B,EMP C,SALGRADE

WHERE A.DEPTNO=B.DEPTNO(+)

AND B.MGR=C.EMPNO(+)

AND B.SAL BETWEEN LOSAL(+) AND HISAL(+)

ORDER BY A.DEPTNO,B.EMPNO;