Preview SQL은크게데이터정의어 (DDL, Data Definition Language), 데이터조작어 (DML, Data Manipulation Language), 데이터제어어 (DCL, Data Control Language) 로나뉜다. 데이터정의어는테이블의구

Transcription

1 Chapter 03 SQL 기초 01 SQL 학습을위한준비 02 SQL 개요 03 데이터조작어 - 검색 04 데이터정의어 05 데이터조작어 - 삽입, 수정, 삭제요약연습문제 학습목표 SQL의개념과주요명령어를알아본다. SELECT 문을이용하여질의를처리하는방법을알아본다. 집계함수와 GROUP BY 문을이용하여질의를처리하는방법을알아본다. 두개이상의테이블을조회하여질의를처리하는방법을알아본다. DDL을이용하여테이블의구조를정의하고변경하는방법을알아본다. DML을이용하여데이터를삽입, 수정, 삭제하는방법을알아본다.

2 Preview SQL은크게데이터정의어 (DDL, Data Definition Language), 데이터조작어 (DML, Data Manipulation Language), 데이터제어어 (DCL, Data Control Language) 로나뉜다. 데이터정의어는테이블의구조를만드는데사용하고, 데이터조작어는저장된데이터를검색, 삽입, 수정, 삭제하는데사용하며, 데이터제어어는데이터에대한접근권한을제어하는데사용한다. 이장에서는데이터정의어와데이터조작어의문법을알아보고 SQL Server에서직접연습해본다. 데이터제어어는 9장에서자세히알아본다.

3 01 SQL 학습을위한준비 마당서점은인터넷에서도서를판매하는온라인서점이다. 주문은웹사이트를통해받고배송은택배로처리한다. 웹사이트는개설한지얼마안되어아직은취급하는도서가열권, 고객은다섯명이다. 마당서점은경영자, 운영자, 프로그래머가웹사이트의구축과운영을지원한다. 경영자 운영자 프로그래머 박지성 김연아추신수박세리장미란 축구의역사 축구아는여자축구의이해골프바이블피겨교본 역도단계별기술야구의추억야구를부탁해올림픽이야기 Olympic Champions 그림 3-1 마당서점현황 마당서점의고객이름은기억하기쉽게유명인의이름을사용하였다. 마당서점을운영하는시스템환경은 [ 그림 3-2] 와같다. 도서와고객에대한정보는데이터베이스 (SQL Server 2012, 이하 SQL Server로부름 ) 에저장하여관리하며, 운영자와고객은 LAN 혹은인터넷에연결된컴퓨터를통해데이터베이스에접속한다. 이때운영자와고객은 Chapter 03 SQL 기초 125

4 모두웹사이트를통해데이터베이스에접속하지만, 운영자는특별히데이터베이스전용관리프로그램을이용하여접속할수도있다. DB 그림 3-2 마당서점운영시스템환경 1.1 마당서점의데이터 마당서점데이터베이스는도서 Book, 고객 Customer, 주문 Orders 에관한데이터를저장하는세개의테이블로구성되어있다. 여기서잠깐테이블이란? 2장에서관계데이터베이스는수학의릴레이션개념에기초한다고배웠다. 릴레이션은실제데이터베이스업무에서 테이블 이라는용어로많이사용된다. 속성도 열, 투플도 행 이라고많이부른다. 속성은릴레이션의스키마부분을주로의미하고, 열은릴레이션의데이터부분을주로의미한다. 2부 (3~5장) 에서는테이블, 열, 행이라는용어를주로사용한다. 릴레이션용어 실무에서많이사용되는용어 같은의미의파일시스템용어 릴레이션 (relation) 테이블 (table) 파일 (file) 속성 (attribute) 열 (column) 필드 (field) 투플 (tuple) 행 (row) 레코드 (record) 126 Part 02 데이터베이스프로그래머

5 Book 테이블 Book 테이블은도서에관한데이터를저장하며속성으로 bookid( 도서번호 ), bookname ( 도서이름 ), publisher( 출판사 ), price( 정가 ) 를갖는다. bookid는기본키이며각도서를식별하기위해임의로만든일련번호값을사용한다. 일반적으로테이블의구조는 테이블이름 ( 속성1, 속성2,...) 와같이표현하고기본키에는밑줄을긋는다. 이책에서는테이블이름은첫글자만대문자로표기하고, 속성은전체를소문자로표기하도록약속한다. Book(bookid, bookname, publisher, price) bookid bookname publisher price Olympic Champions Pearson 그림 3-3 Book 테이블 Customer 테이블 Customer 테이블은고객에관한데이터를저장하며속성으로 custid( 고객번호 ), name( 이름 ), address( 주소 ), phone( 전화번호 ) 을갖는다. custid는기본키이며각고객을식별하기위해임의로만든일련번호값을사용한다. Customer (custid, name, address, phone) custid name nameaddress phone NULL 그림 3-4 Customer 테이블 Chapter 03 SQL 기초 127

6 Orders 테이블 Orders 테이블은고객의주문사항을저장하며속성으로 orderid( 주문번호 ), custid( 고객번호 ), bookid( 도서번호 ), saleprice( 판매가격 ), orderdate( 주문날짜 ) 를갖는다. orderid 는주문번호를나타내는기본키이며 custid와 bookid는각각고객번호와도서번호를가리키는외래키이다. saleprice는도서의판매가격으로, 정가보다할인된가격으로팔았다고가정한다. orderdate는 YYYY-MM-DD( 예, 2013년 10월 22일은 ) 형식으로데이터를저장한다. 이를바탕으로 [ 그림 3-5] 의첫번째튜플을보면 1번고객 박지성 이 1번도서 축구의역사 를주문하였고판매가격은 6,000원이며 2013년 7월 1일에주문하였다는것을알수있다. Orders(orderid, custid, bookid, saleprice, orderdate) orderid custid bookid saleprice orderdate 그림 3-5 Orders 테이블 [ 그림 3-6] 은세개의테이블간속성의참조관계를나타낸것이다. Book(bookid, bookname, publisher, price) Orders(orderid, custid, bookid, saleprice, orderdate) Customer(custid, name, address, phone) 그림 3-6 마당서점의데이터구성도 128 Part 02 데이터베이스프로그래머

7 1.2 누가어떤정보를원하는가? 마당서점데이터베이스를사용하는사람은크게고객, 운영자, 경영자로나눌수있다. 각각은원하는정보가다르다. 먼저고객은도서를주문하기위해도서이름, 가격, 본인의주문내역등을확인하기원한다. 운영자는고객의주문현황, 주문총액, 도서별판매량, 남아있는도서의수등을알기원한다. 경영자는운영자가원하는정보보다좀더요약된고부가가치의정보인월별매출동향, 도서별판매동향등을알기원한다. 고객, 운영자, 경영자관점에서원하는정보를구분해보면 [ 그림 3-7] 과같다. 여기에는물론공통적으로원하는정보도있으며, 실제서점이라면더많은요구사항이있을것이다. 그림 3-7 사용자그룹별로원하는정보 Chapter 03 SQL 기초 129

8 프로그래머는사용자그룹별로원하는정보가무엇인지파악해야한다. 또 DBMS 에익숙해 야하며일반프로그래밍언어와 SQL 언어를다룰줄알아야한다. 반면고객, 운영자, 경영 자는 SQL 언어에대해잘알지못해도된다. 1.3 SQL Server 와샘플데이터설치 SQL Server는마이크로소프트사의관계형데이터베이스시스템이다. 1989년에마이크로소프트 Microsoft 사와사이베이스 Sybase 사그리고애시톤- 테이트 Ashton-Tate 사가공동으로개발하였고, 마이크로소프트사가윈도우 NT 시스템을개발한후사이베이스사와결별하고 1998년윈도우용인 SQL Server 7.0을발표하였다. 이후마이크로소프트사는 SQL Server 2000, 2005, 2008, 2012 버전들을발표하면서성능을개선하고개발자용툴을추가하였다. 현재발표된 SQL Server 2012 버전은세부적으로기업의업무용인엔터프라이즈에디션 enterprise edition, 기본적인기능을제공하는스탠다드에디션 standard edition, 무료로제공되는익스프레스에디션 express edition 이있다. 익스프레스에디션은상업용에디션 ( 스탠다드이상 ) 에비해메모리사용량과데이터베이스크기등의차이가있지만 RDBMS 대부분의기능을사용할수있다. 이책에서는실습을위해 SQL Server 2012 익스프레스에디션을사용한다. SQL Server 2012 익스프레스에디션의설치는윈도우 7을기준으로부록 A에서다운로드, 설치, 마당데이터베이스생성방법등을설명하였다. 윈도우운영체제버전이약간달라도원리는같다. 앞으로실습할 SQL 문은마당 (Madang) 데이터베이스를기준으로한다. 마당데이터베이스가잘설치되었다면 SQL 문을작성하고결과를확인할수있다. 다음과같은순서로진행한다. ➊ SQL Server 시작윈도우의 [ 시작 ]-[ 모든프로그램 ]-[Microsoft SQL Server 2012]-[SQL Server Management Studio] 를선택한다. 서버연결화면에서인증 (SQL Server 인증 ), 로그인 (mduser), 암호 (mdpass) 를입력하고 < 연결 > 을클릭한다. 130 Part 02 데이터베이스프로그래머

9 1 2 3 그림 3-8 SQL Server 시작 여기서잠깐 SQL Server 인증모드 SQL Server 의인증모드는 Windows 인증모드 와 SQL Server 인증모드 가있다. 이책에서는기본적으로 SQL Server 인증모드 를사용한다. 하지만아래그림과같이 Windows 인증모드로접속하여실습해도문제가없다. 각인증모드의차이는 9 장에서자세히설명한다. Windows 인증모드로연결 ➋ 쿼리창열기 SQL Server Management Studio가시작되면툴바에서새쿼리 ( ) 를클릭한다. ➌ 데이터베이스선택 SQL 문을사용하기위해데이터베이스를확인한다. 화면오른쪽에있는개체탐색기상단을보면현재데이터베이스가 Madang 임을알수있다. Chapter 03 SQL 기초 131

10 그림 3-9 데이터베이스확인 ➍ SQL 문입력및결과확인 쿼리창에 SQL 문을입력하고실행 ( ) 을클릭한다. 아래화면에서는 SELECT * FROM Customer 를입력하였다. 결과는쿼리결과창에서확인한다. 2 1 쿼리창 개체탐색기 속성창 쿼리결과창 그림 3-10 SQL 문입력및결과확인 SQL 은 C, 자바와달리명령을위한예약어에대소문자를구분하지않는다. 단, Person, person 과같이데이터베이스에저장된내용 ( 사이의단어 ) 을검색할경우에는대소문자를구분한다. 132 Part 02 데이터베이스프로그래머

