Oracle(2022.02.10)-기타연산자(IN, ANY, SOME, ALL, BETWEEN, LIKE, EXISTS)

기타연산자(IN, ANY, SOME, ALL, BETWEEN, LIKE, EXISTS)

  1)IN 연산자
   -표현식(컬럼 OR 수식)의 값이 주어진 값들 중 어느하나와 일치하면
   -참(true)을 반환
   -불연속적이거나, 규칙적이지 않은 자료를 비교연산할때 사용 
   - =ANY, =SOME 으로 대치 가능
   - OR 연산자로 대치 가능
   (사용형식)
   expr IN(값1[,값2,...])
   사용예)사원테이블에서 부서번호가 10-40번에 속한 사원을 조회하시오
       Alias는 사원번호, 사원명, 입사일, 급여

 SELECT EMPLOYEE_ID AS 사원번호,
       EMP_NAME AS 사원명,
       DEPARTMENT_ID AS 부서코드,
       HIRE_DATE AS 입사일,
       SALARY AS 급여
    FROM HR.EMPLOYEES
      WHERE DEPARTMENT_ID>=10 AND DEPARTMENT_ID<=40;
   대체:   WHERE DEPARTMENT_ID BETWEEN 10 AND 40;

사용예)사원테이블에서 부서번호가 20,70,80,100번에 속한 사원을 조회하시오
       Alias는 사원번호, 사원명, 부서코드, 입사일, 급여

   SELECT EMPLOYEE_ID AS 사원번호,
       EMP_NAME AS 사원명,
       DEPARTMENT_ID AS 부서코드,
       HIRE_DATE AS 입사일,
       SALARY AS 급여
     FROM HR.EMPLOYEES
     WHERE DEPARTMENT_ID=20
        OR DEPARTMENT_ID=70
        OR DEPARTMENT_ID=80
        OR DEPARTMENT_ID=100
     ORDER BY 3;
     --------------------------------------------------------------------------------------------
      대체: SELECT EMPLOYEE_ID AS 사원번호,
                         EMP_NAME AS 사원명,
                         DEPARTMENT_ID AS 부서코드,
                         HIRE_DATE AS 입사일,
                     SALARY AS 급여
           FROM HR.EMPLOYEES
        WHERE DEPARTMENT_ID IN(20,70,80,100) 
        --> 대체:WHERE DEPARTMENT_ID=SOME(20,70,80,100)
         ORDER BY 3;

   IN은 그냥 쓰지만 SOME은 '='를 같이써줘야 한다.

   WHERE DEPARTMENT_ID IN(20,70,80,100) 
   대체:WHERE DEPARTMENT_ID=SOME(20,70,80,100)

                                   :

                              데이터 생략

 

---

    2)ANY(SOME) 연산자
    (사용형식)
    expr 관계연산자 ANY|SOME(값1[,값2,...])
    -expr의 값이 '값1' 에서 '값n' 까지 중에 '관계연산자'의 연산결과를 반환

사용예) 회원테이블에서 직업이 '주부'인 회원들보다 마일리지가 많은 회원들을
       조회하시오. Alias는 회원번호,회원명,직업,마일리지

먼저 (주부회원들의 마일리지 조회)
    SELECT MEM_MILEAGE AS 마일리지
        FROM MEMBER
     WHERE MEM_JOB='주부';
     
     SELECT MEM_ID AS 회원번호,
            MEM_NAME AS 회원명,
            MEM_JOB AS 직업,
            MEM_MILEAGE AS 마일리지
        FROM MEMBER
      WHERE MEM_MILEAGE >ANY(800,1000,2700,8700);
      -------------------------------------------
대체: SELECT MEM_ID AS 회원번호,
            MEM_NAME AS 회원명,
            MEM_JOB AS 직업,
            MEM_MILEAGE AS 마일리지
        FROM MEMBER
      WHERE MEM_MILEAGE >ANY(SELECT MEM_MILEAGE 
                               FROM MEMBER
                               WHERE MEM_JOB='주부');

                          :

                   데이터 생략

---

  3)ALL 연산자
  (사용형식)
  expr 관계연산자ALL(값1[,값2,...])
  -'='는 사용 안됨

