desc dept
dname varchar2(14) //<-- char로 전환
alter table dept modify (dname char(14));
select concat(dname, deptno) from dept;
alter table dept modify (dname varchar2(14));
varchar2 //가변형
ex) 민번, 우편번호
char //고정형
ex) 급여, 수당, 설명
select count(*), count(empno), count(comm) from emp;
select count(comm), sum(comm), avg(comm), count(*), avg(NVL(comm,0)) from emp;
select min(ename), max(ename) from emp;
select sum(sal), avg(sal), stddev(sal), variance(sal) from emp;
select deptno, max(sal), min(sal) from emp group by deptno;
select job, avg(sal, max(sal), min(sal) from emp group by job;
select stddev(100*sal/(select max(sal) from emp)) from emp;
select deptno, job, sum(sal), count(*) from emp group by deptno, job;
//그룹함수를 사용한 select문에 나오는 컬럼은 group by의 조건문에 나와야 한다.
select deptno, avg(sal), sum(sal) from emp group by deptno having avg(sal)>=2500;
//급여평균 2500 이상 부서번호, 급여평균, 합계
select job, count(*) from emp group by job having count(*)>=4;
//업무인원이 4명이상인 업무와 쪽수
select job, max(sal), min(sal) from emp where deptno in (10, 20) group by job having max(sal)>=2000 order by max(sal);
//부서가 10 20 이고 최대 급여가 2000 이상인 업무별 최대 최소 급여
//그룹함수조건이 아닌경우 where절에 기술!!!!!
select 'smith중 i는 '||instr('smith','i')||'번째에 있습니다.' from dual;
select deptno, ename, sal, decode(deptno,10,sal*1.1,20,sal*1.2,30,sal) "Sal Up" from emp;
select deptno, ename, job, case when sal>=2500 then 1200 when sal>=1500 then 1000 else 1500 end Bouns from emp;
//급여 1500이상 1000 , 2500 이상 1200 , 그외 1500
//주의 순서 바꾸지 말것
select ename, case when sal>=2500 then 1200 when sal>=1500 then 1000 else 1500 end Bouns1, case when sal>=1500 then 1000 when sal>=2500 then 1200 else 1500 end Bouns2 from emp;
select ename, case when sal<1000 then '거지' when sal<2000 then '서민' when sal<3000 then '중산층' else '갑부' end from emp;
select
case when sal<1000 then '거지'
when a.sal<2000 then '서민'
when a.sal<3000 then '중산층'
else '갑부' end living
from emp;
select ename, LEVEL, empno, mgr from emp START WITH mgr is NULL CONNECT BY PRIOR empno=mgr;
//LEVEL을 이용해 조직도를 나타냄
select deptno, to_char(hiredate,'yy'), avg(sal) from emp group by deptno, to_char(hiredate,'yy') not having to_char(hiredate,'yy')='82';
select avg(sal), max(sal), min(sal) from emp group by job having lower(job)=lower('salesman');
select count(empno) "C_INWON", count(comm) "C_COMM", avg(comm) "A_COMM", 3 "N_COMM", count(deptno) "C_DEPT", 3 "C_DIS" from emp;
//N????
select deptno, avg(sal), min(sal), max(sal), sum(sal) from emp group by deptno;
select deptno, count(empno), avg(sal), min(sal), max(sal), sum(sal) from emp group by deptno order by sum(sal) desc;
select count(empno), min(sal), max(sal), sum(sal) from emp group by deptno order by sum(sal) desc;
select deptno, job, count(empno), avg(sal), sum(sal) from emp group by deptno, job;
select deptno, job, count(empno), avg(sal), sum(sal) from emp group by job, deptno;
select deptno, count(empno), sum(sal) from emp group by deptno having count(empno)>4;
select deptno, avg(sal), sum(sal) from emp group by deptno having max(sal)>2900;
select job, avg(sal), sum(sal) from emp group by job having avg(sal)>=3000;
select job, sum(sal) "PAYROLL" from emp where not job='SALESMAN' group by job having sum(sal)>5000 order by sum(sal) desc;
select max(avg(sal)), max(sum(sal)), min(min(sal)), max(max(sal)) from emp group by deptno;
1. EMP 테이블에서 모든 SALESMAN에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하라.
select avg(sal), max(sal), min(sal), sum(sal) from emp where job='SALESMAN';
2. EMP 테이블의 이름의 오름차순 정렬상 첫번째에 위치하는 이름, 마지막에 위치하는 이름을 구하고, 입사일의 가장 오래된 사람, 가장 최근에 입사한 사람을 구하고 최대 급여와 최소 급여를 구하여라.
select min(ename), max(ename), min(hiredate), max(hiredate), max(sal), min(sal) from emp;
3. EMP 테이블에 등록되어 있는 인원수, 보너스에 NULL이 아닌 인원수, 보너스의 평균, 등록 되어있는 부서의 수를 구하여 출력하라.
select sum(count(*)), sum(count(comm)), sum(avg(comm)), count(count(deptno)) from emp group by deptno;
4. 부서별로 인원수, 평균급여, 최저급여, 최고급여를 구하여라.
select count(*), avg(sal), min(sal), max(sal) from emp group by deptno;
5. 4번 문제를 급여의 합이 많은 순서로 출력하라.
select count(*), avg(sal), min(sal), max(sal) from emp group by deptno order by sum(sal);
6. 부서별로 그룹지어 부서번호, 인원수, 급여의 평균, 급여의 편차, 급여의 분산을 구하여라.
select deptno, count(*), avg(sal), stddev(sal), variance(sal) from emp group by deptno;
7. 업무별, 부서별로 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 편차, 급여의 합을 출력하라.
select deptno, job, count(*), avg(sal), stddev(sal), sum(sal) from emp group by deptno,job;
8. EMP테이블에서 부서인원이 4명 이상인 부서의 부서번호, 인원수, 급여의 합을 출력하라.
select deptno, count(*), sum(sal) from emp group by deptno having count(*)>=4 ;
9. EMP 테이블에서 업무가 PRESIDENT가 아닌 업무의 부서별 급여의 합계와 평균을 구하라.
select deptno, sum(sal), avg(sal) from emp where job!='PRESIDENT' group by deptno;
10. EMP 테이블에서 급여가 2900 이상인 부서에 대하여 부서번호, 평균급여, 최대급여, 최소급여를 구하여라.
select deptno, avg(sal), max(sal), min(sal) from emp group by deptno having avg(sal)>=2900;
11. EMP 테이블에서 전체 급여가 5000을 초과하는 각 업무에 대해 업무와 급여 합계를 출력하라. 단, SALESMAN은 제외하고 급여 합계를 내림차순으로 정렬하라.
select job, sum(sal) from emp where lower(job)!=lower('SALESMAN') group by job having sum(sal)>5000;
12. 부서별 평균 중 최대 평균 급여, 부서별 급여의 합 중 최대 급여, 부서별 급여의 최소 급여, 부서별 급여의 최대 급여를 출력하라.
select max(avg(sal)), max(sum(sal)), min(min(sal)), max(max(sal)) from emp group by deptno;
13.다음의 결과를 작성하는 SELECT문을 작성하라.
select to_char(hiredate,'YY') as "H_YEAR", count(*), min(sal), max(sal), avg(sal), sum(sal) from emp group by to_char(hiredate,'YY');
14.아래의 급여의 합계를 출력하는 SELECT 문장을 작성하라.
select job, decode(deptno,10,sum(sal),20,NULL,30,NULL) "DEPTNO 10", decode(deptno,10,NULL,20,sum(sal),30,NULL) "DEPTNO 20", decode(deptno,10,NULL,20,NULL,30,sum(sal)) "DEPTNO 30", sum(sal) "TOTAL" from emp group by job, deptno;
//분리 되어 출력
select job, decode(deptno,10,sum(sal),20,NULL,30,NULL) "DEPTNO 10", decode(deptno,10,NULL,20,sum(sal),30,NULL) "DEPTNO 20", decode(deptno,10,NULL,20,NULL,30,sum(sal)) "DEPTNO 30", sum(sal) "TOTAL" from emp group by job;
//출력불가
select job, sum(sal*(deptno-20)*(deptno-30)/200) "DEPTNO 10", sum(sal*(deptno-10)*(deptno-30)/(-100)) "DEPTNO 20", sum(sal*(deptno-10)*(deptno-20)/200) "DEPTNO 30", sum(sal) "TOTAL" from emp group by job;
//먼가 부족
select job, sum(decode(deptno,10,sal,20,0,30,0)) "DEPT 10", sum(decode(deptno,10,0,20,sal,30,0)) "DEPT 20", sum(decode(deptno,10,0,20,0,30,sal)) "DEPT 30", sum(sal) "TOTAL" from emp group by job;
//좀더 쉬운 풀이**
15. 아래의 인원수의 합계를 출력하는 SELECT 문장을 작성하라.
select to_char(hiredate,'YYYY'), count(*) "TOTAL" from emp group by rollup(to_char(hiredate,'YYYY'));
//이건 원하시는 답이 아닐듯
select count(*) "TOTAL",
count(NULLIF(to_char(hiredate,'YYYY'), '1981')*NULLIF(to_char(hiredate,'YYYY'), '1982')*NULLIF(to_char(hiredate,'YYYY'), '1987')*1) "1980",
count(NULLIF(to_char(hiredate,'YYYY'), '1980')*NULLIF(to_char(hiredate,'YYYY'), '1982')*NULLIF(to_char(hiredate,'YYYY'), '1987')*1) "1981",
count(NULLIF(to_char(hiredate,'YYYY'), '1980')*NULLIF(to_char(hiredate,'YYYY'), '1981')*NULLIF(to_char(hiredate,'YYYY'), '1987')*1) "1982",
count(NULLIF(to_char(hiredate,'YYYY'), '1980')*NULLIF(to_char(hiredate,'YYYY'), '1981')*NULLIF(to_char(hiredate,'YYYY'), '1982')*1) "1987" from emp;
//돌려 풀기
select sum(count(*)) "TOTAL", sum(decode(to_char(hiredate,'YYYY'),1980,count(*),1981,NULL,1982,NULL,1987,NULL)) "1980",
sum(decode(to_char(hiredate,'YYYY'),1980,NULL,1981,count(*),1982,NULL,1987,NULL)) "1981",
sum(decode(to_char(hiredate,'YYYY'),1980,NULL,1981,NULL,1982,count(*),1987,NULL)) "1982",
sum(decode(to_char(hiredate,'YYYY'),1980,NULL,1981,NULL,1982,NULL,1987,count(*))) "1987"
from emp group by to_char(hiredate,'YYYY');
//억지완성
select count(*), count(decode(to_char(hiredate,'yy'),'80',job)) "1980", count(decode(to_char(hiredate,'yy'),'81',job)) "1981", count(decode(to_char(hiredate,'yy'),'82',job)) "1982", count(decode(to_char(hiredate,'yy'),'87',job)) "1987" from emp;
//가시나답변**
