멀티캠퍼스 백엔드 과정 26일차[7월 7일] -서브쿼리

GoldenDusk·2023년 7월 12일

멀티캠퍼스 백엔드 과정

목록 보기

21/60

예제

-- 1)
SELECT *
FROM EMP;

SELECT EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE ASC;

2) 
SELECT ENAME, SAL * 12 AS 연봉, HIREDATE
FROM EMP
ORDER BY SAL * 12 DESC;

3) 
SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO IN(10, 20)
ORDER BY ENAME ASC;

4)
SELECT ENAME, SAL, COMM
FROM EMP
WHERE COMM IS NOT NULL
ORDER BY COMM DESC;

5) 
SELECT DISTINCT INITCAP(JOB)
FROM EMP
GROUP BY JOB;

6) 
SELECT SUBSTR(ENAME, 1, 3)
FROM EMP
WHERE ENAME LIKE '%A%';

7) 
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '__A%'

8)
SELECT INITCAP(ENAME) AS name, LENGTH(ENAME) AS length
FROM EMP
WHERE ENAME LIKE 'J%' OR ENAME LIKE 'A%' OR ENAME LIKE 'M%';

9)
SELECT LOWER(ENAME)
FROM EMP
WHERE LENGTH(ENAME) >=6;

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

11)
SELECT ENAME, LPAD(SAL, 15, "$") AS SALARY
FROM EMP;

12)


13)
SELECT EMPNO, ENAME, SAL, ROUND(SAL*(SAL*0.15)) AS "NEW Salary"
FROM EMP;

14)
SELECT ENAME, MOD(SAL, 4)
FROM EMP;

서브쿼리

SELECT 속의 SELECT 속의 SELECT

1. 서브쿼리의 형태

SELECT 
FROM 테이블 메인쿼리
WHERE 조건식(서브쿼리1 - 실행순서3
SELECT 
FROM 테이블 -실행순서2
WHERE 조건식(서브쿼리2 -실행순서 1

)
)

-- 실습 1 사원이름 'JONES' 사원의 급여보다 더 높은 급여를 받는 사원의 이름과 급여 출력
-- 1. SELECT1 와
SELECT SAL
FROM EMP
WHERE ENAME='JONES'; --2975

DESC EMP;
-- 2.  SELECT2
SELECT ENAME,SAL
FROM EMP
WHERE SAL>2975;

-- 3. 합쳐서 최종
SELECT ENAME,SAL
FROM EMP
WHERE SAL>(SELECT SAL
FROM EMP
WHERE ENAME='JONES');

-- EMP 테이블의 사원정보 중 사원이름이 ALLEN 사원의 추가 수당보다 많은 추가 수당을 받는 사원의 이름과 수당를 
-- 출력하세요
SELECT ENAME, COMM, JOB
FROM EMP
WHERE ENAME = 'ALLEN'; --300

SELECT ENAME, COMM, JOB
FROM EMP
WHERE COMM > 300;

SELECT ENAME, COMM, JOB
FROM EMP
WHERE COMM > (
    SELECT COMM -- 앞과 맞춰줘야 함
    FROM EMP
    WHERE ENAME = 'ALLEN');

2. 실행 결과가 하나인 당일행 서브쿼리

단일행 연산자 : 실행결과가 단 하나의 행으로 나오는 서브쿼리는 > >= = <= < (<>, ^=, !=)

--2) (BLAKE와 같은 부서)에 있는 사원들의 이름과 입사일을 구하는데 BLAKE는 제외하고 출력하시오.(BLAKE가 여러명일 수 있음)
-- 1. 우리는 어느 부서에서 일하고 있는지 알아내야 한다. DEPTNO(부서번호)
-- 2. 서로 다른 부서번호 BLAKE를 갖는 여러명 일 수 있다라 했으므로 IN

SELECT ENAME, HIREDATE
FROM EMP
WHERE ENAME <> 'BLAKE' AND DEPTNO IN(
    SELECT DEPTNO
    FROM EMP
    WHERE ENAME='BLAKE'
);

--3) (평균급여)보다 많은 급여를 받는 사원들의 사원번호, 이름, 월급을 출력하는데 월급이 높은 사람순으로 출력하시오.
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (
SELECT AVG(SAL)
FROM EMP
)
ORDER BY SAL DESC;

DESC EMP;
 
--4) (10번부서에서 급여를 가장 적게) 받는 사원과 동일한 급여를 받는 사원의 이름을 출력하시오.
SELECT ENAME
FROM EMP
WHERE SAL =(
SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO=10);
 