11 여기서잠깐데이터베이스를선택하는방법 데이터베이스를선택하는방법은크게두가지가있다. ➊ 쿼리창에 USE 데이터베이스이름 을입력하고실행 ( ) 을클릭한다. ➋ 툴바에서사용하고자하는데이터베이스를선택한다. 한번선택된데이터베이스는다음변경이있을때까지유효하게사용된다. 여기에서는본문실습을위해 Madang 데이터베이스를선택하지만, 이후연습문제를풀기위해데이터베이스를변경해야하므로익혀두기바란다. Chapter 03 SQL 기초 133

12 02 SQL 개요 마당서점고객이 축구의역사 라는도서의출판사와가격을알고싶어한다. 프로그래머는어떻게해야할까? 다행히데이터베이스에서는몇가지사실만 DBMS에게알려주면원하는답을얻을수있다. 즉 도서, 축구의역사, 출판사, 가격 과같은단어를적절히알려주면된다. 이와같이 DBMS에게원하는내용을비교적쉽게알려주고결과를얻는데사용하는데이터베이스전용언어가 SQL이다. 위의예를 SQL로표현하면다음과같다. SELECT FROM WHERE publisher, price Book bookname LIKE ' 축구의역사 '; DBMS는 SQL 문을해석하고프로그램으로변환하여실행한후결과를알려준다. SQL은데이터를다루는데있어서자바, C++, C 같은일반프로그래밍언어보다훨씬쉽고편리하다. SELECT publisher price FROM Book WHERE bookname LIKE DBMS 그림 3-11 SQL 을사용해자료를찾는과정 Book publisher price DB SQL Structured Query Language 은 1970 년대후반 IBM 이 SEQUEL Structured English QUEry Language 이라는 America National Standards Institute 이름으로개발한관계형데이터베이스언어다. 이후 1986년 ANSI 에의해관계형데이터베이스표준언어로승인되었다. SQL 의후속버전은 1992 년에 SQL2, 1999 년에 SQL3 로확장되었으며 SQL3 는객체지향의개념을일부포함하고있다. 134 Part 02 데이터베이스프로그래머

13 SQL은자바나 C 같은완전한프로그래밍언어는아니다. 대신데이터부속어 data sublanguage 라고부르는데, 그이유는데이터베이스의데이터와메타데이터를생성하고처리하는문법만갖고있기때문이다. SQL은 DBMS에직접입력해사용할수있고, 자바나 C로작성된클라이언트 / 서버응용프로그램에삽입하여사용할수도있다. 또 HTML 웹페이지문서에삽입할수있고, 보고서나데이터추출프로그램에서도사용할수있다. 또한 Visual Studio.NET 이나다른개발도구에서직접실행할수도있다. [ 표 3-1] 은 SQL과일반프로그래밍언어의차이점을설명한것이다. 표 3-1 SQL 과일반프로그래밍언어의차이점 용도입출력번역사용예 SQL 데이터베이스에서데이터를추출하여문제해결 입력은테이블, 출력도테이블 DBMS SELECT * FROM Book; 일반프로그래밍언어모든문제해결모든형태의입출력가능컴파일러 int main( ) {... } SQL은기능에따라데이터정의어 DDL, Data Definition Language DML, Data Manipulation 와데이터조작어 Language, 데이터제어어 DCL, Data Control Language 로나뉜다. 데이터정의어 (DDL) : 테이블이나관계의구조를생성하는데사용하며 CREATE, ALTER, DROP 문등이있다. 데이터조작어 (DML) : 테이블에데이터를검색, 삽입, 수정, 삭제하는데사용하며 SELECT, INSERT, DELETE, UPDATE 문등이있다. 여기서 SELECT 문은특별히질의어 (query) 라고부른다. 데이터제어어 (DCL) : 데이터의사용권한을관리하는데사용하며 GRANT, REVOKE 문등이있다. 이장에서는데이터정의어와데이터조작어를배우고데이터제어어는 9장에서배운다. [ 그림 3-12] 는테이블에서데이터정의어와데이터조작어의주요명령어를그림으로표현한것이다. Chapter 03 SQL 기초 135

14 그림 3-12 데이터정의어와데이터조작어의주요명령어 데이터정의어와데이터조작어에대해자세히살펴보기전에 SELECT 문의예를들어 SQL 문을이해해보자. SELECT 문의문장프레임워크는다음과같다. SELECT : 질의결과추출되는속성리스트를열거한다. FROM : 질의에어느테이블이사용되는지열거한다. WHERE : 질의의조건을작성한다. 예를들어 Customer 테이블에서 김연아고객의전화번호를찾으시오 라는질의를 SQL 문으로표현하면다음과같다. SELECT FROM WHERE phone Customer name=' 김연아 '; SQL 문은실행순서가없는비절차적인 non procedural 언어다. 즉찾는데이터만기술하고어떻게찾는지그절차 ( 실행순서 ) 는기술하지않는다. 그렇지만위 SQL 문은내부적으로 DBMS 에의하여 1 FROM 절에쓰여진테이블을가져와서 2 WHERE 절조건에의하여투플을선택한다음 3 SELECT 절에있는속성들을결과로출력한다. FROM Customer WHERE name SELECT phone 그림 3-13 SQL 문의내부적실행순서 136 Part 02 데이터베이스프로그래머

15 03 데이터조작어 - 검색 SQL의 SELECT 문은데이터를검색하는기본문장으로, 특별히질의어 query 라고부른다. SELECT 문은검색한결과를테이블형태로출력하는데, 예를들어 10,000원이상인도서의이름과출판사를보이시오 라는질의는다음과같이작성할수있다. SELECT bookname, publisher FROM Book WHERE price >= 10000; 위 SELECT 문의구성요소에대한설명은 [ 그림 3-14] 와같다. SELECT bookname, publisher FROM Book WHERE price >= 10000; 그림 3-14 SELECT 문의구성요소 SELECT 문의기본문법은다음과같다. SELECT [ALL DISTINCT] 속성이름 ( 들 ) FROM 테이블이름 ( 들 ) [WHERE 검색조건 ( 들 )] [GROUP BY 속성이름 ] [HAVING 검색조건 ( 들 )] [ORDER BY 속성이름 [ASC DESC]] [ ] : 대괄호안의 SQL 예약어들은선택적으로사용한다. : 선택가능한문법들중한개를사용할수있다. Chapter 03 SQL 기초 137

16 SELECT 문의문법을좀더자세히살펴보면다음과같다. SELECT [FROM [ALL DISTINCT] [TOP n] [ 테이블이름.]{ * 속성이름 [[AS] 속성이름별칭 ]} [INTO new_table] { 테이블이름 [AS 테이블이름별칭 ]} [INNER JOIN LEFT [OUTER] JOIN RIGHT [OUTER] JOIN { 테이블이름 [ON 검색조건 ]} CROSS JOIN FULL [OUTER] JOIN { 테이블이름 }]] [WHERE 검색조건 ( 들 )] [GROUP BY { 속성이름, [..., n]}] [HAVING 검색조건 ( 들 )] [ 질의 UNION 질의 질의 UNION ALL 질의 ] [ORDER BY { 속성이름 [ASC DESC], [..., n]}] [ ] : 대괄호안의 SQL 예약어들은선택적으로사용한다. { } : 중괄호안의 SQL 예약어들은필수적으로사용한다. : 선택가능한문법들중한개를사용할수있다. 3.1 SELECT 문 마당서점사용자중 고객 이주로요청하는질의를예로들어 SELECT 문의기본적인기능을살펴보자. 고객박지성은축구와관련된도서를구입하기위해도서정보가필요하며, 또도서를구입한후주문내역을확인하고싶어한다. 실습을진행할때필요한예제데이터베이스는부록 A를참고하여생성한다. 실습은 SQL Server Management Studio에로그인한후 Madang 데이터베이스가선택되어있는지확인한후진행한다. Madang 데이터베이스를선택하는방법은 133쪽 < 여기서잠깐 > 을참고한다 SELECT/FROM_ 서점에어떤도서가있는지알고싶다마당서점에서판매하는모든도서의이름 bookname 과가격 price 을검색하는 SQL 문과실행결과는다음과같다. 138 Part 02 데이터베이스프로그래머

17 질의 3-1 모든도서의이름과가격을검색하시오. SELECT FROM bookname, price Book; SQL 문은세미콜론 (;) 과함께끝난다. 세미콜론은 SQL-92 표준에서정한사항이지만 SQL Server에서는세미콜론을생략해도된다. 그러나 SQL 문을작성할때는세미콜론과함께마치는습관을기르도록하자. 또대소문자의구분은없지만 SQL 예약어는대문자로, 테이블이나속성이름은소문자로적어주면읽기쉽다. SELECT 절에서열순서는결과테이블의열순서를결정한다. 만약 SELECT 절에 bookname 과 price를바꾸면결과테이블에서순서가바뀐다. 다음 SQL 문의처리결과를보면알수있다. SELECT FROM price, bookname Book; Book 테이블의모든열을보고싶으면 SELECT 절에각열의이름을써주면된다. Chapter 03 SQL 기초 139

18 질의 3-2 모든도서의도서번호, 도서이름, 출판사, 가격을검색하시오. SELECT FROM bookid, bookname, publisher, price Book; SELECT 절에 * asterisk( 애스터리스크 ) 는모든열을나타낸다. * 를써주면열의이름을쓰지않아도된다. 열의개수가많거나열의이름을모를경우편리하게사용할수있다. [ 질의 3-2] 의 SQL 문을 * 를이용해서다시작성하면다음과같다. SELECT * FROM Book; SQL 문은관계대수와달리기본적으로중복을제거하지않는다. 따라서결과에같은값이두 개이상나올수있다. 질의 3-3 도서테이블에있는모든출판사를검색하시오. SELECT FROM publisher Book; 140 Part 02 데이터베이스프로그래머

19 중복을제거하고싶으면다음과같이 DISTINCT 라는키워드를사용한다. SELECT FROM DISTINCT publisher Book; WHERE 조건 _ 가격이 20,000원미만인도서가무엇인지알고싶다 WHERE 절은조건에맞는검색을할때사용한다. 조건으로사용할수있는술어 predicate 는비교, 범위, 집합, 패턴, NULL로구분할수있다. 각각에대한설명은 [ 표 3-2] 와같다. NULL 은 4장에서설명하도록한다. 표 3-2 WHERE 절에조건으로사용할수있는술어 술어 연산자 사용예 비교 =, <>, <, <=, >, >= price < 범위 BETWEEN price BETWEEN AND 집합 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 축구의역사 ) Chapter 03 SQL 기초 141

