[SQL] 20190530

1. 부서번호 별로 정렬하여 모든 정보를 출력하는데 부서별 급여가 높은 사원부터 출력하시오.
select *
from emp
order by deptno, sal desc;

2. 부서번호, 부서별 평균 임금을 출력하시오.
단, 평균 임금이 2000이하인 부서는 제외
select deptno, round(avg(sal),0)
from emp
group by deptno
having avg(sal) > 2000
order by deptno;

3. 직무 중 가장 적게 수입을 가지는 직무의 평균 월급을 출력하시오.
select job, avg(sal)
from emp
group by job
having min(sal+nvl(comm, 0)) =
(select min(sal+nvl(comm, 0))
from emp);

select min(avg(sal))
from emp
group by job;

4. 기계과 학생들 중 학년별 평균 신장이 160이상인 학년과 평균 신장을 출력하시오.
select stu_grade, avg(stu_height)
from student
where stu_dept = '기계'
group by stu_grade
having avg(stu_height) >= 160;

5. 최대 신장이 175이상인 학과와 학과별 최대 신장을 출력하시오.
select stu_dept, max(stu_height)
from student
group by stu_dept
having max(stu_height) >= 175;

6. 학과별 평균 신장 중 가장 높은 평균 신장을 출력하시오.
select max(avg(stu_height))
from student
group by stu_dept;

서브쿼리 사용해서 학과도 나타내기
select stu_dept, avg(stu_height)
from student
group by stu_dept
having avg(stu_height) = (select max(avg(stu_height))
                                 from student
                                 group by stu_dept);

 

SQL DEVELOPER 실행

 

SQL Developer

위 버전으로 재설치했음.

 

select emp.ename, emp.job, dept.dname
from emp, dept
where emp.deptno = dept.deptno
and emp.job = 'MANAGER'
and emp.ename = 'CLARK';

 

oracle, natural join, join ~ on, join ~ using 4가지 문제풀이

 

7. 사원번호, 사원이름, 부서이름, 부서번호를 출력하시오.
- oracle
select emp.empno, emp.ename, dept.dname, dept.deptno
from emp, dept
where emp.deptno = dept.deptno;

select e.empno, e.ename, d.dname, d.deptno
from emp e, dept d
where e.deptno = d.deptno;

- natural join : 컬럼 알아서 확인해서 엮어줌
where문 안써도 됨
select empno, ename, dname, deptno
from emp natural join dept;

- join ~ on
select empno, ename, dname, dept.deptno
from emp join dept
on emp.deptno = dept.deptno;

- join ~ using
select empno, ename, dname, deptno
from emp join dept
using (deptno);

8. 부서가 30이고, 급여가 1500이상인 사원의 이름, 부서명, 급여를 출력하시오.
- oracle
select emp.ename, dept.dname, emp.sal
from emp, dept
where emp.deptno = dept.deptno
and dept.deptno = 30
and sal >= 1500;

- natural join (natural join은 식별자 X) ex : dept.deptno
select ename, dname, sal
from emp natural join dept
where deptno = 30
and sal >= 1500;

- join ~ on
select emp.ename, dept.dname, emp.sal
from emp join dept 
on emp.deptno = dept.deptno
where emp.deptno = 30
and emp.sal >= 1500;

- join ~ using (using 절은 식별자 X) ex : dept.deptno
select ename, dname, sal
from emp join dept
using (deptno)
where deptno = 30
and sal >= 1500;

* 참고
select emp.ename, dept.dname, emp.sal
from emp, dept
where emp.deptno = dept.deptno
and emp.deptno = 30
and emp.sal >= 1500;

9. 사원수가 5명이 넘는 부서의 부서명과 사원수를 출력하시오.
- oracle
select dept.dname, count(*)
from dept, emp
where emp.deptno = dept.deptno
group by emp.deptno, dept.dname
having count(*) >=5;

- natural join
select dname, count(dname)
from dept natural join emp
group by dname
having count(*) >=5;

- join ~ on
select dept.dname, count(*)
from emp join dept
on emp.deptno = dept.deptno
group by dname
having count(*) >= 5;

- join ~ using
select dname, count(*)
from emp join dept
using (deptno)
group by dname
having count(*) >= 5;

10. 각 부서의 이름과 가장 많은 월급의 크기를 출력하시오.
- oracle
select dept.dname, max(emp.sal)
from dept, emp
where emp.deptno = dept.deptno
group by dept.dname;

- natural join
select dname, max(sal)
from emp natural join dept
group by dname;

- join ~ on
select dept.dname, max(emp.sal)
from emp join dept
on emp.deptno = dept.deptno
group by dept.dname;

- join ~ using
select dname, max(sal)
from emp join dept
using (deptno)
group by dname;

11. ADAMS 사원이 근무하는 부서이름과 지역이름을 출력하시오.
- oracle
select dept.dname, dept.loc
from emp, dept
where emp.deptno = dept.deptno
and ename = 'ADAMS';

- natural join
select dname, loc
from emp natural join dept
where ename = 'ADAMS';

12. NEW YORK이나 DALLAS 지역에 근무하는 사원들의 사원번호, 사원이름을 사원번호 순으로 검색하시오.
- oracle
select emp.empno, emp.ename, dept.loc
from emp, dept
where emp.deptno = dept.deptno
and (loc = 'NEW YORK'
or loc = 'DALLAS')
order by emp.empno asc;

- natural join
select empno, ename, loc
from emp natural join dept
where loc = 'NEW YORK'
or loc = 'DALLAS'
order by empno asc;

- join ~ on
select emp.empno, emp.ename, dept.loc
from emp join dept
on emp.deptno = dept.deptno
and (dept.loc = 'NEW YORK'
or dept.loc = 'DALLAS')
order by emp.empno asc;

- join ~ using
select empno, ename, loc
from emp join dept
using (deptno)
where loc = 'NEW YORK'
or loc = 'DALLAS'
order by empno asc;

13. 부서이름이 ACCOUNTING이거나, 지역이름이 CHICAGO인 사원의 사원번호와 사원이름을 검색하시오.
- natural join
select empno, ename, dname, loc
from emp natural join dept
where dname = 'ACCOUNTING'
or loc = 'CHICAGO';

* sal 등급 확인 
- (non-equal) ANSI
select emp.ename, emp.sal, salgrade.grade 
from emp, salgrade
where emp.sal 
between salgrade.losal 
and salgrade.hisal;

- oracle
select emp.ename, emp.sal, salgrade.grade 
from emp, salgrade
where emp.sal >= salgrade.losal
and emp.sal <= salgrade.hisal;

14. 사원번호, 사원이름, 급여, 급여등급을 급여등급별 사원번호 순으로 검색하시오.
select e.empno, e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
order by 4, 1;

15. 사원의 상사 찾기 (salf 조인)
select e.ename, e2.ename
from emp e, emp e2
where e.mgr = e2.empno;

16.
select enrol.stu_no, student.stu_name
from enrol inner join student
on enrol.stu_no = student.stu_no;

17.
select emp.empno, emp.ename, dept.deptno
from emp right outer join dept
on emp.deptno = dept.deptno;

select emp.empno, emp.ename, dept.deptno
from dept, emp
on emp.deptno = dept.deptno(+);

18. 사원번호, 사원이름과 상사번호, 상사이름을 모두 출력하는 쿼리를 작성하시오.
(단, 사장님도 포함하여 출력할 수 있도록 작성하시오.)
select e.empno, e.ename, e.mgr, e2.ename
from emp e, emp e2
where e.mgr = e2.empno(+);

select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1 left outer join emp e2
on e1.mgr = e2.empno;