• 검색 결과가 없습니다.

제7장 SQL99-스키마 정의, 기본 제약조건, 질의어 - Daum

N/A
N/A
Protected

Academic year: 2024

Share "제7장 SQL99-스키마 정의, 기본 제약조건, 질의어 - Daum"

Copied!
37
0
0

로드 중.... (전체 텍스트 보기)

전체 글

(1)

제 제 7 7 장 장 SQL99 SQL99 - - 스키마 스키마 정의 정의 , , 기본 기본 제약조건 제약조건 , , 질의어 질의어

7.1 SQL의 데이터 정의와 데이터 타입 7.2 SQL에서 기본 제약조건의 명시 7.3 SQL에서 스키마 변경문

7.4 SQL에서의 기본 질의 7.5 더 복잡한 SQL 질의

7.6 SQL에서 삽입, 삭제, 갱신문 7.7 SQL의 기타 기능

7.8 요약

(2)

7.1.1 SQL

7.1.1 SQL 에서 에서 스키마와 스키마와 카탈로그 카탈로그 개념 개념

™ 스키마

ƒ

SQL 초기버전에서는 모든 테이블들을 동일한 스키마의 일부로 간주하였음

ƒ

SQL2(SQL-92라고도 함)에서는 동일한 데이타베이스 응용에 속해 있는 테이블이나 기타 구성 요소들을 그룹화하기 위해 스키마 개념을 포함시킴

ƒ

SQL 스키마는 스키마 이름으로 식별함

ƒ

스키마의 각 원소에 대한 기술문과 스키마를 소유하는 사용자나 계정을 가리키는 권한부여 식별자도 포함함

9 여기서, 원소란 테이블, 제약조건, 뷰, 도메인 등을 말함

ƒ

스키마를 생성하는 구문: CREATE SCHEMA

예) JSMITH라는 권한부여 식별자를 갖는 사용자가 소유한 스키마 COMPANY를 생성함 CREATE SCHEMA COMPANY AUTHORIZATION JSMITH ;

™ 카탈로그

ƒ

스키마들의 모임

ƒ

무결성 제약조건은 동일한 카탈로그내의 스키마들 안에 있는 릴레이션들 사이에서만 정의 가능함

ƒ

동일한 카탈로그 내의 스키마들은 도메인과 같은 원소들을 공유할 수 있음
(3)

7.1.2 SQL

7.1.2 SQL 의 의 CREATE TABLE CREATE TABLE 명령 명령

™ CREATE TABLE

ƒ

새로운 릴레이션을 생성함

ƒ

릴레이션의 이름과 애트리뷰트들과 이들의 데이터 유형을 기술함

9 데이터 유형 : INTEGER, FLOAT, DECIMAL( i, j ), CHAR(n), VARCHAR(n)

ƒ

애트리뷰트의 속성 정의

9 NOT NULL : 해당 애트리뷰트 값으로 NULL을 허용하지 않음

(기본적으로 모든 애트리뷰트 값들은 NULL을 허용함)

ƒ

키 정의

9 PRIMARY KEY : 기본 키를 지정함

9 UNIQUE : 대체키(또는 보조키)를 지정함 9 FOREIGN KEY : 외래키을 지정함

예) CREATE TABLE PROJECT

( PNAME VARCHAR(15) NOT NULL,

PNUMBER INT NOT NULL,

…,

PRIMARY KEY (PNUMBER), UNIQUE (PNAME),

FOREIGN KEY (DNUM) REFERENCES DEPARTMENT(DNUMBER));

(4)

7.1.3 SQL

7.1.3 SQL에서 에서 애트리뷰트 애트리뷰트 데이터 데이터 타입과 타입과 도메인 도메인

™ 지원하는 기본 데이터 타입

ƒ

숫자: 정수, 실수, 형식화된 수(DECIMAL(i,j))

ƒ

문자열: CHAR(n), VARCHAR(n)

9 || : 두 문자열을 합치는 연산자

ƒ

비트열: BIT(n), BIT VARYNIG(n)

9 비트열 표현 예: B10101

ƒ

불리언: 참, 거진, UNKNOWN의 값을 가짐

ƒ

시간에 관련된 데이터 타입: DATE, TIME, TIMESTAMP, INTERVAL

™ 새로운 데이터 타입 정의

ƒ

CREATE DOMAIN

new_type

AS

data_type

예) CREATE DOMAIN SSN_TYPE AS CHAR(9);

ƒ

용도

9 많은 애트리뷰트가 사용하는 데이터 타입을 쉽게 변경함 9 스키마의 판독성이 향상됨

(5)

7.2 SQL

