Oracle Database (SELECT - 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(or),  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/%';