SQL 날짜 관련 명령어, 그룹함수, 조건명령문

1. 오늘의 날짜 : sysdate
select sysdate from dual;

2. 날짜에서 년도 추출 ( to_char, extract)
select to_char(sysdate, 'yyyy') from dual; -- 문자열형식(좌측 정렬, 처리속도가 조금 더 빠름)
select extract(year from sysdate) from dual; -- 숫자형식(우측 정렬)

--근무일수, 근무개월수, 근무년수를 출력하시오.<inline view>
select round(근무일수), round(근무일수/30), round(근무일수/365) from(select round(sysdate - hiredate) 근무일수 from emp);
select empno 사번, ename 이름, 월급*12 연봉 from (select empno, ename, nvl(sal+comm, sal) 월급 from emp);

select (sysdate-hiredate)/30.445, months_between(sysdate, hiredate) from emp;

* inline view : from 다음에 오는 (select ~)
* subquery : select, where 다음에 노는 (select ~)

​3. 날짜 함수 : months_between()
select months_between(sysdate, hiredate) from emp;

 

-- 인사 테이블에서 입사한지 10년이상사원을 출력
desc Insa;
select * from insa where months_between(sysdate, ibsadate)>=120;

 

3-1. 날짜함수 : next_day(날짜, 요일) : 다음 해당 요일의 날짜.
select next_day(sysdate, '일') from dual;
3-2. 날짜함수 : last_day(날짜) : 날짜가 속한 월의 마지막 날
select last_day(sysdate) from dual;
3-3. 날짜 함수 : add_months(날짜, 정수) : 정수 만큼의 개월이 지난 날짜
select add_months(sysdate, 6) from dual;

 

 

--emp 테이블. 사원들은 입사 후 3개월의 수습기간을 거치며 입사 3개월 후 금요일이 정식 계약일이다.  

-- 사원별 계약일을 출력하시오.
select ibsadate from insa;
select emp.*, next_day(add_months(hiredate, 3)-1, '금') 계약일 from emp;

 

4. 형변환 : to_char, to_data, to_number
select '1'+'2' from dual; -- 오라클이 알아서 정수형으로 바꿔서 덧셈을 해준다.

4-1. 날짜를 문자로 :  to_char(날짜, '서식')
-- 서식 yyyy: 4자리년도, yy : 2자리년도, mm : 월, mon, month : 한글의 경우 동일(몇월)
select to_char(sysdate, 'yyyy'), to_char(sysdate,'mm'), to_char(sysdate, 'mon') from dual;
-- 서식 dd : 일, day : 요일 표시, dy: 요일표시 약자
 select to_char(sysdate, 'dd'), to_char(sysdate, 'day'), to_char(sysdate, 'dy') from dual;
 -- 서식 hh24 : 24시간, hh : 12시간, mi: 분, ss: 초
 -- 서식의 조합
 select to_char(sysdate, 'yy-mm-dd') from dual;
 select to_char(sysdate, 'yy/mm/dd') from dual;
 select to_char(sysdate, 'd dd ddd ') from dual;
 *  d: 주에서의 일 dd: 월에서의 일 ddd: 년에서의 일.
 
 -- employees  테이블의 모든 사원은 2001~2009년 사이에 입사했다. 홀수년도에 입사한 사원들의
 -- first_name과 입사일을 출력하시오.
 desc employees;
 select first_name, hire_date from employees where to_char(hire_date, 'yy' ) in ('01','03','05','07','09');
 
 4-2. 숫자를 문자열로 변환 : 9로 자리수를 지정, 자리수가 모자라면 ###으로 출력
 select to_char(12345, '99999.999') from dual;
 select to_char(12345, '$99,999') from dual; -- 달러로 표시
 select to_char(12345, 'L99,999') from dual; -- 원으로 표시
 
 4-3. to_date(날짜로 해석할 값, 서식)
 select to_char('2014-11-20','yyyy') from dual; -- 묵시적 변환 실패
 select to_date('2014-11-20','yyyy-mm-dd') from dual;
 select to_char(to_date('2014-11-20','yy/mm/dd'), 'yyyy') from dual;  -- '2014-11-20' 문자열을 날짜형으로 바꾼 다음 년도를 뽑아낸다.
 

