Chap. 4  Relational Language - SQL

*Textbook: Chapter 3,4 (6판/7판)

• 기능

① Data Definition Language (DDL)

- Define the structure of the database: CREATE (DATABASE, TABLE, VIEW),	DROP, ALTER

- Specify security constraints (authorization): GRANT

② Data Manipulation Languate (DML)

- Modify data in the database: INSERT, DELETE, UPDATE

- Query a database: SELECT

• 사용 방법

- Interactive SQL (Interpreter): DBA가 직접 DBMS를 사용

- Embedded SQL (Compiler): Programmer가 프로그램 안에서 사용	e.g., JDBC

SQL (Structure Query Language)

• Implemented as a part of the system R project in the early 1970's.

↳ DB2: RDBMS by IBM.

• ANSI published a SQL standard in 1986.

⇒ Then, ANSI and ISO SQL92 standard.

⇒ Every commercial RDBMS supports SQL92.

(DB2, Oracle, Informix, Sybase, MS SQL-Server, MySQL etc.)

SQL 실습

DB

DBMS

• Oracle 9i: iSQL*Plus 사용

- Oracle User ID & Password

- Work Screen > Preferences > Set interface options> Output: New window

• MySQL: phpMyAdmin 사용

- MySQL User ID & Password

- 데이터베이스 선택 > 테이블 작업

- 한글: Collation - utf8_unicode_ci로 실행

Data Definition Languate (DDL)

• Used by DBA.

⇒ DB 구조를 만듬.

• Creating a DB

CREATE DATABASE D ( … )

• Creating a relation into the DB

CREATE TABLE  R (A1 D1, A2 D2, … , An Dn)

WHERE R : the name of the relation

Ai :       〃    an attribute

Di : the data type of the domain Ai

ex) 고객

고객명	주소	CREATE TABLE 고객
char(20)	char(50)	(고객명 char(20) NOT NULL,
		주소 char(50),
		PRIMARY KEY (고객명))

지점

지점명	자산	시	CREATE TABLE 지점
char(15)	int	char(30)	(지점명 char(15) NOT NULL,
			자산 integer,
			시 char(30),
			PRIMARY KEY (지점명))

/* Oracle은 number 사용 */

예금

지점명	계좌번호	고객명	예금액	CREATE TABLE 예금
char(15)	char(10)	char(20)	int	(지점명 char(15),
				계좌번호 char(10) NOT NULL,
				고객명 char(20),
				예금액 integer,
				PRIMARY KEY (계좌번호), CHECK (예금액>=100),
				FOREIGN KEY (지점명) REFERENCES 지점,
				FOREIGN KEY (고객명) REFERENCES 고객)

Entity integrity?

Domain integrity?

e.g., check (지점명 IN ('서울‘, ’경기‘, '부산’))  /* 열거형(enumerated type) */

Referential integrity?

• Create an index on the attribute

CREATE INDEX A-ix ON R(A)

CREATE INDEX AB-ix ON R(A,B)   /* 2개 이상의 attribute에 대한 index. */

• Remove a relation from the DB

DROP TABLE R	/* Remove R from the DB. */

• Change a relation scheme.

ALTER TABLE R	ADD     A D

MODIFY

DROP

ex)	ALTER TABLE R ADD A char(15)	/* 추가 */

ALTER TABLE R MODIFY A integer	/* char를 int로 변경. */

ALTER TABLE R DROP Primary Key	/* 삭제 */

→ ALTER TABLE R ADD Primary Key R(A)	/* 추가 */

- 대부분의 DBMS에서는 테이블 전체를 제거하는 것은 허용하지만, 속성의 제거는 지원하지 않음.

Ex) Oracle의 경우: ALTER TABLE 구문에 나오는 DROP의 경우는 '속성'을 제거하는 것이 아니고, 테이블에 속한 '무결성 제약(integrity constraints)'을 제거.

⇒ DROP 다음에 어떤 제약을 제거할 것인지에 따라서, 아래의 구문들을 사용.

PRIMARY KEY: Drops the table's 'primary key' constraint.

UNIQUE: Drops the 'unique' constaint on the specified columns.

CONSTRAINT constaint: Drops the integrity constaint named 'constraint'.

CASCADE: Drops all other integrity constaints that depend on the dropped integrity constaint

Ex) 아래의 구문은 dept 테이블의 PRIMARY KEY 제약을 제거.

ALTER TABLE dept DROP PRIMARY KEY CASCADE;

⇒ The CASCADE option drops any 'foreign keys' that reference the primary key

• Creating a View (가상 relation)

- Security considerations may require that certain data be hidden from user.