20 비교단순비교는 =, <>, <, <=, >, >= 등을사용한다. 예를들어가격이 20,000원미만인도서를검색하려면다음과같이 WHERE 절을작성한다. 주의할점은 SQL 문작성시숫자의경우천단위를표시하는콤마 (,) 기호는사용하지않는다. 질의 3-4 가격이 20,000 원미만인도서를검색하시오. SELECT * FROM Book WHERE price < 20000; 범위 WHERE 절에서 BETWEEN 연산자를사용하면값의범위를지정할수있다. 예를들어 가격이 10,000 원이상 20,000 원이하인도서를검색하는 SQL 문은다음과같다. 범위의양끝값인 10,000 과 20,000 은결과테이블에포함된다. 질의 3-5 가격이 10,000 원이상 20,000 이하인도서를검색하시오. SELECT * FROM Book WHERE price BETWEEN AND 20000; BETWEEN 은논리연산자인 AND 를사용하여다음과같이사용할수있다. 142 Part 02 데이터베이스프로그래머

21 SELECT * FROM Book WHERE price >= AND price <= 20000; 집합 WHERE 절에두개이상의값을비교하려면 IN 연산자와 NOT IN 연산자를사용하면편리하다. IN 연산자는집합의원소인지판단하는연산자다. 다음은 IN 연산자를사용하여출판사가 굿스포츠 혹은 대한미디어 인도서를검색하는 SQL 문이다. 문자값들을괄호안에포함시켜비교하며, publisher 값이이중하나와같으면선택된다. 질의 3-6 출판사가 굿스포츠 혹은 대한미디어 인도서를검색하시오. SELECT * FROM Book WHERE publisher IN (' 굿스포츠 ', ' 대한미디어 '); 출판사가 굿스포츠 혹은 대한미디어 가아닌출판사를검색하는 SQL 문은다음과같다. SELECT * FROM Book WHERE publisher NOT IN (' 굿스포츠 ', ' 대한미디어 '); Chapter 03 SQL 기초 143

22 패턴 문자열의패턴을비교할때는 LIKE 연산자를사용한다. 축구의역사 라는도서를찾고싶 다면다음과같이 WHERE 절조건에 LIKE 를사용하여비교한다. 만약찾는속성이텍스 트혹은날짜데이터를포함하면비교값에는반드시영문작은따옴표 (' ') 로둘러싸야한다. 한글의작은따옴표 ( ) 를사용하면오류가난다. 여기서잠깐문자열비교시인용부호일반프로그래밍언어에서문자열은 " "( 큰따옴표 ) 를사용하지만 SQL 언어에서는 ' '( 작은따옴표 ) 를사용한다. 그이유는 SQL 문자체가문자열로인용이되어다른프로그래밍언어에삽입될때혼란이있을수있기때문이다. 예를들면자바에서다음과같이 SQL 문을통째로문자열에저장한경우다. myquery = "SELECT FROM WHERE bookname, publisher Book bookname LIKE ' 축구의역사 ';"; 그러나 SELECT 문에서속성과 AS 다음에붙이는별칭은다음과같이 " "( 큰따옴표 ) 를붙이는것은선택사항이다. 단, 별칭이름에공백이포함될경우에는 " "( 큰따옴표 ) 를반드시사용한다. SELECT SUM(price) 총계 SELECT SUM(price) AS 총계 SELECT SUM(price) AS " 총계 " SELECT SUM(price) " 총계 " SELECT SUM(price) " 판매총계 " 질의 3-7 축구의역사 를출간한출판사를검색하시오. SELECT FROM WHERE bookname, publisher Book bookname LIKE ' 축구의역사 '; 도서이름에 축구 가포함된도서를찾고싶다면와일드문자 % 를사용한다. % 는아무문자열이나대신하는기호다. 144 Part 02 데이터베이스프로그래머

23 질의 3-8 도서이름에 축구 가포함된출판사를검색하시오. SELECT FROM WHERE bookname, publisher Book bookname LIKE '% 축구 %'; 와일드문자 _( 밑줄기호 ) 는특정위치에한문자만대신할때사용한다. 다음은도서이름의왼쪽두번째위치에 구 라는문자열을갖는도서를검색하는 SQL 문이다. 질의 3-9 도서이름의왼쪽두번째위치에 구 라는문자열을갖는도서를검색하시오. SELECT * FROM Book WHERE bookname LIKE '_ 구 %'; 축구, 야구, 배구와같이두번째글자에 구 가들어가는도서를찾는다. 문자열검색시 LIKE 와같이사용하는와일드문자를정리하면 [ 표 3-3] 과같다. 표 3-3 와일드문자의종류 와일드문자 의미 사용예 + 문자열을연결 골프 + 바이블 : 골프바이블 % 0개이상의문자열과일치 % 축구 % : 축구를포함하는문자열 [ ] 1개의문자와일치 [0-5]% : 0-5 사이숫자로시작하는문자열 [^] 1개의문자와불일치 [^0-5]% : 0-5 사이숫자로시작하지않는문자열 _ 특정위치의 1개의문자와일치 _ 구 % : 두번째위치에 구 가들어가는문자열 Chapter 03 SQL 기초 145

24 복합조건 WHERE 절에논리연산자 AND, OR, NOT을사용하면복합조건을명시할수있다. 예를들어축구에관한도서중가격이 20,000원이상인도서를검색하려면다음과같이 AND 연산자를사용한다. 질의 3-10 축구에관한도서중가격이 20,000 원이상인도서를검색하시오. SELECT * FROM Book WHERE bookname LIKE '% 축구 %' AND price >= 20000; 출판사가 굿스포츠 혹은 대한미디어 인도서를검색하려면다음과같이 OR 연산자를사용 한다. 그러나같은속성에서여러개값을비교하여찾을때는앞에서배운 IN 연산자를사용 하는것이바람직하다. 질의 3-11 출판사가 굿스포츠 혹은 대한미디어 인도서를검색하시오. SELECT * FROM Book WHERE publisher=' 굿스포츠 ' OR publisher=' 대한미디어 '; ORDER BY_ 도서를이름순으로보고싶다 SQL 문의실행결과행의순서는각 DBMS 에저장된위치에따라결정된다. SQL 문의실행 결과를특정순서대로출력하고싶으면 ORDER BY 절을사용한다. 146 Part 02 데이터베이스프로그래머

25 질의 3-12 도서를이름순으로검색하시오. SELECT * FROM Book ORDER BY bookname; 도서를가격순으로먼저정렬한후가격이같은도서에대하여이름순으로다시정렬하고싶으면 ORDER BY 절에서정렬을원하는열이름을순서대로지정한다. 질의 3-13 도서를가격순으로검색하고, 가격이같으면이름순으로검색하시오. SELECT * FROM Book ORDER BY price, bookname; 문자의경우정렬순서는숫자, 한글, 영문자순이다. 정렬의기본은오름차순이다. 만약내림차순으로정렬하려면열이름다음에 DESC 키워드를사용하면된다. 예를들어가격의내림차순 descending 으로정렬한후출판사의오름차순 ascending 으로정렬하고싶다면다음과같이작성한다. Chapter 03 SQL 기초 147

26 질의 3-14 도서를가격의내림차순으로검색하시오. 만약가격이같다면출판사의오름차순으로검색한다. SELECT * FROM Book ORDER BY price DESC, publisher ASC; 3.2 집계함수와 GROUP BY 앞에서마당서점의고객이필요로하는질의를중심으로살펴보았다. 이제부터는 운영자 가필요로하는질의를중심으로 SQL의기능을살펴본다. 운영자는도서판매총액, 일별판매량등도서판매에도움이되는집계된정보를필요로한다. 집계를하기위해서는 GROUP BY 문을사용하고구체적인집계내용은집계함수를사용한다. 집계 (aggregate) 는통계 (statistics) 와비슷한의미로, 데이터베이스에서는통계보다집계라는용어를사용한다 집계함수 _ 도서판매액의합계를알고싶다운영자는전체도서의판매액합계를알고싶어한다. 이를위해서 SQL의집계함수 aggregate function 를이용할수있다. 집계함수는테이블의각열에대해계산을하는함수로 SUM, AVG, MIN, MAX, COUNT의다섯가지가있다. Orders 테이블에저장된모든주문의총판매액을구하는 SQL 문은다음과같다. 질의 3-15 고객이주문한도서의총판매액을구하시오. SELECT FROM SUM(saleprice) Orders; 148 Part 02 데이터베이스프로그래머

27 SQL 문의결과가항상테이블임을고려하면위질의의결과는한개의속성, 한개의튜플로구성된테이블이다. 또 SQL 문에서 SUM(saleprice) 는저장된데이터를가공하여얻은새로운결과열이기때문에결과테이블에 ( 열이름없음 ) 이라고출력된다. 이는보기에좋지않다. 의미있는열이름을출력하고싶으면속성이름의별칭을지칭하는 AS 키워드를사용하여열이름을부여한다. 위질의를 AS 키워드를사용하여다시작성하면다음과같다. SELECT FROM SUM(saleprice) AS 총매출 Orders; 최근버전의 DBMS에서는다음과같이 AS를생략할수있다. SELECT SUM(saleprice) 총매출또속성이름의별칭중간에공백을넣어야할경우다음과같이큰따옴표를사용한다. SELECT SUM(saleprice) " 전체매출 " 집계함수는 WHERE 문과같이사용하면더유용하다. 예를들어 2번김연아고객이주문한도서의총판매액을알고싶으면다음과같이 SQL 문을작성한다. 질의 번김연아고객이주문한도서의총판매액을구하시오. SELECT FROM WHERE SUM(saleprice) AS 총매출 Orders custid=2; 집계함수는여러개를혼합하여쓸수있다. 질의 3-17 고객이주문한도서의총판매액, 평균값, 최저가, 최고가를구하시오. SELECT FROM SUM(saleprice) AS Total, AVG(saleprice) AS Average, MIN(saleprice) AS Minimum, MAX(saleprice) AS Maximum Orders; Chapter 03 SQL 기초 149

