WHERE 과 조건 함수

-- WHERE 절 == IF문
/*
    표현식 
        비교 연산자( = < > != >= <= <> )
        NULL, IS NULL, IS NOT NULL
        ( ), NOT, AND, OR
*/
 
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME = 'Julia'; 
 
SELECT FIRST_NAME, LAST_NAME, SALARY    
FROM EMPLOYEES
WHERE SALARY >= 9000;
 
SELECT FIRST_NAME, LAST_NAME, SALARY    
FROM EMPLOYEES
WHERE FIRST_NAME >= 'Shanta';
 
SELECT FIRST_NAME, LAST_NAME, SALARY    
FROM EMPLOYEES
WHERE FIRST_NAME >= 'a';
 
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE MANAGER_ID = '';
 
SELECT FIRST_NAME, LAST_NAME, MANAGER_ID, SALARY
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;   -- = NULL   != ''
 
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME = 'Shanta'
    OR FIRST_NAME = 'John';
 
SELECT FIRST_NAME, LAST_NAME, SALARY    
FROM EMPLOYEES
WHERE FIRST_NAME = 'Shanta'
    OR FIRST_NAME = 'John'
        AND SALARY > 3000;
        
SELECT *
FROM EMPLOYEES
WHERE HIRE_DATE > '07/12/31';
 
 
-- ALL(AND), ANY(OR)
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME = ALL('Julia', 'John');
 
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME = 'Julia' AND FIRST_NAME = 'John';
 
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME = ANY('Julia', 'John', 'Steven');
 
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME = 'Julia' OR FIRST_NAME = 'John';
 
 
-- IN, NOT IN
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY IN(8000, 3200, 6000);
 
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME IN('Julia', 'John');
 
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME NOT IN('Julia', 'John');
 
SELECT FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IN(120, 100);
 
-- EXISTS
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE EXISTS(SELECT 1 FROM DUAL);
 
SELECT FIRST_NAME, LAST_NAME, SALARY, JOB_ID
FROM EMPLOYEES A
WHERE EXISTS(SELECT 1 FROM DUAL
             WHERE A.JOB_ID = 'IT_PROG');    
    
SELECT FIRST_NAME, LAST_NAME, SALARY, JOB_ID
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';
 
-- BETWEEN 범위 연산자
/*
    SALARY >= 3200 AND SALARY <= 9000
    BETWEEN 3200 AND 9000
*/
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY BETWEEN 3200 AND 9000;
 
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY NOT BETWEEN 3200 AND 9000;
 
-- LIKE
SELECT FIRST_NAME    
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'G_ra_d';    --   _ 한글자 
 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'K%y';
 
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE LIKE '06/%';