[KOSTA] Spring 기반 Cloud 서비스 개발자 양성 과정 27일차 - SQL 서브쿼리 실습

🎲 문제) ‘Patel’가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사 일, 급여를 출력하라.

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                        FROM EMPLOYEES
                         WHERE LAST_NAME = 'Patel');

🎲 문제) ‘Austin'의 직무(job)와 같은 사람의 이름, 부서명, 급여, 직무를 출력하라.

SELECT E.LAST_NAME, D.DEPARTMENT_NAME, E.SALARY, E.JOB_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND JOB_ID = (SELECT JOB_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'Austin');

🎲 문제) 'Seo'의 급여와 같은 사원의 사원번호, 이름, 급여를 출력하라.

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
 WHERE SALARY = (SELECT SALARY 
                  FROM EMPLOYEES
                 WHERE LAST_NAME = 'Seo');

🎲 문제) 급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT MAX(SALARY)
                FROM EMPLOYEES
                WHERE DEPARTMENT_ID = 30);

🎲 문제) 급여가 30번 부서의 최저 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT MIN(SALARY)
                FROM EMPLOYEES
                WHERE DEPARTMENT_ID = 30);

🎲 문제) 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 지역(city), 급여를 출력하라.

SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_NAME, HIRE_DATE, CITY, SALARY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND SALARY > (SELECT AVG(SALARY)
                FROM EMPLOYEES);

🎲 문제) 100번 부서 중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일, 지역을 출력하라.

SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_NAME, E.HIRE_DATE, L.CITY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.DEPARTMENT_ID = 100
AND JOB_ID NOT IN (SELECT JOB_ID
                    FROM EMPLOYEES
                    WHERE DEPARTMENT_ID = 30);

SELECT E.JOB, E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND JOB = (SELECT JOB
            FROM EMP
            WHERE ENAME = 'ALLEN')
ORDER BY SAL DESC;

SELECT E.EMPNO, E.ENAME, D.DNAME, E.HIREDATE, D.LOC, E.SAL, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
AND SAL > (SELECT AVG(SAL)
            FROM EMP)
AND SAL BETWEEN LOSAL AND HISAL
ORDER BY SAL DESC;

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = 10
AND JOB NOT IN (SELECT JOB
                FROM EMP
                WHERE DEPTNO = 30);

/* 다중행 */
SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP , SALGRADE
WHERE SAL > ALL(SELECT SAL
            FROM EMP
            WHERE JOB = 'SALESMAN'
            )
AND SAL BETWEEN LOSAL AND HISAL
ORDER BY EMPNO;

/* 단일행 */
SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL  > (SELECT MAX(SAL)
            FROM EMP
            WHERE JOB = 'SALESMAN'
            GROUP BY JOB)
AND SAL BETWEEN LOSAL AND HISAL
ORDER BY EMPNO;