4-4. extract
 select extract(year from sysdate), to_char(sysdate, 'yyyy') from dual;
 --extract  함수는 숫자로, to_char 은 문자로 추출. to_char 이 20%정도 빠르다.
 
 -- emp 테이블에서 매월 1일~15일 사이에 입사한 사원의 이름과 입사일을 출력하시오.
 select extract(day from sysdate) from dual;
 select ename, hiredate from emp where (extract(day from hiredate)>= 1) and (extract(day from hiredate)<=15);
 -- 인사 테이블에서 주민등록번호(ssn)을 이용해 사원의 이름과 나이를 출력하시오.
 desc insa;
 select extract(year from to_date(substr(ssn, 1, 2), 'yyyy'))+1900 from insa;
 select name, extract(year from sysdate)-(extract(year from to_date(substr(ssn, 1, 2), 'yyyy'))+1900) 나이 from insa;
 -- employees  테이블에서 2005년 이전에 입사한 사원의 first_name, 연봉, 10%인상후 연봉을 출력하시오.
 -- 연봉은 salary와 (salary*commision_pct)를 더한 결과이며 천단위 구분기호와 달러 표시를 하시오.
 desc employees;
 select * from employees;
 select first_name, to_char(salary+(salary*nvl(commission_pct, 0)), '$9,999,999') 연봉,
 to_char((salary+(salary*nvl(commission_pct,0)))*1.1, '$9,999,999') 인상후연봉 from employees
 where extract(year from hire_date) <= 2005;
 
 
 5. 그룹함수 : avg, min, max, sum, count
 select count(ename) from emp;
 select sum(comm), avg(comm) from emp;     -- null을 무시하고 동작
 select count(*) from emp;   -- 행의 개수가 몇개인지.
 
 5-1. group by : 부분합을 계산한 컬럼.
 select max(sal), avg(sal) from emp;
 -- 전체 1등, 전체 평균 급여를 출력. group by  없이 그룹함수를 사용하면 결과는 1행
 -- 전체가 아니라 부서별 급여 1등, 평균 급여를 알고 싶다면 group by 부서
  select max(sal), avg(sal) from emp group by deptno;
 

* select 문 작성 순서 : select(5) ~ from(1) ~ where(2) ~ group by(3) ~ having(4) ~ order by(6)~;
  -- where : 그룹 함수를 사용하지 않는 조건절.
  -- having : 그룹함수를 사용하는 조건즐. 즉 group by 가 없으면 having 도 없다.
 
  desc emp;
  -- job별 최대 급여를 출력하시오.
  select max(sal) from emp group by job;
  -- 급여가 1000이상인 사원들의 부서별 평균 급여를 부서 번호 내림차순으로 출력하시오.
  select avg(sal) from (select sal, deptno from emp where sal>=1000) group by deptno order by deptno desc;
 
  -- 급여가 2000이상인 사원들의 부서별 평균 급여를 반올림한 값을 출력하시오.
  -- 부서별 평균 급여에 따라 오름차순 정렬하시오
  -- (from 에서 읽어왔거나 select  다음에서 계산한 값으로만 oder by 할 수 있다. )
  select round(avg(sal)) from (select sal, deptno from emp where sal>=2000) group by deptno order by avg(sal);
  select round(avg(sal)) from emp where sal>=2000 group by deptno order by avg(sal);
  -- 각 부서별 같은 job을 가진 사람의 인원수를 구하여 부서번호, job, 인원수를 출력하시오.
  -- (둘 이상의 컬럼으로 group by 하려면 group by a, b)
  select * from emp;
  select deptno, job, count(*) from emp group by deptno, job; 

 

6. decode 함수 : 자바의 if 문에 해당
6-1. if(a==b) return "같다." -> decode(a,b, '같다');
select decode(deptno, 10, sal) from emp;
-- 부서번호가 짝수면 짝수라고 출력하시오
select decode(mod(deptno,2), 0, '짝수') from emp;
-- 부서번호가 10이면 총무라고 출력하시오.
select decode(deptno, 10, '총무') from emp;

 

6-2.if(a==b) return '같다'  else return '다르다' -> decode(a,b,'같다','다르다')
-- 부서번호가 10이면 총무 아니라면 기타라고 출력하시오.
select decode(deptno, 10, '총무', '기타') 부서 from emp;
-- 인사테이블에서 이름, 성별, 전화번호를 출력하시오. 성별은 ssn과 decode를 이용해서 '남자' 또는 '여자'라고 출력하시오.
select name, decode(substr(ssn, 8,1), 1, '남자', '여자') 성별, tel from insa;

 

6-3. if~else if~ else : decode(substr(ssn,8,1),'1','남자', '2','여자', '3', '남자', '여자' )
-- sawon 테이블에서 saname, sajob, 분류를 출력하시오. 분류는 sajob이 회장이나 부회장이면 경영진, 그렇지 않으면 직원으로 출력하시오.
select saname, sajob, decode(sajob, '회장', '경영진', '부회장', '경영진','직원') 분류 from sawon;
-- employees 테이블에서 60번 부서의 David에 '우수사원후보' 라고 출력하시오. 우수사원후보가 아닌 사람은 '후보아님' 이라고 출력하시오.
select DEPARTMENT_ID, first_name, last_name,
decode(first_name, 'David', decode(department_id, 60, '우수사원후보', '후보아님'), '후보아님') from employees;
-- 사번, 이름, 직책, 총급여를 출력. 총급여는 sal+comm 이며 직책이  president 이면 50%를 가산하시오.
select empno, ename, job, decode(lower(job), 'president', (sal+nvl(comm,0))*1.5, (sal+nvl(comm,0))) 총급여 from emp;

 

7. sign(식) 함수는 결과가 양수면 1, 음수면 -1, 0이면 0을 리턴한다.