28 집계함수 COUNT는행의개수를센다. COUNT() 의괄호안에는 * 혹은특정속성의이름이사용되며, 해당속성의투플의개수를세어준다 (NULL 값은제외 ). 예를들어, COUNT(*) 는전체투플의수를세어주며, COUNT(publisher) 는출판사의수를, COUNT(DISTINCT publisher) 는중복을제거한출판사의수를세어준다 (NULL 값은제외 ). 질의 3-18 마당서점의도서판매건수를구하시오. SELECT FROM COUNT(*) Orders; 주로사용되는집계함수는 [ 표 3-4] 와같다. 문법에서 [ ] 기호는선택을나타내며, 기호 는둘중하나를선택하라는의미다. 표 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) GROUP BY_ 어느고객이얼마나주문했는지알고싶다 SQL 문에서 GROUP BY 절을사용하면속성값이같은값끼리그룹을만들수있다. 예를들어 Orders 테이블을사용하는 SELECT 문에 GROUP BY custid 라고명시하면, DBMS 는 custid가같은값끼리그룹으로묶는다. 결과는 custid가서로다른값들에대하여그룹이만들어진다. 질의 3-19 고객별로주문한도서의총수량과총판매액을구하시오. SELECT FROM GROUP BY custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액 Orders custid; 150 Part 02 데이터베이스프로그래머

29 위질의에서 GROUP BY 절을사용할때결과를그림으로보면다음과같다. 그림 3-15 GROUP BY 절의수행 HAVING 절은 GROUP BY 절의결과나타나는그룹을제한하는역할을한다. 예를들어 주문도서가두권이상일경우만출력하려면다음과같이작성한다. 질의 3-20 가격이 8,000 원이상인도서를구매한고객에대하여고객별주문도서의총수량을구하시오. 단, 두권 이상구매한고객만구한다. SELECT custid, COUNT(*) AS 도서수량 FROM Orders WHERE saleprice >= 8000 GROUP BY custid HAVING count(*) >= 2; GROUP BY 와 HAVING 절의문법과주의사항을살펴보면 [ 표 3-5] 와같다. Chapter 03 SQL 기초 151

30 표 3-5 GROUP BY 와 HAVING 절의문법과주의사항 문법 주의사항 GROUP BY 로투플을그룹으로묶은후 SELECT 절에는 GROUP BY 에서사용한 < 속성 > 과집 계함수만나올수있다. GROUP BY < 속성 > 맞는예 SELECT custid, SUM(saleprice) FROM Orders GROUP BY custid; 틀린예 SELECT bookid, SUM(saleprice) / * SELECT 절에 bookid 속성이올수없다 * / FROM Orders GROUP BY custid; WHERE 절과 HAVING 절이같이포함된 SQL 문은검색조건이모호해질수있다. HAVING 절은 1 반드시 GROUP BY 절과같이작성해야하고 2 WHERE 절보다뒤에나와야한다. 그리고 3 < 검색조건 > 에는 SUM, AVG, MAX, MIN, COUNT와같은집계함수가와야한다. HAVING < 검색조건 > 맞는예 SELECT custid, COUNT( * ) AS 도서수량 FROM Orders WHERE saleprice >= 8000 GROUP BY custid HAVING count( * ) >= 2; 틀린예 SELECT custid, COUNT( * ) AS 도서수량 FROM Orders HAVING count( * ) >= 2 / * 순서가틀렸다 * / WHERE saleprice >= 8000 GROUP BY custid; 여기서잠깐 GROUP BY 절이포함된 SQL 문의실행순서 SQL 문은실행순서가없는비절차적인 (non procedural) 언어지만 SQL 문은내부적으로실행순서가있다. 다음예를보면서 GROUP BY, HAVING, ORDER BY 절이포함된 SQL 문의실행순서를이해해보자. SELECT custid, COUNT(*) AS 도서수량 5 FROM Orders 1 WHERE saleprice >= GROUP BY custid 3 HAVING count(*) > 1 4 ORDER BY custid; Part 02 데이터베이스프로그래머

31 FROM Orders GROUP BY custid HAVING count( * ) > 1 saleprice 8000 SELECT custid, COUNT( * ) AS ORDER BY custid; 3.3 두개이상테이블에서 SQL 질의 박지성고객이주문한도서의총구매액을알고싶으면어떻게해야할까? 총구매액은 saleprice 값을합하면되지만문제가있다. saleprice 는 Orders 테이블에있고고객의이름은 Customer 테이블에있다. Orders 테이블에는고객의이름 박지성 이없는것이다. 그렇다면어떻게총구매액을계산할수있을까? 앞에서는하나의테이블을이용한 SQL 질의를배웠다. 이제부터는두개이상의테이블을이용한 SQL 질의에대해알아본다. SQL은여러개의테이블을이용하여질의하는두가지방법을제공한다. 조인 join 과부속질의 subquery 다. 두가지모두여러개의테이블을동시에다루지만방법은약간다르다 조인 _2개의테이블을합체해보자조인 join 은한테이블의행을다른테이블의행에연결하여두개이상의테이블을결합하는연산이다. 다음은 Customer 테이블을 Orders 테이블과조건없이연결한예다. SQL 문은다음과같이작성한다. 두테이블을아무런조건을주지않고 SELECT 시키면관계대수의카티전프로덕트연산이된다. Chapter 03 SQL 기초 153

32 SELECT * FROM Customer, Orders; 그림 3-16 Customer 와 Orders 테이블의합체 Customer와 Orders 테이블의합체결과튜플의개수는고객이다섯명이고주문이열개이므로 5 10 해서 50이된다. Customer 테이블의 1번고객박지성은 Orders 테이블의열개행모두에대해결합하고, 나머지고객들도각각 Orders 테이블의열개행모두에대해결합한다 ( 이와같이조건이없는테이블간의조인을카티전프로덕트라고한다 ). 그렇지만결과는논리에맞지않다. 필요한것은박지성의 custid가 1이므로 Ordres 테이블에서 custid가 1인것만선택하면된다. 이것은 SQL 문의 WHERE 절에두테이블의연결조건을추가함으로써쉽게해결할수있다. 154 Part 02 데이터베이스프로그래머

33 질의 3-21 고객과고객의주문에관한데이터를모두보이시오. SELECT * FROM WHERE Customer, Orders Customer.custid =Orders.custid; 위결과를고객별로보기위해 ORDER BY 절을이용하여정렬하면다음과같다. 질의 3-22 고객과고객의주문에관한데이터를고객별로정렬하여보이시오. SELECT * FROM WHERE ORDER BY Customer, Orders Customer.custid =Orders.custid Customer.custid; 여기서잠깐열이름을표기하는방법 앞의두 SQL 문에서 Customer.custid, Orders.custid 와같이표현된것을볼수있다. 이는 테이블이름. 열이름 형식의표현으로열이름이어느테이블과연관되는지정확히명시한다. Chapter 03 SQL 기초 155

34 여러개의테이블을연결하여하나의테이블을만드는과정을테이블조인이라고한다. 특히앞의 SQL 문처럼동등조건에의하여테이블을조인하는것을동등조인 equi join 이라고한다. 조인이라고하면대부분동등조인을말한다. 조인을사용하면두개이상의테이블에서일부데이터만얻을수있다. 예를들어고객의이름과고객이주문한도서의가격을알고싶다면다음과같이 SQL 문을작성한다. 질의 3-23 고객의이름과고객이주문한도서의가격을검색하시오. SELECT FROM WHERE name, saleprice Customer, Orders Customer.custid =Orders.custid; 모든 SQL 질의의결과는단일테이블이다. 따라서위결과테이블에 SQL 문법을적용할수있다. 예를들어 GROUP BY 절과 ORDER BY 절을추가하면다음과같다. 질의 3-24 고객별로주문한모든도서의총판매액을구하고, 고객별로정렬하시오. SELECT FROM WHERE GROUP BY ORDER BY name, SUM(saleprice) Customer, Orders Customer.custid =Orders.custid Customer.name Customer.name; 156 Part 02 데이터베이스프로그래머

35 세개이상의테이블을조인할수도있다. 예를들어각고객의이름과고객이주문한도서의이름을구하고싶다고하자. 결과를얻으려면세개의테이블을한꺼번에조인해야한다. 고객의이름은 Customer 테이블에있고, 고객의주문내역은 Orders 테이블에있으며, 도서의이름은 Book 테이블에있기때문이다. Customer 테이블과 Orders 테이블은 Customer의기본키인 custid와 Orders의외래키인 custid로연결한다. 또 Orders 테이블과 Book 테이블은 Orders의외래키인 bookid와 Book의기본키인 bookid로연결한다. [ 그림 3-17] 은데이터들이연결되는모습을나타낸것이다. 그림 3-17 마당서점데이터간의연결 Chapter 03 SQL 기초 157

36 [ 그림 3-17] 의질의를 SQL 문으로작성하면다음과같다. 질의 3-25 고객의이름과고객이주문한도서의이름을구하시오. SELECT FROM WHERE Customer.name, book.bookname Customer, Orders, Book Customer.custid =Orders.custid AND Orders.bookid =Book.bookid; 세개이상의테이블을조인하는 SQL 문에 WHERE 절을추가하여원하는결과만추출할수 도있다. 예를들어가격이 20,000 원인도서를주문한고객의이름과도서의이름을구하려 면다음과같이작성한다. 질의 3-26 가격이 20,000 원인도서를주문한고객의이름과도서의이름을구하시오. SELECT FROM WHERE Customer.name, book.bookname Customer, Orders, Book Customer.custid =Orders.custid AND Orders.bookid =Book.bookid AND Orders.saleprice =20000; 조인연산의특별한경우로외부조인 outer join 이있다. [ 질의 3-23] 의고객의이름과고객이주문한도서의가격을구하는동등조인의예에서도서를주문하지않은고객 박세리 는결과에포함되지않는다. 만약도서를구매하지않은고객 박세리 까지포함하여고객의이름과고객이주문한도서의가격을구하려면어떻게해야할까? 방법은외부조인을사용하면된다. 158 Part 02 데이터베이스프로그래머

37 질의 3-27 도서를구매하지않은고객을포함하여고객의이름과고객이주문한도서의가격을구하시오. SELECT FROM Customer.name, saleprice Customer LEFT OUTER JOIN Orders ON Customer.custid =Orders.custid; 결과를보면고객박세리의 saleprice 값이 NULL로표시되어있다. 왼쪽외부조인은 LEFT OUTER JOIN... ON 문법으로질의한다. 오른쪽에있는테이블에대하여같은방법으로질의하려면 RIGHT OUTER JOIN... ON을사용하면되고, 왼쪽과오른쪽테이블모두에대하여질의하려면 FULL OUTER JOIN... ON을사용하면된다. 조인에관한문법은 [ 표 3-6] 과같다. 일반적인조인문은두가지형태로표현할수있다. 외부조인은 OUTER 키워드를사용하여표현한다. 세개이상의테이블은테이블과조인조건을추가하여작성한다. 표 3-6 조인문법 명령문법설명 일반적인조인 SELECT < 속성들 > FROM 테이블1, 테이블2 WHERE < 조인조건 > AND < 검색조건 > SQL 문에서는주로동등조인을사용한다. SELECT < 속성들 > 두가지문법중하나를사용할수있다. FROM 테이블1 INNER JOIN 테이블2 ON < 조인조건 > WHERE < 검색조건 > 외부조인 SELECT < 속성들 > FROM 테이블1 {LEFT RIGHT FULL [OUTER]} JOIN 테이블2 ON < 조인조건 > WHERE < 검색조건 > 외부조인은 FROM 절에조인종류를적 고 ON 을이용하여조인조건을명시한다. Chapter 03 SQL 기초 159

