SELECT문 - 2

/*
    SELECT
    FROM
    WHERE 조건절   -> if

    비교연산자( > < >= <= = != <> )
    NULL, IS NULL, IS NOT NULL
    &&(AND), ||(OR), ( )
*/
-- 이름 -> Julia
SELECT first_name, last_name, salary
FROM employees
WHERE first_name = 'Julia';
 
-- 급여가 $9000 이상인 사원
SELECT first_name, salary
FROM employees
WHERE salary >= 9000;
 
-- 이름이 Shanta 보다 큰이름
SELECT first_name
FROM employees
WHERE first_name >= 'Shanta';
 
-- 이름의 첫 스팰링이 J보다 큰 이름
SELECT first_name, salary
FROM employees
WHERE first_name >= 'J';
 
-- 매니저가 없는 사원
SELECT first_name, last_name
FROM employees
WHERE manager_id = '';      -- '' != null
 
SELECT first_name, last_name
FROM employees
WHERE manager_id IS NULL;
 
SELECT first_name, manager_id
FROM employees
WHERE manager_id IS NOT NULL;
 
-- && == AND
SELECT first_name, last_name
FROM employees
WHERE first_name = 'Shanta'
    AND last_name = 'Vollman';
 
-- || == OR
SELECT first_name, last_name
FROM employees
WHERE first_name = 'Shanta'
    OR last_name = 'Vollman';
 
-- 이름 John, 월급이 5000이상
SELECT first_name, salary
FROM employees
WHERE first_name = 'John'
    AND salary >= 5000;
 
-- 2007년 12월 31일 이후에 입사한 사원을 출력
SELECT first_name, hire_date
FROM employees
WHERE hire_date > '07/12/31';       -- 071231
 
SELECT first_name, hire_date
FROM employees
WHERE hire_date > TO_DATE('20071231', 'YYYYMMDD');
 
-- ALL(=AND), ANY(=OR)
SELECT
    *
FROM employees
WHERE first_name = ALL('Julia', 'John');
--where first_name = 'Julia' AND first_name = 'John'
 
SELECT
    *
FROM employees
WHERE first_name = ANY('Julia', 'John');
 
SELECT first_name, salary
FROM employees
WHERE salary = ANY(8000, 3200, 6000);
 
-- IN, NOT IN
SELECT first_name, salary
FROM employees
WHERE salary IN(8000, 3200, 6000);
 
SELECT first_name, salary
FROM employees
WHERE salary NOT IN(8000, 3200, 6000);
 
SELECT first_name
FROM employees
WHERE first_name IN('Julia', 'John');
 
-- BETWEEN 범위 연산자
SELECT first_name, salary
FROM employees
-- WHERE salary >= 3200 AND salary <= 9000;
WHERE salary BETWEEN 3200 AND 9000;
 
SELECT first_name, salary
FROM employees
-- WHERE salary >= 3200 AND salary <= 9000;
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 > '05/12/31' AND hire_date < '07/01/01';
 
SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '06%';
 
SELECT first_name, phone_number
FROM employees
WHERE phone_number LIKE '590%';