🔮 1) IN 연산자
- 표현식(컬럼or수식)의 값이 주어진 값들중 어느 하나와 일치하면 참(TRUE)를 반환
- 불연속적이거나, 규칙적이지않은 자료를 비교연산할때 사용
- = ANY, =SOME 으로 대치 가능
- OR 연산자로 대치 가능
(사용형식)
expr IN 값(값1[,값2,....])(EX1) 사원테이블에서 부서번호가 10-40번에 속한 사원을 조회하시오
Alias는 사원번호, 사원명, 입사일, 급여
-- 연속적인 범위는 AND, BETWEEN으로 표현 가능
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절은 BETWEEN으로도 표현 가능하다
WHERE DEPARTMENT_ID BETWEEN 10 AND 40;(EX2) 사원테이블에서 부서번호가 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 IN (20,70,80,100)
ORDER BY 3;WHERE절 SOME으로 대체 가능
WHERE DEPARTMENT_ID = SOME (20,70,80,100)
🔮 2) ANY(SOME)연산자
(사용형식)
expr 관계연산자 ANY|SOME(값1[,값2,...])- expr의 값이 ‘값1’에서 ‘값n’까지 중에 ‘관계연산자’의 연산결과를 반환
(EX1) 회원테이블에서 직업이 ‘주부’인 회원들보다 마일리지가 많은 회원들을 조회하시오
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 > ANY (800,1000,2700,8700);
--마일리지 모를땐? 서브쿼리
WHERE MEM_MILEAGE > ANY (SELECT MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB='주부'🔮 3) ALL 연산자
- expr 관계연산자 ALL (값,[,값2,...])
- ‘+’는 사용안됨
(EX1) 회원테이블에서 직업이 ‘학생’인 회원의 마일리지 보다 마일리지가 많은 회원들을 조회하시오
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’의 데이터타입은 일치해야함
- 사용하는 데이터타입은 제한없음
(EX1) 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() : 날짜형으로 변환시켜주는 함수
LAST_DAY() : 달의 마지막 날짜를 구해준다.
WHERE절의 AND연산자는 BETWEEN으로 표현 가능하다.
WHERE BUY_DATE BETWEEN TO_DATE('20050201') AND LAST_DAY(TO_DATE('20050201'));
(EX2) 회원테이블에서 ‘김’씨성을 가진 회원부터 ‘박’씨성의 회원까지 회원번호, 회원명, 나이, 마일리지를 조회하시오
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
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;
(EX4) 상품테이블에서 각 상품별 마일리지를 구하여 PROD_MILEAGE값을 변경하시오
- PROD_MILEAGE 값 = 판매가의 0.2%
ALTER는 구조를 바꾸는것, 우리는 내용을 바꿀거니까 UPDATE문 사용 ( 모든 상품의 마일리지를 바꾸므로 WHERE절 필요 X)
UPDATE PROD
SET PROD_MILEAGE = TRUNC(PROD_PRICE*00002,-1);TRUNC 함수는 주로 소수점 절사 및 날짜의 시간을 없앨 때 사용한다.
(EX4) 상품테이블에서 판매가가 20만원대인 상품들을 조회하시오
Alias는 상품코드,상품명,판매가,마일리지
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_PRICE AS 판매가,
PROD_MILEAGE AS 마일리지
FROM PROD
WHERE PROD_PRICE BETWEEN 200000 AND 299999;🔮 5) LIKE 연산자
- 패턴을 비교할때 사용
- 패턴은 문자열 ‘%’, ‘_’(와일드카드)을 사용하여 구성
- 🚨 문자열 비교 연산자! 숫자,날짜 불가능!
(사용형식)
expr LIKE 패턴문자열(1) ‘%’
- ‘%’가 사용된 위치 이후 모든 문자열과 대응 (EX)
- ‘김%’ : 첫 글자가 ‘김’으로 시작하는 모든 문자열과 대응(참(true)을 반환)
- ‘%김’ : 끝 글자가 ‘김’으로 끝나는 모든 문자열과 대응(참(true)을 반환)
- ‘%김%’ : 문자열 중간에 ‘김’ 문자열이 존재하면 참(true)을 반환
(2) ‘_’
- ‘_’이 사용된 위치에서 한글자와 대응 (EX)
- '김_’ : 첫 글자가 ‘김’이고 2글자로 구성된 문자열과 대응(참(true)을 반환)
- ‘_김’ : 2글자로 구성되고 끝 글자가 ‘김’으로 끝나는 모든 문자열과 대응(참(true)을 반환)
- ‘_김__’ : 3개의 문자열로 구성되며 중간에 ‘김’ 문자가 존재하는 문자열과 대응 (참(true)을 반환)
[CART 테이블]
2 = 8바이트는 상품정보, 5바이트는 고객정보( = 5자리만 가능 하므로 하루 99,999명 수용 ㄱㄴ)
3 = 외래키
수량은 있는데 중요한 상품단가가 없다. >> Join연산 필요!
(EX1) 장바구니테이블에서 2005년 7월 판매현황을 조회하시오
Alias는 일자, 상품코드, 판매수량
SELECT TO_DATE(SUBSTR(CART_NO,1,8)) AS 일자,
--앞자리부터 8글자라는 뜻
CART_PROD AS 상품코드,
CART_QTY AS 판매수량
FROM CART
WHERE CART_NO LIKE '200507%'; --상품코드 앞부분이 바로 날짜!🚨 EX) 매입테이블에서 2005년 7월 매입현황을 조회하시오 >> 형식이 날짜니까 LIKE 불가능
(EX2) 회원테이블에서 ‘충남’에 거주하는 회원을 조회하시오
Alias는 회원번호, 회원명, 주소, 마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_ADD1||''||MEM_ADD2 AS 주소,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_ADD1 LIKE '충남%';(EX3) 상품테이블에서 상품명에 ‘삼성’이 있는 상품을 조회하시오
Alias는 상품코드, 상품명, 거래처코드, 매입가격
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_BUYER AS 거래처코드,
PROD_COST AS 매입가격
FROM PROD
WHERE PROD_NAME LIKE '%삼성%';🔮 6) EXIST : 서브쿼리 개념 필요해서 나중에 공부!
부서번호 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
)