3일차_내장함수, GROUP BY

---------------------------------
SELECT 컬럼리스트,...
 FROM 테이블,...
 WHERE 조건식
 GROUP BY 기준컬럼, ...
 HAVING 조건식
 ORDER BY 기준컬럼,...

------------------------------------
내장 함수

함수란 하나 이상의 SQL 문으로 구성된 서브루틴으로, 코드를 다시 사용할 수 있도록 캡슐화 하는데 사용된다. 오라클 에서는 SQL 언어의 일부로 정의된 기본 제공 함수를 사용하거나 직접 사용자 정의 함수를 만들 수 있다.

------------------------------------
단일 행 함수

함수가 정의된 SQL문이 실행될 때 각각의 행에 대하여 수행되며 행 당 하나의 결과를 리턴 해준다.
SELECT, WHERE, ORDER BY 절에 사용할 수 있다.

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

LOWER(), UPPER() : 대소문자 변환
SELECT LOWER('Steven'), UPPER('Steven')
  FROM dual; --dual 테이블은 실제 데이터가 없을 때 사용하는 가상 테이블

SELECT * FROM employees
 WHERE first_name = 'Steven'; --O
SELECT * FROM employees
 WHERE first_name = 'steven'; --X
SELECT * FROM employees
 WHERE first_name = 'STEVEN'; --X
--> 대소문자를 구분하지 않으면 검색 결과가 나오지 않는다.
--> 검색시에만 대소문자를 무시하도록 하려면 일시적으로 대,소문자를 지정함.
SELECT * FROM employees
 WHERE LOWER(first_name) = LOWER('Steven'); --O
SELECT * FROM employees
 WHERE LOWER(first_name) = LOWER('steven'); --O
SELECT * FROM employees
 WHERE UPPER(first_name) = UPPER('STEVEN'); --O

CONCAT (column | expression, column | expression) : 두 값을 결합 한다.

SELECT first_name || ' ' || last_name AS name FROM employees;
SELECT CONCAT(first_name, last_name) AS name FROM employees;

문제) employees 테이블에서 이메일(email) 출력시 끝 부분에
'@test.com' 문자열 추가 출력. concat() 함수 이용.

SELECT first_name, last_name, email FROM employees;
-->'@test.com' 문자열 추가 출력. concat() 함수 이용.

출력예)
David  Bernstein DBERNSTE@test.com
Laura  Bissot  LBISSOT@test.com
Harrison Bloom  HBLOOM@test.com

 

***SUBSTR (char, m [,n])
지정된 위치(m)에서 지정된 길이(n)만큼의 문자열을 추출한다. m값이 음수이면 시작이 뒤에서 몇 번째 인가를 의미한다. 첫 번째 문자의 위치는 1 부터 시작.

SELECT SUBSTR('TEST', 1, 3) FROM dual; --TES
SELECT SUBSTR('801212-1234567', 8, 1) FROM dual; --1(남자)

문제) employees 테이블에서 전화번호 출력시. 전화번호 끝 4글자를 **** 로 출력. 전화번호가 '650'으로 시작되는 경우만 출력.

SELECT first_name, last_name
 , phone_number  --전화번호 끝 4글자를 **** 로 출력  FROM employees
 WHERE phone_number LIKE '650.%';

출력예)
Girard  Geoni  650.507.****
Nandita  Sarchand 650.509.****

 

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

 

LENGTH (column | expression)
문자열의 길이를 반환한다.

SELECT LENGTH('TEST') FROM dual;

 

INSTR (column | expression, 'string' [,m] [,n])
명명된 문자의 위치를 반환한다. m값은 시작위치고, n값은 발생 횟수이며 m과 n의 기본 값은 1이다.

LPAD (column | expression, n, ['string'])
expression의 문자열을 제외한 공간에 문자열을 왼쪽에 채운다.("expression 문자열 길이 - n" 개) 'string'를 생략하면 디폴트는 single blank이다.

SELECT LPAD(SUBSTR('801212-1234567', 7, 8), 14, '*') AS ssn  FROM dual;
-->******-1234567

 