7.2 SQL 에서 에서 기본 기본 제약조건의 제약조건의 명시 명시

™ NOT NULL: 애트리뷰트의 값이 NULL이 아니어야 함을 지정함

™ DEFAULT <값>: 애트리뷰트의 디폴트 값을 지정함

™ CHECK(조건): 애트리뷰트가 만족해야 할 조건을 지정함

™ CHECK를 사용하여 투플에 제약조건을 명시할 수 있음

™ 제약 조건에 이름 부여: CONSTRAINT

ƒ 향후, 제약 조건을 삭제하거나 수정할 때에 사용됨

™ FOREIGN KEY : 외래키, 참조 무결성과 위반시의 동작을 지정함

ƒ 동작 : SET NULL, CASCADE, SET DEFAULT

ƒ 위반 종류 : ON DELETE, ON UPDATE 예) CREATE TABLE DEPT_LOCATIONS

( DNUMBER INT NOT NULL DEFAULT 1,

PNO INT CHECK(PNO > 0 AND PNO < 30), CONSTRAINT EMPPK

FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)

ON DELETE SET NULL ON UPDATE CASCADE ) CHECK( DNUMBER < PNO );

(6)

7.3.1 DROP

7.3.1 DROP 명령 명령

™ DROP SCHEMA

ƒ

스키마를 제거하는 명령어

ƒ

선택 사항:

9 CASCADE : 제거되는 스키마에 포함된 모든 테이블들, 도메인들, 기타 요소들을 모두 제거함

9 RESTRICT : 비어 있는 스키마만을 제거함

예) DROP SCHEMA COMPANY CASCADE ;

™ DROP TABLE

ƒ

기본 릴레이션을 제거하는 명령어

ƒ

선택 사항:

9 RESTRICT : 제약조건들이나 뷰들에서 참조되지 않는 테이블만을 제거함

9 CASCADE : 테이블 뿐만 아니라 테이블을 참조하는 모든 제약조건과 뷰들을 제거함

예) DROP TABLE DEPENDENT CASCADE ;

(7)

7.3.2 ALTER TABLE

7.3.2 ALTER TABLE 명령 명령

™ ALTER TABLE

ƒ

기본 테이블의 정의를 변경하는 명령어

ƒ

열(애트리뷰트)의 추가/제거, 열 정의의 변경, 테이블 제약 조건들의 추가/제거 등을 수행함

예) ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12) ;

9 EMPLOYEE 에 JOB이라는 애트리뷰트를 추가함 9 JOB 애트리뷰트에 값 입력:

디폴트 절로 지정함 ( 디폴트 절이 없으면 NULL로 입력됨 )

디폴트 절이 없는 경우에는 추가된 애트리뷰트에 “NOT NULL” 조건이 없어야 됨

UPDATE 명령을 사용하여 입력함

ƒ

애트리뷰트 제거시의 선택 사항:

9 CASCADE : 열과 함께 열을 참조하는 모든 제약 조건들과 뷰들을 제거함

9 RESTRICT : 그 열을 참조하는 뷰들과 제약 조건들이 없는 경우에만 열을 제거함

ƒ

기타 예제:

9 ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE; (열 삭제)

9 ALTER TABLE COMPANY.EMPLOYEE ALTER MGRSSN DROP DEFALUT; (열의 default속성 삭제) 9 ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;(제약조건 삭제)

(8)

7.4 SQL

7.4 SQL 에서의 에서의 기본질의 기본질의

™ SELECT 문

ƒ

데이타베이스에서 정보를 검색하는 기본 문장

ƒ

여기서 사용하는 SELECT문은 관계대수의 실렉트 연산과는 무관함

ƒ

관계모델과는 달리 SQL의 테이블 (릴레이션)은 동일한 튜플을 하나 이상 가질 수도 있음

Î

SQL 테이블은 튜플들의 집합이 아니라 튜플들의 다중집합 (multiset or bag)임

ƒ

사용자는 키 제약조건이나 DISTINCT 선택사항을 사용하여 SQL 릴레이션들을 집합으로 제한할 수도 있음
(9)

7.4.1 SQL

7.4.1 SQL 질의의 질의의 SELECT SELECT - - FROM FROM - - WHERE WHERE 구조 구조

™ SELECT 문의 구조

SELECT <애트리뷰트 리스트>

FROM <테이블 리스트>

WHERE <조건>

<애트리뷰트 리스트> : 질의 결과에 나타나는 애트리뷰트 이름 리스트

<테이블 리스트> : 질의의 대상이 되는 릴레이션 리스트

<조건> : 질의 결과의 투플들이 만족해야 하는 조건(부울)식

