SQLD 연습문제 (Oracle Live SQL)

SQLD 연습하기 위한 기본 문제와 답
 Oracle 환경을 이용하여 자유롭게 SQL쿼리를 이용하여 연습하세요.

 

Oracle Live SQL: https://livesql.oracle.com/apex/f?p=590:1000

다음 자료와 함께 보면 풀기 더 쉽습니다.

https://snowgot.tistory.com/entry/SQLD-%EC%9A%94%EC%95%BD%EC%9E%90%EB%A3%8C

 

1. SELECT /WHERE 문제

문제1) ORDER_TOTAL이 500 이상인 주문의 ORDER_ID와 ORDER_TOTAL 값을 구하세요.

SELECT ORDER_ID, ORDER_TOTAL
FROM OE.orders
WHERE ORDER_TOTAL >= 500;

문제2) ORDER_STATUS가 “Entered” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요. (힌트 Schema를 참조)

SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS = 1;

문제3) ORDER_STATUS가 “Cancled” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요.

SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS in (2,3);

문제4) ORDER_STATUS가 “Shipped” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요.

SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS not  in (0,1,2,3);

문제5)SALES_REP_ID가 153인 모든 주문의 ORDER_ID, ORDER_DATE, 및 ORDER_TOTAL 값을 구하세요.

SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL
FROM oe.orders
WHERE SALES_REP_ID = 5;

문제6) ORDER_MODE가 'online'인 주문 중, ORDER_TOTAL이 2000 이하인 주문의 ORDER_ID와 CUSTOMER_ID 값을 구하세요.

 

SELECT ORDER_ID, CUSTOMER_ID
FROM oe.orders
WHERE ORDER_MODE = 'online' AND ORDER_TOTAL <= 2000;

---

2. GROUP / HAVING 문제

문제1) OE.ORDERS 테이블에서 ORDER_STATUS 별로 주문 건수를 구하세요.

SELECT ORDER_STATUS, COUNT(*) as NUMBER_OF_ORDERS
FROM OE.ORDERS
GROUP BY ORDER_STATUS;

 

문제2) ORDER_TOTAL의 합계가 10,000 이상인 CUSTOMER_ID를 구하세요.

SELECT CUSTOMER_ID, SUM(ORDER_TOTAL) as TOTAL_AMOUNT
FROM OEorders
GROUP BY CUSTOMER_ID
HAVING SUM(ORDER_TOTAL) >= 10000;

 

문제3): 각 SALES_REP_ID별로 처리한 주문의 평균 ORDER_TOTAL 값을 구하세요.

SELECT SALES_REP_ID, AVG(ORDER_TOTAL) as AVERAGE_ORDER_TOTAL
FROM OE.orders
GROUP BY SALES_REP_ID;

 

문제4) 3번의 결과에서 평균 ORDER_TOTAL이 500 이상인 SALES_REP_ID를 구하세요.

SELECT SALES_REP_ID, AVG(ORDER_TOTAL) as AVERAGE_ORDER_TOTAL
FROM OE.orders
GROUP BY SALES_REP_ID
HAVING AVG(ORDER_TOTAL) >= 500;

 

문제5) ORDER_MODE별로 주문된 ORDER_TOTAL의 합계를 구하세요.

SELECT ORDER_MODE, SUM(ORDER_TOTAL) as TOTAL_AMOUNT
FROM OE.orders
GROUP BY ORDER_MODE;

 

문제6) CUSTOMER_ID별로 주문한 횟수가 5회 이상인 고객들의 ID를 구하세요.

SELECT CUSTOMER_ID, COUNT(*)
FROM OE.orders
GROUP BY CUSTOMER_ID
HAVING COUNT(*) >= 5;

 

문제7) 각 SALES_REP_ID별로 처리한 주문 중 ORDER_TOTAL이 100 이상인 주문의 건수를 구하세요.

SELECT SALES_REP_ID, COUNT(*) as NUMBER_OF_ORDERS
FROM OE.orders
WHERE ORDER_TOTAL >= 100
GROUP BY SALES_REP_ID;

 

 

