230725 SQL 강의

-- SUBSTR 함수를 사용하여 4월에 입사한 사원을 출력하시오.
SELECT * FROM EMP;

SELECT * 
  FROM EMP
 WHERE SUBSTR(HIREDATE, 4, 2) LIKE '04';

---

-- MOD 함수를 사용하여 사원번호가 짝수인 사람만 출력하시오.
SELECT * FROM EMP;

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

---

-- 입사일을 년도는 2자리(YY), 월은 숫자(MON)로 표시하고 요일은 약어(DD)로 지정하여 출력하시오.
SELECT * FROM EMP;

SELECT TO_CHAR(HIREDATE, 'YY') YY
     , TO_CHAR(HIREDATE, 'MON') MON
     , TO_CHAR(HIREDATE, 'DY') DD
  FROM EMP;

---

-- 올해 몇 칠이 지났는지 출력하시오. 현재날짜에서 올해 1월 1일을 뺀 결과를 출력하고 TO_DATE 함수를 사용하여 데이터 형을 일치 시키시오.
SELECT SYSDATE FROM DUAL;

SELECT ROUND(SYSDATE - TO_DATE('23/01/01')) || '일' "올해 몇 칠 지났나"
  FROM DUAL;

---

--사원들의 사번, 이름, 상관사번을 출력하되 
--상관이 없는 사원에 대해서는 NULL 값 대신 0으로 출력하시오.
SELECT * FROM EMP;

SELECT EMPNO 사번
     , ENAME 이름
     , NVL(MGR, 0) 상관사번
  FROM EMP;

---

--DECODE 함수로 직급에 따라 급여를 인상하도록 하시오. 
--직급이 ‘분석가’인 사원은 200, ‘영업사원’인 사원은 180, ‘관리자’인 사원은 150, ‘점원”인 사원은 130을 인상하시오.
SELECT * FROM EMP;

SELECT ENAME "사원명"
     , JOB "직급"
     , SAL "원래 급여"
     , DECODE(JOB,
              '분석가', SAL + 200,
              '영업사원', SAL + 180,
              '관리자', SAL + 150,
              '점원', SAL + 130,
              SAL) "인상된 급여"
  FROM EMP;

---

--모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오. 평균에 대해서는 정수로 반올림하시오.
SELECT * FROM EMP;

SELECT MAX(SAL) "급여 최고액"
     , MIN(SAL) "급여 최저액"
     , SUM(SAL) "총액"
     , ROUND(AVG(SAL),0) "평균 급여"
  FROM EMP;

---

-- 각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균 액을 출력하시오. 평균에 대해서는 정수로 반올림 하시오.
SELECT JOB "담당 업무"
     , MAX(SAL) "급여 최고액"
     , MIN(SAL) "급여 최저액"
     , SUM(SAL) "총액"
     , ROUND(AVG(SAL),0) "평균 급여"
  FROM EMP
 GROUP BY JOB;

---

--COUNT(*) 함수를 이용하여 담당업무가 동일한 사원 수를 출력하시오.
SELECT * FROM EMP;

SELECT JOB "담당업무"
     , COUNT(*) "사원수"
  FROM EMP
 GROUP BY JOB;

---

--업무를 표시한 다음 해당 업무에 대해 부서 번호별 급여 및 부서 10, 20, 30의 급여 총액을 각각 출력하시오.
--별칭은 각 부서번호, 부서명, 총액으로 지정하시오. ( hint. IN, GROUP BY )
SELECT * FROM EMP;
SELECT * FROM DEPT;

SELECT a.DEPTNO "부서번호"
     , b.DNAME "부서명"
     , SUM(a.SAL) "급여 총액"
  FROM EMP a, DEPT b
 WHERE 1=1
   AND a.DEPTNO = b.DEPTNO
   AND a.DEPTNO IN('10', '20', '30')
 GROUP BY a.DEPTNO, b.DNAME;

---

--EQUI 조인을 사용하여 김민욱 사원의 부서번호와 부서 이름을 출력하시오.
SELECT a.DEPTNO 부서번호
     , b.DNAME 부서명
     , a.ENAME 사원명
  FROM EMP a, DEPT b
 WHERE 1=1
   AND a.DEPTNO = b.DEPTNO
   AND a.ENAME = '김민욱';

---

