교육 8일차 아침에는 SQL 시험을 보았습니다.
지금까지 다루었던 SQL 문법 모든 범위가 시험 범위였습니다.
SW 마에스트로를 준비하면서
프로그래머스 SQL 고득점 Kit를 어느정도 풀어봤기에
어렵지 않게 풀 수 있을 거라 생각했지만, 상당히 난이도가 있는 편이었습니다.
총 20개의 문제가 출제됐고,
사용하는 테이블의 스키마는 다음과 같습니다.
1. EMP 테이블
DESC EMP;이름 널? 유형
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2) EMP 테이블의 튜플들
SELECT * FROM EMP;
2. DEPT 테이블
DESC DEPT;이름 널? 유형
------ -------- ------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13) DEPT 테이블의 튜플들
SELECT * FROM DEPT;
SQL 문제들
- 시험 시간은 120분이었습니다.
- 전반적인 SQL 문제 유형을 모두 접할 수 있습니다.
문제만 궁금하실 분들을 위해
문제 요약본만 적어놓도록 하겠습니다.
1. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하시오.
DEPTNO COUNT(*) SUM(SAL)
------ ---------- ----------
30 6 9400
20 5 10875
2. EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를 출력하시오.
DEPTNO COUNT(*)
------ ---------
30 6
3. EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하시오.
EMPNO
-----
7698
4. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하시오.
CNT10 CNT20
------- -------
3 6
5. EMP 테이블에서
사원번호(EMPNO)가 7521인 사원의 직업(JOB)과 같고
사원번호(EMPNO)가 7934인 사원의 급여(SAL)보다 많은 사원의
사원번호, 이름, 직업, 급여를 출력하시오.
EMPNO ENAME JOB SAL
----- ------- -------- ------
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
6. 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하시오.
조건1 : 직업별로 내림차순 정렬
EMPNO ENAME JOB DNAME
------ ------- ------- -------
7521 WARD SALESMAN SALES
7654 MARTIN SALESMAN SALES
7839 KING PRESIDENT ACCOUNTING
7782 CLARK MANAGER ACCOUNTING
7369 SMITH CLERK RESEARCH
7788 SCOTT ANALYST RESEARCH
7902 FORD ANALYST RESEARCH
7. 각 사원 별 시급을 계산하여 부서번호, 사원이름, 시급을 출력하시오.
조건1. 한달 근무일수는 20일, 하루 근무시간은 8시간이다.
조건2. 시급은 소수 두 번째 자리에서 반올림한다.
조건3. 부서별로 오름차순 정렬
조건4. 시급이 많은 순으로 출력
DEPTNO ENAME 시급
------- ----- ----------
10 KING 31.3
10 CLARK 15.3
10 MILLER 8.1
20 SCOTT 18.8
~~~ 중략 ~~~
30 JAMES 5.9
8. 각 사원 별 커미션(COMM)이 0 또는 NULL이고 부서위치가 ‘GO’로 끝나는 사원의 정보를 사원번호, 사원이름, 커미션, 부서번호, 부서명, 부서위치를 출력하여라.
조건1. 보너스가 NULL이면 0으로 출력
EMPNO ENAME COMM DEPTNO DNAME LOC
------ ------ -------- -------- --------- -------
7499 ALLEN 300 30 SALES CHICAGO
7521 WARD 500 30 SALES CHICAGO
7654 MARTIN 1400 30 SALES CHICAGO
7698 BLAKE 0 30 SALES CHICAGO
7844 TURNER 0 30 SALES CHICAGO
7900 JAMES 0 30 SALES CHICAGO
9. 각 부서 별 평균 급여가 2000 이상이면 초과, 그렇지 않으면 미만을 출력하시오.
DEPTNO 평균급여
------ ----
30 초과
20 미만
10 미만
10. 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명,
부서번호, 입사일을 출력하시오.
EMPNO ENAME DEPTNO HIREDATE
------ ------ -------- ----------
7499 ALLEN 30 1981-02-20
7369 SMITH 20 1980-12-17
7782 CLARK 10 1981-06-09
11. 1980년~1980년 사이에 입사된 각 부서별 사원수를 부서번호, 부서명, 입사1980, 입사1981, 입사1982로 출력하시오.
DEPTNO DNAME 입사1980 입사1981 입사1982
------- ----- -------- -------- --------
10 ACCOUNTING 0 2 1
20 RESEARCH 1 2 0
30 SALES 0 6 0
12. 1981년 5월 31일 이후 입사자 중 커미션(COMM)이 NULL이거나 0인 사원의 커미션은 500으로 그렇지 않으면 기존 COMM을 출력하시오.
ENAME COMM
--------- ----------
MARTIN 1400
CLARK 500
SCOTT 500
KING 500
TURNER 500
ADAMS 500
JAMES 500
FORD 500
MILLER 500
13. 1981년 6월 1일 ~ 1981년 12월 31일 입사자 중 부서명(DNAME)이 SALES인 사원의
부서번호, 사원명, 직업, 입사일을 출력하시오.
조건1. 입사일 오름차순 정렬
DEPTNO DNAME ENAME JOB HIREDATE
------- ----- -------- ------ -------------
30 SALES TURNER SALESMAN 81/09/08
30 SALES MARTIN SALESMAN 81/09/28
30 SALES JAMES CLERK 81/12/03
14. 현재 시간과 현재 시간으로부터 한 시간 후의 시간을 출력하시오.
조건1. 현재시간 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
조건1. 한시간후 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
현재시간 한시간후
--------------------- -------------------
2012-07-31 05:43:45 2012-07-31 06:43:45
15. 각 부서별 사원수를 출력하시오.
조건1. 부서별 사원수가 없더라도 부서번호, 부서명은 출력
조건2. 부서별 사원수가 0인 경우 ‘없음’ 출력
조건3. 부서번호 오름차순 정렬
DEPTNO DNAME 사원수
------------ -------------- ---------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 없음
16.사원 테이블에서 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하시오.
조건1. 각 사원의 급여(SAL)는 매니저 급여보다 많거나 같다.
사원번호 사원명 매니저사원번호 매니저명
---------- ---------- ------------ ---------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
18. 사원명의 첫 글자가 ‘A’이고, 처음과 끝 사이에 ‘LL’이 들어가는 사원의 커미션이 COMM2일때,
모든 사원의 커미션에 COMM2를 더한 결과를 사원명, COMM, COMM2, COMM+COMM2로 출력하시오.
ENAME COMM COMM2 COMM+COMM2
------------ ---------- ---------- ----------
ADAMS 0 300 300
ALLEN 300 300 600
BLAKE 0 300 300
CLARK 0 300 300
FORD 0 300 300
JAMES 0 300 300
JONES 0 300 300
KING 0 300 300
MARTIN 1400 300 1700
MILLER 0 300 300
SCOTT 0 300 300
SMITH 0 300 300
TURNER 0 300 300
WARD 500 300 800
19. 각 부서별로 1981년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력하시오.
조건1. 부서별 사원정보가 없더라도 부서번호, 부서명은 출력
조건2. 부서번호 오름차순 정렬
조건3. 입사일 오름차순 정렬
DEPTNO DNAME EMPNO ENAME HIREDATE
------------ -------------- ---------- ---------- ---------
10 ACCOUNTING 7782 CLARK 09-6-81
10 ACCOUNTING 7839 KING 17-11-81
10 ACCOUNTING 7934 MILLER 23-1-82
20 RESEARCH 7902 FORD 03-12-81
20 RESEARCH 7788 SCOTT 19-4-87
20 RESEARCH 7876 ADAMS 23-5-87
30 SALES 7844 TURNER 08-9-81
30 SALES 7654 MARTIN 28-9-81
30 SALES 7900 JAMES 03-12-81
40 OPERATIONS
20. 입사일로부터 지금까지 근무년수가 40년 미만인 사원의 사원번호, 사원명, 입사일, 근무년수를 출력하시오.
조건1. 근무년수는 월을 기준으로 버림 (예:30.4년 = 30년, 30.7년=30년)
EMPNO ENAME HIREDATE 근무년수
------------ ---------- ---------- ----------
7788 SCOTT 1987-04-19 25
7876 ADAMS 1987-05-23 25
문제 풀이 ( !!스포주의!! )
1. EMP 테이블에서 부서 인원이 4명보다 많은 부서의부서번호, 인원수, 급여의 합을 출력하라.
SELECT
DEPTNO,
COUNT(*),
SUM(SAL)
FROM
EMP
GROUP BY
DEPTNO
HAVING
COUNT(*) > 4;- 그룹함수와 GROUP BY, 그리고 그룹에 대한 조건절인 HAVING을 사용할 줄 아는지 묻는 문제였습니다.
- 부서번호로 GROUP 화 된 집합에 대해,
COUNT(*)로 인원수를 세어주고,SUM(SAL)을 이용해 급여의 합을 계산하여 출력해주었습니다. - 프로그래머스 기준 레벨 1~2 정도의 문제라 느꼈습니다.
2. EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를 출력하라.
SELECT
DEPTNO,
COUNT(*)
FROM
EMP
GROUP BY
DEPTNO
HAVING
COUNT(*) = (SELECT
MAX(COUNT(*))
FROM
EMP
GROUP BY
DEPTNO
);-
부서별 사원 수( EMP 테이블의 PK가 EMPNO이기에, COUNT()로도 사원의 수를 셀 수 있습니다. )를 GROUP BY와 COUNT()로 구해준 이후, 그 그룹 중 가장 많은 인원을 가진다는 조건을 적용하는 쿼리입니다.
-
GROUP 에 대한 조건절인, HAVING 안에 스칼라 서브쿼리를 넣어주어 해결했습니다.
3. EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하라.
SELECT
MGR
FROM
EMP
GROUP BY
MGR
HAVING
COUNT(MGR) = (
SELECT
MAX(COUNT(MGR))
FROM
EMP
GROUP BY
MGR
);- MGR은 특정 사원에 대한 매니저의 사원번호입니다.
- COUNT(MGR)을 통해 매니저가 관리하는 사원 수를 계산할 수 있습니다.
- 매니저가 관리하는 사원 수 중 최대값이 어떤 매니저가 관리하는 사원 수와 같다면,
그 매니저는 가장 많은 사원을 가지는 매니저일 것입니다. - MGR로 GROUP BY 해준 뒤, 해당 그룹에서 COUNT(MGR) 값이 가장 큰 MGR이, 해당 매니저의 사원번호일 것입니다.
- 이를 GROUP BY와 HAVING 조건절에 스칼라 서브쿼리를 넣어주어 해결했습니다.
4. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하라.
SELECT
(SELECT
COUNT(*)
FROM
EMP
WHERE
DEPTNO = 10) as CNT10,
(SELECT
COUNT(*)
FROM
EMP
WHERE
DEPTNO = 30) as CNT30
FROM
DUAL;- 마치 원하는 데이터를 피봇처럼 뽑아내어, 하나의 행에 출력하는 문제입니다.
- 이런 형식으로 뽑아내기 위해서는 SELECT 절 SubQuery를 사용해야 합니다.
- Oracle이 기본적으로 제공하는 더미 테이블인 DUAL을 활용하여, 메인 쿼리의 SELECT 절 안에 여러 SELECT를 통해 원하는 값을 뽑아내는 형식으로 쿼리를 작성하여 해결했습니다.
5. EMP 테이블에서 사원번호(EMPNO)가 7521인 사원의 직업(JOB)과 같고, 사원번호(EMPNO)가 7934인 사원의 급여(SAL)보다 많은 사원의 사원번호, 이름, 직업, 급여를 출력하라.
SELECT
EMPNO,
ENAME,
JOB,
SAL
FROM
EMP
WHERE
JOB = (
SELECT
JOB
FROM
EMP
WHERE
EMPNO = 7521
)
AND
SAL > (
SELECT
SAL
FROM
EMP
WHERE
EMPNO = 7934
);- WHERE절에 스칼라 서브쿼리를 두번 적용해서 해결했습니다.
6. 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하라.
조건1. 직업별로 내림차순 정렬
SELECT
e.EMPNO,
e.ENAME,
e.JOB,
d.DNAME
FROM
EMP e
JOIN
DEPT d
ON
e.deptno = d.deptno
WHERE
(e.JOB, e.SAL) IN (
SELECT
JOB,
MIN(SAL)
FROM
EMP
GROUP BY
JOB
)
ORDER BY
JOB DESC;7. 각 사원 별 시급을 계산하여 부서번호, 사원이름, 시급을 출력하라.
조건 1. 한달 근무일수는 20일, 하루 근무시간은 8시간
조건 2. 시급은 소수 두번째 자리에서 반올림
조건 3. 부서별로 오름차순 정렬
조건 4. 시급이 많은 순으로 출력
SELECT
DEPTNO,
ENAME,
ROUND((SAL / (20 * 8)),1) as 시급
FROM
EMP
ORDER BY
DEPTNO ASC, 시급 DESC;8. 각 사원별 커미션(COMM)이 0 또는 NULL이고, 부서 위치가 'GO'로 끝나는 사원의 정보
조건 1. 보너스가 NULL이면 0으로 출력
SELECT
e.EMPNO,
e.ENAME,
e.JOB,
e.MGR,
e.HIREDATE,
e.SAL,
NVL(e.COMM,0) as COMM,
e.DEPTNO,
d.DNAME,
d.LOC
FROM
EMP e
JOIN
DEPT d
ON
e.deptno = d.deptno
WHERE
d.LOC LIKE '%GO';9. 각 부서 별 평균 급여가 2000이상이면 초과, 그렇지 않으면 미만 출력
SELECT
DEPTNO,
CASE
WHEN
AVG(SAL) >= 2000
THEN
'초과'
ELSE
'미만'
END as 평균급여
FROM
EMP
GROUP BY
DEPTNO;- DEPTNO 를 기준으로 묶어준 후, CASE-WHEN 문을 통해 SELECT 시 조건별로 출력해주어 해결했습니다.
10. 각 부서별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력
SELECT
EMPNO,
ENAME,
DEPTNO,
TO_CHAR(HIREDATE, 'YYYY-MM-DD') as HIREDATE
FROM
EMP
WHERE
(DEPTNO, HIREDATE) IN (
SELECT
DEPTNO,
MIN(HIREDATE)
FROM
EMP
GROUP BY
DEPTNO
);(부서번호,입사일)의 조합이(각 부서 번호, 각 부서별 최소 입사일)에 포함되면 해당하는 튜플이 각 부서별로 가장 오래된 사원일 것입니다.- 이를 활용하여 WHERE절에 조건을 주어 해결했습니다.
11. 1980년 ~ 1982년 사이에 입사된 각 부서별 사원수를 부서번호, 부서명, 입사1980, 입사1981, 입사1982로 출력하라.
SELECT
d.DEPTNO,
d.DNAME,
(
SELECT
COUNT(*)
FROM
EMP temp
WHERE
temp.DEPTNO = d.DEPTNO
AND
TO_CHAR(temp.HIREDATE, 'YYYY') = '1980'
) as "입사1980",
(
SELECT
COUNT(*)
FROM
EMP temp
WHERE
temp.DEPTNO = d.DEPTNO
AND
TO_CHAR(temp.HIREDATE, 'YYYY') = '1981'
) as "입사1981",
(
SELECT
COUNT(*)
FROM
EMP temp
WHERE
temp.DEPTNO = d.DEPTNO
AND
TO_CHAR(temp.HIREDATE, 'YYYY') = '1982'
) as "입사1982"
FROM
(
SELECT
DEPTNO,
TO_CHAR(e.HIREDATE, 'YYYY') as HIREYEAR
FROM
EMP e
) ee
JOIN
DEPT d
ON
ee.deptno = d.deptno
GROUP BY
d.DEPTNO, d.DNAME;-
주어진 20문제 중 체감상 가장 고난이도의 문제였습니다.
-
SELECT 절에서 뽑을 때 (Projection), 다른 컬럼의 값을 가지고 SELECT절 서브쿼리를 해줘서 해결했습니다.
-
TO_CHAR 함수를 활용하여, DATE 타입을 CHAR 타입으로 변환해주었습니다.
-
사실 SELECT 절의 서브쿼리의 WHERE절에서, TO_CHAR로 원본 칼럼을 변형한 것은 좋은 방식이 아닙니다. 왜냐하면 모든 원본 테이블의 컬럼을 변경해주어야 하기에, 인덱스를 타지 못하기 때문입니다.
-
그렇기에 원본 컬럼을 변경하기 보다는, 조건절에 TO_DATE를 사용하여 원본 컬럼을 유지한 상태에서 비교값과 비교하는 것이 더 효율적인 쿼리라 생각합니다.
-
아래와 같이 DECODE를 통해 해결할 수 있습니다. ( 문제 의도는 이것 아니었을까 싶습니다. )
-
DECODE를 활용할 수 있으므로, CASE-WHEN 문도 사용할 수 있지만 이는 생략하겠습니다.
SELECT
d.DEPTNO,
d.DNAME,
SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1980', 1, 0)) as 입사1980,
SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1981', 1, 0)) as 입사1981,
SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1982', 1, 0)) as 입사1982
FROM
EMP e
JOIN
DEPT d
ON
e.deptno = d.deptno
GROUP BY
d.deptno, d.dname;12. 1981년 5월 31일 이후 입사자 중 커미션이 NULL이거나 0인 사원의 커미션은 500으로, 그렇지 않으면 기존 COMM을 출력하라.
SELECT
ENAME,
CASE
WHEN
NVL(COMM, 0) = 0
THEN
500
ELSE
COMM
END as COMM
FROM
EMP;NVL을 사용하는 문제였습니다.
13. 1981년 6월 1일 ~ 1981년 12월 31일 입사자 중 부서명(DNAME)이 SALES인 사원의 부서번호, 사원명, 직업, 입사일 출력
조건1. 입사일로 오름차순 정렬
SELECT
e.DEPTNO,
d.DNAME,
e.ENAME,
e.JOB,
e.HIREDATE
FROM
(SELECT
ee.DEPTNO,
ee.ENAME,
ee.JOB,
ee.HIREDATE
FROM
EMP ee
WHERE
ee.HIREDATE >= TO_DATE('1981/06/01')
AND
ee.HIREDATE <= TO_DATE('1982/01/01')) e
JOIN
DEPT d
ON
e.deptno = d.deptno
WHERE
d.dname = 'SALES'
ORDER BY
HIREDATE ASC;- DATE 자료형 간의 대소비교는 00:00:00
14. 현재 시간과 현재 시간으로부터 한 시간 후의 시간을 출력하라.
조건1. 현재시간 포맷은 '4자리년-2자리일월-2자리일 24시:2자리분:2자리초'로 출력
조건2. 한시간 후 포맷도 동일하다.
SELECT
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as 현재시간,
TO_CHAR(SYSDATE + 1/24, 'YYYY-MM-DD HH24:MI:SS') as 한시간후
FROM
DUAL;- SYSDATE는 현재 시간을 가리키고 있고, DATE 자료형에 1을 더하면 하루를 더하는 것과 같습니다.
- 그렇기에 1/24 를 더하게 되면, 1시간을 더하게 되기에 그렇게 계산해주었습니다.
15. 각 부서별 사원수를 출력하시오.
조건1. 부서별 사원수가 없더라도 부서번호, 부서명은 출력
조건2. 부서별 사원수가 0인 경우 '없음' 출력
조건3. 부서번호로 오름차순 정렬
SELECT
d.deptno,
d.dname,
CASE
WHEN
COUNT(EMPNO) = 0
THEN
'없음'
ELSE
TO_CHAR(COUNT(EMPNO))
END as 사원수
FROM
EMP e
RIGHT OUTER JOIN
DEPT d
ON
e.deptno = d.deptno
GROUP BY
d.deptno, d.dname
ORDER BY
d.deptno asc;OUTER JOIN을 사용하는 문제였습니다.- A테이블에는 있지만, B테이블에 없는 컬럼에 대해 A테이블에 있는 컬럼을 나머지 열을 NULL로 채워서라도 보여주는 것이
OUTER JOIN입니다. DEPTNO = 40인 부서는 존재하지 않습니다. 하지만, 그럼에도 불구하고 출력해줘야하므로
OUTER JOIN을 활용하여 표기해주었습니다.
16. 사원 테이블에서 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하시오.
조건1. 각 사원의 급여(SAL)은 매니저의 급여보다 많거나 같다.
SELECT
e.EMPNO,
e.ENAME,
m.EMPNO,
m.ENAME
FROM
EMP e
JOIN
EMP m
ON
e.mgr = m.empno
WHERE
e.SAL >= m.SAL;- 셀프 조인을 활용하여 문제를 해결했습니다.
17. 사원명의 첫 글자가 'A'이고, 처음과 끝 사이에 'LL'이 들어가는 사원의 커미션이 COMM2일 때, 모든 사원의 커미션에 COMM2에 더한 결과를 사원명, COMM, COMM2, COMM+COMM2로 출력하시오.
SELECT
e.ENAME,
e.COMM,
e.COMM2,
e.COMM + e.COMM2 as "COMM+COMM2"
FROM (
(SELECT
ENAME,
NVL(COMM,0) as COMM,
(SELECT
NVL(COMM,0)
FROM
EMP
WHERE
ENAME LIKE 'A%'
AND
ENAME LIKE '%LL%') as COMM2
FROM
EMP )e
);18. 각 부서별로 1981년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력하시오.
조건1. 부서별 사원정보가 없더라도 부서번호, 부서명은 출력
조건2. 부서번호 오름차순 정렬
조건3. 입사일 오름차순 정렬
SELECT
d.DEPTNO,
d.DNAME,
e.EMPNO,
e.ENAME,
TO_CHAR(e.HIREDATE, 'DD-MM-YY') as HIREDATE
FROM
DEPT d
LEFT OUTER JOIN
(SELECT
DEPTNO,
EMPNO,
ENAME,
HIREDATE
FROM
EMP
WHERE
HIREDATE >= TO_DATE('1981/05/31')) e
ON
d.deptno = e.deptno
ORDER BY
d.deptno asc, e.hiredate asc;19. 입사일로부터 지금까지
--20. 입사일로부터 지금까지 근무년수가 40년 미만인
-- 사원의 사원번호, 사원명, 입사일, 근무년수를 출력하시오.
---조건1. 근무년수는 월을 기준으로 버림 (예:30.4년 = 30년, 30.7년=30년)
SELECT
e.*
FROM (
SELECT
EMPNO,
ENAME,
TO_CHAR(HIREDATE, 'YYYY-MM-DD') as HIREDATE,
FLOOR(((SYSDATE - HIREDATE) / 365)) as 근무년수
FROM
EMP
) e
WHERE
근무년수 < 40;--3.회원관리
--정규직/비정규직 구분하여 출력
--조건1:정규직이면A,비정규직이면B로 출력
--조건2:급여(1일 8시간 한달:20일 기준으로 계산)
--회원번호 회원명 정규/비정규여부 월급여
SELECT
u.USER_SEQ as 회원번호,
u.USER_NAME as 회원명,
CASE
WHEN
u.USER_SEQ IN (
SELECT
USER_SEQ
FROM
PARTTIME
)
THEN
'A'
WHEN
u.USER_SEQ IN (
SELECT
USER_SEQ
FROM
FULLTIME
)
THEN
'B'
END as "정규/비정규 여부",
CASE
WHEN
u.USER_SEQ IN (
SELECT
USER_SEQ
FROM
PARTTIME
)
THEN
p.TSAL * 20 * 8
WHEN
u.USER_SEQ IN (
SELECT
USER_SEQ
FROM
FULLTIME
)
THEN
f.ASAL
END as "월급여"
FROM
USERS u
LEFT OUTER JOIN
PARTTIME p
ON
u.USER_SEQ = p.USER_SEQ
LEFT OUTER JOIN
FULLTIME f
ON
u.USER_SEQ = f.USER_SEQ;