[연습문제] DML

INSERT

 /*
   연습문제
	INSERT 문을 사용하여
	학과 / 학생 테이블에 다음과 같이 데이터 입력
	SELECT 문으로 조회
*/


-- 학과 테이블 생성
    CREATE TABLE department(
        dptNo VARCHAR(10) NOT NULL PRIMARY KEY,
        dptName VARCHAR(30) NOT NULL,
        dptTel VARCHAR(13)
    );

  -- 학생 테이블 생성 
	CREATE TABLE student (
		stdNo VARCHAR(10) NOT NULL PRIMARY KEY,
		stdName VARCHAR(30) NOT NULL,
		stdYear INT DEFAULT 4 CHECK(stdYear >= 1 AND stdYear <= 4),
        stdAddress VARCHAR(50), 
		stdBirthDay DATE,
		dptNo VARCHAR(10) NOT NULL,
        CONSTRAINT FK_student_department FOREIGN KEY (dptNo) REFERENCES department (dptNo)
	);
   
   
    INSERT INTO department (dptNo, dptName, dptTel)
	VALUES  ('1', '컴퓨터학과', '02-1111-1111'),    
			('2', '경영학과', '02-2222-2222'),
			('3', '수학과', '02-7777-7777');
    
    SELECT * FROM department;  
    
    INSERT INTO student (stdNo, stdName, stdYear, stdAddress, stdBirthDay, dptNo) 
	VALUES  ('2018002', '이몽룡', 4, '서울시 강남구', '1998-05-07', '1'),  
			('2019003', '홍길동', 3, '경기도 안양시', '1999-11-11', '2'),
			('2021003', '성춘향', 1, '전라북도 남원시', '2002-01-02', '3'),
            ('2021004', '변학도', 1, '서울시 종로구', '2000-11-11', '2');  

    SELECT * FROM student;

 

DML - 1

/*
연습문제
1. book 테이블에 다음과 같이 행 삽입 
 (출판사 데이터는 테이블 구조에 맞게 입력) - 9번 10번으로 입력
2. book 테이블에서 도서명이 '자바'인 행의 가격을 22000으로 변경
3. book 테이블에서 발행일이 2018년도인 행 삭제  

*/


-- 1. book 테이블에 다음과 같이 행 삽입 (출판사 데이터는 테이블 구조에 맞게 입력)
INSERT INTO book (bookNo, bookName, bookPrice, bookDate, pubNo)
	VALUES  ('9', 'JAVA 웹프로그래밍', 30000, '2021-03-10', '1'),
			('10', '파이썬 데이터 과학', 24000, '2018-02-05', '2'); 

-- 2. book 테이블에서 도서명이 '자바'인 행의 가격을 22000으로 변경
UPDATE book SET bookPrice=22000 WHERE bookName='자바';

-- 3. book 테이블에서 발행일이 2018년도인 행 삭제
DELETE FROM book 
	WHERE bookDate >= '2018-01-01' AND bookDate <= '2018-12-31';

 

 

DML - 2

/*
	종합 연습문제
	다음과 같이 SQL 문 작성
	1. 고객 테이블 (customer) 생성 
	2. 고객 테이블의 전화번호 열을 NOT NULL로 변경
	3. 고객 테이블에 ‘성별’, ‘나이’ 열 추가
	4. 고객 테이블에 데이터 삽입 (3개)
	5. 고객명이 홍길동인 고객의 전화번호 값 수정 (값은 임의로)
	6. 나이가 20살 미만인 고객 삭제
*/


-- 1. 고객 테이블 (customer) 생성  
CREATE TABLE customer(
	custNo VARCHAR(10) NOT NULL PRIMARY KEY,
	custName VARCHAR(30),
	custPhone VARCHAR(13),
	custAddress VARCHAR(50)
);

-- 2. 고객 테이블의 전화번호 열을 NOT NULL로 변경
ALTER TABLE customer MODIFY custPhone VARCHAR(13) NOT NULL;

DESCRIBE customer;

