<DevNote />

--각 부서별 평균급여를 구하되, 평균급여가 2000이상인 부서만 나타내시오

select dept_id

, avg(salary)

from s_emp

group by dept_id

having avg(salary) >= 2000

;

--각 직책별로 급여의 총합을 구하되 직책이 사원인 사람은 제외

--단, 급여총합이 3000이상인 직책만 나타내며, 급여 총합에 대한 내림차순 정렬

select title

, sum(salary)

from s_emp

where title <> '사원' 

group by title

having sum(salary) >= 3000

order by sum(salary) desc

;

--직급이 '부장'인 사람이 2명 이하인 부서가 몇개인지 나타내시오

select count(*)

from (

    select dept_id

    , count(*)

    from s_emp

    where title like '%부장'

    group by dept_id

    having count(*) <= 2 )

;

--담당 직원이 배정되지 않은 고객을 모두 나타내는 select문을 작성하시오

select e.id

, c.sales_rep_id

, c.name

from s_customer c

, s_emp e

where e.id (+)= c.sales_rep_id

and e.id is null

;

--전체 임직원 중에서 연봉이 제일 높은 3명의 이름과 연봉을 출력

select name

, salary

from (select *

    from s_emp

    order by salary desc)

where rownum < 4

;

select name, salary, rownum

from s_emp

;

select name

, salary

, rownum

from s_emp

order by name desc

;

--아래의 SQL문은 비효율적이다 not exists를 이용해서 효율적으로 작성하시오

select *

from s_emp

where title <> '사원'

;

select id

, name

, title

from s_emp e

where not exists (select 'X'

                from s_emp

                where e.title = '사원')

;

--18년 2월 마지막날

select to_date('18/03/01')-1 

from dual

;

select last_day('18/02/01')

from dual

;

--각 부서별 평균 급여를 계산해서 보여주시오

select dept_id

, avg(salary)

from s_emp

group by dept_id

;

--각 부서별 직책이 사원인 직원들의 평균 급여를 계산해서 보여주시오

select dept_id

, title

, avg(salary)

from s_emp

where title='사원'

group by dept_id

, title

;

--각 지역별로 몇 개의 부서가 있는지를 나타내시오

select region_id, count(name)

from s_dept

group by region_id

;

--각 부서별로 평균 급여를 구하되 평균 급여가 2000이상인 부서만 나타내시오

select dept_id, avg(salary)

from s_emp

group by dept_id

having avg(salary) >= 2000

;

--각 직책별 급여의 총합을 구하되 직책이 부장인 사람은 제외

--단, 급여총합이 8000 이상인 직책만 나타내며, 급여 총합에 대한 오름차순 정렬

select title

, sum(salary)

from s_emp

where title not like '%부장'

group by title

having sum(salary) >= 8000

order by sum(salary)

;

--각 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오

select dept_id

, title

, avg(salary)

from s_emp

where title = '사원'

group by dept_id

, title

;

--각 부서내에서 각 직책별로 몇 명의 인원이 있는지를 나타내시오

select dept_id

, title

, count(title)

from s_emp

group by dept_id

, title

order by dept_id

, title

;

--각 부서내에서 몇 명의 직원이 근무하는지를 나타내시오

select dept_id

, count(id)

from s_emp

group by dept_id

order by dept_id

;

--각 부서별로 급여의 최소값과 최대값을 나타내시오

--단 최소값과 최대값이 같은 부서는 출력하지 마시오

select dept_id

, min(salary) "최소연봉"

, max(salary) "최대연봉"

from s_emp

group by dept_id

having min(salary) != max(salary)

order by dept_id

;

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

select e.name

, e.dept_id

, d.name 부서명

from s_emp e

, s_dept d

where e.dept_id = d.id

;

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

select e.name

, e.title

, d.name

, r.name

from s_dept d

, s_emp e

, s_region r

where d.id = e.dept_id

and d.region_id = r.id

and r.name = '서울특별시'

and e.title = '사원'

;

--직원 테이블과 급여 테이블을 조인하여 사원의 이름과 급여, 해당 급여등급을 나타내시오

select e.name

, e.title

, e.salary

, s.grade

from s_emp e

, salgrade s

where e.salary between s.losal and s.hisal

and e.title = '사원'

order by s.grade

;

--직원 테이블과 고객 테이블에서 사원의 이름과 사번, 각 사원의 담당고객 이름을 나타내시오

--단 고객에 대하여 담당영업사원이 없더라도 모든 고객의 이름을 나타내고

--사번 순으로 오름차순 정렬하시오

select e.name 사원이름

, e.id

, c.name 담당고객

from s_emp e

, s_customer c

where e.id (+)= c.sales_rep_id

order by 2

;

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

select b.name

, b.title

, b.salary

, b.dept_id

from s_emp a, s_emp b

where a.title = b.title

and a.name = '김정미'

and b.name != '김정미'

