join & subquery 연습

update dept set dname='연구1부',loc='대전'where deptno=20;
update dept set dname='연구2부' where deptno=30;

 

/* 1. SAWON 테이블에서 일용이와 같은 부서에 있는 
  모든 사원의 이름과 입사일자를 출력하는 SELECT문을 
  작성하시오.*/

 

select s.name as '사원이름', s.hiredate as '입사일자', d.dname as '부서'
   from sawon s inner join dept d
   on s.deptno = d.deptno
   where d.deptno =(select deptno from sawon where name ='일용이')

 

select name, hiredate
 from sawon
 where deptno = (select deptno 
      from sawon
     where name = '일용이')
   and
   name != '일용이'

 

/* 2. SAWON 테이블에서 평균 급여 이상을 받는 모든 
  종업원에 대해서 종업원 번호와 이름을 출력하는 
  SELECT문을 작성하시오.
  단, 급여가 많은 순으로 출력하라. */     
 --총무부서 평균급여이상 받는 모든 사원을 적으시오

 

select sano '사원번호', name '사원이름', sal '급여'
from sawon
where sal >= (select avg(isnull(sal,0))
           from sawon)

 
select s.sano '사원번호', s.name '사원이름', s.sal '급여'
 from sawon s inner join dept d
 on s.deptno = d.deptno
 where sal >= (select avg(isnull(sal,0))
           from sawon)

 

 --총무부서 평균급여이상 받는 모든 사원을 적으시오

 

select sano,name,sal
from sawon
where sal >= (select avg(isnull(sal,0))
    from sawon s inner join dept d
    on s.deptno = d.deptno
    where d.dname='총무부')

 

select sano,name,sal
from sawon
where sal >=(select avg(isnull(sal,0))
    from sawon 
    where deptno =
    (select deptno
     from dept where dname = '총무부'))
  

  
select * from dept

/* 3. SAWON 테이블에서 이름에 '원' 이있는 사원이 
  근무하는 모든 종업원애 대해서 사원 번호, 이름, 
  급여를 출력하는 SELECT 문을 출력하시오. 
  단 사원번호순으로 출력하세요 */

select sano, name, sal
from sawon
where deptno in (select deptno
     from sawon
     where name like '%원%')

 

select sano, name, sal
from sawon
where deptno in (select distinct deptno    
     from sawon
     where name like '%원%')
 
 
/* 4. SAWON 테이블에서 부서 위치가 대전인 모든 종업원에 
  대해 이름, 직급, 급여를 출력하는 SELECT문을 
  작성하시오.  */
 
select name as '사원이름',jik as '직급',sal as '급여'
 from sawon s inner join dept d
 on s.deptno = d.deptno
 where d.loc ='대전'
 
      
/* 5. SAWON 테이블에서 한석규에게 보고하는 모든 사원의 
  이름과 급여를 출력하는 SELECT문을 작성하시오.*/

select [name] as '사원이름', sal as '급여'
 from sawon
 where mgr in (select s1.mgr
      from sawon s1 left outer join sawon s2
      on s1.mgr = s2.sano
      where s2.name like '한석규')

 

select [name] as '사원이름', sal as '급여'
 from sawon
 where mgr = (select sano from sawon where name='한석규')

 

select name,sal
from sawon a inner join (select sano from sawon where name='한석규') b
 on a.mgr=b.sano

/* 6. SAWON 테이블에서 총무부 사원의 이름 직급를 
  출력하는 SELECT문을 출력하시오*/

select name as '사원이름', jik '직급'
from sawon
where deptno = (select deptno
     from dept
     where dname='총무부');

 

select s.name as '사원이름', s.jik '직급'
 from sawon s join dept d
 on d.deptno = s.deptno
 where d.dname='총무부';

     

/* 7. 테이블에서 월급이 연구2부 최저 월급보다 
  높은 사원을 출력하는 SELECT문을 작성하시오. */

select name as '사원이름', sal '연봉'
from sawon
where sal > (select min(isnull(sal,0))
   from sawon s inner join dept d
   on s.deptno = d.deptno
   where d.dname ='연구2부');

 

              
/* 8. 사원테이블에서 부서10에서 부서30의 사원과 
  같은 업무를 맡고 있는 사원과 이름을 출력하시오*/

select name as '사원이름', jik as '직급'
 from sawon
 where deptno =10 and jik in (select distinct jik  -- in and
                        from sawon                    --  all 부등호 상관없이 and
         where deptno=30);               -- any 부등호 or
  
   
/* 9. 사원테이블에서 이미자와 직급도 월급도 
  같은 사원의 모든 정보를 출력하는 SELECT 문을 
  작성하시오 */                            

select *
from sawon
where sal = (select sal
    from sawon
    where name ='이미자')
   and jik = (select jik
     from sawon
     where name ='이미자')
   and name!='이미자'

 

select * 
from sawon s inner join (select sal,jik from sawon where name='이미자') a
on s.jik=a.jik
where a.sal=s.sal
   
