4.1 SQL 소개

• SQL: Structured Query Language – S-Q-L(에쓰 큐 엘)로 읽는다. – Sequel(씨퀄)이 아님 - 옛날 이름 참고. Sequel 의 뜻: 속편, 결과 – SQL의 뜻: "구조화된 질의어" * 과목 누리집에서: iSQL*PLUS, 오라클을 인터넷으로 접속하기 (in HWP) iSQL*PLUS Quick Reference ;

1) Web browser 에서 (보기를 들어) - http://sinsi.pnu.kr:7777/isqlplus -> Oracle login: username, PW (비번) -> Screen mode . Sinsi: 지금은 안 됨.

2) Linux 로 먼저 login % sqlplus /* SQL*Plus */ -> Oracle login: username, PW (비번) -> Line mode

Oracle 10g Express (Win7) 설치

• 약 200 MB, 설치 시간: 5 ~ 15 분쯤?

1) 아래 주소에서 바로 내려받아도 됨 (회원 가입 해야 함, 11g release 2) • http://www.oracle.com/technetwork/kr/index.html

• http://www.oracle.com/technetwork/products/express edition/downloads/index.html

2) 과목 누리집에서 내려 받아도 됨.

Win/XP 용 Oracle 10g Express 설치 파일 OracleXEUniv.exe 내려받기 (학교 network 이 느릴 수도 있음…) 3) USB로 copy: 가장 빠름 ^-^

• SQL에 들어있는 주요 기능 1) 스키마를 정의하는 자료 정의어 (DDL:data definition language) 2) 질의어 (Query) 또는 자료 조작어 (DML:data manipulation language) • 질의어의 두 가지 종류 1) 상호 작용적(interactive) 자료 조작어 2) 내포된(embedded) 자료 조작어 - 보기: C program 안에 있는 SQL

4.2 자료형(domain type)

• 자료를 테이블에 담으려면 – 테이블의 속성에 대한 자료의 형(도메인)등 키마를 정의 하는 DDL (create table) 명령을 스 먼저 배워야 함 – 보기: create table 테이블_이름 ( 속성_이름1 자료_형1, 속성_이름2 자료_형2); create table 학생 ( 학번 varchar2 (9), 이름 varchar2 (20));

• 많이 쓰는 자료형 – char(n): fixed-length character string – varchar2(n): variable-length character string – int, smallint – numeric(p,d), number(p,d), decimal(p,d), dec(p,d): 보기 number (7, 2), 모두 일곱 자리 가운데 소 수점 아래에 2 자리, 정수 7-2=5 자리 – real, double, float(p)

4.3 create table 명령을 써서 스키마 정의하기 • 스키마를 정의하는 명령은 create table 스키마를 지우는 명령은 drop table - 이미 있는 표와 같은 이름의 표를 만들고 자 하면 (create table)  error . Drop table 먼저 한 뒤에 create table - Drop table: 스키마가 없어지면, 그 표의 내용 (instance, 그 표 안의 모든 tuple)도 당연히 다 없어짐.

- Create table 명령의 일반 형식: 1) 속성 및 자료 형, 2) PK, 3) Unique (  CK), 4) FK create table 테이블_이름 ( 속성_이름1 자료_형1, 속성_이름2 자료_형2, primary key (속성11, 속성12, ...), unique (속성21, 속성22, ...), foreign key (속성31, 속성32, ...) references 테이블_이름2); - Drop table table_이름 보기: drop table stu_tbl

• primary key – 어떤 테이블에 primary key(일차 키) 절이 나오면, 그 키 값이 꼭 같은 투플이 그 테이블 안에 두 개 이상 있을 (들어갈) 수 없다.

- Unique 절이 나와도 마찬가지임.

- PK 값으로 NULL을 넣을 수 없음  create table stu_uk ( stu_id varchar2 (9), stu_name varchar2(20), stu_rrn varchr2 (13) primary key (stu_id), unique (stu_rrn) ); insert into stu_uk values ('111', '박 두리', '701') insert into stu_uk values ('111', '연 놀부', '702') ERR insert into stu_uk values ('333', '임 꺽정', '701') ERR  ERROR: unique constraint violated