⇒ It is possible to support a large number of views on top of any given set    of actual relations.

⇒ A view must be 'recomputed' for each query that refers to it.

ex)	CREATE VIEW 예금고객정보 AS

(SELECT 예금계좌번호,지점명,고객명 FROM 예금)

CREATE VIEW 모든고객 AS

(SELECT 지점명,고객명 FROM 예금)

UNION

(SELECT 지점명,고객명 FROM 대출)

ex) Oracle

v$session: user session 정보에 관한 system catalog view

⇒ When a user starts SQL*Plus with a valid username and password, then a session is established.

⇒ A session lasts until the user disconnects or exits the DB application.

SQL> desc v$session;

SQL> SELECT sid, serial# FROM v$session;

Data Manipulation Language (DML)

• Used by DB users

⇒ DB에서 원하는 데이터를 입력, 수정, 삭제, 검색.

• Insertion

- A newly created relation is empty initially.

⇒ Load data into the relation.

예금	대출

지점명	예금계좌번호	고객명	예금액		지점명	대출계좌번호	고객명	대출액
압구정	123	김영삼	1000

INSERT INTO 예금

VALUES (‘압구정‘, 123, ‘김영삼‘, 1000)

INSERT INTO 예금(예금계좌번호, 고객명, 예금액, 지점명)

VALUES (123, ‘김영삼‘, 1000, ‘압구정‘)

INSERT INTO 예금

(SELECT 지점명, 대출계좌번호, 고객명, 200

FROM 대출

WHERE 지점명=‘압구정’)	/* 여러 데이터 입력시 */

INSERT INTO 예금

VALUES (‘압구정‘, 123, NULL, 1000)  /* Not yer determined (≠0) */

Ex) Oracle batch processing

⇒ SQL*Loader: Loading data into a table

① script file 작성(emp_data.ctl)

LOAD DATA

NFILE *

INTO TABLE dept

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(deptno, dname, location)

BEGINDATA

12,"RESEARCH","SARATOGA"

10,"ACCOUNTING","CLEVELAND"

11,"ART","SALEM"

13,"FINANCE","BOSTON"

21,"SALES","PHILA"

22,"SALES","ROCHESTER"

42,"INT'L","SAN FRAN"

② $sqlldr scott/tiger emp_data

- An insertion is permitted through a view only if the view in query is defined in terms of one relation of the actual DB.

ex) INSERT INTO 예금고객정보

VALUES (‘압구정‘, 111, ‘노태우‘)

⇒ (‘압구정‘, 111, ‘노태우‘, NULL) is inserted into 예금.

What if 모든고객?

- All aggregate operation(AVG, MIN, MAX, SUM, COUNT) ignore tuples with NULL values on the argument attributes.

ex) SELECT SUM(예금액)

FROM  예금

SELECT COUNT(고객명) AS 예금고객수

FROM  예금

• Updating: Change a value in a tuple

ex) UPDATE 예금 SET 예금액=예금액*1.05  /* 이자 5%를 모든 tuple에 계산.*/

UPDATE 예금 SET 예금액=예금액*1.06

WHERE 예금액>10000;

UPDATE 예금 SET 예금액=예금액*1.05

WHERE 예금액<=10000     /* 2개 이상의 SQL문을 수행. */

• Deletion

ex) DELETE FROM 대출      /* delete all tuples */

DELETE FROM 대출 WHERE 고객명=‘김영삼‘

DELETE FROM 대출 WHERE 대출계좌번호 BETWEEN 100 AND 200

DELETE FROM 대출 WHERE 지점명 IN (SELECT 지점명

FROM 지점

WHERE 시=‘서울‘)

DELETE FROM 예금 WHERE 예금액 < (SELECT AVG(예금액)

FROM 예금)

⇒ ① Calculate average.

② Mark tuples to be deleted.

③ Once done marking, delete all marked tuples.

• Query Processing (질의 처리)

1. Translation of a high-level SQL query

⇒ Internal ‘relational algebra’ expression

2. Query optimization: The most efficient execution plan

⇒ Performance in terms of the 'no. of disk accesses' required

⇒ The difference between a 'bad' strategy and a 'good' strategy

3. Execution of a query: Query output

Ex) p.501 Fig.13-1

• Query-execution plan

- Parse-tree representation

- Annotated instruction

Ex) p.539 Fig.14-1