38 3.3.2 부속질의 _SQL 문내에또다른 SQL 문을작성해보자 가장비싼도서의이름은얼마인가? 라는질문에대한답을구한다고생각해보자. 가장비싼도서의가격은다음과같이구할수있으며답은 35,000원이다. SELECT FROM MAX(price) Book; 만약가장비싼도서의가격을알고있다면다음과같이가격이 35,000원인도서의이름을바로검색하면된다. SELECT FROM WHERE bookname Book price=35000; 위두질의를하나의질의로작성할수있을까? 가능하다. 두번째질의의 35,000 값위치에첫번째질의를대치하면된다. 질의 3-28 가장비싼도서의이름을보이시오. SELECT FROM WHERE bookname Book price = (SELECT MAX(price) FROM Book); 위와같이 SELECT 문의 WHERE 절에또다른테이블결과를이용하기위해다시 SELECT 문을괄호로묶는것을부속질의 subquery 라고한다. 부속질의는질의가중첩되어있다는의미에서중첩질의 nested query 라고도한다. 부속질의의실행순서는 [ 그림 3-18] 과같이 1 WHERE 절의부속질의를먼저처리하고 2 전체질의를처리한다. 160 Part 02 데이터베이스프로그래머

39 그림 3-18 부속질의의실행순서 부속질의는 SQL 문이다. SQL 문의결과는테이블이며테이블의결과는다음네가지중하나가된다. 단일행- 단일열 (1 1), 다중행-단일열 (n 1), 단일행- 다중열 (1 n), 다중행-다중열 (n n) [ 질의 3-28] 에서는부속질의의결과가단일행- 단일열 (1 1) 으로한개의결과를반환한다. 부속질의의결과가다중행- 단일열 (n 1) 으로여러개의값을반환하면앞에서배운 IN 키워드를사용한다. 예를들어도서를구매한적이있는고객의이름을알려면어떻게해야할까? 도서를구매한적이있는고객은여러명이므로다중행- 단일열 (n 1) 이된다. 따라서먼저 Orders 테이블에서주문내역을살펴보고, 주문한고객의고객번호를찾아야한다. SELECT FROM custid Orders; Orders 테이블에서도서를주문한고객번호를찾으면 {1, 2, 3, 4} 이다. 박세리를제외한모든고객이도서를구매한적이있다. 다음으로찾은고객번호를이용하여 Customer 테이블에서고객의이름을찾는다. SELECT name FROM Customer WHERE custid IN (1, 2, 3, 4); 이제두질의를하나로합쳐보자. 두번째질의의 IN 뒤에첫번째질의문을대치하면다음과같다. Chapter 03 SQL 기초 161

40 질의 3-29 도서를구매한적이있는고객의이름을검색하시오. SELECT name FROM Customer WHERE custid IN (SELECT custid FROM Orders); 세개이상의중첩된부속질의도가능하다. 예를들어대한미디어에서출판한도서를구매한고객의이름을알고싶다고하자. 먼저 Book 테이블에서출판사가대한미디어인도서번호를구하고, 이도서번호를이용하여 Orders 테이블에서도서를주문한고객의고객번호를구한다. 다음으로고객번호를이용하여 Customer 테이블에서고객의이름을구한다. SQL 문에서는다음과같이두개의부속질의를사용하여작성한다. 질의 3-30 대한미디어에서출판한도서를구매한고객의이름을보이시오. SELECT name FROM Customer WHERE custid IN (SELECT custid FROM Orders WHERE bookid IN (SELECT bookid FROM Book WHERE publisher=' 대한미디어 ')); 위문법을이해하려면아래에서부터읽어야한다. 1 마지막 SELECT 문에서는대한미디어에서출판한도서의 bookid를구한다. 2 가운데 SELECT 문에서는 bookid를주문한고객의 custid를구한다. 3 맨위 SELECT 문에서는찾은 custid에대한 name을구한다. [ 그림 3-19] 는 3단계부속질의의실행순서이고 [ 그림 3-20] 은데이터의예를보인것이다. 162 Part 02 데이터베이스프로그래머

41 SELECT name FROM Customer WHERE custid IN (SELECT custid FROM Orders WHERE bookid IN (SELECT bookid FROM Book WHERE publisher ) 그림 단계부속질의의실행순서 그림 단계부속질의의실행순서와데이터예 부속질의간에는상하관계가있으며, 실행순서는하위부속질의를먼저실행하고그결과를이용하여상위부속질의를실행한다. 반면상관부속질의 correlated subquery 는상위부속질의의투플을이용하여하위부속질의를계산한다. 즉상위부속질의와하위부속질의가독립적이지않고서로관련을맺고있다. Chapter 03 SQL 기초 163

42 예를들어출판사별로출판사의평균도서가격보다비싼도서를구한다고하자. 출판사굿스포츠를살펴보면모두세권의도서를출판하였고, 세권의가격평균은 7,000원이며, 세권중 7,000원보다비싼도서는 피겨교본 이다. 이문제는평균을구한후평균보다비싼도서를구해야하기때문에부속질의없이단독 SQL 문으로작성하기어렵다. 하위부속질의는상위부속질의에서도서와출판사가주어지면종속적으로출판사의도서평균을구한다. 상위부속질의는이도서가평균보다비싼지비교한다. 질의 3-31 출판사별로출판사의평균도서가격보다비싼도서를구하시오. SELECT b1.bookname FROM Book b1 WHERE b1.price > (SELECT avg(b2.price) FROM Book b2 WHERE b2.publisher=b1.publisher); 위 SQL 문을보면상위부속질의에사용된 Book 테이블을 b1으로별칭하고, 하위부속질의에사용된 Book 테이블을 b2로별칭한다. [ 그림 3-21] 은데이터예를보인것이다. 그림 3-21 상관부속질의의데이터예 164 Part 02 데이터베이스프로그래머

43 여기서잠깐투플변수 테이블이름이길거나한개의테이블이 SQL 문에두번사용될때혼란을피하기위해테이블의별칭을붙여사용하 는데, 이를투플변수 (tuple variable) 라한다. 투플변수는 FROM 절의테이블이름뒤에표기한다. 부속질의와조인은여러테이블을하나의 SQL 문에서다루는점은같지만차이점이있다. 부속질의는 SELECT 문에나오는결과속성을 FROM 절의테이블에서만얻을수있고, 조인은조인한모든테이블에서결과속성을얻을수있다. 조인은부속질의가할수있는모든것을할수있다. 그러나부속질의를조인을이용해서작성해보면부속질의만의편리함을알수있다. 한개의테이블에서만결과를얻는여러테이블질의는조인보다부속질의로작성하는것이훨씬편하다 집합연산 _ 도서를주문하지않은고객을알고싶다 SQL 문의결과는테이블로나타난다. 테이블은튜플의집합이므로테이블간의집합연산을이용하여합집합, 차집합, 교집합을구할수있다. SQL에서집합연산은합집합을 UNION, 차집합을 EXCEPT( 다른 DBMS에서는 MINUS를사용한다 ), 교집합을 INTERSECT로나타낸다. 예를들어보자. Customer 테이블에는도서를주문한고객과주문하지않은고객이같이있다. 도서를주문하지않은고객의이름은어떻게알수있을까? 집합으로보면다음과같다. { 도서를주문하지않은고객 } = { 모든고객 } - { 도서를주문한고객 } 모든고객의이름은 Customer 테이블에서다음과같이구할수있다. SELECT FROM name Customer; 그림 3-22 모든고객 Chapter 03 SQL 기초 165

44 도서를주문한고객은 Orders 테이블에기록이있는고객번호를이용하여 Customer 테이 블에서구할수있다. SELECT FROM WHERE name Customer custid IN (SELECT custid FROM Orders); 그림 3-23 도서를주문한고객 도서를주문하지않은고객은위의두 SQL 문을이용하여구한다. 질의 3-32 도서를주문하지않은고객의이름을보이시오. SELECT FROM EXCEPT SELECT FROM WHERE name Customer name Customer custid IN (SELECT custid FROM Orders); EXISTS_ 주문이있는고객을알고싶다 EXISTS는상관부속질의문형식이다. EXISTS는원래단어에서의미하는것과같이조건에맞는튜플이존재하면결과에포함시킨다. 즉부속질의문의어떤행이조건에만족하면참이다. 반면 NOT EXISTS는부속질의문의모든행이조건에만족하지않을때만참이다. EXISTS와 NOT EXISTS는상관부속질의문의다른형태다. 예를들어도서를주문한고객의이름을찾으려면고객중 Orders 테이블에고객번호가있는지확인하면된다. 166 Part 02 데이터베이스프로그래머

45 질의 3-33 주문이있는고객의이름과주소를보이시오. SELECT name, address FROM Customer cs WHERE EXISTS (SELECT * FROM Orders od WHERE cs.custid =od.custid); EXISTS는상관부속질의문형식이기때문에 SELECT 문처리는내포된다. 실행순서는 1 cs의첫행을가져와서부속질의문에 cs 값으로입력한다. 부속질의문 od의어떤행에서 cs 의고객번호와같은것을찾으면 EXISTS는참이되어 cs의첫행에대한 name과 address 가반환된다. 2 다음으로 cs의두번째행이부속질의문에입력된다. 같은방식으로부속질의문에서 SELECT 문이처리되고 EXISTS가참인지거짓인지판단한다. 참이면두번째행에대한 name과 address가반환된다. 이과정은 cs의모든행에대하여반복된다. Customer Orders 그림 3-24 EXIST 상관부속질의문의데이터예 Chapter 03 SQL 기초 167

46 04 데이터정의어 데이터를저장하려면먼저데이터를저장할테이블의구조를만들어야한다. SQL의데이터정의어 DDL, Data Definition Language 는바로이구조를만드는명령이다. 예를들어사람이집에서살려면집 ( 테이블구조정의 ) 을먼저지은후사람 ( 데이터입력 ) 이들어가서살수있는것과같다. 데이터정의어는테이블의구조를만드는 CREATE 문, 구조를변경하는 ALTER 문, 구조를삭제하는 DROP 문이있다. 3 절에서는이미만들어진테이블을이용하여데이터를검색하는방법을배웠다면, 4 절에서는테이블을만드는방법을배운다. 4.1 CREATE 문 CREATE 문은테이블을구성하고, 속성과속성에관한제약을정의하며, 기본키및외래키를정의하는명령이다. 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]] } ) 168 Part 02 데이터베이스프로그래머