• unique (unique key 가 아니라…) – 어떤 테이블에 unique (유일한 ) 절이 나오면, 그 값이 꼭 같은 투플이 그 테이블 안에 두 개 이상 있을 (들어갈) 수 없다 . – 어떤 테이블에 후보 키가 두 개 이상 있을 때, 일 차 키가 되지 못한 키 를 unique 로 지정하면 된다.

– 보기: 학번 – primary key, 주민 등록 번호 – unique (candidate key) - Unique key에는 NULL 값을 넣을 수 있다.

- Insert into stu_uk values (NULL, ‘이름’, ‘주번’) XX - Insert into stu_uk values (‘PK’, ‘이름’, NULL) OO

• foreign key Create table 수강 (학번 varchar2 (9) not null, 과목번호 varchar2 (7) not null, primary key (학번, 과목번호), foreign key (학번) references 학생, foreign key (과목_번호) references 과목 ) Create table 과목 (과목_번호 varchar2 (7), …) - 과목과 학생 들어야 표를 먼저 만든 뒤에 수강 표를 만 * 이유: forward reference 못 함 Create table 학생 (학번 varchar2 (9), …)

• 테이블에 투플 넣기: insert into 명령 insert into 테이블이름 values(속성값1, 속성값 2 ...);  insert into stu_uk values ('111', '박 두리', '701')

• 테이블을 복사하기: 실습할 때 좋음.

- 스키마 + instance 가 그대로 베껴짐.

create table 학생_tmp as select * from 학생; - Oracle에서는 되지만, 안 되는 DBMS도 있음.

4.4 select-from-where 명령 실행 원리 [중요]

• select 명령 기본 형식 select 속성1, 속성2, ... from 테이블1, 테이블2, ...

where 조건1 and/or 조건2 ...

• 학번이 9902101인 투플을 선택하는 select 명령 select 학번, 이름 from 학생 where 학번 = '9902101';

• SQL select 명령이 수행되는 과정을 관계 형 대수로 나타내면 다음과 같다 ∏ 속성1, 속성2, ...

( σ 조건1 and/or 조건2 ...

(테이블1 X 테이블2 ...)) 1) X (cartesian product) - 보기: 표가 세 개이면 (T1 X T2) X (T3) 2) σ: Select (원하는 투플만 고른다) 3) ∏: Project (원하는 속성만 보여준다)

• Select 명령의 세 단계.

1) from 절에는, 이 select 명령에서 쓰고자 하는 테이블 이름이 나오는데 • 테이블(엄밀하게 말하면 투플 변수 이름)이 둘 이 상 있을 때 "개념적으로" 모든 테이블(투플 변수가 가리키는 테이블)의 카티전 곱 을 다음 단계로 넘긴다 연산의 결과 테이블 • from 학생, 수강  from 학생 학생 , 수강 수강 처음 학생 ( table name ), 둘째 학생 (tuple var.) 보기: from 학생 학생1, 학생 학생2  학생 X 학생 * From 절에서 투플 변수가 하나뿐이면 이 과정은 그냥 넘어감.

2) where 절에는 조건이 나오는데, 관계 대수의 선 택(σ, select) 연산을 한다.

• 조건이 여러 개 있으면, 원하는 바에 따라 and 또 는 or로 붙이고, 또는 not을 쓸 수도 있음.

• where 절은 반드시 있어야 하는 것은 아니지만, 실제로는 거의 모든 select 명령에 where 절이 있음.

• From 에서 넘어온 표에서, 이 조건에 맞는 투플만 으로 이루어진 테이블을 다음 단계로 넘긴다.

- Where 절이 없으면, from에서 넘어온 표의 모든 투플이 다음 단계로 넘어간다.

3) Select 명령: 원하는 속성만 고른다.

select 명령 바로 뒤에 나오는 "속성1, 속성 2, ..."는 관계 대수의 속성 추출 연산(∏)을 한다 • 위의 둘째 단계에서 넘어온 테이블에 대하 여 필요한 속성만 추출한 결과가 이 select 명령의 최종 결과이다.

• 참고.

