SQL 시험 오답노트 정리

1. 테이블 생성

오더리스트 테이블 생성하기

조건

회원이 상품을 구매하면 그 기록은 주문내역(ORDERLIST) 테이블에 저장된다.
주문내역 테이블은 주문번호(OID),구매자(MID),주문상품번호(PID),구매개수(CNT),구매방법(PAYMENT)를 가진다.
주문번호는 PK이고, 구매내역 테이블의 모든 컬럼은 NULL이 아니다.
구매자,주문상품번호는 FK 역할을 수행한다.

내가 적은 답

CREATE TABLE ORDERLIST(
-- 주문내역 테이블 만들기
	OID INT PRIMARY KEY,
	-- 주문번호는 숫자로 받도록 설정했고 pK로 설정했다
	MID VARCHAR(15) NOT NULL,
	-- 구매자의 아이디는 멤버 테이블과 똑같이 맞춰주었고 주문내역 테이블에 FK다
	PID INT NOT NULL,
	-- 주문상품 번호의 아이디는 상품 테이블과 똑같이 맞춰주었고 주문내역 테이블에 FK다
	CNT INT NOT NULL,
	--구매개수를 숫자로 받고 공백을 허용하지 않는 제약조건을 사용했다
	PAYMENT VARCHAR(30) NOT NULL
	--구매 방법이 30자가 넘지 않는다 공백을 허용하지 않는 제약조건을 사용했다

);

틀린 이유

구매자,주문상품번호는 FK 역할을 수행한다. -> 이 조건에서 테이블 생성시 FK값에대한 이름과 조건을 맞춰주었다고 생각했지만 FK라고 선언하지 않았다

정답

CREATE TABLE ORDERLIST(
    OID INT PRIMARY KEY,
    MID VARCHAR(15) NOT NULL,
    PID INT NOT NULL,
    CNT INT NOT NULL,
    PAYMENT VARCHAR(30) NOT NULL,
    FOREIGN KEY (MID) REFERENCES MEMBER(MID),
    외부키 멤버의 아이디 가져올곳 멤버의 멤버아이디
    FOREIGN KEY (PID) REFERENCES PRODUCT(PID)
)

[4] 주문내역 테이블에 아래설명된 8개의 주문내역 데이터를 생성하시오.
   (210203   teemo   1003   1   계좌이체)
   (210204   amumu   1008   1   계좌이체)
   (210205   teemo   1006   1   카카오페이)
   (210206   admin   1009   1   계좌이체)
   (210207 teemo   1008   2   계좌이체)
   (210208 test   1010   1   네이버페이)
   (210209 ari   1007   3   네이버페이)
   (210210 ari   1008   10   카카오페이)
※ 현재 상품 테이블의 데이터(재고)에는 변화가 없습니다! ※
※ 과거에 주문했던 이력이라고 생각해주세요!!! ※



내가 적은답

-- 과거 주문 내용추가하기
INSERT INTO ORDERLIST VALUES (210203 'teemo', 1003, 1, '계좌이체');
INSERT INTO ORDERLIST VALUES (210204 'amumu', 1008, 1, '계좌이체');
INSERT INTO ORDERLIST VALUES (210205 'teemo', 1006, 1, '카카오페이');
INSERT INTO ORDERLIST VALUES (210206 'admin', 1009, 1, '계좌이체');
INSERT INTO ORDERLIST VALUES (210207 'teemo', 1008, 2, '계좌이체');
INSERT INTO ORDERLIST VALUES (210208 'test', 1010, 1, '네이버페이');
INSERT INTO ORDERLIST VALUES (210204 'ari', 1007, 3, '네이버페이');
INSERT INTO ORDERLIST VALUES (210204 'ari', 1008, 10, '카카오페이');

틀린이유

주문 번호뒤 쉼표누락 및 주문 번호 중복 오타남

정답

INSERT INTO ORDERLIST VALUES (210203, 'teemo', 1003, 1, '계좌이체');
INSERT INTO ORDERLIST VALUES (210204, 'amumu', 1008, 1, '계좌이체');
INSERT INTO ORDERLIST VALUES (210205, 'teemo', 1006, 1, '카카오페이');
INSERT INTO ORDERLIST VALUES (210206, 'admin', 1009, 1, '계좌이체');
INSERT INTO ORDERLIST VALUES (210207, 'teemo', 1008, 2, '계좌이체');
INSERT INTO ORDERLIST VALUES (210208, 'test', 1010, 1, '네이버페이');
INSERT INTO ORDERLIST VALUES (210209, 'ari', 1007, 3, '네이버페이');
INSERT INTO ORDERLIST VALUES (210210, 'ari', 1008, 10, '카카오페이');