;

--부서넘버 110, 113 데이터 합

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

;

--single row subquery

select name

, title

, dept_id

from s_emp

where dept_id = (select dept_id

                from s_emp 

                where name = '김정미')

;

--multi row subquery

select name

,dept_id

from s_emp

where dept_id in (select id

                from s_dept

                where region_id = 3)

;

--multi-column subquery

select name

, dept_id

, salary

from s_emp

where (salary, dept_id) in

    (select min(salary), dept_id

    from s_emp

    group by dept_id)

;

--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

;

--가장 적은 평균급여를 받는 직책에 대해 그 직책과 평균급여를 나타내시오

select title

, avg(salary)

from s_emp

group by title

having avg(salary) = (select min(avg(salary))

                    from s_emp

                    group by title)

;

--각 부서별 평균 급여

select dept_id, avg(salary)

from s_emp

group by dept_id

;

--각 부서별 직책이 사원인 직원들의 평균 급여

select dept_id, title, avg(salary)

from s_emp

group by dept_id, title

having title='사원'

;

--각 지역별 몇 개의 부서가 있는지 나타내시오

select address, count(region_id) as "부서 갯수"

from s_customer

group by address

;

--각 부서별 평균 급여, 평균 급여가 2000이상인 부서만 나타내시오

select dept_id, avg(salary)

from s_emp

group by dept_id

having avg(salary) >= 2000

order by dept_id

;

--각 직책별로 급여의 총합을 구하되 직책이 부장인 사람은 제외

--급여총합이 8000 이상인 직책만 나타내며, 급여 총합에 대한 오름차순 정렬

select title, sum(salary)

from s_emp

where title not like '%부장'

group by title

having sum(salary) >= 8000

order by sum(salary)

;

--각 부서별 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오

select dept_id, title, avg(salary)

from s_emp

group by dept_id, title

having title = '사원'

;

--각 부서내에서 각 직책별로 몇 명의 인원이 있는지를 나타내시오

select title

, count(id)

from s_emp

group by title

;

--각 부서내에서 몇 명의 직원이 근무하는지를 나타내시오

select dept_id

, count(id)

from s_emp

group by dept_id

order by dept_id

;

--각 부서별로 급여의 최소값과 최대값을 나타내시오 

--단, 최소값과 최대값이 같은 부서는 출력하지 마시오

select dept_id

, max(salary) as "최대 급여"

, min(salary) as "최소 급여"

from s_emp

group by dept_id

having max(salary) != min(salary)

order by dept_id

;

--직원(s_emp) 테이블과 부서(s_dept)테이블을 join하여,

--사원의 이름과 부서, 부서명을 나타내시오

select e.name

, e.title

, e.dept_id

, d.name

from s_emp e, s_dept d

where e.dept_id = d.id 

and e.title = '사원'

;

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

select

from

;

--시퀀스 생성

create sequence c_emp_id

increment by 1

start with 26

maxvalue 9999999

nocache

nocycle

;

--시퀀스 생성 확인

select sequence_name

, min_value

, max_value

, increment_by

, last_number

from user_sequences

;

--s_emp 테이블에서 이름은 홍길동, 급여는 2000, 나머지는 null을 입력하되, 

--사번은 sequence값을 이용하시오

insert into s_emp (id, name, salary) values (c_emp_id.nextval ,'홍길동', 2000);

select * from s_emp

;

--s_emp 테이블의 이름 컬럼에 인덱스를 추가하시오

create index idx_name

on s_emp(name)

;

--뷰 생성

create view vw_emp

as select *

from s_emp

;

select * from vw_emp

;

select view_name, text from user_views;

select name

, salary

, decode(trunc(salary/1000), 0, 'E', 1, 'D', 2, 'C', 3, 'B', 'A') 급여등급

from s_emp

;

select id

, name

, case salary when 1000 then 'Low'

                when 5000 then 'High'

                else 'Medium'

  end

from s_emp

;

select id

, name

, case when salary < 1000 then 'Very Low'

        when salary >= 1000 and salary <= 5000 then 'Low'

        when salary >= 5000 then 'High'

        else 'Medium'

  end

from s_emp

;

select * 

from (select dept_id, title from s_emp)

pivot (

    count(*)

    for title in('사원', '과장', '부장', '이사', '사장')

)

order by dept_id

;

select dept_id,

count(decode(title, '사원', 0)) "사원",

count(decode(title, '과장', 0)) "과장",

count(decode(title, '부장', 0)) "부장",

count(decode(title, '이사', 0)) "이사",

count(decode(title, '사장', 0)) "사장"

from s_emp

group by dept_id

order by dept_id

;

select name

, salary

, dept_id

from s_emp outer

where salary < (select avg(salary)

                from s_emp

                where dept_id = outer.dept_id)

order by dept_id, name

;

select name

, salary

, dept_id

from s_emp

