SQL 학습을 위한 준비 SQL 개요 데이터 조작어 - 검색 데이터 정의어 데이터 조작어 - 삽입, 수정, 삭제.

Presentation on theme: "SQL 학습을 위한 준비 SQL 개요 데이터 조작어 - 검색 데이터 정의어 데이터 조작어 - 삽입, 수정, 삭제."— Presentation transcript:

1

2 SQL 학습을 위한 준비 SQL 개요 데이터 조작어 - 검색 데이터 정의어 데이터 조작어 - 삽입, 수정, 삭제

3 SQL의 개념과 주요 명령어를 알아본다. SELECT 문을 이용하여 질의를 처리하는 방법을 알아본다. 집계 함수와 GROUP BY 문을 이용하여 질의를 처리하는 방법을 알아본다. 두 개 이상의 테이블을 조회하여 질의를 처리하는 방법을 알아본다. DDL로 테이블의 구조를 정의하고 변경하는 방법을 알아본다. DML로 데이터를 삽입, 수정, 삭제하는 방법을 알아본다.

4 01. SQL 학습을 위한 준비 마당서점의 데이터 누가 어떤 정보를 원하는가? 오라클과 샘플 데이터 설치

5 01. SQL 학습을 위한 준비 그림 3-1 마당서점 현황

6 01. SQL 학습을 위한 준비 그림 3-2 마당서점 운영 시스템 환경

7 1.1 마당서점의 데이터 Book 테이블 Orders 테이블 Customer 테이블 bookid bookname
publisher price 1 축구의 역사 굿스포츠 7000 2 축구아는 여자 나무수 13000 3 축구의 이해 대한미디어 22000 4 골프 바이블 35000 5 피겨 교본 8000 6 역도 단계별기술 6000 7 야구의 추억 이상미디어 20000 8 야구를 부탁해 9 올림픽 이야기 삼성당 7500 10 Olympic Champions Pearson orderid custid bookid saleprice orderdate 1 6000 2 3 21000 5 8000 4 6 7 20000 12000 8 13000 10 9 7000 Book 테이블 Orders 테이블 custid name address phone 1 박지성 영국 맨체스타 2 김연아 대한민국 서울 3 장미란 대한민국 강원도 4 추신수 미국 클리블랜드 5 박세리 대한민국 대전 NULL Customer 테이블

8 1.1 마당서점의 데이터 Book(bookid, bookname, publisher, price)
Orders(orderid, custid, bookid, saleprice, orderdate) Customer(custid, name, address, phone) 그림 3-6 마당서점의 데이터 구성도

9 1.2 누가 어떤 정보를 원하는가? 그림 3-7 사용자 그룹별로 원하는 정보

10 1.3 오라클과 샘플 데이터 설치 오라클 11g r2 설치(부록 A 참조)
오라클 DBMS를 내려받아 설치함(C:\app\madang\product\11.2.0\dbhome_1 폴더) 시스템 관리자 계정 : system, 비밀번호 : Manager1 (비밀번호에 대문자와 숫자가 필요) [시작]-[모든 프로그램]-[Oracle-OraDB11g_home1]-[응용 프로그램 개발] 메뉴에 포함된 SQL 프로그램 사용 가능 윈도우 버전 오라클 버전 윈도우 7, 윈도우 XP (32bit) (64bit) 11g XE (Express Edition) O (released 2014 June) 11g r2 standard r2(release 2) 12c r1 standard r1(release 1) X  O <여기서 잠깐> 오라클 11g 설치 소요시간 오라클 11g 버전은 설치 파일이 용량이 2G가 넘기 때문에 다운로드부터 설치까지 많은 시간이 소요된다. 다운로드 속도가 5M/초인 시스템에서 10분정도 소요된다. 다운로드 속도가 느리다면 30분 이상 소요될 수도 있다. 또 설치 시간도 30분 정도 소요될 수 있다.

