SQL - 단일행 함수 & 그룹 함수

1. 단일행 함수

(UPPER, LOWER, LENGTH, LPAD, RPAD, SUBSTR, TRIM)

-- 단일행 함수 -- 
-- 문자
SELECT UPPER('abc') , LOWER('AAddedC'), LENGTH('abc'),
      '-'||TRIM(' abc  ')||'-',
      LPAD('abc', 7, '0'),  RPAD('abc', 7, '0'),
      SUBSTR('abcde', 1, 2),SUBSTR('abcde', 3, 2),
      SUBSTR('abcde', 2   ),  -- 2번째부터 다가져와
      SUBSTR('abcde', -1),    --맨뒤에서부터 다가져와
      substr('abcde', -3, 3)  --맨뒤에서부터 3글자만 가져와
FROM DUAL; 
 
 
SELECT ename, UPPER(ename) , LOWER(ename), LENGTH(ename),
      '-'||TRIM(ename)||'-',
      LPAD(ename, 7, '0'),  RPAD(ename, 7, '0'),
      SUBSTR(ename, 1, 2),SUBSTR(ename, 3, 2),
      SUBSTR(ename, 2   ),  -- 2번째부터 다가져와
      SUBSTR(ename, -1),    --맨뒤에서부터 다가져와
      SUBSTR(ename, -3, 3)  --맨뒤에서부터 3글자만 가져와
FROM emp; 
 
-- ename을 다음과 같은 형태로 출력
SMITH --> S**TH
WARD  --> W**D
MARTIN --> M**TIN
...
--김문식님이 모두에게 11:38 AM
SELECT SUBSTR(ENAME, 1,1)||LPAD('*',2,'*')||SUBSTR(ENAME, 3) ENAME FROM EMP;
--박희진님이 모두에게 11:38 AM
SELECT ENAME,SUBSTR(ENAME,1,1)||'*'||'*'||SUBSTR(ENAME, 4)
FROM EMP;
--이혜린님이 모두에게 11:38 AM
select substr(ename,1,1)||'**'||substr(ename,3)
FROM EMP;
--김효선님이 모두에게 11:38 AM
SELECT SUBSTR(ENAME,1,1) || '**'|| SUBSTR(ENAME,4) FROM EMP;
--최상현님이 모두에게 11:38 AM
select rpad(substr(ename,1,1),3,'*')||substr(ename,4) from emp;
--나라 신님이 모두에게 11:39 AM
select? replace (ename, SUBSTR (ename, 2,2), '**')
FROM EMP;

2. 반올림/버림 함수

(TRUNC, ROUND, CEIL)

-- 한달 20일 기준: 일당, 반올림
round('값',자리수) - 반올림
trunc('값',자리수) - 버림
ceil(값) - 반올림하여 정수로 출력
 
-- 하루8시간 근무 기준 : 시급 소수점 1자리까지 나타내고 버림, 시급 소수점 1자리까지 나타내고 올림
SELECT SAL, SAL/20, ROUND(SAL/20,0) 
      , sal/20/8, trunc(sal/20/8, 1), ceil(sal/20/8 )
from emp;

 

3. 타입 변환 함수 

(TO_DATE, TO_CHAR, TO_NUMBER)

-- 형변환 == 타입변환 == 캐스팅
-- 날짜 타입 변환 3 
SELECT SYSDATE, 
       TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') 
FROM DUAL;
 
-- 날짜 타입은 가감산 가능
SELECT HIREDATE, HIREDATE+1 ,
       -- to_char(hiredate, 'yyyy-mm-dd') + 1  버전마당 상이 현 버전에선 에러
from emp;
 
-- 글자로 날짜가 들어올 경우 to_date를 사용해 날짜 타입으로 캐스팅
SELECT TO_DATE('2021-01-01','yyyy-mm-dd')+1  FROM DUAL;
 
SELECT SYSDATE , HIREDATE , TRUNC(SYSDATE-HIREDATE,0), 
     trunc((SYSDATE-HIREDATE)/365,0)
from emp;
 
-- 입사일이 1982년도 이후 입사자
select *
FROM EMP
where hiredate > '1981-12-31';
 
select *
FROM EMP
WHERE HIREDATE > TO_DATE('1981-12-31','yyyy-mm-dd');
 
SELECT TO_DATE(SYSDATE, 'yyyy-mm-dd') FROM DUAL;      -- 현 버전에서는 구동
SELECT TO_CHAR('2021-01-01', 'yyyy-mm-dd') FROM DUAL; -- 현 버전에서는 에러
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') FROM DUAL;      -- 정석
SELECT TO_DATE('2021-01-01', 'yyyy-mm-dd') FROM DUAL; -- 정석
 
