ORACLE SQL 2차 평가 시험 문제

SQL 문제

 

 ‘OE’ 사용자로 로그인하여 요구 조건을 만족하도록 SQL 및 PL/SQL 구문으로 작성하세요.

 

 

1. 단일 주문으로 가장 큰 주문 금액은 얼마인가? (4점)

 

SELECT MAX(ORDER_TOTAL)

FROM OC_ORDERS;

 

결과 :

최고 주문 금액

--------------

        295892

 

2. ‘online’ 주문 중에서 1회 주문 시 중 최고 금액으로 주문한 고객은 누구인가? (4점)

SELECT c.CUST_LAST_NAME || ' ' || c.CUST_FIRST_NAME

FROM CUSTOMERS c

JOIN ORDERS o ON o.CUSTOMER_ID = c.CUSTOMER_ID

WHERE

o.ORDER_TOTAL = (

  SELECT MAX(ORDER_TOTAL) "max_ord"

  FROM ORDERS

  WHERE ORDER_MODE = 'online'

)

 

Roberts Ishwarya

 

3. 직무가 Sales Manager인 사원 ‘Cambrault Gerald’가 관리하는 고객의 수는 몇 명인가? (4점)

SELECT SUM(NUM_CUSTOMERS)

FROM ACCOUNT_MANAGERS am

JOIN EMPLOYEES e ON e.employee_id = am.ACCT_MGR

WHERE e.last_name = 'Cambrault' AND e.first_name = 'Gerald' AND e.job_id = 'SA_MAN'

 

답 : 232

 

4. 'SDRAM - 48 MB' 제품의 총 주문 건수는 몇 건인가? (4점)

SELECT COUNT(*) "건수"

FROM ORDERS o

JOIN ORDER_ITEMS oi ON oi.ORDER_ID = o.ORDER_ID

JOIN PRODUCT_INFORMATION pi ON pi.product_id = oi.product_id

WHERE pi.product_name = 'SDRAM - 48 MB'

 

답 : 2 (건)

5. 'Sydney ' 창고(저장소) 내 'SDRAM - 48 MB' 제품의 재고 수량은 몇 개인가? (4점)

SELECT quantity_on_hand

FROM SYDNEY_INVENTORY

WHERE product_name = 'SDRAM - 48 MB'

 

답 : 65 (개)

 

6. 아래와 같이 고객의 주문 정보를 확인할 수 있는 프로시져 ‘info_order’를 작성하세요. (5점)

 

EXEC info_order(101)

실행 결과

order_id, order_date, order_mode, product_name

 

 

CREATE OR REPLACE PROCEDURE info_order

  (

    p_cust_id orders.customer_id%type

   )

IS

     l_ord orders%ROWTYPE;

     l_pn product_information.product_name%type;

  CURSOR ord_cur IS

   SELECT o.order_id, order_date,  order_mode, product_name

   FROM orders o

   JOIN order_items i ON o.order_id = i.order_id

   JOIN product_information p ON i.product_id = p.product_id

   WHERE customer_id = p_cust_id;

   BEGIN

     OPEN ord_cur;

     LOOP

     FETCH ord_cur INTO l_ord.order_id, l_ord.order_date, l_ord.order_mode, l_pn;

     EXIT WHEN ord_cur%NOTFOUND;

     DBMS_OUTPUT.PUT_LINE(l_ord.order_id||' '||l_ord.order_date||' '||l_ord.order_mode||' '||l_pn);

     END LOOP;

  CLOSE ord_cur;

END;

 

SET SERVEROUTPUT ON;

EXEC info_order(101);

 

결과 : 2447 00/07/27 23:59:10.223344 direct RAM - 16 MB

2430 99/10/02 21:18:36.663332 direct SDRAM - 16 MB

2413 00/03/30 04:34:04.525934 direct MB - S500

2447 00/07/27 23:59:10.223344 direct MB - S600

2413 00/03/30 04:34:04.525934 direct Sound Card STD

2447 00/07/27 23:59:10.223344 direct Video Card /E32

2413 00/03/30 04:34:04.525934 direct KB E/EN

2458 99/08/17 06:34:12.234359 direct Mouse C/E

2413 00/03/30 04:34:04.525934 direct Mouse C/E

2447 00/07/27 23:59:10.223344 direct CDW 20/48/I

2447 00/07/27 23:59:10.223344 direct DVD 12x

2447 00/07/27 23:59:10.223344 direct Battery - NiHM

2447 00/07/27 23:59:10.223344 direct PS 12V /P

2458 99/08/17 06:34:12.234359 direct PS 220V /D

2413 00/03/30 04:34:04.525934 direct Monitor Hinge - HD

2430 99/10/02 21:18:36.663332 direct Plasma Monitor 10/LE/VGA

2447 00/07/27 23:59:10.223344 direct Inkjet B/6

2458 99/08/17 06:34:12.234359 direct LaserPro 600/6/BW

2413 00/03/30 04:34:04.525934 direct LaserPro 600/6/BW

2430 99/10/02 21:18:36.663332 direct HD 10GB /R

2447 00/07/27 23:59:10.223344 direct PS 220V /L

2458 99/08/17 06:34:12.234359 direct Screws <B.28.S>

2458 99/08/17 06:34:12.234359 direct Screws <B.32.S>

2430 99/10/02 21:18:36.663332 direct C for SPNIX4.0 - Sys

2430 99/10/02 21:18:36.663332 direct Web Browser - SB/S 4.0

2458 99/08/17 06:34:12.234359 direct Manual - Vision Net6.3/US

2413 00/03/30 04:34:04.525934 direct Manual - Vision Net6.3/US