SQL 문제풀기 2

6.4

직원과 부서테이블을 join하여 사원의 이름과 부서, 부서명을 나타내시오

select s_emp.name, s_dept.name
from s_emp, s_dept
where e_emp.dept_id = s_dept.id
;

6.5 alias 사용

select e.name, e.dept_id, d.name
from s_emp e, s_dept d
where e.dept_id = d.id;
;

6.5

서울 지역에 근무하는 사원에 대해 각 사원의 이름과 근무하는 부서명을 나타내시오

select e.name as 사원명, r.name as 지역명, d.name as 부서명
from s_emp e, s_dept d, s_region r
where e.dept_id = d.id
and d.region_id = r.id
and r.id = 1
order by 2, 1
;

6.6

non-equijoin 각 사원의 이름과 급여등급을 나타내시오

select e.name, e.salary, g.grade 급여등급
from s_emp e, salgrade g
where e.salary between g.losal and g.hisal
;

6.7

outer join 이름, 사번, 담당고객 이름. 단, 고객에 대하여 담당영업사원이 없더라도 모든 고객의 이름을 나타내고, 사번순으로 오름차순 정렬

select e.name, e.id, c.name
from s_emp e, s_customer c
where e.id (+) = c.sales_rep_id -- (+)를 빼면?
--where e.id = c.sales_rep_id (+) -- (+)위치를 바꾸면?
order by 2
;

6.8 self join

select w.id 사번, w.name 사원명, m.id 부서장사번, m.name 부서장명
from s_emp w, s_emp m
where w.manager_id = m.id
order by 3
;

6.8

self join 직원중에 ‘김정미’와 같은 직책을 가지는 사원의 이름과 직책, 급여, 부서번호를 나타내시오

select e1.name, e1.title, e1.dept_id
from s_emp e1, s_emp e2
where e1.title = e2.title
and e2.name = '김정미'
and e1.name <> '김정미'
order by 1
;

6.11 set 연산자

select name, dept_id, title
from s_emp
where dept_id = 110
union
select name, dept_id, title
from s_emp
where dept_id = 113
order by 1
;

from절에서의 subquery

select e.name, e.title, d.name
from
(select name, title, dept_id
from s_emp
where title = '사원') e, s_dept d
where e.dept_id = d.id
;

7.7 having 절에서의 subquery

select avg(salary)
from s_emp
group by dept_id
having avg(salary) >
    (select avg(salary)
    from s_emp
    where dept_id = 113)
;

7.7

having 절에서의 가장 적은 평균급여를 받는 직책에 대해 그 직책과 평균급여를 나타내시오. (*****)

--1) 서브쿼리 3개
select title, avgsalary
from
    (select title, avg(salary) as avgsalary
    from s_emp
    group by title
    )
where avgsalary =
    (select min(avgsalary)
    from
        (select avg(salary) as avgsalary
        from s_emp
        group by title
        )
    ) -- 809
;

--2) 서브쿼리 2개
select title, avg(salary)
from s_emp
group by title
having avg(salary) =
    (select min(avgsalary)
    from
        (select avg(salary) as avgsalary
        from s_emp
        group by title
        )
    ) -- 809
;

--3) 서브쿼리 1개
select title, avg(salary)
from s_emp
group by title
having avg(salary) =
    (select min(avg(salary))
    from s_emp
    group by title) -- 809
;

7.8 create 절에서의 subquery

create table emp_113 (id, name, mailid, start_date)
as select id, name, mailid, start_date
from s_emp
where dept_id = 113;

select * from emp_113;

컬럼 사이즈 변경

s_emp 테이블의 mailid 길이는 8로 설정되어 있다. 길이를 4로 변경하시오

--1. 기존 테이블 복사
CREATE table tmp as select * from s_emp;
--2. s_emp 테이블 모든 데이터 삭제
delete from s_emp;
select * from s_emp;
--3. s_emp테이블의 구조변경 (4 byte)
--4. tmp2의 데이터를 s_emp로 insert
insert into s_emp select id, name, substr(mailid,1,4) , start_date, manager_id, title, dept_id, salary, commision_pct from tmp2;

-- 다른풀이
update s_emp set mailid = substr(mailid, 1, 4);
-- 이후 s_emp테이블 구조 4바이트로 변경

drop table tmp;