Oracle의 다양한 문법들- 1

SELECT * FROM EMPLOYEES e ;

SELECT EMPLOYEE_ID , FIRST_NAME , LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME = 'Smith'
AND FIRST_NAME = 'William'
;

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

SELECT  *
FROM	EMPLOYEES
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이고
   manager_id가 121인 사람의
   employee_id, first_name, last_name, job_id 조회

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

-- 별칭, 알리아스(테이블이 하나일 땐 생략도 가능), as도 생략 가능

SELECT	e.EMPLOYEE_ID AS "ID", 
		SALARY "SAL"
FROM	EMPLOYEES e
;

-- distinct : 중복제거

SELECT	DISTINCT DEPARTMENT_ID  
FROM 	EMPLOYEES e 
;

-- 비교문
-- >=

SELECT	*
FROM 	EMPLOYEES e
WHERE	SALARY >= 5000
;

-- AND
-- OR
-- 부서가 50 또는 부서가 30번인 사원

SELECT  *
FROM	EMPLOYEES e 
WHERE   DEPARTMENT_ID = 50
OR      DEPARTMENT_ID = 30
;

-- NOT

SELECT	*
FROM	EMPLOYEES e 
WHERE 	NOT DEPARTMENT_ID = 50
;

-- NOT -> <>

SELECT	*
FROM 	EMPLOYEES e 
WHERE   DEPARTMENT_ID <> 50
;

-- NOT -> !=

SELECT 	*
FROM 	EMPLOYEES e 	
WHERE   DEPARTMENT_ID  != 50
;

-- salary 4000 ~ 8000 = BETWEEN A AND B

SELECT 	*
FROM 	EMPLOYEES e 
WHERE 	SALARY >= 4000
AND 	SALARY <= 8000
;

SELECT  *
FROM 	EMPLOYEES e 
WHERE 	SALARY BETWEEN 4000 AND 8000
;

-- OR

SELECT	* 
FROM  	EMPLOYEES e 
WHERE 	SALARY  = 6500
OR 		SALARY  = 7700
OR 		SALARY  = 13000
;

-- IN 절(OR와 동일)

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

1.employees 테이블에서
employee_id, first_name, last_name, 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 
   FROM   EMPLOYEES e 
   WHERE  DEPARTMENT_ID IN (10, 30, 90, 100)
   AND 	SALARY  BETWEEN 5000 AND 10000
   AND		MANAGER_ID != 100
   ;

-- LIKE : D로 시작하는 사람

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

-- LIKE : d로 끝나는 사람

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

-- 세번째 글자가 a인 사람

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

-- IS NULL = 빈 값인 사람 찾기

SELECT 	*
FROM 	EMPLOYEES e
WHERE 	COMMISSION_PCT IS NULL 
;

-- IS NOT NULL

SELECT  *
FROM 	EMPLOYEES e 
WHERE 	e.COMMISSION_PCT IS NOT NULL
;

-- 정렬, ORDER BY ASC : 오름차순. WHERE 위에 붙을 수 없고, 가장 마지막에 붙음, ASC는 생략 가능

SELECT  EMPLOYEE_ID , FIRST_NAME , LAST_NAME , DEPARTMENT_ID 
FROM 	EMPLOYEES e 
ORDER BY DEPARTMENT_ID , FIRST_NAME ASC
;

-- 정렬, ORDER BY DESC : 내림차순, ASC와 달리 DESC는 생략 불가

SELECT  EMPLOYEE_ID , FIRST_NAME , SALARY 
FROM 	EMPLOYEES e 
ORDER BY SALARY DESC 
;

-- 합계 : SUM

SELECT SUM(SALARY)
FROM 	EMPLOYEES e 
;

-- COUNT(컬럼) or COUNT(*)

--107명
SELECT 	count(EMPLOYEE_ID) AS "empCnt"
FROM	EMPLOYEES e 
; 

--106명
SELECT 	COUNT(DEPARTMENT_ID)
FROM	EMPLOYEES e 
;

-- Employees 테이블에서 중복을 제거한 부서 갯수

SELECT COUNT(DISTINCT DEPARTMENT_ID)
FROM   EMPLOYEES e 
;

SELECT	COUNT(FIRST_NAME) , COUNT(DISTINCT FIRST_NAME)  
FROM  	EMPLOYEES e 
;

-- AVG()
-- 6461.831775700934579439252336448598130841

SELECT AVG(SALARY)
FROM 	EMPLOYEES e 
;

-- 부서가 80번인 사원의 급여 평균

SELECT AVG(SALARY)  
FROM   EMPLOYEES e
WHERE  DEPARTMENT_ID = 80
;

-- MAX() 최대값 구하기

SELECT	max(SALARY)
FROM  	EMPLOYEES e 
;

SELECT 	max(HIRE_DATE)
FROM  	EMPLOYEES e 
;

-- MIN() 최소값 구하기
SELECT min(SALARY)
FROM EMPLOYEES e
;

SELECT  MIN(HIRE_DATE)
FROM	EMPLOYEES e 
;

-- ABS, 절대값 구하기

SELECT abs(-23)
FROM   dual
;

-- 반올림 ROUND()

SELECT ROUND(0.1234), round(0.5678) 
FROM dual
;

-- 절삭 TRUNC. 두번째 파라미터도 있다.

SELECT TRUNC(1234.56789, -1)
FROM   dual
;

-- 1. 연봉이 12000 초과되는 직원들의 LAST_NAME 및 연봉을 조회한다.
단, 급여 오름차순으로 조회

SELECT LAST_NAME , SALARY 
FROM   EMPLOYEES e
WHERE  SALARY > 12000
ORDER BY salary ASC 
;

-- 2. 사원번호가 176 인 사람의 LAST_NAME 과 부서 번호를 조회한다.

SELECT LAST_NAME , DEPARTMENT_ID 
FROM   EMPLOYEES e 
WHERE  e.EMPLOYEE_ID = 176
;

-- 3. 연봉이 5000 에서 12000의 범위 이외인 사람들의
LAST_NAME 및 연봉을 조회한다.

SELECT LAST_NAME , SALARY 
FROM   EMPLOYEES e 
WHERE  NOT SALARY BETWEEN 5000 AND 12000
;

-- 4. 2001-01-01 일부터 2001-12-31 사이에 고용된 사원들의
LAST_NAME 사번, 고용일자를 조회한다.

SELECT LAST_NAME , e.EMPLOYEE_ID , HIRE_DATE , TO_CHAR(HIRE_DATE, 'yyyy-mm-dd' )
FROM  EMPLOYEES e 
WHERE TO_CHAR(HIRE_DATE, 'yyyy-mm-dd' ) BETWEEN '2001-01-01' AND '2001-12-31'
;

-- 5. 20 번 및 50 번 부서에서 근무하는 모든 사원들의
LAST_NAME, DEPARTMENT_ID를 조회하고, LAST_NAME을 알파벳순으로 조회한다.

SELECT LAST_NAME, DEPARTMENT_ID 
FROM   EMPLOYEES e 
WHERE  DEPARTMENT_ID = 20
or     DEPARTMENT_ID = 50
ORDER BY LAST_NAME