3. built-in 함수 문제 1

문제 1) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)이 50000 이상인 주문들에 대해 주문 모드(ORDER_MODE)를 기반으로 'direct'인 경우 '직접 주문', 그렇지 않은 경우 '일반 주문'으로 출력하세요.

SELECT ORDER_ID, DECODE(ORDER_MODE, 'direct', '직접 주문', '일반 주문') AS ORDER_TYPE
FROM OE.ORDERS
WHERE ORDER_TOTAL >= 50000;

문제 2) OE.ORDERS 테이블에서 주문 상태(ORDER_STATUS)를 기반으로 상태 값이 0인 경우 '진행 중', 1인 경우 '완료', 2,3인 경우 '취소'로 출력하세요. 나머지는 ‘기타’로 출력하세요.

SELECT ORDER_ID, ORDER_STATUS, CASE ORDER_STATUS
                 WHEN 0 THEN '진행 중'
                 WHEN 1 THEN '완료'
                 WHEN 2 THEN '취소'
		         WHEN 3 THEN '취소'
                 ELSE '기타' END  AS STATUS_DESC
FROM OE.ORDERS;

문제 3) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)의 절대값을 출력하세요.

SELECT ORDER_ID, ABS(ORDER_TOTAL) AS ABS_TOTAL
FROM OE.ORDERS;

문제 4) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)이 양수인지 음수인지를 판별하여 결과를 출력하세요.

SELECT ORDER_ID, SIGN(ORDER_TOTAL) AS SIGN_STATUS
FROM OE.ORDERS;

문제 5) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)을 100으로 나눈 나머지를 출력하세요.

SELECT ORDER_ID, ORDER_TOTAL, MOD(ORDER_TOTAL, 100) AS MOD_RESULT
FROM OE.ORDERS;

문제 6) OE.ORDERS 테이블에서 주문 날짜(ORDER_DATE)의 년도만을 출력하세요.

SELECT ORDER_ID, ORDER_DATE, TO_CHAR(ORDER_DATE, 'YYYY') AS ORDER_YEAR
FROM OE.ORDERS;

문제7) OE.ORDERS 테이블에서 ORDERS_STATUS 별로 그룹지은 후 ORDER_TOTAL 의 값을 평균을 내세요. 해당 테이블을 with 절을 이용하여 temp1 에 저장한 후 전체 temp1 테이블을 출력하세요.

WITH TEMP1 AS
(SELECT ORDER_STATUS, ROUND(AVG(ORDER_TOTAL))
    FROM OE.ORDERS GROUP BY ORDER_STATUS)
SELECT * FROM TEMP1

문제8) 당신은 ORDE_STATUS 별로 전체 매출액을 알고 싶다. 다만, 현재 취소건 등 고객들의 클레임이 많은 것을 확인하였다. 얼마나 취소로 인한 손해가 많은지 알고 싶다. 해당 쿼리를 하단에 작성하라.

-- order_status 2,3 취소건 -> 취소건만 order_total
-- order_status 0 ~ 10 까지있는데, 카테고리화 / 퍼센트(분율) 로 보여주면 더 구체적으로 판단할수있다. 

-- 0. 쓸 컬럼을 order_total, order_status
-- 1. case문을 이용해서 order_status 카테고리화 할예정.
-- 2. order_status 별로 그룹을 지어 sum(order_total)

with temp1 as(
select order_total,
    case
    	when order_status = 0 then '주문미완료'
    	when order_status = 1 then '주문완료'
    	when order_status in (2,3) then '주문취소'
    	else '배송중'
    end as os_cat
from oe.orders)
select os_cat, sum(order_total), round(sum(order_total)/3668054.7*100)
from temp1
group by os_cat

 

4. DML/DDL 문제

 

문제 1) 새로운 테이블 NEW_EMPLOYEES를 만들어 주세요. 이 테이블에는 EMP_ID, EMP_NAME, EMP_SALARY 세 개의 컬럼이 있어야 합니다.

