MySQL 로 배우는 데이터베이스 개론과 실습 (- 박우창, 남송휘, 이현룡 지음) 교재의 연습문제입니다.
오답이 있다면 거리낌없이 지적해주세요!
1. 마당서점의 고객이 요구하는 다음 질문에 대해 SQL 문을 작성하시오.
1. 도서번호가 1인 도서의 이름
SELECT bookname
FROM Book
WHERE bookid = '1';2. 가격이 20,000 원 이상인 도서의 이름
SELECT bookname
FROM Book
WHERE price >= 20000;
3. 박지성의 총 구매액
-- <조인조건> AND <검색조건> 사용
SELECT SUM(od.saleprice)
FROM Orders od
WHERE custid = (SELECT custid
FROM Customer cs
WHERE cs.name = '박지성')
-- JOIN 사용
SELECT SUM(saleprice)
FROM Orders
JOIN Customer
ON Orders.custid = Customer.custid
WHERE Customer.name = '박지성';
-- 부속질의 사용
SELECT SUM(od.saleprice)
FROM Orders od
WHERE custid = (SELECT custid
FROM Customer cs
WHERE cs.name = '박지성')
4. 박지성이 구매한 도서의 수
-- <조인조건> AND <검색조건>
SELECT COUNT(*) AS '구매 도서 수'
FROM Orders od, Customer cs
WHERE od.custid = cs.custid
AND cs.name = '박지성';
-- JOIN 사용
SELECT COUNT(*) AS '구매 도서 수'
FROM Orders od
JOIN Customer cs
ON od.custid = cs.custid
WHERE cs.name = '박지성';
-- 부속질의 사용
SELECT COUNT(*) AS '구매 도서 수'
FROM Orders
WHERE custid = (SELECT custid
FROM Customer
WHERE name = '박지성');5. 박지성이 구매한 도서의 출판사 수
-- <조인 조건> AND <검색 조건>
SELECT COUNT(DISTINCT bo.publisher) AS '구매한 도서 출판사 수'
FROM Book bo, Customer cs, Orders od
WHERE bo.bookid = od.bookid
AND cs.custid = od.custid
AND cs.name = '박지성';
-- JOIN 사용
SELECT count(DISTINCT bo.publisher) AS '구매한 도서 출판사 수'
FROM Orders od
JOIN Book bo ON bo.bookid = od.bookid
JOIN Customer cs ON cs.custid = od.custid
WHERE cs.name = '박지성';
-- 부속질의 사용
SELECT count(DISTINCT publisher) as '구매한 도서 출판사 수'
FROM Book
WHERE bookid IN (SELECT bookid
FROM Orders
WHERE custid IN (SELECT custid
FROM Customer
WHERE name = '박지성');6. 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
-- <조인 조건> AND <검색 조건>
SELECT bo.bookname, bo.price, (bo.price - od.saleprice) as '정가와 판매가격 차이'
FROM Book bo, Customer cs, Orders od
WHERE bo.bookid = od.bookid
AND cs.custid = od.custid
AND cs.name = '박지성';
-- JOIN 사용
SELECT bo.bookname, bo.price, (bo.price - od.saleprice) as '정가와 판매가격 차이'
FROM Orders od
JOIN Book bo ON bo.bookid = od.bookid
JOIN Customer cs ON cs.custid = od.custid
WHERE cs.name = '박지성';
부속질의를 사용한다면?
-- 부속질의 사용 에러 발생
SELECT bo.bookname, bo.price, (bo.price - od.saleprice) as '정가와 판매가격 차이이'
FROM Book bo
WHERE bookid IN (SELECT bookid
FROM Orders od
WHERE custid IN (SELECT custid
FROM Customer
WHERE name = '박지성')
);위 SQL 코드는 오류가 나타난다.
Error Code: 1054. Unknown column 'bo.bookname' in 'field list' 0.0033 sec
부속 질의는 SELECT 문에서 나오는 결과 속성을 FROM 절의 테이블에서만 얻을 수 있다. 상위 SELECT 문의 FROM 절의 테이블은 Book 밖에 없으므로 필드의 없는 값(od.saleprice) 때문에 에러가 발생한다.
굳이 부속질의를 사용하고 싶다면 아래처럼 해야 한다.
-- 부속질의 사용
SELECT bo.bookname, bo.price, (bo.price - od.saleprice) as '정가와 판매가격 차이이'
FROM Book bo, Orders od
WHERE bo.bookid = od.bookid
AND od.custid IN (SELECT custid
FROM Customer
WHERE name = '박지성');
7. 박지성이 구매하지 않은 도서의 이름
<조인 조건> AND <검색 조건>
-- NOT IN
SELECT bookname
FROM Book bo
WHERE bookid NOT IN (SELECT bo.bookid
FROM Customer cs, Orders od
WHERE bo.bookid = od.bookid
AND cs.custid = od.custid
AND cs.name = '박지성');
-- NOT EXISTS
SELECT bo.bookname
FROM Book bo
WHERE NOT EXISTS (SELECT *
FROM Customer cs, Orders od
WHERE bo.bookid = od.bookid
AND cs.custid = od.custid
AND cs.name = '박지성');JOIN 사용
-- NOT IN 사용
SELECT bo.bookname
FROM Book bo
WHERE bo.bookid NOT IN (SELECT bo.bookid
FROM Orders od
JOIN Book bo ON od.bookid = bo.bookid
JOIN Customer cs ON od.custid = cs.custid
WHERE cs.name = '박지성'
);
-- NOT EXISTS 사용
SELECT bo.bookname
FROM Book bo
WHERE NOT EXISTS (SELECT *
FROM Orders od
JOIN Customer cs ON od.custid = cs.custid
WHERE od.bookid = bo.bookid
AND cs.name = '박지성'
);NOT IN 으로 한 경우 중첩된 내부 쿼리의 Book bo 와 외부 쿼리의 Book bo 는 서로 연관이 없는 테이블이다. 단지 조건에 맞는 bookid 들의 집합을 계산한 후 그 집합에 없는 bookid 의 bookname 을 쿼리하고 있다.
하지만 NOT EXISTS 의 경우 이야기가 조금 다르다. EXISTS 와 NOT EXISTS 는 상관 부속 질의문(correlated subquery) 의 다른 형태이다. 그러므로 NOT IN 에서처럼 새로운 Book bo 을 조인하는 것이 아니다. 즉, NOT EXISTS 을 할 경우 중첩된 내부 쿼리에서 Book bo 을 조인할 수 없다. 위처럼 하는 것이 최선인 것 같네요...
그런데 이렇게 푸는 게 더 깔끔하고 간단해 보입니다.
-- JOIN 만 사용
SELECT bo.bookname
FROM Orders od
JOIN Book bo ON bo.bookid = od.bookid
JOIN Customer cs ON cs.custid = od.custid
WHERE cs.name != '박지성';
부속질의 사용
-- NOT IN 사용
SELECT bo.bookname
FROM Book bo
WHERE bo.bookid NOT IN (SELECT od.bookid
FROM Orders od
WHERE od.custid IN (SELECT cs.custid
FROM Customer cs
WHERE cs.name = '박지성')
);
-- NOT EXISTS 사용
SELECT bo.bookname
FROM Book bo
WHERE NOT EXISTS (SELECT *
FROM Orders od
WHERE bo.bookid = od.bookid
AND od.custid IN (SELECT cs.custid
FROM Customer cs
WHERE cs.name = '박지성')
);
2. 마당 서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오.
1. 마당 서점 도서의 총 개수
SELECT COUNT(*) AS '도서의 총 개수'
FROM Book;2. 마당 서점에 도서를 출고하는 출판사의 총 개수
SELECT COUNT(DISTINCT publisher) AS '출판사 개수'
FROM Book;3. 모든 고객의 이름, 주소
SELECT name, address
FROM Customer;4. 2014년 7월 4일 ~ 7월 7일 사이에 주문받은 도서의 주문번호
SELECT orderid
FROM Orders
WHERE orderdate BETWEEN '2014-07-04' AND '2021-07-07';5. 2014년 7월 4일 ~ 7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호
SELECT orderid
FROM Orders
WHERE orderdate NOT BETWEEN '2014-07-04' AND '2021-07-07';6. 성이 '김'씨인 고객의 이름과 주소
SELECT name, address
FROM Customer
WHERE name LIKE '김%' ;7. 성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소
SELECT name, address
FROM Customer
WHERE name LIKE '김%아';8. 주문하지 않은 고객의 이름(부속질의 사용)
SELECT name
FROM Customer cs
WHERE NOT EXISTS( SELECT custid
FROM Orders od
WHERE od.custid = cs.custid);9. 주문 금액의 총액과 주문의 평균 금액
SELECT sum(saleprice) AS '주문 금액 총액', AVG(saleprice) AS '주문 평균 금액'
FROM Orders;10. 고객의 이름과 고객별 구매액
SELECT cs.name, SUM(od.saleprice)
FROM Customer cs, Orders od
WHERE cs.custid = od.custid
GROUP BY cs.custid;11. 고객의 이름과 고객이 구매한 도서 목록
SELECT
cs.name, bo.bookname
FROM
Customer cs,
Book bo,
Orders od
WHERE
cs.custid = od.custid
AND bo.bookid = od.bookid12. 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문
SELECT *
FROM Book bo, Orders od
WHERE bo.bookid = od.bookid
AND bo.price - od.saleprice = (SELECT MAX(bo.price - od.saleprice)
FROM Book bo,Orders od
WHERE bo.bookid = od.bookid);
13. 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
SELECT cs.custid, AVG(od.saleprice) AS '구매액 평균'
FROM Orders od
JOIN Customer cs ON cs.custid = od.custid
GROUP BY cs.custid
HAVING AVG(od.saleprice) > (SELECT AVG(od.saleprice) AS '도서 판매액 평균'
FROM Orders od);
3. 마당서점에서 다음의 심화된 질문에 대해 SQL 문을 작성하시오.
1. 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름
SELECT DISTINCT cs.name
FROM Orders od, Customer cs, Book bo
WHERE od.bookid = bo.bookid
AND od.custid = cs.custid
AND cs.name != '박지성'
AND bo.publisher IN (SELECT bo.publisher
FROM Orders od,Customer cs,Book bo
WHERE od.bookid = bo.bookid
AND od.custid = cs.custid
AND cs.name = '박지성');혹은
SELECT DISTINCT cs.name
FROM Orders od
JOIN Customer cs ON cs.custid = od.custid
JOIN Book bo ON bo.bookid = od.bookid
WHERE cs.name != '박지성'
AND bo.publisher IN (SELECT bo.publisher
FROM Orders od
JOIN Customer cs ON cs.custid = od.custid
JOIN Book bo ON bo.bookid = od.bookid
WHERE cs.name = '박지성');혹은
SELECT DISTINCT name
FROM Customer
JOIN Orders ON Customer.custid = Orders.custid
WHERE bookid IN (SELECT bookid
FROM Book
WHERE Customer.name NOT LIKE '박지성'
AND publisher IN (SELECT publisher
FROM Orders
JOIN Book ON Orders.bookid = Book.bookid
WHERE Orders.custid = (SELECT custid
FROM Customer
WHERE name LIKE '박지성')));2. 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름
SELECT Customer.name AS '두 개 이상의 서로 다른 출판사에서 도서 구매 고객'
FROM Book, Customer, Orders
WHERE Book.bookid = Orders.bookid
AND Customer.custid = Orders.custid
GROUP BY Customer.name
HAVING Count(DISTINCT Book.publisher) >= 2;
3. 전체 고객의 30% 이상이 구매한 도서
SELECT Book.bookname AS '전체 고객의 30% 이상이 구매한 도서'
FROM Book, Orders
WHERE Book.bookid = Orders.bookid
GROUP BY Book.bookname
HAVING COUNT(*) >= (SELECT COUNT(*)
FROM Customer) * 0.3;혹은
SELECT bookname AS '전체 30% 이상이 구매한 도서'
FROM Book b1
WHERE (
SELECT COUNT(Book.bookid)
FROM Book
JOIN Orders ON Book.bookid = Orders.bookid
WHERE Book.bookid = b1.bookid
) >= 0.3 * (SELECT COUNT(*) FROM Customer);
4. 다음 질의에 대해 DDL 문과 DML 문을 작성하시오.
(1) 새로운 도서('스포츠 세계', '대한 미디어', 10,000원) 이 마당 서점에 입고되었다. 삽입이 안될 경우 필요한 데이터가 더 있는지 찾아보시오.
해당 데이터로는 새로운 도서가 Book 테이블에 삽입되지 않는다. bookid 라는 값이 하나 더 필요하다.
Error Code: 1364, Filed 'bookid' doesn't have a default value
예를 들어
INSERT INTO Book(bookid, bookname, publisher, price)
VALUES (11,'스포츠 세계', '대한 미디어', 10000);
(2) ‘삼성당’에서 출판한 도서를 삭제하시오.
DELETE FROM Book
WHERE publisher = '삼성당';
(3) ‘이상미디어’에서 출판한 도서를 삭제하시오. 삭제가 안 되면 원인을 생각해보시오.
DELETE FROM Book
WHERE publisher = '이상미디어';을 하면 아래와 같은 Error Code 가 발생한다.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`madang`.`orders`, CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`bookid`) REFERENCES `book` (`bookid`))
Book 의 bookid 는 Orders 에서 외래키로 갖고 있다. 그리고 '이상미디어' 에서 출판한 도서의 bookid 가 7 인 주문과 8 인 주문이 Orders 테이블에 존재하므로 삭제할 수 없다. 즉, 참조 무결성 제약 조건에 걸린다.
(4) 출판사 ‘대한미디어’를 ‘대한출판사’로 이름을 바꾸시오.
UPDATE Book
SET publisher = '대한출판사'
WHERE publisher = '대한미디어';
(5) (테이블 생성) 출판사에 대한 정보를 저장하는 테이블 Bookcompany(name, address, begin)
를 생성하고자 한다. name은 기본키며 VARCHAR(20), address는 VARCHAR(20), begin은
DATE 타입으로 선언하여 생성하시오.
CREATE TABLE Bookcompany(
name VARCHAR(20) PRIMARY KEY,
address VARCHAR(20),
begin DATE
);
-- 혹은
CREATE TABLE Bookcompany (
name VARCHAR(20),
address VARCHAR(20),
begin DATE,
PRIMARY KEY(name)
);
(6) (테이블 수정) Bookcompany 테이블에 인터넷 주소를 저장하는 webaddress 속성을
VARCHAR(30) 으로 추가하시오.
ALTER TABLE Bookcompany
ADD COLUMN webaddress VARCHAR(30);
(7) Bookcompany 테이블에 임의의 투플 name=한빛아카데미, address=서울시 마포구,
begin=1993—01—01, webacldress=http：//hanbit.co.kr을 삽입하시오
INSERT INTO Bookcompany(name, address, begin, webaddress)
VALUES ('한빛아카데미', '서울시 마포구', '1993-01-01', 'http://hanbit.co.kr');
5. 다음 EXISTS 질의의 결과를 보이시오.
SELECT *
FROM Customer c1
WHERE EXISTS (SELECT *
FROM Orders c2
WHERE c1.custid = c2.custid);(1) 질의의 결과는 무엇인가?
(2) NOT 을 지우면 질의의 결과는 무엇인가?
너무 길어져서 심화 문제는 다음 글에서 계속 풉니다.