오라클 데이터 베이스 서브쿼리

Notice

Recent Posts

Recent Comments

Link

« 2024/11 »
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

Tags more

Archives

Today

Total

관리 메뉴

gwooden_코린이

오라클 데이터 베이스 서브쿼리 본문

데이터베이스

오라클 데이터 베이스 서브쿼리

gwooden22 2023. 1. 11. 18:09

728x90

1. 오라클 서브쿼리

SQL문 안에 SELECT를 작성

(SELECT ~~~ FROM ~~ WHERE ~~~)

서브쿼리는 반드시 괄호안에 작업해야 된다.

DEPT 테이블에서 사원이름(EMP테이블)이 KING인 부서명을 출력하고 싶을때

SELECT DNAME FROM DEPT WHERE ????

DEPTNO = 10 ???

사원이름(EMP테이블)이 KING에 부서번호를 먼저 뽑아낸다.

SELECT DNAME FROM DEPT WHERE ????

1. EMP 테이블에서 이름이 KING인 부서번호를 검색

2. DEPT 테이블에 부서번호랑 KING인 부서번호가 같은거를 찾아라

SELECT DNAME 
    FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO --KING인 부서번호
                    FROM EMP
                WHERE ENAME = 'KING');

- 문제

--급여가 CLARK인 급여보다 큰사람들을 조회
--EMP 테이블
SELECT *
    FROM EMP
WHERE SAL > (SELECT SAL
                FROM EMP
            WHERE ENAME = 'CLARK');

SELECT *
    FROM EMP
WHERE HIREDATE < (SELECT HIREDATE
                        FROM EMP
                    WHERE ENAME = 'BLAKE')
                    ORDER BY HIREDATE;

--급여가 총급여평균보다 큰 사람들의 레코드를 조회
SELECT *
    FROM EMP
WHERE SAL > (SELECT AVG(SAL)
                    FROM EMP)
                    ORDER BY SAL;

- 단일행 서브쿼리

서브쿼리에서 뽑아낼때 결과물이 딱 하나만
비교연산자는 >, <, =, <=, >=, !=
결과가 여러개 나올 경우 출력 오류

(SELECT AVG(SAL) FROM EMP)

(SELECT HIREDATE FROM EMP WHERE ENAME = 'BLAKE')

(SELECT SAL FROM EMP WHERE ENAME = 'CLARK')

- 다중행 서브쿼리

서브쿼리에서 뽑아낼때 결과물을 여러개 뽑아내어 작업

IN (OR 같은거)

하나라도 만족하는 TRUE (일치)

--EMP 테이블에서 직책별 급여가 가장 큰 사람들의 레코드를 조회
SELECT *
    FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY JOB);

ANY

하나라도 만족하는 TRUE (일치) (모든 비교연산자 가능)

--직책이 SALESMAN인 급여보다 큰사람들을 조회
SELECT *
    FROM EMP
WHERE JOB = 'SALESMAN';

SELECT *
    FROM EMP
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');

--부서 번호가 30번인 사람들의 급여보다 작은 급여를 받은 사람들을 조회

SELECT *
    FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30);

ALL

모두 만족하는 경우만 TRUE

SELECT *
    FROM EMP
WHERE SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30);

EXISTS

서브쿼리의 결과 값이 하나라도 있으면 TRUE

서브쿼리의 결과 값이 하나도 없으면 FALSE

SELECT *
    FROM EMP
WHERE EXISTS (SELECT ENAME FROM EMP WHERE DEPTNO = 20)
    AND SAL>=2000;

열 맞춰주고 컬럼명 달라도 됨

SELECT *
    FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL
                                FROM EMP
                            WHERE DEPTNO=30);

--부서번호가 10인 데이터들 중에서 검색을 시작
SELECT *
    FROM (SELECT * FROM EMP WHERE DEPTNO=10)
WHERE SAL >= 2000;

--부서번호가 10인 데이터들 중에서 검색을 시작
SELECT *
    FROM (SELECT * FROM EMP WHERE DEPTNO=10) E,
            (SELECT * FROM DEPT) D
