Sql Server #002

Workshop

1. 모든 직원의 급여 총액, 급여 평균, 최고 급여, 최소 급여를 표시 하십시오.

2. 직위가 동일한 직원의 수를 표시 하십시오.

3. 직원들 중에서 관리자의 수를 표시 하십시오.

4. 총 직원 수를 구하고 2000, 2001, 2002, 2003년에 입사한 직원 수를 표시하십시오.

5. 지급된 보너스의 평균값을 구하십시오. (보너스가 없는 경우는 0으로 적용하세요)

6. 최고 급여가 10000원이 넘는 부서번호와 평균급여를 표시 하십시오.

7. 영업부 부서에서 근무하는 모든 직원의 이름, 직위, 부서번호, 부서이름을

   표시 하십시오.

8. 직원의 이름, 사번, 관리자 이름, 사번을 표시 하십시오.

9. 관리자가 지정되지 않은 직원도 포함하여 직원의 이름, 사번, 관리자 이름, 관리자의 사번을 표시 하십시오.(관리자가 지정되지 않은 경우 관리자의 이름과 사번은 ‘없음’으로 표시하세요)

10. 관리자 보다 먼저 입사한 모든 직원의 이름, 입사일, 관리자 이름,

    관리자 입사일을 표시 하십시오.

11. 보너스를 받는 직원의 이름, 부서 번호, 부서 이름을 표시 하십시오.

---

DROP TABLE grade;

DROP TABLE member;

DROP TABLE dept;

CREATE TABLE dept

    ( dept_id    int

    , dept_name  VARCHAR(30)

    , loc_id      int

    ,CONSTRAINT deptid_pk

        PRIMARY KEY (dept_id));

CREATE TABLE member 

    ( member_id    int

    , name      VARCHAR(25) NOT NULL

    , hire      DATE

    , jikwi     VARCHAR(10)

    , sal   int

    , bonus int

    , mgr     int

    , dept_id  int

    ,CONSTRAINT     member_member_id_pk

                     PRIMARY KEY (member_id)

    ,CONSTRAINT     member_deptid_fk

                     FOREIGN KEY (dept_id)

                      REFERENCES dept) ;

INSERT INTO dept VALUES (10,'총무부',100);

INSERT INTO dept VALUES (20,'인사부',200);

INSERT INTO dept VALUES (30,'영업부',300);

INSERT INTO dept VALUES (40,'감사팀',400);

INSERT INTO dept VALUES (50,'전산팀',500);

INSERT INTO member VALUES(100,'배기수', '1987-05-04','사장', 54000, 40, NULL,10);

INSERT INTO member VALUES(101,'홍길동', '1990-06-17','부장', 12000, 30, 100,20);

INSERT INTO member VALUES(102,'차승호', '2001-06-03','부장', 11000, 10, 100,30);

INSERT INTO member VALUES(103,'김주부', '2000-01-01','과장', 14000, NULL, 102,30);

INSERT INTO member VALUES(104,'황기훈', '2001-01-15','과장', 30000, NULL, 101,20);

INSERT INTO member VALUES(105,'정수미', '2003-12-10','대리', 24000, NULL, 104,20);

INSERT INTO member VALUES(106,'권민수', getdate(),'사원', 4000, NULL, NULL, 50);

INSERT INTO member VALUES(107,'김지희', getdate(),'사원', 5000, NULL, 105, NULL);

CREATE TABLE grade

(grade_level VARCHAR(3),

 lowest_sal  int,

 highest_sal int

 );

INSERT INTO grade

VALUES ('A', 1000, 2999);

INSERT INTO grade

VALUES ('B', 3000, 5999);

INSERT INTO grade

VALUES('C', 6000, 9999);

INSERT INTO grade

VALUES('D', 10000, 14999);

INSERT INTO grade

VALUES('E', 15000, 24999);

INSERT INTO grade

VALUES('F', 25000, 40000);

---

--1. 모든 직원의 급여 총액, 급여 평균, 최고 급여, 최소 급여를 표시 하십시오.

select sum(sal) 총액, avg(sal)평균, max(sal) 최고급여, min(sal) 최소급여 from member

--2. 직위가 동일한 직원의 수를 표시 하십시오.

select jikwi, count(*) from member group by jikwi 

--3. 직원들 중에서 관리자의 수를 표시 하십시오.

select count(distinct mgr) from member

--4. 총 직원 수를 구하고 2000, 2001, 2002, 2003년에 입사한 직원 수를 표시하십시오. (x)

--5. 지급된 보너스의 평균값을 구하십시오. (보너스가 없는 경우는 0으로 적용하세요)

select avg(case 

when bonus is null then 0

else bonus

end) as 평균연봉

from member

select avg(isnull(bonus, 0)) from member

--6. 최고 급여가 10000원이 넘는 부서번호와 평균급여를 표시 하십시오.

select dept_id, max(sal) 최고급여, avg(sal)  평균급여 from member  group by dept_id 

HAVING  max(sal)  > 10000;

--7. 영업부 부서에서 근무하는 모든 직원의 이름, 직위, 부서번호, 부서이름을 

--   표시 하십시오.

select name, jikwi, member.dept_id, dept_name from member, dept where member.dept_id = dept.dept_id and dept_name = '영업부'

--8. 직원의 이름, 사번, 관리자 이름, 사번을 표시 하십시오.

select * from member

select a.name, a.member_id, m.name, m.member_id from member a, member m where a.mgr = m.member_id

select name, (case 

when bonus is null then sal*12

else sal*12+bonus

end) as 연봉

from member

--9. 관리자가 지정되지 않은 직원도 포함하여 직원의 이름, 사번, 관리자 이름, 관리자의 사번을 표시 하십시오.(관리자가 지정되지 않은 경우 관리자의 이름과 사번은 ‘없음’으로 표시하세요)

select * from member

select a.name 이름, a.member_id 사번, (case 

when a.mgr is null then '없음'

else b.name

end

) as 관리자이름

, (case 

when a.mgr is null then '없음'

else convert(varchar, b.member_id)

end

) as 관리자사번

 from member a left join member b on a.mgr = b.member_id 

--10. 관리자 보다 먼저 입사한 모든 직원의 이름, 입사일, 관리자 이름, 

--    관리자 입사일을 표시 하십시오.

select * from member

select a.name, a.hire, b.name, b.hire from member a inner join member b on a.mgr = b.member_id

where a.hire < b.hire

--11. 보너스를 받는 직원의 이름, 부서 번호, 부서 이름을 표시 하십시오.

select * from member

select * from dept

select name, m.dept_id, dept_name from member m , dept d where m.dept_id = d.dept_id and m.bonus is not null