[오라클DB] 쿼리 연습문제

SELECT *FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM SALGRADE;

SELECT *
FROM EMP E
INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND HISAL;


--1. 각 부서별 급여 등급 평균 구하기
SELECT DEPTNO, ROUND(AVG(GRADE),2)
FROM EMP
INNER JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL
GROUP BY DEPTNO;

-- 2. 각 부서별 급여 등급이 3등급 이상인 사람의 수 구하기
SELECT DEPTNO, COUNT(*)
FROM EMP 
INNER JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL
WHERE GRADE >= 3
GROUP BY DEPTNO;

--3. 평균급여 등급이 가장 높은 부서와 낮은 부서와의 차이 구하기
SELECT MAX(A)-MIN(A)
FROM (
        SELECT DEPTNO, AVG(GRADE)AS A
        FROM EMP
        INNER JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL
        GROUP BY DEPTNO
        );

--4. 평균급여 등급이 가장 낮은 부서의 급여 평균 구하기
SELECT ROUND(AVG(SAL),2) AS 급여평균
FROM EMP
WHERE DEPTNO IN (
                SELECT DEPTNO
                FROM (
                        SELECT DEPTNO, AVG(GRADE)AS A
                        FROM EMP
                        INNER JOIN SALGRADE S ON SAL BETWEEN LOSAL AND HISAL
                        GROUP BY DEPTNO
                        ORDER BY A
                        )
                WHERE ROWNUM = 1
                );
                
--5. 평균급여 등급이 가장 낮은 부서에서 가장 먼저 입사한 사람 구하기
SELECT *
FROM (
        SELECT *
        FROM EMP
        WHERE DEPTNO IN (
                        SELECT DEPTNO
                        FROM (
                                SELECT DEPTNO, AVG(GRADE)AS A
                                FROM EMP
                                INNER JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL
                                GROUP BY DEPTNO
                                ORDER BY A
                                )
                        WHERE ROWNUM = 1
                        )
        ORDER BY HIREDATE
        )
WHERE ROWNUM = 1;