[SQL] 실습 정리(+JOIN, OUTER_JOIN, CASE, SUBQUARY)

지난시간 JOIN문 예제

0.JOIN.1. 고객ID, 고객명, 도서ID, 도서판매금액, 판매일을 출력해 보시오

SELECT a.custid, a.name, b.bookid, b.saleprice, b.orderdate
FROM Customer a, Orders b
WHERE a.custid = b.custid;

RESULT

0.JOIN.2. 고객ID, 고객명, 도서ID, 도서명, 판매금액, 판매일을 출력해 보시오

SELECT a.custid, a.name, b.bookid, b.bookname, c.saleprice, c.orderdate
FROM Customer a, Book b, Orders c
WHERE a.custid = c.custid
  AND b.bookid = c.bookid;

RESULT

0.OUTER_JOIN.3. 모든 고객의 구매 금액을 구하시오

OUTER JOIN이란? 한쪽 테이블에 하나라도 있으면 전부 나오도록 하는 함수

기준이 되는 테이블을 중심으로 출력하기에 모두 출력을 원하는 부분의 방향을 적어준다

EX) LEFT OUTER JOIN / RIGHT OUTER JOIN

NULL값은 모두 제일 마지막 부분에 출력된다

SELECT a.custid, a.name, b.saleprice
FROM Customer a LEFT OUTER JOIN Orders b
ON a.custid = b.custid;

0.OUTER_JOIN.4 구매가 일어나지 않은 고객만 출력하시오

SELECT a.custid, a.name, b.saleprice
FROM Customer a LEFT OUTER JOIN Orders b
ON a.custid = b.custid
AND b.saleprice IS NULL;

0.CASE WHEN.5 도서번호, 도서명, 도서금액에서

도서금액이 3만원 이상이면 A

2만원 이상 3만원 미만이면 B

2만원 미만이면 C로 출력

이 문제는 실제 기업에서도 많이 다루고 있는 문제로 외워두는 것이 좋다

SELECT bookid, bookname,
  CASE WHEN price >= 30000 THEN 'A' 
      WHEN price >= 20000 AND PRICE < 30000 THEN 'B' 
      WHEN price < 20000 THEN 'C'
      ELSE NULL
      END
FROM Book;

RESULT

SELECT bookid, bookname, price,
# PRICE 추가 후 LEVEL DATA 제대로 들어갔는 지 확인
  CASE WHEN price >= 30000 THEN 'A' 
      WHEN price >= 20000 AND PRICE < 30000 THEN 'B' 
      WHEN price < 20000 THEN 'C'
      ELSE NULL
      END AS LEVEL
      # 변경된 부분
FROM Book;

CUSTOMIZED RESULT

---

이번시간 예제들

1.SUB_QUARY.1 책 중에 가장 비싼 책을 출력하시오

SELECT bookname 
FROM Book
WHERE PRICE = (SELECT MAX(price) FROM Book);

RESULT

1.SUB_QUARY.2 구매가 발생한 고객의 정보를 출력하시오

SELECT *
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);

RESULT

1.SUB_QUARY.3 'DadHan Media'라는 출판사의 책을 구매한 고객의 아이디, 주문일자를 출력하시오

SELECT custid, orderdate
FROM Orders
WHERE bookid IN (SELECT bookid FROM Book WHERE publisher = 'DaeHan Media');

RESULT

이론 상 다중 SUB QUARY 가능하다

2. JOIN.4 대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오

SELECT b.name
FROM Book a, Customer b, Orders c
WHERE a.bookid = c.bookid
AND b.custid = c.custid
AND a.publisher = 'DAEHAN MEDIA'
AND c.orderdate IS NOT NULL;

이전 데이터를 옮겨오는 과정 중 날짜가 소실되었을 가능성을 넣어 만든 SQL문이다

RESULT

---

집합 연산

UNION, MINUS, INTERSECT(합집합, 차집합, 교집합)

느린 처리속도, 자주 이용되지 않음

 

EXISTS 

SUB QUARY, JOIN과 비슷하지만 속도를 더 빠르게 하기 위한 메소드

집합 연산과는 다른 연산방식을 이용해서 더 빠른 속도를 자랑한다

사용 위치는 WHERE 바로 뒤

---

3.EXISTS.1 구매한 내역이 있는 고객의 이름, 주소

SELECT a.name, a.address
FROM Customer a
WHERE EXISTS 
  (
  SELECT 1
  FROM Orders b
  WHERE b.custid = a.custid
  );

RESULT

---

다음에 다룰 내용 PREVIEW

ALTER 문

테이블 구조를 변경하거나 삭제할 때 쓰는 SQL문

그래서 ALTER 다음에 TABLE명이 온다

EX) ALTER TABLE TABLE명 [] [] [] []

ADD, DROP(속성 추가/제거)

MODIFY(속성의 기본겂 설정/삭제)