[OracleDB] SCOTT 계정 실습문제

 

 

 

EMPNO 사번

ENAME 이름

JOB 직업

MGR 상사사번

HIREDATE 입사날짜

SAL 급여

COMM 추가수당

DEPTNO 부서번호

DNAME 부서이름

LOC 부서위치

 

---

 

▶ 부서번호가 20 이고 직업이 SALESMAN인 사람 찾기

SELECT *
FROM EMP
WHERE DEPTNO = 20 OR JOB = 'SALESMAN';

 

▶ A 가 들어간 직업 모두 찾기

SELECT *
FROM EMP
WHERE JOB LIKE '%A%';

 

▶ 직업이 MANAGER, SALESMAN, CLERK가 아닌 사람 찾기

SELECT *
FROM EMP
/* WHERE JOB != 'MANAGER' AND JOB != 'SALESMAN' AND JOB != 'CLERK'; */
WHERE NOT(JOB ='MANAGER' OR JOB = 'SALESMAN' OR JOB = 'CLERK');

 

▶ 추가수당이 없는 사람 찾기

SELECT *
FROM EMP
WHERE COMM IS NOT NULL;

 

SELECT *
FROM EMP
WHERE COMM IS NULL OR COMM = 0;

 

▶ 뉴욕지사의 CLARK 찾기

SELECT *
FROM EMP
JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE LOC = 'NEW YORK' AND ENAME = 'CLARK';

 

SELECT *
FROM EMP, DEPT
WHERE DEPT.LOC = 'NEW YORK' AND EMP.JOB = 'SALESMAN';

 

▶ 연봉이 30,000 이상인 사람 찾기

SELECT SAL*12+ NVL(COMM, 0) AS TOTAL_SAL
FROM EMP;

 

▶ 이름이 5글자 이상인 직원 찾기

SELECT *
FROM EMP
WHERE LENGTH(ENAME) >= 5;

 

▶ 직업을 3글자로 줄여보기

SELECT SUBSTR(JOB,1,3) AS SUB_JOB
FROM EMP;

 

SELECT EMPNO, ENAME, SUBSTR(JOB,1,3)
FROM EMP;

 

▶ A가 들어간 직업 % 안쓰고 찾아보기

SELECT DISTINCT ENAME, JOB, INSTR(JOB, 'A', 1)
FROM EMP
WHERE INSTR(JOB, 'A', 1) != 0;

 

▶ 직원들의 입사 40주년이 언제인지 알아보기

SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 480)
FROM EMP;

 

▶ 입사 42년차 직원들만 찾아보기

SELECT *
FROM EMP
WHERE EXTRACT(YEAR FROM HIREDATE)+ 42 <= EXTRACT(YEAR FROM SYSDATE);

 

SELECT *
FROM EMP
WHERE FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE)) >= 42*12;

 

▶ 추가수당에 '해당사항 없음' 과 '해당사항 있음' 으로 구분하기

SELECT EMPNO, ENAME, JOB, SAL, COMM, NVL2(COMM, 'O', 'X')
FROM EMP;

 

▶ MANAGER 급여의 합계 알아보기

SELECT JOB, SUM(SAL)
FROM EMP
WHERE JOB = 'MANAGER'
GROUP BY JOB;

 

▶ 부서번호 30의 인원수 알아보기

SELECT COUNT(*)
FROM EMP
WHERE DEPTNO = 30;

 

▶ 제일 오래된 직원과 입사 동기들 알아보기

SELECT *
FROM EMP
WHERE EXTRACT(YEAR FROM HIREDATE) = (SELECT MIN(EXTRACT(YEAR FROM HIREDATE)) FROM EMP);

 

▶ 같은 직업에 있는 직원이 3명 이상인 직업 찾기

SELECT JOB, COUNT(*)
FROM EMP
GROUP BY JOB
HAVING COUNT(*) >= 3;

 

▶ 추가수당에 '해당사항 없음' 과 추가수당 없음', '추가수당 : 얼마얼마' 으로 구분하기

SELECT ENAME, COMM,
CASE
WHEN COMM IS NULL THEN '해당X'
WHEN COMM = 0 THEN '추가X'
ELSE CONCAT('추가 : ', TO_CHAR(COMM))
END AS NEW_COMM, SAL
FROM EMP;

