[Oracle] 7.Oracle 문제 풀이 정리

--=======================================문제 풀이 =================================
--10번,20번 부서 사원들 중 최고 급여를 받는 사원의 사원번호 사원이름,급여를 검색하라
select eno,ename,salary,dno from employee  where salary >= (select max(salary) from employee) and (dno =10 or dno =20);

--30번 부서 사원들 중 최저 급여를 받는 사원의 사원번호, 사원이름, 급여를 검색하라
select eno, ename, salary from employee where dno = 30 group by eno, ename, salary having salary = (select min(salary) from employee where dno=30) ;

--전체 사원들 중 최고 커미션을 받는 사원의 사원번호 사원이름 커미션 출력
select eno, ename, commission from employee group by eno, ename, commission having commission = (select max(commission) from employee) ;

select * from department;

--부서별 사원들의 인원수
select dno,count(*) from employee group by dno;

--부서별 사원들의 평균급여 소수점 2자리
select dno,ROUND(AVG(salary),2) as 평균급여 from employee group by dno;

--부서별 사원직무별 사원들의 평균 급여
select dno,job,ROUND(AVG(salary),2) as 평균급여 from employee group by dno,job;

--부서별 사원직무별 사원들의 입사일의 MAX와 MIN 값을 검색하라
select dno,job, MAX(hiredate) as "입사일MXX", MIN(hiredate) as "입사일MIN"  from employee group by dno,job;

--부서별 사원들의 인원수를 인원수가 많은 순으로 검색하라
select count(dno) from employee group by dno order by dno desc;

--부서별 사무직무별 급여합을 많은 순서순으로
select job,sum(salary) from employee group by job order by sum(salary) desc;

--부서별 사원직무별 사원들의 평균 급여를 평균급여가 많은 순으로 검색하라
select job,avg(salary) from employee group by job order by avg(salary) desc;

--현재 입사자들 중에서 직업별로 평균 salary 이상 받는 사람들을 조사하라
select JOB, count(salary) as "평균 이상자" from employee where salary >= (select avg(salary) from employee) group by job;

--현재 dno가 10이 아닌 사원들 중에 평균 급여 이상으로 받고 있는 사원들을 group by로 나타내시오
select ename, avg(salary) from employee where dno != 10 and salary >= (select avg(salary) from employee) group by ename;

--위의 표인 emp + dpr 를 from 으로 받아서 eno ename manger dname loc 를 출력하는
select * from employee inner join department on employee.dno = department.dno;
select eno,ename, manager, dname, loc from (select * from employee inner join department on employee.dno = department.dno);

select*from employee;
select * from department;
--사원번호, 사원이름, 부서이름을 검색하라
select eno,ename,job from employee inner join department on employee.dno=department.dno group by eno,ename,job; 

--지역이 newyork인 사원이름을 검색하라
select ename, loc from employee inner join department on employee.dno=department.dno group by ename,loc having loc = 'NEW YORK'; 

--adams 사원이 근무중인 부서이름과 지역을 검색하라
select ename,job,loc from employee inner join department on employee.dno=department.dno group by ename,job,loc having ename='ADAMS';

--급여가 2000이상인 사원들의 사원명과 지역을 검색하라
select ename,salary,loc from (select ename,salary,loc from employee inner join department on employee.dno=department.dno group by ename,salary,loc) where salary >= 2000;

--급여가 1000이상 2000이하인 사원들의 사원번호, 사원이름, 부서이름을 사원번호 순으로 검색하라
select eno,ename,salary,job from employee inner join department on employee.dno=department.dno
group by eno,ename,salary,job having salary >=1000 and salary <= 2000 order by eno;

--사원 직무가 salesman이면서 chicago 지역에 근무중인 사원명을 검색하라
select job,loc,ename from employee inner join department on employee.dno=department.dno
group by job,loc,ename having job = 'SALESMAN' and loc = 'CHICAGO';

--부서 이름이 ACCOUNTING 이거나 지역이 CHICAGO인 사원의 사원번호와 사원이름을 검색하라
select dname, loc, eno, ename from employee inner join department on employee.dno=department.dno
group by dname, loc, eno, ename having dname = 'ACCOUNTING' or loc = 'CHICAGO';