[5] 상품 테이블의 데이터를 재고가 많은 순서대로 출력하시오.

단, 재고의 양이 같으면 먼저 등록된 상품부터 출력하시오.

내가 적은 답

SELECT * FROM PRODUCT ORDER BY CNT DESC PID ASC;

틀린이유 

ORDER BY CNT DESC 뒤에 쉼표 누락

정답

SELECT * FROM PRODUCT ORDER BY CNT DESC, PID ASC;

[6] 상품 테이블의 데이터를 아래와 같이 출력하시오.
   PID   NAME   PRICE   CNT
   (1001   립*   7000   11)
   (1002   핸***   7900   3)
   (1003   후**   34500   2)
   (1004   실**   11900   11)
   (1005   티**   13900   2)
   (1007   운**   97900   2)
   (1008   우*   1200   11)
   (1009   초***   1250   8)
HINT. 상품명과 재고품절상태에 유의하세요.

틀린 이유

이미 앞에서 시간 분배 실패했다 못풀어서 아쉽긴 한데 시간이 더 있었다고 해도 적는데 시간이 꽤 걸렸을 것같다

정답

SELECT 
    PID,
    CASE 
        WHEN PID = 1001 THEN '립*'
        “PID가 1001이면 ‘립*’로 출력, 이 결과 컬럼 이름은 NAME으로 하겠다”
        WHEN PID = 1002 THEN '핸***'
        WHEN PID = 1003 THEN '후**'
        WHEN PID = 1004 THEN '실**'
        WHEN PID = 1005 THEN '티**'
        WHEN PID = 1007 THEN '운**'
        WHEN PID = 1008 THEN '우*'
        WHEN PID = 1009 THEN '초***'
    END AS NAME, 이름 *처리 끝 컬럼이름 지정
    PRICE, 기존 가격 컬럼 출력
    CNT 기존 상품개수 출력 
FROM PRODUCT
WHERE CNT > 0
ORDER BY PID;

CASE문은 조건에 따라 다른 값을 반환하고 싶을때 쓰는 조건문이다 자바의 if~esle정도의 느낌
이라고 생각하면 된다

여기서 상품명의 * 표시를 위해 CASE문을 사용했고 WHERE CNT > 0우로 재고가 0인 상품은 제거 했으며
ORDER BY PID로 상품번호순으로 출력했다

[7] 상품 테이블의 1001번 데이터를 변경하세요.

기존 (1001 립밤 7000 11)

변경 (1001 립스틱 7550 11)

내가 적은답

UPDATE PRODUCT SET NAME = '립스틱' WHERE PID = 1001;

틀린이유

 답적은게 어디갔는지 보이지는 않는데 기억하기로 가격변경을 빼먹은것 같음

정답

UPDATE PRODUCT SET NAME = '립스틱', PRICE = 7550 WHERE PID = 1001;

[8] 상품 테이블의 1001번 데이터를 삭제하세요.

틀린이유

제출한 답이 안보여서 일단 정답 적음 근데 정답처럼 적었던것 같기는함

정답

DELETE FROM PRODUCT WHERE PID = 1001;

[9] 상품 테이블의 데이터를 조건에 맞게 출력하시오.
   1) 상품명이 '우유'로 끝나는 상품을 검색
   2) 상품명에 '화'가 들어가있으면서 재고가 품절이 아닌 상품을 검색
※ 2문제 입니다! 쿼리문을 2개 작성해주세요! ※

내가 적은 답
SELECT * FROM STUDENT WHERE NAME LIKE '%우유';
SELECT * FROM STUDENT WHERE NAME LIKE '%화%' ;

틀린 이유

블로그 보면서 베끼면서 적다가 컬럼명을 PRODUCT 가 아니라 STUDENT로 적음 그리고 화가 들어가는 상품중에

재고가 품절이 아닌 상품을 검색이 빠짐

정답

SELECT * FROM PRODUCT WHERE NAME LIKE '%우유';
SELECT * FROM PRODUCT WHERE NAME LIKE '%화%'
 AND CNT > 0;

[10] 상품 테이블의 데이터를 아래와 같이 출력해주세요.
   상품번호   상품명   판매량(TOTALCNT)   판매금액(TOTALPRICE)
   (1008   우유   13      15600)
   (1009   초코우유   1      1250)
※ 우유들은 예시입니다!
HINT. 주문내역 테이블과의 JOIN

틀린이유

앞 문제푸느라 시간 분배를 잘못해서 문제를 읽지 못함

정답

SELECT 
    P.PID, 상품의 상품번호
    P.NAME, 상품의 상품이름
    SUM(O.CNT) AS TOTALCNT, 상품의 총판매량
    SUM(O.CNT * P.PRICE) AS TOTALPRICE 상품의 총판매금액
