관계 대수와 SQL.

관계 대수와 SQL

목차 I. 관계 대수 II. SQL 개요 III. 데이터 정의어와 무결성 제약조건 IV. SELECT문 V. INSERT, DELETE, UPDATE문

4.2 SQL 개요 SQL 개요 SQL은 현재 DBMS 시장에서 관계 DBMS가 압도적인 우위를 차지하는데 중요한 요인의 하나
SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로, ( )등을 추가하여 개발된 언어 1986년에 ANSI(미국 표준 기구)에서 SQL 표준을 채택함으로써 SQL이 널리 사용되는데 기여 다양한 상용 관계 DBMS마다 지원하는 SQL 기능에 다소 차이가 있음 본 책에서는 SQL2를 따름

4.2 SQL 개요 [표4.2] SQL의 발전 역사 버전 특징 SEQUEL
Structured English Query Language의 약어. System R 프로젝트에서 처음으로 제안된 SQL ( )의 약어.1983년에 IBM의 DB2, 1991년에 IBM SQL/DS에 사용됨 SQL-86 1986년에 미국 ANSI에서 표준으로 채택됨. 1987년에 ISO 표준으로 채택됨 SQL-89 ( ) 기능이 강화됨 SQL2(SQL-92) 새로운 데이터 정의어와 데이터 조작어 기능이 추가됨. 약 500페이지 분량 SQL3(SQL-99) 객체지향과 순환 기능 등이 추가됨. 약 2000페이지 분량