(10)

7.4.1 SQL

7.4.1 SQL 질의의 질의의 SELECT SELECT - - FROM FROM - - WHERE WHERE 구조 구조

™ SQL 질의와 관계 대수

ƒ

SQL은 관계 대수의 SELECT, PROJECT, JOIN 명령으로 표현 가능함

™ SQL 질의의 결과

ƒ

동일한 속성값을 가지는 튜플들이 중복될 수 있음

예1) Company database에서 이름이 'John B. Smith'인 종업원의 생일과 주소를 검색하라 SELECT BDATE, ADDRESS

FROM EMPLOYEE

WHERE FNAME='John' AND MINIT='B' AND LNAME='Smith’ ; 예2) 'Research' 부서에서 일하는 모든 종업원들의 이름과 주소를 검색하시오.

SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT

WHERE DNAME='Research' AND DNUMBER=DNO ; 9 관계대수 연산 SELECT-PROJECT-JOIN과 유사함

9 SELECT 절은 관계 대수의 PROJECT 연산에 해당되고,WHERE 절은 관계대수의 SELECT 연산, 그리고, DNUMBER=DNO는 조인조건으로서 관계대수의 JOIN 연산에 해당됨

(11)

7.4.2

7.4.2 모호한 모호한 애트리뷰트 애트리뷰트 이름, 이름 , 재명명 재명명( (별명 별명 ), 및 ), 및 투플 투플 변수 변수

™ 서로 다른 릴레이션이 동일한 이름의 애트리뷰트를 사용함

ƒ

릴레이션 이름과 함께 애트리뷰트 이름을 사용함으로써 모호함을 방지함

ƒ

SQL 작성시 릴레이션 이름 다음에 점(.)을 두고 애트리뷰트 이름을 명시함

예) SELECT FNAME, EMPLOYEE.NAME, ADDRESS

FROM EMPLOYEE, DEPARTMENT

WHERE DEPARTMENT.NAME='Research' AND

DEPARTMENT.DNUMBER=EMPLOYEE.DNUMBER ;

™ 동일한 릴레이션을 두 번 참조하는 경우가 발생함

ƒ

모호함을 방지하기 위해 릴레이션 이름의 별명을 사용함

예) 종업원에 대해, 종업원의 성과 이름, 그리고 직속 감독자의 성과 이름을 검색하시오.

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE AS E, EMPLOYEE ASS // EMPLOYEE에 대한 별명 WHERE E.SUPERSSN=S.SSN ;

9 위의 예는 EMPLOYEE 릴레이션에 대해서 두 개의 별명(alias) E와 S를 선언하여 사용함 9 AS를 생략할 수도 있음

(12)

7.4.2

7.4.2 모호한 모호한 애트리뷰트 애트리뷰트 이름, 이름 , 재명명 재명명( (별명 별명 ), 및 ), 및 투플 투플 변수 변수

™ 릴레이션에 대한 별명

ƒ

From 절의 릴레이션 이름 바로 다음에 오거나, EMPLOYEE AS E 처럼 키워드

AS를 이용해서 릴레이션과 연관시킴

ƒ

질의 내에서 별명을 주어 릴레이션의 애트리뷰트를 재명명할 수도 있음

예) EMPLOYEE AS E( FN, MI, LN, SSN, BD, ADDR, SEX, SAL, SSSN, DNO) ;

여기서, FN은 FNAME, MI는 MINIT,

LN은 LNAME으로 별명 관계가 이루어짐

(13)

7.4.3 WHERE

7.4.3 WHERE 절의 절의 생략과 생략과 ‘ ‘ * * ’ ’ (별표 ( 별표 ) ) 의 의 사용 사용

™ SQL에서 WHERE 절을 생략한 경우

ƒ

튜플 선택에 대한 조건이 없다는 것을 의미함

ƒ

FROM 절에 있는 테이블의 모든 튜플이 조건을 만족하게 됨

예) (1) 데이타베이스에서 EMPLOYEE의 모든 SSN을 검색하라 SELECT SSN

FROM EMPLOYEE ;

(2) EMPLOYEE의 SSN과 DEPARTMENT의 DNAME의 모든 조합을 선택하시오 SELECT SSN, DNAME

FROM EMPLOYEE, DEPARTMENT ;

™ SQL에서 SELECT절에 “ * ” 를 사용한 경우

ƒ

선택된 튜플들의 모든 애트리뷰트 값들을 검색함을 의미함

예) 5번 DEPARTMENT에서 일하는 EMPLOYEE 튜플들의 모든 애트리뷰트 값들을 검색하라 SELECT *

FROM EMPLOYEE WHERE DNO=5 ;