사용예)회원테이블에서 직업이 '학생'인 모든 회원의 마일리지보다 마일리지가 많은 회원들을
       조회하시오.
       Alias는 회원번호,회원명,직업,마일리지

 (직업이 학생인 회원들의 마일리지)
    SELECT MEM_MILEAGE 
      FROM MEMBER
    WHERE MEM_JOB='학생';
     
     SELECT MEM_ID AS 회원번호,
            MEM_NAME AS 회원명,
            MEM_JOB AS 직업,
            MEM_MILEAGE AS 마일리지
        FROM MEMBER
     WHERE MEM_MILEAGE>ALL(700,2200,2700)
     ORDER BY 4;

    

직업이학생인회원들의마일리지

---

  4)BETWEEN 연산자
  - 범위를 지정할 때 사용
  - AND 연산자와 호환 사용 가능
  (사용형식)
   expr BETWEEN 값1 AND 값2
   -'값1'과 '값2'의 데이터 타입은 일치해야 함
   - 사용하는 데이터 타입은 제한없음

① 컬럼명 BETWEEN 시작일자 AND 종료일자 
② 컬럼명 >= 시작일자 AND 컬럼명 <= 종료일자
   

사용예)2005년 2월 매입현황을 출력하시오
       Alias는 일자, 상품코드, 수량, 단가, 금액이다.

 SELECT BUY_DATE AS 일자,
      BUY_PROD AS 상품코드,
      BUY_QTY AS 수량, 
      BUY_COST AS 단가, 
      BUY_QTY * BUY_COST AS 금액
    FROM BUYPROD
    WHERE BUY_DATE>=TO_DATE('20050201') AND BUY_DATE<=LAST_DAY(TO_DATE('20050201'));
    형 변환 TO_DATE: 괄호안에 받은 매개변수를 날짜로 바꿔준다. 
    Between사용한 경우
    WHERE BUY_DATE BETWEEN TO_DATE('20050201') AND LAST_DAY(TO_DATE('20050201'));

사용예)회원테이블에서 '김'씨성을 가진 회원부터 '박'씨 성의 회원까지
      회원번호, 회원명, 주민번호, 나이, 마일리지를 조회하시오.

SELECT MEM_ID AS 회원번호,
             MEM_NAME AS 회원명,
             MEM_REGNO1 AS 주민번호1,
             CASE WHEN SUBSTR (MEM_REGNO2,1,1)='1' OR
                       SUBSTR (MEM_REGNO2,1,1)='2' THEN
                       EXTRACT(YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))+1900)
             ELSE 
                       EXTRACT(YEAR FROM SYSDATE)-(TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))+2000)
             END AS 나이,
             MEM_MILEAGE AS 마일리지
        FROM MEMBER
       WHERE SUBSTR(MEM_NAME,1,1) BETWEEN '김' AND '박'
       ORDER BY 4;  -->정렬해주고 싶은 열 이름의 컬럼이 위치한 위치값을 쓴것이다. 나이를 정렬하기위해 4를 씀

 

** 상품테이블에서 각 상품별 마일리지를 구하여 PROD_MILEAGE값을 변경하시오
   PROD_MILEAGE 값=판매가의 0.5%
   UPDATE PROD
   SET PROD_MILEAGE=TRUNC(PROD_PRICE*0.0005,-1);
   -->TRUNC(-1)자리도 날려버려라
   -->데이터 값을 전부 변경해야하므로 WHERE절을 쓰지 않는다. WHERE절은 행을 담당하며 모든행을 담당하기 때문에 쓰지 않는 것임!
    UPDATE PROD
       SET PROD_MILEAGE=5
      WHERE PROD_MILEAGE=0;
  COMMIT;
-->값이 너무 작으니까 0인것을 찾아 5로 바꿔주려고 update문을 쓰게 됨

마일리지 부분이 수정된 것을 볼 수 있음

사용예)상품테이블에서 판매가가 20만원대 상품들을 조회하시오
    Alias는 상품코드, 상품명, 판매가, 마일리지

 SELECT PROD_ID AS 상품코드,
    PROD_NAME AS 상품명,
    PROD_PRICE AS 판매가,
    PROD_MILEAGE AS 마일리지
    FROM PROD
  WHERE PROD_PRICE>=200000 AND PROD_PRICE<300000
대체:WHERE PROD_PRICE BETWEEN 200000 AND 299999
   ORDER BY 3;

20만원대의 판매가 가격인 상품들

 