FROM PRODUCT P 상품에
JOIN ORDERLIST O 오더리스트를 연결하겟다
  ON P.PID = O.PID 상품번호랑 주문번호를
GROUP BY P.PID, P.NAME
ORDER BY P.PID;

SUM
특정 컬럼의 합계를 구할때 사용함
SELECT SUM(컬럼명) AS 합계컬럼
FROM 테이블명;
으로 사용할수 있음

GRUOP BY
특정 컬럼을 기준으로 데이터를 그룹화 할때 사용
SUM,COUNT,AVG 같은 집계함수와 함께 사용된다
SELECT 그룹컬럼, SUM(값컬럼) AS 합계
FROM 테이블
GROUP BY 그룹컬럼;
으로 사용한다

[11] [10]번에서 출력한 데이터들을 판매량 순서대로 정렬하여 출력해주세요.
판매량이 같다면 상품번호 순서대로 정렬해주세요.

틀린이유

앞 문제푸느라 시간 분배를 잘못해서 10번 문제를 읽지 못함 따라서 11번도 풀지 못함

정답

SELECT 
    P.PID,
    P.NAME,
    SUM(O.CNT) AS TOTALCNT,
    SUM(O.CNT * P.PRICE) AS TOTALPRICE
FROM PRODUCT P
JOIN ORDERLIST O
  ON P.PID = O.PID
GROUP BY P.PID, P.NAME
ORDER BY TOTALCNT DESC, P.PID ASC;
판매량이 많은 상품이 먼저 나오고 상품번호 오름차순

[12] 회원 테이블의 데이터를 아래와 같이 출력해주세요.
   아이디   이름   총구매금액(TOTAL)
   (teemo   티모   58900)
   (ari   아리   305700)
※ 티모와 아리는 예시입니다!
※ 아이디 순서대로(abcd 순서대로) 정렬하여 출력해주세요.

틀린이유

앞 문제푸느라 시간 분배를 잘못해서 문제를 읽지 못함

정답

SELECT 
    M.MID AS 아이디,
    M.NAME AS 이름,
    SUM(O.CNT * P.PRICE) AS TOTAL AS로 정해진 칼럼명사용 
    각 회원의 총 결제금액 계산
FROM MEMBER M 멤버테이블에서
JOIN ORDERLIST O 연결하겟다 오더리스트를
  ON M.MID = O.MID
JOIN PRODUCT P 상품도 연결하겟다
  ON O.PID = P.PID
GROUP BY M.MID, M.NAME 회원별로 집계
ORDER BY M.MID ASC; 아이디 기준 오름차순

[13] [12]번에서 출력한 데이터들을 총구매금액 순서대로 정렬하여 출력해주세요.

틀린 사유

12번을 풀지못하여 13번도 진행하지 못함

정답

SELECT 
    M.MID AS 아이디,
    M.NAME AS 이름,
    SUM(O.CNT * P.PRICE) AS TOTAL
FROM MEMBER M
JOIN ORDERLIST O
  ON M.MID = O.MID
JOIN PRODUCT P
  ON O.PID = P.PID
GROUP BY M.MID, M.NAME
ORDER BY TOTAL DESC;
구매금액이 많은 회원이 먼저 출력

[14] 주문내역 테이블이 있을 때, 구매방법별로 판매금액의 총합(TOTALPRICE)을 출력해주세요.
   구매방법      TOTALPRICE
   (계좌이체      39350)
   (네이버페이   298200)
   (카카오페이   34000)
HINT. 집계 함수 & 그룹화

틀린사유

시간 관계상 문제 못읽음

정답

정답

SELECT 
    O.PAYMENT AS 구매방법, 지정된 AS 이름 사용
    SUM(O.CNT * P.PRICE) AS TOTALPRICE
    각 결제 수단별 총 판매금액
FROM ORDERLIST O 오더리스트 에
JOIN PRODUCT P 상품을 연결하겟다
  ON O.PID = P.PID 오더리스트 주문번호랑 상품넘버를 
GROUP BY O.PAYMENT
결제수단별 집계
ORDER BY O.PAYMENT;
보기 편하게 정렬

[15] 회원,상품,주문내역 테이블을 삭제하시오.

내가 적은 답

DROP TABLE MEMBER; 

DROP TABLE PRODUCT;

DROP TABLE ORDERLIST;

틀린이유

문법적으로는 틀리지 않은것같은데 삭제시 FK가 있는 오더리스트를 고려해서 순서를 생각하지 않았다

정답

DROP TABLE ORDERLIST;
DROP TABLE MEMBER;
DROP TABLE PRODUCT;

FK가 있는 오더리스트를 제일먼저 삭제해야한다
그다음 멤버 -> 프로덕트 순으로 삭제한다