Oracle SQL - 기타연산자
- 기타연산자 : 복수행 비교연산자(IN, ANY, SOME, ALL, EXISTS), BETWEEN, LIKE 등 제공
-- 복수개 데이터 >= 오른쪽 연산자 = 그 중의 어느하나가 존재할때
-- 원래는 단일 데이터 비교가 원칙
-- 범위를 나타낼때 논리곱 연산자 사용 ' 10< a <330' => a>10 AND a<330 --길어진다
-- BETWEEN : ↑ 너무 길어서 범위를 나타낼때 사용
-- LIKE : 문자열에만 쓰는 연산자. => 날짜나 숫자에는 쓰지말자
- 다중 값의 비교연산을 수행(IN, ANY, SOME, ALL, EXISTS)
-- EXISTS는 뒤에 반드시 서브쿼리가와야해서 나중에 배움
- 범위 설정(BETWEEN)
- 패턴비교(LIKE)
1) IN 연산자 -- (or - in 서로 호환 가능)
. 주어진 복수개의 값 중 어느 하나와 일치하면 전체 조건이 참의 결과를 반환
. OR 연산자로 치환 가능
. IN 연산자에는 '='(equal to)기능이 포함되어 있음.
(사용형식)
expr IN(값1,값2, ... 값n)
- 'expr'의 값이 '값1,값2, ... 값n' 중 어느하나와 일치하면 TRUE 반환
- 불연속적인 값의 비교나 불규칙적인 값의 비교에 유리
사용예) 사원테이블에서 30, 70, 80, 100부서에 속한 사원 정보를 조회하시오
Alias는 사원번호,사원명,부서번호,직무코드이며 부서번호 순으로 출력
(OR 연산자 / IN 연산자)
SELECT
EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서번호,
JOB_ID AS 직무코드
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID = 30 OR DEPARTMENT_ID = 70 OR
DEPARTMENT_ID = 80 OR DEPARTMENT_ID = 110
ORDER BY 3;
SELECT
EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서번호,
JOB_ID AS 직무코드
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID IN(30,70,80,110)
ORDER BY 3;
사용예) 2020년 4월, 6월, 7월 매출집계를 조회하시오. Alias는 월,매출수량 합계,매출금액 합계
SELECT SUBSTR(A.CART_NO,5,2)||'월' AS 월,
SUM(A.CART_QTY) AS "매출수량 합계",
TO_CHAR(SUM(A.CART_QTY * B.PROD_PRICE),'999,999,999') AS "매출금액 합계"
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
-- (조인조건)카트테이블의 PROD 상품코드가지고 상품테이블의 상품코드를 조회
AND SUBSTR(A.CART_NO,1,6) IN('202004','202006','202007')
--CART_NO 장바구니 날짜 포함/년도4자리와 월 2자리
GROUP BY SUBSTR(A.CART_NO,5,2) -- 같은 값끼리 모아라....
ORDER BY 1;
-- 단가가 있는 테이블 PROD 상품테이블
-- 상품코드를 가지고 상품테이블의(부모테이블) => 판매단가 PROD_PRICE 찾아서 JOIN
-- SUBSTR문자열
-- 그룹화 GROUP BY /년도는 2020년으로 고정 => 월만 출력
사용예) 회원테이블에서 회원들의 보유 마일리지가 4000이상인 회원들의
2020년 구매현황을 조회하시오. Alias 는 회원번호,회원명,구매금액 합계
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
SUM(B.CART_QTY * C.PROD_PRICE) AS "구매금액 합계"
FROM MEMBER A, CART B, PROD C
-- 회원은 MEMBER테이블, 구매수량은 CART테이블, 금액단가는 PROD 테이블
-- A, B, C = 테이블 별칭 //2이상의 테이블의 같은 컬럼명이 있을때 // FROM절에서만 가능함.
WHERE B.CART_MEMBER=A.MEM_ID
AND B.CART_PROD=C.PROD_ID
AND B.CART_NO LIKE '2020%'
AND B.CART_MEMBER IN(SELECT MEM_ID
FROM MEMBER
WHERE MEM_MILEAGE >=4000)
GROUP BY A.MEM_ID,A.MEM_NAME
ORDER BY 1;
====================================================================
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
SUM(B.CART_QTY*C.PROD_PRICE) AS 구매금액합계
FROM MEMBER A, CART B, PROD C --=> 3개이므로 조인은 적어도 2개 이상
-- A,B,C (테이블 별칭) : 두 개이상의 테이블에서 컬럼명이 같으면 꼭 써야 함
-- FROM 절에서만 별칭 선언한다. FROM 테이블명 별칭(,테이블명 별칭,...)
-- SELECT 절과 WHERE 절에서 참조해서 적을 수 잇다. A.MEM_ID
WHERE B.CART_MEMBER=A.MEM_ID
AND B.CART_PROD=C.PROD_ID
AND B.CART_NO LIKE '2020%'
AND B.CART_MEMBER IN(SELECT MEM_ID
FROM MEMBER
WHERE MEM_MILEAGE >= 4000)
GROUP BY A.MEM_ID,A.MEM_NAME
ORDER BY 1;
-- 오류 주의 별칭에 "구매금액 합계" 띄어쓰기 조심하자
2) ANY(SOME) 연산자 --'='기능이 없다.
. 주어진 값들 중 어느 하나와 ANY(SOME) 앞에 기술된 관계연산자의 조건을 만족하면 TRUE
. IN연산자는 '='기능이 포함되어 있지만 ANY(SOME)은 연산자 앞에 관계연산자를 반드시 기술해야함
(즉, IN은 =ANY, =SOME과 같다)
. ANY와 SOME은 완전히 동일한 기능 제공
사용형식)
expr 관계연산자 ANY|SOME (값1,값2,...,값n)
사용예) 회원테이블에서 직업이 '자영업'인 회원들이 보유한 제일적은 마일리지보다
많은 마일리지를 보유한 회원들을 조회하시오. Alias는 회원번호,회원명,직업,마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_JOB AS 직업,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_MILEAGE >ANY(자영업회원들의 마일리지);
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_JOB AS 직업,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_MILEAGE >ANY(SELECT MEM_MILEAGE -- SOME을 쓴것과 같다.
FROM MEMBER
WHERE MEM_JOB='자영업') --3700,5300,1500,4300 //1500보다
ORDER BY 4; -- 1500보다 많은사람만 출력
3) ALL 연산자 (조건이 AND로 연결된 것으로 간주) -- 모두 다 만족이 되어야 참이다.
. 주어진 값 모두와 ALL 앞에 기술된 관계연산자의 조건을 만족하면 TRUE를 반환
. IN연산자는 '='기능이 포함되어 있지만 ALL 은 연산자 앞에 관계연산자를 반드시 기술해야함
. ALL연산자는 =ALL을 사용할 수 없다(다가속성) -- 거주지가 대전이면서 대구이면서 서울인 사람은 없다.
-- <,> 부등호 관계만 사용 가능.
사용형식)
expr 관계연산자 ALL값(값1, 값2... 값n)
사용예) 회원테이블에서 직업이 '자영업'인 어느 회원들이 보유한 마일리지보다 많이 보유한 마일리지를
보유한 회원들을 조회하시오. Alias는 회원번호,회원명,직업,마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_JOB AS 직업,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_MILEAGE !=ALL(SELECT MEM_MILEAGE -- 같지 않은 마일리지 출력
FROM MEMBER
WHERE MEM_JOB='자영업')
ORDER BY 4;
4) BETWEEN 연산자
. 범위를 지정할 때 사용
. 모든 타입의 데이터에 사용가능
사용형식)
expr BETWEEN 값1 AND 값2
. expr에 저장된 내용이 '값1'에서 '값2' 사이의 값이면 TRUE 반환
. AND 연산자로 대치 가능
사용예) 상품테이블에서 분류코드가 'P200'번대에 속한 상품정보를 조회하시오
Alias는 상품번호,상품명,분류코드,매입단가,매출단가
SELECT PROD_ID AS 상품번호,
PROD_NAME AS 상품명,
PROD_LGU AS 분류코드,
PROD_COST AS 매입단가,
PROD_PRICE AS 매출단가
FROM PROD
-- WHERE PROD_LGU>='P200' AND PROD_LGU<'P299';
WHERE PROD_LGU BETWEEN 'P200' AND 'P299'; (BETWEEN A AND B)
** 사원테이블에 퇴직일자(RETIRE_DATE)컬럼 날짜타입으로추가시키시오
ALTER TABLE HR.EMPLOYEES ADD(RETIRE_DATE DATE);
** 사원테이블의 입사일자(HIRE_DATE)를 10년 후의 값으로 변환하시오
UPDATE HR.EMPLOYEES
SET HIRE_DATE=ADD_MONTHS(HIRE_DATE,120);
-- 10년후=120개월/ ADD_MONTHS 주어진 날짜에 +개월수 더한값 반환
COMMIT;
SELECT EMP_NAME, EXTRACT(YEAR FROM SYSDATE)HIRE_DATE
FROM HR.EMPLOYEES
사용예) 사원테이블에서 근속년수에 따른 보너스를 지급하려한다.
지급액과 보너스를 조회하시오.
보너스는 근속년수가 5년 미만인 사원은 기본급의 15%,
5~10년인 사원은 25%
그 이상인 사원은 40% 지급한다.
지급액 = 기본급 + 보너스
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 입사일,
EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) AS 근속년수,
-- 오늘 - 입사일 = 근속년수
SALARY AS 기본급,
CASE WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) <5 THEN
ROUND(SALARY * 0.15)
WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) >=5 AND
EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) <=10 THEN
ROUND(SALARY * 0.25)
ELSE
ROUND(SALARY * 0.40)
END AS 보너스,
CASE WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) <5 THEN
SALARY+ROUND(SALARY * 0.15)
WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) >=5 AND
EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) <=10 THEN
SALARY+ROUND(SALARY * 0.25)
ELSE
SALARY+ROUND(SALARY * 0.40)
END AS 지급액
FROM HR.EMPLOYEES; -- 전직원 조회여서 WHERE절 없음
---------------------------------------------------------------------------------------------------
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 입사일,
EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) AS 근속년수, -- 오늘 - 입사일
SALARY AS 기본급,
CASE WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) <5 THEN
ROUND(SALARY * 0.15)
WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) BETWEEN 5 AND 10 THEN
ROUND(SALARY * 0.25)
ELSE
ROUND(SALARY * 0.40)
END AS 보너스,
CASE WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) <5 THEN
SALARY+ROUND(SALARY * 0.15)
WHEN EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) BETWEEN 5 AND 10 THEN
SALARY+ROUND(SALARY * 0.25)
ELSE
SALARY+ROUND(SALARY * 0.40)
END AS 지급액
FROM HR.EMPLOYEES;
사용예) 매입테이블에서 2020년 3월 제품별 매입집계를 조회하시오
-- 제품별 : 같은 제품 끼리 => 집계함수 GROUP BY
상품코드,상품명,매입수량,매입금액
SELECT A.BUY_PROD AS 상품코드,
B.PROD_NAME AS 상품명,
SUM(A.BUY_QTY) AS 매입수량,
SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
FROM BUYPROD A, PROD B
-- WHERE A.BUY_DATE >='20200301' AND A.BUY_DATE <='20200331'
WHERE A.BUY_DATE BETWEEN TO_DATE('20200301') AND TO_DATE('20200331')
AND A.BUY_PROD=B.PROD_ID
GROUP BY A.BUY_PROD, B.PROD_NAME
ORDER BY 1;
5) LIKE 연산자
. 패턴을 비교하여 같은 문자열 패턴이면 TRUE을 반환
. 패턴을 구성하는 문자(와일드 카드로) '&'와 '_'가 사용
. '%'
- '%'가 쓰인 위치 이후의 문자와 대응(공백도 대응)
- ex) '김%' : '김'으로 시작하는 모든 문자와 대응
'%김%' : 문자열중 '김'이라는 글자가 존재하면 참(true) 반환
'%김' : '김'으로 끝나는 모든 문자열과 대응
. '_'
- '_'가 쓰인 위치 이후의 하나의 문자와 대응
- ex) '김_' : '김'으로 시작하는 2글자 문자열과 대응
'_김_' : 3글자 문자열중 중간 글자가 '김'인 문자열과 대응
'_김' : '김'으로 끝나는 2글자 문자열과 대응
사용형식)
expr LIKE '패턴문자열'
. 문자열 연산자임(숫자,날짜 자료는 사용 자제)
. 연산결과 많은 자료가 반환되므로 Query의 수행 효율성은 낮음
사용예) 회원테이블에서 대전에 거주하는 회원을 조회하시오
Alias는 회원번호,회원명,주소,마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_ADD1||' '||MEM_ADD2 AS 주소,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_ADD1 LIKE '대전%';
사용예) 장바구니 테이블에서 2020년 4월 판매현황을 출력하시오
Alias는 날짜,회원번호, 상품코드, 판매수량
SELECT SUBSTR(CART_NO,1,8) AS 날짜,
CART_MEMBER AS 회원번호,
CART_PROD AS 상품코드,
CART_QTY AS 판매수량
FROM CART
WHERE CART_NO LIKE '202004%' -- 202004~로 시작하는 문자열 검색
ORDER BY 1;
'DataBase' 카테고리의 다른 글
| 221111 데이터 베이스 오라클 : 자료타입 (0) | 2022.11.16 |
|---|---|
| 22111~14 데이터 베이스 오라클 SQL : 연산자 (0) | 2022.11.16 |
| 데이터베이스 04일 정리 - 정규화 과정과 함수종속성 (0) | 2022.11.12 |
| 데이터베이스 03일 정리 (0) | 2022.11.11 |
| 데이터베이스 02일 정리 (0) | 2022.11.11 |