---

   5)LIKE 연산자
   -패턴을 비교할 때사용
   -패턴은 문자열 '%','_'(와일드 카드)을 사용하여 구성
   -*문자열 비교 연산자 *   중요!! 날짜와 숫자는 사용못함
   
   (사용형식)
   expr LIKE 패턴문자열 (문자열조회(비교)할때만 날짜,숫자X)
   (1) '%'
    -'%'가 사용된 위치 이후 모든 문자열과 대응
    (ex)'김%' : 첫 글자가 '김'으로 시작하는 모든 문자열과 대응(참(true)을 반환)
        '%김' : 마지막글자가 김'으로 끝나는 모든 문자열과 대응(참(true)을 반환)
        '%김%' : 문자열 중간에 '김' 문자열이 존재하면 참(true)을 반환
    (2) '_'
    -'_'가 사용된 위치에서 한 글자와 대응
    (ex) '김_' : 첫 글자가 '김'이고 2글자로 구성된 문자열과 대응(참(true)을 반환)
        '_김' : 2글자로 구성되고 끝 글자가 '김'으로 끝나는 문자열과 대응(참(true)을 반환)
        '_김_' : 3개의 문자열로 구성되며 중간에' 김' 문자가 존재하는 문자열과 대응참(true)을 반환)

사용예)장바구니테이블에서 2005년 7월 판매현황을 조회하시오
      Alias는 일자, 상품코드, 판매수량
      
     SELECT TO_DATE(SUBSTR(CART_NO,1,8)) AS 일자,
     CART_PROD AS상품코드,
     CART_QTY AS 판매수량
     FROM CART
     WHERE CART_NO LIKE '200507%';  --->200507로 시작하는 결과는 참이다.

                         :

                   데이터 생략

사용예)매입테이블에서 2005년 6월 매입현황을 조회하시오
      Alias는 일자, 상품코드, 매입수량  
      
    SELECT BUY_DATE AS 일자, 
    BUY_PROD AS 상품코드, 
    BUY_QTY AS 매입수량 
    FROM BUYPROD
    WHERE BUY_DATE BETWEEN TO_DATE(20050601) AND TO_DATE(20050630);

사용예)회원테이블에서 '충남'에 거주하는 회원을 조회하시오 
      Alias는 회원번호, 회원명, 주소, 마일리지
    SELECT MEM_ID AS 회원번호, 
    MEM_NAME AS 회원명,
    MEM_ADD1||' '||MEM_ADD2 AS 주소, 
    MEM_MILEAGE AS 마일리지
    FROM MEMBER
    WHERE MEM_ADD1 LIKE '충남%';

사용예)상품테이블에서 상품명에 '삼성'이 있는 상품을 조회하시오
       Alias는 상품코드,상품명,거래처코드,매입가격
       SELECT PROD_ID AS 상품코드,
       PROD_NAME AS 상품명,
       PROD_BUYER AS 거래처코드,
       PROD_COST AS 매입가격
       FROM PROD
       WHERE PROD_NAME LIKE '%삼성%';

 

---

6) EXISTS :행이 존재하면 전체가 참이다.
     (뒤에 서브쿼리가 와야함)

사용예)부서번호 80번부서의 평균급여보다 더 많은 급여를 받는 사원들을 조회하시오
       Alias는 사원번호, 사원명, 부서번호, 급여
     
SELECT EMPLOYEE_ID AS 사원번호, 
       EMP_NAME AS 사원명,
       DEPARTMENT_ID AS 부서번호,
       SALARY AS 급여
    FROM HR.EMPLOYEES    
    WHERE SALARY >=(SELECT AVG(SALARY)
                        FROM HR.EMPLOYEES
                       WHERE DEPARTMENT_ID=80)
---------------------------------------------------------
위에와 같은의미
    SELECT A.EMPLOYEE_ID AS 사원번호,
           A.EMP_NAME AS 사원명,
           A.DEPARTMENT_ID AS 부서번호,
           A.SALARY AS 급여
        FROM HR.EMPLOYEES A
       WHERE EXISTS(SELECT 1
                     FROM(SELECT AVG(B.SALARY) AS ASL
                            FROM HR.EMPLOYEES B
                          WHERE B.DEPARTMENT_ID=80) C
                    WHERE C.ASL<A.SALARY)

                            :

                    데이터 생략