--sign()과 decode()를 이용해서
-- employees 테이블에서 salary가 10000 이상이면 '고급여', 미만이라면 '저급여'로 출력하는 급여열을 포함해서
-- first_name, salary, 급여를 출력하시오.

select first_name, salary, decode(sign(salary-10000),-1, '저급여', '고급여') 급여 from employees;

 

8. case when ~ then ~ when ~ then ~ end 문
select case mod(&data, 2) when 0 then '짝수' else '홀수' end from dual;
select case department_id when 10 then '우수부서' when 20 then '최우수부서'  else '해당없음' end from employees; 

 

* case 식 when : decode와 같은 경우. 범위 안걸리는 경우
* case when : 범위 걸리는 경우

 

[예제]

1. 사번, 이름, 부서명을 출력하시오. 부서명은 부서번호가 10이면 총무, 20이면 연구, 30이면 영업, 40이면 운영으로 하되 case를 사용하여 출력하시오.
select empno , ename, case deptno when 10 then '총무' when 20 then '연구' when 30 then '영업' when 40 then '운영' end 부서명 from emp;

 

2.  급여의 10퍼센트가 50이상이면 0.03, 200이상이면 0.02, 그 외에는 0.01을 가산세라는 이름으로 출력하시오.
select empno, ename, sal, sal*0.1,
case when sal*0.1 >=200 then sal*0.02 else (case when sal*0.1>=50 then sal*0.03 else sal*0.01 end) end 가산세 from emp;
3. 사번, 이름, 급여수준을 출력하시오. 급여수준은 급여가 1000이하면 저급여, 3000이하까지는 보통, 그 이상은 고급여로 하시오.
select empno, ename,
case sign(sal-1001) when -1 then '저급여' else(case sign(sal-3001) when -1 then '보통' else '고급여' end) end 급여수준 from emp;

 

4. 사번, 이름, 급여, 세액을 출력하시오. 급여는  sal+comm 이며세액은 급여*세율이다. 세율은 급여가 3000이상이면 10퍼센트, 2000이상이면 7퍼센트, 1000이상이면 5퍼센트, 이하는 3퍼센트로 하시오.
select empno, ename, sal+nvl(comm, 0) 급여,
case sign(sal+nvl(comm, 0)-3001)
when 1 then (sal+nvl(comm, 0))*0.1
else(sign(sal+nvl(comm, 0)-2001)
when 1 then (sal+nvl(comm, 0))*0.07 else(sign(sal+nvl(comm, 0)-1001) when 1 then
(sal+nvl(comm, 0))*0.05 else (sal+nvl(comm, 0))*0.03 end) end)end from emp;

 

5. commission_pct 별로 그룹화하여 인원수를 출력
select commission_pct, count(*) from employees group by commission_pct; 

 

6. employees  테이블에서 각 부서별, 업무별 사원수를 구해 부서번호, 직무, 인원수를 출력. 부서가 없는 사원은 출력에서 제외하시오.
select department_id, job_id, count(*) from employees
where department_id is not null
group by department_id, job_id order by department_id;

 

7. 부서 인원이 3명 이상인 부서의 부서별 최고 급여액을 출력하시오.
select department_id, max(salary), count(*) from employees group by department_id having count(*)>=3 ;

 

8. emp 에서 급여가 1000이상인 사원들의 부서별 평균급여를 출력하시오. 단 부서별 평균급여가 2000이상인 경우만 출력하시오.
select deptno, trunc(avg(sal),0) from emp where sal >= 1000 group by deptno having avg(sal) >= 2000 ; 

 

9. employees 테이블에서 인원수가 5명 이상인 부서를 대상으로 부서별 평균급여액을 출력하시오.
select department_id, trunc(avg(salary),0) from employees group by department_id having count(*)>= 5 ; 

 

10. employees 테이블에서 부서번호가 50이상, 급여 3000인 사원들의 부서별 부서번호와 평균급여를 부서번호 내림차순으로 출력하시오.
select department_id, trunc(avg(salary),0) from employees
where salary >=3000 and department_id >= 50
group by department_id order by department_id desc;

 

11. employees 에서 부서별 인원수가 2명 이상인 부서의 부서 id와 인원수를 출력하되 소속 부서가 없는 직원은 제외하시오.
select department_id, count(*) from employees where department_id is not null
group by department_id having count(*)>=2; 

 

12. job-id가 s 로 시작하는 2005년 이후 입사자를 대상으로 부서별 인원수가 2명 이상인 부서의 부서별 최고 금여자의 급여를 출력하시오. (s가 대문자인지 소문자인지는 모름)
desc employees;
select * from employees where hire_date >= '2005/01/01';
select department_id, max(salary) from employees where lower(job_id) like 's%' and hire_date >= '2005/01/01'
group by department_id having count(*)>=2; 

 

13. 연봉 1000이상인 사원들의 부서별 평균연봉의 반올림값을 부서번호로 내림차순 정렬해서 출력하시오. 단 부서번호가 20이상인 경우만 출력하시오.
select deptno, round(avg(sal*12)) from emp where sal*12>=1000 and deptno >= 20 group by deptno;