230801 SQL 강의

--조건에 따른 다중 ELSIF
SET SERVEROUTPUT ON;  -- 출력옵션으로 ON
DECLARE 
   v_num   NUMBER := 67;
BEGIN
   DBMS_OUTPUT.ENABLE; 
   IF v_num > 90 THEN
      DBMS_OUTPUT.PUT_LINE('수');
   ELSIF  v_num > 80 THEN
      DBMS_OUTPUT.PUT_LINE('우');
   ELSIF  v_num > 70 THEN
      DBMS_OUTPUT.PUT_LINE('미');     
   ELSE
      DBMS_OUTPUT.PUT_LINE('분발합시다.');
   END IF; 
END;
/

---

--SELECT INTO 로 변수에 값을 할당
SELECT * FROM PROD;
DECLARE 
      v_avg_sale PROD.PROD_SALE%TYPE; --NUMBER(10,0)
      v_sale NUMBER := 500000; 
  BEGIN 
     DBMS_OUTPUT.ENABLE;    

     SELECT AVG(prod_sale) INTO v_avg_sale FROM prod;

     IF v_sale < v_avg_sale THEN
         DBMS_OUTPUT.PUT_LINE( '평균 단가가 500000 초과입니다.');
     ELSE
         DBMS_OUTPUT.PUT_LINE( '평균 단가가 500000 이하 입니다.');
     END IF;
  END;  
  / -- CTRL+ENTER 를 쓰기 위한 구분 표시

---

--회원테이블에서 아이디가 'e001' 인 회원의 마일리지가 5000을 넘으면
--'VIP 회원' 그렇지 않다면 '일반회원'으로 출력하시오. (회원이름, 마일리지 포함)
SELECT * FROM MEMBER;

DECLARE
    V_MEM_ID      VARCHAR2(10) := 'e001';
    V_MEM_MILEAGE MEMBER.MEM_MILEAGE%TYPE;
    V_MEM_NAME    MEMBER.MEM_NAME%TYPE;
    V_MILE        NUMBER  := 5000;
BEGIN
--    DBMS_OUTPUT.ENABLE;
    
    SELECT MEM_MILEAGE,   MEM_NAME
      INTO V_MEM_MILEAGE, V_MEM_NAME
      FROM MEMBER
     WHERE MEM_ID = V_MEM_ID;
    
    IF V_MILE < V_MEM_MILEAGE THEN
        DBMS_OUTPUT.PUT_LINE('VIP 회원(' || V_MEM_NAME || ', ' || V_MEM_MILEAGE || ')');
    ELSE
        DBMS_OUTPUT.PUT_LINE('일반회원(' || V_MEM_NAME || ', ' || V_MEM_MILEAGE || ')');
    END IF;
END;
/

---

--SQL 에서 사용하는 CASE 문과 동일하다.
--단, 차이점은 END CASE 로 마지막을 지정해야 한다. 
DECLARE
    V_NUM NUMBER := 77;
BEGIN
    V_NUM := TRUNC(V_NUM / 10);
    
    CASE V_NUM    
        WHEN 10 THEN
            DBMS_OUTPUT.PUT_LINE('수' || '(' || V_NUM || ')');
        WHEN 9 THEN
            DBMS_OUTPUT.PUT_LINE('우' || '(' || V_NUM || ')');
        WHEN 8 THEN
            DBMS_OUTPUT.PUT_LINE('미' || '(' || V_NUM || ')');
        WHEN 7 THEN
            DBMS_OUTPUT.PUT_LINE('양' || '(' || V_NUM || ')');
        ELSE
            DBMS_OUTPUT.PUT_LINE('분발합시다');
    END CASE;
END;
/

---

-- 부하직원이 있는 사원의 이름을 표시하시오. (사원 번호가 매니저로 저장되어 있는 사람을 조회해야 함)
SELECT * FROM EMP;
SELECT DISTINCT(E.ENAME) 사원명
  FROM EMP E, EMP E1
 WHERE E.EMPNO = E1.MGR;

---

--김동혁과 동일한 부서에 속한 사원의 부서코드, 이름, 입사일을 표시하는 질의를 작성하시오.
--( 단 김동혁은 제외 )
SELECT * FROM EMP;
SELECT EMPNO
     , DEPTNO 
  FROM EMP 
 WHERE ENAME = '김동혁';
 
SELECT E1.DEPTNO 부서코드
     , E1.ENAME 이름
     , E1.HIREDATE 입사일
  FROM EMP E1,
  (
    SELECT DEPTNO 
      FROM EMP 
     WHERE ENAME = '김동혁'
  )E2
 WHERE E1.DEPTNO = E2.DEPTNO
   AND E1.ENAME != '김동혁';
   