σ 와 SELECT 는 다르다 !!!

Relational algebra σ: SQL where Relational algebra ∏: SQL SELECT

4.5 select 명령에서 속성 추출하기

• 개념적으로 테이블은 투플의 집합이므로, 꼭 같은 투플이 한 테이블 안에 있어서는 안 된다. • 실제 많은 데이터베이스 관리 시스템에서 테이블 안에 꼭 같은 투플을 허용한다

• 같은 투플을 왜 허용할까?

• 꼭 같은 투플을 한 테이블 안에 허용하는 이유 – 만일 테이블 안에 꼭 같은 투플을 허용하지 않으 려면 - 테이블 안에 새 투플을 넣거나, 있던 투플의 값이 바뀔 때마다,  그 투플이 다른 투플과 꼭 같지 않다는 것을 확인 해야 함 • 같은 투플이 없다고 확인하는 방법 – 정렬(sort) – 그와 비슷한 색인(index), 해쉬(hash) 등

• select 명령의 결과 테이블에서, 겹치는 투 플을 허용할지 말지는 다음과 같이 지정 select 속성1, ... : 겹치는 투플 허용함 select all 속성1, ... : 겹치는 투플 허용함 (같은 투플이 세 개 있으면 세 투플 모두 보여줌) select distinct 속성1, …: 겹치는 투플 한 번만 … select unique 속성1, …: 겹치는 투플 한 번만 … (같은 투플이 여러 개 있으면 한 번만 보여줌) * Oracle: distinct, unique 둘 다 허용함.

* DBMS에 따라 distinct, unique 한 개만 허용하기도 함.

수강 1 학번 9912345 9912345 9922222 9922222 9933333 과목번호 CS100 CS200 CS200 CS300 CS400

select 학번 from 수강; select all 학번 from 수강;

학번 9912345 9912345 9922222 9922222 9933333

• select distinct 명령은 겹치는 투플을 허용 하지 않음 select distinct 학번 from 수강; select unique 학번 from 수강;

학번 9912345 9922222 9933333

4.6 where 절에서 조건을 나타내기

• and, or, not: 고급 프로그래밍 언어에서와 같음.

• where 절에서 조건을 나타내는 보기 – where 평균평점 > 4.00

- where 이름 = '홍 길동' – where 과목번호 = 'CS100’ * DBMS 에 따라 ‘ 를 쓰기도 하고 “ 를 쓰기도 함 .

확인하고 써야 됨 . Oracle: ‘ ( 홑 따옴표 )

• SQL에서 자연 조인을 나타내기 아래 보기를 생각하자.

– 학생 = (학번, 이름), PK={학번} – 수강 = (학번, 과목번호), PK= {학번, 과목번호}, FK={학번} - 학생과 수강 사이의 자연 조인: 공통 (= 조인) 속성: {학번}

• 1) 학생과 수강 테이블의 카티전 곱: from 학생, 수강 • 2) 학생.학번과 수강.학번이 같은 투플을 고 르기: where 학생.학번 = 수강.학번 • 3) 공통 속성은 하나만(한 번만) 보여주기: select 학생.학번, 학생.이름, 수강.과목번호

• 위의 from, where, select 명령을 하나로 묶은 것 – 자연 조인에 대응하는 SQL 명령 select 학생.학번, 학생.이름, 수강.과목번호 from 학생, 수강 where 학생.학번 = 수강.학번;

4.7 속성의 이름을 정하거나 바꾸기 (renaming attributes [as]) • 속성에 이름이 없을 때 새 이름을 주거나 이미 속성의 이름이 있지만 다른 이름을 주 려면 – 속성 이름 바로 다음에 as 를 쓰고 – 그 뒤에 속성의 새 이름 또는 다른 이름을 적는다.

• 테이블 두 개에 꼭 같은 속성 이름이 각각 있을 때, 화면에 나오는 학번 속성 이름이 같게 나옴 (아래에서 학번).

select 학생.*, 수강.* from 학생, 수강 where 학생.학번 = 수강.학번; /* output */ 학번 111 이름 박 성혜 학번 과목_번호 111 CE11111 * 학번 속성 이름 두 개를 다르게 하려면…?

