3.2.2 GROUP BY_어느 고객이 얼마나 주문했는지 알고 싶다

Presentation on theme: "3.2.2 GROUP BY_어느 고객이 얼마나 주문했는지 알고 싶다"— Presentation transcript:

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

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

3 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 /* 순서가 틀렸다 */

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

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

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

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

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

9 3.3.1 조인_2개의 테이블을 합체해보자 질의 3-25 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.
질의 고객의 이름과 고객이 주문한 도서의 이름을 구하시오. SELECT Customer.name, book.bookname FROM Customer, Orders, Book WHERE Customer.custid =Orders.custid AND Orders.bookid =Book.bookid; 질의 가격이 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;

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

11 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을 이용하여 조인조건을 명시한다.

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

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

14 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='대한미디어') 그림 단계 부속질의의 실행 순서

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

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

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

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

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

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

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

22 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]] )

23 4.1 CREATE 문 질의 다음과 같은 속성을 가진 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); =

24 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 CHECK(price > 1000), PRIMARY KEY (bookname, publisher));

25 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));

26 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);

27 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

28 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] 제약이름]

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

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

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

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

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

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

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

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

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