-- 3. 고객 테이블에 ‘성별’, ‘나이’ 열 추가
ALTER TABLE customer ADD (custGender VARCHAR(1), custAge INT);

DESCRIBE customer;

-- 4. 고객 테이블에 데이터 삽입 (3개)
INSERT INTO customer (custNo, custName, custPhone, custAddress, custGender, custAge) 
VALUES  ('1001', '홍길동', '010-1111-1111', '강원도 평창', '남', 25),  
		('1002', '이몽룡', '010-2222-2222', '서울 종로구', '남', 15),
		('1003', '성춘향', '010-3333-3333', '서울시 강남구', '여', 27);

SELECT * FROM customer;  

-- 5. 고객명이 홍길동인 고객의 전화번호 값 수정 (값은 임의로)
UPDATE customer SET custPhone='010-1234-1234' WHERE custName='홍길동';

SELECT * FROM customer;

-- 6. 나이가 20살 미만인 고객 삭제
DELETE FROM customer WHERE custAge < 20;

SELECT * FROM customer;

 

 

SELECT - 1

-- 연습문제
-- 1. 고객 테이블에서 고객명, 생년월일, 성별 출력
SELECT clientName, clientBirth, clientGender FROM client;

-- 2. 고객 테이블에서 주소만 검색하여 출력 (중복되는 주소는 한번만 출력)
SELECT DISTINCT clientAddress FROM client;
SELECT clientAddress FROM client;

-- 3. 고객 테이블에서 취미가 '축구'이거나 '등산'인 고객의 고객명, 취미 출력
SELECT clientName, clientHobby 
FROM client
WHERE clientHobby = '축구' OR clientHobby = '등산';

-- 4. 도서 테이블에서 저자의 두 번째 위치에 '길'이 들어 있는 저자명 출력
-- (중복되는 저자명은 한번만 출력)
SELECT DISTINCT bookAuthor FROM book
WHERE bookAuthor LIKE '_길%';

-- 5. 도서 테이블에서 발행일이 2019년인 도서의 도서명, 저자, 발행일 출력
SELECT bookName, bookAuthor, bookDate FROM book 
WHERE bookDate LIKE '2019%';

-- 6. 도서판매 테이블에서 고객번호1, 2를 제외한 모든 판매 데이터 출력
SELECT* FROM bookSale
WHERE clientNo NOT IN('1', '2');

-- 7. 고객 테이블에서 취미가 NULL이 아니면서 
-- 주소가 '서울'인 고객의 고객명, 주소, 취미 출력
SELECT clientName, clientAddress, clientHobby FROM client
WHERE clientHobby IS NOT NULL AND clientAddress LIKE '%서울%';

SELECT clientName, clientAddress, clientHobby FROM client
WHERE clientAddress LIKE '%서울%';

-- '천안'으로 한 경우 결과 다르게 출력
SELECT clientName, clientAddress, clientHobby FROM client
WHERE clientHobby IS NOT NULL AND clientAddress LIKE '%천안%';

SELECT clientName, clientAddress, clientHobby FROM client
WHERE clientAddress LIKE '%천안%';

-- 8. 도서 테이블에서 가격이 25000원 이상이면서 
-- 저자 이름에 '길동'이 들어가는 도서의 도서명, 저자, 가격, 재고 출력
SELECT bookName, bookPrice, bookAuthor, bookStock FROM book 
WHERE bookPrice >= 25000 AND  bookAuthor LIKE '%길동';

-- 9. 도서 테이블에서 가격이 20,000 ~ 25,000원인 모든 도서 출력
SELECT * FROM book
WHERE bookPrice BETWEEN 20000 AND 25000;

-- 10. 도서 테이블에서 저자명에 '길동'이 들어 있지 않는 도서의 도서명, 저자 출력
SELECT bookName, bookAuthor FROM book
WHERE bookAuthor NOT LIKE '%길동%';

 

 

SELECT - 2

-- 1. 도서 테이블에서 가격 순으로 내림차순 정렬하여, 
-- 도서명, 저자, 가격 출력. 
-- (가격이 같으면 저자 순으로 오름차순 정렬)
SELECT bookName, bookAuthor, bookPrice
FROM book
ORDER BY bookPrice DESC, bookAuthor ASC;

