DAY.06

-- 질의 3-33. 주문이 있는 고객의 이름과 주소를 나타내시오. 
-- 주문 정보는 orders 테이블에 존재
-- 고객 정보는 customer 테이블에 존재

SELECT *
FROM orders;

SELECT NAME, address
  FROM customer
 WHERE custid IN ( SELECT custid FROM orders );
 
SELECT DISTINCT C.name, C.address
  FROM customer C
  JOIN orders O
    ON O.custid = C.custid; 
    
SELECT C.name, C.address
  FROM customer C
  JOIN orders O
    ON O.custid = C.custid
 GROUP BY C.name, C.address;
 
 
 SELECT C.name, C.address
   FROM customer C
  WHERE EXISTS ( SELECT 1 FROM orders O 
                  WHERE o.custid = C.custid );

                                
-- p.182 CREATE 문
DROP TABLE newbook;

CREATE TABLE newbook(
	  bookid INT PRIMARY KEY -- PK
	, bookname VARCHAR(20) NOT NULL -- NULL 비허용
	, publisher VARCHAR(20) UNIQUE -- 중복 비허용
	, price INTEGER DEFAULT '10000' NOT NULL  -- 값을 넣지 않으면 기본값 10,000원 입력
	                CHECK(price >= 1000) -- 값을 넣을 때 1,000원 미만 비허용
);

INSERT INTO newbook
( bookid, bookname, publisher, price )
VALUES
( 1, '책제목1', '출판사1', 900 ); -- (CHECK 위배) 책 가격이 1,000원 이상이 아니다. 

INSERT INTO newbook
( bookid, bookname, publisher, price )
VALUES
( 1, '책제목1', '출판사1', 1000 ); -- 삽입 성공

INSERT INTO newbook
( bookid, bookname, publisher, price )
VALUES
( 1, '책제목2', '출판사2', 2000 ); -- (UNIQUE 위배) 중복 PK 


INSERT INTO newbook
( bookid, bookname, publisher, price )
VALUES
( 2, '책제목2', '출판사1', 2000 ); -- (UNIQUE 위배) 중복 출판사명

INSERT INTO newbook
( bookid, publisher, price )
VALUES
( 2, '출판사2', 2000 ); -- bookname not null 때문에 에러 발생

INSERT INTO newbook
( bookid, bookname, publisher, price )
VALUES
( 2, '책제목2', '출판사2', 2000 ); -- 삽입 성공

INSERT INTO newbook
   SET bookid = 3
     , bookname = '책제목3'
     , publisher = '출판사3'; 

INSERT INTO newbook
( bookid, bookname, publisher )
VALUES
  ( 4, '책제목4', '출판사4' )
, ( 5, '책제목5', '출판사5' )
, ( 6, '책제목6', '출판사6' )
;




DROP TABLE newbook2;    
CREATE TABLE newbook2(
	  bookid INT
	, bookname VARCHAR(20)
	, publisher VARCHAR(20)
	, price INTEGER
	, PRIMARY KEY (bookid)
);                

INSERT INTO newbook2
SET bookid = 1000
, bookname = '테스트1'
, publisher = '출판사1'
, price = 10000;            

                
INSERT INTO newbook2
SET bookid = 1002
, bookname = 'ABCDEFGHIJABCDEFGHIJK'
, publisher = '출판사1'
, price = 10000;   
                

/* 질의 3-35. 다음과 같은 속성을 가진 newcustomer 테이블을 생성하시오.
custid - 정수
name - 문자열(40)
address - 문자열(40)
phone - 문자열(30)
PK (custid)
*/
DROP TABLE newcustomer;

CREATE TABLE newcustomer (
	  custid INT PRIMARY KEY   
	, name VARCHAR(40)
	, address VARCHAR(40)
	, phone VARCHAR(30)
);


-- 고객번호: 1, 이름: 홍길동1, 주소: 대구시 중구, 폰번호: 010-2222-2222
INSERT INTO newcustomer
SET custid = 1
, NAME = '홍길동1'
, address = '대구시 중구'
, phone = '010-2222-2222';

/* 질의 3-36. 다음과 같은 속성을 가진 neworders 테이블을 생성하시오.
orderid - int, primary key
custid - int, not null
bookid - int, not null
saleprice - int
orderdate - date

FK (Foreign Key) - 무결성, 테이블 간 관계 연결, 
*/
DROP TABLE neworders; 

CREATE TABLE neworders (
     orderid INT PRIMARY KEY 
	, custid INT NOT NULL 
	, bookid INT NOT NULL 
	, saleprice INT 
	, orderdate DATE 
	, FOREIGN KEY ( custid ) REFERENCES newcustomer( custid )
	, FOREIGN KEY ( bookid ) REFERENCES newbook( bookid )
);

INSERT INTO neworders
   SET orderid = 1
     , custid = 1
     , bookid = 1
     , saleprice = 10000
     , orderdate = '2025-04-21';


 
-- 질의 3-37. newbook 테이블에 varchar(13) 자료형을 가진 isbn 속성을 추가하시오.
ALTER TABLE newbook
  ADD isbn VARCHAR(13);
  
 ALTER TABLE newbook
MODIFY isbn INT;  

ALTER TABLE newbook
 DROP isbn;

ALTER TABLE newbook
MODIFY publisher VARCHAR(20) NOT NULL;        

ALTER TABLE newbook
DROP PRIMARY KEY;

ALTER TABLE newbook
ADD PRIMARY KEY (bookid);


ALTER TABLE neworders
DROP FOREIGN KEY neworders_ibfk_1;

ALTER TABLE neworders
DROP FOREIGN KEY neworders_ibfk_2;

ALTER TABLE neworders
ADD CONSTRAINT fk_neworders_bookid 
    FOREIGN KEY (bookid) REFERENCES newbook(bookid);
    
ALTER TABLE neworders
ADD FOREIGN KEY (custid) REFERENCES newcustomer(custid);

DROP TABLE neworders, newbook, newcustomer;