π고객명 [σ고객지점명=‘Brooklyn'  (지점 ∞ (계좌 ∞ 예금)) ]

⇒ π고객명 [ (σ고객지점명=‘Brooklyn' 지점) ∞ (계좌 ∞ 예금)) ]  ; Optimization

▪

EX) Memory Structures and Processes of Oracle

D000    Dispatcher

$ sqlplus

① shared pool - 'program'

□Memory에 존재하는 SQL 영역

□모든 SQL문은 이곳에서 수행됨. (parsing/optimization/execution/fetch)

□User가 입력한 SQL문은 이 곳에 저장되며, Oracle server process는 memory에 있는 SQL문을 실행함.

② database buffer cache - 'data'

□Server process가 data를 이용할 수 있도록 data block을 저장하는 memory 영역.

□Oracle은 가장 최근에 사용된 block을 이 곳에 저장함.

□할당된 memory block이 이미 꽉 차 있을 때, 새로운 block이 필요하면 least recently used block을 사용함.

⇒ LRU algorithm

□SGA 영역 중 가장 큼.

③ redo log buffer

□data recovery에 이용되는 영역.

□모든 trans.은 redo log file에 기록되기 전에 이 곳에 먼저 저장됨.

□LGWR은 정기적으로 이 buffer의 내용을 redo log file에 옮김.

(redo log file:

□Transaction들에 의해 DB에 일어나는 모든 변경들을 기록

□이 log를 사용하여 DB의 recovery가 가능)

④ large Pool

□Backup/Restore Operation, I/O Server Process, Multi-thread Server를 위한 'Session' Memory 영역.

⑤ cursor

□Oracle program의 Statement Handler가 이용.

Ex) Server process의 SQL문 처리: Parse, Execute, Fetch의 세 단계로 처리됨.

① Parse:

□SQL문장의 처음부터 끝까지 조사: SELECT, FROM, WHERE 같은 예약어, 괄호, 쉼표 등 문법적으로 맞는가를 확인.

□ 테이블명, 컬럼명 같은 오브젝트가 실제로 DB에 존재하는지 확인.

□ 실행계획: Rule-based/Cost-based Optimization

□가장 시간이 많이 걸리며, shared pool을 이용하면 중복을 피할 수 있음.

② Execution

□ 실행계획을 수행.

□ I/O가 일어나며 lock을 발생시킴.

③ Fetch

□추출 조건을 만족하는 행을 추출하여 User process에게 넘겨줌.

□정렬도 이 단계에서 수행됨. (ORDER BY)

• Retrieving

SELECT [eqn: A_1 ,A_2 ,~ ... ~,A_m]

FROM  [eqn: R_1 ,R_2 ,~ ... ~,R_n]

WHERE [eqn: P]

where [eqn: A_i] : attribute

[eqn: R_i] : relation

[eqn: P] : predicate (조건)

⇔[eqn: Π_{A_1 ,A_2 , ... ,A_m}(σ_P~ (R_1 × R_2 ×~...~ × R_n ) )]

-	SELECT *                   /* project all attributes of all relations */

FROM  [eqn: R_1 ,R_2 , ... ,R_n]   /* in the FROM clause.                */

WHERE  [eqn: P]

- Duplicate tuples

ex) SELECT DISTINCT 지점명   /* Duplicates are removed */

FROM 예금

SELECT 지점명   /* Duplicates are NOT removed */

FROM 예금

Which one is the project operaton in relational algebra?

- Set operations

ex)

예금	대출

지점명	예금계좌번호	고객명	예금액		지점명	대출계좌번호	고객명	대출액
둔산	123	김영삼	1000

예금계좌, 대출계좌 혹은 두 계좌 모두를 ‘둔산’ 지점에 갖고 있는 고객명단?

[eqn: (Π_고객명 σ_{지점명='둔산'}(대출)) ~∪~ (Π_고객명~ σ_{지점명='둔산'}(예금))]

⇔ (SELECT 고객명

FROM 대출

WHERE 지점명=‘둔산‘)

UNION

(SELECT 고객명

FROM 예금

WHERE 지점명=‘둔산‘)

두 계좌 모두를 ‘둔산’ 지점에 갖고 있는 고객명단?

[eqn: Π_고객명left {σ_{지점명='둔산'}(대출)right} ∩ Π_고객명left {σ_{지점명='둔산'}(예금)right}]

⇔ (SELECT 고객명

FROM 대출

WHERE 지점명=‘둔산‘)

INTERSECT

(SELECT 고객명

FROM 예금

WHERE 지점명=‘둔산‘)

/* MySQL은 지원하지 않음, IN or JOIN 구문 사용 */

‘둔산’ 지점에 대출 계좌만 갖고 있는 고객명단?

⇒ MINUS (NOT standard, Supported in System R)

⇒ EXCEPT

/* MySQL은 지원하지 않음, NOT IN 사용 */

- Join

ex)

고객
고객명	시	동