WHERE SAL >= 2000 AND E.DEPTNO = D.DEPTNO;

--ROWNUM 행 번호를 출력해주는 명령어
SELECT ROWNUM, ENAME, SAL
    FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM<=3;

- 문제

--EMP, DEPT테이블
--사원중에 ALLEN과 같은 직책인 사람달의
--직책, 사원번호, 이름, 급여, 부서번호, 부서이름을 출력

--INNER JOIN으로 처리 1
SELECT EMP.JOB, EMP.EMPNO, EMP.ENAME, EMP.SAL, EMP.DEPTNO, DEPT.DNAME
    FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
    WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME='ALLEN');
    
--INNER JOIN으로 처리 2
SELECT EMP.JOB, EMP.EMPNO, EMP.ENAME, EMP.SAL, EMP.DEPTNO, DEPT.DNAME
    FROM EMP INNER JOIN DEPT
ON EMP.JOB = (SELECT JOB FROM EMP WHERE ENAME='ALLEN')
    AND EMP.DEPTNO = DEPT.DEPTNO;

--JOIN 없이 조건으로 처리했을때    
SELECT EMP.JOB, EMP.EMPNO, EMP.ENAME, EMP.SAL, EMP.DEPTNO, DEPT.DNAME
    FROM EMP, DEPT
WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME='ALLEN')
    AND EMP.DEPTNO = DEPT.DEPTNO;

--EMP, SALGRADE 테이블
--사원번호, 이름, 급여, 급여등급 출력

--JOIN 없이
SELECT EMP.EMPNO, EMP.ENAME, EMP.SAL, SALGRADE.GRADE
    FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;

--JOIN 사용
SELECT EMP.EMPNO, EMP.ENAME, EMP.SAL, SALGRADE.GRADE
    FROM EMP INNER JOIN SALGRADE
ON SAL BETWEEN LOSAL AND HISAL;

--부서번호가 10번인 사람들 중에서
--부서번호가 30번인 부서에 없는 직책을 가진 사람들의
--사원번호, 이름, 직책, 부서번호, 부서명, 부서위치를 출력

SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.DEPTNO, DEPT.DNAME, DEPT.LOC
    FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
AND EMP.DEPTNO = 10
AND JOB NOT IN(SELECT DISTINCT JOB
                        FROM EMP
                    WHERE DEPTNO = 30);

--직책이 SALESMAN인 사람들의 최고급여보다 큰 급여를 받는 사람들의
--이름, 직책, 급여, 급여등급을 출력

-- MAX를 이용한
SELECT EMP.ENAME, EMP.JOB, EMP.SAL, SALGRADE.GRADE
    FROM EMP INNER JOIN SALGRADE
    ON SAL BETWEEN LOSAL AND HISAL
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY SAL DESC;

-- ALL을 이용한 (SAL에 값이 여러개라 모두 비교해야 되니)
SELECT EMP.ENAME, EMP.JOB, EMP.SAL, SALGRADE.GRADE
    FROM EMP INNER JOIN SALGRADE
    ON SAL BETWEEN LOSAL AND HISAL
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY SAL DESC;

- 문제02

--1. 사원번호가 7499인 사원의 이름, 입사일 부서번호 출력
SELECT ENAME, HIREDATE, DEPTNO
    FROM EMP
WHERE EMPNO = 7499;

--2. 이름이 ALLEN인 사원의 모든 정보 출력
SELECT *
    FROM EMP
WHERE ENAME = 'ALLEN';

--3. 이름이 K보다 큰 글자로 시작하는 사원의 모든 정보 출력
SELECT *
    FROM EMP
WHERE ENAME > 'K';


--4. 입사일이 81년 4월2일 보다 늦고, 82년 12월9일 보다 빠른 사원의 이름, 급여, 부서번호 출력

SELECT ENAME, SAL, DEPTNO, HIREDATE
    FROM EMP
WHERE HIREDATE > '81/04/02' AND HIREDATE < '82/12/09';


