[MSSQL]쿼리 실습 예문

-- 사원번호, 사원명, 부서명, 근무지역
--      E                 E             D              D       
-- INNER JOIN : 두 테이블간의 조건에 사용된 컬럼의 값들이
--        일치해야만 선행 테이블의 데이터를 출력합니다.
-- CONDITION : E.DEPTNO = D.DEPTNO
SELECT E.empno AS "사원번호" ,
    E.ename AS "사원명",
    D.dname AS "부서명",
    D.loc AS "근무지"
 FROM EMP E INNER JOIN DEPT D
 ON E.deptno = D.deptno;

-- 사원번호, 사원명, 부서명, 부서장
--    E        E       D       E

-- 자기 자신의 테이블을 조인 걸때 salf join을 함.

SELECT E.empno AS "사원번호",
    E.ename AS "사원명" ,
    D.dname AS "부서명",
    E2.ename AS "부서장"
 FROM EMP E INNER JOIN DEPT D
 ON E.deptno = D.deptno
 INNER JOIN EMP E2
 ON E.mgr = E2.empno;

 

-- VIEW CREATION
  --> CONVINIENCE, SECURITY(접근성 , 보안성)
  --> 뷰 만드는 방법
ALTER VIEW SAMSUNG
AS
SELECT E.empno AS "사원번호",
  E.ename AS "사원명" ,
  D.dname AS "부서명",
  E.mgr AS "부서장사원번호"
 FROM EMP E INNER JOIN DEPT D
 ON E.deptno = D.deptno;

SELECT S.사원번호, S.사원명, S.부서명, E2.ename AS "상관"
 FROM SAMSUNG S INNER JOIN EMP E2
 ON S.부서장사원번호=E2.empno;
 
 --뷰에서 만들어 조인을 걸때 는 나타내고자 하는 필들까지를 출력 시킴.
 
-- 부서명, 부서위치, 부서장, 부서직원수
--      D         D          E          E
-- 집계함수(sum() , ave() , max() , min(), count()) group by절에서 나타내고자 하는 필드명을 모두 적어 둔다.

SELECT D.dname AS "부서명",
    D.loc AS "근무지",
    CONVERT(NVARCHAR, COUNT(*)) + '명'  "부서직원수"
 FROM EMP E INNER JOIN DEPT D
 ON E.deptno = D.deptno
 GROUP BY D.deptno, D.dname, D.loc ;

 

-- 부서장 구하는 공식
SELECT DISTINCT E.deptno, E2.ename
 FROM EMP E INNER JOIN EMP E2
 ON E.mgr = E2.empno
 WHERE E.mgr != 1001;
 
 -- INLINE VIEW
SELECT D.dname AS "부서명",
  D.loc AS "근무지",
  E3.ename as "부서장",
  CONVERT(NVARCHAR, COUNT(*)) + '명'  "부서직원수"
 FROM EMP E INNER JOIN DEPT D
 ON E.deptno = D.deptno
 INNER JOIN (SELECT DISTINCT E.deptno, E2.ename
       FROM EMP E INNER JOIN EMP E2
       ON E.mgr = E2.empno
       WHERE E.mgr != 1001) E3
 ON E.deptno = E3.DEPTNO
 GROUP BY D.deptno, D.dname, D.loc, E3.ename ;
 
SELECT * FROM EMP;

-- MGR 컬럼의 데이터 변경
UPDATE EMP SET  mgr =NULL WHERE empno= 3003;
update EMP set mgr = 1001 where deptno = 10 and jik='사장';  --사장을  1001 이라는 그룹에 포함을 시킴
update EMP SET mgr=1002 where deptno = 10 and mgr != 1001;
update EMP set mgr= 2001 where deptno = 20 and mgr != 1001;
update EMP set mgr= 3001 where deptno = 30 and mgr != 1001;

USE TestDB
GO

/* JOIN 실습
 1. 총무부에 근무하는 사원의 사원명, 직급, 급여, 연봉, 부서장을 출력하시오(단, 사원번호가 1001인 사원은 제외하시오)
 연봉 : (SAL * 12) + COMM
 OUTPUT EXAMPLE :
 -----------------------------------------------------------------------------------------------------------------
      사원명     직급     급여    연봉       부서장
        E          E          E        E
                       
 -----------------------------------------------------------------------------------------------------------------
       한석규    사장     $ 5000   $ 60700             한석규
 */            :                  :                    :                :                        :