--5) 사원수가 3명이 넘는 부서의 부서명과 사원수를 출력하시오. -- 부서가 나오면 GROUP BY
SELECT d.dname 부서명, COUNT(*) 사원수
FROM EMP e, DEPT d
WHERE e.deptno=d.deptno
GROUP BY d.dname
HAVING COUNT(*) > 3;

 
--6) (사원번호가 7844인 사원보다 빨리 입사)한 사원의 이름과 입사일을 출력하시오. -- 빨리 입사 => 작아야함
 SELECT ENAME, HIREDATE
 FROM EMP
 WHERE HIREDATE < (
 SELECT HIREDATE
 FROM EMP
 WHERE EMPNO =7844
 );
 
 
--7) 직속상사(mgr)가 (KING)인 모든 사원의 이름과 급여를 출력하시오.
SELECT ENAME, SAL
FROM EMP
WHERE MGR IN (
SELECT EMPNO -- 왜 사원번호를 넣는걸까..? 사원번호를 반환해야 MGR과 비교 가능해서
FROM EMP
WHERE ENAME='KING');
 
--8) 20번 부서에서 가장 급여를 많이 받는 사원과 동일한 급여를 받는 사원의 이름과 부서명,급여, 급여등급을 출력하시오.
-- 여러 개의 테이블이구나 => 조인해야 겠구나[비등가조인]
SELECT e.ENAME, d.dname, e.sal, s.grade
FROM EMP e, DEPT d, SALGRADE s
WHERE sal = (select max(sal) from emp where deptno = 20) and e.deptno=d.deptno and sal between s.losal and s.hisal;

3. 실행 결과가 여러 개인 다중행 서브쿼리

다중행 연산자 : IN, ANY, SOME, ALL

-- 다중행 서브쿼리 연산자 IN : 메인쿼리의 데이터가 서브쿼리 결과 중 하나라도 일치한 데이터가 있다면 TRUE 
SELECT *
FROM EMP
WHERE DEPTNO IN (20,30);

-- 확장, 각 부서별 최고 급여와 동일한 급여를 받는 사원 정보를 출력하세요
-- 부서니까 GROUP BY
-- 1. 각 부서별 최고 급여 구하기
SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY deptno
ORDER BY DEPTNO;

SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
GROUP BY deptno); -- IN 하나라도 맞는게 있다면 다 가져와라

-- ANY, SOME : 메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 TURE
-- = ANY(=IN)
SELECT *
FROM EMP
WHERE SAL = ANY(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

SELECT *
FROM EMP
WHERE SAL = SOME(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

-- 30번 부서 사원들의 최대 급여 보다 적은 급여를 받는 사원들의 정보 출력
SELECT *
FROM EMP
WHERE SAL < ANY(SELECT SAL FROM EMP WHERE DEPTNO=30); -- ANY 연산자 서브쿼리 결과 값 중 급여의 최댓값(2850)보다 작은값 모두 출력

-- 30번 부서 사원들의 최소 급여 보다 많은 급여를 받는 사원들의 정보 출력
SELECT *
FROM EMP
WHERE SAL > ANY(SELECT SAL FROM EMP WHERE DEPTNO=30);
 
-- ALL : 메인쿼리의 조건식을 서브쿼리의 결과가 모두 만족 시 TRUE
-- 부서번호가 30번인 사원들의 최소급여보다 더 적은 급여를 받는 사원정보 출력
SELECT *
FROM EMP
WHERE SAL < ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);

-- 30번 부서 사원들의 최대 급여 보다 적은 급여를 받는 사원들의 정보 출력
SELECT *
FROM EMP
WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO=30); -- ALL 최댓값 초과값을 가져올 때

-- EXISTS 연산자 : 서브쿼리에 결과값이 하나 이상 존재하면 조건식이 모두 true, 존재하지 않으면 false
SELECT *
FROM DEPT
WHERE DEPTNO = 10;

SELECT *
FROM EMP
WHERE EXISTS (SELECT *
FROM DEPT
WHERE DEPTNO = 50); -- 존재하니 안하니?

-- EXISTS 연산자 : 서브쿼리에 결과값이 하나 이상 존재하면 조건식이 모두 true, 존재하지 않으면 false
SELECT *
FROM DEPT
WHERE DEPTNO = 10;

SELECT *
FROM EMP
WHERE EXISTS (SELECT *
FROM DEPT
WHERE DEPTNO = 50); -- 존재하니 안하니

-- 서브쿼리를 이용해서 EMP테이블의 사원 중 10번 부서에 속한 모든 사원들보다 일찍 입사한 사원 정보를 모두 출력하세요
SELECT hiredate
FROM EMP
WHERE DEPTNO = 10;

