ORACLE을 기반으로하는 데이터베이스 배움터 (스스로 해보는 실습문제 1번 ~ 23번) [523P]

-- 1. 가장 오래 근무한 사원에 관한 모든 데이터를 검색하라

select *

from employee

where title = '사원' and hiredate <= (

    select min(hiredate)

    from employee

    where title = '사원');

 

 

-- 2. 최종철과 같은 부서에 근무하는 사원에 관한 모든 데이터를 검색하라

select *

from employee

where dno = (select deptno from department, employee

    where empname = '최종철' and deptno = dno);

 

-- 3. 회사 전체의 사원 수를 검색하라

select count(empno)

from employee;

 

-- 4. 회사에 몇개의 부서가 있는가를 검색하라.

select count(deptno)

from department;

-- 5. 기획부에 근무하는 사원들의 이름과 직급을 검색하라.

select empname, title

from employee, department

where deptname = '기획' and  deptno = dno;

 

--6. 한명 이상의 사원이 근무하는 부서의 개수를 검색하라.

select count(distinct deptno)

from employee, department

where deptno =dno

 

-- 7. 사원이 한명도 근무하지 않는 부서를 검색하라.

select deptno , deptname

from department

where not exists (select * from employee where dno = deptno);

 

-- 8. 사원이 한명 이상 속한 부서에 대해서 평균 급여를 검색하라.

select avg(salary)

from employee

group by dno;

 

-- 9. 부서에 속한 사원들의 평균 급여가 가장 많은 부서의 이름과 평균 급여를 검색하라.

select deptname, avg(salary)

from employee, department

where dno = deptno

group by deptno, deptname

having avg(salary) = (select max(avg(salary)) from employee group by dno);

 

-- 10. EMPLOYEE와 DEPARTMENT 릴레이션을 조인하고, 부서번호 순서에 따라 정렬하라.

select *

from employee, department

where dno=deptno

order by dno;

 

-- 11.모든 사원들을 직급별로 구룹화하고, 각 그룹별 사원수와 평균급여를 검색하라.

select title, count(*), avg(salary)

from employee

group by title;

 

-- 12. 모든 사원들을 직급별로 그룹화하고, 동일 직급을 갖는 사원 수가 2명이상인 직급에 대해서 직급, 사원수, 연간급여를 검색하라.

select title, count(*) emp_cnt, avg(salary)*12 Y_salary

from employee

group by title

having count(*) >= 2;

 

-- 13. 직급이 대리인 사원이 적어도 2명 이상 속한 부서의 이름을 검색하라.

select deptname

from department, employee

where title = '대리' and dno = deptno

group by deptname

having count(*) >= 2;

 

 

-- 14. 모든 부서에 대해서 이름, 층, 각 부서에 근무하는 사원 수를 검색하라 사원이 없는 부서도 포함시켜라.

select deptname, floor, count(dno)

from department left join employee on deptno = dno

group by deptname,floor;

 

 

-- 15.부서 1,2,3에 공통으로 있는 직급을 검색하라.

select title from employee where dno =1

INTERSECT

select title from employee where dno = 2

INTERSECT

select title from employee where dno = 3;

 

-- 16. 개발부서에서 근무하는 사원들의 직급과 직급별 사원 수를 검색하라. 사원수가 많은 직급부터 표시하라.

select title, count(empno)

from employee, department

where deptname = '개발' and deptno = dno

group by title

order by count(empno) desc ;

 

-- 17. 평균 급여가 가장 높은 부서의 부서번호와 이 부서의 최저 급여를 검색하라.

select dno, min(salary)

from employee

group by dno

having avg(salary) = (select max(avg(salary)) from employee group by dno);

 

-- 18. 소속 사원 수가 4명 이하인 부서의 이름과 사원 수를 검색하라.

select deptname, count(empno)

from department left join employee on deptno = dno

group by deptname

having count(*) <= 4;

 

-- 19. 자신이 속한 부서의 평균 급여보다 많이 받는 사원의 이름, 부서번호, 급여를 검색하라.

select empname, dno, salary

from employee e

where salary > (select avg(salary) from employee where e.dno = dno);

 

-- 20. 각 부서에서 가장 많은 급여를 받는 사원들의 이름, 부서번호, 급여를 검색하라.

select empname, dno, salary

from employee e

where salary = (select max(salary) from employee where e.dno = dno);

 

-- 21. 모든 사원에 대하여 사원번호, 이름, 급여, 부서번호, 소속부서의 평균 급여를 검색하라.

select empno, empname, salary, dno ,

(select avg(salary) from employee where dno = e.dno group by dno) avg_salary

from employee e;

 

-- 22. 최종철 또는 이수민과 같은 직급을 가진 모든 사원에 대해서 사원의 이름과 직급을 검색하라.

select empname, title

from employee

where title = (select title from employee where empname = '최종철') or

title = (select title from employee where empname = '이수민');

 

-- 23. 기획 또는 총무 부서에서 근무하지 않는 모든 사원들의 이름을 검색하라.

select empname

from employee

where dno in (select deptno from department where not deptname = '기획' and not deptname = '총무' );