--5. 급여가 1,600 보다 크고, 3000보다 작은 사원의 이름, 직무, 급여를 출력

SELECT ENAME, JOB, SAL
    FROM EMP
WHERE SAL > 1600 AND SAL < 3000;


--6. 입사일이 81년 이외에 입사한 사원의 모든 정보 출력

SELECT *
    FROM emp
WHERE TO_CHAR(HIREDATE,'YYYY')!='1981';


--7. 직업이 MANAGER와 SALESMAN인 사원의 모든 정보를 출력

SELECT *
    FROM EMP
WHERE JOB = 'MANAGER' OR JOB = 'SALESMAN';
--WHERE JOB IN('MANAGER', 'SALESMAN');

--8. 부서가 20번, 30번을 제외한 모든 사원의 이름, 사원번호, 부서번호를 출력

SELECT ENAME, EMPNO, DEPTNO
    FROM EMP
WHERE DEPTNO != 20 AND DEPTNO != 30;
--WHERE DEPTNO NOT IN(20, 30);

--9. 이름이 S로 시작하는 사원의 사원번호, 이름, 부서번호 출력
SELECT EMPNO, ENAME, DEPTNO
    FROM EMP
WHERE ENAME LIKE 'S%';


--10. 처음 글자는 관계없고, 두 번째 글자가 A인 사원의 모든 정보를 출력
SELECT *
    FROM EMP
WHERE ENAME LIKE '_A%';

--11. 커미션이 NULL이 아닌 사원의 모든 정보를 출력
SELECT *
    FROM EMP
WHERE COMM IS NOT NULL;

--12. 이름이 J자로 시작하고 마지막 글자가 S인 사원의 모든 정보를 출력
SELECT *
    FROM EMP
WHERE ENAME LIKE 'J%' AND ENAME LIKE '%S';
--HERE ENAME LIKE 'J%S';


--13. 급여가 1500이상이고, 부서번호가 30번인 사원 중 직무가 MANAGER인 사원의 모든
--정보 출력
SELECT *
    FROM EMP
WHERE SAL > 1500
AND DEPTNO = 30
AND JOB = 'MANAGER';


-- 14. 모든 사원의 이름, 급여, 커미션, 총액(급여+커미션)을 구하여 총액이 많은 순서로 출력
--(단, 커미션이 null인 사원도 0으로 포함)

SELECT ENAME, SAL, NVL(COMM, 0), SAL + NVL(COMM, 0) AS 총액
    FROM EMP
ORDER BY 총액 DESC;
    
    
--15. 10번 부서의 모든 사원에게 급여의 13%를 보너스로 지불하기로 하였다. 10번 부서 사
--원들의 이름, 급여, 보너스, 부서번호 출력

SELECT ENAME, SAL, SAL * 0.13 AS 보너스, DEPTNO
    FROM EMP
WHERE DEPTNO = 10;

--16. 모든 사원에 대해 입사한 날로 부터 60일이 지난 후의 ‘월요일’에 대한 년,월,일를 구하
--여 이름, 입사일, 60일 후의 ‘월요일’ 날짜를 출력

SELECT ENAME, HIREDATE,
    TO_CHAR(NEXT_DAY(HIREDATE + 60, '월요일'), 'YYYY/MM/DD') AS 수습종료일
    FROM EMP;

--17. 이름의 글자수가 6자 이상인 사원의 이름을 앞에서 3자만 구하여 소문자로 이름만 출력

SELECT LOWER(SUBSTR(ENAME, 1, 3))
    FROM EMP
WHERE LENGTH(ENAME) >= 6;

--18. 사원들의 사원 번호와 급여, 커미션, 연봉((comm+sal)*12)을 연봉이 많은 순서로 출력

SELECT EMPNO, SAL, NVL(COMM, 0) AS 커미션, (NVL(COMM, 0) + SAL) * 12 AS 연봉
    FROM EMP
ORDER BY 연봉 DESC;

--19. 모든 사원들의 입사한 년/월/일
--(예, 1981년5월30일)