• EMP_ID는 숫자 타입이며, 기본 키로 설정해주세요.
• EMP_NAME은 최대 50자의 문자열 타입으로 설정해주세요.
• EMP_SALARY는 숫자 타입으로 설정해주세요.

CREATE TABLE NEW_EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(50),
    EMP_SALARY NUMBER
);

 

문제 2) NEW_EMPLOYEES 테이블에 다음 정보를 입력해주세요.

• EMP_ID: 101, EMP_NAME: 'John Doe', EMP_SALARY: 5000

INSERT INTO NEW_EMPLOYEES (EMP_ID, EMP_NAME, EMP_SALARY) VALUES (101, 'John Doe', 5000);

---

문제 3) NEW_EMPLOYEES 테이블에서 EMP_ID가 101인 직원의 EMP_SALARY를 5500으로 수정해주세요.

UPDATE NEW_EMPLOYEES SET EMP_SALARY = 5500 WHERE EMP_ID = 101;

---

문제 4) NEW_EMPLOYEES 테이블에서 EMP_ID가 101인 직원의 정보를 삭제해주세요.

DELETE FROM NEW_EMPLOYEES WHERE EMP_ID = 101

문제 5) NEW_EMPLOYEES 테이블에 EMP_EMAIL 컬럼을 추가해주세요. 이 컬럼은 최대 100자의 문자열 타입입니다.

ALTER TABLE NEW_EMPLOYEES ADD (EMP_EMAIL VARCHAR2(100));

---

문제6) NEW_EMPLOYEES 테이블의 모든 데이터를 삭제해주세요.

TRUNCATE TABLE NEW_EMPLOYEES;

---

문제 7) NEW_EMPLOYEES 테이블을 삭제해주세요.

DROP TABLE NEW_EMPLOYEES;

 

문제8) 최종 문제

주문 테이블을 생성하고, 데이터를 삽입한 뒤 데이터를 생성하고 주문별로 개수를 확인하려한다. 다음 1번~4번 절차대로 쿼리를 수행하여 결과를 다음과 같은 예시처럼 나타내어라. 내부 값은 다를 수 있음

문제8-1) 아래 제공된 테이블의 컬럼 이름과 제약 조건을 기반으로 주문 정보를 저장할 orders 테이블을 생성하세요.

1. order_id: 주문 번호, 숫자, 기본 키
2. customer_name: 고객 이름, 문자열(50자 제한)
3. product_name: 제품 이름, 문자열(50자 제한)
4. quantity: 주문 수량, 숫자
5. order_date: 주문 날짜, 날짜형

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(50),
    product_name VARCHAR2(50),
    quantity NUMBER,
    order_date DATE
);

문제8-2) 주어진 데이터를 orders 테이블에 삽입하세요.

1, '김영희', '노트북', 1, '2023-10-01'
2, '박철수', '키보드', 2, '2023-10-02'
3, '이민수', '마우스', 1, '2023-10-03'
4, '최수진', '모니터', 2, '2023-10-05'
5, '박지훈', '노트북', 1, '2023-10-06'
6, '김소연', '키보드', 3, '2023-10-07'
7, '이태희', '마우스', 5, '2023-10-08'
8, '박민아', '노트북', 2, '2023-10-09'
9, '최영준', '모니터', 1, '2023-10-10'
10, '정희선', '노트북', 1, '2023-10-11'

 

