[Oracle] 문제 풀이

<문제1>
진행되고 있는 공사의 공사건수와 총공사 비용을 구하시오

SELECT COUNT(*) AS "공사 건수",
       SUM(ST_AMOUNT) AS "총 공사 비용"
FROM SITE
WHERE ST_ENDDATE IS NULL;

---

<문제2>
공사별 자재별 수량을 구하시오.

SELECT S.ST_NAME AS 사업장명, 
       I.ITM_NAME AS 자재명,
       SUM(I.ITM_QTY) AS 수량
FROM ITEM I, SITE S
WHERE S.ST_NO=I.ST_NO
GROUP BY S.ST_NAME, I.ITM_NAME;

---

<문제3>
총 자재 현황을 금액이 큰 것 순서대로 자재번호, 자재명, 수량, 금액을 조회하시오

SELECT ITM_NO AS 자재번호, ITM_NAME AS 자재명, ITM_QTY AS 수량, ITM_PRICE AS 금액
FROM ITEM
ORDER BY 4 DESC;

---

<문제4> 
공사가 끝난 사업장 중 공사금액이 큰 순서로 사업장번호, 사업장명, 공사금액을 구하시오

SELECT ST_NO AS 사업장번호, ST_NAME AS 사업장명, ST_AMOUNT AS 공사금액
FROM SITE
WHERE ST_ENDDATE IS NOT NULL
ORDER BY ST_AMOUNT DESC;

---

<문제5>
2023년도에 공사현장에 파견된 직원의 사번, 이름, 파견일자, 공사명을 구하시오

SELECT E.EMP_NO AS 사원번호,
       E.EMP_NAME AS 사원명,
       W.WRK_INPDATE AS 파견일자,
       S.ST_NAME AS 공사명
FROM WORK W, EMPLOYEE E, SITE S
WHERE E.EMP_NO=W.EMP_NO
AND W.ST_NO=S.ST_NO
AND EXTRACT(YEAR FROM W.WRK_INPDATE)=2023;