(14)

7.4.4 SQL

7.4.4 SQL 에서 에서 집합으로서의 집합으로서의 테이블 테이블

™ SQL 질의 결과에서 중복된 튜플들을 삭제하려면 SELECT 항목에서 키워드 DISTINCT( 반대는 ALL 이고 디폴트임 ) 를 사용해야 함

예) SELECT DISTINCTSALARY FROM EMPLOYEE ;

™ 집합 연산 – 합집합(UNION), 차집합(EXCEPT), 교집합(INTERSECT)

ƒ

릴레이션에 대한 집합 연산의 결과는 튜플들의 집합임(즉, 중복이 제거됨)

ƒ

중복된 튜플을 결과로 하려면, UNION ALL, EXCEPT ALL, INTERSECT ALL을 사용함

예) 성이 'Smith'인 종업원 (일반 직원 혹은 프로젝트를 담당하는 부서의 관리자)이 참여하는 프로젝트의 프로젝트 번호 목록을 작성하라 (SELECT PNUMBER // Smith가 관리자인projects

FROM PROJECT, DEPARTMENT, EMPLOYEE

WHERE DNUM=DNUMBER ANDMGRSSN=SSN AND LNAME='Smith') UNION

(SELECT PNO // Smith가 참여하는projects FROM WORKS_ON, EMPLOYEE

WHERE ESSN=SSN ANDLNAME='Smith') ;

(15)

7.4.5

7.4.5 부분 부분 문자열 문자열 비교와 비교와 산술 산술 연산자 연산자

™ 부분 문자열 비교 연산 - LIKE

ƒ ‘

%

는 임의의 개수의 문자를 의미함

ƒ ‘

_

는 임의의 한 문자를 의미함

예) 주소가 Houston, TX인 모든 종업원을 검색하라

SELECT FNAME, LNAME FROM EMPLOYEE

WHERE ADDRESS LIKE'%Houston,TX%’ ;

™ 문자열 접합 연산자 : ||

™ BETWEEN 연산자

ƒ

예: SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 30000 AND 40000
(16)

7.4.6

7.4.6 질의 질의 결과의 결과의 정렬 정렬

™ 질의 결과의 정렬 연산 – ORDER BY 절

ƒ

SQL은 ORDER BY 절을 사용하여 하나 이상의 애트리뷰트 값 순서로 질의 결과 튜플들을 정렬할 수 있음

ƒ

디폴트 정렬은 오름차순임

ƒ

내림차순으로 정렬하고자 한다면 키워드 DESC를 사용함

ƒ

오름차순 정렬을 명시적으로 지정 할 경우에는 키워드 ASC를 사용함

예1) ORDER BY DNAMEDESC, LANME ASC, FNAME ASC

예2) 프로젝트에 참여하는 종업원을 부서의 알파벳 순서대로, 각 부서 내에서는 성과 이름의 알파벳 순서대로 출력하시오.

SELECT DNAME, LNAME, FNAME, PNAME

FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME, LNAME, FNAME ;

(17)

7.5 7.5 더 더 복잡한 복잡한 SQL SQL 질의 질의

7.5.1 널값을 포함한 비교와 세 값을 갖는 논리 7.5.2 중첩질의와 집합비교

7.5.3 상관 중첩 질의

7.5.4 SQL의 EXISTS 함수와 UNIQUE 함수

7.5.5 SQL에서 명시적 집합과 애트리뷰트의 재명명 7.5.6 SQL에서 조인된 테이블

7.5.7 SQL에서 집단함수

7.5.8 그룹핑: GROUP BY와 HAVING 절

7.5.9 SQL 질의에 대한 논의와 요약

(18)

7.5.1

7.5.1 널값을 널값을 포함한 포함한 비교와 비교와 세 세 값을 값을 갖는 갖는 논리 논리

™ SQL에서는 참(TRUE), 거짓(FALSE), 모름(UNKNOWN)을 사용함

™ 널 값 비교 연산자: IS NULL, IS NOT NULL

예) SELECT FNAME, LNAME FROM EMPLOYEE

WHERE SUPERSSN IS NULL UNKNOWN UNKNOWN

TRUE FALSE

FALSE TRUE

NOT

UNKNOWN UNKNOWN

TRUE UNKNOWN

UNKNOWN FALSE

TRUE FALSE

TRUE TRUE

TRUE TRUE

UNKNOWN FALSE

TRUE OR

UNKNOWN FALSE

UNKNOWN UNKNOWN

FALSE FALSE

FALSE FALSE

UNKNOWN FALSE

TRUE TRUE

UNKNOWN FALSE