INSERT ALL
  INTO orders VALUES (1, '김영희', '노트북', 1, TO_DATE('2023-10-01', 'YYYY-MM-DD'))
  INTO orders VALUES (2, '박철수', '키보드', 2, TO_DATE('2023-10-02', 'YYYY-MM-DD'))
  INTO orders VALUES (3, '이영수', '마우스', 1, TO_DATE('2023-10-03', 'YYYY-MM-DD'))
  INTO orders VALUES (4, '최민영', '모니터', 2, TO_DATE('2023-10-04', 'YYYY-MM-DD'))
  INTO orders VALUES (5, '황영준', '노트북', 1, TO_DATE('2023-10-05', 'YYYY-MM-DD'))
  INTO orders VALUES (6, '김영미', '키보드', 1, TO_DATE('2023-10-06', 'YYYY-MM-DD'))
  INTO orders VALUES (7, '박진수', '마우스', 3, TO_DATE('2023-10-07', 'YYYY-MM-DD'))ㄷㄷ
  INTO orders VALUES (8, '이민철', '모니터', 1, TO_DATE('2023-10-08', 'YYYY-MM-DD'))
  INTO orders VALUES (9, '최영주', '노트북', 1, TO_DATE('2023-10-09', 'YYYY-MM-DD'))
  INTO orders VALUES (10, '황지수', '키보드', 2, TO_DATE('2023-10-10', 'YYYY-MM-DD'))
SELECT * FROM DUAL;

문제8-3) 모든 주문 정보를 조회하되, 수량이 3 이상인 경우 '대량 주문'이라는 문자열을, 그렇지 않은 경우 '일반 주문'이라는 문자열을 새로운 컬럼 order_type에 표시하여 출력하세요.

SELECT order_id, customer_name, product_name, quantity, order_date,
       CASE WHEN quantity >= 3 THEN '대량 주문'
            ELSE '일반 주문'
       END AS order_type
FROM orders;

문제8-4): 제품별로 총 주문 수량을 계산하고, 주문 수량이 많은 순으로 정렬하여 출력하세요.

with temp1 as (SELECT order_id, customer_name, product_name, quantity, order_date,
       CASE WHEN quantity >= 3 THEN '대량 주문'
            ELSE '일반 주문'
       END AS order_type
FROM orders)
SELECT order_type, SUM(quantity) as total_quantity
FROM temp1
GROUP BY order_type
ORDER BY total_quantity DESC;;

 

5. 조인문제 1차

문제 1) OE.ORDERS 테이블에서 주문 상태(ORDER_STATUS)가 '1'인 모든 주문을 조회하십시오.

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1;

 

문제 2) OE.ORDERS와 OE.CUSTOMERS 테이블을 INNER JOIN을 사용하여 결합하고, 이를 기반으로 주문 상태(ORDER_STATUS)가 '0'인 고객의 이름 (CUST_FIRST_NAME, CUST_LAST_NAME)과 주문 총액(ORDER_TOTAL)을 조회하십시오.

SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.ORDER_STATUS = 0;

 

문제 3) OE.CUSTOMERS 테이블을 기준으로 OE.ORDERS 테이블과 LEFT JOIN을 하여, 주문 정보가 없는 고객의 이름 (CUST_FIRST_NAME, CUST_LAST_NAME)을 조회하십시오.

SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM OE.CUSTOMERS C
LEFT JOIN OE.ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE O.CUSTOMER_ID IS NULL;

 

문제 4) OE.ORDERS와 OE.CUSTOMERS 테이블을 INNER JOIN을 사용하여 결합하고, 주문 총액(ORDER_TOTAL)이 10,000 이상이며, 고객의 성별(GENDER)이 'M'인 모든 주문을 조회하십시오.

SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.ORDER_TOTAL >= 10000 AND C.GENDER = 'M';

 

문제 5) FULL OUTER JOIN 이용한 조회 OE.ORDERS와 OE.CUSTOMERS 테이블을 FULL OUTER JOIN을 사용하여 결합하고, 주문 정보나 고객 정보 중 하나라도 없는 모든 레코드를 조회하십시오.

SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
FULL OUTER JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.CUSTOMER_ID IS NULL OR C.CUSTOMER_ID IS NULL;

 

6. 조인문제 2차

문제 1) ORDER_DATE와 관련된 JOIN OE.ORDERS 테이블과 OE.ORDER_ITEMS 테이블을 JOIN하여 ORDER_DATE, PRODUCT_ID 및 QUANTITY 정보를 가져와보세요.

SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID;

SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
natural JOIN OE.ORDER_ITEMS I ;

SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I using(order_id)

문제 2) 특정 조건에 따른 집계 ORDER_STATUS가 1인 주문에 대하여 총 주문 금액(UNIT_PRICE * QUANTITY)을 계산하세요.