-- 2. 도서 테이블에서 저자에 '길동'이 들어가는 도서의 
-- 총 재고 수량 계산하여 출력
SELECT SUM(bookStock) AS "총 재고 수량"
FROM book
WHERE bookAuthor LIKE '%길동%';

-- 3. 도서 테이블에서 '서울 출판사' 도서 중 최고가와 최저가 출력 
SELECT MAX(bookPrice) AS "최고가", MIN(bookPrice) AS "최저가"
FROM book
WHERE pubNo = '1';

-- 4. 도서 테이블에서 출판사별로 '총 재고 수량'과 '평균 재고 수량' 계산하여 출력
-- 총 재고 수량"으로 내림차순 정렬
SELECT pubNo, SUM(bookStock) AS 총재고수량,
			  AVG(bookStock) AS '평균 재고 수량'
FROM book 
GROUP BY pubNo
ORDER BY 총재고수량 DESC;

-- 주의!! : MySQL에서는 ORDER BY 다음의 열이름에 따옴표 있으면 안 됨
SELECT pubNo, SUM(bookStock) AS '총재고수량',
			  AVG(bookStock) AS '평균 재고 수량'
FROM book 
GROUP BY pubNo
ORDER BY '총재고수량' DESC; -- 따옴표 있으면 정렬 안 됨

-- 열이름 대신 숫자 사용
SELECT pubNo, SUM(bookStock) AS '총재고수량',
			  AVG(bookStock) AS '평균 재고 수량'
FROM book 
GROUP BY pubNo
ORDER BY 2 DESC;

-- 5. 도서판매 테이블에서 고객별로(그룹화:기본키(외래키)) 
-- '총 주문 수량'과 '총 주문 건수' 출력. 단 주문 건수가 2이상인 고객만 해당.
SELECT clientNo, COUNT(*) AS "총 주문 건수", SUM(bsQty) AS "총 주문 수량"
FROM bookSale
GROUP BY clientNo
HAVING COUNT(*) >= 2;

 

 

JOIN

-- 연습문제

-- 1. 모든 도서에 대하여 도서의 도서번호, 도서명, 출판사명 출력
SELECT B.bookNo, B.bookName, b.bookauthor, P.pubName
FROM book B 
	INNER JOIN publisher P ON  B.pubNo = P.pubNo ;


-- 2. '서울 출판사'에서 출간한 도서의 도서명, 저자명, 출판사명 출력 
-- (조건에 출판사명 사용)
SELECT B.bookName, B.bookAuthor, P.pubName
FROM book B
	INNER JOIN publisher P ON  B.pubNo = P.pubNo
WHERE P.pubName = '서울 출판사';


-- 3. '종로출판사'에서 출간한 도서 중 판매된 도서의 도서명 출력
-- 중복된 경우 한 번만 출력 (조건에 출판사명 사용)
SELECT DISTINCT B.bookName, P.pubName
FROM book B
	INNER JOIN publisher P ON  B.pubNo = P.pubNo
	INNER JOIN bookSale BS ON  B.bookNo = BS.bookNo
WHERE  P.pubName = '종로출판사';


-- 4. 도서가격이 30,000원 이상인 도서를 주문한 고객의 
-- 고객명, 도서명, 도서가격, 주문수량 출력
SELECT C.clientName,  B.bookName, B.bookPrice, BS.bsQty
FROM bookSale BS 
	INNER JOIN client C ON C.clientNo = BS.clientNo
	INNER JOIN book B ON B.bookNo = BS.bookNo
WHERE B.bookPrice >= 30000;		


-- 5. '안드로이드 프로그래밍' 도서를 구매한 고객에 대하여
-- 도서명, 고객명, 성별, 주소 출력 (고객명으로 오름차순 정렬)
SELECT b.bookName, c.clientName, c.clientGender, c.clientAddress
FROM bookSale BS 
	INNER JOIN client C ON C.clientNo = BS.clientNo
	INNER JOIN book B ON B.bookNo = BS.bookNo