--사원번호, 사원이름, 상급자 사원번호, 상급자 이름을 검색하라
select*from employees;
select employees.eno as "사원번호",  employees.ename as "사원이름", manager.ename as "상급자이름", manager.eno as"상급자 사원번호"
from employee employees, employee manager where employees.manager = manager.eno;

--BLAKE사원보다 많은 급여를 받는 사원이름을 검색하라(SELF JOIN)
select ename, salary from employee where salary>=(select salary from employee where ename='BLAKE');


salary>=(select a.salary from employee where ename='BLAKE')



--부서별 인원수를 부서이름과 함께 사원이 많은 순으로 검색하라
select dname, count(job) as "부서별 인원수" from employee inner join department on employee.dno=department.dno group by dname order by count(job) desc;


select a.eno,a.ename,b.manager,b.ename from employee a inner join employee b
on b.manager=a.eno;

--scoot 급여를 알아냅니다
select salary from employee where ename = 'SCOTT'; --3000

--scott 보다 급여가 많은 사원을 검색합니다.
select ename,salary from employee where salary >3000; --king 5000

--이 두개의 쿼리문을 결합

select ename,salary from employee where salary > (select salary from employee where ename = 'SCOTT');
--메인 쿼리라는 전체의 부분에서 > 이후 서브쿼리 출현

--최소 급여를 받는 사원이름 , 담당 업무, 급여 출력하기
SELECT ename,job,salary from employee where salary = (select min(salary) from employee);

--30번 부서에서 최소급여를 구한 후 부서별 최소 급여가 구한 최소급여보다 큰 부서만 출력
select dno, min(salary) from employee group by dno having min(salary) > (select min(salary) from employee where dno = 30);

--사원 번호가 7788인 사원과 담당 업무가 같은 사원을 표시(사원이름,담당업무)하시오.
select ename,job from employee where job = (select job from employee where eno=7788);

--사원번호가 7499인 사원보다 급여가 많은 사원을 표시(사원이름과 담당 업무)하시오
select ename,job from employee where salary > (select salary from employee where eno = 7499);

--최소 급여를 받는 사원의 이름,담당 업무 및 급여를 표시하시오
select ename, job,salary from employee where salary = (select min(salary) from employee);

--평균 급여가 가장 적은 사원의 담당 업무를 찾아 직급과 평균 급여를 표시하시오
select job,round(avg(salary),1) from employee group by job having round(avg(salary),1) = (select min(avg(salary)) from employee group by job);

-- 각 부서의 최소급여를 받는 사원의 이름,급여,부서번호를 표시하시오
select ename,salary,dno from employee where salary in (select min(salary) from employee group by dno);

--담당 업무가 분석가(ANALYST)인 사원보다 급여가 적으면서 업무가 분석가(ANALYST)가 아닌 사원들을 표현하시오
select eno,ename,job,salary from employee where salary < ANY (select salary from employee where job = 'ANALYST') and job!='ANALYST';

--부하직원이 없는 사원의 이름을 표시하시오.
select ename from employee where eno in (select eno from employee where manager is null);

--부하직원이 있는 사원의 이름을 표시하시오
select ename from employee where eno in (select eno from employee where manager is not null);

--BLAKE와 동일한 부서에 속한 사원의 이름과 입사일을 표시하시오
select ename, hiredate from employee where dno = (select dno from employee where ename = 'BLAKE') and ename != 'BLAKE';

--급여가 평균 급여보다 많은 사원들의 사원번호와 이름을 표시하되 결과를 급여에 대해서 오름차순으로 정렬 하시오
select eno,ename from employee where salary > (select avg(salary) from employee) order by salary;

--이름에 K가 포함된 사원과 같은 부서에서 일하는 사원의 사원번호와 이름을 표시하는 질의를 작성하시오
select eno , ename from employee where dno in (select dno from employee where ename like '%K%');

--부서 위치가 dallas인 사원의 이름과 부서번호 및 담당 업무를 표시하시오
select eno,dno,job from employee where dno = (select dno from department where loc = 'DALLAS');

--King에게 보고하는 사원의 이름과 급여를 표시하시오
select ename,salary from employee where manager = (select eno from employee where ename = 'KING');

--RESEARCH 부서의 사원에 대한 부서번호, 사원이름 및 담당 업무를 표시하시오
select dno,ename,job from employee where dno = (select dno from department where dname = 'RESEARCH');