• 방안: 학생.학번-> 학생_학번, 수강.학번 -> 수강_학번 select 학생.학번 수강_학번 as 학생_학번 , 수강.학번 as from 학생, 수강 where 학생.학번 = 수강.학번; /* output */ 학생_학번 수강_학번

• select 다음에 속성 대신 수식이 올 때 select 과목 번호, 학점 수 * 1.5

from 과목;

• 수식 "학점수 * 1.5"의 값을 계산하여 찍기는 하지만, 그 수식에 대한 속성 이름이 없다. • 해결 방안 select 과목 번호, 학점_수 * 1.5 as 조정된_학점_수 from 과목; • 학점_수 * 1.5

 학점_수*1.5

(as를 안 쓰면…) • 학점_수 * 1.5 as 조정된_학점_수  조정된_학점_수 (as를 쓰면…)

select 성적등급 from 수강;  이미 있는 속성 이름과 다른 속성 이름 을 주고자 할 때 • 보기: 속성 이름을 성적등급 대신 성적으 로 찍고자 하면 select 성적등급 as 성적 from 수강;

4.8 테이블을 가리키는 투플 변수를 정의하기 (129 쪽)

• 보기 select 학생.학번, 수강.과목번호 from 학생, 수강 where 학생.학번 = 수강.학번; 표 이름 * 표 이름만 적고, 투플 이름을 명시적으로 적지 않았음

• 보기: 표 이름만 있고 투플 이름이 안 나오 면, DBMS (SQL processor)가 표 이름과 꼭 같은 투플 이름을 만든다.

select 학생.학번, 수강.과목번호 from 학생 학생 , 수강 수강 where 학생.학번 = 수강.학번 표 이름 투플 변수 이름 * 이런 경우에는, 투플 변수 이름을 따로 적지 않아도 별 로 문제가 되지 않음.

1) select 학생.학번, 수강.과목번호 from 학생 t학생, 수강 t수강 where 학생.학번 = 수강.학번 안 됨… 왜 ??

// 130 쪽 2) select t학생.학번, t수강.과목번호 from 학생 t학생, 수강 t수강 where t학생.학번 = t수강.학번 됨… 왜 ??

// 131 쪽

• 같은 테이블에 대하여 둘 이상의 투플 변 수를 정의할 필요가 있을까? //131쪽 아래 – 학생=(학번, 이름, 이수학점, 평균평점) 9912345, 홍길동, 100, 4.10

9922222, 박두리, 80, 3.25

9933333, 김돌쇠, 90, 3.00

9944444, 이해솔, 120, 2.60

9955555, 임하늘, 130, 1.90

• 보기: 투플 변수 정의 기능을 잘 활용하기 – SQL에서 각 학생의 이수학점을 9933333 김돌쇠의 이수학점 90과 견주려면 어떻게 해야 하나?

• select 명령 하나 안에 그 모든 내용을 넣 는 방법 – 두 테이블 사이에 모든 조합 가능한 경우를 검사하려면, 카티전 곱 을 쓰면 된다!!!

학생과 학생 사이에 cartesian product 하면 됨.

– 그런데, 카티전 곱을 하려니 한 가지 문제가 있음 from 학생, 학생 ??

• From 학생 t1학생, 학생 • (133 쪽 가운데 그림자 부분) t2학생 From 학생 t학생, 학생 t2_홍_길동 where t2_홍_길동.학번 = ‘200700001’ - t학생 tuple var.: 학생 표의 모든 투플을 차례 대로 가리키게 된다.

t2_홍_길동 tuple var: 학생 표의 모든 투플을 차례대로 가리키지만, 조건 때문에 t.v. t2_홍_길동은 학번이 ‘200700001’ 인 학생 한 사람에게 실제로 고정된다 [중요]  where t학생.이수학점 > t2_홍_길동.이수학점 (홍 길동이 한 사람뿐인 때. 두 사람 이상이면?)

