DML 실습 예제 1 보러가기

-- ★ 연습문제 3 (DML)

--1. 사원테이블에서 부서별 최대 월급을 출력하라.
SELECT (SELECT DNAME FROM DEPT WHERE DEPTNO=E.DEPTNO) AS '부서명',
MAX(SAL) AS '최대월급' FROM EMP E GROUP BY DEPTNO

--2. 사원테이블에서 직위별 최소 월급을 구하되 직위가 CLERK인 것만 출력하라.
SELECT JOB, MIN(SAL) FROM EMP WHERE JOB = 'CLERK' GROUP BY JOB

--3. 커미션이 책정된 사원은 모두 몇 명인가?
SELECT COUNT(*) FROM EMP WHERE COMM IS NOT NULL AND COMM != '0'

--4. 직위가 'SALESMAN'이고 월급이 1000이상인 사원의 이름과 월급을 출력하라.
SELECT ENAME, SAL FROM EMP WHERE JOB = 'SALESMAN' AND SAL >= 1000

--5. 부서별 평균월급을 출력하되, 평균월급이 2000보다
-- 큰 부서의 부서번호와 평균월급을 출력하라.
SELECT DEPTNO, AVG(SAL) AS '평균월급' FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >= 2000

--6. 사원테이블에서 커미션을 가장 많이 받는 사원 2명을
-- 출력하되 랭킹이 중복될 경우 동률처리를 하여 출력하라.

SELECT TOP 2 WITH TIES ENAME, COMM FROM EMP ORDER BY COMM DESC

--7. 직위가 MANAGER인 사원을 뽑는데 월급이 높은 사람
-- 순으로 이름, 직위, 월급을 출력하라.
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB = 'MANAGER' ORDER BY SAL DESC

--8. 각 직위별로 총월급을 출력하되 월급이 낮은 순으로 출력하라.
SELECT JOB, SUM(SAL) AS '총 월급' FROM EMP GROUP BY JOB ORDER BY '총 월급' ASC

--9. 직위별 총월급을 출력하되, 직위가 'MANAGER'인 사원들은 제외하라.
-- 그리고 그 총월급이 5000보다 큰 직위와 총월급만 출력하라.
SELECT JOB, SUM(SAL) AS '총 월급' FROM EMP
GROUP BY JOB HAVING JOB != 'MANAGER' AND SUM(SAL) >= 5000
ORDER BY '총 월급' ASC

--10. 직위별 최대월급을 출력하되, 직위가 'CLERK'인 사원들은 제외하라.
-- 그리고 그 최대월급이 2000 이상인 직위와 최대월급을 최대 월급이
-- 높은 순으로 정렬하여 출력하라.
SELECT JOB, MAX(SAL) AS '최대월급' FROM EMP
GROUP BY JOB HAVING JOB !='CLERK' AND MAX(SAL) >= 2000
ORDER BY '최대월급' DESC

--11. COMPUTE BY 구문을 이용하여 직위별 사원의 이름과
-- 월급, 그리고 직위별 월급의 합을 구하라.
SELECT JOB, ENAME, SAL FROM EMP ORDER BY JOB COMPUTE SUM(SAL) BY JOB

--12. 위 문제를 WITH ROLLUP 구문을 이용하여 집계하여라.
SELECT JOB, (CASE WHEN ENAME IS NULL THEN '[합계]' ELSE ENAME END) AS ENAME,
SUM(SAL) AS SAL FROM EMP GROUP BY JOB, ENAME WITH ROLLUP


-- ★ 연습문제 (JOIN)

-- 1. 사원들의 이름, 부서번호, 부서이름을 출력하라.
SELECT ENAME, EMP.DEPTNO, DNAME
-- FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
ORDER BY DEPT.DNAME ASC

-- 2. DALLAS에서 근무하는 사원의 이름, 직위, 부서번호, 부서이름을 출력하라.
SELECT ENAME, JOB, EMP.DEPTNO, DNAME, LOC FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO WHERE DEPT.LOC = 'DALLAS'

-- 3. 이름에 'A'가 들어가는 사원들의 이름과 부서이름을 출력하라.
SELECT ENAME, DNAME FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO WHERE ENAME LIKE '%A%'

-- 4. 사원이름과 그 사원이 속한 부서의 부서명, 그리고 월급을
--출력하는데 월급이 3000이상인 사원을 출력하라.
SELECT ENAME, DNAME, SAL FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO WHERE SAL >= 3000

-- 5. 직위가 'SALESMAN'인 사원들의 직위와 그 사원이름, 그리고
-- 그 사원이 속한 부서 이름을 출력하라.
SELECT JOB, ENAME, DNAME FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO WHERE JOB = 'SALESMAN'

-- 6. 커미션이 책정된 사원들의 사원번호, 이름, 연봉, 연봉+커미션,
-- 급여등급을 출력하되, 각각의 컬럼명을 '사원번호', '사원이름',
-- '연봉','실급여', '급여등급'으로 하여 출력하라.
SELECT EMPNO AS '사원번호', ENAME AS '이름', (SAL*12) AS '연봉',
((SAL*12)+ISNULL(COMM, 0)) AS '실급여', S.GRADE AS '급여등급'
FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

-- 7. 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름,
-- 월급, 급여등급을 출력하라.
SELECT E.DEPTNO AS '부서번호', D.DNAME AS '부서이름', ENAME AS '사원이름',
SAL AS '월급', S.GRADE AS '급여등급'
FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.DEPTNO = 10

-- 8. 부서번호가 10번, 20번인 사원들의 부서번호, 부서이름, 사원이름,
-- 월급, 급여등급을 출력하라. 그리고 그 출력된 결과물을
-- 부서번호가 낮은 순으로, 월급이 높은 순으로 정렬하라.
SELECT E.DEPTNO AS '부서번호', D.DNAME AS '부서이름',
E.ENAME AS '사원이름', E.SAL AS '월급', S.GRADE AS '급여등급'
FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.DEPTNO IN (10, 20)
ORDER BY E.DEPTNO ASC, E.SAL DESC

-- 9. 사원번호와 사원이름, 그리고 그 사원을 관리하는 관리자의
-- 사원번호와 사원이름을 출력하되 각각의 컬럼명을 '사원번호',
-- '사원이름', '관리자번호', '관리자이름'으로 하여 출력하라.
SELECT E1.EMPNO AS '사원번호', E1.ENAME AS '사원이름',
EM.EMPNO AS '관리자번호', EM.ENAME AS '관리자이름'
FROM EMP E1, EMP EM WHERE E1.MGR = EM.EMPNO

< 위 예제를 실습하려면 아래의 DB를 만들어야 합니다!! >
 

Trackbacks 0 | Comments 3

microdesk's Blog is powered by Daum & tistory