47 문법에서대문자는키워드, { } 안의내용은반복가능, [ ] 은선택적사용, 는 1개선택, < > 은해당되는문법사항이있음을나타낸다. 편의상콤마 (,) 기호는생략하였다. 또 NOT NULL 은 NULL 값을허용하지않은제약, UNIQUE는유일한값에대한제약, DEFAULT는기본값설정, CHECK는값에대한조건을부여할때사용한다. PRIMARY KEY는기본키를정할때사용하고 FOREIGN KEY는외래키를지정할때사용하며, ON UPDATE와 ON DELETE는외래키속성의수정과투플삭제시동작을나타낸다. 질의 3-34 다음과같은속성을가진 NewBook 테이블을생성하시오. 정수형은 INT를사용하며문자형은가변형문자타입인 VARCHAR을사용한다. bookid( 도서번호 ) - INT bookname( 도서이름 ) - VARCHAR(20) publisher( 출판사 ) - VARCHAR(20) price( 가격 ) - INT CREATE TABLE NewBook ( bookid bookname publisher price INT, VARCHAR(20), VARCHAR(20), INT); 작성한 NewBook 테이블에는아무제약사항이없다. 만약기본키를지정하고싶다면다음 과같이생성한다. 기본키속성에괄호는필수다. CREATE TABLE NewBook ( bookid INT, bookname VARCHAR(20), publisher VARCHAR(20), price INT PRIMARY KEY (bookid)); 위 CREATE 문은다음과같이써도같은의미가된다. CREATE TABLE NewBook ( bookid INT PRIMARY KEY, bookname publisher price VARCHAR(20), VARCHAR(20), INT); Chapter 03 SQL 기초 169

48 만약 bookid 속성이없어서두개의속성 bookname, publisher 가기본키가된다면다음 과같이괄호를사용하여복합키를지정한다. CREATE TABLE NewBook ( bookname VARCHAR(20), publisher VARCHAR(20), price INT PRIMARY KEY (bookname, publisher)); NewBook 테이블의 CREATE 문에좀더복잡한제약사항을추가하면다음과같다. bookname 은 NULL 값을가질수없고, publisher 는같은값이있으면안된다. price 에값이입력되지않을경우 기본값 을저장한다. 또가격은최소 1,000 원이상으로한다. CREATE TABLE NewBook ( bookname VARCHAR(20) NOT NULL, publisher VARCHAR(20) UNIQUE, price INT DEFAULT CHECK(price > 1000), PRIMARY KEY (bookname, publisher)); 질의 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)); 170 Part 02 데이터베이스프로그래머

49 질의 3-36 다음과같은속성을가진 NewOrders 테이블을생성하시오. orderid( 주문번호 ) - INT, 기본키 custid( 고객번호 ) - INT, NOT NULL 제약조건, 외래키 (NewCustomer.custid, 연쇄삭제 ) bookid( 도서번호 ) - INT, NOT NULL 제약조건 saleprice( 판매가격 ) - INT orderdate( 판매일자 ) - DATE CREATE 문에서외래키를생성해보자. NewOrders 테이블은고객의주문사항을저장하 며, 속성으로 orderid, custid, bookid, saleprice, orderdate 를갖는다. orderid 는주문 번호를나타내는기본키이며, custid 는 NewCustomer 의 custid 를참조하는외래키이다. 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); 외래키제약조건을명시할때는주의할점이있다. 반드시참조되는테이블 ( 부모릴레이션 ) 이존재해야하며참조되는테이블의기본키여야한다. 외래키지정시 ON DELETE 또는 ON UPDATE 옵션은참조되는테이블의튜플이삭제되거나수정될때취할수있는동작을지정한다. 위 CREATE 문은 ON DELETE CASCADE 옵션으로외래키 custid를설정하였다. 이경우참조되는 NewCustomer 테이블의튜플 (NewCustomer.custid=3일경우라고가정 ) 이삭제되면참조하는 NewOrders 테이블의해당튜플 (NewOrders.custid=3) 이연쇄삭제 CASCADE 된다. NO ACTION은어떠한동작도취하지않고, SET NULL은 NULL 값으로바꾸며, SET DEFAULT는정해진값으로바꾼다. 외래키의삭제와수정시선택가능한옵션은 2장의참조무결성제약조건에서설명하였다. 참조무결성제약조건은 88 쪽을참고하기바란다. 한편속성의데이터타입부분에는 INT, VARCHAR와같은데이터타입을명시한다. SQL Server에서사용하는데이터타입의종류는 [ 표 3-7] 과같다. Chapter 03 SQL 기초 171

50 표 3-7 속성의데이터타입종류데이터타입 설명 비슷한타입 INT 정수형, 크기는 ± 2 31 BIGINT, SMALLINT NUMERIC(p, s) 실수형 p자리정수, s자리소수 DECIMAL(p, s) CHAR(n) VARCHAR(n) 문자형고정길이 문자형가변길이 DATE 날짜형, 기본형은 YYYY-MM-DD DATETIME, TIME 데이터타입의상세내용은 를참고하기바란다. 여기서잠깐 CREATE DATABASE 문 CREATE TABLE 문을사용하려면먼저테이블이저장될데이터베이스가있어야한다. 파일을저장하려면폴더가있어야하는것과같다. 데이터베이스를생성하려면 CREATE DATABASE 문을이용한다. 생성된데이터베이스를사 용하려면 USE 명령어를사용한다. Madang 데이터베이스를생성하고사용하려면다음과같은명령어를사용한다. CREATE DATABASE Madang USE Madang CREATE TABLE Book(... Madang 데이터베이스는부록 A.4 에서이미생성하였다. 4.2 ALTER 문 ALTER 문은생성된테이블의속성과속성에관한제약을변경하며, 기본키및외래키를변 경한다. ALTER 문의문법은다음과같다. ALTER TABLE 테이블이름 [ADD 속성이름데이터타입 ] [DROP COLUMN 속성이름 ] [ALTER COLUMN 속성이름데이터타입 ] [ALTER COLUMN 속성이름 [NULL NOT NULL]] [ADD PRIMARY KEY( 속성이름 )] [[ADD DROP] 제약이름 ] ALTER 문에서 ADD, DROP 은속성을추가하거나제거할때사용한다. DEFAULT 는속성 의기본값을설정하거나삭제할때사용한다. 그리고 ADD < 제약이름 >, DROP < 제약이름 > 172 Part 02 데이터베이스프로그래머

51 은제약사항을추가하거나삭제할때사용한다. < 제약이름 > 은 PRIMARY, FOREIGN, NOT NULL, UNIQUE 등이있다. 다음은앞에서실습한 NewBook 테이블을생성하는 CREATE 문이다. CREATE TABLE NewBook ( bookid INT, bookname VARCHAR(20), publisher VARCHAR(20), price INT); 질의 3-37 NewBook 테이블에 VARCHAR(13) 의자료형을가진 isbn 속성을추가하시오. NewBook 테이블에 isbn 속성을추가하려면다음과같이 ALTER 문을사용해야한다. 테이블을수정하려면테이블을삭제하고다시생성해도되지만테이블에저장된데이터를그대로두고변경할때는 ALTER 문을사용한다. 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 속성을기본키로변경하시오. Chapter 03 SQL 기초 173

52 기본키는 NOT NULL 속성만가능하다. ALTER TABLE NewBook ADD PRIMARY KEY(bookid); 4.3 DROP 문 DROP 문은테이블을삭제하는명령이다. DROP 문은테이블의구조와데이터를모두삭제하므로사용에주의해야한다. 데이터만삭제하려면 DELETE 문을사용한다. DROP 문의문법은다음과같다. DROP TABLE 테이블이름 DROP 문의예는다음과같다. 질의 3-42 NewBook 테이블을삭제하시오. DROP TABLE NewBook; 질의 3-43 NewCustomer 테이블을삭제하시오. 만약삭제가거절된다면원인을파악하고관련된테이블을같 이삭제하시오. DROP TABLE NewCustomer; 삭제하려는테이블의기본키를다른테이블에서참조하고있다면삭제가거절될수있다. 삭제를하기위해서는참조하고있는테이블부터삭제해야한다. DROP TABLE NewOrders; DROP TABLE NewCustomer; 174 Part 02 데이터베이스프로그래머

53 05 데이터조작어 - 삽입, 수정, 삭제 지금까지 SELECT 문을사용하여검색하는방법과 CREATE 문을사용하여테이블의구조를만드는방법을배웠다. 이절에서는만들어진테이블에투플을삽입, 삭제, 수정하는방법을배운다. 4절에서데이터정의어를사람이살기위해집을짓는것에비유했다면, 이절의데이터조작어는만들어진집에사람을들여보내고 INSERT, 내보내는 DELETE 것과같다. 5.1 INSERT 문 INSERT 문은테이블에새로운튜플을삽입하는명령이다. 문법은다음과같다. INSERT INTO 테이블이름 [( 속성리스트 )] VALUES ( 값리스트 ); 질의 3-44 Book 테이블에새로운도서 스포츠의학 을삽입하시오. 스포츠의학은한솔의학서적에서출간했으며가격은 90,000원이다. INSERT INTO Book(bookid, bookname, publisher, price) VALUES (11, ' 스포츠의학 ', ' 한솔의학서적 ', 90000); 결과를확인하기위해서는 SELECT * FROM Book; 명령을실행해야한다. Chapter 03 SQL 기초 175

54 새로운투플을삽입할때속성의이름은생략할수있다. 단, 데이터의입력순서는속성의순서와일치해야한다. INSERT INTO Book VALUES (11, ' 스포츠의학 ', ' 한솔의학서적 ', 90000); 데이터는항상속성의순서대로입력하지않아도된다. 만약 price를 publisher 앞에입력하고싶다면속성의이름과데이터의순서를바꾸면된다. INSERT INTO Book(bookid, bookname, price, publisher) VALUES (11, ' 스포츠의학 ', 90000, ' 한솔의학서적 '); 만약몇개의속성만입력해야한다면해당되는속성만명시하면된다. 예를들어도서의가격을몰라 bookid, bookname, publisher만입력해야할경우다음과같이작성한다. 질의의결과도서의가격은 0이아닌 NULL이라는특수한값으로저장된다. 질의 3-45 Book 테이블에새로운도서 스포츠의학 을삽입하시오. 스포츠의학은한솔의학서적에서출간했으며가격은미정이다. INSERT INTO Book(bookid, bookname, publisher) VALUES (12, ' 스포츠의학 ', ' 한솔의학서적 '); INSERT 문은 SELECT 문을사용하여작성할수도있다. 이는한꺼번에여러개의튜플을삽입하는방법으로대량삽입 bulk insert 이라고도한다. 예를들어 Imported_book이라는테이블에저장된데이터를읽어서 Book 테이블에삽입하려면다음과같이작성한다. 이때 Imported_book(bookid, bookname, price, publisher) 테이블에는 Book 테이블과같 176 Part 02 데이터베이스프로그래머

55 은타입의데이터가저장되어있어야한다. 질의 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 < 검색조건 >]; 질의 3-47 Customer 테이블에서고객번호가 5 인고객의주소를 대한민국부산 으로변경하시오. UPDATE SET WHERE Customer address=' 대한민국부산 ' custid=5; 결과를확인하기위해서는 SELECT * FROM Customer; 명령을실행해야한다. Chapter 03 SQL 기초 177