대출 계좌를 갖고있는 고객들의 명단과 시?

[eqn: Π_고객.고객명,시left {σ_{고객.고객명=대출.고객명}(고객×대출) right}]

⇔[eqn: Π_고객.고객명,시 (고객~∞~대출)]

⇔ SELECT 고객.고객명, 시

FROM 고객, 대출

WHERE 고객.고객명=대출.고객명	// join attr.를 명시

⇔ SELECT 고객.고객명, 시

FROM 고객 natural join 대출	// natural join 구문

⇔ SELECT 고객.고객명, 시

FROM 고객 join 대출 using (고객명)	// join ~ using 구문

‘둔산' 지점에 대출 계좌를 갖고있는 고객들의 명단과 시?

⇔ SELECT 고객.고객명, 시

FROM 고객, 대출

WHERE 고객.고객명=대출.고객명 AND 지점명=‘둔산‘

‘둔산’ 지점에 대출 계좌를 갖고있는 고객들의 명단과 시와 예금액?

⇔ SELECT 고객.고객명, 시, 예금액

FROM 고객, 대출, 예금

WHERE 고객.고객명=대출.고객명 AND 대출.지점명=‘둔산‘

AND 고객.고객명=예금.고객명

natural join 구문?

join ~ using 구문?

두 계좌 모두를 ‘둔산’ 지점에 갖고 있는 고객명단?

[eqn: Π_고객명left {σ_{지점명='둔산'}(대출)right} ∩ Π_고객명left {σ_{지점명='둔산'}(예금)right}]

⇒ INTERSECT

/* MySQL 표현식, 대출,예금 테이블을 조인해서 질의수행 */

SELECT 대출.고객명

FROM 대출 JOIN 예금 USING (고객명)  /* Natural Join */

WHERE 대출.지점명 = '둔산'

(c.f.) SELECT 예금.고객명

FROM 대출 JOIN 예금 USING (고객명)

WHERE 예금.지점명 = '둔산'

⇒ 예금의 ‘둔산’ 지점 3개의 튜플이 (고객명)으로 모두 대출과 조인 가능

(c.f.) SELECT 예금.고객명

FROM 대출 JOIN 예금 USING (고객명, 지점명)

WHERE 예금.지점명 = '둔산'

지점명	예금계좌번호	고객명	예금액		지점명	대출계좌번호	고객명	대출액
둔산 둔산 둔산	111 112 113	YS Rho Chun	100 200 400		둔산 어은 대흥 둔산	111 112 113 114	YS Rho Chun DJ	10000 20000 40000 50000

예금	대출

SELECT 예금.고객명

FROM 대출 JOIN 예금 USING (고객명)

WHERE 예금.지점명 = '둔산'

⇒ 고객명으로 YS, Rho, Chun이 조인

⇒ 예금.지점명=‘둔산’이므로 모두 해당

SELECT 대출.고객명

FROM 대출 JOIN 예금 USING (고객명)

WHERE 대출.지점명 = '둔산'

⇒ 고객명으로 YS, Rho, Chun이 조인

⇒ 대출.지점명=‘둔산’이므로 YS만 해당

SELECT 예금.고객명

FROM 대출 JOIN 예금 USING (고객명, 지점명)

WHERE 예금.지점명 = '둔산'

⇒ (고객명, 지점명)으로 (YS, 둔산)만 조인

⇒ 예금.지점명=‘둔산’이므로 YS가 해당

c.f. SELECT *

FROM 대출 JOIN 예금

USING (고객명)

⇒ “고객명으로 3개의 투플이 조인됨”

- Arithmetic Expression

ex) SELECT 예금계좌번호

FROM 예금

WHERE 예금액 BETWEEN 200 AND 300

( ⇔ WHERE 예금액 >= 200 AND 예금액 <= 300)

SELECT 예금계좌번호

FROM 예금

WHERE 예금액 NOT BETWEEN 200 AND 300

- String match

Any substring:	% Oracle	*  Informix	% MySQL

Any character:	-	?	_ (underbar)

ex) SELECT 고객명

FROM 고객

WHERE 동 LIKE ‘%당%‘   /* 사당동, 신당동, 당산동 … */

SELECT 고객명

FROM 고객

WHERE 동 NOT LIKE ‘%당%‘

escape character:   ‘percent: \%‘

- Set Membership (∈)

ex) SELECT DISTINCT 고객명

FROM 대출

WHERE 지점명=‘둔산‘ AND

고객명 IN (SELECT 고객명

FROM 예금

WHERE 지점명=‘둔산‘)

⇔ SELECT DISTINCT 고객명

FROM 대출