SELECT ENAME, COMM,
CASE
WHEN COMM IS NULL THEN '해당X'
WHEN COMM = 0 THEN '추가X'
WHEN COMM > 0 THEN '추가 : ' || COMM
END AS NEW_COMM
FROM EMP;

 

▶ EMP 테이블과 DEPT 테이블에 공통으로 포함된 부서번호 찾기

SELECT DEPTNO
FROM EMP
INTERSECT
SELECT DEPTNO
FROM DEPT;

 

▶ 연도별 부서별 인원수 알아보기

SELECT TO_CHAR(HIREDATE, 'YYYY'), DEPTNO, COUNT(*)
FROM EMP
GROUP BY GROUPING SETS(TO_CHAR(HIREDATE, 'YYYY'), DEPTNO)
ORDER BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO;

 

▶ 본인과 본인의 상급자가 누구인지 찾기

SELECT *
FROM EMP E1, EMP E2
WHERE E1.EMPNO = E2.MGR;

 

▶ 직원의 급여에 맞는 등급 찾기

SELECT *
FROM EMP E, SALGRADE SG
WHERE E.SAL>=SG.LOSAL AND E.SAL<=SG.HISAL;

SELECT *
FROM EMP E, SALGRADE SG
WHERE E.SAL BETWEEN SG.LOSAL AND SG.HISAL;

 

▶ 높은 급여를 받는 사람의 인원수를 등급별로 찾기

SELECT S.GRADE, COUNT(*) AS CNT
FROM EMP E, SALGRADE S
WHERE E.SAL = S.HISAL
GROUP BY S.GRADE;

 

▶ EMP테이블과 DEPT 테이블에 공통된 'DEPT' 값을 가지지 않는 부서이름과 위치 찾기

SELECT D.DEPTNO, D.DNAME, D.LOC
FROM EMP E
RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.DEPTNO IS NULL;

 

▶ EMP와 DEPT 결합해서 직업이 MANAGER인 직원 찾기

SELECT *
FROM EMP 
NATURAL JOIN DEPT
WHERE JOB = 'MANAGER';

SELECT *
FROM EMP 
JOIN DEPT USING(DEPTNO)
WHERE JOB = 'MANAGER';

 

▶ 그룹 별 최고 급여 받는 직원 찾기

SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

SELECT *
FROM EMP
WHERE SAL = ANY(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

 

▶ 30번 부서 직원들의 최대급여보다 적은 급여를 받는 직원들 찾기

SELECT *
FROM EMP
WHERE SAL < (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30); 

 

▶ EMP 테이블에서 가장 높은 급여를 받는 직원의 이름과 급여 출력

SELECT ENAME, SAL
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP);

 

▶ DEPT 테이블에서 각 부서별로 속한 직원의 수와 평균 급여 출력

SELECT DEPTNO, 
(SELECT COUNT(*) FROM EMP WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DEPTNO) AS AVG_CNT, 
(SELECT ROUND(AVG(SAL)) FROM EMP WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DEPTNO) AS AVR_SAL
FROM DEPT ;

 

▶ EMP 테이블에서 각 직원의 이름과 급여 조회, 해당 직원의 급여가 부서 평균 급여보다 높은 경우에만 출력

SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP)
ORDER BY SAL; 

SELECT ENAME, SAL
FROM EMP E1, (SELECT AVG(SAL) AS AVG_SAL, DEPTNO FROM EMP GROUP BY DEPTNO) E2
WHERE E1.SAL > E2.AVG_SAL AND E1.DEPTNO = E2.DEPTNO;

 

▶ EMP 테이블에서 10번 부서에 속한 직원들 중에서 급여가 3000 이상인 직원의 이름과 급여 출력

WITH E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10)
SELECT E10.ENAME, E10.SAL
FROM E10
WHERE E10.SAL > 3000;

SELECT DEPTNO, COUNT(*) AS EMPCNT, MAX(SAL) AS MAXSAL
FROM EMP
GROUP BY DEPTNO
HAVING MAX(DEPTNO) = 10 AND MAX(SAL) > 3000;

SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = 10 AND SAL >= 3000;

SELECT ENAME, SAL
FROM (SELECT * FROM EMP WHERE DEPTNO = 10 AND SAL > 3000);

 

▶ 각 직원의 이름과 부서 이름을 출력 (부서 번호 대신 부서 이름)

SELECT E.ENAME, E.JOB, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

WITH DEPT_DATA AS (SELECT DEPTNO, DNAME FROM DEPT)
SELECT E.ENAME, D.DNAME
FROM EMP E
JOIN DEPT_DATA D ON E.DEPTNO = D.DEPTNO;

 

▶ 각 직원의 이름과 해당 직원의 급여를 조회하되,

▶ 급여가 부서 평균 급여보다 높은 경우에는 "상위"라고 표시하고 그 외에는 "일반"이라고 출력

SELECT E1.ENAME, E1.DEPTNO, E1.SAL,
CASE
WHEN E1.SAL > E2.AVG_SAL THEN '상위'
WHEN E1.SAL <= E2.AVG_SAL THEN '일반'
END AS GRD
FROM EMP E1, (SELECT AVG(SAL) AS AVG_SAL, DEPTNO FROM EMP GROUP BY DEPTNO) E2
WHERE E1.DEPTNO = E2.DEPTNO;

SELECT E.ENAME, E.SAL,
CASE
WHEN E.SAL > E1.AVGSAL THEN '상타'
ELSE '평타'
END AS SAL_GRADE
FROM EMP E, (SELECT AVG(SAL) AS AVGSAL, DEPTNO FROM EMP GROUP BY DEPTNO) E1
WHERE E.DEPTNO = E1.DEPTNO;

 

SELECT E.ENAME, E.SAL,
CASE
WHEN E.SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = EMP.DEPTNO) THEN '상타'
ELSE '평타'
END AS SAL_GRADE
FROM EMP E;

 

▶ 각 부서별로 속한 직원의 수와 평균 급여를 조회하되, 

▶ 급여가 해당 부서의 평균 급여보다 높은 직원의 이름과 급여를 함께 출력 (평균 급여 내림차순으로 정렬)

WITH A AS(SELECT DEPTNO, COUNT(*) AS EMP_COUNT, ROUND(AVG(SAL)) AS AVER_SAL FROM EMP GROUP BY DEPTNO),
     B AS (SELECT E1.ENAME, E1.SAL, E1.DEPTNO
           FROM EMP E1, (SELECT ROUND(AVG(SAL)) AS AVG_SAL, DEPTNO FROM EMP GROUP BY DEPTNO) E2
           WHERE E1.SAL > E2.AVG_SAL AND E1.DEPTNO = E2.DEPTNO)
SELECT A.DEPTNO, A.EMP_COUNT, A.AVER_SAL, B.ENAME, B.SAL
FROM A, B
WHERE A.DEPTNO = B.DEPTNO
ORDER BY A.AVER_SAL DESC;
-- A : 부서별 인원수 평균 급여
-- b : 부서평균급여보다 높은 급여를 받는 직원들



WITH DEPTSUM AS (
    SELECT DEPTNO, COUNT(*) AS EMPCNT, AVG(SAL) AS AVGSAL
    FROM EMP
    GROUP BY DEPTNO
)
SELECT E.ENAME, E.SAL
FROM EMP E
JOIN DEPTSUM DS ON E.DEPTNO = DS.DEPTNO
WHERE E.SAL> DS.AVGSAL
ORDER BY DS.AVGSAL DESC;

 

▶ 각 직원의 이름과 보너스를 조회하되, 보너스가 없는 직원들 중에서 가장 급여가 높은 직원의 이름과 급여를 출력

SELECT ENAME, COMM, SAL
FROM (SELECT ENAME, SAL, COMM FROM EMP WHERE COMM IS NULL)
WHERE SAL IN (SELECT MAX(SAL) FROM EMP);

SELECT ENAME, COMM, SAL
FROM EMP
WHERE COMM IS NULL AND SAL = (SELECT MAX(SAL) FROM EMP WHERE COMM IS NULL);