MySQL로 배우는 데이터베이스 개론과 실습 답안 - Chapter 03

 

## 연습문제

1. 마당서점의 고객이 요구하는 다음 질문에 대해 SQL문 작성

 

(1) 도서번호가 1인 도서의 이름

SELECT bookname
FROM book
WHERE bookid = 1;

(2) 가격이 20,000원 이상인 도서의 이름

SELECT bookname
FROM book
WHERE price >= 20000;

(3) 박지성의 총 구매액

SELECT SUM(saleprice)
FROM orders
WHERE custid = (
	SELECT custid
	FROM customer
	WHERE name = '박지성'
);

(4) 박지성이 구매한 도서의 수

SELECT COUNT(bookid)
FROM orders
WHERE custid = (
	SELECT custid
	FROM customer
	WHERE name = '박지성'
);

(5) 박지성이 구매한 도서의 출판사 수

SELECT COUNT(DISTINCT publisher)
FROM book
WHERE bookid IN (
	SELECT bookid
	FROM orders
	WHERE custid = (
		SELECT custid
		FROM customer
		WHERE name = '박지성'
	)
);

(6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이

..^^..

(7) 박지성이 구매하지 않은 도서의 이름

SELECT bookname
FROM book
WHERE bookid NOT IN (
	SELECT bookid
	FROM orders
	WHERE custid = (
		SELECT custid
		FROM customer
		WHERE name = '박지성'
	)
);

 

2. 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL문을 작성

(1) 마당서점 도서의 총 개수

SELECT COUNT(*)
FROM book;

(2) 마당서점에 도서를 출고하는 출판사의 총 개수

SELECT COUNT(DISTINCT publisher)
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 '2014-07-07';

(5) 2014년 7월 4일~7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호

SELECT orderid
FROM orders
WHERE orderid NOT IN (
	SELECT orderid
	FROM orders
	WHERE orderdate BETWEEN '2014-07-04' AND '2014-07-07'
);

(7) 성이 ‘김'씨인 고객의 이름과 주소

SELECT name, address 
FROM customer
WHERE name LIKE '김%';

(8) 성이 ‘김'씨이고 이름이 ‘아'로 끝나는 고객의 이름과 주소

SELECT name, address
FROM customer
WHERE name LIKE '김_아';

(9) 주문하지 않은 고객의 이름(서브쿼리 사용)

SELECT name
FROM customer
WHERE custid NOT IN (
	SELECT custid
	FROM orders
);

(10) 주문 금액의 총액과 주문의 평균 금액

SELECT SUM(saleprice), AVG(saleprice)
FROM orders;

(11) 고객의 이름과 고객별 구매액

SELECT customer.name, SUM(saleprice)
FROM customer
JOIN orders
	ON customer.custid = orders.custid
GROUP BY customer.name;

(12) 고객의 이름과 고객이 구매한 도서 목록

(13) 도서의 가격(book 테이블)과 판매가격(orders 테이블)의 차이가 가장 많은 주문

(14) 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름

 

 

3. 마당서점에서 다음의 심화된 질문에 대해 SQL문을 작성

(1) 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이

(2) 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름

(3) 전체 고객의 30% 이상이 구매한 도서

 

4. 다음 질의에 대해 DDL문과 DML문을 작성

(1) 새로운 도서 (’스포츠 세계’, ‘대한미디어', 10000원)이 마당서점에 입고되었다. 삽입이 안 될 경우 필요한 데이터가 더 있는지 찾아보시오.

 

bookid 정보 없음

 

(2) ‘삼성당'에서 출판한 도서를 삭제하시오.

DELETE FROM book
WHERE publisher = '삼성당';

 

(3) ‘이상미디어'에서 출판한 도서를 삭제하시오. 삭제가 안 되면 원인은?

DELETE FROM book
WHERE publisher = '이상미디어';
// 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
);

(6) (테이블 수정) bookcompany 테이블에 인터넷 주소를 저장하는 webaddress 속성을 VARCHAR(30)으로 추가하시오.

ALTER TABLE bookcompany ADD webaddress VARCHAR(30);

(7) bookcompany 테이블에 임의의 튜플 name = 한빛아카데미, address = 서울시 마포구, begin = 1993-01-01, webaddress = 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 NOT EXISTS (
	SELECT *
	FROM orders c2
	WHERE c1.custid = c2.custid
);

(1) 질의의 결과는 무엇인가?

 

(2) NOT을 지우면 질의의 결과는 무엇인가?