-- INNER JOIN과 ON 연산자를 사용하여 사원 이름과 함께 그 사원이 소속된 부서이름과 지역명을 출력하시오.
SELECT EMP.EMPNO
     , EMP.ENAME
     , EMP.JOB
     , EMP.MGR
     , EMP.HIREDATE
     , EMP.SAL
     , EMP.COMM
     , EMP.DEPTNO
     , DEPT.DEPTNO
     , DEPT.DNAME
     , DEPT.LOC
  FROM EMP 
 INNER JOIN DEPT ON(EMP.DEPTNO = DEPT.DEPTNO);
 
SELECT EMP.ENAME 사원이름
     , DEPT.DNAME 부서이름
     , DEPT.LOC 지역명
  FROM EMP 
 INNER JOIN DEPT ON(EMP.DEPTNO = DEPT.DEPTNO);

---

--NATURAL JOIN을 사용하여 커미션을 받는 모든 사원의 이름, 부서이름, 커미션, 지역 명을 출력하시오.
SELECT * FROM EMP;
SELECT * FROM DEPT;

SELECT a.ENAME 사원명
     , b.DNAME 부서명
     , a.COMM 커미션
     , b.LOC 지역명
  FROM EMP a
 NATURAL JOIN DEPT b
 WHERE COMM IS NOT NULL;

---

--EQUI 조인과 WILDCARD를 사용하여 이름에 ‘민’자가 포함된 모든 사원의 이름과 부서명을 출력하시오.
SELECT a.ENAME 사원명
     , b.DNAME 부서명
  FROM EMP a, DEPT b
 WHERE 1=1
   AND a.DEPTNO = b.DEPTNO
   AND a.ENAME LIKE '%민%';

---

-- NATUAL JOIN을 이용하여 대전에 근무하는 모든 사원의 이름, 업무, 부서번호 및 부서명을 출력하시오.
SELECT * FROM EMP;
SELECT * FROM DEPT;

SELECT a.ENAME 사원명
     , a.JOB 업무
     , DEPTNO 부서번호 -- NATURAL JOIN의 키 값은 테이블명이나 별칭을 붙이지 않는다.
     , b.DNAME 부서명
  FROM EMP a
 NATURAL JOIN DEPT b
 WHERE b.LOC = '대전';

---

-- SELF JOIN을 사용하여 사원의 이름 및 사원번호를 관리자 번호와 함께 출력하시오.
SELECT * FROM EMP;

SELECT e1.ENAME 사원명
     , e1.EMPNO 사원번호
     , e1.MGR 관리자번호
     , e2.ENAME 관리자명
  FROM EMP e1, EMP e2
 WHERE e1.MGR = e2.EMPNO;

---

-- 회원 중에 성이 '김' 이고, 성 다음에  '성' 또는 '형' 이 있는 회원을 검색
SELECT MEM_ID   AS "회원ID"
     , MEM_NAME AS "회원명"
  FROM MEMBER  
 WHERE REGEXP_LIKE(MEM_NAME, '^김(성|형)');

---

--상품 이름 중에 '삼성' 이라는 말이 있고, 숫자 두 개가 같이 있는 상품의 상품코드, 상품명, 판매가를 검색하시오?
SELECT * FROM PROD;

SELECT PROD_ID   AS "상품ID"
     , PROD_NAME AS "상품명"
     , PROD_SALE AS "판매가"
  FROM PROD
 WHERE REGEXP_LIKE(PROD_NAME, '^삼성.*\d\d'); -- 삼성으로 시작하고(any) 오는 것 중(*) 숫자가 2회(\d\d) 반복되는 것

---

--공백이 아닌 데이터가 1개 이상 연결되는 문자열 중 첫 번째 문자열을 반환한다.
SELECT REGEXP_SUBSTR('Java Flex Oracle', '[^ ]+') 
  FROM DUAL;

---

--공백이 아닌 데이터가 1개 이상 연결되는 문자열 중 첫 번부터 시작해서 세 번째 문자열을 반환한다.
SELECT REGEXP_SUBSTR('Java Flex Oracle', '[^ ]+', 1, 3)
  FROM DUAL;

---

--회원테이블에서 이메일주소를 근거로 이메일아이디, 이메일서버로 구분하여 검색하시오?
--(Alias는 회원이름, 이메일, 이메일아이디, 이메일서버)
SELECT MEM_NAME                             AS "회원이름"
     , MEM_MAIL                             AS "이메일"
     , REGEXP_SUBSTR(MEM_MAIL, '[^@]+')     AS "이메일아이디"
     , REGEXP_SUBSTR(MEM_MAIL, '[^@]+',1,2) AS "이메일서버"
  FROM MEMBER;

