21일차//[Oracle] / SELECT FROM WHERE✅ /standard Function(3)

/*
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%';