WHERE 절에서 사용하는 명령어 D02

- AND 문

SELECT	 EMPLOYEE_ID 
	 	,FIRST_NAME 
		,EMAIL 
		,HIRE_DATE 
		,JOB_ID 
		,LAST_NAME 
FROM	 EMPLOYEES e
WHERE 	 LAST_NAME = 'Smith'
AND 	 FIRST_NAME ='William'
;

-- db 에서는 OR 조건이 잘 안쓰인다.

문제

--1. 
--employees 테이블에서 department_id가 100이고 
--job_id가 FI_MGR인 사람의 모든 데이터 조회

SELECT	 *
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID = 100
AND 	JOB_ID = 'FI_MGR'
;

--2.
--employees 테이블에서 first_name이 Guy인 사람의 
--employee_id, first_name, last_name, job_id 조회

SELECT	EMPLOYEE_ID ,
		FIRST_NAME ,
		LAST_NAME ,
		JOB_ID 
FROM 	EMPLOYEES e 
WHERE 	FIRST_NAME = 'Guy'
;

 --3.
 --employees 테이블에서 department_id가 50이고 
 --managet_id가 121인 사람의 
 --employee_id, first_name, last_name, job_id 조회

SELECT 	EMPLOYEE_ID ,
		FIRST_NAME ,
		LAST_NAME ,
		JOB_ID 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID = 50
AND 	MANAGER_ID = 121
;

- AS

• 별칭, 알리아스 이라고 불린다. 생략가능하다.
• EMPLOYEES e 에서 e도 별칭이다 그치만 테이블에 AS를 붙이는건 안된다.

SELECT	e.EMPLOYEE_ID AS id
FROM 	EMPLOYEES e 
;

- distinct

• 중복제거
• 중복제거 할때 EMPLOYEES_ID 를 SELECT 에 추가하면 중복제거가 안된다.

SELECT 	DISTINCT JOB_ID
FROM 	EMPLOYEES e 
;

- OR 조건문

SELECT 	EMPLOYEE_ID ,
		LAST_NAME ,
		MANAGER_ID ,
		DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID = 50
OR 		MANAGER_ID  = 100
;

- IN 절

• OR와 동일하게 사용 가능하다.

SELECT  	*
FROM 	EMPLOYEES e 
WHERE 	SALARY IN (6500, 7700, 13000)
;

- NOT (= <>, !=)

- NOT

SELECT 	FIRST_NAME ,
		LAST_NAME ,
		DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	NOT (DEPARTMENT_ID = 50)
;

- <>

SELECT 	FIRST_NAME ,
		LAST_NAME ,
		DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID <> 50
;

- !=

SELECT 	FIRST_NAME ,
		LAST_NAME ,
		DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID != 50
;

- 범위 지정

- >=, <=

SELECT 	FIRST_NAME ,
		LAST_NAME ,
		SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY >= 4000
AND 	SALARY <= 8000
;

- between A and B

SELECT 	FIRST_NAME ,
		LAST_NAME ,
		SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY BETWEEN 4000 AND 8000
;

• BETWEEN A AND B 에서 A 보다 작고 B보다 크게만 가능

문제

/* 문1
 * employees 테이블에서 employee_id, first_name, last_anem, job_id를 출력하는데,
 * job_id가 ST_MAN이고, manager_id가 100이고, salary가 5000이상인 사람.
 */

SELECT 	EMPLOYEE_ID ,
		FIRST_NAME ,
		LAST_NAME ,
		JOB_ID 
FROM 	EMPLOYEES e
WHERE 	JOB_ID = 'ST_MAN'
AND 	MANAGER_ID = 100
AND		SALARY >= 5000
;


-- 문2.
-- EMPLOYEES 테이블에서 EMPLOYEE_ID, first_name, last_name, JOB_ID
-- MANAGER_ID, SALARY 를 조회
-- DEPARTMENT_ID가 10 또는 30 또는 100 또는 90에 속하고,
-- 급여(SALARY)가 5000에서 10000사이 이고
-- 매니저(MANAGER_ID)가 100이 아닌 사람을 조회


SELECT 	EMPLOYEE_ID ,
		FIRST_NAME ,
		LAST_NAME ,
		JOB_ID , MANAGER_ID , SALARY 
		DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID IN (10, 30, 100, 90)
AND		SALARY BETWEEN 5000 AND 10000
AND 	MANAGER_ID != 100
;

- LIKE : 이름이 d로 끝나는 사람

SELECT 	FIRST_NAME , LAST_NAME , EMPLOYEE_ID 
FROM 	EMPLOYEES e 
WHERE 	FIRST_NAME LIKE '%d'
;
-- 자바에서 * 로 쓰이던게 디비에선 % 이다

- LIKE : __a인 사람

SELECT 	FIRST_NAME , LAST_NAME , EMPLOYEE_ID 
FROM 	EMPLOYEES e 
WHERE 	FIRST_NAME LIKE '__a%'
;

• _ 언더바는 자리? 칸? 문자? 한자리를 의미한다.

- IS NULL : NULL 값을 가지고 있는거 가져올때

SELECT 	FIRST_NAME , LAST_NAME ,
		EMPLOYEE_ID , COMMISSION_PCT 
FROM  EMPLOYEES e 
WHERE COMMISSION_PCT IS NULL

;

- IS NOT NULL

SELECT  FIRST_NAME , LAST_NAME , COMMISSION_PCT 
FROM 	EMPLOYEES e 
WHERE 	COMMISSION_PCT IS NOT NULL
;

- ORDER BY ASC : 오름차순

SELECT  FIRST_NAME , LAST_NAME , COMMISSION_PCT 
FROM 	EMPLOYEES e 
WHERE 	COMMISSION_PCT IS NOT NULL
ORDER BY FIRST_NAME ASC
;

• ASC는 ORDER문 마지막에 작성(생략가능)

- ORDER BY DESC : 내림차순

SELECT  FIRST_NAME , LAST_NAME , COMMISSION_PCT 
FROM 	EMPLOYEES e 
WHERE 	COMMISSION_PCT IS NOT NULL
ORDER BY FIRST_NAME DESC