ORACLE) decode 및 그룹함수 실습 풀이
--decode(표현식, 조건1, 결과1,
-- 조건2, 결과2,
-- 기본결과n
-- )
select eno, ename, job, salary,
decode(job,'analyst',salary+200,
'salesman',salary+180,
'manager',salary+150,
'clerk',salary+100,
salary) as update_salary
from employee;
select max(salary),
min(salary),
sum(salary),
round(avg(salary))
from employee;
--급여 최고 최저 총액 평균급여
select job as "Job",
max(salary),
min(salary),
sum(salary),
round(avg(salary))
from employee
group by job;
select job, count(*)
from employee
group by job;
select count(manager)
from employee;
--담당업무별
select job as "Job",
max(salary),
min(salary),
sum(salary),
round(avg(salary))
from employee
group by job;
select job,count(*)
from employee
group by job;
select count(manager)
from employee;
select max(salary)-min(salary) as difference
from employee;
select job, min(salary)
from employee
group by job
having not min(salary) <2000
order by min(salary) desc;
select dno,
count(*),
round(avg(salary),2) as "Salary"
from employee
group by dno
order by dno asc;
댓글