---

SELECT REGEXP_SUBSTR('C-01-02', '[^-]+', 1, 1)
  FROM DUAL;

SELECT REGEXP_SUBSTR('C-01-02', '[^-]+', 1, 2)
  FROM DUAL;
  
SELECT REGEXP_SUBSTR('C-01-02', '[^-]+', 1, 3)
  FROM DUAL;

---

SELECT REGEXP_REPLACE('Java Flex Oracle', '[^ ]+','C++') 
  FROM DUAL;

---

SELECT REGEXP_REPLACE('Java Flex Oracle', '[^ ]+', 'C++', 1, 2)
  FROM DUAL;

---

--회원테이블에서 주민번호 1 컬럼을 기준으로 xx월 xx일 형식으로 
--조회하시오.(회원명, 주민번호1, 생일)
SELECT MEM_NAME 회원이름
     , MEM_REGNO1 || '-' || MEM_REGNO2 주민번호
     , SUBSTR(MEM_REGNO1,1,2) || '년' ||                                                      
       SUBSTR(MEM_REGNO1,3,2) || '월' ||    
       SUBSTR(MEM_REGNO1,5,2) || '일' 생일
  FROM MEMBER;

---

SELECT REGEXP_INSTR('JAVA Flex Oracle','[ae]') RESULT -- a나 e가 첫번째 오는 위치 => 8
  FROM DUAL;

---

SELECT REGEXP_INSTR('JAVA Flex Oracle','[ae]', 1, 1, 0, 'i') RESULT, -- a나 e가 대소문자구분없이(i) 첫번째 오는 위치로 부터 첫번째 오는 위치 => 2
       REGEXP_INSTR('JAVA Flex Oracle','[ae]', 3, 2, 1, 'i') RESULT  -- a나 e가 대소문자구분없이(i) 세번째부터 두번째 오는 마지막 위치 => 9, 세번째 매개변수 값 0 : 정방향, 1 : 역방향
  FROM DUAL;

---

SELECT prod_id, prod_name, prod_lgu
  FROM prod
 WHERE prod_lgu IN ('P301');

SELECT prod_id, prod_name, prod_lgu
  FROM prod
 WHERE prod_lgu IN ( SELECT lprod_gu
                       FROM lprod
                      WHERE lprod_nm = '피혁잡화' );

SELECT prod_id, prod_name, prod_lgu
  FROM prod
 WHERE EXISTS ( SELECT lprod_gu
                  FROM lprod
                 WHERE lprod_gu = prod.prod_lgu
                   AND lprod_gu = 'P301' );

---

SELECT COUNT(*) FROM LPROD; -- 9
SELECT COUNT(*) FROM PROD; -- 74
SELECT COUNT(*) FROM BUYER; -- 13
SELECT COUNT(*)   
  FROM LPROD, PROD, BUYER; -- 9*74*13 = 8658

---

---

-- 1. 분류테이블 조회
SELECT * FROM lprod;

---

-- 2. 일반 JOIN
SELECT LPROD_GU        AS "분류코드"
     , LPROD_NM        AS "분류명"
     , COUNT(PROD_LGU) AS "상품자료수"
  FROM LPROD, PROD
 WHERE LPROD_GU = PROD_LGU
 GROUP BY LPROD_GU ,LPROD_NM;

---

-- 3. OUTER JOIN 사용 확인
SELECT LPROD_GU        AS "분류코드"
     , LPROD_NM        AS "분류명"
     , COUNT(PROD_LGU) AS "상품자료수"
  FROM LPROD, PROD
 WHERE LPROD_GU = PROD_LGU(+)
 GROUP BY LPROD_GU ,LPROD_NM
 ORDER BY LPROD_GU;

---

-- 4. ANSI OUTER JOIN 사용 확인
SELECT LPROD_GU 	   AS "분류코드"
     , LPROD_NM 	   AS "분류명"
     , COUNT(PROD_LGU) AS "상품자료수"
  FROM LPROD 
  LEFT OUTER JOIN PROD ON (LPROD_GU = PROD_LGU)
  GROUP BY LPROD_GU, LPROD_NM
  ORDER BY LPROD_GU;