RPAD (column | expression, n, 'string')
expression의 문자열을 제외한 공간에 문자열을 우측에 채운다.("expression 문자열 길이 - n" 개)

문제) 주민번호를 아래 형태로 출력. 함수 사용.
'801212-1234567'  -->  801212-*******

SELECT RPAD(SUBSTR('801212-1234567', 1, 7), 14, '*') AS ssn  FROM dual;

 

문제) employees 테이블에서 전화번호 출력시. 전화번호 끝 4글자를 **** 로 출력. 전화번호가 '650'으로 시작되는 경우만 출력. rpad() 함수 이용.

SELECT first_name, last_name
 , phone_number  --전화번호 끝 4글자를 **** 로 출력  FROM employees
 WHERE phone_number LIKE '650.%';

출력예)
Girard  Geoni  650.507.****
Nandita  Sarchand 650.509.****

SELECT first_name, last_name
 , RPAD(SUBSTR(phone_number, 1, 8)
 , LENGTH(phone_number)
 , '*') AS phone_number
 FROM employees
 WHERE phone_number LIKE '650.%';

 

***REPLACE (text, search_string [, replacement_string])
이 함수는 문자열에서 지정한 문자를 다른 문자로 치환한다. 치환될 문자를 지정하지 않으면 해당 문자를 삭제한다.

SELECT REPLACE('test@naver.com', '@naver', '@nate') AS email FROM dual;

SELECT first_name
      , last_name
      , department_id
      , REPLACE(department_id, 100,  'Finance') AS department_name
      FROM employees;

 

RTRIM(char [,set])
char의 문자열 중 오른쪽에서부터 set 문자열을 만나면 제거한다.

SELECT RTRIM('총무부', '부') AS buseo FROM dual;
SELECT RTRIM('개발부', '부') AS buseo FROM dual;
SELECT RTRIM('마케팅부', '부') AS buseo FROM dual;

SELECT
 job_id
 , RTRIM(job_id, 'CLERK') AS job_id2
    FROM employees;

LTRIM(char [,set])
char의 문자열 중 왼쪽에서부터 set 문자열을 만나면 제거한다.

 

TRIM (leading | trailing | both trim_character FROM trim_source)
문자열의 앞, 뒤, 또는 앞뒤에서 공백 문자를 제거 한다.

SELECT *
  FROM dual
  WHERE 'TEST'='TEST'; --true
SELECT *
  FROM dual
  WHERE 'TEST'='  TEST'; --false
SELECT *
  FROM dual
  WHERE TRIM('TEST')=TRIM('  TEST'); --true

 

ASCII(char)
문자열 중 첫 문자의 아스키 코드 값
-- A -> 65
-- a -> 97
-- 0 -> 48

 

CHR(n)
아스키 코드에 해당하는 문자

 

 

 

------------------------------------------
ROUND(n [,m])
반올림(예를 들어 m이 2이면 소수점 3째 자리에서 반올림하여 소수점 이하 자리를 2자리로 표현) 한다.

TRUNC(n [,m])
절삭

employees 테이블에서 급여(salary) 10000 이상, 20000 미만인 경우 출력.
-->18명
SELECT * FROM employees
 WHERE salary>=10000 AND salary<20000;

SELECT TRUNC(salary/10000) AS salary FROM employees;

SELECT * FROM employees
 WHERE TRUNC(salary/10000)=1;

문제) employees 테이블에서 급여(salary)가 20000달러대(20000 이상, 30000 미만) 출력. trunc() 함수 이용.
-->1명

 

 

MOD(m, n)
m을 n으로 나눈 나머지

FLOOR(n)
주어진 값보다 적거나 같은, 가장 큰 정수(절삭)

CEIL(n)
주어진 값보다 크거나 같은, 가장 적은 정수(절상)

 

----------------------------------------------
***SYSDATE
시스템에 저장된 현재 날짜를 반환하는 함수로서, 초 단위까지 반환한다. 결과를 시, 분, 초 단위까지 출력하기 위해서는 TO_CHAR 함수를 사용하여 날짜 형식을 변환해야 한다.

SELECT SYSDATE FROM dual; --15/03/18
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual; --
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; --