56 UPDATE 문은다른테이블의속성값을이용할수도있다. 예를들어 Customer 테이블의 박세리고객의주소를김연아고객의주소로바꿀경우다음과같이 SELECT 문을넣어작성 한다. 질의 3-48 Customer 테이블에서박세리고객의주소를김연아고객의주소로변경하시오. UPDATE SET Customer address = (SELECT address FROM Customer WHERE name=' 김연아 ') WHERE name=' 박세리 '; 여기서잠깐 UPDATE 문작성시주의사항 UPDATE 문에서여러속성값을한꺼번에수정하는작업은가능하다. 그러나잘못사용하면위험하다. 예를들어다음 SQL 문을보자. UPDATE SET Customer address=' 대한민국서울 '; 위 SQL 문은고객번호가 5 인고객의주소를변경하고자의도했다가잘못작성되어엉뚱한결과가나온예다. 모든고 객의주소가 대한민국서울 로바뀐것이다. 잘못수정된데이터는다시원래대로되돌리는방법이있기는하지만경우에따라문제를일으킬수있으므로주의해야한다. 178 Part 02 데이터베이스프로그래머

57 5.3 DELETE 문 DELETE 문은테이블에있는기존튜플을삭제하는명령이다. 문법은다음과같다. DELETE FROM 테이블이름 [WHERE 검색조건 ]; DELETE 문은 < 검색조건 > 에해당되는투플을삭제한다. < 검색조건 > 이없으면모든투플을삭제한다. 질의 3-49 Customer 테이블에서고객번호가 5 인고객을삭제하시오. DELETE FROM WHERE Customer custid=5; 위 SQL 문에서 WHERE 절을빼면, 고객테이블의모든튜플이삭제되고빈테이블만남는다. 질의 3-50 모든고객을삭제하시오. DELETE FROM Customer; 위 SQL 문은실행되지않는다. 그이유는 Orders 테이블에서 Customer.custid 속성을외래키로참조하고있기때문이다. 제약이해제되지않으면데이터삭제가중지된다. DROP 문은 DDL 문으로테이블의구조를삭제한다. 당연히데이터도같이삭제된다. DELETE 문은 DML 문으로테이블의구조는그대로두고데이터만삭제한다. Chapter 03 SQL 기초 179

58 요약 1 SQL Server 2012 익스프레스에디션 SQL Server 2012 버전은기업의업무용인엔터프라이즈에디션 (Enterprise Edition), 기본적인기능을제공하는스탠다드에디션 (Standard Edition), 무료로제공되는익스프레스에디션 (Express Edition) 이있다. 익스프레스에디션은상업용에디션에비해메모리사용량과데이터베이스크기등의차이가있지만 RDBMS 대부분의기능을사용할수있다. 2 SQL 3 4 SQL(Structured Query Language) 은 1970 년대후반 IBM 이 SEQUEL(Structured English QUEry Language) 이라는이름으로개발한관계형데이터베이스언어다. 데이터정의어 (DDL) 테이블이나관계의구조를생성하는데사용하며 CREATE, ALTER, DROP 문등이있다. 데이터조작어 (DML) 테이블에데이터를검색, 삽입, 수정, 삭제하는데사용하며 SELECT, INSERT, DELETE, UPDATE 문등이있다. 여기서 SELECT 문은데이터를조회하는명령어라하여특별히질의어 (query) 라고부른다. 5 WHERE 조건 WHERE 절다음에나올수있는조건으로사용할수있는술어 (predicate) 는비교, 범위, 집합, 패턴, NULL 등이있다. 6 집계함수 테이블의각열에대해계산을하는함수로 SUM, AVG, MIN, MAX, COUNT 의다섯가지가있다. 7 GROUP BY 속성의공통값에따라그룹을만드는데사용하는명령이다. 8 HAVING 9 조인 10 GROUP BY 절의결과나타나는그룹을제한하는역할을한다. 한테이블의행을다른테이블의행에연결함으로써두개이상의테이블을결합하는연산이다. 동등조인 ( 내부조인 ) 동등조건에의하여테이블을조인하는것을동등조인 (equi join) 이라고한다. 조인이라고하면대부분동등조인을말한다. 동등조인은내부조인 (inner join) 이라고도한다. 180 Part 02 데이터베이스프로그래머

59 11 부속질의 SELECT 문의 WHERE 절에또다른테이블결과를이용하기위해다시 SELECT 문을괄호로묶는것을부속질의 (subquery) 라고한다. 부속질의는질의가중첩되어있다는의미에서중첩질의 (nested query) 라고도한다. 상관부속질의 상위부속질의와하위부속질의가독립적이지않고상위부속질의의투플을이용하여하위부속질의를계산하는질의를말한다. 투플변수 FROM 절의테이블이름뒤에테이블의다른이름을붙여주는것을투플변수라고한다. 14 집합연산 SQL에서집합연산은합집합을 UNION, 차집합을 EXCEPT( 다른 DBMS에서는 MINUS를사용한다 ), 교집합을 INTERSECT로나타낸다. 15 EXISTS 원래단어에서의미하는것과같이조건에맞는튜플이존재하면결과에포함시킨다. EXISTS 는부속질의문의어떤행이조건에만족하면참이다. 반면 NOT EXISTS 는부속질의문의모든행이조건에만족하지않을때만참이다. 16 CREATE 테이블을구성하고, 속성과속성에관한제약을정의하며, 기본키및외래키를정의한다. 1 7 ALTER 생성된테이블의속성과속성에관한제약을변경하며, 기본키및외래키를변경한다. 1 8 DROP 테이블의구조와데이터를삭제한다. 1 9 INSERT 테이블에새로운튜플을삽입한다. 20 UPDATE 기존튜플에있는특정속성값을수정한다. 21 DELETE 테이블에있는기존튜플을삭제한다. Chapter 03 SQL 기초 181

60 연습문제 [ 기본문제 ] 1 절에포함된마당서점데이터베이스를바탕으로다음 1-5 번문제에답하시오. 부록에포함된스크립트를이용하여 Madang 데이터베이스를지우고다시생성하도록한다. 1 2 마당서점의고객이요구하는다음질문에대해 SQL 문을작성하시오. (1) 도서번호가 1인도서의이름 (2) 가격이 20,000원이상인도서의이름 (3) 박지성의총구매액 (4) 박지성이구매한도서의수 (5) 박지성이구매한도서의출판사수 (6) 박지성이구매한도서의이름, 가격, 정가와판매가격의차이 (7) 박지성이구매하지않은도서의이름마당서점의운영자와경영자가요구하는다음질문에대해 SQL 문을작성하시오. (1) 마당서점도서의총개수 (2) 마당서점에도서를출고하는출판사의총개수 (3) 모든고객의이름, 주소 (4) 2013년 7월 4일 ~7월 7일사이에주문받은도서의주문번호 (5) 2013년 7월 4일 ~7월 7일사이에주문받은도서를제외한도서의주문번호 (6) 성이 김 씨인고객의이름과주소 (7) 성이 김 씨이고이름이 아 로끝나는고객의이름과주소 (8) 주문하지않은고객의이름 ( 조인사용 ) (9) 주문금액의총액과주문의평균금액 (10) 고객의이름과고객별구매액 (11) 고객의이름과고객이구매한도서목록 (12) 도서의가격 (Book 테이블 ) 과판매가격 (Orders 테이블 ) 의차이가가장많은주문 (13) 도서의판매액평균보다자신의구매액평균이더높은고객의이름 182 Part 02 데이터베이스프로그래머

61 3 마당서점에서다음의심화된질문에대해 SQL 문을작성하시오. (1) 박지성이구매한도서의출판사와같은출판사에서도서를구매한고객의이름 (2) 두개이상의서로다른출판사에서도서를구매한고객의이름 (3) 전체고객의 30% 이상이구매한도서 4 다음질의에대해 DML 문을작성하시오. (1) 새로운도서 ( 스포츠세계, 대한미디어, 원 ) 이마당서점에입고되었다. 삽입이안될경우필요한데이터가더있는지찾아보자. (2) 삼성당 에서출판한도서를삭제해야한다. (3) 이상미디어 에서출판한도서를삭제해야한다. 삭제가안될경우원인을생각해보자. (4) 출판사 대한미디어 가 대한출판사 로이름을바꾸었다. 5 다음 EXISTS 질의의결과를보이시오. SELECT * FROM Customer c1 WHERE NOT EXISTS (SELECT * FROM Orders c2 WHERE c1.custid =c2.custid); (1) 질의의결과는무엇인가? (2) NOT 을지우면결과는무엇인가? Chapter 03 SQL 기초 183

62 [ 심화문제 ] 6 [ 영화관데이터베이스 ] 다음은 4 개의지점을가진극장데이터베이스다. 밑줄친속성은기본키이 다. 테이블의구조를만들고데이터를입력한후다음질의에대한 SQL 문을작성하시오. 테이 블의구조를만들때다음제약조건을반영하여작성한다. 테이블구조와데이터 제약조건 영화가격은 20,000 원을넘지않아야한다. 상영관번호는 1 부터 10 사이이다. 같은사람이같은좌석번호를두번예약하지않아야한다. (1) 단순질의 1 모든극장의이름과위치를보이시오. 2 잠실 에있는극장을보이시오. 3 잠실 에사는고객의이름을오름차순으로보이시오. 4 가격이 6,000원이하인영화의극장번호, 상영관번호, 영화제목을보이시오. 5 극장위치와고객의주소가같은고객을보이시오. (2) 집계질의 1 극장의수는몇개인가? 2 상영되는영화의평균가격은얼마인가? 년 9월 1일에영화를관람한고객의수는얼마인가? 184 Part 02 데이터베이스프로그래머