11 1.3 오라클과 샘플 데이터 설치 샘플 데이터베이스 설치(부록 B.3~B4 참조)
madang 사용자 계정 및 샘플 데이터베이스 설치 : B.3 (스크립트를 실행한다 - demo_madang.sql) scott 사용자 계정 사용(해제) : B.4 오라클의 기본 계정으로 이미 설치되어 있다. 예제로 Emp와 Dept 테이블 포함 계정 사용(해제) 명령 : ALTER USER scott ACCOUNT UNLOCK SQL Developer 설치(부록 B.1 참조) 오라클 홈페이지에서 본인의 환경에 맞는 버전 다운로드하여 설치

12 1.3.1 SQL Plus SQL Plus 시작  쿼리창 열기   

13 1.3.1 SQL Plus SQL 문을 작성할 때 주로 사용하는 명령어.
<Tip> SQL Plus에서 사용하는 명령어에 관한 자세한 설명은 다음의 링크를 참고한다. </Tip> -데이터베이스 접속 : conn [예] conn scott/tiger : scott 계정에 비밀번호 tiger로 접속한다. - 명령어 실행 : run, / [예] run : 바로 전에 실행했던 명령어를 다시 실행한다. [예] / : run과 같은 의미다. - 명령어 찾기 : list [예] list : 마지막에 수행했던 명령어를 출력한다. 직전 명령줄이 길 경우 편리하다. - 메모장을 이용하여 명령어 작성 및 실행하기 : ed <파일이름>, run <파일이름> [예] ed test : test.sql 이름의 파일이 메모장을 이용하여 작성할 수 있도록 열린다. [예] start test : test.sql 이름에 저장된 명령어 스크립트가 실행된다. test : start test와 같은 의미다. - 출력 모양을 조절하는 명령 : column [예] column bookname format a20 : bookname을 길이 20의 문자 포맷으로 출력한다. [예] column price format : price를 길이 6개의 숫자 포맷으로 출력한다.

14 1.3.2 SQL Developer 그림 3-9 SQL Developer에서 SQL 문을 실행한 화면 메뉴와 도구바
질의 작성기 네비게이터 출력 화면 그림 3-9 SQL Developer에서 SQL 문을 실행한 화면

15 1.3.2 SQL Developer 그림 SQL Developer 접속 아이콘 생성 화면

16 02. SQL 개요 그림 3-11 SQL을 사용해 자료를 찾는 과정

17 02. SQL 개요 표 3-1 SQL과 일반 프로그래밍 언어의 차이점 구분 SQL 일반 프로그래밍 언어 용도
데이터베이스에서 데이터를 추출하여 문제 해결 모든 문제 해결 입출력 입력은 테이블, 출력도 테이블 모든 형태의 입출력 가능 번역 DBMS 컴파일러 사용 예 SELECT * FROM Book; int main() {…}

18 02. SQL 개요 SQL 기능에 따른 분류 데이터 정의어(DDL) : 테이블이나 관계의 구조를 생성하는 데 사용하며 CREATE, ALTER, DROP 문 등이 있음. 데이터 조작어(DML) : 테이블에 데이터를 검색, 삽입, 수정, 삭제하는 데 사용하며 SELECT, INSERT, DELETE, UPDATE 문 등이 있음. 여기서 SELECT 문은 특별히 질의어(query)라고 함. 데이터 제어어(DCL) : 데이터의 사용 권한을 관리하는 데 사용하며 GRANT, REVOKE 문 등이 있음.

19 02. SQL 개요 그림 3-12 데이터 정의어와 데이터 조작어의 주요 명령어

20 02. SQL 개요 예) 김연아 고객의 전화번호를 찾으시오. SELECT phone FROM Customer
예) 김연아 고객의 전화번호를 찾으시오. SELECT phone FROM Customer WHERE name=‘김연아’ ① FROM Customer ② WHERE name='김연아' ③ SELECT phone 그림 3-13 SQL 문의 내부적 실행 순서

