220411 데이터베이스 SELECT 함수 3

1. SELECT 함수 3

1-1. SUBSTR(문자열, 시작위치, 길이)

SELECT 	SUBSTR('good morning john', 1, 4)	-- 첫번째 자리부터 4글자
FROM 	dual;
-- good 출력됨

SELECT 	SUBSTR('good morning john', 8, 4)	-- 8번째 자리(공백포함)부터 4글자
FROM 	dual;
-- rnin 출력됨

SELECT 	SUBSTR('good morning john', 8)		-- 8번째 자리부터 끝가지
FROM 	dual;
-- rning john 출력됨

SELECT 	SUBSTR('good morning john', -4)		-- 뒤에서부터 4글자
FROM 	dual;
-- john 출력됨

 

1-2. REPLACE(문자열, 문자지정, 대체할문자)

SELECT 	replace('good morning tom', 'morning', 'evening')
FROM 	dual;
-- good evening tom 출력됨

 

1-3. SYSDATE

현재 날짜 및 시간을 가져오는 SYSDATE 함수와 관련 함수들

-- sysdate
SELECT 	SYSDATE 
FROM	dual;
-- 현재 날짜 및 시간을 출력한다.
-- 2022-04-27 02:30:48.000 


-- add months
SELECT 	ADD_MONTHS(sysdate, 7) 
FROM 	dual;
-- SYSDATE를 기준으로 7개월을 더해준다
--2022-11-27 02:31:09.000


-- last_day
SELECT	LAST_DAY(SYSDATE) 	
FROM	dual;
-- SYSDATE를 기준으로 현재 달의 마지막 날짜를 가져온다.(시간은 현재 시각)
-- 2022-04-30 02:34:20.000


-- 날짜 interval
SELECT SYSDATE + (INTERVAL '1' YEAR)
	,  SYSDATE + (INTERVAL '1' MONTH)
	,  SYSDATE + (INTERVAL '1' DAY)
	,  SYSDATE + (INTERVAL '1' HOUR)
	,  SYSDATE + (INTERVAL '1' MINUTE)
	,  SYSDATE + (INTERVAL '1' SECOND)
FROM dual;
-- 각각 1년, 1개월, 1일, 1시간, 1분, 1초가 더해진 값이 출력된다.


-- to_char() : 문자열 변환
SELECT 	TO_CHAR(SYSDATE, 'yyyy/mm/dd') 
FROM 	dual;
-- 2022-04-27 02:35:50.000 > 문자열 형식에 맞춰 2022/04/27로 변환된다.


SELECT	TO_CHAR(SYSDATE, 'yyyymmdd') 
	,	TO_CHAR(SYSDATE, 'yyyy-mm--dd')
	,	TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:mi:ss')		
FROM 	dual;
-- 다양한 방식으로 형식을 지정해줄 수 있다. 시간의 경우 24시간이나 12시간으로 지정 가능


-- to_date()
SELECT  TO_DATE('2022/04/11')
FROM 	dual;
-- 문자열을 날짜로 표현해준다. 
-- 2022-04-11 00:00:00.000
-- 인터넷으로 추가적으로 검색해보니, 아래와 같이 문자열의 날짜 형식을 따로 지정해주더라.
-- 문자열과 형식이 맞지 않으면 에러가 나는데, 
-- 위에처럼 아예 안 써주면 에러가 나지 않는 이유는 잘 모르겠다.

SELECT  TO_DATE('2022/04/11', 'yyyy/mm/dd')
FROM 	dual;

 

1-4. NVL(널값을 포함한 테이블, 널값을 대체할 숫자) ★

SELECT 	nvl(COMMISSION_PCT, 0)
FROM 	EMPLOYEES e;
-- COMMISION_PCT 테이블의 널값이 0으로 바꾸어져 출력된다.

 

 

1-5. DECODE()

자바의 SWITCH문 역할을 하는 함수

-- department_id가 20이면 marketing -> MA, 60이면 IT -> IT, 90이면 Executive -> EX, 나머지는 etc
SELECT 	DEPARTMENT_ID 
	,	decode(DEPARTMENT_ID, 20, 'MA', 60, 'IT', 90, 'EX', 'ETC') deptvalue
FROM 	DEPARTMENTS d 
;

 

