27일차 오라클 명령어 퀴즈 1,2

퀴즈1_2020-03-09.txt

0.00MB

퀴즈2_2020-03-09.txt

0.01MB

 

Q1) 'MARTIN'과 같은 급여를 받는 사원의 이름,사원번호,급여를 출력해 보세요.

SELECT ENAME, EMPNO, SAL
FROM EMP
WHERE SAL=(SELECT SAL FROM EMP WHERE ENAME='MARTIN');

ENAME                     EMPNO        SAL
-------------------- ---------- ----------
WARD                       7521       1250
MARTIN                     7654       1250

Q2) 최고급여를 받는 사원의 모든 정보를 출력하세요.

SELECT *
FROM EMP
WHERE SAL=(SELECT MAX(SAL) FROM EMP);

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7839 KING                 PRESIDENT                     81/11/17       5000                    10

Q3) 사원번호가 7369인 사원과 같은 직업을 갖고, 7369인 사원보다 많은 급여를 받는 사원을 
모든정보를 조회하세요.(단일행)

SELECT *
FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7369)
AND SAL>(SELECT SAL FROM EMP WHERE EMPNO=7369);

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7900 JAMES                CLERK                    7698 81/12/03        950                    30
      7934 MILLER               CLERK                    7782 82/01/23       1300                    10

Q4) 부서별 최소급여가 30번부서의 최소급여보다 많은 부서번호와 해당 부서의 최소급여를 출력하세요.(복수행)

SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL)>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

    DEPTNO   MIN(SAL)
---------- ----------
        10       1300

Q5) 'JONES'와 같은 부서에 근무하는 모든 사원의 부서번호,사원번호,사원이름,급여를 출력하시오.

SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='JONES');

    DEPTNO      EMPNO ENAME              SAL
---------- ---------- -------------------- ----------
        20       7369 SMITH                       800
        20       7566 JONES                      2975
        20       7902 FORD                       3000


============================================================================

 

1. 'ALLEN'의 직무와 같은 사람의 이름,급여,직무를 출력하라.

SELECT JOB FROM EMP WHERE ENAME='ALLEN';

JOB
------------------
SALESMAN

SELECT ENAME, SAL, JOB
FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN');

ENAME                       SAL JOB
-------------------- ---------- ------------------
ALLEN                      1600 SALESMAN
WARD                       1250 SALESMAN
MARTIN                     1250 SALESMAN
TURNER                     1500 SALESMAN

2. 전체 사원의 평균임금보다 많은 사원의 사원번호, 이름,입사일,급여를 출력하라.

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

     EMPNO HIREDATE        SAL
---------- -------- ----------
      7566 81/04/02       2975
      7698 81/05/01       2850
      7782 81/06/09       2450
      7839 81/11/17       5000
      7902 81/12/03       3000

3. 10번 부서 사람들중에 20번 부서의 사원과 같은 업무를 하는 사원의 사원번호,업무,이름,
입사일을 출력하시오.
 
SELECT EMPNO, JOB, ENAME
FROM EMP
WHERE DEPTNO=10 AND JOB IN(SELECT JOB FROM EMP WHERE DEPTNO=20);

     EMPNO JOB                ENAME
---------- ------------------ --------------------
      7934 CLERK              MILLER
      7782 MANAGER            CLARK

4. 10번 부서중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호,업무,이름,입사일을 출력하시오.
(단일행 결과 어떻게 아는지)
SELECT EMPNO, JOB, ENAME, HIREDATE
FROM EMP
WHERE DEPTNO=10 AND JOB IN(SELECT JOB FROM EMP WHERE DEPTNO!=30);

SELECT EMPNO, JOB, ENAME, HIREDATE
FROM EMP
WHERE DEPTNO=10 AND JOB!=ALL(SELECT JOB FROM EMP WHERE DEPTNO=30);

     EMPNO JOB                ENAME                HIREDATE
---------- ------------------ -------------------- --------
      7839 PRESIDENT          KING                 81/11/17

5. 10번 부서와 같은 일을 하는 사원의 사원번호,업무,이름,부서번호,급여를 급여가 많은 순으로 출력하라.

SELECT EMPNO, JOB, ENAME, DEPTNO, SAL
FROM EMP
WHERE JOB=ANY(SELECT JOB FROM EMP WHERE DEPTNO=10) 
ORDER BY SAL DESC;

(ALL이 참이되는 경우는 DEPTNO=10 의 인자들 직업이 모두 같을 경우만 성립)

     EMPNO JOB                ENAME                    DEPTNO        SAL
---------- ------------------ -------------------- ---------- ----------
      7839 PRESIDENT          KING                         10       5000
      7566 MANAGER            JONES                        20       2975
      7698 MANAGER            BLAKE                        30       2850
      7782 MANAGER            CLARK                        10       2450
      7934 CLERK              MILLER                       10       1300
      7900 CLERK              JAMES                        30        950
      7369 CLERK              SMITH                        20        800

6.'MARTIN' 이나 'SCOTT'의 급여와 같은 사원의 사원번호,이름,급여를 출력하라.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL=ANY(SELECT SAL FROM EMP WHERE ENAME='MARTIN' OR ENAME='SCOTT');

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7654 MARTIN                     1250
      7521 WARD                       1250

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

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

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7566 JONES                      2975
      7839 KING                       5000
      7902 FORD                       3000

8. 급여가 30번 부서의 최저 급여보다 낮은 사원의 사원번호,이름,급여를 출력하라.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL<(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7369 SMITH                       800

9. 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호를 출력하시오. 
단 업무별로 정렬하시오.

복수행서브쿼리
SELECT MIN(SAL) FROM EMP GROUP BY JOB;

  MIN(SAL)
----------
       800
      1250
      5000
      2450
      3000

SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE (JOB,SAL) IN(SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB)
ORDER BY JOB;

     EMPNO ENAME                JOB                    DEPTNO
---------- -------------------- ------------------ ----------
      7902 FORD                 ANALYST                    20
      7369 SMITH                CLERK                      20
      7782 CLARK                MANAGER                    10
      7839 KING                 PRESIDENT                  10
      7521 WARD                 SALESMAN                   30
      7654 MARTIN               SALESMAN                   30

10. 부서별로 최소 급여를 받는 사원의 사원번호,이름,부서번호,급여를 출력하시오.

복수행서브쿼리
SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO;

  MIN(SAL)
----------
       950
       800
      1300

SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP
WHERE (DEPTNO,SAL)=ANY(SELECT DEPTNO,MIN(SAL) FROM EMP GROUP BY DEPTNO);

     EMPNO ENAME                    DEPTNO        SAL
---------- -------------------- ---------- ----------
      7369 SMITH                        20         800
      7900 JAMES                        30         950
      7934 MILLER                       10       1300

 

퀴즈2_정답.zip

0.03MB

퀴즈1_정답.zip

0.02MB