3.2.2 GROUP BY_어느 고객이 얼마나 주문했는지 알고 싶다 질의 3-19 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오. SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액 FROM Orders GROUP BY custid; 그림 3-15 GROUP BY 절의 수행

3.2.2 GROUP BY_어느 고객이 얼마나 주문했는지 알고 싶다 질의 3-20 가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구하시오. 단, 두 권 이상 구매한 고객만 구한다. SELECT custid, COUNT(*) AS 도서수량 FROM Orders WHERE saleprice >= 8000 GROUP BY custid HAVING count(*) >= 2;

3.2.2 GROUP BY_어느 고객이 얼마나 주문했는지 알고 싶다 표 3-5 GROUP BY와 HAVING 절의 문법과 주의사항 문법 주의사항 GROUP BY <속성> GROUP BY로 투플을 그룹으로 묶은 후 SELECT 절에는 GROUP BY에서 사용한 <속성>과 집 계함수만 나올 수 있다. 맞는 예 SELECT custid, SUM(saleprice) FROM Orders GROUP BY custid; 틀린 예 SELECT bookid, SUM(saleprice) /* SELECT 절에 bookid 속성이 올 수 없다 */ HAVING <검색조건> WHERE 절과 HAVING 절이 같이 포함된 SQL 문은 검색조건이 모호해질 수 있다. HAVING 절은 ① 반드시 GROUP BY 절과 같이 작성해야 하고 ② WHERE 절보다 뒤에 나와야 한다. 그리고 ③ <검색조건>에는 SUM, AVG, MAX, MIN, COUNT와 같은 집계함수가 와야 한다. SELECT custid, COUNT(*) AS 도서수량 WHERE saleprice ＞= 8000 GROUP BY custid HAVING count(*) ＞= 2; HAVING count(*) ＞= 2 /* 순서가 틀렸다 */

3.3.1 조인_2개의 테이블을 합체해보자 Customer 테이블과 Orders 테이블의 카티전 프로덕트 SELECT * FROM Customer, Orders; 중략 그림 3-16 Customer와 Orders 테이블의 합체

3.3.1 조인_2개의 테이블을 합체해보자 질의 3-21 고객과 고객의 주문에 관한 데이터를 모두 보이시오. SELECT * 질의 3-21 고객과 고객의 주문에 관한 데이터를 모두 보이시오. SELECT * FROM Customer, Orders WHERE Customer.custid =Orders.custid;

3.3.1 조인_2개의 테이블을 합체해보자 질의 3-22 고객과 고객의 주문에 관한 데이터를 고객별로 정렬하여 보이시오. 질의 3-22 고객과 고객의 주문에 관한 데이터를 고객별로 정렬하여 보이시오. SELECT * FROM Customer, Orders WHERE Customer.custid =Orders.custid ORDER BY Customer.custid;

3.3.1 조인_2개의 테이블을 합체해보자 질의 3-23 고객의 이름과 고객이 주문한 도서의 가격을 검색하시오. 질의 3-23 고객의 이름과 고객이 주문한 도서의 가격을 검색하시오. SELECT name, saleprice FROM Customer, Orders WHERE Customer.custid =Orders.custid; 질의 3-24 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오. SELECT name, SUM(saleprice) FROM Customer, Orders WHERE Customer.custid =Orders.custid GROUP BY Customer.name ORDER BY Customer.name;

고객의 이름과 구매한 고객이 주문한 도서의 이름을 구하여라. 3.3.1 조인_2개의 테이블을 합체해보자 고객의 이름과 구매한 고객이 주문한 도서의 이름을 구하여라. 그림 3-17 마당서점 데이터 간의 연결

3.3.1 조인_2개의 테이블을 합체해보자 질의 3-25 고객의 이름과 고객이 주문한 도서의 이름을 구하시오. 질의 3-25 고객의 이름과 고객이 주문한 도서의 이름을 구하시오. SELECT Customer.name, book.bookname FROM Customer, Orders, Book WHERE Customer.custid =Orders.custid AND Orders.bookid =Book.bookid; 질의 3-26 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오. SELECT Customer.name, book.bookname FROM Customer, Orders, Book WHERE Customer.custid =Orders.custid AND Orders.bookid =Book.bookid AND Orders.saleprice =20000;

