[데이터 베이스] SQL 기본 문법 2 (JOIN, SUBQUERY)

mysql로 배우는 데이터베이스 개론과 실습 책을 보고 정리했습니다.

https://www.hanbit.co.kr/academy/books/book_view.html?p_code=B8548562053 

IT CookBook , MySQL로 배우는 데이터베이스 개론과 실습

 

 

 

Table Book
bookid	INTEGER
bookname	VARCHAR
publisher	VARCHAR
price	INTEGER

 

 

 

Table Customer
custid	INTEGER
name	VARCHAR
address	VARCHAR
phone	VARCHAR

 

 

 

Table Orders
orderid	INTEGER
custid	INTEGER
bookid	INTEGER
saleprice	INTEGER
orderdate	DATE
foreign key	REFERENCES Customer(custid)
foreign key	REFERENCES Book(bookid)

 

 

 

Table Imported_Book
bookid	INTEGER
bookname	VARCHAR
publisher	VARCHAR
price	INTEGER

 

 

두 개 이상 테이블에서 SQL 질의

SQL은 여러 개의 테이블을 이용하여 질의하는 두 가지 방법을 제공한다. 조인(join)과 부속질의(subquery)이다. 

 

 

조인 : 테이블의 행을 다른 테이블의 행에 연결하여 두 개 이상의 테이블을 결합하는 연산이다. 

 

 

고객과 고객의 주문에 관한 데이터를 모두 보이시오

동등조건에 의하여 테이블을 조인하는 것을 동등조인이라고 한다. 조인을 사용하면 두 개 이상의 테이블에서 일부 데이터만 얻을 수 있다. 

SELECT *
FROM Customer, Orders
WHERE Customer.custid=Orders.custid;

 

고객과 고객의 주문에 관한 데이터를 고객별로 정렬하여 보이시오

SELECT *
FROM Customer, Orders
WHERE Customer.custid=Orders.custid
ORDER BY Customer.custid

 

고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오

SELECT name, SUM(saleprice)
FROM Customer, Orders
WHERE Customer.custid=Orders.custid
GROUP BY Customer.name
ORDER BY Customer.name;

 

고객의 이름과 고객이 주문한 도서의 이름을 구하시오

SELECT Customer.name, Book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid=Orders.custid AND Orders.bookid=Book.bookid;

 

가격이 20000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오

SELECT Customer.name, Book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid=Orders.custid AND Orders.bookid=Book.bookid AND Book.price=20000;

 

도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오

조인 연산의 특별한 경우로 외부 조인(OUTER JOIN)이 있다. 

왼쪽에 있는 테이블에 대해 질의하려면 LEFT OUTER JOIN을 사용한다. 

오른쪽에 대해 질의하려면 RIGHT OUTER JOIN, 둘 다에 대해 질의하려면 FULL OUTER JOIN이다. 

이는 도서를 구매하지 않는 고객의 도서 판매가격 정보가 나오며 이는 NULL로 표현된다. 

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

 

 

 

부속질의 (중첩 질의라고도 한다) : SQL 내부의 또 다른 SQL문을 작성한다. 

 

 

가장 비싼 도서의 이름을 보이시오

두 질의를 하나의 질의로 작성할 수 있다. 

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

부속질의의 진행 순서는 WHERE 절의 부속질의를 먼저 처리하고 전체질의를 처리한다. 

 

 

도서를 구매한 적 있는 고객의 이름을 검색하시오

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

 

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

[상하관계가 있는 부속질의]

SELECT name
FROM Customer
WHERE custid IN (SELECT custid
                 FROM Orders
                 WHERE bookid IN (SELECT bookid
                                  FROM Book
                                  WHERE publisher='대한미디어'));

 

출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오

[상관관계가 있는 부속질의]

상위 부속질의에 사용된 Book 테이블을 b1으로 별칭하고, 하위 부속질의에 사용된 Book 테이블을 b2로 별칭한다. 

테이블 뒤에 이름으로 표기하는 것을 튜플변수라고 한다. 

SELECT b1.bookname
FROM Book b1
WHERE b1.price > (SELECT AVG(b2.price)
                  FROM Book b2
                  WHERE b2.publisher=b1.publisher);

 

 

집한 연산 : 고객 이름을 합집합으로 구한다. 

 

 

대한민국에서 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 보이시오

UNION 연산은 합집합이다. MYSQL은 차집합과 교집합이 없다. NOT IN 과 INTERSECT로 구현해야한다. 

UNION ALL은 중복을 포함한 모든 결과를 출력한다. 

SELECT name
FROM Customer
WHERE address LIKE `대한민국%`
UNION
SELECT name
From Customer
WHERE custid IN (SELECT custid FROM Orders);

 

 

EXISTS : 상관 부속질의문 형식이다. 원래 단어에서 의미하는 것과 같이 조건에 맞는 튜플이 존재하면 결과에 포함시킨다. 반면 NOT EXSITS는 모든 행이 조건에 만족하지 않을 때만 참이다. 

 

 

주문이 있는 고객의 이름과 주소를 보이시오

SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT *
              FROM Orders od
              WHERE cs.custid=id.custid);