where salary < any(select avg(salary) 

                from s_emp

                group by dept_id)

;

-- 관리자인 직원의 사번, 이름, 직책, 부서번호를 나타내시오

select id

, name

, title

, dept_id

from s_emp e

where exists (select id from s_emp

            where manager_id = e.id)

;

select rownum

, id

, name

from s_emp

where rownum <= 5

; 

select dept_id, max(salary)

from s_emp

group by dept_id

order by dept_id;

--각 지역별로 몇개의 부서가 있는지 나타내시오

select name, count(id)

from s_dept

group by name;

--각 부서별로 평균 급여를 구하되 평균 급여가 2000이상인 부서만

select dept_id, avg(salary)

from s_emp

group by dept_id

having avg(salary)>=2000

order by dept_id;

-- 부서에서 사원이 1명 이하인 부서를 출력 

select dept_id, title, count(*)

from s_emp

where title='사원'

group by dept_id, title

having count(*)<=1;

--각 직책(title)별로 급여(salary)의 총합을 구하세요(단, 부장 제외)

--급여총합이 8000만원 이상인 직책

--급여총합을 오름차순으로 정렬

select title, sum(salary)

from s_emp

where title not like '%부장'

group by title

having sum(salary) >= 8000

order by sum(salary);

--각 부서별(dept_id) 로 직책(title)이 사원인 직원들에 대해 평균급여

select dept_id, title, avg(salary)

from s_emp

where title = '사원'

group by dept_id, title

order by dept_id

;

-- 각 부서(dept_id) 내에서 각 직책(title)별로 몇명의 인원이 있는지를 나타내시오

select dept_id, title, count(*)

from s_emp

group by dept_id, title

;

-- 각 부서 내에서 몇명의 직원이 근무하는지를 나타내시오

select dept_id, count(*)

from s_emp

group by dept_id

;

-- 각 부서별로 급여의 최소값과 최대값을 나타내시오

-- 단, 최소값과 최대값이 같은 부서는 출력하지 마시오

select dept_id, max(salary) as "최고연봉" , min(salary) as "최소연봉"

from s_emp

group by dept_id

having max(salary)<>min(salary)

order by dept_id

;

select s_emp.name, s_emp.dept_id, s_dept.name

from s_emp, s_dept

where s_emp.dept_id = s_dept.id

;

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

select a.name, a.dept_id, b.name

from s_emp a, s_dept b

where a.dept_id = b.id

;

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

select e.name, d.name, s.name, e.title

from s_region s, s_dept d, s_emp e

where s.id = d.region_id and d.id = e.dept_id 

    and s.name like '서울%' and e.title ='사원'

;

select e.name, e.salary, g.grade

from s_emp e, salgrade g

where e.salary between g.losal and g.hisal

;

select g.grade, count(g.grade)

from s_emp e, salgrade g

where e.salary between g.losal and g.hisal

group by g.grade

order by g.grade

;

select e.name 사원명, e.id, c.name 고객명

from s_emp e, s_customer c

where e.id (+) = c.sales_rep_id

order by 2

;

select w.id 사번, w.name 사원명, m.id 부서장사번, m.name 부서장명

from s_emp w, s_emp m

where w.manager_id = m.id

;

--'김정미'와 같은 직책을 가진 사람의 사원이름, 직책, 급여, 부서번호

select b.name, b.title, b.salary, b.dept_id

from s_emp a, s_emp b

where a.title = b.title  

and a.name='김정미'

and b.name <> '김정미'

;

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

;

select name, title, dept_id

from s_emp

where dept_id = (select dept_id from s_emp where name='김정미')

;

--부서별 최소급여인 사람 출력(잘못된 예)

select name, dept_id, salary

from s_emp

where salary in(select min(salary) from s_dept group by dept_id)

;

--부서별 최소급여인 사람 출력(잘된예)

select name, dept_id, salary

from s_emp

where (dept_id, salary) in (select dept_id, min(salary) from s_dept group by dept_id)

;

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

;

select name, title, dept_id

from s_emp

where title='사원'

;

-- 부서별 평균연봉

select dept_id, avg(salary)

from s_emp

group by dept_id

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

--가장 적은 평균급여를 받는 직책에 대해 그 직책과 평균급여를 나타내시오

select title, avg(salary)

from s_emp

group by title

having avg(salary) = (select min(avg(salary)) from s_emp group by title)

;

--DDL

create table emp_13(id, name, mailid, start_date)

as select id, name, mailid, start_date

from s_emp

where dept_id = 113

;

select * from emp_13;

select id, name, mailid, start_date

from s_emp

where dept_id = 113

;

insert into emp_13 (id, name, mailid, start_date)

select id, name, mailid, start_date

from s_emp

where start_date < '16/01/01'

;

update s_emp

set dept_id =(select dept_id

                from s_emp

                where title = '사장')

where name = '안창환'

;

select * 

from emp_13

order by id;