SELECT *
FROM EMP
WHERE hiredate < ANY(SELECT hiredate
FROM EMP
WHERE DEPTNO = 10);

4. 비교할 열이 여러 개인 다중열 서브쿼리

-- 비교할 열이 여러 개인 다중열 서브쿼리(실무에서 많이 사용)

-- 실습 9-18
SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO;

5. FROM 절에 사용하는 서브쿼리와 WITH 절

-- 인라인뷰 중요***** FROM절에서 사용하는 서브쿼리를 인라인 뷰라고 한다.
SELECT e10.empno, e10.ename, d.deptno, d.dname, d.loc
FROM (SELECT * FROM EMP WHERE DEPTNO=10) e10, (SELECT *FROM DEPT) d
WHERE e10.deptno = d.deptno;

-- 실습 9-20) 260
WITH
E10 AS(SELECT * FROM EMP WHERE DEPTNO = 10),
D   AS(SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM E10,D
WHERE E10.DEPTNO=D.DEPTNO;

6. SELECT 절에 사용하는 서브쿼리

-where앞에 붙는 속성을 잘 못 붙히는 거 연습

--- 1.
SELECT e.JOB, e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.DNAME
FROM EMP e, DEPT d
WHERE job=(select job from emp where ename='ALLEN') AND e.deptno = d.deptno;

-- 2. AND e.sal BETWEEN s.losal AND s.hisal 연결필요
SELECT e.JOB, 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 e.sal BETWEEN s.losal AND s.hisal AND e.SAL > ANY(SELECT ROUND(AVG(SAL)) FROM EMP)
ORDER BY e.SAL DESC, e.EMPNO ASC;

-- 3.
SELECT e.empno, e.ename, 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 );

-- 4.
SELECT e.EMPNO, e.JOB, e.sal, s.grade
FROM EMP e, SALGRADE s
WHERE e.sal BETWEEN s.losal AND s.hisal
      AND SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN');

--9) 총급여(sal+comm)가 평균 급여보다 많은 급여를 받는 사람의 
--부서번호, 이름, 총급여, 커미션을 출력하시오.(커미션은 유(O),무(X)로 표시하고 컬럼명은 "comm유무" 출력)
-- NVL(COMM, 0) : NULL을 만나면 0으로 치환해라
SELECT COMM FROM EMP;
SELECT DEPTNO 부서번호, ENAME 이름, SAL+NVL(COMM,0) 총급여, NVL2(COMM, '유(O)', '무(X)') comm유무
FROM EMP
WHERE (SAL+NVL(COMM, 0)) > (SELECT AVG(SAL) FROM EMP);

SELECT AVG(SAL) FROM EMP;
 
--10) CHICAGO 지역에서 근무하는 사원의 평균 급여보다 높은 급여를 받는 사원의 이름과 급여, 지역명을 출력하시오.
SELECT *
FROM DEPT;

-- 1. 시카고 지역번호
SELECT DEPTNO
FROM DEPT
WHERE LOC='CHICAGO';

-- 2. 평균급여
SELECT ROUND(AVG(SAL))
FROM EMP;

-- 3. SAL 비교 메인 실행
SELECT e.ename, e.sal, d.loc
FROM EMP e, DEPT d
WHERE SAL >(
    SELECT ROUND(AVG(SAL)) FROM EMP WHERE DEPTNO=(
    SELECT DEPTNO
    FROM DEPT
    WHERE LOC='CHICAGO'
    )
)AND e.deptno = d.deptno; -- 조인으로 중복됨을 빼줌
 
--11) 업무가 SALESMAN인 직원이 2명 이상인 부서의 이름, 근무하는 사원의 이름, 업무를 출력하시오.(컬럼명은 부서명, 사원명, 업무로 출력)
-- 그룹별 deptno 그룹에 대한 조건 직원의 수가 2명 이상
-- 테이블에 대한 조건 job = 'SALESMAN'
-- 정렬 조건 : 부서명, 사원명, 업무
SELECT d.DNAME, e.ENAME, e.JOB
FROM EMP e, DEPT d
WHERE e.deptno IN(
SELECT deptno
FROM emp
WHERE job = 'SALESMAN' 
GROUP BY deptno
HAVING COUNT(*) >= 2
) AND e.deptno = d.deptno;

SELECT *
FROM DEPT;

SELECT *
FROM EMP;

--12) 커미션(COMM)이 없는 사원들 중 월급이 가장 높은 사원의 이름과 급여등급을 출력하시오.
-- 해당 데이터 테이블 : EMP, SALEGRADE
SELECT e.ename, s.grade
FROM EMP e, SALGRADE s
WHERE sal = (
    SELECT MAX(SAL) FROM EMP WHERE comm is null or comm=0
) AND e.SAL BETWEEN s.losal and s.hisal;
 