4.2 SQL 개요 SQL 개요(계속) SQL은 ( )(선언적 언어)이므로 사용자는 자신이 원하는 바(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없음 관계 DBMS는 사용자가 입력한 SQL문을 번역하여 사용자가 요구한 데이터를 찾는데 필요한 모든 과정을 담당 SQL의 장점은 자연어에 가까운 구문을 사용하여 질의를 표현할 수 있다는 것 두 가지 인터페이스 대화식 SQL(interactive SQL) 내포된 SQL(embedded SQL)

4.2 SQL 개요 관 계 데이터베이스 D B M S … [그림 4.3] 관계 데이터베이스에 대한 두가지 인터페이스
EMPLOYEE DEPARTMENT 호스트 언어 + ( ) [그림 4.3] 관계 데이터베이스에 대한 두가지 인터페이스

4.2 SQL 개요 오라클SQL의 구성요소 데이터 검색 데이터 조작어 데이터 검색(SQL의 select문)
데이터 정의어로 정의된 데이터베이스 스키마 내의 데이터 조작 데이터를 ( ) 기본 명령어 릴레이션에서 기존의 튜플들을 검색(SELECT) 릴레이션에서 새로운 튜플을 삽입(INSERT) 릴레이션에서 기존의 튜플들을 삭제(DELETE) 릴레이션에서 기존의 튜플들의 값을 수정(UPDATE)

4.2 SQL 개요 오라클SQL의 구성요소(계속) 데이터 정의어 트랜잭션 제어 데이터 제어어
릴레이션 생성 및 제거, 릴레이션에 새로운 애트리뷰트 추가 삭제 뷰 생성 및 제거, 인덱스 생성 및 제거 릴레이션 생성시 ( ) 많은 제약조건을 명시 하면 성능 저하 초래 트랜잭션 제어 트랜잭션의 시작, 철회, 완료 등을 명시하기 위해 사용 COMMIT, ROLLBACK 데이터 제어어 트랜잭션의 시작, 철회, 완료 등을 명시 릴레이션에 대한 권한 부여 및 취소

4.2 SQL 개요 [그림 4.4] 데이터 검색과 데이터 조작어의 기능 DEPARTMENT DEPTNO DEPTNAME
FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 7 5 연구 UPDATE DEPARTMENT SET FLOOR = 10 WHERE DEPTNO = 1; 수정 (DELETE) FROM DEPARTMENT WHERE DEPTNAME = 총무; 삭제 삽입 INSERT INTO DEPARTMENT VALUE (5, ‘연구’, 9); 검색 SELECT DEPTNAME, FLOOR FROM DEPARTMENT WHERE DEPTNO = 1 OR DEPTNO = 3; DEPTNAME FLOOR 영업 8 개발 9 [그림 4.4] 데이터 검색과 데이터 조작어의 기능

4.2 SQL 개요 [그림 4.5] (SQL의 인터페이스와 구성요소)

4.3 데이터 정의어와 무결성 제약조건 데이터 정의어
키 제약조건, 엔티티 무결성 제약조건, 참조 무결성 제약조건 등을 포함하는 관계 데이터베이스 ( ) 릴레이션 정의 애트리뷰트 생성 도메인(데이터 타입) 정의 키 명시

4.3 데이터 정의어와 무결성 제약조건 [표 4.4] 데이터 정의어의 종류 CREAT DOMAIN 도메인 생성 TABLE
테이블 생성 VIEW 뷰 생성 INDEX 인덱스 생성, SQL2 표준이 아님 ALTER ( ) DROP 도메인 제거 테이블 제거 뷰 제거 인덱스 제거, SQL2 표준이 아님

4.3 데이터 정의어와 무결성 제약조건 데이터 정의어 스키마의 ( )
스키마의 ( ) SQL2에서는 동일한 데이터베이스 응용에 속하는 릴레이션, 도메인, 제약조건, 뷰, 권한 등을 그룹화하기 위해서 스키마 개념을 지원 CREATE SCHEMA COMPANY_DB AUTHORIZATION kim; DROP SCHEMA COMPANY_DB RESTRICT; DROP SCHEMA COMPANY_DB CASCADE; RESTRICT : 스키마가 비어있지 않을 때 CASCADE : 스키마내의 모든 구성요소 연쇄적으로 제거

4.3 데이터 정의어와 무결성 제약조건 릴레이션 정의 CREAT TABLE DEPARTMENT
(DEPTNO NUMBER NOT NULL, DEPTNAME CHAR(10), FLOOR NUMBER, PRIMARY KEY(DEPTNO)); CREAT TABLE EMPLOYEE (EMPNO NUMBER NOT NULL, EMPNAME CHAR(10), TITLE CHAR(10, MANAGER NUMBER, SALARY NUMBER, DNO NUMBER, PRIMARY KEY (EMPNO), FOREIGN KEY (MANAGER) REFERENCE EMPLOYEE(EMPNO) FOREIGN KEY (DNO) REFERENCE DEPARTMENT(DEPTNO)); [그림 4.6] EMPLOYEE 릴레이션과 DEPARTMENT 릴레이션의 생성

4.3 데이터 정의어와 무결성 제약조건 [표 4.5] 릴레이션 정의에 사용되는 데이터 타입 데이터 타입 의미
INTEGER 또는 INT 정수형( ~ ) SMALLINT 작은 정수형( ~ ) NUMBER(n, s)또는 DECIMAL(n, s) n개의 숫자에서 소수 아래 숫자가 s개인 십진수 REAL 실수형 FLOAT(n) 적어도 n개의 숫자가 표현되는 실수형 CHAR(n) 또는 CHARACTER (n) n바이트 문자열, n을 생략하면 1 VARCHAR2(n) 또는 CHARACTER VARYING(n) 최대 n바이트까지의 가변 길이 문자열 BIT(n) 또는 BIT VARYING(n) n개의 비트열 또는 최대 n개까지의 가변 비트열 DATE 날짜형 BLOB Binary Large Object. 멀티미디어 데이터 등을 저장

4.3 데이터 정의어와 무결성 제약조건 릴레이션 제거(DROP TABLE) ALTER TABLE
릴레이션의 정의와 튜플 모두 삭제 삭제 옵션 RESTRICT : 다른 릴레이션에서 참조되지 않는 릴레이션만 제거 CASCADE : 릴레이션을 참조하는 ( ) 모두 삭제 DROP TABLE DEPARTMENT RESTRICT DROP TABLE DEPARTMENT CASCADE ALTER TABLE 릴레이션에 애트리뷰트 추가 삭제 ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);

4.3 데이터 정의어와 무결성 제약조건 인덱스 생성 도메인 생성 검색 성능 향상 위해 사용
CREATE INDEX EMPINDEX ON EMPLOYEE(EMPNO); 도메인 생성 CREATE DOMAIN DEPTNAME CHAR(10) DEFAULT ‘개발’;

4.3 데이터 정의어와 무결성 제약조건 제약조건 CREAT TABLE EMPLOYEE
(EMPNO NUMBER NOT NULL, (1) EMPNAME CHAR(10) UNIQUE, (2) TITLE CHAR(10 DEFAULT ‘사원’, (3) MANAGER NUMBER, SALARY NUMBER CHECK (SALARY < ), (4) DNO NUMBER CHECK (DNO IN (1, 2, 3, 4)) (5) DAFAULT 1, PRIMARY KEY (EMPNO), (6) FOREIGN KEY (MANAGER) REFERENCE EMPLOYEE(EMPNO) (7) FOREIGN KEY (DNO) REFERENCE DEPARTMENT(DEPTNO) (8) ON DELETE SET DEFAULT ON UPDATE CASCADE); (9) [그림 4.7] 릴레이션 정의에서 다양한 제약조건을 명시

4.3 데이터 정의어와 무결성 제약조건 애트리뷰트 제약조건 기본키 제약 조건(6) 참조 무결성 제약 조건(7,8)
NOT NULL(1) 널 값을 허용하지 않을때 UNIQUE(2) 동일한 애트뷰트값을 갖는 튜플이 두개 이상 존재하지 않도록 보장 DEFAULT(3) 널 값 대신 특정 값을 지정 CHECK(4, 5) 애트리뷰트가 가질수 있는 값들의 범위 지정 기본키 제약 조건(6) 각 릴레이션마다 최대 한 개의 기본 키 지정 참조 무결성 제약 조건(7,8) 외래 키의 무결성 보장

4.3 데이터 정의어와 무결성 제약조건 참조 무결성 제약조건 유지 ON DELETE NO ACTION
ON DELETE CASCADE ON DELETE SET NULL ON DELETE SET DEFAULT ON UPDATE NO ACTION ON UPDATE CASCADE ON UPDATE SET NULL ON UPDATE SET DEFAULT

4.3 데이터 정의어와 무결성 제약조건 예: ON UPDATE CASCADE
UPDATE문을 사용하여 DEPARTMENT 릴레이션의 3번 부서의 부서번호를 6번으로 수정하면 EMPLOYEE 릴레이션에서 3번 부서에 근무하는 모든 사원들의 소속 부서번호가 자동적으로 6으로 수정된다 UPDATE DEPARTMENT SET DEPTNO = 6 WHERE DEPTNO = 3; DEPARTMENT EMPLOYEE EMPNO EMPNAME … DNO 2106 김창섭 2 3426 박영권 1 3011 이수민 3 1003 조민희 3427 최종철 1365 김상원 4377 이성래 DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 7 6 6 6 기본 키의 수정이 외래 키에도 파급됨

4.3 데이터 정의어와 무결성 제약조건 무결성 제약조건의 추가 및 삭제
ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY (STNO); ALTER TABLE STUDENT DROP CONSTRAINT STUDENT_PK;

4.4 SELECT문 SELECT문 관계 데이터베이스에서 ( ) SQL문 관계 대수의 셀렉션과 의미가 완전히 다름
관계 대수의 셀렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것 관계 데이터베이스에서 가장 자주 사용됨 여러 가지 질의들의 결과를 보이기 위해서 그림 4.8의 관계 데이터베이스 상태를 사용함

4.4 SELECT문 [그림 4.8] 관계 데이터베이스 상태 DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10
EMPLOYEE EMPNO EMPNAME TITLE MANAGER SALARY DNO 2106 김창섭 대리 1077 2 3426 박영권 과장 4377 1 3011 이수민 부장 3 1003 조민희 3427 최종철 사원 1365 김상원 이성래 사장 ^ DEPARTMENT DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 7 [그림 4.8] 관계 데이터베이스 상태

4.4 SELECT문 기본적인 SQL 질의 SELECT절과 FROM절만 필수적인 절이고, 나머지는 선택 사항
SELECT [DISTINCT] 애트리뷰트(들) (1) FROM 릴레이션(들) (2) [WHERE 조건 (3) [중첩 질의] ] (4) [GROUP BY 애트리뷰트(들)] (5) [ HAVING 조건] (6) [ORDER BY 애트리뷰트(들) [ASC | DESC] ]; (7) [그림 4.9] SELECT문의 형식

4.4 SELECT문 기본적인 SQL 질의 SELECT절 FROM절 WHERE절 중첩질의 GROUP BY절 HAVING절
질의 결과를 포함 하려는 애트리뷰트들의 리스트 열거 DISTINCT 절을 사용해서 ( ) FROM절 질의에서 필요로 하는 릴레이션들의 리스트 열거 WHERE절 관계 대수의 셀렉션(Select) 연산의 조건에 해당 중첩질의 다른 질의의 WHERE절에 포함된 SELECT문 GROUP BY절 동일한 값을 갖는 튜플들을 한 그룹으로 묶는다 HAVING절 튜플들의 그룹이 만족해야 하는 조건 ORDER BY절 결과 튜플들의 정렬 순서

FROM EMPLOYEE AS E, DEPARTMENT AS D
4.4 SELECT문 별칭(alias) 서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법 EMPLOYEE.DNO FROM EMPLOYEE AS E, DEPARTMENT AS D

4.4 SELECT문 릴레이션의 모든 애트리뷰트나 일부들을 검색 예: *를 사용하여 모든 애트리뷰트들 검색
질의 : 전체 부서의 모든 애트리뷰트들을 검색하라. SELECT * FROM DEPARTMENT; DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 7

4.4 SELECT문 릴레이션의 모든 애트리뷰트나 일부들을 검색 예: 원하는 애트리뷰트들의 이름을 열거
질의 : 모든 부서의 부서번호와 부서이름을 검색하라. SELECT DEPTNO, DEPTNAME FROM DEPARTMENT; DEPTNO DEPTNAME 1 영업 2 기획 3 개발 4 총무

4.4 SELECT문 상이한 값들을 검색 예: DISTINCT절을 사용하지 않을 때 FROM EMPLOYEE;
질의 : 모든 사원의 직급을 검색하라. SELECT TITLE FROM EMPLOYEE; TITLE 대리 과장 부장 사원 사장

4.4 SELECT문 상이한 값들을 검색 예: DISTINCT절을 사용할 때 FROM EMPLOYEE;
질의 : 모든 사원의 직급을 검색하라. SELECT DISTINCT TITLE FROM EMPLOYEE; TITLE 대리 과장 부장 사원 사장

예: WHERE절을 사용하여 검색 조건을 명시
4.4 SELECT문 특정한 투플들의 검색 예: WHERE절을 사용하여 검색 조건을 명시 질의 : 2번 부서에 근무하는 사원들에 관한 모든 정보를 검색하라. SELECT * FROM EMPLOYEE WHERE DNO = 2; EMPNO EMPNAME TITLE MANAGER SALARY DNO 2106 김창섭 대리 1077 2 1003 조민희 과장 4377 이성래 사장 ^

4.4 SELECT문 문자열 비교 예: %를 사용하여 문자열 비교 FROM EMPLOYEE
질의 : 이씨 성을 가진 사원들의 이름, 직급, 소속부서 정보를 검색하라. SELECT EMPNAME, TITLE, DNO FROM EMPLOYEE WHERE EMPNAME LIKE ‘이%’; EMPNAME TITLE DNO 이수민 부장 3 이성래 사장 2

4.4 SELECT문 다수의 검색 조건 WHERE 절은 여러 조건들이 ( ) 결합된 조건을 포함할 수 있다.
SELECT FLOOR FROM DEPARTMENT WHERE DEPTNAME = ‘영업’ AND DEPTNAME = ‘개발’; 연산자 우선순위 비교연산자 1 NOT 2 AND OR 3 [표 4.5] 연산자들의 우선순위

4.4 SELECT문 예: 부울 연산자를 사용한 프레디키드 FROM EMPLOYEE
질의 : 직급이 과장이면서 1번 부서에 근무하는 사원들의 이름과 급여를 검색하라. SELECT EMPNAME, SALARY FROM EMPLOYEE WHERE TITLE = ‘과장’ AND DNO = 1; EMPNAME SALARY 박영권

4.4 SELECT문 부정 검색 조건 예: 부정 연산자 FROM EMPLOYEE
질의 : 직급이 과장이면서 1번 부서에 속하지 않은 사원들의 이름과 급여를 검색하라. SELECT EMPNAME, SALARY FROM EMPLOYEE WHERE TITLE = ‘과장’ AND DNO <> 1; EMPNAME SALARY 조민희

4.4 SELECT문 범위를 사용한 검색 예: 범위 연산자 FROM EMPLOYEE
질의 : 급여가 원 이상이고, 원 이하인 사원들의 이름, 직급, 급여를 검색하라. SELECT EMPNAME, TITLE, SALARY FROM EMPLOYEE WHERE SALARY BETWEEN AND ; BETWEEN은 양쪽의 경계값을 포함하므로 이 질의는 아래의 질의와 동일 WHERE SALARY >= AND SALARY <= ; EMPNAME TITLE SALARY 박영권 과장 이수민 부장 조민희

4.4 SELECT문 리스트를 사용한 검색 IN은 리스트 내의 값과 비교 예제는 DNO 값이 (1, 3)에 속하는가를 검사
질의 : 1번 이나 3번부서에 소속된 사원들에 관한 모든 정보를 검색하라. SELECT * FROM EMPLOYEE WHERE DNO IN (1, 3); EMPNO EMPNAME TITLE MANAGER SALARY DNO 3426 박영권 과장 4377 1 3011 이수민 부장 3 3427 최종철 사원 1365 김상원

SELECT절에서 산술 연산자(+, -, *, /) 사용
예: 산술 연산자 질의 : 직급이 과장인 사원들에 대하여 이름과, 현재의 급여, 급여가 10% 인상됐을때의 값을 검색하라. SELECT EMPNAME, SALARY, SALARY * 1.1 FROM EMPLOYEE WHERE TITLE = ‘과장’; EMPNAME SALARY SALARY * 1.1 박영권 조민희

4.4 SELECT문 널값 널값을 포함한 다른 값과 널값을 +, - 등을 사용하여 연산하면 결과는 널이 됨
COUNT(*)를 제외한 집단 함수들은 ( ) 을 무시함 어떤 애트리뷰트에 들어 있는 값이 널인가 비교하기 위해서 ‘DNO=NULL’처럼 나타내면 안됨 SELECT EMPNAME, EMPNAME FROM EMPLOYEE WHERE DNO = NULL;

4.4 SELECT문 널값(계속) 다음과 같은 비교 결과는 모두 거짓 올바른 표현 SELECT EMPNAME, EMPNAME
NULL > 300 NULL = 300 NULL <> 300 NULL = NULL NULL <> NULL 올바른 표현 SELECT EMPNAME, EMPNAME FROM EMPLOYEE WHERE DNO IS NULL;

4.4 SELECT문 표 4.7 unknown에 대한 OR 연산 true false unknown
표 4.8 unknown에 대한 AND연산 표 4.9 unknown에 대한 NOT연산

4.4 SELECT문 ORDER BY절 사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 DBMS가 투플들을 검색한 임의의 순서대로 사용자에게 제시됨 ORDER BY절에서 하나 이상의 애트리뷰트를 사용하여 검색 결과를 정렬할 수 있음 ORDER BY절은 SELECT문에서 가장 마지막에 사용되는 절 디폴트 정렬 순서는 오름차순(ASC) DESC를 지정하여 정렬 순서를 내림차순으로 지정할 수 있음 널값은 오름차순에서는 가장 ( ) 에 나타나고, 내림차순에서는 가장 ( ) 나타남

4.4 SELECT문 예: ORDER BY FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY;
질의 : 2번 부서에 근무하는 사원들의 이름, 직급, 급여를 검색하여 급여의 오름차순으로 정렬하라. SELECT EMPNAME, TITLE, SALARY FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY; EMPNAME TITLE SALARY 김창섭 대리 조민희 과장 이성래 사장

4.4 SELECT문 집단 함수 데이터베이스에서 검색된 여러 투플들의 집단에 적용되는 함수
각 집단 함수는 한 릴레이션의 한 개의 애트리뷰트에 적용되어 단일 값을 반환함 SELECT절과 HAVING절에만 나타날 수 있음 COUNT(*)를 제외하고는 모든 집단 함수들이 ( ) 후 남아 있는 값들에 대해서 집단 함수의 값을 구함 COUNT(*)는 결과 릴레이션의 모든 행들의 총 개수를 구하는 반면에 COUNT(애트리뷰트)는 해당 애트리뷰트에서 널값이 아닌 값들의 개수를 구함 키워드 DISTINCT가 집단 함수 앞에 사용되면 집단 함수가 적용되기 전에 먼저 중복을 제거함

4.4 SELECT문 예: MAX FROM EMPLOYEE; [표 4.10] 집단함수의 기능
질의 : 모든 사원들의 평균 급여와 최대 급여를 검색하라. SELECT AVG(SALARY),MAX(SALARY) FROM EMPLOYEE; AVG(SALARY) MAX(SALARY) [표 4.10] 집단함수의 기능 집단함수 기능 COUNT 튜플이나 값들의 개수 SUM 값들의 합 AVG 값들의 평균값 MAX 값들의 최대값 MIN 값들의 최소갑

4.4 SELECT문 그룹화 GROUP BY절에 사용된 애트리뷰트에 동일한 값을 갖는 투플들이 각각 하나의 그룹으로 묶임
이때 사용된 애트리뷰트를 그룹화 애트리뷰트(grouping attribute)라고 함 각 그룹에 대하여 결과 릴레이션에 하나의 투플이 생성됨 SELECT절에는 각 ( ), 집단 함수, 그룹화에 사용된 애트리뷰트들만 나타날 수 있음 다음 질의는 그룹화를 하지 않은 채 EMPLOYEE 릴레이션의 모든 투플에 대해서 사원번호와 모든 사원들의 평균 급여를 검색하므로 잘못됨 SELECT EMPNO,AVG(SALARY) FROM EMPLOYEE;

4.4 SELECT문 예: 그룹화 FROM EMPLOYEE GROUP BY DNO;
질의 : 모든 사원들에 대해서 사원들이 속한 부서번호별로 그룹화하고, 각 부서마다 부서 번호, 평균급여, 최대급여를 검색하라. SELECT DNO, AVG(SALARY),MAX(SALARY) FROM EMPLOYEE GROUP BY DNO;

4.4 SELECT문 EMPLOYEE 그룹 GROUP BY EMPNO EMPNAME TITLE MANAGER SALARY
DNO 1365 김상원 사원 3426 1 박영권 과장 4377 2106 김창섭 대리 1077 2 1003 조민희 이성래 사장 ^ 3427 최종철 3011 3 이수민 부장 그룹 DNO AVG(SALARY) MAX(SALARY) 1 2 3 GROUP BY

4.4 SELECT문 HAVING 각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용하여 각 그룹이 만족해야 하는 조건을 명시함
( ) 그룹들에 대해서만 집단 함수를 적용할 수 있음 각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용하여 각 그룹이 만족해야 하는 조건을 명시함 HAVING절은 그룹화 애트리뷰트에 같은 값을 갖는 투플들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타남 HAVING절에 나타나는 애트리뷰트는 반드시 GROUP BY절에 나타나거나 집단 함수에 포함되어야 함

4.4 SELECT문 예: HAVING FROM EMPLOYEE GROUP BY DNO
질의 : 모든 사원들에 대해서 사원들이 속한 부서번호별로 그룹화하고, 평균 급여가 이상인 부서에 대해서 부서번호, 평균급여, 최대급여를 검색하라. SELECT DNO, AVG(SALARY),MAX(SALARY) FROM EMPLOYEE GROUP BY DNO HAVING AVG(SALARY) >= ;

4.4 SELECT문 EMPLOYEE 그룹 GROUP BY HAVING EMPNO EMPNAME TITLE MANAGER
SALARY DNO 1365 김상원 사원 3426 1 박영권 과장 4377 2106 김창섭 대리 1077 2 1003 조민희 이성래 사장 ^ 3427 최종철 3011 3 이수민 부장 그룹 DNO AVG(SALARY) MAX(SALARY) 1 2 3 GROUP BY DNO AVG(SALARY) MAX(SALARY) 2 3 HAVING

4.4 SELECT문 집합 연산 집합 연산을 적용하려면 두 릴레이션이 합집합 호환성을 가져야 함
입력릴레이션 결과 릴레이션에서 중복 튜플 배제 UNION(합집합) EXCEPT(차집합) INTERSECT(교집합) 입력릴레이션 결과 릴레이션에서 중복 튜플 허용 UNION ALL(합집합) EXCEPT ALL(차집합) INTERSECT ALL(교집합)

4.4 SELECT문 예: 집합연산 FROM EMPLOYEE WHERE EMPNAME = ‘김창섭’) UNION
질의 : 김창섭이 속한 부서이거나 개발 부서의 부서번호를 검색하라. (SELECT DNO FROM EMPLOYEE WHERE EMPNAME = ‘김창섭’) UNION (SELECT DEPTNO FROM DEPARTMENT WHERE DEPTNAME = ‘개발’) ; DNO 2 3

4.4 SELECT문 조인 조인은 ( ) 연관된 투플들을 결합
조인은 ( ) 연관된 투플들을 결합 조인의 일반적인 형식은 아래의 SELECT문과 같이 FROM절에 두 개 이상의 릴레이션들이 열거되고, 두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE절에 포함됨 조인 조건은 두 릴레이션 사이에 속하는 애트리뷰트 값들을 비교 연산자로 연결한 것 가장 흔히 사용되는 비교 연산자 : = SELECT … FROM R, S WHERE R.A <비교 연산자> S.B ; 조인조건

4.4 SELECT문 조인(계속) 조인 조건을 생략했을 때와 조인 조건을 틀리게 표현했을 때는 카티션 곱이 생성됨
조인 조건이 명확해지도록 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용하는 것이 바람직 두 릴레이션의 조인 애트리뷰트 이름이 동일하다면 반드시 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용해야 함

4.4 SELECT문 예: 조인질의 질의 : 모든 사원의 이름과 이 사원이 속한 부서 이름을 검색하라
SELECT EMPNAME, DEPTNAME FROM EMPLOYEE AS E, DEPARTMENT AS D WHERE E.DNO = D.DEPTNO; EMPLOYEE 릴레이션의 각 튜플에 대해 부서번호를 사용하여 DEPARTMENT 릴레이션에서 같은 부서번호를 갖는 튜플을 찾는다. EMPLOYE 릴레이션의 튜플과 DEPARTMENT 튜플을 결합하면 결과 릴레이션의 한 튜플이 생성된다. EMPLOYEE 릴레이션의 마지막 튜플까지 이 과정을 반복한다.

4.4 SELECT문 EMPLOYEE DEPARTMENT EMPNO EMPNAME … DNO 2106 김창섭 2 3426
박영권 1 3011 이수민 3 1003 조민희 3427 최종철 1365 김상원 4377 이성래 DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 7 2106 김창섭 … 2 2 기획 10 2106 김창섭 … 2 기획 10

4.4 SELECT문 EMPLOYEE EMPNO EMPNAME TITLE MANAGER SALARY DNO 2106 김창섭
대리 1077 2 3426 박영권 과장 4377 1 3011 이수민 부장 3 1003 조민희 3427 최종철 사원 1365 김상원 이성래 사장 ^ DEPTNAME FLOOR 영업 8 기획 10 개발 9 총무 7

4.4 SELECT문 자체 조인(self join) 한 릴레이션에 속하는 투플을 동일한 릴레이션에 속하는 투플들과 조인하는 것
실제로는 한 릴레이션이 접근되지만 FROM절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서 그 릴레이션에 대한 별칭을 두 개 지정해야 함 예: 자체조인 질의 : 모든 사원에 대해서 사원의 이름과 직속상사의 이름을 검색하라 SELECT E.EMPNAME, M.EMPNAME FROM EMPLOYEE AS E, EMPLOYEE AS M WHERE E.MANAGER = M.EMPNO; EMPLOYEE 릴레이션의 각 튜플에 대해 상사번호(MANAGER)를 사용하여 자체릴레이션 에서 그 상사번호와 같은 사원번호를 갖는 튜플을 찾는다. 두 튜플을 결합하면 결과 릴레이션의 한 튜플이 생성된다. EMPLOYEE 릴레이션의 마지막 튜플까지 이 과정을 반복한다. 사용자가 검색하려는 애트리뷰트만 프로젝션한다.

4.4 SELECT문 EMPLOYEE EMPLOYEE EMPNO EMPNAME MANAGER … 2106 김창섭 1003
3426 박영권 4377 3011 이수민 조민희 3427 최종철 1365 김상원 이성래 ^ EMPNO EMPNAME MANAGER … 2106 김창섭 1003 3426 박영권 4377 3011 이수민 조민희 3427 최종철 1365 김상원 이성래 ^ 2106 김창섭 1003 … 1003 조민희 4377 … 2106 김창섭 1003 … 조민희 4377 …

4.4 SELECT문 최종결과 릴레이션 E.EMPNAME M.EMPNAME 김창섭 조민희 박영권 이성래 이수민 최종철 김상원

4.4 SELECT문 예: 조인과 ORDER BY의 결합 FROM EMPLOYEE AS E, DEPARTMENT AS D
질의 : 모든 사원에 대해서 사원에 대해서 소속 부서이름, 사원의 이름, 직급, 급여를 검색 하라. 부서 이름에 대해서 오름차순, 부서이름이 같은 경우에는 SALARY에 대해서 내림차 순으로 정렬하라 SELECT DEPTNAME, EMPNAME, TITLE, SALARY FROM EMPLOYEE AS E, DEPARTMENT AS D WHERE E.DNO = D.DEPTNO ORDER BY DEPTNAME, SALARY DESC; DEPTNAME EMPNAME TITLE SALARY 개발 이수민 부장 최종철 사원 기획 이성래 사장 조민희 과장 김창섭 대리 영업 박영권 김상원 오름차순 내림차순

4.4 SELECT문 중첩 질의(nested query)
외부 질의의 WHERE절에 다시 SELECT ... FROM ... WHERE 형태로 포함된 SELECT문 부질의(subquery)라고 함 INSERT, DELETE, UPDATE문에도 사용될 수 있음 중첩 질의의 결과로 한 개의 스칼라값(단일 값), 한 개의 애트리뷰트로 이루어진 릴레이션, 여러 애트리뷰트로 이루어진 릴레이션이 반환될 수 있음

4.4 SELECT문 외부질의 중첩질의 SELECT ... FROM ... WHERE ... (SELECT ...
[그림 4.10] 중첩 질의의 구조

4.4 SELECT문 예: 한 개의 스칼라 값이 반환되는 경우 FROM EMPLOYEE WHERE TITLE =
질의 : 박영권과 같은 직급을 갖는 모든 사원들의 이름과 직급을 검색하라. 아래와 같이 각 사원의 이름과 직급을 검색하는 질의(외부질의)가 필요하다 SELECT EMPNAME, TITLE FROM EMPLOYEE WHERE TITLE = 아래와 같이 박영권의 직급을 검색하는 질의(중첩질의)가 필요하다. SELECT TITLE WHERE EMPNAME = ‘박영권’ 두 질의를 하나로 결합하면 박영권과 같은 직급을 갖는 모든 사원들의 이름과 직급을 검색 할 수 있다. SELCECT EMPNAME, TITLE (SELECT TITLE WHERE EMPNAME = ‘박영권’); 과장

4.4 SELECT문 중첩 질의의 결과로 한 개의 애트리뷰트로 이루어진 다수의 투플들이 반환될 수 있음
( ) 이루어진 릴레이션이 반환되는 경우 중첩 질의의 결과로 한 개의 애트리뷰트로 이루어진 다수의 투플들이 반환될 수 있음 외부 질의의 WHERE절에서 IN, ANY(SOME), ALL, EXISTS와 같은 연산자를 사용해야 함 IN : 한 애트리뷰트가 값들의 집합에 속하는가를 테스트할 때 사용됨 ANY : 한 애트리뷰트가 값들의 집합에 속하는 하나 이상의 값들과 어떤 관계를 갖는가를 테스트하는 경우에 사용 ALL : 한 애트리뷰트가 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는가를 테스트하는 경우에 사용 EXISTS : 여러 애트리뷰트로 이루어진 릴레이션이 반환되는 경우에 사용

4.4 SELECT문 예: IN 예: ANY 2106 3426 3011 ( 3426 IN )은 참이다 ( < ANY )은 거짓이다 2106 3426 3011 ( 1365 IN )은 거짓이다 ( < ANY )은 거짓이다 2106 3426 3011 ( 1365 NOT IN )은 참이다

4.4 SELECT문 예: ALL ( < ALL )은 거짓이다 ( = ALL )은 거짓이다 ( < ALL )은 참이다 ( <> ALL )은 참이다

4.4 SELECT문 예: IN을 사용한 질의 SELECT EMPNAME FROM EMPLOYEE WHERE DNO IN
질의 : 영업부나 개발부에 근무하는 사원들의 이름을 검색하라. SELECT EMPNAME FROM EMPLOYEE WHERE DNO IN (SELECT DEPTNO FROM DEPARTMENT WHERE DEPTNAME = ‘영업’ OR DEPTNAME = ‘개발’); (1, 3)

4.4 SELECT문 예: IN을 사용한 질의 SELECT EMPNAME
이 질의를 중첩질의를 사용하지 않은 다음과 같은 질의로 나타낼 수 있다. 실제로 중첩질의 를 사용하여 표현된 대부분의 질의가 없는 질의로 표현할 수 있다. SELECT EMPNAME FROM EMPLOYEE AS E, DEPARTMENT AS D WHERE E.DNO = D.DEPTNO AND (D.DEPTNAME = ‘영업’ OR D.DEPTNAME = ‘개발’); EMPNAME 박영권 이수민 최종철 김상원

4.5 INSERT, DELETE, UPDATE문 INSERT문 기존의 릴레이션에 투플을 삽입
참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나 참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있음 릴레이션에 한 번에 한 투플씩 삽입하는 것과 한 번에 여러 개의 투플들을 삽입할 수 있는 것으로 구분 릴레이션에 한 번에 한 투플씩 삽입하는 INSERT문 INSERT INTO 릴레이션(애트리뷰트1, …, 애트리뷰트n) VALUE (값1, …, 값n) ;

4.5 INSERT, DELETE, UPDATE문 예: 한 개의 튜플을 삽입 INSERT INTO DEPARTMENT
질의: DEPARTMENT 릴레이션에 (5, 연구, ^) 튜플을 삽입하는 INSERT문은 아래와 같다. INSERT INTO DEPARTMENT VALUES (5, ‘연구’,NULL ); DEPARTMENT DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 7 5 연구 ^

4.5 INSERT, DELETE, UPDATE문 INSERT문(계속)
INTO 릴레이션(애트리뷰트1, …, 애트리뷰트n) SELECT … FROM … WHERE … ; 예: 여러 개의 튜플을 삽입 질의: EMPLOYEE 릴레이션에서 급여가 이상인 사원들의 이름, 직급, 급여를 검색하여 HIGH SALARY라는 릴레이션에 삽입하라. HIGH_SALARY 릴레이션은 이미 생성 되어 있다. INSERT INTO HIGH_SALARY(ENAME, TITLE, SAL) SELECT EMPNAME, TITLE, SALARY FROM EMPLOYEE WHERE SALARY >= ;

4.5 INSERT, DELETE, UPDATE문 DELETE문 삭제 연산은 한 릴레이션으로부터 한 개 이상의 투플들을 삭제함
참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않음 DELETE문의 구문 DELETE FROM 릴레이션 WHERE 조건 ;

4.5 INSERT, DELETE, UPDATE문 예: DELETE문 DELETE FROM DEPARTMENT
WHERE DEPTNO = 4 ; DEPARTMENT DEPARTMENT DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 7 DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4번 부서 삭제

4.5 INSERT, DELETE, UPDATE문 UPDATE문
한 릴레이션에 들어 있는 ( ) 기본 키나 외래 키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있음 UPDATE문의 구문 UPDATE 릴레이션 SET 애트리뷰트 = 값 또는 식[, …] WHERE 조건 ;

4.5 INSERT, DELETE, UPDATE문 예: UPDATE문 UPDATE EMPLOYEE
질의: 사원번호가 2106인 사원의 소속 부서를 3번 부서로 옮기고, 급여를 5% 올려라. UPDATE EMPLOYEE SET DNO = 3, SALARY = SALARY * 1.05 WHERE EMPNO = 2106 ; EMPLOYEE EMPLOYEE EMPNO … SALARY DNO 2106 2 3426 1 3011 3 1003 3427 1365 4377 EMPNO … SALARY DNO 2106 3 3426 1 3011 1003 2 3427 1365 4377 2106 UPDATE

관계 대수와 SQL.

Similar presentations

Presentation on theme: "관계 대수와 SQL."— Presentation transcript:

Similar presentations

About project

지원

로그인

Auth with social network:

관계 대수와 SQL.

Similar presentations

Presentation on theme: "관계 대수와 SQL."— Presentation transcript:

Similar presentations

About project

지원