TRUE AND

(19)

7.5.2

7.5.2 중첩 중첩 질의와 질의와 집합비교 집합비교

™ 중첩 질의

ƒ

다른 질의의 WHERE 절 내에 완전한 SELECT 질의가 나타나는 형태

ƒ

외부 질의와 내부 질의로 구분됨

예) SELECT DISTINCT PNUMBER FROM PROJECT

WHERE PNUMBER IN (SELECT PNUMBER

FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND

MGRSSN=SSN AND LNAME='Smith’) OR

PNUMBER IN (SELECT PNO

FROM WORKS_ON, EMPLOYEE

WHERE ESSN=SSN AND LNAME='Smith') ;

(20)

7.5.2

7.5.2 중첩질의와 중첩질의와 집합비교 집합비교 (cont.) (cont.)

™ 비교 연산자 IN

ƒ

외부 질의의 한 투플이 내부 질의의 결과 투플 집합의 원소이면,

“ 참 ”

아니면

“ 거짓 ”

인 연산

™ = ANY (또는 = SOME) 연산자

ƒ

외부 질의의 한 투플이 내부 질의의 결과 투플 집합의 한 원소와 같으면,

“ 참 ”

아니면

“ 거짓 ”

인 연산

ƒ

IN과 동등한 연산자임

ƒ

ANY 앞에 올 수 있는 연산자들로는 =외에도 >, > =, <, <=, < > 등이 있음

™ = ALL 연산자

ƒ

외부 질의의 한 투플이 내부 질의의 결과 투플 집합의 모든 원소와 같으면,

“ 참 ”

아니면

“ 거짓 ”

인 연산

ƒ

ALL 앞에 >, > =, <, <=, < > 를 사용할 수도 있음
(21)

7.5.2

7.5.2 중첩질의와 중첩질의와 집합비교 집합비교 (cont.) (cont.)

(22)

7.5.2

7.5.2 중첩질의와 중첩질의와 집합비교 집합비교 (cont.) (cont.)

™ 중첩 질의에서의 애트리뷰트 참조규칙

ƒ

애트리뷰트 참조가 애매한 경우: 외부 질의문과 내부 질의문에서 사용하는 릴레이션들인 동일한 이름의 애트리뷰트를 가진 경우

ƒ

애매한 애트리뷰트에 대한 참조규칙은 항상 가장 안쪽에 가까운 질의문에 선언된 릴레이션을 먼저 참조하는 것임

ƒ

내부 질의에서 외부 질의에 명시된 릴레이션의 애트리뷰트를 참조할려면 별명을 사용해야 함

Q16) 부양가족과 이름, 성별이 같은 종업원들의 이름을 검색하라 SELECT E.FNAME, E.LNAME

FROM EMPLOYEE E

WHERE E.SSN IN (SELECT ESSN

FROM DEPENDENT

WHERE ESSN=E.SSN AND

E.FNAME=DEPENDENT_NAME AND SEX=E.SEX) ;

(23)

7.5.3

7.5.3 상관 상관 중첩 중첩 질의 질의

™ 상관된 질의 (correlated query)

ƒ

내부 질의의 WHERE 절에 있는 조건에서 외부질의에 선언된 릴레이션의 일부 애트리뷰트를 참조하는 경우에 두 질의를 상관된 질의라고 함 (예: Q16)

™ 비중첩 질의로의 변환

ƒ

중첩된 SELECT ⋯ FROM ⋯ WHERE⋯ 블록과 =나 IN 비교 연산자를 이용해서 작성한 질의는 항상 단일 블록 질의로 변환할 수 있음

Q16A) 부양가족과 이름, 성별이 같은 종업원들의 이름을 검색하라 (Q16과 같은 질의) SELECT E.FNAME, E.LNAME

FROM EMPLOYEE AS E, DEPENDENT AS D

WHERE E.SSN = D.ESSN AND E.SEX = D.SEX AND E.FNAME=DEPENDENT_NAME ;

™ Contains 연산자

ƒ

한 집합이 다른 집합 내의 모든 값들을 포함하면 참을 반환함

ƒ

관계대수의 DIVION 연산 기능과 유사함

ƒ

SQL의 요소가 아니기 때문에 이러한 유형의 질의를 표현하기 위해서는 7.3.2절에서 설명될 EXISTS 함수를 사용함
(24)

7.5.4 SQL

7.5.4 SQL 의 의 EXISTS EXISTS 함수와 함수와 UNIQUE UNIQUE 함수 함수

™ EXIST 함수

ƒ

상관된 중첩질의에서 내부 질의의 결과가 공집합인지를 검사함

ƒ EXISTS(Q)