select *        
 from EMP E INNER JOIN DEPT D
 ON E.deptno = D.DEPTNO

 
select E.ename AS "사원 명",     
    E.jik AS "직 급",
    E.sal AS "급 여" ,
    (E.sal * 12) + ISNULL(E.comm,0) AS "연 봉",
   E3.ename AS "부서장"
 from EMP E INNER JOIN DEPT D
 ON E.deptno = D.DEPTNO inner join (SELECT DISTINCT E.deptno, E2.ename
           FROM EMP E INNER JOIN EMP E2
           ON E.mgr = E2.empno
           WHERE E.mgr != 1001) E3
          ON D.DEPTNO = E3.deptno
 
   where D.DEPTNO = 10 AND E.empno != 1001;
 

 
 
 
 /*
 2.  부산에 있는 연구부 소속의 사원명, 직급, 급여, 연봉, 부서장, 부서위치를 출력하시오(단, 사원번호가 1001인 사원은 제외하시오)
 */
 select * from DEPT;
 select * from EMP;
 
 select E.ename AS "사 원 명",
     E.jik AS "직   급",
     E.sal AS "급   여",
  (E.sal * 12) + ISNULL(E.comm,0) AS "연 봉",
  E3.ename AS "부서장명",
  D.LOC AS "부서위치"
 from EMP E inner join DEPT D
 on E.deptno = D.DEPTNO inner join (select DISTINCT  E.deptno, E2.ename  
           FROM  EMP E INNER JOIN EMP E2
           ON E.mgr = E2.empno
           WHERE E.mgr != 1001) E3
           on E.DEPTNO = E3.deptno
 where D.DEPTNO = 30;
 
 
 /*
 3. 부서별로 부서명,  사원수, 평균연봉을 출력하시오
     (단, 사원번호가 1001인 사원은 제외, 서울과 부산의 연구부는 하나의 부서로 데이터 통합)
*/    
SELECT * FROM DEPT;
SELECT * FROM EMP;

SELECT  D.DNAME AS "부서명" ,
 CONVERT(NVARCHAR , COUNT(*)) AS "사원수" ,
 AVG((E.sal * 12) + ISNULL(E.comm,0)) AS "평균연봉"
 FROM EMP E INNER JOIN DEPT D
 ON E.deptno = D.DEPTNO
 WHERE E.empno != 1001
 GROUP BY D.dname;
 /*
 4. 지역별로 지역명, 사원수 평균연봉을 출력하시오
     (단, 사원번호가 1001인 사원은 제외)
*/
 SELECT * FROM DEPT;
 SELECT * FROM EMP;
 
 SELECT D.LOC AS "지역명",
  CONVERT(NVARCHAR , COUNT(*)) AS "사원수" ,
  ROUND(AVG((E.sal * 12) + ISNULL(E.comm,0)), 0) AS "평균연봉"
 FROM DEPT D INNER JOIN EMP E
 ON D.DEPTNO = E.deptno
 WHERE E.empno != 1001
 GROUP BY D.LOC;
 /*
 5. 부서별로 부서명, 부서장, 입사일, 연봉, 근무지를 출력하시오 (단, 사원번호가 1001인 사원은 제외하고 연봉이 큰 순으로 출력하시오)
*/

 SELECT * FROM DEPT;
 SELECT * FROM EMP;
 
 SELECT DISTINCT D.DNAME AS "부서명" ,
  E3.ename AS "부서장" ,
  E.hiredate AS "입사일",
  (E.sal * 12) + ISNULL(E.comm,0) AS "연봉",
  D.LOC AS "근무지"
 FROM DEPT D INNER JOIN EMP E
 ON D.DEPTNO = E.deptno INNER JOIN (SELECT DISTINCT E.deptno, E2.ename 
          FROM EMP E INNER JOIN EMP E2
          ON E.mgr = E2.empno
           WHERE E.mgr != 1001) E3
           on E.DEPTNO = E3.deptno
 WHERE E.empno != 1001 AND E.mgr = 1001
 -- 부서명(지역) 3개
 --WHERE E.empno != 1001
 ORDER BY "연봉" DESC;