SELECT E1.DEPTNO 부서코드
     , E1.ENAME 사원명
     , E1.HIREDATE 입사일
  FROM EMP E ,EMP E1
 WHERE E.DEPTNO = E1.DEPTNO -- 김동혁 이 속한 부서 
   AND E.ENAME = '김동혁'
   AND NOT E1.ENAME ='김동혁';

---

--급여가 평균 급여보다 많은 사원들의 사원 번호와 이름을 표시하되
--결과를 급여에 대해서 오름차순으로 정렬하시오.
SELECT * FROM EMP;
SELECT ROUND(AVG(SAL),0) FROM EMP;

SELECT EMPNO 사원번호
     , ENAME 이름
     , SAL 급여
  FROM EMP
 WHERE SAL > (SELECT ROUND(AVG(SAL),0) FROM EMP)
 ORDER BY SAL;

---

--이름에 “국”이 포함된 사원과 같은 부서에서 일하는 사원의 사원 번호와 이름을 표시하시오.
SELECT * FROM EMP;
SELECT DEPTNO 
  FROM EMP 
 WHERE ENAME LIKE '%국%';
 
SELECT EMPNO 사원번호
     , ENAME 이름
  FROM EMP
 WHERE DEPTNO = (SELECT DEPTNO 
                   FROM EMP 
                  WHERE ENAME LIKE '%국%');

SELECT DISTINCT(E1.EMPNO) 사원번호
     , E1.ENAME 사원명
  FROM EMP E, EMP E1
 WHERE E.DEPTNO = E1.DEPTNO 
   AND E.ENAME LIKE '%국%';

---

--부서위치가 서울인 사원의 이름과 부서번호 및 담당업무를 표시하시오.
SELECT * FROM EMP;
SELECT * FROM DEPT;
 
SELECT A.ENAME 사원이름
     , A.DEPTNO 부서번호
     , A.JOB 담당업무
  FROM EMP A, DEPT B
 WHERE A.DEPTNO = B.DEPTNO
   AND B.LOC = '서울';

---

--김지완에게 보고하는 사원의 이름과 급여를 표시하시오.
SELECT * FROM EMP;

SELECT EMPNO FROM EMP WHERE ENAME = '김지완';

SELECT ENAME 사원이름
     , SAL 급여
  FROM EMP
 WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = '김지완');
 
SELECT E1.ENAME 사원이름
     , E1.SAL 급여
  FROM EMP E, EMP E1
 WHERE E.EMPNO = E1.MGR 
   AND E.ENAME ='김지완';

---

--조사부 부서의 사원에 대한 부서번호, 사원이름 및 담당 업무를 표시하시오.
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT DEPTNO FROM DEPT WHERE DNAME='조사부';

SELECT DEPTNO 부서번호
     , ENAME 사원이름
     , JOB 담당업무
  FROM EMP
 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME='조사부');
 
SELECT DEPT.DEPTNO 부서번호
     , EMP.ENAME 사원명
     , EMP.JOB 담당업무
  FROM EMP, DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO 
   AND DEPT.DNAME = '조사부';

---

--평균 월급보다 많은 급여를 받고 이름에 “우”가 포함된 사원과 같은 부서에서 근무하는 사원의 사원 번호, 이름, 급여를 표시하시오.
SELECT * FROM EMP;
SELECT ROUND(AVG(SAL)) FROM EMP;

SELECT EMPNO 사원번호
     , ENAME 이름
     , SAL 급여
  FROM EMP
 WHERE ENAME LIKE '%우%'
   AND SAL > (SELECT ROUND(AVG(SAL)) FROM EMP);

---

--평균급여가 가장 적은 업무를 찾으시오.
SELECT * FROM EMP;

SELECT JOB
     , ROUND(AVG(SAL))
  FROM EMP
 GROUP BY JOB
 ORDER BY ROUND(AVG(SAL));
 
SELECT ASD 업무
  FROM (
        SELECT JOB ASD
             , ROUND(AVG(SAL))
          FROM EMP
         GROUP BY JOB
         ORDER BY ROUND(AVG(SAL))
        )
 WHERE ROWNUM = 1;

---

--담당업무가 관리자 인 사원이 소속된 부서와 동일한 부서의 사원을 표시하시오.
SELECT * FROM EMP;
SELECT DEPTNO 
  FROM EMP 
 WHERE JOB = '관리자';
 
SELECT A.ENAME 사원명
  FROM EMP A,
  (
    SELECT DEPTNO 
      FROM EMP 
     WHERE JOB = '관리자'
  ) B
 WHERE A.DEPTNO = B.DEPTNO;

---