[SQL 기본쿼리] 연습문제 4

📢 테이블 준비

https://dorothy-yang.tistory.com/191

[SQL 기본쿼리] 연습문제 테이블 생성 준비

 

---

🕵️‍♂️ 문제 풀이 및 해답

-- 사용문법이 대부분 JOIN, SUB QUERY 문법위주 연습.
-- EMP e : EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
-- DEPT d : DEPTNO, DNAME 부서명, LOC 지역
-- FROM EMP e, DEPT d
-- WHERE e.DEPTNO = d.DEPTNO 같은 부서

-- 1. 최소급여를 받는 사원과 같은 부서에서 근무하는 모든 사원명, 부서명을 출력
    SELECT ENAME, DNAME
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND e.DEPTNO = (SELECT DEPTNO FROM EMP WHERE SAL 
                    = (SELECT MIN(SAL) FROM EMP));
                
-- 2. SCOTT보다 입사일이 늦은 사원과 같은 부서에서 / 근무하는 사원들의 부서명, 이름, 급여를 출력
    SELECT DNAME, ENAME, SAL
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND e.DEPTNO = (SELECT DEPTNO FROM EMP WHERE HIREDATE 
                    > (SELECT HIREDATE FROM EMP WHERE ENAME = 'SCOTT'));
                
-- 3. 이름에 'K'자가 들어가는 사원들 중 급여가 가장 적은 사원의 부서명, 사원명, 급여를 출력   
    SELECT DNAME, ENAME, SAL
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND SAL = (SELECT MIN(SAL) FROM EMP WHERE ENAME LIKE '%K%');
                                
-- 4. 커미션 계약이 없는 사원중 입사일이 가장 빠른 사원의 /부서명, 사원명, 입사일을 출력
    SELECT DNAME, ENAME, HIREDATE
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND HIREDATE = (SELECT MIN(HIREDATE) FROM EMP WHERE COMM IS NULL);
                                    
-- 5. 위치가 시카고인 부서에 속한 사원들의 이름과 부서명을 출력.
    SELECT ENAME, DNAME
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND e.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'CHICAGO');
                            
-- 6. KING의 급여에서 SCOTT의 급여를 뺀 결과보다 적은 급여를 받는 사원의 부서명, 이름, 급여를 출력
-- SAL 내용만 고려했음.. **FROM DUAL : 테이블 생성할 필요 없이!!
    SELECT DNAME, ENAME, SAL
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND SAL < (SELECT(SELECT SAL FROM EMP WHERE NAME = 'KING') - (SELECT SAL FROM EMP WHERE NAME = 'SCOTT')FROM DUAL);
          
-- 7. DALLAS에 위치한 부서에 속한 사원의 / 총사원수, 평균급여,전체급여,최고급여,초저급여를 구하세요.
-- ?FROM절 뭐임?
    SELECT COUNT(*), AVG(SAL), SUM(SAL), MAX(SAL), MIN(SAL)
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND e.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');

SELECT COUNT(*), AVG(SAL), SUM(SAL), MAX(SAL),MIN(SAL) 
FROM (SELECT SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO 
AND E.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS'));

-- 8. 커미션 계약조건이 있으며 이름에 'N'자가 들어가는 사원들 중 급여가 가장 적은 사원의 / 사원명, 급여,부서명을 출력
-- 서브쿼리 위치 
    SELECT ENAME, SAL, DNAME
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND SAL = (SELECT MIN(SAL) FROM EMP WHERE ENAME LIKE '%N%')
    AND COMM = (SELECT COMM FROM EMP WHERE COMM IS NOT NULL);
-- 카페 정답
SELECT ENAME,SAL,DNAME 
FROM EMP E,DEPT D 
WHERE E.DEPTNO = D.DEPTNO
AND SAL = (SELECT MIN(SAL) FROM EMP WHERE COMM IS NOT NULL
AND ENAME LIKE '%N%') AND ENAME LIKE '%N%';

/*
SELECT ENAME,SAL,DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO
AND SAL = (SELECT MIN(SAL) FROM (SELECT SAL, ENAME FROM EMP WHERE COMM IS NOT NULL
AND ENAME LIKE '%N%')) AND ENAME LIKE '%N%';
*/
-- 9. ALLEN 보다 입사일이 빠른 사원의 부서명,사원명을 출력
    SELECT DNAME, ENAME
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME = 'ALLEN');