WHERE 지점명=‘둔산‘ AND

(지점명, 고객명) IN (SELECT 지점명, 고객명

FROM 예금)

⇒ INTERSECT

- Set comparison

ex)    지점

지점명	자산	시
둔산 어은 대흥 신촌 압구정 서초	100 200 400 300 50 500	대전 대전 대전 서울 서울 서울

대전에 있는 지점들보다 더 큰 자산을 갖고 있는 지점명은?

대전에 있는 모든 지점들보다 더 큰 자산을 갖고 있는 지점명은?

SELECT 지점명

FROM 지점

WHERE  시 != ‘대전‘ AND       /* 혹은 <> */

자산 〉SOME (SELECT 자산

FROM 지점

WHERE 시=‘대전‘)

SELECT 지점명

FROM 지점

WHERE  시 != ‘대전‘ AND	/* 혹은 <> */

자산 〉ALL (SELECT 자산

FROM 지점

WHERE 시=‘대전‘)

- Testing for empty relations.

EXISTS  → True  : if the subquery is non empty.

→ False :         〃         empty.

ex) 예금, 대출 계좌 모두를 ‘둔산’ 지점에 갖고 있는 고객명단?

① Set operation: INTERSECT

② Join

③ Set membership: IN

④ EXISTS

SELECT 고객명

FROM 고객

WHERE EXISTS (SELECT *  FROM 예금

WHERE 예금.고객명=고객.고객명 AND

지점명=‘둔산‘)

AND EXISTS (SELECT *  FROM 대출

WHERE 대출.고객명=고객.고객명 AND

지점명=‘둔산‘)

- Tuple variables: Rename (ρ)

Tuple variables are defined in the FROM clause.

ex) 김영삼과 같은 지점에 예금계좌를 갖고있는 고객명?

[eqn: Π_예금.고객명 σ_{예금.지점명=예금1.지점명~AND~ 예금.고객명≠‘김영삼’}left {예금×LEFT{ σ_{예금1.고객명=‘김영삼‘}ρ_예금1 (예금) right} RIGHT}]

⇔ SELECT DISTINCT T.고객명

FROM 예금 S, 예금 T

WHERE S.고객명 =‘김영삼‘  AND

S.지점명 = T.지점명 AND

T.고객명 != ‘김영삼‘

⇒ Tuple variables are most useful for comparing two tuples in the same relation.

⇔  SELECT DISTINCT 고객명

FROM 예금

WHERE 고객명 != ‘김영삼‘ AND

지점명 IN ( SELECT 지점명 FROM 예금

WHERE 고객명 =‘김영삼‘)

- Ordering the tuples

ex) SELECT 고객명

FROM   대출

WHERE  지점명=‘둔산‘    /* 입력된 순서  */

ORDER BY 고객명  ASC   /* 오름차순     */

DESC  /* 내림차순     */

- Aggregate functions

AVG, MIN, MAX, SUM, COUNT

ex) SELECT COUNT(고객명) FROM 예금

SELECT SUM(예금액) FROM 예금

SELECT 지점명, AVG(대출액) FROM 대출

GROUP BY 지점명

⇒ 각 지점마다의 평균 대출액.

ex) 지점별 평균 대출액이 30,000 이상인 지점의 지점명, 평균대출액, 대출총액으로 구성된 뷰(view)를 생성

⇒ alias (별명) 지정

CREATE VIEW View30000 AS

(SELECT 지점명, AVG(대출액) AS ‘평균대출액’, SUM(대출액) AS '대출총액'

FROM 대출

GROUP BY 지점명

HAVING AVG(대출액) >= 30000)

- Outer join: natural join에 포함되지 않은 (왼쪽/오른쪽/전체) 투플들을 포함시킴

p.103 그림 4.1

p.104 그림 4.2

p.107 그림 4.4 – 왼쪽 외부 조인	NATURAL LEFT OUTER JOIN

p.108 그림 4.5 – 오른쪽 외부 조인	NATURAL RIGHT OUTER JOIN

p.109 ~ p.110 – 전체 외부 조인	NATURAL FULL OUTER JOIN

- 참조 무결성(referential integrity) 지원??

CREATE TABLE  사원

(사원명   char(10),

지점명   char(10),

FOREIGN KEY (지점명) REFERENCES 지점);

INSERT INTO 사원 VALUES ('안중근‘, ’어은‘);

INSERT INTO 사원 VALUES ('유관순‘, ’탄방‘);

- 도메인 무결성(domain integrity) 지원??

CHECK (예금액>=100),

INSERT INTO 예금

VALUES (‘압구정‘, 123, ‘김영삼‘, 10)