ㅤㅤㅤ
기본 셀렉트문 연습문제 난이도 중급! 본문
--32쪽 연습문제--
============================================
1. 부서번호 부서명 직원명 직급을 출력하세요.
============================================
SELECT DEP.DEP_NO,DEP.DEP_NAME,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP, DEPT DEP
WHERE EMP.DEP_NO=DEP.DEP_NO;
--ANSI--
SELECT DEP.DEP_NO,DEP.DEP_NAME,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP INNER JOIN DEPT DEP ON EMP.DEP_NO=DEP.DEP_NO;
======================================================================================================
2. 고객명 고객전화번호 담당직원명 담당직원직급을 출력하세요. <조건> 담당직원이 있는 고객만 출력하세요.
======================================================================================================
SELECT CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP, CUSTOMER CUS
WHERE (EMP.EMP_NO=CUS.EMP_NO) AND CUS.EMP_NO IS NOT NULL;
--ANSI--
SELECT CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP INNER JOIN CUSTOMER CUS ON (EMP.EMP_NO=CUS.EMP_NO) AND CUS.EMP_NO IS NOT NULL;
SELECT CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP INNER JOIN CUSTOMER CUS ON (EMP.EMP_NO=CUS.EMP_NO) WHERE CUS.EMP_NO IS NOT NULL;
=================================================================================================================
3. 고객명 고객전화번호 담당직원명 담당직원직급을 출력하세요. <조건> 10번부서의 담당직원이 있는 고객만 출력하세요.
=================================================================================================================
SELECT CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP, CUSTOMER CUS
WHERE (EMP.EMP_NO=CUS.EMP_NO) AND EMP.DEP_NO=10;
--ANSI--
SELECT CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP INNER JOIN CUSTOMER CUS ON (EMP.EMP_NO=CUS.EMP_NO) AND EMP.DEP_NO=10;
SELECT CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP INNER JOIN CUSTOMER CUS ON (EMP.EMP_NO=CUS.EMP_NO) WHERE EMP.DEP_NO=10;
=============================================================================================================================
4. 고객명 고객전화번호 담당직원명 담당직원직급을 출력하세요. <조건> 연봉이 3000만원 이상인 담당직원이 있는 고객만 포함하세요.
=============================================================================================================================
SELECT CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP, CUSTOMER CUS
WHERE (EMP.EMP_NO=CUS.EMP_NO) AND EMP.SALARY>=3000;
--ANSI--
SELECT CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP INNER JOIN CUSTOMER CUS ON (EMP.EMP_NO=CUS.EMP_NO) AND EMP.SALARY>=3000;
SELECT CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP INNER JOIN CUSTOMER CUS ON (EMP.EMP_NO=CUS.EMP_NO) WHERE EMP.SALARY>=3000;
=====================================================================================================
5. 직원부서명 직원명 직원직급 담당고객명 고객전화를 출력하면? <조건>직원 이름 오름차순 정렬 해주세요.
=====================================================================================================
SELECT DEP.DEP_NAME, EMP.EMP_NAME, EMP.JIKUP,CUS.CUS_NAME,CUS.TEL_NUM
FROM EMPLOYEE EMP, CUSTOMER CUS, DEPT DEP
WHERE (EMP.EMP_NO=CUS.EMP_NO) AND (EMP.DEP_NO=DEP.DEP_NO)
ORDER BY EMP.EMP_NAME ASC ;
--ANSI--
SELECT DEP.DEP_NAME, EMP.EMP_NAME, EMP.JIKUP,CUS.CUS_NAME,CUS.TEL_NUM
FROM (EMPLOYEE EMP INNER JOIN CUSTOMER CUS ON EMP.EMP_NO=CUS.EMP_NO) INNER JOIN DEPT DEP ON EMP.DEP_NO=DEP.DEP_NO ORDER BY EMP.EMP_NAME ASC ;
======================================================
6. 직원부서명 직원명 직원직급 연봉등급을 출력해주세요.
======================================================
SELECT DEP.DEP_NAME, EMP.EMP_NAME, EMP.JIKUP, S.SAL_GRADE_NO
FROM EMPLOYEE EMP, SALARY_GRADE S, DEPT DEP
WHERE (EMP.DEP_NO=DEP.DEP_NO) AND EMP.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL;
SELECT DEP.DEP_NAME, EMP.EMP_NAME, EMP.JIKUP, S.SAL_GRADE_NO
FROM EMPLOYEE EMP, SALARY_GRADE S, DEPT DEP
WHERE (EMP.DEP_NO=DEP.DEP_NO) AND EMP.SALARY>= S.MIN_SAL AND EMP.SALARY<=S.MAX_SAL;
--ANSI--
SELECT
D.DEP_NAME
,E.EMP_NAME
,E.JIKUP
,S.SAL_GRADE_NO
FROM
(DEPT D INNER JOIN EMPLOYEE E ON D.DEP_NO=E.DEP_NO ) INNER JOIN SALARY_GRADE S
ON E.SALARY>=S.MIN_SAL AND E.SALARY<=S.MAX_SAL
=====================================================================================
7. 직원명 직원직급 상관명 직속상관직급을 출력하면? <조건>직속상관이 있는 직원만 포함.
=====================================================================================
SELECT EMP.EMP_NAME,EMP.JIKUP,EMP1.EMP_NAME,EMP1.JIKUP
FROM EMPLOYEE EMP , EMPLOYEE EMP1
WHERE (EMP.MGR_EMP_NO=EMP1.EMP_NO) AND EMP.MGR_EMP_NO IS NOT NULL;
--ANSI--
SELECT EMP.EMP_NAME,EMP.JIKUP,EMP1.EMP_NAME,EMP1.JIKUP
FROM EMPLOYEE EMP INNER JOIN EMPLOYEE EMP1 ON EMP.MGR_EMP_NO=EMP1.EMP_NO AND EMP.MGR_EMP_NO IS NOT NULL;
=====================================================================================
8. 직원명 직원직급 직속부하 직속부하직급 출력하면? <조건>직속부하가 있는 직원만 포함.
=====================================================================================
SELECT EMP1.EMP_NAME,EMP1.JIKUP,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP , EMPLOYEE EMP1
WHERE (EMP.MGR_EMP_NO=EMP1.EMP_NO) AND EMP.MGR_EMP_NO IS NOT NULL;
SELECT EMP1.EMP_NAME,EMP1.JIKUP,EMP.EMP_NAME,EMP.JIKUP
FROM EMPLOYEE EMP INNER JOIN EMPLOYEE EMP1 ON EMP.MGR_EMP_NO=EMP1.EMP_NO AND EMP.MGR_EMP_NO IS NOT NULL;
SELECT EMP.EMP_NAME,EMP.JIKUP,EMP1.EMP_NAME,EMP1.JIKUP
FROM EMPLOYEE EMP , EMPLOYEE EMP1
WHERE (EMP.EMP_NO=EMP1.MGR_EMP_NO) AND EMP.EMP_NO IS NOT NULL;
SELECT EMP.EMP_NAME,EMP.JIKUP,EMP1.EMP_NAME,EMP1.JIKUP
FROM EMPLOYEE EMP INNER JOIN EMPLOYEE EMP1 ON EMP.EMP_NO=EMP1.MGR_EMP_NO
WHERE EMP.EMP_NO IS NOT NULL;
===========================================================================
9. 부서명 직원명 직원직급 연봉등급 직속상관 직속상관직급 고객명 출력하세요.
===========================================================================
SELECT DEP.DEP_NAME,EMP.EMP_NAME,EMP.JIKUP,S.SAL_GRADE_NO,EMP1.EMP_NAME,EMP1.JIKUP,CUS.CUS_NAME
FROM EMPLOYEE EMP , EMPLOYEE EMP1 ,DEPT DEP, SALARY_GRADE S, CUSTOMER CUS
WHERE (EMP.EMP_NO=CUS.EMP_NO) AND (EMP.DEP_NO=DEP.DEP_NO) AND (EMP.MGR_EMP_NO=EMP1.EMP_NO) AND EMP.MGR_EMP_NO IS NOT NULL AND EMP.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL;
SELECT DEP.DEP_NAME,EMP.EMP_NAME,EMP.JIKUP,S.SAL_GRADE_NO,EMP1.EMP_NAME,EMP1.JIKUP,CUS.CUS_NAME
FROM((((EMPLOYEE EMP INNER JOIN EMPLOYEE EMP1 ON EMP.MGR_EMP_NO=EMP1.EMP_NO) INNER JOIN DEPT DEP ON EMP.DEP_NO=DEP.DEP_NO) INNER JOIN CUSTOMER CUS ON EMP.EMP_NO=CUS.EMP_NO) INNER JOIN SALARY_GRADE S ON EMP.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL)
WHERE EMP.MGR_EMP_NO IS NOT NULL ;
=============================================================================================
10. 고객명 고객전화번호 담당직원명 담당직원직급을 출력하면?<조건>담당직원이 없는 고객도 포함.
=============================================================================================
SELECT CUS.CUS_NO,CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP,EMP.DEP_NO
FROM CUSTOMER CUS, EMPLOYEE EMP
WHERE CUS.EMP_NO=EMP.EMP_NO(+);
SELECT CUS.CUS_NO,CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP,EMP.DEP_NO
FROM CUSTOMER CUS LEFT OUTER JOIN EMPLOYEE EMP ON CUS.EMP_NO=EMP.EMP_NO;
SELECT CUS.CUS_NO,CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP,EMP.DEP_NO
FROM EMPLOYEE EMP RIGHT OUTER JOIN CUSTOMER CUS ON CUS.EMP_NO=EMP.EMP_NO;
===============================================================================================================================
11. 고객번호 고객명 고객전화번호 담당직원명 담당직원직급을 출력하면?<조건>고객정보는 모두 보이고 직원정보는 10번 부서만 보일것.
===============================================================================================================================
SELECT CUS.CUS_NO,CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM CUSTOMER CUS, EMPLOYEE EMP
WHERE CUS.EMP_NO=EMP.EMP_NO(+) AND EMP.DEP_NO(+)=10;
SELECT CUS.CUS_NO,CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP
FROM CUSTOMER CUS LEFT OUTER JOIN EMPLOYEE EMP ON CUS.EMP_NO=EMP.EMP_NO AND EMP.DEP_NO=10;
==============================================================================================================================
12. 고객번호 고객명 고객전화번호 담당직원명 담당직원직급 담당직원 연봉등급을 출력하면?<조건>담당직원 없는 고객도 출력해주세요.
==============================================================================================================================
SELECT CUS.CUS_NO,CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP,S.SAL_GRADE_NO
FROM CUSTOMER CUS, EMPLOYEE EMP, SALARY_GRADE S
WHERE CUS.EMP_NO=EMP.EMP_NO(+) AND EMP.SALARY>=S.MIN_SAL(+) AND EMP.SALARY<=S.MAX_SAL(+)
ORDER BY CUS.CUS_NO;
SELECT CUS.CUS_NO,CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP,S.SAL_GRADE_NO
FROM CUSTOMER CUS, EMPLOYEE EMP, SALARY_GRADE S
WHERE CUS.EMP_NO=EMP.EMP_NO(+) AND EMP.SALARY BETWEEN S.MIN_SAL(+) AND S.MAX_SAL(+)
ORDER BY CUS.CUS_NO;
SELECT CUS.CUS_NO,CUS.CUS_NAME,CUS.TEL_NUM,EMP.EMP_NAME,EMP.JIKUP,S.SAL_GRADE_NO
FROM (CUSTOMER CUS LEFT OUTER JOIN EMPLOYEE EMP ON CUS.EMP_NO=EMP.EMP_NO) LEFT OUTER JOIN SALARY_GRADE S ON EMP.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL
ORDER BY CUS.CUS_NO;
--36~41쪽 연습문제--
======================================
1. 최고 연봉을 받는 직원을 검색하세요.
======================================
SELECT *
FROM EMPLOYEE
WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE);
===========================================
2. 평균 연봉 이상을 받는 직원을 검색하세요.
===========================================
SELECT *
FROM EMPLOYEE
WHERE SALARY>=(SELECT AVG(SALARY) FROM EMPLOYEE);
===============================================
3. 20번 부서에서 최고 연봉자 직원을 검색하세요.
===============================================
SELECT *
FROM EMPLOYEE
WHERE DEP_NO=20 AND SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE WHERE DEP_NO=20);
=========================================================================================
4. 직원명 직급 연봉 전체연봉에서 차지하는 비율을 검색하세요 소수점 없는 %로 계산해주세요.
=========================================================================================
SELECT EMP_NAME,JIKUP,SALARY,ROUND((SALARY/(SELECT SUM(SALARY) FROM EMPLOYEE))*100)||'%' "연봉비율"
FROM EMPLOYEE;
=====================================================================
5. 10번 부서 직원들이 관리하는 고객번호 고객명 직원번호를 검색하세요.
=====================================================================
SELECT
CUS_NO
,CUS_NAME
,EMP_NO
FROM
CUSTOMER
WHERE EMP_NO IN (SELECT EMP_NO FROM EMPLOYEE WHERE DEP_NO=10)
SELECT
C.CUS_NO
,C.CUS_NAME
,E.EMP_NO
FROM
CUSTOMER C, EMPLOYEE E
WHERE C.EMP_NO=E.EMP_NO AND E.DEP_NO=10
SELECT
C.CUS_NO
,C.CUS_NAME
,E.EMP_NO
FROM
CUSTOMER C INNER JOIN EMPLOYEE E ON C.EMP_NO=E.EMP_NO ON E.DEP_NO=10
SELECT
C.CUS_NO
,C.CUS_NAME
,E.EMP_NO
FROM
CUSTOMER C INNER JOIN EMPLOYEE E ON C.EMP_NO=E.EMP_NO WHERE E.DEP_NO=10
=============================================================
6. 최고 연봉 직원의 직원번호 직원명 부서명 연봉을 검색하세요.
=============================================================
SELECT EMP.EMP_NO,EMP.EMP_NAME,DEP.DEP_NAME
FROM EMPLOYEE EMP, DEPT DEP
WHERE EMP.DEP_NO=DEP.DEP_NO AND EMP.SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE);
SELECT EMP.EMP_NO,EMP.EMP_NAME,DEP.DEP_NAME
FROM EMPLOYEE EMP INNER JOIN DEPT DEP ON EMP.DEP_NO=DEP.DEP_NO
WHERE EMP.SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE);
==================================================================================================
7. 10번 부서 직원들 보다 연봉을 더 많이 받는 20번 부서의 직원의 직원번호 직원명 연봉을 검색하세요.
==================================================================================================
SELECT EMP_NO,EMP_NAME,SALARY
FROM EMPLOYEE
WHERE DEP_NO=20 AND SALARY>(SELECT MIN(SALARY) FROM EMPLOYEE WHERE DEP_NO=10);
=============================================
8. 부장들 보다 연봉이 많은 과장을 검색하세요.
=============================================
SELECT *
FROM EMPLOYEE
WHERE JIKUP='과장' AND SALARY>(SELECT MIN(SALARY) FROM EMPLOYEE WHERE JIKUP='부장');
============================================
9. 한국남과 직급이 동일한 직원을 검색하세요.
============================================
SELECT *
FROM EMPLOYEE
WHERE JIKUP IN (SELECT JIKUP FROM EMPLOYEE WHERE EMP_NAME='한국남');
SELECT *
FROM EMPLOYEE
WHERE JIKUP = ANY (SELECT JIKUP FROM EMPLOYEE WHERE EMP_NAME='한국남');
=============================================
10. 무궁화와 직급이 동일한 직원을 검색하세요.
=============================================
SELECT *
FROM EMPLOYEE
WHERE JIKUP IN (SELECT JIKUP FROM EMPLOYEE WHERE EMP_NAME='무궁화');
SELECT *
FROM EMPLOYEE
WHERE JIKUP = ANY (SELECT JIKUP FROM EMPLOYEE WHERE EMP_NAME='무궁화');
==========================================
11. 직원번호 직원명 소속부서명을 검색하면?
==========================================
SELECT E.EMP_NO, E.EMP_NAME, D.DEP_NAME
FROM EMPLOYEE E, DEPT D
WHERE E.DEP_NO=D.DEP_NO;
SELECT E.EMP_NO, E.EMP_NAME, D.DEP_NAME
FROM EMPLOYEE E INNER JOIN DEPT D ON E.DEP_NO=D.DEP_NO;
SELECT E.EMP_NO, E.EMP_NAME, (SELECT D.DEP_NAME FROM DEPT D WHERE D.DEP_NO=E.DEP_NO)
FROM EMPLOYEE E
=========================================================================================
12. 평균 연봉 이상이고 최대 연봉 미만의 직원명 연봉 전체평균연봉 전체최대연봉을 출력하면?
=========================================================================================
SELECT
EMP_NAME "직원명"
,SALARY "연봉"
,(SELECT AVG(SALARY) FROM EMPLOYEE)"전체평균연봉"
,(SELECT MAX(SALARY) FROM EMPLOYEE) "전체최대연봉"
FROM
EMPLOYEE
WHERE
SALARY>=(SELECT AVG(SALARY) FROM EMPLOYEE) AND SALARY<(SELECT MAX(SALARY) FROM EMPLOYEE);
SELECT
E1.EMP_NAME
,E1.SALARY
,E2.SAL_AVG
,E2.SAL_MAX
FROM
EMPLOYEE E1, (SELECT AVG(SALARY) "SAL_AVG", MAX(SALARY) "SAL_MAX" FROM EMPLOYEE ) E2
WHERE
E1.SALARY>=E2.SAL_AVG AND E1.SALARY<E2.SAL_MAX;
====================================
13. 연봉 상위 5명의 직원을 검색하면?
====================================
SELECT *
FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC)
WHERE ROWNUM<=5;
SELECT *
FROM (SELECT RANK() OVER(ORDER BY SALARY DESC) AS RANK, EMPLOYEE.* FROM EMPLOYEE)
WHERE RANK<=5;
=============================================================
14. 연봉 상위 3위~5위 직원의 직원번호 직원명 연봉을 검색하면?
=============================================================
SELECT
*
FROM
(SELECT
ROWNUM "RWN"
,E.*
FROM (SELECT * FROM EMPLOYEE ORDER BY CASE JIKUP WHEN '사장' THEN '1' WHEN '부장' THEN '2' WHEN '과장' THEN '3'WHEN '대리' THEN '4' WHEN '사원' THEN '5' ELSE '6' END, JUMIN_NUM ASC ) E
WHERE ROWNUM<=5
)
WHERE
RWN>=3
SELECT
*
FROM
(SELECT
ROWNUM "RWN"
,E.*
FROM (SELECT * FROM EMPLOYEE ORDER BY CASE JIKUP WHEN '사장' THEN '1' WHEN '부장' THEN '2' WHEN '과장' THEN '3'WHEN '대리' THEN '4' WHEN '사원' THEN '5' ELSE '6' END, JUMIN_NUM ASC ) E
)
WHERE
RWN>=3 AND RWN<=5
SELECT
EMP_NO
,EMP_NAME
,SALARY
FROM
(SELECT RANK() OVER (ORDER BY SALARY DESC) AS RANK, EMPLOYEE.*
FROM EMPLOYEE
)
WHERE
RANK<=5 AND RANK>=3;
SELECT
EMP_NO
,EMP_NAME
,SALARY
FROM
( SELECT E.*, ROWNUM RNUM
FROM
(SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) E
WHERE
ROWNUM<=5)
WHERE
RNUM>=3;
SELECT
EMP_NO
,EMP_NAME
,SALARY
FROM
(SELECT E.*, ROWNUM RNUM FROM
(SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) E
)
WHERE RNUM>=3 AND RNUM<=5;
SELECT
*
FROM
(SELECT
ROWNUM "RUN", E.*
FROM
(SELECT * FROM EMPLOYEE ORDER BY SALARY DESC)E
WHERE ROWNUM<=5)E
WHERE RUN>=1
==========================================================================
15. 직원번호 직원명 연봉 연봉 순위를 출력하면? 단 연봉순위를 오름차순 유지
==========================================================================
SELECT
EMP_NO
,EMP_NAME
,SALARY
,RANK() OVER(ORDER BY SALARY DESC) AS "연봉순위"
FROM EMPLOYEE;
SELECT E.EMP_NO, E.EMP_NAME, E.SALARY, E.ROWN
FROM
(SELECT E.*, ROWNUM "ROWN"
FROM
(SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) E )E
====================================================================================================================
16. 직원번호 직원명 주민번호 탄생열순위 출력하면? 단 탄생서열순위를 오름차순 유지 같은날 태어났으면 서열순위는 동일.
====================================================================================================================
SELECT
EMP_NO
,EMP_NAME
,JUMIN_NUM
,RANK() OVER(ORDER BY JUMIN_NUM ASC) AS "탄생서열순위"
FROM EMPLOYEE;
SELECT
E1.EMP_NO
,E1.EMP_NAME
,E1.JUMIN_NUM
,(SELECT COUNT(*)+1
FROM EMPLOYEE E2
WHERE
CASE WHEN SUBSTR(E2.JUMIN_NUM,7,1) IN ('1','2') THEN '19' ELSE '20' END||SUBSTR(E2.JUMIN_NUM,1,6)
<
CASE WHEN SUBSTR(E1.JUMIN_NUM,7,1) IN ('1','2') THEN '19' ELSE '20' END||SUBSTR(E1.JUMIN_NUM,1,6)
) "탄생서열순위"
FROM EMPLOYEE E1
ORDER BY 4
=================================================================================
17. 직원번호 직원명 직급 직급서열순위를 출력하면? 단 직급서열순위를 오름차순 유지
=================================================================================
SELECT
EMP_NO AS "직원번호"
,EMP_NAME AS "직원명"
,JIKUP AS "직급"
,CASE JIKUP
WHEN '사장' THEN 1
WHEN '부장' THEN 2
WHEN '과장' THEN 3
WHEN '대리' THEN 4
WHEN '사원' THEN 5
ELSE 6 END AS "직급서열순위"
FROM EMPLOYEE
ORDER BY "직급서열순위";
SELECT
EMP_NO AS "직원번호"
,EMP_NAME AS "직원명"
,JIKUP AS "직급"
,CASE JIKUP
WHEN '사장' THEN 1
WHEN '부장' THEN 2
WHEN '과장' THEN 3
WHEN '대리' THEN 4
WHEN '사원' THEN 5
ELSE 6 END AS "직급서열순위"
FROM EMPLOYEE
ORDER BY 4;
==========================================
18. 직원번호 직원명 담당고객수를 출력하면?
==========================================
SELECT
E.EMP_NO "직원번호"
,E.EMP_NAME "직원명"
,(SELECT COUNT(EMP_NO) FROM CUSTOMER C WHERE C.EMP_NO=E.EMP_NO) ||'명' "담당고객수"
FROM EMPLOYEE E
================================================
19. 부서명 부서직원수 부서담당고객수를 출력하면?
================================================
SELECT
DEP_NAME "부서명"
,(SELECT COUNT(*) FROM EMPLOYEE E WHERE E.DEP_NO=D.DEP_NO) ||'명' "부서직원수"
,(SELECT COUNT(*) FROM EMPLOYEE E, CUSTOMER C WHERE E.DEP_NO=D.DEP_NO AND E.EMP_NO=C.EMP_NO) ||'명' "담당총고객수"
FROM DEPT D;
=============================================================================================
20. 고객명 고객전화번호 담당직원명 담당직원직급을 출력하면? <조건>담당직원이 없는 고객도 포함
=============================================================================================
SELECT
ROWNUM "고객번호"
,C.CUS_NAME "고객명"
,C.TEL_NUM "고객전화번호"
,E.EMP_NAME "담당직원이름"
,E.JIKUP "담당직원직급"
,E.DEP_NO "부서번호"
,(SELECT D.DEP_NAME FROM DEPT D WHERE D.DEP_NO=E.DEP_NO AND E.EMP_NO=C.EMP_NO) "담당직원부서명"
FROM CUSTOMER C, EMPLOYEE E
WHERE C.EMP_NO=E.EMP_NO(+);
===============================================================================================================================
21. 고객번호 고객명 고객전화번호 담당직원명 담당직원직급을 출력하면?<조건>고객정보는 모두 보이고 직원정보는 10번 부서만 보일 것
===============================================================================================================================
SELECT
ROWNUM "고객번호"
,C.CUS_NAME "고객명"
,C.TEL_NUM "고객전화번호"
,(SELECT E.EMP_NAME FROM EMPLOYEE E WHERE E.EMP_NO=C.EMP_NO AND E.DEP_NO=10) "담당직원이름"
,(SELECT E.JIKUP FROM EMPLOYEE E WHERE E.EMP_NO=C.EMP_NO AND E.DEP_NO=10) "담당직원직급"
,(SELECT E.DEP_NO FROM EMPLOYEE E WHERE E.EMP_NO=C.EMP_NO AND E.DEP_NO=10) "부서번호"
,(SELECT D.DEP_NAME FROM DEPT D, EMPLOYEE E WHERE D.DEP_NO=E.DEP_NO AND E.EMP_NO=C.EMP_NO) "담당직원부서명"
FROM CUSTOMER C;
SELECT
ROWNUM "고객번호"
,C.CUS_NAME "고객명"
,C.TEL_NUM "고객번호"
,(SELECT E.EMP_NO FROM EMPLOYEE E WHERE E.EMP_NO=C.EMP_NO AND E.DEP_NO=10) "담당직원명"
,(SELECT E.JIKUP FROM EMPLOYEE E WHERE E.EMP_NO=C.EMP_NO AND E.DEP_NO=10) "담당직원직급"
,(SELECT E.DEP_NO FROM EMPLOYEE E WHERE E.EMP_NO=C.EMP_NO AND E.DEP_NO=10) "부서번호"
FROM
CUSTOMER C
SELECT
ROWNUM "고객번호"
,C.CUS_NAME "고객명"
,C.TEL_NUM "고객번호"
,E.EMP_NO
,E.JIKUP
,E.DEP_NO
FROM
CUSTOMER C, EMPLOYEE E
WHERE
C.EMP_NO=E.EMP_NO(+) AND E.DEP_NO(+)=10
SELECT
ROWNUM "고객번호"
,C.CUS_NAME "고객명"
,C.TEL_NUM "고객번호"
,E.EMP_NO
,E.JIKUP
,E.DEP_NO
FROM
CUSTOMER C LEFT OUTER JOIN EMPLOYEE E ON C.EMP_NO=E.EMP_NO AND E.DEP_NO=10
--44쪽 연습문제--
==============================================
1. 평균 연봉 이상 연봉을 받는 직원을 출력하면?
==============================================
SELECT * FROM EMPLOYEE WHERE SALARY>=(SELECT AVG(SALARY) FROM EMPLOYEE);
=============================================================
2. 연봉총합 평균연봉 최대연봉 최저연봉 최저입사일을 출력하면?
=============================================================
SELECT SUM(SALARY), AVG(SALARY),MAX(SALARY),MIN(SALARY),MIN(HIRE_DATE) FROM EMPLOYEE;
=======================================================================
3. 고객총수 담당직원 있는 고객총수 고객담당직원수(중복제거)를 출력하면?
=======================================================================
SELECT
COUNT(C.CUS_NAME) "고객총수"
,COUNT((SELECT C.CUS_NAME FROM EMPLOYEE E WHERE E.EMP_NO=C.EMP_NO)) "담당직원있는고객총수"
,COUNT(DISTINCT (SELECT C.EMP_NO FROM EMPLOYEE E WHERE E.EMP_NO=C.EMP_NO)) "고객담당직원수"
FROM CUSTOMER C;
SELECT COUNT(CUS_NAME),COUNT(EMP_NO),COUNT(DISTINCT EMP_NO) FROM CUSTOMER;
=======================================================
4. 부서별로 부서번호 금여합 평균급여 인원수를 출력하면?
=======================================================
SELECT DEP_NO, SUM(SALARY), ROUND(AVG(SALARY),0), COUNT(*) FROM EMPLOYEE GROUP BY DEP_NO ORDER BY 1;
===================================================
5. 직급별로 직급 급여합 평균급여 인원수를 출력하면?
===================================================
SELECT JIKUP "직급", SUM(SALARY) "급여합", ROUND(AVG(SALARY)) "평균급여", COUNT(*) "인원수" FROM EMPLOYEE GROUP BY JIKUP;
=================================================================
6. 부서별 직급별 부서번호 직급 급여합 평균급여 인원수를 출력하면?
=================================================================
SELECT DEP_NO "부서번호", JIKUP "직급", SUM(SALARY) "급여합", ROUND(AVG(SALARY)) "평균급여", COUNT(*) "인원수" FROM EMPLOYEE GROUP BY DEP_NO, JIKUP ORDER BY DEP_NO,JIKUP;
=====================================================================================
7. 부서별 직급별 부서번호 직급 급여합 평균급여 인원수를 출력하되 3명 이상을 출력하면?
=====================================================================================
SELECT DEP_NO "부서번호", JIKUP "직급", SUM(SALARY) "급여합", AVG(SALARY) "평균급여", COUNT(*) "인원수" FROM EMPLOYEE GROUP BY DEP_NO, JIKUP HAVING COUNT(*)>=3 ORDER BY DEP_NO,JIKUP;
SELECT DEP_NO, JIKUP, SUM(SALARY),ROUND(AVG(SALARY),0),COUNT(*) FROM EMPLOYEE GROUP BY DEP_NO,JIKUP HAVING COUNT(*)>=3;
SELECT * FROM (SELECT DEP_NO, JIKUP, SUM(SALARY),ROUND(AVG(SALARY),0),COUNT(*) "EMP" FROM EMPLOYEE GROUP BY DEP_NO,JIKUP) WHERE "EMP">=3;
--FROM 뒤에 인라인 뷰이면서 알리아스가 나중에 컬럼명으로 사용이 가능합니다.--
===============================================================
8. 부서별 성별로 부서번호 성 급여합 평균급여 인원수를 출력하면?
===============================================================
SELECT
DEP_NO "부서번호"
,CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' ELSE '퍽' END "성별"
,SUM(SALARY) "급여합"
,AVG(SALARY) "평균급여"
,COUNT(*) "인원수"
FROM EMPLOYEE
GROUP BY DEP_NO,CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' ELSE '퍽' END
ORDER BY DEP_NO,CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' ELSE '퍽' END;
SELECT
DEP_NO "부서번호"
,CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' ELSE '퍽' END "성별"
,SUM(SALARY) "급여합"
,AVG(SALARY) "평균급여"
,COUNT(*) "인원수"
FROM EMPLOYEE
GROUP BY DEP_NO,CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' ELSE '퍽' END
ORDER BY "부서번호", "성별";
SELECT DEP_NO, CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' END, SUM(SALARY),ROUND(AVG(SALARY),0),COUNT(*) FROM EMPLOYEE
GROUP BY DEP_NO, CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' END ORDER BY DEP_NO;
==================================================================
9. 입사년도별로 입사년도 인원수를 출력하고 년도별로 오름차순 하면?
==================================================================
SELECT
CASE WHEN (TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1980 AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))<=1989) THEN '1980'
WHEN (TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1990 AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))<=1999) THEN '1990' END
"입사년도"
,COUNT(*)
FROM EMPLOYEE
GROUP BY
CASE
WHEN (TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1980 AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))<=1989) THEN '1980'
WHEN (TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1990 AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))<=1999) THEN '1990' END
ORDER BY "입사년도";
SELECT
TO_CHAR(HIRE_DATE,'YYYY')"입사년도"
,COUNT(*)
FROM EMPLOYEE
GROUP BY TO_CHAR(HIRE_DATE,'YYYY')
ORDER BY "입사년도";
=========================================
10. 직원명 생일 년 월 일 나이를 출력하면?
=========================================
SELECT
EMP_NAME "직원명"
,TO_CHAR(TO_DATE('19'||SUBSTR(JUMIN_NUM,1,6),'YYYYMMDD'),'YYYY-MM-DD') "생일"
,(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-TO_NUMBER('19'||SUBSTR(JUMIN_NUM,1,2)))+1 "나이"
FROM EMPLOYEE;
=========================================
11. 부서별로 부서번호 평균근무년수를 출력하면? 근년수는 소수점 1째 자리까지 반올림 할 것.
=========================================
SELECT
DEP_NO "부서번호"
,ROUND( (AVG(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY')))
),1 )"평균근무년수"
FROM EMPLOYEE
GROUP BY DEP_NO;
SELECT
DEP_NO
,ROUND(AVG((SYSDATE-HIRE_DATE)/365),1)
FROM EMPLOYEE
GROUP BY DEP_NO;
=========================================
12. 입사분기별로 입사분기 인원수 출력하면?
=========================================
SELECT
TO_CHAR(HIRE_DATE,'Q')||'분기' "입사분기"
,COUNT(*) "인원수"
FROM EMPLOYEE
GROUP BY TO_CHAR(HIRE_DATE,'Q')||'분기'
ORDER BY "입사분기";
SELECT
TO_CHAR(HIRE_DATE,'Q') || '분기'"입사분기"
,COUNT(*)
FROM EMPLOYEE
GROUP BY TO_CHAR(HIRE_DATE,'Q') || '분기'
ORDER BY "입사분기";
SELECT
TO_CHAR(HIRE_DATE,'Q')|| '분기' "입사분기"
,COUNT(*)
FROM EMPLOYEE
WHERE DEP_NO=10
GROUP BY TO_CHAR(HIRE_DATE,'Q') || '분기'
ORDER BY "입사분기";
=========================================
13. 입사분기별로 입사분기 인원수 출력하면 단 1분기만 보여라.
=========================================
SELECT
TO_CHAR(HIRE_DATE,'Q') "입사분기"
,COUNT(*) "인원수"
FROM EMPLOYEE
GROUP BY TO_CHAR(HIRE_DATE,'Q')
HAVING TO_CHAR(HIRE_DATE,'Q')='1'
ORDER BY "입사분기";
=========================================
14. 입사연대별 성별로 입사연대 성 연대별입사자수 출력하면?
=========================================
SELECT
CASE WHEN (TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1980 AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))<=1989) THEN '1980'
WHEN (TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1990 AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))<=1999) THEN '1990' END || '년'
"입사년도"
,CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' END "성별"
,COUNT(*) "연대별입사자수"
FROM EMPLOYEE
GROUP BY CASE WHEN (TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1980 AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))<=1989) THEN '1980'
WHEN (TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1990 AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))<=1999) THEN '1990' END
,CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' END
ORDER BY "입사년도";
=========================================
15. 직원명 입사일[ 년 월 일 분기 요일 ] 퇴직일 [년 월 일] 출력하면 ? 단 퇴직일은 입사 후 20년 5개월 10일 후
=========================================
SELECT
EMP_NAME "직원명"
,CONCAT(TO_CHAR(HIRE_DATE,'YYYY-MM-DD'),' ')||CONCAT(CONCAT(TO_CHAR(HIRE_DATE,'Q'),'분기'),' ')||TO_CHAR(HIRE_DATE,'DAY','NLS_DATE_LANGUAGE = KOREAN') "입사일"
,TO_CHAR(ADD_MONTHS((HIRE_DATE+((365*20)+10)),5),'YYYY-MM-DD' ) "퇴직일"
FROM EMPLOYEE
=========================================
16. 부서별로 부서번호 부서위치 직원수를 출력하면? 퍽킹
=========================================
SELECT
E.DEP_NO "부서번호"
,D.LOC "부서위치"
,COUNT(E.EMP_NAME) "직원수"
FROM EMPLOYEE E, DEPT D
WHERE E.DEP_NO = D.DEP_NO
GROUP BY E.DEP_NO, D.LOC
SELECT
E.DEP_NO "부서번호"
,D.LOC "부서위치"
,COUNT(E.EMP_NAME) "직원수"
FROM EMPLOYEE E INNER JOIN DEPT D ON E.DEP_NO = D.DEP_NO
GROUP BY E.DEP_NO, D.LOC
ORDER BY E.DEP_NO
=========================================
17. 월별로 입사월 인원수를 검색하면 입사월 오름차순 유지
=========================================
SELECT
SUBSTR(TO_CHAR(HIRE_DATE,'YYYYMM'),5,2) "입사월"
,COUNT(*) "인원수"
FROM EMPLOYEE
GROUP BY SUBSTR(TO_CHAR(HIRE_DATE,'YYYYMM'),5,2)
ORDER BY "입사월";
SELECT
TO_CHAR(HIRE_DATE,'MM') "입사월"
,COUNT(*) "인원수"
FROM EMPLOYEE
GROUP BY TO_CHAR(HIRE_DATE,'MM')
ORDER BY "입사월";
SELECT
SUBSTR(TO_CHAR(HIRE_DATE,'YYYYMM'),5,2) "입사월"
,COUNT(*)
FROM EMPLOYEE
GROUP BY SUBSTR(TO_CHAR(HIRE_DATE,'YYYYMM'),5,2)
ORDER BY 1;
SELECT
SUBSTR(TO_CHAR(HIRE_DATE,'YYYYMM'),5,2) "입사월"
,COUNT(*)
FROM EMPLOYEE
GROUP BY SUBSTR(TO_CHAR(HIRE_DATE,'YYYYMM'),5,2)
ORDER BY SUBSTR(TO_CHAR(HIRE_DATE,'YYYYMM'),5,2);
=========================================
18. 위 결과에서 2월,9월은 없어서 빠진다. 2월,9월 도 포함 시키고 인원수는 0으로 포함하려면?
=========================================
SELECT '02' "입사월", '0' "입사인원수" FROM DUAL
UNION
SELECT '09' "입사월", '0' "입사인원수" FROM DUAL
UNION
SELECT
TO_CHAR(HIRE_DATE,'MM') "입사월"
,TO_CHAR(COUNT(*)) "입사인원수"
FROM EMPLOYEE
GROUP BY TO_CHAR(HIRE_DATE,'MM')
ORDER BY "입사월";
SELECT '02' "입사월", '0' "입사인원수" FROM DUAL
UNION
SELECT '09' "입사월", '0' "입사인원수" FROM DUAL
UNION
SELECT
SUBSTR(TO_CHAR(HIRE_DATE,'YYYYMM'),5,2) "입사월"
,TO_CHAR(COUNT(*))"입사인원수"
FROM EMPLOYEE
GROUP BY SUBSTR(TO_CHAR(HIRE_DATE,'YYYYMM'),5,2)
ORDER BY "입사월";
=========================================
19. EMPLOYEE 테이블에서 직급순서대로 정렬하여 직급별로 직급평균연봉 인원수를 검색하면?
=========================================
SELECT
JIKUP "직급"
,ROUND(AVG(SALARY),1)||'만원' "평균연봉"
,COUNT(*)||'명' "인원수"
FROM EMPLOYEE
GROUP BY JIKUP
ORDER BY CASE JIKUP WHEN '사장' THEN '1'WHEN '부장' THEN '2'WHEN '과장' THEN '3' WHEN '대리' THEN '4'WHEN '사원' THEN '5' END ;
=========================================
20. EMPLOYEE 테이블에서 부서별 부서번호 부서명 직원수 관리고객수를 검색하면? 퍽킹
=========================================
SELECT
E.DEP_NO "부서번호"
,D.DEP_NAME "부서명"
,COUNT(E.EMP_NAME) "직원수"
,COUNT(C.CUS_NAME) "관리고객수"
FROM
EMPLOYEE E, DEPT D, CUSTOMER C
WHERE (E.DEP_NO=D.DEP_NO) AND E.EMP_NO=C.EMP_NO
GROUP BY E.DEP_NO,D.DEP_NAME
SELECT
E.DEP_NO "부서번호"
D.DEP_NAME "부서명"
,Z.QWE
,COUNT(C.CUS_NAME) "관리고객수"
FROM EMPLOYEE E, CUSTOMER C , (
SELECT
E.DEP_NO "ASD",
COUNT(E.EMP_NAME) "QWE"
FROM EMPLOYEE E
GROUP BY E.DEP_NO) Z
WHERE E.EMP_NO=C.EMP_NO AND E.DEP_NO=Z."ASD"
GROUP BY E.DEP_NO,Z.QWE,D.DEP_NAME
ORDER BY 1;
SELECT * FROM EMPLOYEE
SELECT
D.DEP_NO
,COUNT(E.EMP_NAME)
FROM EMPLOYEE E, DEPT D
WHERE E.DEP_NO=E.DEP_NO
GROUP BY D.DEP_NO
SELECT
E.DEP_NO "부서번호"
,D.DEP_NAME "부서명"
,COUNT(E.EMP_NAME) "인원수"
FROM EMPLOYEE E, DEPT D
WHERE E.DEP_NO=D.DEP_NO
GROUP BY E.DEP_NO,D.DEP_NAME
ORDER BY 1;
--50쪽 연습문제--
=========================================
1. EMPLOYEE 테이블에서 <'장보고', 40 '대리', 3500, '2012-05-28', '83311091109310', '01092499215', 3> 데이터를 입력하면?
=========================================
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,DEP_NO,JIKUP,SALARY,HIRE_DATE,JUMIN_NUM,PHONE_NUM,MGR_EMP_NO)
VALUES((SELECT NVL(MAX(EMP_NO)+1,1) FROM EMPLOYEE),'장보고',40,'대리',3500,TO_DATE('2012-05-28','YYYY-MM-DD'),'8311091109310','01092499215',3);
SELECT * FROM EMPLOYEE;
ROLLBACK;
COMMIT;
=========================================
2. EMPLOYEE 테이블에서 주민번호 8203121977315, 이름 강감찬 직원의 직급을 주임으로 수정하려먼?
=========================================
UPDATE EMPLOYEE SET JIKUP='주임' WHERE JUMIN_NUM='8203121977315' AND EMP_NAME='강감찬';
SELECT * FROM EMPLOYEE WHERE EMP_NAME='강감찬';
ROLLBACK;
COMMIT;
=========================================
3. EMPLOYEE 테이블에서 주민번호 8410031281312 이름 공부해 직원의 연봉을 10% 인상하면?
=========================================
UPDATE EMPLOYEE SET SALARY=(SALARY+SALARY*0.1) WHERE EMP_NAME='공부해' AND JUMIN_NUM='8410031281312';
SELECT * FROM EMPLOYEE WHERE EMP_NAME='공부해';
ROLLBACK;
COMMIT;
=========================================
4. EMPLOYEE 테이블에서 연봉 4000만원 이상의 직원 연봉을 2% 삭감하면?
=========================================
SELECT * FROM EMPLOYEE WHERE SALARY>=4000;
UPDATE EMPLOYEE SET SALARY = (SALARY - SALARY*0.02) WHERE SALARY>=4000;
UPDATE EMPLOYEE SET SALARY=SALARY-SALARY*0.02 WHERE SALARY>=4000;
ROLLBACK;
COMMIT;
=========================================
5. EMPLOYEE 테이블에서 평균 연봉 이상의 직원을 2% 삭감하면?
=========================================
SELECT * FROM EMPLOYEE WHERE SALARY>=(SELECT AVG(SALARY) FROM EMPLOYEE);
UPDATE EMPLOYEE SET SALARY = (SALARY - SALARY*0.02) WHERE SALARY>=(SELECT AVG(SALARY) FROM EMPLOYEE);
UPDATE EMPLOYEE SET SALARY=SALARY-SALARY*0.02 WHERE SALARY>=(SELECT AVG(SALARY) FROM EMPLOYEE);
ROLLBACK;
COMMIT;
=========================================
6. 담당 고객이 있는 직원의 급여를 10% 인상하면?
=========================================
UPDATE EMPLOYEE SET SALARY=SALARY*1.1 WHERE EMP_NO=ANY(SELECT EMP_NO FROM CUSTOMER WHERE EMP_NO IS NOT NULL ) ;
UPDATE EMPLOYEE SET SALARY=SALARY*1.1 WHERE EMP_NO=ANY(SELECT DISTINCT(EMP_NO) FROM CUSTOMER WHERE EMP_NO IS NOT NULL ) ;
UPDATE EMPLOYEE SET SALARY=SALARY*1.1 WHERE EMP_NO IN (SELECT DISTINCT(EMP_NO) FROM CUSTOMER WHERE EMP_NO IS NOT NULL ) ;
ROLLBACK;
COMMIT;
=========================================
7. 평균 연봉 보다 적은 연봉자의 연봉을 50만원 인상하면?
=========================================
UPDATE EMPLOYEE SET SALARY = SALARY + 50 WHERE SALARY<(SELECT AVG(SALARY) FROM EMPLOYEE);
UPDATE EMPLOYEE SET SALARY=SALARY+50 WHERE SALARY<(SELECT AVG(SALARY) FROM EMPLOYEE);
ROLLBACK;
COMMIT;
=========================================
8. 연봉 서열 5위까지의 연봉을 10% 인하하면?
=========================================
UPDATE EMPLOYEE SET SALARY = SALARY * 0.9 WHERE EMP_NO IN (SELECT EMP_NO FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM<=5);
UPDATE EMPLOYEE SET SALARY=SALARY*0.90 WHERE EMP_NO = ANY(SELECT EMP_NO FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM<=5);
ROLLBACK;
COMMIT;
=========================================
9. 평균 연봉 보다 적은 연봉자의 연봉을 1% 인상하면?
=========================================
UPDATE EMPLOYEE SET SALARY=SALARY*1.01 WHERE SALARY<(SELECT AVG(SALARY) FROM EMPLOYEE);
ROLLBACK;
COMMIT;
=========================================
10. EMPLOYEE 테이블과 데이터를 그대로 복사하여 EMPLOYEE2 테이블을 만들면 ?
=========================================
CREATE TABLE EMPLOYEE2 AS SELECT * FROM EMPLOYEE;
SELECT * FROM EMPLOYEE2;
ROLLBACK;
COMMIT;
CREATE TABLE EMPLOYEE3 AS SELECT * FROM EMPLOYEE WHERE 1=2; --이거는 테이블 구조만 복사한다.--
SELECT * FROM EMPLOYEE3;
INSERT INTO EMPLOYEE3 SELECT * FROM EMPLOYEE; --이거는 데이터를 주입한다.--
ROLLBACK;
COMMIT;
=========================================
11. EMPLOYEE 테이블에서 평균급여 이상인 직원의 EMP_NAME,JIKUP,SALARY 컬럼만 추출하여 EMPLOYEE4 테이블을 만들면?
=========================================
CREATE TABLE EMPLOYEE4 AS SELECT EMP_NAME,JIKUP,SALARY FROM EMPLOYEE WHERE SALARY>=(SELECT AVG(SALARY) FROM EMPLOYEE);
SELECT * FROM EMPLOYEE4;
ROLLBACK;
COMMIT;
=========================================
12. 직원번호 직원명 부서번호 담당고객번호 담당고객명 이 저장되는 테이블 EMPLOYEE5 테이블을 만들면 데이터 카피는 하지 말것?
=========================================
CREATE TABLE EMPLOYEE5 AS SELECT E.EMP_NO, E.EMP_NAME, E.DEP_NO, C.CUS_NO, C.CUS_NAME
FROM EMPLOYEE E, CUSTOMER C
WHERE 1=2;
--테이블 컬럼만 복사하기 때문에 조인은 필요없음.--
SELECT * FROM EMPLOYEE5;
ROLLBACK;
COMMIT;
=========================================
13. INSERT 구문을 사용하여 위 EMPLOYEE5 테이블에 직원번호 직원명 부서번호 담당고객번호 담당고객명 를 저장하면?
=========================================
INSERT INTO EMPLOYEE5 (EMP_NO,EMP_NAME,DEP_NO,CUS_NO,CUS_NAME)
SELECT
E.EMP_NO
,E.EMP_NAME
,E.DEP_NO
,C.CUS_NO
,C.CUS_NAME
FROM EMPLOYEE E, CUSTOMER C
WHERE E.EMP_NO=C.EMP_NO
SELECT * FROM EMPLOYEE5;
ROLLBACK;
COMMIT;
INSERT INTO EMPLOYEE5(EMP_NO,EMP_NAME,DEP_NO,CUS_NO,CUS_NAME)
SELECT E.EMP_NO, E.EMP_NAME, E.DEP_NO, C.CUS_NO, C.CUS_NAME
FROM EMPLOYEE E, CUSTOMER C
WHERE E.EMP_NO=C.EMP_NO;
SELECT * FROM EMPLOYEE5;
ROLLBACK;
COMMIT;
'プログラミング > Oracle' 카테고리의 다른 글
| 오라클 회원가입 테이블! (0) | 2017.06.22 |
|---|---|
| 기본 셀렉트문 연습문제 잡다한 문제! (0) | 2017.06.22 |
| 기본 셀렉트문 연습문제 난이도 하급! (0) | 2017.06.22 |
| 오라클 연습용 테이블! (0) | 2017.06.22 |
| SQL 연습 - SCOTT 계정 활성화 하는 방법 (1) | 2017.06.19 |