서브쿼리
-- 07_Subquery.sql
-- 서브쿼리 : 단일행 서브쿼리(1건만 나옴)
-- 예제1) SCOTT 사원보다 급여를 많이 받는 사원을 찾기위한 SQL문을 작성하세요
-- 해결 : 1-1) 사원테이블에서 SCOTT 사원의 급여를 찾은 후에(1번)
-- 1-2) 그 급여를 비교해서 많이 받는 사원을 추가로 찾아야함(2번)
-- 1번
SELECT SALARY
FROM EMPLOYEE
WHERE ENAME = 'SCOTT'; -- 3000(급여)
-- 2번
SELECT ENAME, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000;
-- 문제점 ) 성능 저하 : 여러번 쿼리로 인해
-- 사용법) SELECT 컬럼명 FROM 테이블명
-- WHERE 비교컬럼 > (SELECT 비교컬럼 FROM 테이블명)
SELECT ENAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY
FROM EMPLOYEE
WHERE ENAME = 'SCOTT');
-- 예제1) SCOTT 사원과(1개) 동일한 부서에서(DNO) 근무하는 사원(ENAME)(2개) 출력하기
-- 힌트) 1) 2개의 SQL 만들고
-- 1-1) SCOTT 사원의 부서번호를 찾기
-- 1-2) 위의 부서번호로 다른 사원 찾기
-- 2) 1개로 합치기
SELECT ENAME FROM EMPLOYEE
WHERE DNO = (SELECT DNO FROM EMPLOYEE
WHERE ENAME = 'SCOTT');
-- 예제 2) 30번 부서에서 최소급여를(MIN) 구한 후) 2)그 최소 급여보다(MIN)
-- 부서별(DNO) 최소 급여가 큰 부서만 출력하세요.
-- 서브쿼리를 사용하세요.
-- 1) 30번 부서에서 최소급여를 구한 후 : 950
SELECT MIN(SALARY)
FROM EMPLOYEE
WHERE DNO = 30;
-- 2) 부서별 최소 급여 구하기 :
SELECT DNO, MIN(SALARY)
FROM EMPLOYEE
GROUP BY DNO
HAVING MIN(SALARY) > (SELECT MIN(SALARY)
FROM EMPLOYEE
WHERE DNO = 30);
-- 3) 특수한 경우 아래처럼 사용도 가능
-- SELECT 예약어 다음에도 서브쿼리 사용가능
SELECT 2*3, (SELECT 4*3 FROM DUAL)
FROM DUAL;
-- FROM 예약어 다음에도 서브쿼리 사용가능
SELECT EMP.ENAME, EMP.ENO
FROM (SELECT ENAME, ENO FROM EMPLOYEE) EMP;
-- 2) 다중 행 서브쿼리(자식쿼리) : 여러 건이 나옴
-- 예제4) (1)부서별 최소 급여를 받는 사원 중에서 (2) 사원번호와 이름을 출력하세요
-- (1) 부서번호별(DNO) 최소 급여(SALARY) : (950, 1300, 800)
SELECT MIN(SALARY)
FROM EMPLOYEE
GROUP BY DNO;
-- (2) (950, 1300, 800) 급여를(SALARY) 받는 사원번호(ENO), 이름을(ENAME) 출력하라
SELECT ENO, ENAME
FROM EMPLOYEE
WHERE SALARY IN (950, 1300, 800);
-- 최종 쿼리
SELECT ENO, ENAME
FROM EMPLOYEE
WHERE SALARY IN (SELECT MIN(SALARY)
FROM EMPLOYEE
GROUP BY DNO);
서브쿼리 연습문제
-- 07_Subquery_Exam.sql
-- 서브쿼리(자식쿼리) 연습문제
-- 1) (사원번호가(ENO) 7788인 사원)과
-- 담당 업무가(JOB) 같은 사원을(사원이름(ENAME),담당업무(JOB)) 표시하세요.
-- 대상 : EMPLOYEE
-- 1) (사원번호가(ENO) 7788인 사원)
SELECT JOB FROM EMPLOYEE
WHERE ENO = 7788; -- ANALYST
-- 2) ANALYST 와 같은 업무을 가진 사원 출력
SELECT ENAME, JOB FROM EMPLOYEE
WHERE JOB = (SELECT JOB FROM EMPLOYEE
WHERE ENO = 7788);
-- 2-1) (사원번호가(ENO) 7499인 사원)보다
-- 급여가(SALARY) 많은 사원을(사원이름(ENAME),담당업무(JOB)) 표시하세요.
-- 대상 : EMPLOYEE
-- 1) 사원번호가(ENO) 7499인 사원의 급여(SALARY)
SELECT SALARY FROM EMPLOYEE
WHERE ENO = 7499; -- 1600
-- 2-2) 1600 보다 큰 사원의 사원이름(ENAME),담당업무(JOB)를 화면에 표시
SELECT ENAME, JOB FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY FROM EMPLOYEE
WHERE ENO = 7499);
-- 3) (최소(MIN) 급여를(SALARY) 받는 사원)의 이름,(ENAME)
-- 담당 업무(JOB) 및 급여(SALARY)를 표시하세요.
-- 대상 : EMPLOYEE
-- 1) 최소(MIN) 급여를(SALARY) 받는 사원의 급여
SELECT MIN(SALARY) FROM EMPLOYEE; -- 800
-- 2) 800 급여를 받는 이름,(ENAME) , 담당 업무(JOB) 및 급여(SALARY)를 출력
SELECT ENAME, JOB, SALARY FROM EMPLOYEE
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEE);
-- 4) 평균 급여가(SALARY) 가장 적은(MIN) 사원의 담당 업무(JOB)를 찾아
-- 직급과(JOB) 평균(AVG) 급여를(SALARY) 표시하세요.
-- (설명) == (담당 업무별 평균 급여가 가장 적은) 사람을 찾아
-- 직급과 평균 급여를 표시하세요.
-- 단, 소수점 나오면 반올림하기(첫째자리에서)
-- 힌트) 4-1) (담당 업무별(JOB) 평균 급여가 가장 적은) 사람 :
-- (1037.5)
SELECT MIN(ROUND(AVG(SALARY),1))
FROM EMPLOYEE
GROUP BY JOB;
-- 4-2) 그 사람의 직급과 평균급여 화면에 출력 : 그 사람의 직급별(JOB) 평균급여를 화면에 출력
SELECT JOB, ROUND(AVG(SALARY),1)
FROM EMPLOYEE
GROUP BY JOB
HAVING ROUND(AVG(SALARY),1) = (SELECT MIN(ROUND(AVG(SALARY),1))
FROM EMPLOYEE
GROUP BY JOB);
-- 5) (각 부서의 최소(MIN) 급여(SALARY))를 받는
-- 사원의 이름(ENAME), 급여(SALARY), 부서번호(DNO)를 표시하세요.
-- 1) 각 부서의(부서별) 최소(MIN) 급여(SALARY)를 받는 사원의 최소급여
-- (950, 1300, 800)
SELECT MIN(SALARY) FROM EMPLOYEE
GROUP BY DNO;
-- 2) (950, 1300, 800) 급여를(SALARY 받는 사원의
-- 이름(ENAME), 급여(SALARY), 부서번호(DNO)를 표시
SELECT ENAME, SALARY, DNO
FROM EMPLOYEE
WHERE SALARY IN (SELECT MIN(SALARY) FROM EMPLOYEE
GROUP BY DNO);
-- 6) 매니저가(MANAGER) 없는 사원의 이름을(ENAME) 표시하세요.
-- 1) 매니저가(MANAGER) 없는 사원의 사원번호
SELECT ENAME
FROM EMPLOYEE
WHERE MANAGER IS NULL; -- KING(사장님) , 7839
-- 2) 그 사람의 이름을 화면에 출력
SELECT ENAME FROM EMPLOYEE
WHERE ENO = (SELECT ENO FROM EMPLOYEE
WHERE MANAGER IS NULL);
-- 7) 매니저가(MANAGER) 있는 사원의 이름을(ENAME) 표시하세요.
-- 단, 서브쿼리를 이용하세요
-- 사원테이블 : EMPLOYEE
SELECT ENAME
FROM EMPLOYEE
WHERE MANAGER IN (SELECT MANAGER
FROM EMPLOYEE
WHERE MANAGER IS NOT NULL);
-- 8) (BLAKE와 동일한 부서(DNO))에 속한 사원의 이름과(ENAME)
-- 입사일을(HIREDATE) 표시하는 질의를 작성하세요.
-- 단, BLAKE는 제외
-- 사원테이블 : EMPLOYEE
SELECT ENAME, HIREDATE
FROM EMPLOYEE
WHERE DNO = (SELECT DNO
FROM EMPLOYEE
WHERE ENAME = 'BLAKE')
AND ENAME <> 'BLAKE';
-- 9) 급여가(SALARY) (평균(AVG) 급여(SALARY))보다 많은 사원들의
-- 사원번호와(ENO)
-- 이름을(ENAME) 표시하되 결과를 급여(SALARY)에 대해서
-- 오름차순으로 정렬하세요.
-- 사원테이블 : EMPLOYEE
SELECT ENO, ENAME
FROM EMPLOYEE
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEE)
ORDER BY ENO, ENAME;
-- 10) (이름에(ENAME) K가 포함된 사원)과 같은 부서에서(DNO) 일하는 사원의
-- 사원번호와(ENO) 이름을(ENAME) 표시하는
-- 질의를 작성하세요.
-- 사원테이블 : EMPLOYEE
SELECT ENO, ENAME
FROM EMPLOYEE
WHERE DNO IN (SELECT DNO
FROM EMPLOYEE
WHERE ENAME LIKE '%K%');
-- 11) (부서 위치가(LOC) DALLAS인) 사원의 이름과(ENAME)
-- 부서번호(DNO) 및 담당 업무를(JOB) 표시하세요.
-- 단, 서브쿼리를 이용하세요
-- 힌트 : 서브쿼리
SELECT ENAME, DNO, JOB
FROM EMPLOYEE
WHERE DNO = (SELECT DNO
FROM DEPARTMENT
WHERE LOC = 'DALLAS');
-- 12) (KING에게) 보고하는 사원의 이름과(ENAME) 급여를(SALARY) 표시하세요.
-- 힌트 : 보고하는 사원의 매니저가(MANAGER) KING 임(7839)
-- 사원테이블 : EMPLOYEE
SELECT ENAME, SALARY
FROM EMPLOYEE
WHERE MANAGER = (SELECT ENO
FROM EMPLOYEE
WHERE ENAME = 'KING');
-- 13) (RESEARCH 부서(DNO))의 사원에 대한 부서번호(DNO),
-- 사원이름(ENAME) 및 담당 업무(JOB)를 표시하세요.
SELECT DNO, ENAME, JOB
FROM EMPLOYEE
WHERE DNO = (SELECT DNO
FROM DEPARTMENT
WHERE DNAME = 'RESEARCH');
-- 14(*)) 1)평균(AVG) 급여보다(SALARY) 많은 급여를 받고 (서브쿼리1)
-- 2)이름에 M이 포함된 사원과(ENAME) 같은 부서에서(DNO) 근무하는(서브쿼리2)
-- 사원의 사원번호(ENO), 이름(ENAME), 급여(SALARY)를 표시하세요.
SELECT ENO, ENAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEE)
AND DNO IN (SELECT DNO
FROM EMPLOYEE
WHERE ENAME LIKE '%M%');
-- 15) 평균(AVG) 급여가(SALARY) 가장 적은(MIN) 업무를(JOB) 찾으세요.
-- 출력은 담당업무(JOB), 평균(AVG)급여(SALARY) 출력하세요
-- 설명) 담당 업무별(JOB) 평균(AVG) 급여를(SALARY) 찾아서
-- 그중에서 가장 평균 급여가 적은(MIN) 업무를 찾으면 됨
SELECT JOB, AVG(SALARY)
FROM EMPLOYEE
GROUP BY JOB
HAVING AVG(SALARY) = (SELECT MIN(AVG(SALARY))
FROM EMPLOYEE
GROUP BY JOB);
-- 16) 전체 사원 중 ALLEN과 같은 직위(JOB)인 사원들의
-- 직위(JOB), 사원번호(ENO), 사원명(ENAME), 급여(SALARY),
-- 부서번호(DNO), 부서명(DNAME) 출력하는 SQL문을 작성하세요
-- 힌트) 조인 + 서브쿼리
SELECT EMP.JOB, EMP.ENO, EMP.ENAME, EMP.SALARY, EMP.DNO, DEP.DNAME
FROM EMPLOYEE EMP, DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND JOB = (SELECT JOB
FROM EMPLOYEE
WHERE ENAME = 'ALLEN');
-- 17) 10번 부서에 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원들의
-- 사원번호(ENO), 사원명(ENAME), 직위(JOB), 부서번호(DNO), 부서명(DNAME),
-- 부서위치를(LOC) 출력하세요
-- 힌트) 조인 + 서브쿼리
SELECT JOB
FROM EMPLOYEE
WHERE DNO = '30';
SELECT EMP.ENO, EMP.ENAME, EMP.JOB, EMP.DNO, DEP.DNAME
FROM EMPLOYEE EMP, DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND JOB IN (SELECT JOB
FROM EMPLOYEE
WHERE DNO = 10)
AND EMP.JOB NOT IN (SELECT DISTINCT JOB FROM EMPLOYEE
WHERE DNO = 30);
SELECT EMP.ENO, EMP.ENAME, EMP.JOB, EMP.DNO, DEP.DNAME
FROM EMPLOYEE EMP
,DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO
AND EMP.DNO = 10
AND EMP.JOB NOT IN (SELECT DISTINCT JOB FROM EMPLOYEE
WHERE DNO = 30);
'HTML > SQL' 카테고리의 다른 글
[SQL] 테이블 (0) | 2023.09.13 |
---|---|
[SQL] DDL, DML, DCL (0) | 2023.09.13 |
[SQL] 그룹 함수, 조인 (0) | 2023.09.11 |
오라클 내장함수 모음 (0) | 2023.09.08 |
SQL 실습(SELECT) (비교 연산자 , 대입 연산자 , 정렬 , OR, AND, IN( ) , BETWEEN A AND B) (0) | 2023.09.07 |