SELECT TO_CHAR(SYSDATE, 'HH24:MI') FROM dual; --12:41

SELECT TO_CHAR(hire_date, 'YYYY-MM-DD HH24:MI:SS') FROM employees;
--> 기존 데이터는 시분초가 모두 00으로 세팅되어 있다.

----------------------------------------------
***TO_CHAR(label [, fmt]) : MLSLABEL datatype을 VARCHAR2 타입으로 변환
TO_CHAR(n [, fmt [, 'nlsparams'] ]) : 숫자를 문자로 변환(VARCHAR2 타입)
TO_CHAR(d [, fmt [, 'nlsparams'] ]) : 날짜를 문자로 변환(VARCHAR2 타입)

TO_DATE(char [, fmt [, 'nlsparams'] ])
문자를 날짜로 변환

2012년 1월 1일부터 오늘 날짜까지의 일 수 출력.
SELECT SYSDATE - TO_DATE('20120101') AS nalsu FROM dual;
-->12.5896875

TO_NUMBER(char [,fmt [, 'nlsparams'] ])
문자를 숫자로 변환

---------------------------------------
***NVL(expr1, expr2)
expr1이 Null 이면 expr2 반환

employees 테이블에서 commission_pct 가 NULL인 경우 0으로 계산하는 경우

SELECT salary
  , NVL(commission_pct, 0) AS commission_pct
  , salary*NVL(commission_pct, 0) AS commission
  FROM employees;

 

NVL2(expr1, expr2, expr3)
expr1이 null이 아니면 expr2를 반환하고, null이면 expr3를 반환

employees 테이블에서 commission_pct가 있는 경우와 없는 경우로 구분해서 출력.
SELECT first_name, last_name
 , NVL2(commission_pct, '있음', '없음') AS commission FROM employees;

 

 

-------------------------------------------
***DECODE 함수

형식
DECODE(검색컬럼, 조건1, 결과값1, 조건2, 결과값2, ..., 기본값);

의미
DECODE는 일반적인 프로그래밍 언어의 IF문을 SQL 문자 또는 PL/SQL 안으로 끌어들여 사용하기 위하여 만들어진 오라클 함수이다. 따라서 일반 프로그래밍 언어의 IF문이 수행할 수 있는 기능을 포함하고 있다. SELECT시의 DECODE 사용은 FROM 절만 빼고 어디에서나 사용할 수 있다.

특징
- IF~THEN~ELSE문장과 똑같은 결과를 출력할 수 있다.
- 각각의 조건에 맞는 값에 대한 처리를 결과와 같게 연산해 리턴 한다.
- 비교 연산은 '='만 가능하다.
- CASE 문은 DECODE 함수의 확장기능이다. DECODE는 분류 작업이 필요하므로 CASE가 성능이 좋다.

자바에서는
switch(비교대상) {
case 값1: 결과1; break;
case 값2: 결과2; break;
case 값3: 결과3; break;
default: 기본값; break;
}
--> 비교대상이 값1과 일치하면 결과1을 반환. 값2와 일치하면 결과2를 반환.
--> DECODE(검색컬럼, 조건1, 결과값1, 조건2, 결과값2, ..., 기본값);
는 검색컬럼이 조건1과 일치하면 결과값1을 반환, 조건2와 일치하면 결과값2를 반환.

 

employees 테이블에서 department_id를 department_name으로 출력.
SELECT * FROM employees
 WHERE job_id LIKE '%CLERK';
--> department_id 가 30, 50만 존재함

SELECT * FROM departments
  WHERE department_id IN (30, 50);
--> 30, 50 부서번호는 'Purchasing', 'Shipping'에 해당함.

SELECT first_name, last_name
 , DECODE(department_id
  , 30, 'Purchasing'
  , 50, 'Shipping')
  AS department_name
 FROM employees
 WHERE job_id LIKE '%CLERK';

문제) employees 테이블에서 job_id가 'IT_PROG'인 직원들을 출력하되,
manager_id 대신 manager_name으로 출력. DECODE() 함수 이용.

manager_id -> manager_name
102 ->Lex De Haan
103 ->Alexander Hunold