• (135 쪽) 평균 평점이 홍 길동보다 높고, 연 흥부보다 낮은 모든 학생의 학번과 이 름을 보여라 • • (136 쪽 아래) 임 꺽정이 듣는 과목과 같은 과목을 적어도 한 과목 듣는 모든 학생의 학번과 이름을 보여라 (임 꺽정 포함)  (임 꺽정을 빼고…) (137 쪽 아래)

• (138 쪽) 수강 표에서 CS200과 CS300을 모두 듣는 학생을 모두 보여라.

- Where 과목 번호 = ‘cs200’ and 과목 번호 = ‘cs300’ ???

- Where t1수강.학번 = t2수강.학번 and t1수강.과목번호 = ‘cs200’ and t2수강.과목번호 = ‘cs300’

• (141 쪽 아래) 표가 두 개 있고, 각 테이블 에 대해 투플 변수 두 개씩 정의해야 하는 보기 - 수강 표, 학생 표 - 홍 길동이 듣는 과목 가운데 적어도 한 과 목을 듣는 다른 학생을 모두 보여라. (앞의 보기보다는 조금 어려움)  책을 보고 혼자서 잘 이해할 것. 프로그램 도 해 보고… ?? 만일 이름이 홍 길동인 학생이 두 명 이상 있을 때도 위의 SQL 명령은 돌아가는지?

4.9 like: 문자열(character string) 패턴 연산

• 문자열의 패턴(pattern)을 다루는 특별한 연 산 • like 연산: where 절 안에서 속성이 어떤 패 턴에 맞는지를 확인 (=, is 등과 다름) • select 이름 from 학생 where 이름 like '% 길동%'; - %: 글자가 0, 1, 2, … 자 - 144 쪽 가운데쯤 보기

• select 이름 from 학생 where 이름 like ‘_ _길동%'; - 밑줄 (_): 밑줄 하나가 한 글자에 해당.

- 144 쪽 맨 아래: 참인 보기, 거짓인 보기 • 밑줄 (_): 보통은 한 바잇 (byte)에 해당 - EUC-KR에서 한글 글자마디 하나는 두 바잇 차지.

EUC-KR (Extended UNIX Code: KoRea) - msb=0: 한 바잇으로 ASCII 글자를 나타냄 (KS X 1003). 보기: ‘A’=0x41, - msb=1: 완성형. 한글 글자마디, 한자, 기 호 등 한 자를 두 바잇으로 나타냄.

보기: ‘가’=0xb0a1, 佳=0xcaa2, http://www.itscj.ipsj.or.jp/ISO-IR/149.pdf

(이 표에서 msb=1 을 더해야 부호 자리 (code position) 값이 됨) - 연습: unix (xxd 명령 또는 od 명령)

4.10 투플 간추리기: order by

• 사용자가 결과 테이블에 나오는 투플의 차 례를 지정하려면 order by 절을 쓰면 된다.

– 개념적으로는 테이블의 투플의 집합이기 때문 에 차례가 없음.

– 그러나 실제로 구현된 DB에서는 차례가 있음 (DB 안에서의 투플의 차례는 DBMS가 정함) – Select 명령의 결과에서 사용자가 자기가 원 하는 차례대로 투플을 보고자 할 때 order by 명령 씀 (DB 안의 투플 순서를 바꾸는 게 아님)

※ 올림 가나다 /abc 차례 select 이름 from 학생 order by 이름; - default: ascending/descending 가운데 ascending 임.

- default 뜻?

• 올림 차례라는 것을 명시적으로 나타낼 때 – order by 절의 뒤에 나오는 속성 이름 바로 다음에 asc 사용 select 이름 from 학생 order by 이름 asc ;

• 내림 차례 – desc 사용 select 이름 from 학생 order by 이름 desc;

• select 학번, 과목 번호 from 수강 order by 학번, 과목 번호 - sort key: 1 차 (학번, asc: default) 2 차 (과목 번호 asc: default) - 111 CS300 111 CS400 333 CS150 333 CS400 555 CS100

• select 학번, 과목 번호 from 수강 order by 학번, 과목 번호 desc.

- sort key: 1 차 (학번, asc: default) 2 차 (과목 번호 desc) - 111 CS400 111 CS300 333 CS400 333 CS150 555 CS100