1-6. CASE()

if ~ else if문과 같은 역할 함수

decode()와 case() 모두 동일한 값을 출력하나 case()의 활용성이 더 다양하다.

-- department_id가 20이면 marketing -> MA, 60이면 IT -> IT, 90이면 Executive -> EX, 나머지는 etc
SELECT	DEPARTMENT_ID 
	,	CASE WHEN DEPARTMENT_ID = 20 THEN 'MA'
			 WHEN DEPARTMENT_ID = 60 THEN 'IT'
			 WHEN DEPARTMENT_ID = 90 THEN 'EX'
			 ELSE ''
		END "deptvalue"	-- deptvalue로 알리아스
FROM 	EMPLOYEES e 
;

 

2. 예제

/*
 * 1. EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고(검색조건)
 * 	  사원번호, 성명, 담당업무(소문자 출력), 부서번호 출력
 * 	  -- where last_name = king 
 */
SELECT 	*
FROM 	EMPLOYEES e; 

SELECT 	EMPLOYEE_ID , FIRST_NAME || '	' || LAST_NAME , LOWER(JOB_ID) , DEPARTMENT_ID 
FROM 	EMPLOYEES e
WHERE 	lower(LAST_NAME) = 'king'
;

/*
 * 2. EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고(검색조건)
 * 	  사원번호, 성명, 담당업무(대문자 출력), 부서번호 출력
 * 	  -- where last_name = KING
 */

SELECT 	EMPLOYEE_ID , FIRST_NAME || '	' || LAST_NAME , UPPER(JOB_ID) , DEPARTMENT_ID 
FROM 	EMPLOYEES e
WHERE 	UPPER(LAST_NAME) = 'KING'
;

/*
 * 3. DEPARMENTS 테이블에서 부서번호와 부서이름, 위치번호(LOCATION_ID)를 합하여 출력하도록 하라
 */
SELECT 	DEPARTMENT_ID || DEPARTMENT_NAME || LOCATION_ID "DE info"
FROM 	DEPARTMENTS d 
;

SELECT 	CONCAT(DEPARTMENT_ID, CONCAT(DEPARTMENT_NAME, LOCATION_ID))
FROM 	DEPARTMENTS d 
;


/*
 * 4. EMPLOYEES 테이블에서 30번 부서의 사원번호, 이름과 담당자 아이디를 연결하여 출력하여라
 */
SELECT *
FROM EMPLOYEES e ;

SELECT 	EMPLOYEE_ID || FIRST_NAME || LAST_NAME || MANAGER_ID "Employee info"
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID = 30
;

SELECT 	CONCAT(CONCAT(EMPLOYEE_ID, FIRST_NAME || LAST_NAME), MANAGER_ID) 
FROM	EMPLOYEES e  
WHERE 	DEPARTMENT_ID = 30
;

/*
 * 5. EMPLOYEES 테이블에서 SALARY + SALARY*COMMISSION_PCT이 10000이상이면 'GOOD', 
 * 	  5000이상이면 'AVERAGE', 1이상 5000 미만이면 'BAD', 
 * 	  0이면 'NO GOOD'으로 해서 AS 'GRADE'로 평가를 출력하고,
 * 	  employee_id, first_name, salary, commision_pct, salary+salary*commission_pct 출력  
 */
SELECT 	EMPLOYEE_ID , FIRST_NAME , SALARY , nvl(COMMISSION_PCT, 0)	
	, SALARY+SALARY*nvl(COMMISSION_PCT, 0) SAL
	,	CASE WHEN SALARY+SALARY*nvl(COMMISSION_PCT, 0) >= 10000 THEN 'GOOD'
	 		 WHEN SALARY+SALARY*nvl(COMMISSION_PCT, 0) >= 5000 THEN 'AVERAGE'
			 WHEN SALARY+SALARY*nvl(COMMISSION_PCT, 0) BETWEEN 1 AND 5000 THEN 'BAD'
			 -- SALARY+SALARY*nvl(COMMISSION_PCT, 0) >= 1 THEN 'BAD'도 가능
			 WHEN SALARY+(SALARY*nvl(COMMISSION_PCT, 0)) = 0 THEN 'NO GOOD'
		END "GRADE"
FROM 	EMPLOYEES e 
;