21 03. 데이터 조작어 - 검색 SELECT 문 집계 함수와 GROUP BY 두 개 이상 테이블에서 SQL 질의

22 03. 데이터 조작어 - 검색 SELECT 문의 구성 요소 SELECT 문의 기본 문법 속성 이름
SELECT bookname, publisher FROM Book WHERE price >= 10000; 키워드 테이블 이름 검색 조건 SELECT [ALL┃DISTINCT] 속성이름(들) FROM 테이블이름(들) [WHERE 검색조건(들)] [GROUP BY 속성이름] [HAVING 검색조건(들)] [ORDER BY 속성이름 [ASC┃DESC]] [ ] : 대괄호 안의 SQL 예약어들은 선택적으로 사용한다. | : 선택 가능한 문법들 중 한 개를 사용할 수 있다.

23 3.1.1 SELECT/FROM_서점에 어떤 도서가 있는지 알고 싶다
질의 모든 도서의 이름과 가격을 검색하시오. SELECT bookname, price FROM Book; (질의 3-1 변형) 모든 도서의 가격과 이름을 검색하시오. SELECT price, bookname FROM Book; SELECT price, bookname FROM Book;

24 3.1.1 SELECT/FROM_서점에 어떤 도서가 있는지 알고 싶다
질의 모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오. SELECT bookid, bookname, publisher, price FROM Book; SELECT * FROM Book;

25 3.1.1 SELECT/FROM_서점에 어떤 도서가 있는지 알고 싶다
질의 3-2 모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오. 질의 도서 테이블에 있는 모든 출판사를 검색하시오. SELECT publisher FROM Book; ※ 중복을 제거하고 싶으면 DISTINCT라는 키워드를 사용한다. SELECT DISTINCT publisher FROM Book;

26 3.1.2 WHERE 조건_가격이 20,000원 미만인 도서가 무엇인지 알고 싶다
비교 술어 연산자 예 비교 =, <>, <, <=, >, >= price < 20000 범위 BETWEEN price BETWEEN AND 20000 집합 IN, NOT IN price IN (10000, 20000, 30000) 패턴 LIKE bookname LIKE '축구의 역사' NULL IS NULL, IS NOT NULL price IS NULL 복합조건 AND, OR, NOT (price < 20000) AND (bookname LIKE '축구의 역사') 질의 가격이 20,000원 미만인 도서를 검색하시오. SELECT * FROM Book WHERE price < 20000;

27 3.1.2 WHERE 조건_가격이 20,000원 미만인 도서가 무엇인지 알고 싶다
범위 ※ BETWEEN은 논리 연산자인 AND를 사용할 수 있다. 질의 가격이 10,000원 이상 20,000 이하인 도서를 검색하시오. SELECT * FROM Book WHERE price BETWEEN AND 20000; SELECT * FROM Book WHERE price >= AND price <= 20000;

28 3.1.2 WHERE 조건_가격이 20,000원 미만인 도서가 무엇인지 알고 싶다
집합 ※ 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’가 아닌 도서를 검색하시오. 질의 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오. SELECT * FROM Book WHERE publisher IN ('굿스포츠', '대한미디어'); SELECT * FROM Book WHERE publisher NOT IN ('굿스포츠', '대한미디어');

29 3.1.2 WHERE 조건_가격이 20,000원 미만인 도서가 무엇인지 알고 싶다
패턴 질의 ‘축구의 역사’를 출간한 출판사를 검색하시오. SELECT bookname, publisher FROM Book WHERE bookname LIKE '축구의 역사'; 질의 도서이름에 ‘축구’가 포함된 출판사를 검색하시오. SELECT bookname, publisher FROM Book WHERE bookname LIKE '%축구%';