: 질의 Q의 결과에 최소한 한 개의 튜플이 있다면 참을 반환하고, 그렇지 않으면 거짓을 반환함

ƒ NOT EXISTS(Q)

: 질의 Q의 결과에 튜플이 없다면 참을 반환하고, 그렇지 않으면 거짓을 반환함

예) 부양가족과 이름, 성별이 같은 종업원들의 이름을 검색하라 (Q16과 같은 질의) SELECT E.FNAME, E.LNAME

FROM EMPLOYEE E

WHERE EXISTS (SELECT *

FROM DEPENDENT

WHERE E.SSN=ESSN AND SEX=E.SEX AND E.FNAME=DEPENDENT_NAME) ;

™ UNIQUE 함수

ƒ

상관된 중첩질의에서 내부 질의의 결과에 중복된 튜플이 있는 지를 검사함

ƒ UNIQUE(Q)

: 질의 Q의 결과에 중복된 튜플이 없다면 참을 반환하고, 중복 투플이 있으면 거짓을 반환함
(25)

7.5.5 SQL

7.5.5 SQL에서 에서 명시적 명시적 집합과 집합과 애트리뷰트의 애트리뷰트의 재명명 재명명

™ 명시적 집합

ƒ

WHERE 절에 명시적인 값들의 집합을 사용할 수 있음

예) 프로젝트 번호 1, 2, 3에서 일하는 모든 사원들의 주민등록번호를 검색하라 SELECT DISTINCT ESSN

FROM WORKS_ON WHERE PNO IN(1,2,3) ;

™ 질의 결과 애트리뷰트의 재명명

ƒ

결과에 나타나는 애트리뷰트의 이름은 키워드 AS를 사용하여 원하는 새 이름으로 재명명할 수 있음

예) SELECT E.LNAME AS EMPLOYEE_NAME, S.LNAME AS SUPERVISOR_NAME FROM EMPLOYEE AS E, EMPLOYEE AS S

WHERE E.SUPERSSN=S.SSN ;

(26)

7.5.6 SQL

7.5.6 SQL 에서 에서 조인된 조인된 테이블 테이블

™ FROM 절에 조인 연산의 결과를 지정

ƒ

SQL에서는 질의의 FROM 절에 조인연산의 결과를 지정할 수 있음

예) SELECT FNAME, LNAME, ADDRESS

FROM (EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER) WHERE DNAME='Researsh’ ;

™ 외부 조인 (outer join)

ƒ

SQL에서는 INNER JOIN(JOIN과 같음), LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, NATURAL JOIN 등을 사용할 수 있음

ƒ

후자의 세 경우에는 키워드 OUTER를 생략해도 됨

예) SELECT E.LNAME AS EMPLOYEE NAME, S.LNAME AS SUPERVISOR NAME FROM (EMPLOYEE E LEFT OUTER JOINEMPLOYEE S ON

E.SUPERSSN = S.SSN ) ;

(27)

7.5.7 SQL

7.5.7 SQL 에서 에서 집단함수 집단함수

™ 집단함수

ƒ

SQL 에서는 COUNT, SUM, MAX, MIN, AVG 등의 집단 (or 내장) 함수를 제공함

예) 종업원의 봉급의 합, 최고 봉급, 최저 봉급, 평균 봉급을 구하라

SELECT SUM (SALARY), MAX(SALARY), MIN (SALARY), AVG (SALARY) FROM EMPLOYEE ;

ƒ

조건을 만족하는 투플들을 대상으로 집단 함수 값들을 얻으려면, WHERE절에서 튜플의 조건을 제시할 수 있음

예) 'Research' 부서에 있는 모든 종업원들의 봉급의 합과 최고 봉급, 최소 봉급, 평균 봉급을 구하라 SELECT SUM(SALARY), MAX (SALARY), MIN (SALARY), AVG (SALARY)

FROM EMPLOYEE, DEPARTMENT

WHERE DNO=DNUMBER AND DNAME='Research’ ;

예) (1)회사내의 총 종업원의 수와, (2) 'Research' 부서에 속해 있는 종업원의 수를 검색하라 (1) SELECT COUNT (*)

FROM EMPLOYEE ; (2) SELECT COUNT (*)

FROM EMPLOYEE, DEPARTMENT

WHERE DNO=DNUMBER AND DNAME='Research’ ;

(28)

7.5.8

7.5.8 그룹핑 그룹핑 : GROUP BY : GROUP BY 와 와 HAVING HAVING 절 절

™ 그룹화 (grouping)

ƒ

특정 애트리뷰트(들)의 값이 같은 투플들을 모아서 그룹을 생성함

