GROUP BY 관련 SQL 예제 2, 고객, 주문, 총액, 쇼핑몰 데이터베이스

▣ GROUP BY 관련 SQL 예제 2

  - SELECT문에 있는 서브쿼리 : Scalar Subquery

  - FROM절에 있는 서브쿼리 : Inline View

  - WHERE절에 있는 서브쿼리 : Subquery

 

---

2012년 57번

정답 : 3번

쇼핑몰 데이터베이스 중 고객 테이블의 고객번호 컬럼을 GROUP BY한 값이 10보다 큰 고객의 이름과 주문 수량의 합을 구하는 조회문임

고객번호로 GROUP BY 한 값이 10보다 크다는 것은 주문 횟수가 11번 이상이라는 의미임

 

3번 '열한 번 이상 상품을 주문한 고객이름과 주문한 수량의 합을 구한다.' 정답임

 

---

2015년 65번

정답 : 3번

-- 실제 SQL 실행시 에러 발생, WHERE 절의 ALIAS명 인식 불가 

-- WHERE절에서 서브쿼리 사용 시 다수 row 반환 시 IN, 단일 row반환 시 = 사용

 

----------------------------------------------------------------
-- 2015년 정보시스템감리사 DB 65번

CREATE TABLE CUSTOMER_65
(
  CNO INT
, CNAME VARCHAR(20) NOT NULL
, PRIMARY KEY (CNO)
)
;

INSERT INTO CUSTOMER_65 VALUES (1, '홍길동');
INSERT INTO CUSTOMER_65 VALUES (2, '강감찬');
INSERT INTO CUSTOMER_65 VALUES (3, '이순신');
INSERT INTO CUSTOMER_65 VALUES (4, '김홍도');

COMMIT;

SELECT * FROM CUSTOMER_65;

 DROP TABLE ORDERS_65;
CREATE TABLE ORDERS_65
(
  ONO INT  
, CNO INT
, BNAME VARCHAR(20)
, PRICE INT
, PRIMARY KEY (ONO)
, FOREIGN KEY (CNO) REFERENCES CUSTOMER_65(CNO)
)
;

INSERT INTO ORDERS_65 VALUES (101, 1, '톰소여의 모험',1000);
INSERT INTO ORDERS_65 VALUES (102, 2, '개션문',1100);
INSERT INTO ORDERS_65 VALUES (103, 3, '셜록홈즈',1200);
INSERT INTO ORDERS_65 VALUES (104, 4, '오만과 편견',1500);
INSERT INTO ORDERS_65 VALUES (105, 1, '정보시스템감리사',2000);
INSERT INTO ORDERS_65 VALUES (106, 2, '정보처리기사',1500);
INSERT INTO ORDERS_65 VALUES (107, 3, '부자아빠가난한아빠',2200);
INSERT INTO ORDERS_65 VALUES (108, 4, '초격차',2000);

COMMIT;

SELECT * FROM ORDERS_65;

-- 보기 1번
SELECT CNO, (SELECT CNAME FROM CUSTOMER_65 CS
             WHERE CS.CNO = OD.CNO)
AS NAME, SUM(PRICE)
FROM ORDERS_65 OD
GROUP BY OD.CNO;

-- 보기 2번
SELECT CS.CNO, CS.CNAME, S
FROM (SELECT CNO, SUM(PRICE) S
      FROM ORDERS_65 GROUP BY CNO) OD,
      CUSTOMER_65 CS
WHERE CS.CNO = OD.CNO;

-- 보기 3번
SELECT CS.CNO, CS.CNAME, S
FROM CUSTOMER_65 CS, ORDERS_65 OD 
WHERE CS.CNO = OD.CNO AND
     (SELECT SUM(PRICE) S FROM ORDERS_65 OD);

--  에러 발생, WHERE 절의 ALIAS명 인식 불가

    
-- 보기 4번
SELECT CS.CNO, CS.CNAME, SUM(PRICE)
FROM CUSTOMER_65 CS, ORDERS_65 OD 
WHERE CS.CNO = OD.CNO
GROUP BY CS.CNO, CS.CNAME;