30 3.1.2 WHERE 조건_가격이 20,000원 미만인 도서가 무엇인지 알고 싶다
질의 도서이름의 왼쪽 두 번째 위치에 ‘구’라는 문자열을 갖는 도서를 검색하시오. SELECT * FROM Book WHERE bookname LIKE '_구%'; 표 3-3 와일드 문자의 종류 와일드 문자 의미 사용 예 + 문자열을 연결 ‘골프 ’ + ‘바이블’ : ‘골프 바이블’ % 0개 이상의 문자열과 일치 ‘%축구%’ : 축구를 포함하는 문자열 [ ] 1개의 문자와 일치 ‘[0-5]%’ : 0-5 사이 숫자로 시작하는 문자열 [^] 1개의 문자와 불일치 ‘[^0-5]%’ : 0-5 사이 숫자로 시작하지 않는 문자열 _ 특정 위치의 1개의 문자와 일치 ‘_구%’ : 두 번째 위치에 ‘구’가 들어가는 문자열

31 3.1.2 WHERE 조건_가격이 20,000원 미만인 도서가 무엇인지 알고 싶다
복합조건 질의 축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하시오. SELECT * FROM Book WHERE bookname LIKE '%축구%' AND price >= 20000; 질의 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오. SELECT * FROM Book WHERE publisher='굿스포츠' OR publisher='대한미디어';

32 3.1.3 ORDER BY_도서를 이름순으로 보고 싶다
질의 도서를 이름순으로 검색하시오. SELECT * FROM Book ORDER BY bookname; 질의 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오. SELECT * FROM Book ORDER BY price, bookname;

33 3.1.3 ORDER BY_도서를 이름순으로 보고 싶다
질의 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름 차순으로 검색한다. SELECT * FROM Book ORDER BY price DESC, publisher ASC;

34 3.2.1 집계 함수_도서 판매액의 합계를 알고 싶다 질의 3-15 고객이 주문한 도서의 총 판매액을 구하시오.
※ 의미 있는 열 이름을 출력하고 싶으면 속성이름의 별칭을 지칭하는 AS 키워드를 사용하여 열 이름을 부여한다. 질의 고객이 주문한 도서의 총 판매액을 구하시오. SELECT SUM(saleprice) FROM Orders; SELECT SUM(saleprice) AS 총매출 FROM Orders;

35 3.2.1 집계 함수_도서 판매액의 합계를 알고 싶다 질의 3-16 2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오.
질의 번 김연아 고객이 주문한 도서의 총 판매액을 구하시오. SELECT SUM(saleprice) AS 총매출 FROM Orders WHERE custid=2; 질의 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오. SELECT SUM(saleprice) AS Total, AVG(saleprice) AS Average, MIN(saleprice) AS Minimum, MAX(saleprice) AS Maximum FROM Orders;

36 3.2.1 집계 함수_도서 판매액의 합계를 알고 싶다 질의 3-18 마당서점의 도서 판매 건수를 구하시오.
질의 마당서점의 도서 판매 건수를 구하시오. SELECT COUNT(*) FROM Orders; 표 3-4 집계 함수의 종류 집계 함수 문법 사용 예 SUM SUM([ALL | DISTINCT] 속성이름) SUM(price) AVG AVG([ALL | DISTINCT] 속성이름) AVG(price) COUNT COUNT({[[ALL | DISTINCT] 속성이름] | *}) COUNT(*) MAX MAX([ALL | DISTINCT] 속성이름) MAX(price) MIN MIN([ALL | DISTINCT] 속성이름) MIN(price)

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

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

39 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; SELECT custid, COUNT(*) AS 도서수량 FROM Orders HAVING COUNT(*) ＞= 2 /* 순서가 틀렸다 */ WHERE saleprice ＞= 8000 GROUP BY custid;