SELECT O.ORDER_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_ORDER_AMOUNT
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE O.ORDER_STATUS = 1

문제 3) ORDER_MODE가 'direct'이며 ORDER_STATUS가 0인 주문에 대해 총 주문된 제품 수(QUANTITY)를 계산하세요.

SELECT O.ORDER_ID, SUM(I.QUANTITY) AS TOTAL_QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE O.ORDER_MODE = 'direct' AND O.ORDER_STATUS = 0

문제 4)  각 SALES_REP_ID별로 주문된 제품의 총 가격(UNIT_PRICE * QUANTITY의 합)을 조회하고, 주문 총 가격이 높은 순으로 정렬하세요.

SELECT O.SALES_REP_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_SALES
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
GROUP BY O.SALES_REP_ID
ORDER BY TOTAL_SALES DESC;

문제 5) 2008년에 이루어진 주문과 관련된 모든 PRODUCT_ID와 해당 PRODUCT_ID의 총 판매 금액을 조회하세요.

SELECT O.ORDER_DATE, I.PRODUCT_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_AMOUNT
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE TO_CHAR(O.ORDER_DATE,'YYYY-MM-DD') BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY O.ORDER_DATE,I.PRODUCT_ID;

 

7. 집합 문제

문제1) 주문 상태(ORDER_STATUS)가 1과 2인 주문을 합쳐서 출력해보세요. (UNION을 이용)

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1 
UNION 
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 2;

 

문제2) 주문 상태(ORDER_STATUS)가 1인 주문과 ORDER_TOTAL이 50000을 초과하는 주문을 모두 출력하세요. 단, 중복된 주문이 있을 수 있습니다. (UNION ALL을 이용)

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
UNION ALL 
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;

문제3)  주문 상태(ORDER_STATUS)가 1인 주문과 ORDER_TOTAL이 50000을 초과하는 공통된 주문을 찾아 출력하세요. (INTERSECT를 이용)

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1 
INTERSECT 
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;

문제4) 주문 상태(ORDER_STATUS)가 1인 주문 중에서 ORDER_TOTAL이 50000 초과하는 주문을 제외하고 출력하세요. (MINUS를 이용

SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1 
MINUS 
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;

 

8. SubQuery 문제

문제1) 총 주문 금액이 가장 높은 주문의 금액을 조회하시오.

SELECT * 
FROM OE.ORDERS WHERE ORDER_TOTAL = (SELECT MAX(ORDER_TOTAL) FROM OE.ORDERS);

문제2) 2007년에 주문한 모든 고객의 평균 주문 금액을 조회하시오.

SELECT CUSTOMER_ID, AVG(ORDER_TOTAL) AS AVG_TOTAL 
FROM (SELECT CUSTOMER_ID, ORDER_TOTAL FROM OE.ORDERS WHERE TO_CHAR(ORDER_DATE, 'YYYY') = '2007') GROUP BY CUSTOMER_ID;

문제3) SALES_REP_ID가 155인 판매 대표의 평균 주문 금액보다 높은 주문들만 조회하시오.

SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS 
WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM OE.ORDERS WHERE SALES_REP_ID = 155);

문제4) IN 사용: 주문 금액이 10,000 이상 50,000 이하인 CUSTOMER_ID의 모든 주문을 조회하시오.

SELECT ORDER_ID, CUSTOMER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM OE.ORDERS WHERE ORDER_TOTAL BETWEEN 10000 AND 50000);

문제5) ALL 사용: 모든 판매 대표의 평균 주문 금액보다 높은 주문들만 조회하시오.

SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS 
WHERE ORDER_TOTAL > ALL (SELECT AVG(ORDER_TOTAL) FROM OE.ORDERS GROUP BY SALES_REP_ID);

문제6) ANY 사용:주문 상태가 1 또는 3인 주문보다 주문 금액이 높은 주문들을 조회하시오.

SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS 
WHERE ORDER_TOTAL > ANY (SELECT ORDER_TOTAL FROM OE.ORDERS WHERE ORDER_STATUS IN (1,3))

 

 

9. GROUPING 문제

문제1) OE.ORDERS 테이블을 이용하여 ORDER_MODE, ORDER_STATUS 그룹짓고 모든 subtotal과 총합을 생성하세요.

SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY CUBE (ORDER_MODE, ORDER_STATUS);

문제2) Grouping sets 으로 OE.ORDERS 테이블을 이용하여 ORDER_MODE, ORDER_STATUS 과 전체 total을 생성하세요.

SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY GROUPING SETS ((ORDER_MODE), (ORDER_STATUS), ());
-- 두 컬럼을 모두 넣으면 모든행 출력

문제3) ORDER_MODE로 그룹화한 후, 각 ORDER_MODE 내에서 SALES_REP_ID로 추가 그룹화하여 주문 총액을 반환합니다. 또한 ORDER_MODE에 대한 subtotal와 Grand total도 반환하세요

SELECT ORDER_MODE, SALES_REP_ID, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY ROLLUP (ORDER_MODE, SALES_REP_ID);

문제4) 문제 3처럼 ORDER_MODE와 ORDER_STATUS의 각 조합에 대한 주문 총액을 반환하며, GROUPING 함수를 사용하여 어떤 열이 집계된 결과인지를 0과 1로 나타내세요.

SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL),
       GROUPING(ORDER_MODE) AS GRP_MODE,
       GROUPING(ORDER_STATUS) AS GRP_STATUS
FROM OE.ORDERS
GROUP BY ROLLUP (ORDER_MODE, ORDER_STATUS);

 

10. Window function 문제

문제1) 모든 주문에 대한 주문 총액(ORDER_TOTAL)의 합계를 출력하세요.

SELECT ORDER_ID, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER() AS TOTAL_SUM
FROM OE.ORDERS;

문제2) 주문 모드(ORDER_MODE)별 주문 총액(ORDER_TOTAL)의 합계를 출력하세요.

SELECT ORDER_ID, ORDER_MODE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(PARTITION BY ORDER_MODE) AS MODE_TOTAL
FROM OE.ORDERS;

문제3) 주문 날짜(ORDER_DATE)에 따라 주문 총액(ORDER_TOTAL)의 주문 날짜까지의 누적 합계를 반환하세요.

SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(ORDER BY ORDER_DATE) AS CUMULATIVE_TOTAL
FROM OE.ORDERS;

문제4) 주문 모드(ORDER_MODE)별로 그룹화하고, 주문 날짜(ORDER_DATE)에 따라 주문 총액과 해당 주문 모드 및 날짜까지의 누적 합계를 반환하세요.

SELECT ORDER_ID, ORDER_MODE, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(PARTITION BY ORDER_MODE ORDER BY ORDER_DATE) AS CUMULATIVE_MODE_TOTAL
FROM OE.ORDERS;

문제5) 문제: 주문 날짜(ORDER_DATE)에 따라 주문 총액 누적합계와 해당 주문 날짜를 포함한 최근 7일 동안의 누적 합계를 반환하세요.

Hint) BETWEEN INTERVAL ‘7’ DAY PRECEDING AND CURRENT ROW

SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(ORDER BY ORDER_DATE RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS WEEKLY_CUMULATIVE_TOTAL
FROM OE.ORDERS;

문제6) 각 판매 대표(SALES_REP_ID)에 대한 주문 총액(ORDER_TOTAL)과 해당 판매 대표의 평균 주문 총액을 반환하세요.

SELECT ORDER_ID, SALES_REP_ID, ORDER_TOTAL, 
AVG(ORDER_TOTAL) OVER(PARTITION BY SALES_REP_ID) AS AVG_SALES_BY_REP
FROM OE.ORDERS;

 

도움이 되셨다면 공감을 눌러주시면 큰 도움이 됩니다!!
자격증 취득과 SQL 향상에 도움이 되길 바랍니다 화이팅!
강의문의:https://snowgot.tistory.com/notice/96