--평균 급여보다 많은 급여를 받고 이름에 M이 포함된 사원과 같은 부서에서 근무하는 사원의 사원번호,이름,급여를 표시하시오
select eno,ename,salary from employee where salary >(select avg(salary) from employee) and dno in (select dno from employee where ename like '%M%');

--평균 급여가 가장 적은 업무를 찾으시오
select job,avg(salary) from employee group by job having avg(salary) = (select min(avg(salary)) from employee group by job);

--담당 업무가 manager인 사원이 소속된 부서와 동일한 부서의 사원을 표시하시오
select ename from employee where eno in (select manager from employee);

--문제 : employy의 ename과 employees의 first 이 같을 경우 phone_number를 추가하는 테이블을 만들고싶다.
--해당 쿼리문 (ename, first_name, phone_number 출력)

select ename, UPPER(FIRST_NAME), phone_number
from employee inner join employees on employee.ename = UPPER(employees.first_name);

--maneger가 담당하는 사원이 몇명인지 파악하여라.

select A.employee_id, A.first_name, A.manager_id, B.employee_id, B.first_name
from employees A inner join employees B on A.manager_id=B.employee_id;

select*from employees;

select A.employee_id, A.first_name, count(B.employee_id) "매니저의 담당 사원 수"
from employees A inner join employees B on A.employee_id = B.manager_id
group by A.employee_id, A.first_name;

--예시 select * from table1, table2 where table1.key = table2.key;
select ename,job,dname,loc from employee A, department B where A.dno = B.dno;

select A.eno,A.ename,B.dname,A.dno from employee A INNER JOIN department B on A.dno = B.dno and A.eno = 7788;

select eno,ename,dname,A.dno from employee A, department B where A.dno = B.dno and eno = 7788;

--위 테이블에서 grade가 3 이상인 사람의 eno,ename,dname,loc,grade를 출력하면서 grade가 가장 큰 것부터 나열하라.

select * from employee A inner join department B on A.dno= B.dno inner join salgrade C on A.salary between C.losal and C.hisal;

select eno,ename,dname,loc,grade from
(select * from employee A inner join department B on A.dno= B.dno inner join salgrade C on A.salary between C.losal and C.hisal);

--scott 사원보다 많은 급여를 받는 사원 정보를 검색하라
select * from employee where salary > (select salary from employee where ename = 'SCOTT');
--줘 사원정보컬럼을 급여(salary)조건으로  >(보다 큰 ) SCOTT 사원의 급여 정보

--20번 부서의 사원의 사원직무와 같은 사원직무인 다른 부서의 사원정보를 검색하라
select * from employee where dno != 20 and job in(select job from employee where dno = 20);

--전체 사원의 평균급여보다 급여가 많은 사원 정보를 검색하라.
select * from employee where salary > (select avg(salary) from employee);

--급여가 모든 부서들의 평균 급여보다 많은 사원 정보를 검색하라 
select * from employee where salary > all(select avg(salary) from employee group by dno);;

--20번 부서의 최대 급여보다 최대급여가 큰 부서의 번호와 최대 급여를 검색하라
select dno,max(salary) from employee group by dno having max(salary)
>(select max(salary) from employee where dno=20);

--CHICAGO 지역에 위치하는 부서에 근무하는 사원 정보를 검색하라
select * from employee where dno = (select dno from department where loc= 'CHICAGO');

--급여와 커미션을 합한 급액으로 상위 7명의 사원번호와 사원 이름을 구하다.
select eno, ename from (select eno,ename,nvl(salary+commission,salary) result from employee order by result desc) where rownum<=7;

select eno, ename from (select eno,ename,nvl(salary+commission,salary) result from employee order by result desc); where rownum<8;
--select eno,ename,nvl(salary+commission,salary) as "급여와 커미션" from employee; 연봉과 커미션을 null에 salary를 넣어 연봉+커미션 /연봉 값이 나옴

--부서별 평균 급여가 가장 큰 부서 2개 부서이름을 구하라
select dname from department dpt, (select emp.dno, round(avg(salary)) avgsalary
from employee emp group by dno order by avgsalary desc)
s where dpt.dno=s.dno and rownum<=2;

--부서 번호가 30번인 사원들의 최소급여보다 더 적은 급여를 받는 사원을 출력
select * from employee where salary <(select min(salary) from employee where dno = 30);

--SMITH의 존재 유무를 EXISTS 연산자를 사용해 확인 
select ename from employee where exists(select ename from employee where ename='SMITH');