WHERE

--WHERE 절 == IF == 조건절    

표현식
    비교 연산자 (>, <, >=, <=, =, !=, <>( = !=와동일)
    NULL, IS NULL, IS NOT NULL
    ( ), NOT, AND(&&), OR(||)
    

 

CONCAT( ) == | |

lower( ), upper( )

AND 조건 OR 조건

ALL(AND), ANY(OR)

IN, NOT IN 정해진 값을 검색

BETWEEN / NOT BETWEEN 범위 연산자 

LIKE  %  _   (날짜, 이름, 번호, 게시판검색어 등)

MOD(  ,  )  나머지

DECODE(A가, B면, C, ...... ) AS명

 

 

 

 

기본 WHERE

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';

SELECT first_name
FROM employees
WHERE first_name >= 'a';    --아무것도 안나옴 -> ASCII CODE 값 이후 데이터들

SELECT first_name, last_name
FROM employees
WHERE manager_id Is NULL;   --manager_id = ''; 라고는 에러

SELECT first_name, last_name
FROM employees
WHERE manager_id Is NOT NULL;

 

 

 

 

 

CONCAT( ) == | |

--문제3) DEPARTMENTS 테이블에서 부서번호와 부서이름, 부서이름과 위치번
--호를 합하여 출력하도록 하라.
SELECT concat(department_id, department_name) , concat(department_name, location_id)
FROM departments;

 

 

 

 

 

lower( ), upper( )

--문제1 EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고 사원번호,
--성명, 담당업무(소문자로),부서번호를 출력하라.
 SELECT employee_id, last_name, LOWER(job_id), department_id
 FROM employees
 WHERE LOWER(last_name) = 'king';

--문제2) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호,
--성명, 담당업무(대문자로),부서번호를 출력하라. upper
SELECT employee_id, last_name, upper(job_id), department_id
FROM employees
WHERE UPPER(last_name) = 'KING';

 

 

 

 

AND 조건 OR 조건

SELECT first_name, last_name
FROM employees
WHERE first_name = 'Shanta'
    AND last_name = 'Vollman';

SELECT first_name, last_name
FROM employees
WHERE first_name = 'Shanta'
    OR first_name = 'John';

--이름 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';

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');
-- WHERE first_name = 'Julia' OR first_name = 'John';


SELECT first_name, salary
FROM employees
WHERE salary = ANY(8000, 3200, 6000);   --급여가 3가지종류중 하나인사람

 

 

 

 

IN, NOT IN 정해진 값을 검색

 

SELECT first_name, salary
FROM employees
WHERE salary IN(8000,3200,6000);    --IN은 ANY와 동일 

SELECT first_name, salary
FROM employees
WHERE salary NOT IN(8000,3200,6000);    --세 종류를 제외한 나머지사람들

SELECT *
FROM employees
WHERE first_name in('Julia', 'John');   --두 사원의 정보 

 

 

 

BETWEEN / NOT BETWEEN 범위 연산자
-> salary >= 3200 AND salary <= 9000
-> salary BETWEEN 3200 AND 9000 (윗줄과 동일)

 

SELECT first_name, salary
FROM employees
WHERE salary > 3200 AND salary <= 9000;

SELECT first_name, salary
FROM employees
--WHERE salary > 3200 AND salary <= 9000;
WHERE salary BETWEEN 3200 AND 9000;     --윗줄의 BETWEEN 버전

SELECT first_name, salary
FROM employees
--WHERE salary < 3200 OR salary > 9000;
WHERE salary NOT BETWEEN 3200 AND 9000;     --윗줄의 반대 범위(NOT BETWEEN)

 

 

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
FROM employees
WHERE first_name LIKE 'A%';     --김씨는 '김%' (A뒤는 신경안씀)
--WHERE first_name LIKE '%A';   -- A앞자리 신경안씀

SELECT first_name
FROM employees
WHERE first_name LIKE '%e%';     --e가 맨 앞/ 중간/ 끝 상관없이 e가 포함되면 나옴

SELECT first_name, hire_date
FROM employees
WHERE hire_date > '05/12/31' AND hire_date < '06/02/01';

SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '06/01%';      --위와 동일(날짜는 아무때나)

SELECT first_name, phone_number
FROM employees
WHERE phone_number LIKE '590%';     --지역번호 590인사람들 조회

 

 

 

 

 

mod(  , ) 나머지

--문제8) EMPLOYEES 테이블에서 부서번호가 80인 사람의 급여를 30으로 나눈
--나머지를 구하여 출력하라.
SELECT mod(salary,30), department_id
FROM employees
WHERE department_id = 80;

 

 

DECODE

SELECT
EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY,DECODE(JOB_ID,'IT_PROC',
SALARY*1.1,
'ST_MAN', SALARY*1.15,'SA_MAN', SALARY*1.2, SALARY) d_sal
FROM EMPLOYEES
ORDER BY SALARY DESC;

 

------------------

EX) emp테이블에서 급여가 2,000와 5,000 사이고 부서번호가 10 또는 30인 사원의 이름과 급여,부서번호를 나열하시오.

Query

SELECT ename, sal, deptno
FROM emp
WHERE (sal BETWEEN 2000 AND 5000)
    AND (deptno = ANY(10,30));

SELECT ename, sal, deptno
FROM (SELECT ename, sal, deptno
        FROM emp
        WHERE sal BETWEEN 2000 AND 5000)
WHERE deptno = 10 OR deptno = 30;

 

 

 

 

 

---------------

SELECT employee_id, first_name, job_id, salary, department_id
FROM employees
WHERE job_id = 'ST_MAN' AND salary >= 7000;

SELECT employee_id, first_name, job_id, salary, department_id
FROM (SELECT employee_id, first_name, job_id, salary, department_id
        FROM employees
        WHERE job_id = 'ST_MAN')
WHERE salary >= 7000;