9 각 그룹에 대하여 집단함수를 적용할 수 있음

9 특정 애트리부트들을 그룹화 애트리부트 라고 하며, SQL의 GROUP BY절로 지정함 예) 각 부서에 대해서 부서 번호, 부서 내에 있는 종업원의 수, 평균 봉급을 구하라

SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE

GROUP BY DNO ;

9 위 질의에서는 EMPLOYEE 투플들을 DNO 값을 기준으로 분할하여 그룹들을 생성함 9 그 다음에, 각 그룹의 투플들에 대하여 COUNT와 AVG함수를 적용함

9 SELECT 절 에 는 그 룹 화 애 트 리 뷰 트 (DNO) 와 각 튜 플 들 의 그 룹 에 적 용 할 집 단 함수들만 포함함

AV G(SALA Y) 33250 31000 55000 COUNT(*)

3 1 DNO

5 4 1

4

Q24의 결과 DNO 값으로 EMPLOYEE 투플들을 그룹화

SSN

999887777 987654321 987987987 888665555 12345678 33344555 666884444 453453453 T

MINIT B K A J S V E

LNAME Smith Wong Narayan

English Zelaya Wallace

Jabbar Bong FNAME

John Frankin Ramesh Joyce Alicia Jennifer

Ahmad James

. . .

. . .

SUPERSS N

987654321 888665555 987654321

null 333445555 888665555 333445555 333445555 SALARY

30000 40000 38000 25000 25000 43000 25000 55000

DDN 5 5 5 5 4 4 4 1

그림7.6 GROUP BY와 HAVING의 결과

(29)

7.5.8

7.5.8 그룹핑 그룹핑 : GROUP BY : GROUP BY 와 와 HAVING HAVING 절 절 (cont.) (cont.)

™ 그룹 선정(selection)은 HAVING절로 수행함

예) 종업원 수가 6명 이상인 부서에 대해서 부서 번호, 종업원, 평균 봉급을 구하라 SELECT DNO, COUNT (*), AVG (SALARY)

FROM EMPLOYEE GROUP BY DNO

HAVING COUNT(*) > 5;

™ WHERE절이 먼저 수행된 이후에 HAVING절이 수행됨

예) 사원 수가 6명 이상인 부서에서 급여가 40000달러 이상인 사원 수를 구하라 SELECT DNAME, COUNT(*)

FROM DEPARTMENT, EMPLOYEE

WHERE DNUMBER = DNO AND SALARY >40000 GROUP BY DNAME

HAVING COUNT(*) > 5;

SELECT DNAME, COUNT(*) FROM DEPARTMENT, EMPLOYEE

WHERE DNUMBER = DNO AND SALARY > 40000 AND DNO IN ( SELECT DNO FROM EMPLOYEE GROUP BY DNAME

HAVING COUNT(*) > 5 ) GROUP BY DNAME

틀린

틀린 질의문질의문

(30)

7.5.9 SQL

7.5.9 SQL 질의에 질의에 대한 대한 논의와 논의와 요약 요약

™ SQL질의는 6개의 절로 구성되고, 필수사항은 처음의 두 개임

SELECT <애트리뷰트 목록>

FROM <테이블 목록>

[WHERE <

조건

>]

[GROUP BY <집단화 애트리뷰트>]

[HAVING <집단 조건>]

[ORDER BY <애트리뷰트 목록>]

ƒ

SELECT 절은 질의 결과에 포함될 애트리뷰트들이나 함수를 나열함

ƒ

FROM 절은 질의의 대상을 명시하는 절로서, 조인될(된) 릴레이션이나 릴레이션(들)을 지정함

ƒ

WHERE 절은 튜플들에 대한 조건을 명시함

ƒ

GROUP BY절은 그룹화 애트리뷰트들을 지정함

ƒ

HAVING 절은 그룹들에 대한 조건을 지정함

ƒ

ORDER BY 절은 정렬 기준이 되는 애트리뷰트(들)을 지정함

ƒ

질의의 평가 순서는 먼저 FROM, WHERE절, GROUP BY절, HAVING절, SELECT절, ORDER BY 절 순서임
(31)

7.6 SQL

7.6 SQL 에서 에서 삽입 삽입 , , 삭제 삭제 , , 갱신문 갱신문

7.6.1 INSERT 명령

7.6.2 DELETE 명령

7.6.3 UPDATE 명령

(32)

7.6.1 INSERT

7.6.1 INSERT 명령 명령

™ INSERT 명령어는 릴레이션에 새로운 튜플을 삽입하는 명령어임

™ 간단한 형식:

ƒ