3.3.1 조인_2개의 테이블을 합체해보자 외부조인 질의 3-27 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 가격을 구하시오. SELECT Customer.name, saleprice FROM Customer LEFT OUTER JOIN Orders ON Customer.custid =Orders.custid;

3.3.1 조인_2개의 테이블을 합체해보자 명령 문법 설명 일반적인 조인 SELECT <속성들> 표 3-6 조인 문법 명령 문법 설명 일반적인 조인 SELECT <속성들> FROM 테이블1, 테이블2 WHERE <조인조건> AND <검색조건> SQL 문에서는 주로 동등조인을 사용한다. 두 가지 문법 중 하나를 사용할 수 있다. FROM 테이블1 INNER JOIN 테이블2 ON <조인조건> WHERE <검색조건> 외부조인 FROM 테이블1 {LEFT |RIGHT |FULL [OUTER]} JOIN 테이블2 ON <조인조건> 외부조인은 FROM 절에 조인 종류를 적 고 ON을 이용하여 조인조건을 명시한다.

3.3.2 부속질의_SQL 문 내에 또 다른 SQL 문을 작성해보자 질의 3-28 가장 비싼 도서의 이름을 보이시오. SELECT bookname FROM Book WHERE price = ( SELECT MAX(price) FROM Book; 가장 비싼 도서의 가격은 → 35,000원 그림 3-18 부속질의의 실행 순서

3.3.2 부속질의_SQL 문 내에 또 다른 SQL 문을 작성해보자 질의 3-29 도서를 구매한 적이 있는 고객의 이름을 검색하시오. SELECT name FROM Customer WHERE custid IN (SELECT custid FROM Orders); 질의 3-30 대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오. SELECT name FROM Customer WHERE custid IN (SELECT custid FROM Orders WHERE bookid IN (SELECT bookid FROM Book WHERE publisher='대한미디어'));

3.3.2 부속질의_SQL 문 내에 또 다른 SQL 문을 작성해보자 ③ SELECT name FROM Customer WHERE custid IN ② (SELECT custid FROM Orders WHERE bookid IN ① (SELECT bookid FROM Book WHERE publisher='대한미디어') 그림 3-19 3단계 부속질의의 실행 순서

3.3.2 부속질의_SQL 문 내에 또 다른 SQL 문을 작성해보자 ‘대한미디어’에서 출판한 도서를 구매한 고객의 이름을 보이시오. ① ③ ② 그림 3-20 3단계 부속질의의 실행 순서와 데이터 예

3.3.2 부속질의_SQL 문 내에 또 다른 SQL 문을 작성해보자 상관 부속질의(correlated subquery) 상위 부속질의의 투플을 이용하여 하위 부속질의를 계산 상위 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련을 맺고 있음 질의 3-31 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오. SELECT b1.bookname FROM Book b1 WHERE b1.price > (SELECT avg(b2.price) FROM Book b2 WHERE b2.publisher=b1.publisher);

3.3.2 부속질의_SQL 문 내에 또 다른 SQL 문을 작성해보자 테이블 Book – b1으로 나타냄 테이블 Book – b2로 나타냄 B1 테이블의 튜플 t에 해당되는 출판사를 b2 테이블로 가져가서 해당되는 출판사 튜플들의 price 값의 평균을 구한다. 그림 3-21 상관 부속질의의 데이터 예

3.3.3 집합연산_도서를 주문하지 않은 고객을 알고 싶다 질의 3-32 도서를 주문하지 않은 고객의 이름을 보이시오. SELECT name FROM Customer EXCEPT WHERE custid IN (SELECT custid FROM Orders);

3.3.4 EXISTS_주문이 있는 고객을 알고 싶다 조건에 맞는 튜플이 존재하면 결과에 포함시킴 부속질의문의 어떤 행이 조건에 만족하면 참 NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참 질의 3-33 주문이 있는 고객의 이름과 주소를 보이시오. SELECT name, address FROM Customer cs WHERE EXISTS (SELECT * FROM Orders od WHERE cs.custid =od.custid);

3.3.4 EXISTS_주문이 있는 고객을 알고 싶다 Customer Orders ① ②` ③ ④ ⑤

04. 데이터 정의어 CREATE 문 ALTER 문 DROP 문

4.1 CREATE 문 테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본키 및 외래키를 정의하는 명령 PRIMARY KEY : 기본키 정의 FOREIGN KEY : 외래키 지정 ON UPDATE, ON DELETE : 외래키 속성의 수정과 투플 삭제 시 동작을 의미 CREATE 문의 기본 문법 CREATE TABLE 테이블이름 ( { 속성이름 데이터타입 [NOT NULL] [UNIQUE] [DEFAULT 기본값] [CHECK 체크조건] } [PRIMARY KEY 속성이름(들)] {[FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)] [ON UPDATE [NO ACTION┃CASCADE┃SET NULL┃SET DEFAULT]] [ON DELETE [NO ACTION┃CASCADE┃SET NULL┃SET DEFAULT]] )

4.1 CREATE 문 질의 3-34 다음과 같은 속성을 가진 NewBook 테이블을 생성하시오, 정수형은 INT를 사용하며 문자형은 가변형 문자타입인 VARCHAR을 사용한다. bookid(도서번호) - INT bookname(도서이름) - VARCHAR(20) publisher(출판사) - VARCHAR(20) price(가격) - INT CREATE TABLE NewBook ( bookid INT, Bookname VARCHAR(20), publisher VARCHAR(20), price INT);  기본키를 지정할 경우 CREATE TABLE NewBook ( bookid INT, Bookname VARCHAR(20), publisher VARCHAR(20), price INT PRIMARY KEY (bookid)); CREATE TABLE NewBook ( bookid INT PRIMARY KEY, bookname VARCHAR(20), publisher VARCHAR(20), price INT); =

4.1 CREATE 문  bookname, publisher가 기본키일 경우 CREATE TABLE NewBook ( bookname VARCHAR(20), publisher VARCHAR(20), price INT PRIMARY KEY (bookname, publisher)); bookname은 NULL 값을 가질 수 없고, publisher는 같은 값이 있으면 안 된다. price에 값이 입력되지 않을 경우 기본 값 10000을 저장한다. 또 가격은 최소 1,000원 이상으로 한다. CREATE TABLE NewBook ( bookname VARCHAR(20) NOT NULL, publisher VARCHAR(20) UNIQUE, price INT DEFAULT 10000 CHECK(price > 1000), PRIMARY KEY (bookname, publisher));

4.1 CREATE 문 질의 3-35 다음과 같은 속성을 가진 NewCustomer 테이블을 생성하시오. custid(고객번호) - INT, 기본키 name(이름) - VARCHAR(40) address(주소) - VARCHAR(40) phone(전화번호) - VARCHAR(30) CREATE TABLE NewCustomer ( custid INT PRIMARY KEY, name VARCHAR(40), address VARCHAR(40), phone VARCHAR(30));

4.1 CREATE 문 질의 3-36 다음과 같은 속성을 가진 NewOrders 테이블을 생성하시오. orderid(주문번호) - INT, 기본키 custid(고객번호) - INT, NOT NULL 제약조건, 외래키(NewCustomer.custid, 연쇄삭제) bookid(도서번호) - INT, NOT NULL 제약조건 saleprice(판매가격) - INT orderdate(판매일자) - DATE CREATE TABLE NewOrders ( orderid INT, custid INT NOT NULL, bookid INT NOT NULL, saleprice INT, orderdate DATE, PRIMARY KEY (orderid), FOREIGN KEY (custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE);

4.1 CREATE 문 데이터 타입 설명 비슷한 타입 INT 정수형, 크기는 ± 2 BIGINT, SMALLINT 표 3-7 속성의 데이터 타입 종류 데이터 타입 설명 비슷한 타입 INT 정수형, 크기는 ± 2 BIGINT, SMALLINT NUMERIC(p, s) 실수형 p자리 정수, s자리 소수 DECIMAL(p, s) CHAR(n) 문자형 고정길이 VARCHAR(n) 문자형 가변길이 DATE 날짜형, 기본형은 YYYY-MM-DD DATETIME, TIME

4.2 ALTER 문 ALTER 문의 기본 문법 생성된 테이블의 속성과 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경 ADD, DROP : 속성을 추가하거나 제거 DEFAULT : 속성의 기본값을 설정하거나 삭제할 때 사용 ADD <제약이름>, DROP <제약이름> : 제약사항을 추가하거나 삭제할 때 사용 ALTER 문의 기본 문법 ALTER TABLE 테이블이름 [ADD 속성이름 데이터타입] [DROP COLUMN 속성이름] [ALTER COLUMN 속성이름 데이터타입] [ALTER COLUMN 속성이름 [NULL┃NOT NULL]] [ADD PRIMARY KEY(속성이름)] [[ADD┃DROP] 제약이름]

4.2 ALTER 문 질의 3-37 NewBook 테이블에 VARCHAR(13)의 자료형을 가진 isbn 속성을 추가하시오. ALTER TABLE NewBook ADD isbn VARCHAR(13); 질의 3-38 NewBook 테이블의 isbn 속성의 데이터 타입을 INT형으로 변경하시오. ALTER TABLE NewBook ALTER COLUMN isbn INT; 질의 3-39 NewBook 테이블의 isbn 속성을 삭제하시오. ALTER TABLE NewBook DROP COLUMN isbn; 질의 3-40 NewBook 테이블의 bookid 속성에 NOT NULL 제약조건을 적용하시오. ALTER TABLE NewBook ALTER COLUMN bookid INT NOT NULL; 질의 3-41 NewBook 테이블의 bookid 속성을 기본키로 변경하시오. ALTER TABLE NewBook ADD PRIMARY KEY(bookid);

4.3 DROP 문 DROP문의 기본 문법 테이블을 삭제하는 명령 테이블의 구조와 데이터를 모두 삭제하므로 사용 데이터만 삭제하려면 DELETE 문을 사용 DROP문의 기본 문법 DROP TABLE 테이블이름 질의 3-42 NewBook 테이블의 bookid 속성을 기본키로 변경하시오. DROP TABLE NewBook; 질의 3-43 NewCustomer 테이블을 삭제하시오. 만약 삭제가 거절된다면 원인을 파악하고 관련된 테이블을 같이 삭제하시오. DROP TABLE NewCustomer;

05. 데이터 조작어 – 삽입, 수정, 삭제 INSERT 문 UPDATE 문 DELETE 문

5.1 INSERT 문 INSERT 문의 기본 문법 테이블에 새로운 튜플을 삽입하는 명령 INSERT INTO 테이블이름[(속성리스트)] VALUES (값리스트); 질의 3-44 Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하시오. 스포츠 의학은 한솔의학서적에서 출간했으며 가격은 90,000원이다. INSERT INTO Book(bookid, bookname, publisher, price) VALUES (11, '스포츠 의학', '한솔의학서적', 90000);

5.1 INSERT 문 질의 3-45 Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하시오. 스포츠 의학은 한솔의학서적에서 출간했으며 가격은 미정이다. INSERT INTO Book(bookid, bookname, publisher) VALUES (12, '스포츠 의학', '한솔의학서적');

5.1 INSERT 문 대량 삽입(bulk insert) 질의 3-46 수입도서 목록(Imported_book)을 Book 테이블에 모두 삽입하시오. INSERT INTO Book(bookid, bookname, price, publisher) SELECT bookid, bookname, price, publisher FROM Imported_book;

5.2 UPDATE 문 UPDATE 문의 기본 문법 특정 속성 값을 수정하는 명령 UPDATE 테이블이름 SET 속성이름1=값1[, 속성이름2=값2, ...] [WHERE <검색조건>];

5.2 UPDATE 문 질의 3-47 Customer 테이블에서 고객번호가 5인 고객의 주소를 ‘대한민국 부산’으로 변경하시오. UPDATE Customer SET address='대한민국 부산' WHERE custid=5; 질의 3-48 Customer 테이블에서 박세리 고객의 주소를 김연아 고객의 주소로 변경하시오. UPDATE Customer SET address = (SELECT address FROM Customer WHERE name='김연아') WHERE name='박세리';

5.3 DELETE 문 DELETE 문의 기본 문법 테이블에 있는 기존 튜플을 삭제하는 명령 FROM 테이블이름 [WHERE 검색조건]; 질의 3-49 Customer 테이블에서 고객번호가 5인 고객을 삭제하시오. DELETE FROM Customer WHERE custid=5; 질의 3-50 모든 고객을 삭제하시오. DELETE FROM Customer;