23.10.06. Oracle SQL WHRER 절, IN 연산자

■ WHERE 절 (조건절)
- 행을 제한하는 절
- 기준컬럼이 문자열, 날짜열이면 비교값은 작은 따옴표로 묶어야 한다.
- 영문자는 대소문자를 구분한다.
- 날짜형식은 지역, 언어에 따라 기본 날짜 표시형식이 다르다. 한국(RR/MM/DD), 미국(DD-MON-RR) 
- 비교연산자
    =(같다), >(크다), >=(크거나 같다), <(작다), <=(작거나 같다), !=,^=<>(같지 않다)
- 논리연산자
    AND : 두 조건이 모두 참일 경우 TRUE 
    OR : 두 조건 중에 하나가 참일 경우 TRUE 
    NOT : FALSE 경우 TRUE, TRUE 일 경우 FALSE

SELECT 컬럼이름, 컬럼이름, ..
FROM 테이블명
WHERE 기준컬럼 비교연산자 비교값 ; 

SELECT 컬럼이름, 컬럼이름, ..
FROM 테이블명
WHERE 기준컬럼 비교연산자 비교값 
AND 기준컬럼 비교연산자 비교값; 

SELECT 컬럼이름, 컬럼이름, ..
FROM 테이블명
WHERE 기준컬럼 비교연산자 비교값 
OR 기준컬럼 비교연산자 비교값;

DESC employees

SELECT *
FROM hr.employees
WHERE department_id = 20; 

SELECT *
FROM hr.employees
WHERE last_name = 'King'; -- 비교값은 대소문자 구분 // 데이터품질의 여부를 체크함

SELECT *
FROM hr.employees
WHERE last_name = 'KING';

SELECT *
FROM hr.employees
WHERE hire_date = '03/06/17'; -- 날짜형에는 작은 따옴표로 묶어야 한다.

-- 비교연산자
SELECT *
FROM hr.employees
WHERE salary > 10000; 

SELECT *
FROM hr.employees
WHERE department_id <> 50;

-- 논리연산자
SELECT *
FROM hr.employees
WHERE department_id = 20
OR department_id = 10; 

SELECT *
FROM hr.employees
WHERE department_id = 20
OR salary >= 10000; 

SELECT *
FROM hr.employees
WHERE department_id = 20;

SELECT *
FROM hr.employees
WHERE salary >= 10000; 

SELECT *
FROM hr.employees
WHERE department_id = 20
AND salary >= 10000;

-- 문자형 비교연산자
SELECT *
FROM hr.employees
WHERE last_name >= 'Abel' 
AND last_name <= 'Austin';

SELECT *
FROM hr.employees
WHERE last_name NOT BETWEEN 'Abel' AND 'Austin';

-- 날짜형 비교연산자
SELECT *
FROM hr.employees
WHERE hire_date >= '01/01/01'
AND hire_date <= '02/12/31' ;

SELECT *
FROM hr.employees
WHERE hire_date BETWEEN '01/01/01' AND '02/12/31' ;

SELECT *
FROM hr.employees
WHERE hire_date < '01/01/01'
OR hire_date > '02/12/31' ;

SELECT *
FROM hr.employees
WHERE hire_date NOT BETWEEN '01/01/01' AND '02/12/31' ;

SELECT * 
FROM hr.employees
WHERE department_id = 10
OR department_id = 20
OR department_id = 30 ;

 
[문제] EMPLOYEES 테이블에서 SALARY 가 2500 ~ 3500 인 사원들의 정보를 출력해주세요. 

SELECT *
FROM hr.employees
WHERE salary >= 2500 
AND salary <= 3500;

SELECT *
FROM hr.employees
WHERE salary BETWEEN 2500 AND 3500;

BETWEEN 하한값 AND 상한값
- 범위 조건을 사용하여 값의 범위에 따라 행을 추출
>= 하한값 AND <= 상한값 

[문제] EMPLOYEES 테이블에서 SALARY 가 2500 ~ 3500 이 아닌 사원들의 정보를 출력해주세요. 

SELECT *
FROM hr.employees
WHERE salary < 2500 
OR salary > 3500;

SELECT *
FROM hr.employees
WHERE salary NOT BETWEEN 2500 AND 3500;

SELECT *
FROM hr.employees
WHERE salary NOT BETWEEN 2500 AND 3500 -- 한 구절만 NOT 
AND department_id != 20 ;

■ IN 연산자
- 각 목록의 값과 일치하는 값을 추출할 때 사용
- 기준컬럼 = 비교값 OR 기준컬럼 = 비교값

SELECT * 
FROM hr.employees
WHERE department_id IN (10, 20, 30); 

SELECT * 
FROM hr.employees
WHERE department_id NOT IN (10, 20, 30); 

SELECT * 
FROM hr.employees
WHERE department_id != 10
AND department_id != 20
AND department_id != 30;

- 논리연산자 우선 순위
NOT > AND > OR

SELECT * 
FROM hr.employees
WHERE department_id = 10 -- 급여의 조건절과 관련없이 출력
OR department_id = 20 -- 급여의 조건절과 관련없이 출력
OR department_id = 30 
AND salary > 5000; -- department_id=30이면서 salary > 5000 1번 수행

SELECT * 
FROM hr.employees
WHERE (department_id = 10
OR department_id = 20
OR department_id = 30) -- ()로 우선순위 설정 
AND salary > 5000;

SELECT * 
FROM hr.employees
WHERE department_id IN (10,20,30)
AND salary > 5000;