[SQL] 20190531

1. 사원번호, 사원이름, 부서이름을 출력하는데, 부하직원 수가 적은 순으로 검색하시오.
1) 부하직원 수가 적은 순
select e.mgr, count(*)
from emp e
group by e.mgr
order by e.mgr, count(*);

2) 사원번호, 사원이름, 부서이름 (self-join)
select e.mgr, a.ename, count(*)
from emp e, emp a
where e.mgr = a.empno
group by e.mgr, a.ename
order by e.mgr, count(*);

select e1.ename, e2.empno, count(*)
from emp e1, emp e2
where e1.mgr = e2.empno
group by e2.mrg, e2.ename, e2.empno
order by 3;

------------

select e1.empno, e1.ename, d.dname, count(e2.mgr)
from emp e1 left outer join emp e2
on e1.empno = e2.mgr
join dept d on d.deptno = e1.deptno
group by e1.empno, d.dname, e1.ename
order by 4;

------------

SELECT A.EMPNO, A.ENAME, D.DNAME, COUNT(B.MGR) AS 부하직원수
FROM EMP A
LEFT OUTER JOIN EMP B ON A.EMPNO = B.MGR
JOIN DEPT D ON A.DEPTNO = D.DEPTNO
GROUP BY A.EMPNO, A.ENAME, D.DNAME
ORDER BY COUNT(B.MGR);

2. 사원 JAMES의 사원번호, 이름, 급여, 부서명과
그의 매니저 이름, 매니저 부서명을 출력하시오.
select e.empno, e.ename, e.sal, d.dname, e.mgr
from emp e, dept d
where e.deptno = d.deptno
and e.ename = 'JAMES';

1)
select e1.empno, d.dname
from emp e1, dept d
where e1.deptno = d.deptno;

2)
select e1.empno, e1.ename, e1.sal, d.dname, e2.ename, d2.dname
from emp e1, emp e2, dept d, dept d2
where e1.deptno = d.deptno
and e1.mgr = e2.empno
and e2.deptno = d2.deptno
and e1.ename = 'JAMES';

<서브쿼리 사용 가능>
where
having
insert문의 into절
update문장의 set절
select또는 delete문의 from절

- 평균 급여보다 높은 사원의 이름, 급여를 구하라.
select ename, sal
from emp
where sal >= 평균급여 ?
->
select avg(sal)
from emp
where sal >= 2073.21429;

* 서브쿼리 사용해서 풀이
select avg(sal)
from emp
where sal >= (select avg(sal)
                  from emp);

3. 부서 평균 급여가 전체 직원 평균 급여보다 많은 부서의 이름, 부서 평균 급여를 출력하시오.
select dept.deptno, dept.dname, emp.sal
from emp, dept
where dept.dname = emp.sal;


전체 직원 평균 급여
select avg(sal)
from emp;

부서별 평균 급여
select dept.dname, round(avg(sal))
from emp natural join dept
group by deptno, dept.dname
having avg(sal) > (전체 직원 평균 급여)

->
select dept.dname, round(avg(sal))
from emp natural join dept
group by deptno, dept.dname
having avg(sal) > (select avg(sal)
from emp);

4. SCOTT 사원보다 많은 급여를 받는 사원 정보를 출력하시오.
SCOTT 사원의 급여
select ename, sal
from emp
where ename = 'SCOTT';

사원의 정보
select *
from emp;

->
select *
from emp
where sal > (SCOTT 사원 급여);

->
select *
from emp
where sal > (select sal
    from emp
    where ename = 'SCOTT');

5. CHICAGO에서 근무하지 않은 모든 사원의 정보를 출력하시오.
select *
from emp
where deptno <> (시카고 지역번호);

시카고 지역번호
select deptno
from dept
where loc = 'CHICAGO';

->
from emp
where deptno <> ( select deptno
             from dept
 where loc = 'CHICAGO');

6. SALESMAN의 커미션 평균을 출력하시오.
select avg(nvl(comm, 0))
from emp
where job = 'SALESMAN';

* from에 테이블 넣기
select avg(comm)
from (select nvl(comm, 0) as comm
       from emp
       where job = 'SALESMAN');

select nvl(comm, 0)
from emp
where job = 'SALESMAN';
-> 0, 300, 500, 1400

7. 옥한빛 학생보다 키가 큰 학생들의 이름, 학과, 키를 출력하시오.
(단, 키 정보가 없는 학생은 제외하시오.)

select stu_name, stu_dept, nvl(stu_height, 0)
from student
where stu_height > (select nvl(stu_height, 0)
  from student
  where stu_name = '옥한빛');

select stu_name, stu_dept, stu_height
from student
where (stu_name, stu_height) = (select stu_name, stu_height
       from student
     where stu_name = '옥한빛');


- 각 부서별 최소 급여를 받는 사원의 이름과 급여를 구하라.

select ename, sal
from emp
where(deptno, sal) in (select deptno, min(sal)
    from emp
       group by deptno);

-> in은 여러가지 항목에서 하나라도 겹치면 출력함

각 부서별 최소 급여
select deptno, min(sal)
from emp
group by deptno;

8. 컴퓨터정보과의 최소 신장과 각 학과 최소 신장을 가진 학생을 비교하여
컴퓨터 정보과 최소 신장보다 큰 학과의 학과명과 최소 신장을 출력하시오.

컴퓨터정보과의 전체 신장
select stu_dept, min(stu_height)
from student
group by stu_dept
having stu_dept = '컴퓨터정보';

각 과의 최소 신장
select stu_dept, min(stu_height)
from student
group by stu_dept;

->
select stu_dept, min(stu_height)
from student
group by stu_dept
having min(stu_height) > (select min(stu_height)
        from student
        where stu_dept = '컴퓨터정보');

select dept, min_height
from (select stu_dept dept, min(stu_height) min_height
       from student
       group by stu_dept) a
where min_height > (select min(stu_height)
  from student
  where stu_dept = '컴퓨터정보');

학생-과
키-과
9. 학번, 학생명, 과명(기계, 전기전자 등), 키를 출력하고, 각 학생이 소속된 과의 평균키를 출력하시오.
학번, 학생명, 과명, 키
select stu_no, stu_name, stu_dept, stu_height
from student
order by stu_dept;

각 과의 평균 키
select stu_dept, avg(stu_height)
from student
group by stu_dept;

select s1.stu_no, s1.stu_name, s1.stu_dept, s2.stu_dept, s1.stu_height
from student s1 join student s2
on s1.stu_name = s2.stu_dept
order by s1.stu_dept;

-------
학번 학생명 과명 키
select stu_no, stu_name, stu_dept, stu_height
from student;

각 과의 평균 키
select stu_dept, avg(stu_height)
from student
group by stu_dept;

접접 = 소속된 과

select stu_no, stu_name, stu_dept, stu_height
from student, (select stu_dept, avg(stu_height)
      from student
      group by stu_dept) d_avg_height

테이블이 2개니까 무조건 조인함

select stu_no, stu_name, student.stu_dept, stu_height, avg_height
from student, (select stu_dept, avg(stu_height) avg_height
      from student
      group by stu_dept) d_avg_height
where student.stu_dept = d_avg_height.stu_dept;


10. 20번 부서 사원의 사원 직무와 같은 사원직무인 다른 부서의 사원 정보를 검색하라.
(in, any, some)
select *
from emp
where deptno != 20
and job in (select distinct job
  from emp
  where deptno = 20);