SELECT employee_id, first_name, last_name
  , manager_id
 --, manager_name
 FROM employees
 WHERE job_id='IT_PROG';

출력예)
103 Alexander Hunold  102 Lex De Haan
104 Bruce  Ernst  103 Alexander Hunold
105 David  Austin  103 Alexander Hunold
106 Valli  Pataballa 103 Alexander Hunold
107 Diana  Lorentz  103 Alexander Hunold

 

문제) employees 테이블에서 급여(salary)를 세 가지 등급으로 구분해서 출력.
DECODE() 함수 이용.
20000 이상 ~ 30000 미만-> A등급
10000 이상 ~ 20000 미만 -> B등급
0 이상 ~ 10000 미만 -> C등급

 

-----------------------------------------------
CASE 표현식

형식
CASE 컬럼명|표현식
 WHEN 조건1 THEN 결과1
 [WHEN 조건2 THEN 결과2 ......
 WHEN 조건n THEN 결과n
 ELSE 결과4]
END

의미
각각의 조건에 맞는 값에 대한 처리를 수행하여 결과와 같게 연산해 리턴 하는 것은 DECODE 함수와 동일하나 DECODE 함수에서는 지원하지 않는 범위 비교가 가능하다. IF~THEN~ELSE문장과 똑같은 결과를 출력할 수 있다. DECODE 함수는 표현식 또는 컬럼 값이 '=' 비교를 통해 조건과 일치하는 경우에만 다른 값으로 대치할 수 있지만, CASE 표현식에서는 산술연산, 관계연산, 논리연산과 같은 다양한 비교가 가능하다. 또한 WHEN 절에서 표현식을 다양하게 정의할 수 있다.

특징
- CASE 표현식은 ANSI SQL 형식도 지원한다.
- 조건문가 조건문 사이에는 콤마를 사용하지 않는다.
- CASE 문은 반드시 END로 끝내야 한다.
- 결과를 기술해야 하는 부분은 NULL을 사용해서는 안 된다.
- CASE 명령어 다음에 기술하는 컬럼명/표현식과 조건, 결과에 표현되는 데이터들은 모두 데이터
타입이 동일해야 한다. 여기에 올 수 있는 데이터 타입으로는 CHAR, VARCHAR2, NCHAR,NVARCHAR2가 있다.

 

employees 테이블에서 department_id를 department_name으로 출력.
SELECT first_name, last_name
 , DECODE(department_id, 30, 'Purchasing', 50, 'Shipping')
  AS department_name
 FROM employees
 WHERE job_id LIKE '%CLERK';
-->CASE 표현식으로 변경

SELECT first_name, last_name
 , CASE
  WHEN department_id=30 THEN 'Purchasing'
  WHEN department_id=50 THEN 'Shipping'
   END AS department_name
 FROM employees
 WHERE job_id LIKE '%CLERK';

 

employees 테이블에서 급여(salary)를 세 가지 등급으로 구분해서 출력.
DECODE() 함수 이용.
20000 이상 -> A등급
10000 이상 ~ 20000 미만 -> B등급
0 이상 ~ 10000 미만 -> C등급
SELECT first_name, last_name
 ,DECODE(TRUNC(salary/10000),0,'C등급',1,'B등급',2,'A등급')
  AS salary
 FROM employees;
-->CASE 표현식으로 변경
SELECT first_name, last_name
 ,CASE
  WHEN TRUNC(salary/10000)=0 THEN 'C등급'
  WHEN TRUNC(salary/10000)=1 THEN 'B등급'
  WHEN TRUNC(salary/10000)=2 THEN 'A등급'
  END AS salary
 FROM employees;

문제) employees 테이블에서 급여(salary)의 수준에 따라 다른 세금 비율(tax_pct) 적용해서 세금액수(tax)을 출력. CASE~END 표현식 사용.
20000 이상 -> 4%
10000 이상 ~ 20000 미만 -> 2%
0 이상 ~ 10000 미만 -> 0%

 

문제) employees 테이블에서 부서(department_id)를 세 가지로 분류해서 출력.
CASE~END 표현식 사용. 부서(department_id)는 10, 20, 30, ..., 110 만 존재합니다.
10~30 -> 'A 지역'
40~70 -> 'B 지역'
80~110 -> 'C 지역'
null -> '기타 지역'

 