SELECT TO_CHAR(HIREDATE, 'YYYY"년"MM"월"DD"일"')
    FROM EMP;
    
    
--20. 10번 부서에 대해 급여의 평균 값, 최대 값, 최소 값, 인원 수를 출력

SELECT AVG(SAL), MAX(SAL), COUNT(*)
    FROM EMP
WHERE DEPTNO = 10;

--21. 사원번호가 짝수인 사원들의 모든 정보를 출력

SELECT *
    FROM EMP
WHERE MOD(EMPNO, 2) = 0;

--22. 각 부서별 같은 직무를 갖는 사원의 인원수를 구하여 부서 번호, 직무, 인원수 출력

SELECT DEPTNO, JOB, COUNT(*)
    FROM EMP
GROUP BY DEPTNO, JOB;

--23. EMP와 DEPT테이블을 조인하여 모든 사원에 대해 부서 번호, 부서이름, 사원이름 급
--여를 출력

SELECT EMP.DEPTNO, DNAME, ENAME, SAL
    FROM EMP INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO;


--24. 이름이 ‘ALLEN’인 사원의 부서 번호, 부서 이름, 사원 이름, 급여 출력

SELECT EMP.DEPTNO, DNAME, ENAME, SAL
    FROM EMP INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO
WHERE ENAME = 'ALLEN';

--25. ‘ALLEN’과 직무가 같은 사원의 이름, 부서 이름, 급여, 부서위치를 출력

SELECT ENAME, DNAME, SAL, LOC
    FROM EMP INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO
WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN');

--26. 모든 사원들의 평균 급여 보다 많이 받는 사원들의 사원번호와 이름 출력

SELECT EMPNO, ENAME
    FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP);


--27. 부서별 평균 급여가 2000보다 적은 부서 사원들의 부서 번호 출력

SELECT DEPTNO
    FROM EMP
GROUP BY DEPTNO
    HAVING AVG(SAL) < 2000;

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

SELECT EMPNO, JOB, SAL
    FROM EMP
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);

--29. ‘FORD’와 부서가 같은 사원들의 이름, 부서 이름, 직무, 급여를 출력

SELECT ENAME, DNAME, JOB, SAL
    FROM EMP INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO
WHERE EMP.DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME='FORD');

--30. 부서 이름이 ‘SALES’인 사원들의 평균 급여 보다 많고, 부서 이름이 ‘RESEARCH’인
--사원들의 평균 급여 보다 적은 사원들의 이름, 부서번호, 급여, 직무 출력

SELECT ENAME, DEPTNO, SAL, JOB
    FROM EMP
WHERE SAL > (SELECT AVG(SAL)
                    FROM EMP INNER JOIN DEPT
                    ON EMP.DEPTNO = DEPT.DEPTNO
                WHERE DNAME = 'SALES')
    AND SAL < (SELECT AVG(SAL)
                    FROM EMP INNER JOIN DEPT
                    ON EMP.DEPTNO = DEPT.DEPTNO
                WHERE DNAME = 'RESEARCH');

728x90

저작자표시 비영리 변경금지

'데이터베이스' 카테고리의 다른 글

오라클 데이터 베이스 DELETE 삭제 (0)	2023.01.13
오라클 데이터 베이스 INSERT 삽입문 (0)	2023.01.13
오라클 데이터 베이스 집합 연산자 (0)	2023.01.11
오라클 데이터 베이스 정규화/정규형 (0)	2023.01.11
오라클 데이터베이스 함수다뤄보기 (0)	2023.01.10

'데이터베이스' Related Articles

Comments

gwooden_코린이

오라클 데이터 베이스 서브쿼리 본문

오라클 데이터 베이스 서브쿼리

1. 오라클 서브쿼리

- 문제

- 단일행 서브쿼리

- 다중행 서브쿼리

IN (OR 같은거)

ANY

ALL

EXISTS

- 문제

- 문제02

'데이터베이스' 카테고리의 다른 글

티스토리툴바