/* 10. SAWON 테이블에서 직급이 고소영과 같거나 
  월급이 이미자이상인 사원의 정보를 이름,직급,
  부서번호,급여를 출력하는 SELECT 문을 작성하시오
  단,업무별,월급이 많은 순으로 출력*/

select name as '사원이름', jik as '직급', deptno as '부서번호', sal as '급여'
from sawon
where jik=(select jik
   from sawon
   where name = '고소영')
 or sal >= (select sal
    from sawon
    where name = '이미자')
order by 3,4 desc;
   

/* 11. SAWON 테이블에서 네석규 또는 박찬호와 월급이 
  같은 사원의 정보를 이름,직급,급여를 출력하는 
   SELECT 문을 작성하시오 */

 

select name as '사원이름', jik as '직급', sal as '급여'
from sawon
where name not in ('네석규','박찬호') and 
sal in (select sal from sawon where name in ('네석규','박찬호'))

 

/* 12. SAWON 테이블에서 서울에서 근무하는 사원과 
  같은 직급을 갖는 사원의 이름, 직급을 출력하는 
  SELECT문을 작성하시오 */

 

select s.name as '사원이름', s.jik as '직급'
from sawon s inner join dept d
on s.deptno = d.deptno
where jik in (select jik from dept where loc='서울')

/* 13. SAWON테이블에서 부서별로 월급이 부서별 평균보다 
  높은 사원을 부서번호, 이름,급여를 출력하는 
  SELECT문을 작성하시오 */

 

select deptno as '부서번호', name as '사원이름',sal as '급여'
from sawon
 where deptno=10 and sal > (select avg(isnull(sal,0)) from sawon where deptno=10)
  or   deptno=20 and sal > (select avg(isnull(sal,0)) from sawon where deptno=20)
     or   deptno=30 and sal > (select avg(isnull(sal,0)) from sawon where deptno=30) 
     or   deptno=40 and sal > (select avg(isnull(sal,0)) from sawon where deptno=40)

 

select deptno as '부서번호', name as '사원이름',sal as '급여'
from sawon s
where sal > (select avg(isnull(sal,0))
     from sawon 
    where deptno=s.deptno)

 

select a.deptno, a.name, a.sal
 from sawon a inner join
  (select deptno, avg(sal) as avg_sal
  from sawon
  group by deptno) b
 on a.deptno=b.deptno
 where a.sal > b.avg_sal
 
/* 14. 사원 테이블에서 직급별로 월급이 회사내 평균 
  월급보다 낮은 사원을 부서번호,이름,급여를 출력하는 
  SELECT 문을 작성하시오 */

 

select deptno as '부서번호', name as '사원이름',sal as '급여'
from sawon
 where sal < (select avg(isnull(sal,0)) from sawon)

select deptno as '부서번호', name as '사원이름',sal as '급여'
from sawon s
group by deptno, name, sal
having avg(isnull(sal,0)) < (select avg(isnull(sal,0))
     from sawon s
    where jik=s.jik
    )

 

select a.deptno as '부서번호', a.name as '사원이름',a.sal as '급여'
from sawon a inner join( select avg(isnull(sal,0)) avg_sal,jik
                  from sawon 
                  group by jik) b
on a.jik=b.jik
where a.sal < b.avg_sal

/* 15. 사원 테이블에서 적어도 한명 이상으로부터 보고를 
  받을 수 있는 사원을 업무, 이름, 사원번호, 
  부서번호를 출력하는 SELECT문을 작성하시오. */

select d.dname as '부서', s.name as '사원이름', s.sano as '사원번호', s.deptno as '부서번호'
from sawon s join dept d
on s.deptno = d.deptno
 where mgr not in(
            select max(mgr) from sawon
       where mgr is not null
       group by deptno )
  or mgr is null

 

select d.dname as '부서', s.name as '사원이름', s.sano as '사원번호', s.deptno as '부서번호'
  from sawon s join dept d
  on s.deptno = d.deptno
  where sano in(
   select sano from sawon
   intersect
   select mgr from sawon)

 

select d.dname as '부서', s.name as '사원이름', s.sano as '사원번호', s.deptno as '부서번호'
 from sawon s join dept d
  on s.deptno = d.deptno
 where sano in (select distinct mgr from sawon)

     
/* 16. SAWON 테이블에서 말단 사원의 사원번호, 이름, 
  업무, 부서번호를 출력하는 SELECT문을 작성하시오 */

select s.sano as '사원번호', s.name as '사원이름', d.dname as '업무' , d.deptno as '부서번호'
from sawon s inner join dept d
on s.deptno=d.deptno
 where s.mgr in (select max(mgr) from sawon
       where mgr is not null
       group by deptno )

 

select s.sano as '사원번호', s.name as '사원이름', d.dname as '업무' , d.deptno as '부서번호'
from sawon s inner join dept d
on s.deptno=d.deptno
 where s.sano not in (select distinct isnull(mgr,0) from sawon)