40 연습문제 풀이 1 마당서점의 고객이 요구하는 다음 질문에 대해 SQL 문을 작성하시오. (1) 도서번호가 1인 도서의 이름 (2) 가격이 20,000원 이상인 도서의 이름 (3) 박지성의 총 구매액(박지성의 고객번호는 1번으로 놓고 작성) (4) 박지성이 구매한 도서의 수(박지성의 고객번호는 1번으로 놓고 작성) 2 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오. (1) 마당서점 도서의 총 개수 (2) 마당서점에 도서를 출고하는 출판사의 총 개수 (3) 모든 고객의 이름, 주소 (4) 2014년 7월 4일~7월 7일 사이에 주문받은 도서의 주문번호 (5) 2014년 7월 4일~7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호 (6) 성이 ‘김’ 씨인 고객의 이름과 주소 (7) 성이 ‘김’ 씨이고 이름이 ‘아’로 끝나는 고객의 이름과 주소

41 3.3.1 조인_2개의 테이블을 합체해보자 Customer 테이블을 Orders 테이블과 조건 없이 연결해보자. Customer와 Orders 테이블의 합체 결과 투플의 개수는 고객이 다섯 명이고 주문이 열 개이므로 5×10 해서 50이 된다. SELECT * FROM Customer, Orders; 그림 3-16 Customer와 Orders 테이블의 합체

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

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

44 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;

45 3.3.1 조인_2개의 테이블을 합체해보자 그림 3-17 마당서점 데이터 간의 연결

46 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 Book.price =20000;

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

48 3.3.1 조인_2개의 테이블을 합체해보자 표 3-8 조인 문법 명령 문법 설명 일반적인 조인
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을 이용하여 조인조건을 명시함.

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

50 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='대한미디어'));

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

52 3.3.2 부속질의_SQL 문 내에 또 다른 SQL 문을 작성해보자
그림 단계 부속질의의 실행 순서와 데이터 예

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

54 3.3.2 부속질의_SQL 문 내에 또 다른 SQL 문을 작성해보자
그림 3-21 상관 부속질의의 데이터 예

55 3.3.3 집합연산_도서를 주문하지 않은 고객을 알고 싶다
합집합 UNION, 차집합 MINUS, 교집합 INTERSECT {도서를 주문하지 않은 고객} = {모든 고객} - {도서를 주문한 고객} 질의 도서를 주문하지 않은 고객의 이름을 보이시오. SELECT name FROM Customer MINUS WHERE custid IN (SELECT custid FROM Orders); (주) Oracle은 차집합을 MINUS로 하지만 SQL 표준에서는 EXCEPT 를 사용한다.

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