---------------------------------------
복수 행 함수

[1] 개요
그룹 당 하나의 결과를 리턴 해 준다.
SELECT 또는 HAVING절에서 사용할 수 있다.
GROUP BY절에 의해 그룹화 시킬 컬럼을 정의할 수 있다.
COUNT 함수를 제외한 모든 그룹함수는 NULL값은 처리하지 않는다.

***복수 행 함수끼리만 사용 가능

---------------------------------------
집합 함수

COUNT : 행의 개수를 리턴
AVG : NULL값을 제외한 행의 평균값을 출력한다.
SUM : NULL값을 제외한 합계를 출력한다.

SELECT COUNT(*) AS 전체직원수 FROM employees;

SELECT SUM(salary) AS 전체급여합
 , ROUND(AVG(salary)) AS 전체급여평균 FROM employees;

문제) employees 테이블에서 job_id가 'IT_PROG'인 직원들의 급여 평균 출력. avg() 함수 사용.
--> 5760

SELECT 평균
 FROM employees
 WHERE job_id='IT_PROG';

 

----------------------------------------
GROUP BY

개념

1. 테이블의 행들을 원하는 그룹으로 나눌 때 사용되는 키워드이다. 그룹함수의 컬럼 명을 SELECT 절에 사용하고자 하는 경우, GROUP BY 다음에 컬럼명을 추가한다.

2. WHERE 절을 사용하여 행들을 그룹으로 나누기 전에 미리 조건에 맞지 않는 행들을 제외시킬 수 있다.

3. GROUP BY 절에 컬럼의 위치 또는 컬럼 별칭을 줄 수 없다.기본적으로 행들은 오름차순으로 정렬되어 출력된다. ORDER BY절을 사용하여 바꾸어 줄 수 있다.

4. 그룹 함수가 아닌 SELECT 절의 어떤 컬럼이나 표현식은 GROUP BY 절에 와야 한다. SELECT 절에서 나열된 컬럼 이름이나 표현식은 GROUP BY 절에서 반드시 명시해야 한다. 그러나 GROUP BY 절에서 명시한 컬럼 이름은 SELECT 절에서 명시하지 않아도 된다. GROUP BY 절을 이용하여 보다 적은 그룹으로 분류하여 처리하는 것이 가능하다.

employees 테이블에서 부서별(department_id)로 그룹을 만든 후 출력하되 각 그룹별 직원수 출력.
SELECT department_id, COUNT(*) AS 그룹별직원수 FROM employees
 GROUP BY department_id;

출력 결과)
department_id 그룹별직원수
---------------------------
100  6
30  6
null  1
90  3
20  2
70  1
110  2
50  45
80  34
40  1
60  5
10  1

 

 

 

employees 테이블에서 직무별(job_id) 직원수 출력.
SELECT job_id, COUNT(*) AS 그룹별직원수 FROM employees
 GROUP BY job_id;

 

 

employees 테이블에서 직무별(job_id) 직원의 평균급여 출력.
SELECT job_id, ROUND(AVG(salary)) AS 그룹별평균급여 FROM employees
 GROUP BY job_id
 ORDER BY ROUND(AVG(salary)) DESC;

 

SELECT job_id, ROUND(AVG(salary)) AS 그룹별평균급여 FROM employees
 GROUP BY job_id
 ORDER BY 그룹별평균급여 DESC;

 

 

문제) 상급자(manager_id)별 직원수 출력. 하급 직원이 많은 순으로 출력.

SELECT manager_id, COUNT(*) AS 하급직원수
 FROM employees
 GROUP BY manager_id
 ORDER BY  하급직원수 DESC;

 

문제) 입사년도(hire_date)별 직원수 출력. 입사년도순 출력.
2001년~2008년

문제) employees 테이블에서 commission_pct별 직원의 수, 평균 급여 출력. 정렬시켜서 출력.
SELECT ~
 FROM employees
 GROUP BY ~
 ORDER BY ~
--> commisstion_pct 가 null인 직원의 수는 72명

 

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