-- 10. EMP 테이블에서 이름이 5글자인 사원중 급여가 가장 높은 사원의 이름, 급여 , 부서명을 출력
-- 뒤에 AND LENGTH(ENMAE) = 5 반복 추가?
    SELECT ENAME, SAL, DNAME
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND SAL = (SELECT MAX(SAL) FROM EMP WHERE LENGTH(ENAME) = 5)
    AND LENGTH(ENAME) = 5;

-- 11. CLARK 이 속한 부서의 평균 연봉보다 급여가 높은 사원중 입사일이 가장 빠른 사원의 부서명, 사원명, 급여를 출력
    SELECT DNAME, ENAME, SAL
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND HIREDATE = (SELECT MIN(HIREDATE) FROM EMP WHERE SAL 
                        > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO
                        = (SELECT DEPTNO FROM EMP WHERE ENAME = 'CLARK'))); 
                                                
-- 12. ALLEN의 부서명을 출력
    SELECT DNAME FROM DEPT WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'ALLEN');

-- 13. 이름에 J가 들어가는 사원들 중, 급여가 가장 높은 사원의 // 사원번호, 이름, 부서명, 급여, 부서위치를 출력
-- AND 절 위치
    SELECT EMPNO, ENAME, DNAME, SAL, LOC
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND SAL = (SELECT MAX(SAL) FROM EMP WHERE ENAME LIKE '%J%');

-- 14. 두번째로 많은 급여를 받는 사원의 이름과 부서명,급여를 출력
-- 두번째로 많은 급여? -> 급여가 MAX보다 작은 값들 중 MAX!
    SELECT ENAME, DNAME, SAL
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND SAL = (SELECT MAX(SAL) FROM EMP WHERE SAL 
                < (SELECT MAX(SAL) FROM EMP));

-- 15. 입사일이 2번째로 빠른 사원의 부서명과 이름, 입사일을 출력
    SELECT DNAME, ENAME, HIREDATE
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND HIREDATE = (SELECT MIN(HIREDATE) FROM EMP WHERE HIREDATE
                    > (SELECT MIN(HIREDATE) FROM EMP));

-- 16. DALLAS에 위치한 부서의 사원 중 최대 급여를 받는 사원의 급여에서 최소 급여를 받는 사원의 급여를 뺀 결과를 출력
    SELECT MAX(SAL) - MIN(SAL)
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND LOC = 'DALLAS';

---

🤯 아리송~ 문제들

 

3. 결과같음

    ➡ 조인구문으로 한번에 FROM절과 묶어서 쓰는게 좋다 :) 위 쿼리로 가자~

 

4. 결과 같음

    ➡ 굳이 아래 빨간네모 넣을 필요는 없다!  위 쿼리로 가자~

---

7. 다시 풀어보기 / 결과 같다!

    ➡ SAL을 먼저 뽑아낸 걸 기준으로 조건을 붙인다.

 

---

8. 서브쿼리 안에 AND ENAME LIKE '%N%'이 들어가도 되는지??

    ➡ 결과값은 동일하다.

---

10. 서브쿼리 결과를 직접 대입하여 생각해보기

    ➡ 헷갈릴 수 있으니 주의하자!!

     급여가 2975이면서 ENAME이 4글자인 데이터 하나 추가해서 시도해보시면 다를거에요
     뒤에 LENGTH = 5 가 없으면,

     SELECT ENAME, SAL, DNAME

     FROM EMP E, DEPT D

     WHERE E.DEPTNO = D.DEPTNO

     AND SAL = 2975(서브쿼리 결과); 가 되면서 2975인 넘들이 다 나오게 된다. 이름 글자 수 상관없이!

     (데이터가 적어서 혼동했던 문제,, ㅠ)

 

---

💻 후기

두번째로 많은 급여? -> 급여가 MAX보다 작은 값들 중 MAX!
    SELECT ENAME, DNAME, SAL
    FROM EMP e, DEPT d
    WHERE e.DEPTNO = d.DEPTNO
    AND SAL = (SELECT MAX(SAL) FROM EMP WHERE SAL 
                < (SELECT MAX(SAL) FROM EMP));