--13) SMITH의 관리자(mgr)의 이름과 부서명, 근무지역을 출력하시오.
SELECT e.ename, d.dname, d.loc
FROM EMP e, DEPT d
WHERE  e.deptno = d.deptno and e.empno = ( -- where앞에 붙는 속성을 잘 못 붙히겠네
    SELECT mgr FROM EMP WHERE ename='SMITH'
);

기억해야 할 키워드

서브쿼리
단일행
다중행
WITH절 사용방법
다중행 서브쿼리(IN, ANY, SOME, ALL)

10장 DML (추가, 수정, 삭제)

-- 테이블을 생성
-- 그대로 적용해서 생성해라
CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT; 
DESC DEPT_TEMP;

SELECT * FROM DEPT_TEMP;

INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (50, 'IT', 'SEOUL');
INSERT INTO DEPT VALUES (60, 'COMPUTER', 'BUSAN');
COMMIT;
SELECT * FROM DEPT;

SELECT e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

-- emp 테이블에 데이터 넣어보자, 마이그레이션
CREATE TABLE EMP_TEMP AS SELECT * FROM EMP WHERE 1<>1; -- 구조만 가지고 온 것
SELECT * FROM EMP_TEMP;
INSERT INTO EMP_TEMP VALUES(7577, '이금주', 'computer', 7902, SYSDATE, 400, null, 60);
INSERT INTO EMP_TEMP VALUES(7878, '이승민', 'CTO', 7878, '2001-06-04', 7000, 1000, 20);
INSERT INTO EMP_TEMP VALUES(1212, '김정주', 'MANAGER', 1111, TO_DATE('09/04/01', 'DD/MM/YYYY'), 3000, NULL, 30);

-- 서브쿼리를 이용한 선택적 데이터 입력 방법(VALUES문이 없음), 컬럼의 개수 꼭 맞춰주기
INSERT INTO EMP_TEMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
        SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
            FROM EMP E, SALGRADE S
                WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND S.GRADE=1;
                                
COMMIT;
SELECT * FROM EMP_TEMP;

SELECT ENAME, D.DEPTNO, DNAME, LOC
FROM EMP_TEMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

UPDATE

-- 테이블에 있는 데이터 수정
-- UPDATE 변경할 테이블 이름 SET 컬럼명 = 데이터, ..., .. WHERE (선택요소)

-- EMP_TEMP 테이블에서 EMPNO 7900 사원의 급여를 1000으로 변경하세요.
-- 항상 현재 상태 데이터 정보 확인!
UPDATE EMP_TEMP
SET SAL=1000
WHERE EMPNO=7900;

COMMIT;

-- EMP_TEMP 테이블에서 EMPNO 7521[7521이 없어 7577을 변경] 사원의 급여와 직무를 3000, CLERK으로 변경
UPDATE EMP_TEMP
SET SAL=3000, JOB='CLERK'
WHERE EMPNO=7577;

ROLLBACK; --DBMS의 쿼리해석기 실행하는 작업단위 : 트랜잭션 

-- 서브쿼리를 이용하여 데이터 수정
CREATE TABLE DEPT_TEMP1 AS SELECT * FROM DEPT;

SELECT * FROM DEPT WHERE DEPTNO = 40;

UPDATE DEPT_TEMP1
    SET(DNAME, LOC) = (SELECT DNAME, LOC FROM DEPT WHERE DEPTNO=50) WHERE DEPTNO = 40;

-- 풀어쓰면 이런 느낌
UPDATE DEPT_TEMP1
    SET DNAME = (SELECT DNAME FROM DEPT WHERE DEPTNO=60),
        LOC =(SELECT LOC FROM DEPT WHERE DEPTNO=60)
    WHERE DEPTNO = 40;

DELETE

-- 데이터 삭제
DELETE FROM DEPT_TEMP;

ROLLBACK;

SELECT *
FROM DEPT_TEMP1;

회고

음... 서브쿼리 구조는 알겠는데 쓸 때 너무 헤갈린다. 쓸 때 원하는 것이 무엇인지 두 번 세 번 고민하도록 노력해보자 그리고 예제 받은 것 좀 연습해보기! 요즘 잘 하는 애들보면 좀 걱정되고 현타오는데 그래도 이겨내자!!
이 길 만큼 내가 재미있게 할 수 있는 길이 별로 없을 것 같다는 생각도 같이 들기 때문에 나보다 잘 하는 사람이 많아도 내가 할 수 있는 선에서 노력하자