63 (3) 부속질의와조인 1 대한 극장에서상영된영화제목을보이시오. 2 대한 극장에서영화를본고객의이름을보이시오. 3 대한 극장의전체수입을보이시오. (4) 그룹질의 1 극장별상영관수를보이시오. 2 잠실 에있는극장의상영관을보이시오 년 9월 1일의극장별평균관람고객수를보이시오 년 9월 1일에가장많은고객이관람한영화를보이시오. (5) DML 1 각테이블에데이터를삽입하는 INSERT 문을하나씩실행시켜보시오. 2 영화의가격을 10% 씩인상하시오. 7 [ 판매원데이터베이스 ] 다음릴레이션을보고물음에답하시오. Salesperson 은판매원, Order 는 주문, Customer 는고객을나타낸다. 밑줄친속성은기본키이고 custname 과 salesperson 은 각각 Customer.name 과 Salesperson.name 을참조하는외래키이다. Salesperson(name, age, salary) Order(number, custname, salesperson, amount) Customer(name, city, industrytype) (1) 테이블을생성하는 CREATE 문과데이터를삽입하는 INSERT 문을작성하시오. (2) 모든판매원의이름과급여를보이시오. 단, 중복행은제거한다. (3) 나이가 30세미만인판매원의이름을보이시오. (4) S 로끝나는도시에사는고객의이름을보이시오. (5) 주문을한고객의수 ( 서로다른고객만 ) 를보이시오. (6) 판매원각각에대하여주문의수를계산하시오. (7) LA 에사는고객으로부터주문을받은판매원의이름과나이를보이시오 ( 부속질의를사용 ). (8) LA 에사는고객으로부터주문을받은판매원의이름과나이를보이시오 ( 조인을사용 ). (9) 두번이상주문을받은판매원의이름을보이시오. (10) 판매원 TOM 의봉급을 45,000원으로변경하는 SQL 문을작성하시오. Chapter 03 SQL 기초 185

64 8 [ 기업프로젝트데이터베이스 ] 다음릴레이션을보고물음에답하시오. Employee 는사원, Department 는부서, Project 는프로젝트, Works 는사원이프로젝트에참여한내용을나타낸다. 한사원이여러프로젝트에서일할수있고, 한프로젝트에여러사원이일할수있다. hours-worked 속성은각사원이각프로젝트에서일한시간수를나타낸다. 밑줄친속성은기본키이다. Employee(empno, name, phoneno, address, sex, position, deptno) Department(deptno, deptname, manager) Project(projno, projname, deptno) Works(empno, projno, hours-worked) (1) 모든사원의이름을보이시오. (2) 여자사원의이름을보이시오. (3) 팀장 (manager) 의이름을보이시오. (4) IT 부서에서일하는사원의이름과주소를보이시오. (5) 홍길동 팀장 (manager) 부서에서일하는사원의수를보이시오. (6) 사원들이일한시간수를부서별, 사원이름별오름차순으로보이시오. (7) 두명이상의사원이참여한프로젝트의번호, 이름, 사원의수를보이시오. (8) 세명이상의사원이있는부서의사원이름을보이시오. 186 Part 02 데이터베이스프로그래머

65 9 [ 사원데이터베이스 ] 다음은어느기업의사원데이터베이스다. 편의상데이터베이스이름을 COMPANY 라고부른다. COMPANY 데이터베이스의스키마는아래와같다. 다음질문에대해 SQL 문을작성하시오 ( 부록의마당서점샘플데이터베이스생성스크립트를수행하였다면이미생성되어있다. 데이터베이스를사용하기위해 USE COMPANY; 명령을실행한다 ). Dept(deptno INT, dname VARCHAR(14), loc VARCHAR(13)) Emp(empno INT, ename VARCHAR(10), job VARCHAR(9), MGR SMALLINT, hiredate DATE, sal DECIMAL(7,2), comm DECIMAL(7,2), deptno INT) Dept 는부서테이블로 deptno( 부서번호 ), dname( 부서이름 ), loc( 위치, location) 으로구성되어있다. Emp 는사원테이블로 empno( 사원번호 ), ename( 사원이름 ), job( 업무 ), MGR( 팀장 번호, manager), hiredate( 고용날짜 ), sal( 급여, salary), comm( 커미션금액, commission), deptno( 부서번호 ) 로구성되어있다. 밑줄친속성은기본키이고 Emp 의 deptno 는 Dept 의 deptno 를참조하는외래키이다. 다음과같이데이터베이스다이어그램을그리시오. SQL Server Management Studio 개체탐색기의 [ 데이터베이스 ] [COMPANY] [ 데이터베이스다이어그램 ] 을선택하고마우스오른쪽단추를눌러 [ 새데이터베이스다이어그램 ] 을선택하면된다. 다이어그램생성결과 Chapter 03 SQL 기초 187

66 (1) 사원의이름과직위를출력하시오. 단, 사원의이름은 사원이름, 직위는 사원직위 머리글이나오도록출력한다. (2) 30 번부서에근무하는모든사원의이름과급여를출력하시오. (3) 사원번호와이름, 현재급여와 10% 인상된급여 ( 열이름은 인상된급여 ) 를출력하시오. 단, 사원번호순으로출력한다. 증가된급여분에대한열이름은 증가액 으로한다. (4) S 로시작하는모든사원과부서번호를출력하시오. (5) 모든사원의최대및최소급여, 합계및평균급여를출력하시오. 열이름은각각 MAX, MIN, SUM, AVG 로한다. 단, 소수점이하는반올림하여정수로출력한다. (6) 업무이름과업무별로동일한업무를하는사원의수를출력하시오. 열이름은각각 업무 와 업무별사원수 로한다. (7) 사원의최대급여와최소급여의차액을출력하시오. (8) 30 번부서의구성원수와사원들급여의합계와평균을출력하시오. (9) 평균급여가가장높은부서의번호를출력하시오. (10) 세일즈맨을제외하고, 각업무별사원의총급여가 3,000 이상인각업무에대해서, 업무명과각업무별평균급여를출력하시오. 단평균급여의내림차순으로출력한다. (11) 전체사원가운데직속상관이있는사원의수를출력하시오. (12) Emp 테이블에서이름, 급여, 커미션금액, 총액 (sal + comm) 을구하여총액이많은순서대로출력하시오. 단, 커미션이 NULL인사람은제외한다. (13) 각부서별로같은업무를하는사람의인원수를구하여부서번호, 업무명, 인원수를출력하시오. (14) 사원이한명도없는부서의이름을출력하시오. (15) 같은업무를하는사람의수가네명이상인업무와인원수를출력하시오. (16) 사원번호가 7400 이상 7600 이하인사원의이름을출력하시오. (17) 사원의이름과사원의부서를출력하시오. (18) 사원의이름과팀장의이름을출력하시오. (19) 사원 SCOTT 보다급여를많이받는사람의이름을출력하시오. (20) 사원 SCOTT가일하는부서번호혹은 DALLAS에있는부서번호를출력하시오. 188 Part 02 데이터베이스프로그래머

67 10 [AdventureWorksLT 데이터베이스 ] 다음은 SQL Server 에서제공하는샘플데이터 베이스 AdventureWorks 를단순화한 AdventureWorksLT 를이용한질의들이다. AdventureWorksLT 데이터베이스의설치는부록 A.5 를참고하여설치하기바란다. AdventureWorksLT 는열두개의테이블로구성되어있다. 그중열개의중요한테이블에대한스키마는다음과같다. CustomerAW(CustomerID, FirstName, MiddleName, LastName, CompanyName, Address) CustomerAddress(CustomerID, AddressID, AddressType) Address(AddressID, AddressLine1, AddressLine2, City, StateProvince, CountyRegion, PostalCode) SalesOrderHeader(SalesOrderID, RevisionNumber, OrderDate, CustomerID, BillToAddressID, ShipToAddressID, ShipMethod, SubTotal, TaxAmt, Freight) SalesOrderDetail(SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount) ProductAW(ProductID, Name, Color, ListPrice, Size, Weight, ProductModelID, ProductCategoryID) ProductModel(ProductModelID, Name) ProductCategory(ProductCategoryID, ParentProductCategoryID, Name) ProductModelProductDescription(ProductModelID, ProductDescriptionID, Culture) ProductDescription(ProductDescriptionID, Description) (1) 각테이블에대하여기본키, 외래키를표시하시오. (2) James D. Kramerd 의회사이름을찾으시오. (3) 고객 (CustomerLT) 에게안내문을발송하기위하여고객정보를출력하고자한다. 고객의이름, 메일주소, 전화번호를출력하시오. (4) 마케팅부서에서상품을고가순으로출력하고자한다. 상품의번호, 가격, 이름을고가순으로출력하시오. (5) Bike World 사와메일로연락을하고자한다. Bike World 사의메일주소를출력하시오. (6) 판매원 jillian 과 jose 의고객이름을모두출력하시오. (7) 상품 socks 와 tights 에대하여가격대가 7~100( 파운드 ) 이고, 사이즈가 M, L 인상품을모두출력하시오. (8) 상품이름에 Socks 가포함된모든상품을찾으시오. (9) PRODUCT 테이블에서 size 와 weight 가 NULL 인상품을모두찾으시오. (10) 연도별상품총판매액을출력하시오. Chapter 03 SQL 기초 189

68 지금부터는영어로기술된질의다. 질의에해당하는 SQL 문을작성하시오. (11) A Single Item Order is a customer order where only one item is ordered. Show the SalesOrderID and the UnitPrice for every Single Item Order. (12) Where did the racing socks go? List the product name and the CompanyName for all Customers who ordered ProductModel Racing Socks. (13) Show the product description for culture fr for product with ProductID 736. (14) Use the SubTotal value in SaleOrderHeader to list orders from the largest to the smallest. For each order show the CompanyName and the SubTotal and the total weight of the order. (15) How many products in ProductCategory Cranksets have been sold to an address in London? (16) For every customer in Dallas show AddressLine1 of the Main Office and AddressLine1 of the Shipping address - if there is no shipping address leave it blank. Use one row per customer. (17) For every customer in Dallas show AddressLine1 of the Main Office and AddressLine1 of the Shipping address - if there is no shipping address leave it blank. Use one row per customer. (18) Show the best selling item by value. (19) Show how many orders are in the following ranges (in $): RANGE Num Orders Total Value 0 ~ ~ ~ ~ (20) Identify the three most important cities. Show the break down of top level product category against city. 190 Part 02 데이터베이스프로그래머