튜플값들을 CREATE TABLE 명령에서 지정한 애트리뷰트 순서와 동일하게 지정하여 하나의 튜플을 삽입함

예) INSERT INTOEMPLOYEE

VALUES ('Richard','K','Marini','653298653','30-DEC-52',

'98 Oak Forest, Katy, TX','M',37000,'987654321',4) ;

™ 값들의 순서를 애트리뷰트 순서와 다르게 지정하는 형식:

ƒ

INSERT 문장에서 애트리뷰트 이름을 명시해야 함

ƒ

이름을 명시하지 않은 애트리뷰트들은 NULL이나 DEFAULT 값을 가짐

예) INSERT INTO EMPLOYEE (FNAME, LNAME, SSN) VALUES ('Richard', 'Marini', '653298653') ;

(33)

7.4.1 INSERT

7.4.1 INSERT 명령 명령 (cont.) (cont.)

™ SELECT와 결합된 형식:

ƒ

SELECT 질의의 결과로 생성된 다중 튜플들을 릴레이션에 삽입함

™ 뷰와 DEPTS_INFO 테이블의 차이

ƒ

DEPTS_INFO 테이블은 최신정보를 가지고 있지 않을 수도 있음

9 위의 질의를 수행한 후에 DEPARTMENT나 EMPLOYEE 릴레이션을 갱신한다면, DEPTS_INFO는 이 변경을 반영하지 않은 과거 상태 그대로 유지함

ƒ

DEPTS_INFO 테이블을 최신정보로 유지하려면 뷰를 사용해야 함

예) CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(15), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER);

INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL ) SELECT DNAME, COUNT (*), SUM (SALARY)

FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO

GROUP BY DNAME ;

(34)

7.6.2 DELETE

7.6.2 DELETE 명령 명령

™ DELETE 명령은 릴레이션에서 튜플(들)을 제거하는 명령임

™ 삭제할 튜플에 대한 조건은 WHERE 절에서 명시함

ƒ

한번의 DELETE 명령으로 WHERE 절의 조건을 만족하는 튜플 수만큼 삭제함

ƒ

WHERE 절을 생략한 경우에는 테이블내의 모든 튜플을 삭제함

예) (1) DELETE FROM EMPLOYEE WHERE LNAME='Brown’ ; (2) DELETE FROM EMPLOYEE

WHERE SSN='123456789’ ; (3) DELETE FROM EMPLOYEE

WHERE DNO IN ( SELECT DNUMBER FROM DEPARTMENT

WHERE DNAME='Research’ ) ; (4) DELETE FROM EMPLOYEE ;

(35)

7.6.3 UPDATE

7.6.3 UPDATE 명령 명령

™ UPDATE 명령은 튜플들의 애트리뷰트 값을 수정하기 위해 사용함

ƒ

WHERE 절은 한 릴레이션에서 수정할 튜플들을 선택하는데 사용됨

ƒ

SET절은 변경할 애트리뷰트와 그들의 새로운 값을 명시함

) (1) PROJECT 테이블에서PNUMBER10인 튜플에 대해 PLOCATION 'Bellaire' 변경하고, 담당 부서인 DNUM5로 변경하라.

UPDATE PROJECT

SET PLOCATION='Bellaire', DNUM=5 WHERE PNUMBER=10 ;

(2) 'Research' 부서에 있는 모든 종업원들의 봉급을10% 인상하라. UPDATE EMPLOYEE

SET SALARY=SALARY*1.1

WHERE DNO IN ( SELECT DNUMBER FROM DEPARTMENT

WHERE DNAME='Research') ;

(36)

7.7 SQL

7.7 SQL 의 의 기타 기타 기능 기능

™ 주장(assertion) 기능

™ 뷰(view) 기능

™ 응용 프로그램과 관련된 기능: 내포된 SQL, ODBC, JDBC 등등

™ 트랜잭션 기능

™ 권한 부여 기능

™ 트리거(trigger) 기능

™ 객체지향 모델 관련 기능: 중첩릴레이션, 객체 식별자

™ 그외 기능: XML, OLAP(On-Line Analytical Processing)관련 기능

(37)

7.8 7.8 요 요 약 약

• SQL에서 데이터 정의어, 제약조건 및 스키마 변경

• SQL에서의 기본질의

Select/From/Where 절의 사용

• 더 복잡한 SQL 질의들

중첩질의, 집단함수, 집합과 널, 그룹핑 연산, Select/From/Where 절의 사용

• SQL에서 삽입, 삭제, 갱신 구문

• SQL의 부가적인 기능들

수치

그림 7.6 GROUP BY와 HAVING의 결과

참조

관련 문서