8일차 그동안 공부하였던 SQL 시험을 봤다. 몇몇 문제는 꽤 힘들게 풀었다.
1. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하시오.
SELECT deptno, COUNT(empno), SUM(sal)
FROM emp
GROUP BY deptno
HAVINT COUNT(empno) > 4;
2. EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를 출력하시오.
SELECT deptno, COUNT(empno)
FROM emp
GROUP BY deptno
HAVING COUNT(empno) = (SELECT MAX(COUNT(empno))
FROM emp
GROUP BY deptno);
3. EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하시오.
EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하시오.
SELECT MAX(COUNT(empno))
FROM emp
GROUP BY mgr
SELECT mgr
FROM emp
GROUP
4. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하시오.
4. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하시오.
SELECT COUNT(empno)
FROM emp
WHERE deptno = 10 // 부서번호 10번 사원수
SELECT COUNT(empno)
FROM emp
WHERE deptno = 30 // 부서번호 30번 사원수
SELECT (SELECT COUNT(empno)
FROM emp
WHERE deptno = 10), (SELECT COUNT(empno)
FROM emp
WHERE deptno = 30)
FROM dual;
5. EMP 테이블에서 사원번호(EMPNO)가 7521인 사원의 직업(JOB)과 같고 사원번호(EMPNO)가 7934인 사원의 급여(SAL)보다 많은 사원의 사원번호, 이름, 직업, 급여를 출력하시오.
5. EMP 테이블에서 사원번호(EMPNO)가 7521인 사원의 직업(JOB)과 같고 사원번호(EMPNO)가 7934인 사원의 급여(SAL)보다 많은 사원의 사원번호, 이름, 직업, 급여를 출력하시오.
SELECT job
FROM emp
WHERE empno = 7521; // 사원번호가 7521인 사원의 직업
SELECT sal
FROM emp
WHERE empno = 7934; // 사원번호가 7934인 사원의 급여
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);
6. 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하시오.
6. 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하시오.
SELECT job, MIN(sal)
FROM emp
GROUP BY job;
SELECT empno, ename, job, dname
FROM emp
WHERE (job,sal) IN (SELECT job, MIN(sal)
FROM emp
GROUP BY job) // WHERE절 이용
SELECT empno, ename, job, dname
FROM emp e, dept d, (SELECT job, MIN(sal) as msal
FROM emp
GROUP BY job) sub
WHERE e.job = sub.job
AND e.deptno = d.deptno
AND e.sal = sub.msal // 인라인뷰 이용
7. 각 사원 별 시급을 계산하여 부서번호, 사원이름, 시급을 출력하시오.
조건1. 한달 근무일수는 20일, 하루 근무시간은 8시간이다.
조건2. 시급은 소수 두 번째 자리에서 반올림한다.
조건3. 부서별로 오름차순 정렬
조건4. 시급이 많은 순으로 출력
7. 각 사원 별 시급을 계산하여 부서번호, 사원이름, 시급을 출력하시오.
조건1. 한달 근무일수는 20일, 하루 근무시간은 8시간이다.
조건2. 시급은 소수 두 번째 자리에서 반올림한다.
조건3. 부서별로 오름차순 정렬
조건4. 시급이 많은 순으로 출력
SELECT deptno, ename, ROUND((sal/20*8),1)
FROM emp
ORDER BY deptno ASC, ROUND((sal/20*8),1) DESC;
8. 각 사원 별 커미션(COMM)이 0 또는 NULL이고 부서위치가 ‘GO’로 끝나는 사원의 정보를 사원번호, 사원이름, 커미션, 부서번호, 부서명, 부서위치를 출력하여라.
조건1. 보너스가 NULL이면 0으로 출력
--8. 각 사원 별 커미션(COMM)이 0 또는 NULL이고 부서위치가 ‘GO’로 끝나는 사원의 정보를 사원번호, 사원이름, 커미션, 부서번호, 부서명, 부서위치를 출력하여라.
---조건1. 보너스가 NULL이면 0으로 출력
SELECT e.empno, e.ename, NVL(e.comm,0) as comm, d.deptno, d.danme, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND NVL(comm,0) = 0
AND d.loc like '&go';
9. 각 부서 별 평균 급여가 2000 이상이면 초과, 그렇지 않으면 미만을 출력하시오.
9. 각 부서 별 평균 급여가 2000 이상이면 초과, 그렇지 않으면 미만을 출력하시오.
SELECT deptno, (CASE
WHEN AVG(sal)>=2000 THEN '초과'
ELSE '미만'
END)
FROM emp
GROUP BY deptno;
10. 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하시오.
10. 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하시오.
SELECT deptno, MIN(hiredate)
FROM emp
GROUP BY deptno;
SELECT empno, ename, deptno, hiredate
FROM emp
WHERE (deptno,hiredate) IN (SELECT deptno, MIN(hiredate)
FROM emp
GROUP BY deptno);
SELECT empno, ename, deptno, hiredate
FROM emp e, (SELECT deptno, MIN(hiredate) mdate
FROM emp
GROUP BY deptno) sub
WHERE e.deptno = sub.deptno
AND e.hiredate = sub.mdate
11. 1980년~1980년 사이에 입사된 각 부서별 사원수를 부서번호, 부서명, 입사1980, 입사1981, 입사1982로 출력하시오.
11. 1980년~1980년 사이에 입사된 각 부서별 사원수를 부서번호, 부서명, 입사1980, 입사1981, 입사1982로 출력하시오.
SELECT deptno, (CASE WHEN hiredate BETWEEN '1980/01/01' AND '1980/12/31' THEN 1 END) as 입사1980,
(CASE WHEN hiredate BETWEEN '1981/01/01' AND '1981/12/31' THEN 1 END) as 입사1981,
(CASE WHEN hiredate BETWEEN '1982/01/01' AND '1982/12/31' THEN 1 END) as 입사1982
FROM emp
GROUP BY deptno;
SELECT sub.*, d.dname
FROM (SELECT deptno, (CASE WHEN hiredate BETWEEN '1980/01/01' AND '1980/12/31' THEN 1 END) as 입사1980,
(CASE WHEN hiredate BETWEEN '1981/01/01' AND '1981/12/31' THEN 1 END) as 입사1981,
(CASE WHEN hiredate BETWEEN '1982/01/01' AND '1982/12/31' THEN 1 END) as 입사1982
FROM emp
GROUP BY deptno) sub, dept d
WHERE sub.deptno = d.deptno;
12. 1981년 5월 31일 이후 입사자 중 커미션(COMM)이 NULL이거나 0인 사원의 커미션은 500으로 그렇지 않으면 기존 COMM을 출력하시오.
12. 1981년 5월 31일 이후 입사자 중 커미션(COMM)이 NULL이거나 0인 사원의 커미션은 500으로 그렇지 않으면 기존 COMM을 출력하시오.
SELECT ename, DECODE(NVL(comm,0),0,500,comm)
FROM emp e
WHERE hiredate > '1981/05/31';
13. 1981년 6월 1일 ~ 1981년 12월 31일 입사자 중 부서명(DNAME)이 SALES인 사원의 부서번호, 사원명, 직업, 입사일을 출력하시오.
13. 1981년 6월 1일 ~ 1981년 12월 31일 입사자 중 부서명(DNAME)이 SALES인 사원의 부서번호, 사원명, 직업, 입사일을 출력하시오.
SELECT d.deptno, e.ename, e.job, e.hiredate
FROM emp e, dept d
WHERE e.deptno = d.deptno AND
e.hiredate BETWEEN '1981/06/01' AND '1981/12/31'
AND d.dname = 'SALES';
14. 현재 시간과 현재 시간으로부터 한 시간 후의 시간을 출력하시오.
조건1. 현재시간 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
조건1. 한시간후 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') FROM dual
SELECT TO_CHAR(sysdate+1/24, 'YYYY-MM-DD HH24:MI:SS') FROM dual
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;
15. 각 부서별 사원수를 출력하시오.
조건1. 부서별 사원수가 없더라도 부서번호, 부서명은 출력
조건2. 부서별 사원수가 0인 경우 ‘없음’ 출력
조건3. 부서번호 오름차순 정렬
--15. 각 부서별 사원수를 출력하시오.
---조건1. 부서별 사원수가 없더라도 부서번호, 부서명은 출력
---조건2. 부서별 사원수가 0인 경우 ‘없음’ 출력
---조건3. 부서번호 오름차순 정렬
SELECT d.deptno,d.dname ,DECODE(COUNT(empno),'0','없음',TO_CHAR(COUNT(empno))
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
GROUP BY deptno;
16.사원 테이블에서 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하시오.
조건1. 각 사원의 급여(SAL)는 매니저 급여보다 많거나 같다.
SELECT e.empno, e.ename, m.empno as m_empno, m.ename as m_ename
FROM emp e, emp m
WHERE e.mgr = m.empno
AND e.sal > m.sal;
18. 사원명의 첫 글자가 ‘A’이고, 처음과 끝 사이에 ‘LL’이 들어가는 사원의 커미션이 COMM2일때,
모든 사원의 커미션에 COMM2를 더한 결과를 사원명, COMM, COMM2, COMM+COMM2로 출력하시오.
18. 사원명의 첫 글자가 ‘A’이고, 처음과 끝 사이에 ‘LL’이 들어가는 사원의 커미션이 COMM2일때,
모든 사원의 커미션에 COMM2를 더한 결과를 사원명, COMM, COMM2, COMM+COMM2로 출력하시오.
SELECT comm as comm2
FROM emp
WHERE enmae ='A%LL%';
SELECT e.ename, e.comm, sub.comm2, e.comm + sub.comm2
FROM emp e, (SELECT comm as comm2
FROM emp
WHERE enmae ='A%LL%') sub;
19. 각 부서별로 1981년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력하시오.
조건1. 부서별 사원정보가 없더라도 부서번호, 부서명은 출력
조건2. 부서번호 오름차순 정렬
조건3. 입사일 오름차순 정렬
--19. 각 부서별로 1981년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력하시오.
---조건1. 부서별 사원정보가 없더라도 부서번호, 부서명은 출력
---조건2. 부서번호 오름차순 정렬
---조건3. 입사일 오름차순 정렬
SELECT d.deptno, d.dname, e.empno, e.hiredate
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.hiredate > '1981/05/31'
ORDER BY deptno ASC, hiredate ASC;
20. 입사일로부터 지금까지 근무년수가 40년 미만인 사원의 사원번호, 사원명, 입사일, 근무년수를 출력하시오.
조건1. 근무년수는 월을 기준으로 버림 (예:30.4년 = 30년, 30.7년=30년)
20. 입사일로부터 지금까지 근무년수가 40년 미만인 사원의 사원번호, 사원명, 입사일, 근무년수를 출력하시오.
---조건1. 근무년수는 월을 기준으로 버림 (예:30.4년 = 30년, 30.7년=30년)
SELECT empno, ename, hirdate, TRUNC((sysdate-hiredate)/365) as 근무년수
FROM emp
WHERE TRUNC((sysdate-hiredate)/365) < 40
상품주문문제
1.회원별 주문 상품 통계
회원아이디 상품번호 상품갯수 구매금액
(조건:주문건이 없더라도 회원출력)
SELECT u.user_id, og.good_seq, og.order_amount, og.order_price
FROM users u, orders o, orders_goods og
WHERE u.user_seq = o.user_seq (+)
AND o.order_code = og.order_code (+)
ORDER BY u.user_seq ASC;
2.업체별 공급 상품 리스트
업체번호 업체명 상품번호 상품명
(조건:상품이 없더라도 업체명 출력)
SELECT c.com_seq, c.com_name, g.good_seq, g.good_name
FROM company c, company_goods cg, goods g
WHERE c.com_seq = cg.com_seq(+)
AND cg.good_seq = g.good_seq (+)
ORDER BY c.com_seq;
3.회원관리
정규직/비정규직 구분하여 출력
조건1:정규직이면A,비정규직이면B로 출력
조건2:급여(1일 8시간 한달:20일 기준으로 계산)
회원번호 회원명 정규여부 월급여
SELECT u.user_seq, u.user_name, 'A' as 정규여부, (p.tsal*8*20)
FROM user u, parttime p
WHERE u.user_seq = p.user_seq (+); // 비정규직 회원 정보
SELECT u.user_seq, u.user_name, 'B' as 정규여부, f.asal
FROM user u, fulltime f
WHERE u.user_seq = f.user_seq (+); // 정규직 회원 정보
(SELECT u.user_seq, u.user_name, 'A' as 정규여부, (p.tsal*8*20) FROM user u, parttime p WHERE u.user_seq = p.user_seq (+))
UNION
(SELECT u.user_seq, u.user_name, 'B' as 정규여부, f.asal FROM user u, fulltime f WHERE u.user_seq = f.user_seq (+))'KOSTA : 클라우드 네이티브 애플리케이션 개발 전문가 양성과정' 카테고리의 다른 글
| 04/23 10일차 인덱스,뷰,시퀀스, JAVA 설치, 변수 (0) | 2024.04.23 |
|---|---|
| 04/22 9일차 (0) | 2024.04.22 |
| 04/18 7일차 SQL 문제, ROWNUM (0) | 2024.04.18 |
| 04/17 6일차 DECODE, CASE WHEN THEN, 올림 내림(ROUND, CEIL, FLOOR, TRUNC) (0) | 2024.04.17 |
| 04/16 5일차 서브쿼리 (0) | 2024.04.16 |