WHERE bookName = '안드로이드 프로그래밍'
ORDER BY clientName;


SELECT * FROM bookSale;
SELECT * FROM client;
SELECT * FROM book;

-- 6. '도서출판 강남'에서 출간된 도서 중 판매된 도서에 대하여
-- '총 매출액' 출력
SELECT SUM(B.bookPrice * BS.bsQty) AS "총 매출액"
FROM book B
	INNER JOIN bookSale BS ON B.bookNo = BS.bookNo        
	INNER JOIN publisher P ON B.pubNo = P.pubNo
WHERE P.pubName = '도서출판 강남';     

-- 집계 함수 앞에 열을 출력할 경우
SELECT P.pubName, SUM(B.bookPrice * BS.bsQty) AS "총 매출액"
FROM book B
	INNER JOIN bookSale BS ON B.bookNo = BS.bookNo        
	INNER JOIN publisher P ON B.pubNo = P.pubNo
WHERE P.pubName = '도서출판 강남'
GROUP BY P.pubNo;

-- 각 매출 건수 출력
SELECT P.pubName, B.bookPrice * BS.bsQty AS "매출액"
FROM book B
	INNER JOIN bookSale BS ON B.bookNo = BS.bookNo        
	INNER JOIN publisher P ON B.pubNo = P.pubNo
WHERE P.pubName = '도서출판 강남';        



-- 7. '서울 출판사'에서 출간된 도서에 대하여
-- 판매일, 출판사명, 도서명, 도서가격, 주문수량, 주문액 출력
SELECT BS.bsDate, P.pubName, B.bookName, B.bookPrice, 
		   BS.bsQty, B.bookPrice * BS.bsQty AS "판매액"
FROM book B
	INNER JOIN bookSale BS ON B.bookNo = BS.bookNo        
	INNER JOIN publisher P ON B.pubNo = P.pubNo
WHERE P.pubName = '서울 출판사';


-- 8. 판매된 도서에 대하여 
-- 도서별로 도서번호, 도서명, '총 주문 수량' 출력
SELECT B.bookNo, B.bookName, SUM(BS.bsQty) AS "총 주문 수량"
FROM book B
	INNER JOIN bookSale BS ON B.bookNo = BS.bookNo       		
GROUP BY B.bookNo;

SELECT * FROM bookSale;


-- 9. 판매된 도서에 대하여 
-- 고객별로 고객명, 총구매액 출력
-- 총구매액이 100,000원 이상인 경우만 해당   
SELECT C.clientName, SUM(B.bookPrice * BS.bsQty) AS "총구매액"
FROM bookSale BS 
	INNER JOIN client C ON C.clientNo = BS.clientNo
	INNER JOIN book B ON B.bookNo = BS.bookNo
GROUP BY C.clientNo
HAVING SUM(B.bookPrice * BS.bsQty) >= 100000;

-- HAVING 조건 없는 경우 고객별 총구매 내역 
SELECT C.clientName, SUM(B.bookPrice * BS.bsQty) AS "총구매액"
FROM bookSale BS 
	INNER JOIN client C ON C.clientNo = BS.clientNo
	INNER JOIN book B ON B.bookNo = BS.bookNo
GROUP BY C.clientNo;

SELECT * FROM bookSale;

-- 10. 판매된 도서 중 '도서출판 강남'에서 출간한 도서에 대하여
-- 고객명, 주문일, 도서명, 주문수량, 출판사명 출력
-- 고객명으로 오름차순 정렬
-- 4개 테이블 : 외래키 제약조건 3개
SELECT C.clientName, BS.bsDate, B.bookName, BS.bsQty, P.pubName    
FROM bookSale BS 
	INNER JOIN client C ON C.clientNo = BS.clientNo
	INNER JOIN book B  ON B.bookNo = BS.bookNo       
	INNER JOIN publisher P ON B.pubNo = P.pubNo
WHERE P.pubName = '도서출판 강남'
ORDER BY C.clientName;