57 3.3.4 EXISTS_주문이 있는 고객을 알고 싶다 Customer Orders ① ②` ③ ④ ⑤
SELECT name, address FROM Customer cs WHERE EXISTS (SELECT * FROM Orders od WHERE cs.custid =od.custid);

58 연습문제 풀이 1 마당서점의 고객이 요구하는 다음 질문에 대해 SQL 문을 작성하시오. (5) 박지성이 구매한 도서의 출판사 수 (6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이 (7) 박지성이 구매하지 않은 도서의 이름 2 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오. (8) 주문하지 않은 고객의 이름(부속질의 사용) (9) 주문 금액의 총액과 주문의 평균 금액 (10) 고객의 이름과 고객별 구매액 (11) 고객의 이름과 고객이 구매한 도서 목록 (12) 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문 (13) 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름

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

60 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 DELETE [CASCADE┃SET NULL] )

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

62 4.1 CREATE 문 ※ bookid 속성이 없어서 두 개의 속성 bookname, publisher가 기본키가 된다면 괄호
를 사용하여 복합키를 지정한다. ※ NewBook 테이블의 CREATE 문에 좀 더 복잡한 제약사항을 추가한다. CREATE TABLE NewBook ( bookname VARCHAR2(20), publisher VARCHAR2(20), price NUMBER, PRIMARY KEY (bookname, publisher)); bookname은 NULL 값을 가질 수 없고, publisher는 같은 값이 있으면 안 된다. price에 값이 입력되지 않을 경우 기본 값 10000을 저장한다. 또 가격은 최소 1,000원 이상으로 한다. CREATE TABLE NewBook ( bookname VARCHAR(20) NOT NULL, publisher VARCHAR(20) UNIQUE, price NUMBER DEFAULT CHECK(price > 1000), PRIMARY KEY (bookname, publisher));

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

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

65 CHARACTER(n) VARYING(n)
4.1 CREATE 문 외래키 제약조건을 명시할 때는 반드시 참조되는 테이블(부모 릴레이션)이 존재해야 하며 참조되는 테이블의 기본키여야 함. 외래키 지정 시 ON DELETE 또는 ON UPDATE 옵션은 참조되는 테이블의 튜플이 삭제되거나 수정될 때 취할 수 있는 동작을 지정함. NO ACTION은 어떠한 동작도 취하지 않음. 표 3-9 속성의 데이터 타입 종류 데이터 타입 설명 비슷한 타입 NUMBER(p, s) 실수형 p자리 정수, s자리 소수 부분. P와 s를 생략하여 NUMBER라고 쓰면 NUMBER(8, 2)로 저장됨. DECIMAL(p, s) NUMBER[(p,s)] INTEGER, INT SMALLINT CHAR(n) 문자형 고정길이. 문자를 저장하고 남은 공간은 공백으로 채움. CHARACTER(n) VARCHAR2(n) 문자형 가변길이. 4000바이트까지 저장됨. CHARACTER(n) VARYING(n) CHAR(n) VARYING(n) DATE 날짜형, 연도/월/날/시간을 지정함.

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

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

68 4.3 DROP 문 DROP 문은 테이블을 삭제하는 명령. DROP 문은 테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야 함(데이터만 삭제하려면 DELETE 문을 사용함). DROP문의 기본 문법 DROP TABLE 테이블이름 질의 NewBook 테이블을 삭제하시오. DROP TABLE NewBook; 질의 NewCustomer 테이블을 삭제하시오. 만약 삭제가 거절된다면 원인을 파악하고 관련된 테이블을 같이 삭제하시오(NewOrders 테이블이 NewCustomer를 참조하고 있음). DROP TABLE NewCustomer;

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

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

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

72 5.1 INSERT 문 대량 삽입(bulk insert)이란 한꺼번에 여러 개의 투플을 삽입하는 방법임.
질의 수입도서 목록(Imported_book)을 Book 테이블에 모두 삽입하시오. (Imported_book 테이블은 스크립트 Book 테이블과 같이 이미 만들어져 있음) INSERT INTO Book(bookid, bookname, price, publisher) SELECT bookid, bookname, price, publisher FROM Imported_book;

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

74 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 LIKE '박세리';

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

76 연습문제 풀이 3 마당서점에서 다음의 심화된 질문에 대해 SQL 문을 작성하시오. (1) 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름 (2) 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름 (3) (생략) 전체 고객의 30% 이상이 구매한 도서 4 다음 질의에 대해 DML 문을 작성하시오. (1) 새로운 도서 (‘스포츠 세계’, ‘대한미디어’, 10000원)이 마당서점에 입고되었다. 삽입이 안 될 경우 필요한 데이터가 더 있는지 찾아보자. (2) ‘삼성당’에서 출판한 도서를 삭제해야 한다. (3) ‘이상미디어’에서 출판한 도서를 삭제해야 한다. 삭제가 안 될 경우 원인을 생각해보자. (4) 출판사 ‘대한미디어’가 ‘대한출판사’로 이름을 바꾸었다.

77 요약 Oracle Database 11g SQL 데이터 정의어(DLL) 데이터 조작어(DML) WHERE 조건 집계 함수
GROUP BY HAVING 조인 동등조인(내부조인) 부속질의 상관 부속질의 투플 변수 집합연산 EXISTS CREATE ALTER DROP INSERT UPDATE DELETE