--1. 글자로 되있는 날짜라면 to_date를 사용해 날짜 타입으로 캐스팅
--2. 날짜 타입이면 연산,조건비교가 수월
 
-- 암묵적 형변환이 일어남 -- 글자123에  가감산이 가능
SELECT '123'+1 FROM DUAL;
SELECT TO_NUMBER('123') +1 FROM DUAL;

 

4. 함수중첩

(CASE, DECODE)

-- 함수중첩
SELECT ENAME||'_kr'||'_123' FROM EMP;
SELECT CONCAT(ENAME,'_kr') FROM EMP;
SELECT CONCAT(ENAME,'_kr')||'_123' FROM EMP;
SELECT CONCAT(CONCAT(ENAME,'_kr'),'_123') FROM EMP;
 
--nvl *******
select comm, comm+100, nvl(comm, 0),  nvl(comm, 0)+100 from emp;
 
 
SELECT DEPTNO, 
       CASE DEPTNO WHEN 10 THEN '십'
                   WHEN 20 THEN '이십'
                   WHEN 30 THEN '삼십'
                   ELSE '기타'
       END as kr
FROM DEPT;
SELECT SAL, 
        CASE       WHEN (SAL>=1000 and sal<2000) THEN '1'
                   WHEN (SAL>=2000 and sal<3000) THEN '2'
                   WHEN (SAL>=3000 and sal<4000) THEN '3'
                   ELSE '5'
        END as kr
FROM emp;
 
SELECT SAL, 
        CASE       WHEN (SAL BETWEEN 1000 AND 1999) THEN '1'
                   WHEN (SAL BETWEEN 2000 AND 2999) THEN '2'
                   WHEN (sal between 3000 and 3999) THEN '3'
                   ELSE '5'
        END as kr
FROM emp;
 
 
SELECT DEPTNO ,
       DECODE(DEPTNO, 10 , '십'
                    , 20 , '이십'
                    , 30, '삼십'
                    , '기타'
              ) as kr
from emp;

 

5. 그룹 함수

(MAX, MIN, AVG, SUM, COUNT)

-------------------------- GROUP ----------------------------------
 
총 5개 그룹함수
-- max() , min(), avg(), sum(), count()
select max(sal), min(sal), avg(sal), sum(sal), count(sal)
FROM EMP;
 
SELECT trunc(avg(sal),0) --10번부서
FROM EMP
where deptno = 10;  --2916
SELECT trunc(avg(sal),0) --20번부서
FROM EMP
WHERE DEPTNO = 20;  --2175
 
------------ ****** 그룹함수5개는 Group by와 무관하게 그냥 쓸수 있다.
SELECT trunc(avg(sal),0) --30번부서
FROM EMP
where deptno = 30;  --1566

 

6. GROUP BY 함수

------------ ****** Group by 컬럼1,컬럼2  --> select 컬럼1,컬럼2
------ORA-00979: not a GROUP BY expression
------00979. 00000 -  "not a     GROUP BY expression"
---- ename이 있으면 에러가 난다 ( Group by deptno만 사용하고 있다.)
select ename, deptno, trunc(avg(sal),0)
FROM EMP
GROUP BY ename, deptno
order by deptno asc;
 
--- ORA-00937: not a single-group group function
-- 00937. 00000 -  "not a single-group group function"
SELECT sal, min(ENAME)
from emp;
 
 
select deptno,job, avg(sal)  --- trunc(avg(sal),0)
FROM EMP
GROUP BY deptno,job
order by deptno asc;
 
 
-- mgr 있으면 y 없으면 n : as mgr_yn
select * from emp;
 
SELECT MGR ,
      CASE NVL(MGR,0) WHEN 0 THEN 'n'
                      ELSE 'y' 
      END  MGR_YN
from emp;

 

7. select 명령어 순서

(SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY 순)

-- 명령어 순서 중요 --
-- ************** WHERE 뒤에는 AVG 그룹함수 사용 불가
SELECT DEPTNO, AVG(SAL)
FROM EMP
WHERE  
GROUP BY DEPTNO
HAVING AVG(SAL)>=2000
ORDER BY
;

GROUP BY DEPTNO   SELECT  AVG(SAL)       AVG(SAL)>=2000
 
SELECT  DEPTNO,AVG(SAL) 
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>=2000;