본문 바로가기
정보과학/데이터베이스특론

SQL

by J1소프트 2023. 9. 3.
728x90

1. SQL의 데이터 정의

 

① SQL의 데이터 정의와 데이터 타입

∘SQL에서는 릴레이션→테이블, 투플→행, 애트리뷰트→열이라는 용어로 사용함

∘SQL 초기 버전은 스키마 개념을 포함하지 않고, 모든 테이블을 같은 스키마에 속했음

∘SQL2에서부터 동일한 데이터베이스 응용에 속하는 테이블이나 기타 구성요소들을 그룹화 하기 위해서 SQL에 스키마 개념을 포함하였음

∘스키마의 생성을 CREATE SCHEMA문을 사용함,

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

∘실제로는 CREATE SCHEMA보다 CREATE DATABASE 명령문을 많이 사용하고 있음

∘스키마, 테이블 및 다른 구조물의 생성권한은 시스템 관리자나 DBA에 의해 허락된 사용자 계정에게만 명시적으로 허락됨

∘CREATE TABLE 구문은 새로운 기본 릴레이션을 생성하는 데 사용하며, 릴레이션의 이름과 함께 각 애트리뷰트와 데이터 유형을 기술함

∘데이터 유형 :
- 숫자, 문자열, 비트열, 불리언, 날짜, 타임스템프 등이 있음

∘예2) INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n), BIT(n), DATE 등

∘예3)
CREATE TABLE DEPARTMENT
(DNAME VARCHAR(10),
DNUMBER INTEGER,
MGRSSN CHAR(9),
MGRSTARTDATE DATE);

∘데이터 타입을 도메인을 선언한 후 이 도메인 이름을 애트리뷰트 선언에 이용할 수 있으며, 이 방법은 한 스키마에 믾이 사용되는 도메인의 애트리뷰트 타입을 쉽게 변경 가능함

∘예4) CREATE DOMAIN SSN_TYPE AS CHAR(9);

※ SQL에서의 대소문자

∘SQL에서의 키워드는 대문자나 소문자 모두 동일하게 취급됨

② SQL에서 제약조건 명시

- NOT NULL 제약조건을 각 애트리뷰트에 명시할 수 있음

∘예1)
CREATE TABLE DEPARTMENT
(DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE DATE);

∘ SQL 구문의 CREATE TABLE 명령은 Primary Key와 Secondary Keys, 그리고 참조 무결성 제약(Foreign Keys)을 명시할 수 있음

∘Key 애트리뷰트들은 Primary Key와 UNIQUE 절을 통해 명시할 수 있음

∘예2)
CREATE TABLE DEPARTMENT
(DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY(MGRSSN) REFERENCES EMP ;

∘ Primary Key구문이 복합키가 아닌 경우에는 해당 열(column)명 뒤에 표기하기도 함
예) DNUMBER INTEGER PRIMARY KEY

※ 제약조건이 포함된 CREATE문의 일반형식

CREATE 문의 일반형식
CREATE TABLE 기본테이블
( { 열이름 데이터타입 [NOT NULL] [DEFAULT 값], }+
[PRIMARY KEY ( 열이름_ 리스트),]
{[UNIQUE ( 열이름_ 리스트),] }*
{[FOREIGN KEY( 열이름_ 리스트)
REFERENCES 기본테이블[( 열이름_ 리스트)]
[ON DELETE 옵션]
[ON UPDATE 옵션] ,] } *
[CONSTRAINT 이름] [CHECK( 조건식)]);

 

③ 스키마 변경 및 삭제 구문

∎ ALTER TABLE

∘SQL의 ALTER TABLE구문은 이미 만들어진 기본 릴레이션에 애트리뷰트를 추가, 변경, 삭제하기 위해 사용하는 구문임

∘ALTER TABLE ~ ADD ~ 를 이용하여 릴레이션에 애트리뷰트를 추가할 경우에 새로 추가된 모든 투플들의 해당 애트리뷰트 값은 NULL 값으로 지정됨 따라서, 추가되는 열에 대해 NOT NULL 제약조건을 사용할 수 없음

∘예1) ALTER TABLE EMPLOYEE ADD (JOB VARCHAR(12));

∘위의 각 EMPLOYEE 투플에 대해 새로운 애트리뷰트 JOB의 값을 별도로 입력해야 함

∘새로운 값의 입력은 UPDATE 명령을 사용하여 수행할 수 있음

∘위 JOB의 데이터 타입이 12자리에서 20자리로 변경해야할 경우에는,

∘예2) ALTER TABLE EMPLOYEE MODIFY (JOB VARCHAR(20));

∘위 JOB 열이 더 이상 필요없어서 제거할 경우에는,

∘예3) ALTER TABLE EMPLOYEE DROP (JOB);

∘만약 EMPLOYEE 테이블의 기본키의 제약조건을 제거할 경우에는,

∘예4) ALTER TABLE EMPLOYEE DROP PRIMARY KEY;


∎ DROP TABLE

∘SQL의 DROP TABLE구문은 릴레이션(기본 테이블)과 그 정의를 제거함

∘제거된 릴레이션은 질의(queries), 갱신(updates), 다른 명령어로 더 이상 사용하지 못함

∘기본 테이블의 제거의 일반 형식
- DROP TABLE 기본_테이블명 [RESTRICT | CASCADE];
예) DROP TABLE EMPLOYEE CASCADE;

∘스키마 제거의 일반형식
- DROP SCHEMA 스키마_이름 [RESTRICT | CASCADE];
예) DROP SCHEMA COMPANY CASCADE;
- DROP DATABASE 스키마_이름 [RESTRICT | CASCADE];
예) DROP DATABASE COMPANY CASCADE;

※ SQL2에서 SQL-99로 버전업되면서 추가된 데이터 타입들

∘DATE, TIME, TIMESTAMP 등의 데이터 타입을 추가로 가짐
- DATE: yyyy-mm-dd 형식으로 year-month-day을 표현함
- TIME: hh:mm:ss 형식으로 hour:minute:second을 표현함
- TIME(i): hour:minute:second에 초 이하의 단위를 명시하는 i개의 추가 숫자를 표현함
. 형식 : hh:mm:ss:ii...i
- TIMESTAMP: DATA와 TIME 구성요소를 포함
. 형식 : '2002-09-27 09:12:47 648302'
- 기간(INTERVAL):
. 절대값보다는 상대값으로 명시
. 기간은 YEAR/MONTH이나 DAY/TIME 기간이 될 수 있음
. 하나의 절대 시간 값을 더하거나 뺄 경우, 양수나 음수가 될 수 있으며 그 결과는 하나의 절대 시간 값이 됨

2. SQL의 데이터 조작

 

① SQL의 삽입(INSERT) 구문

∘INSERT의 간단한 형식은 한 릴레이션에 투플 한 개를 추가하는 데 사용

∘애트리뷰트 값의 순서는 CREATE TABLE 명령에 명시한 애트리뷰트들의 순서와 같아야 함

∘예1)
INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini', '653298653',
'30-DEC-52', '98 Oak Forest,Katy,TX', 'M', 37000,'987654321', 4 );

∘INSERT 명령의 다른 형식으로는 새로운 투플에서 명시한 값에 대응하는 애트리뷰트 이름들을 명시적으로 나타낼 수 있으며, 그럴 경우에 Null이 허용된 애트리뷰트에는 값을 명시하지 않아도 됨

∘FNAME, LNAME, DNO, SSN 애트리뷰트의 값만 알고 있는 새로운 사원 투플을 EMPLOYEE 릴레이션에 삽입할 경우의 INSERT문은 다음과 같음

∘예2)
INSERT INTO EMPLOYEE (FNAME, LNAME, SSN)
VALUES ('Richard', 'Marini', '653298653');
- 갱신할 때는, DBMS는 DDL 명령에서 명시된 무결성 제약조건을 지원해야 함

∘INSERT 명령의 또 다른 유형은 한 질의의 결과로 검색되는 다수의 투플을 릴레이션에 삽입할 수 있음

∘부서이름, 각 부서의 사원 수, 각 부서의 총 급여 액수를 갖는 임시 테이블을 생성 예

∘다음의 DEPTS_INFO 테이블을 생성하고, 그 테이블을 질의함으로서 데이터베이스에서 검색한 요약 정보를 이 테이블에 적재함

∘ 예3)
CREATE TABLE DEPTS_INFO
(DEPT_NAME VARCHAR(10),
NO_OF_EMPS INTEGER,
TOTAL_SAL INTEGER);

∘ 예4)
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;
- DEPTS_INFO 테이블은 최신정보을 가지고 있지 않을 수도 있음
- 즉, 위 INSERT문을 수행한 후에 DEPARTMENT나 EMPLOYEE 릴레이션에서 투플들을 갱신한다면 DEPTS_INFO에 있는 정보는 이런 변경을 반영하지 않는 상태가 됨
- DEPTS_INFO 테이블을 최신정보로 유지하려면 뷰를 생성해야 함

 

② SQL의 갱신(UPDATE) 및 삭제(DELETE) 구문

∎ 갱신(UPDATE)문

∘UPDATE문은 하나 이상의 투플들의 애트리뷰트 값을 수정하기 위해 사용

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

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

∘UPDATE 명령은 같은 릴레이션 내에서 여러 투플을 수정할 수 있음

∘프로젝트 번호 10인 투플에 대해 PLOCATION을 'Bellaire'로 변경하고 담당부서인 DNUM을 5로 변경하고자 하는 경우

∘예1)
UPDATE PROJECT
SET PLOCATION = 'Bellaire', DNUM = 5
WHERE PNUMBER=10;

∘'Research' 부서에 있는 모든 종업원들의 봉급을 10% 인상하는 갱신문

∘예2)
UPDATE EMPLOYEE SET SALARY = SALARY *1.1
WHERE DNO IN
(SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research');

∘변경된 SALARY 값은 원래 SALARY 값에 영향을 받음

∘오른쪽 SALARY 애트리뷰트는 수정되기 전의 SALARY 값을 왼쪾 SALARY 애트리뷰트는 수정된 후의 새로운 SALARY 값을 의미함


∎ 삭제(DELETE)문

∘DELETE문은 릴레이션에서 투플들을 제거하는 명령임

∘삭제할 투플들의 조건을 나타내는 WHERE절을 포함함

∘CASCADE가 참조 무결성 제약조건에 명시되어 있지 않으면, 한번에 한 테이블 내의 투플들만 삭제됨

∘WHERE 절을 생략한 경우에는 테이블 내의 모든 투플을 삭제되며, 이 때 테이블은 데이터베이스 내에서 빈 테이블로 남게 됨

∘WHERE 절을 기술할 경우에는 그 조건을 만족하는 투플 수에 따라 삭제함

∘예3)
DELETE FROM EMPLOYEE
WHERE LNAME='Brown';

∘예4)
DELETE FROM EMPLOYEE
WHERE SSN='123456789';

∘예5)
DELETE FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT
WHERE DNAME='Research');

∘예6) DELETE FROM EMPLOYEE;

③ SQL의 SELECT 구문(1)

∎ SELECT문의 형식

∘SELECT 문은 데이터베이스로부터 정보를 검색하는 SQL 문장이며, 관계 대수의 SELECT 연산과는 무관함

∘SQL과 관계대수와의 중요한 차이점은 SQL의 테이블은 모든 애트리뷰트 값이 동일한 투플을 하나 이상 가질 수 있는 투플의 다중집합(multi-set or bag)인 반면에 관계대수는 SQL 릴레이션(테이블)은 투플의 집합이다. 그러나 키 제약조건을 선언하거나 DISTINCT 선택사항을 사용하여 SQL 릴레이션들을 집합으로 제한할 수도 있음

∘SQL SELECT 문은 SELECT-FROM-WHERE 블록의 기본 형식임
SELECT <attribute list>
FROM <table list>
WHERE <condition>
- <attribute list> : 질의 결과에 나타나는 애트리뷰트 이름 목록
- <table list> : 질의의 대상이 되는 릴레이션 목록
- <condition> : 질의 결과에 포함될 투플들을 표시하는 조건(부울) 식


∎ 간단한 SQL 질의문

∘기본 SQL 질의들은 관계 대수의 SELECT, PROJECT, JOIN 연산으로 표현 가능함

∘이 후의 모든 예제들은 COMPANY 데이터베이스를 사용함

∘질의1) 'John B. Smith'인 종업원의 생일과 주소를 검색하시오

∘예문1)
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME='John' AND MINIT='B' AND LNAME='Smith';
- 관계대수 연산의 SELECT-PROJECT 쌍과 유사함
- SELECT 절은 프로젝트 애트리뷰트을 표시하고, WHERE 절은 선택 조건을 표시
- 그러나, 질의의 결과는 중복된 투플을 포함

∘질의2) 'Research' 부서에서 일하는 모든 종업원들의 이름과 주소를 검색하시오

∘예문2)
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO;
- 관계대수 연산의 SELECT-PROJECT-JOIN과 유사
- (DNAME='Research')은 선택 조건이고 관계대수에서 SELECT 연산에 해당함
- (DNUMBER=DNO)은 조인조건이고 관계대수의 JOIN 연산에 해당함

∘질의3) 'Stafford'에 위치한 모든 프로젝트에 대해 프로젝트 번호(PNUMBER), 담당부서 번호(DNUM), 부서 관리자의 성(LNAME), 주소(ADDRESS), 생일(BDATE)을 검색하시오

∘예문3)
SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford';
- 조인조건 DNUM=DNUMBER는 프로젝트와 담당부서를 조인함
- 조인조건 MGRSSN=SSN은 부서와 그 관리자를 조인

 

④ SQL의 SELECT 구문(2)

∎ 별명(alias)

∘SQL에서는 서로 다른 릴레이션에서 동일한 애트리뷰트가 사용될 수 있으며, 이 경우 릴레이션 이름과 함께 애트리뷰트 이름을 사용함으로써 모호함을 방지해야 함

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

∘예) EMPLOYEE.LNAME, DEPARTMENT.DNAME

∘ 어떤 질의들은 동일한 릴레이션을 두번 참조할 필요가 있으며, 이런 경우, 릴레이션 이름에 별명을 부여해야 함

∘질의4) 종업원에 대해 성과 이름, 직속 감독자의 성과 이름을 검색하시오

∘예문4)
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.SUPERSSN=S.SSN;
- E와 S를 EMPLOYEE의 두 개의 사본으로 생각할 수 있음
- E는 감독을 받는 사원(종업원)을, S는 감독을 하는 사원(감독자)을 나타냄

∘별명은 편의를 위해 SQL 질의에 사용될 수 있으며, 또한 키워드 AS를 사용할 수 있음

∘S.LNAME FROM EMPLOYEE AS E, EMPLOYEE AS S


∎ WHERE 절을 생략

∘SQL에서 WHERE 절을 생략하면 투플 선택에 대한 조건이 없다는 것을 의미하여서 FROM 절에 있는 릴레이션의 모든 투플이 조건을 만족함

∘질의5) 데이터베이스에서 EMPLOYEE의 모든 SSN을 선택하시오.

∘예문5)
SELECT SSN
FROM EMPLOYEE;


∘만일 두 개 이상의 릴레이션이 FROM 절에 명시되고 조인조건이 없으면, 투플의 카티션 곱이 검색됨

∘예)
SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTMENT

∘따라서 WHERE 절에서 모든 선택조건과 조인조건을 명시하는 것은 매우 중요함

∘만일 일부 조건을 빠뜨리면 부정확하거나 매우 큰 릴레이션이 결과로 생성됨


∎ ‘*’과 DISTINCT

∘선택된 투플들의 모든 애트리뷰트 값들을 검색하려면 모든 애트리뷰트 이름을 명시적으로 열거하지 않고 단지 ‘*’을 사용함

∘예) SELECT * FROM EMPLOYEE WHERE DNO=5;

∘예) SELECT * FROM EMPLOYEE, DEPARTMENT;

∘SQL의 연산 결과는 일반적으로 집합으로 취급하지 않고, 중복된 투플들이 나타날 수 있음

∘질의 결과에서 중복된 투플들을 삭제하려면, 키워드 DISTINCT를 사용해야 함

∘예) SELECT DISTINCT SALARY FROM EMPLOYEE;

 

⑤ SQL의 SELECT 구문(3)

∎ 집합 연산

∘SQL은 일부 집합 연산들을 수용하며, SQL에서는 합집합(UNION) 연산, 차집합(EXCEPT) 연산, 교집합(INTERSECT) 연산을 제공함

∘릴레이션에 대한 집합 연산의 결과는 투플들의 집합이며, 중복된 투플을 결과에서 제거됨

∘집합 연산들은 합집합 호환성을 갖는 릴레이션에만 적용해야 하는데, 적용할 두 개의 릴레이션은 동일한 애트리뷰트들을 가지며 이 애트리뷰트는 같은 순서로 나타나야 함

∘질의6) 성이 ‘Smith’인 종업원(일반 직원 혹은 프로젝트를 담당하는 부서의 관리자)이 참여하는 프로젝트의 프로젝트 번호를 작성하시오

∘예문6)
(SELECT PNAME // Smith가 관리자인 projects
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith')
UNION
(SELECT PNAME // Smith가 참여하는 projects
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith');


∎중첩 질의

∘SELECT 중첩 질의는 WHERE 절 내에 명시할 수 있음

∘질의7) Research에서 근무하는 모든 사원의 이름과 주소를 검색하시오

∘예문7)
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research' );
- 중첩 질의는 Research 부서의 번호(number)를 선택함
- 외부 질의는 DNO 값이 중첩 질의 결과에 있으면 EMPLOYEE 투플을 선택함
- 비교 연산자 IN은 하나의 값 v와 값들의 집합 V를 비교함
- v가 V에서 요소들(elements) 중의 하나이면 TRUE이 됨
- 일반적으로 중첩 질의들을 여러 레벨들을 포함할 수 있음

∘만약 중첩 질의의 WHERE 절에 있는 조건에서 외부 질의에 선언된 릴레이션의 애트리뷰트를 참조하는 경우에 두 질의는 상관된 질의라고 함

∘질의8) 부양가족의 성별(FNAME)과 같은 이름을 가진 사원들의 이름을 검색하시오

∘예문8)
SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E
WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT
WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME);

∘위의 구문은 조인문으로 다음과 같이 작성할 수도 있음

∘예)
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E, DEPENDENT D
WHERE E.SSN=D.ESSN AND E.FNAME=D.DEPENDENT_NAME

⑥ SQL의 SELECT 구문(4)

∎ CONTAINS 연산자

∘CONTAINS 연산자는 두 집합을 비교하여 한 집합이 다른 집합 내에 모든 값들을 포함하면 TRUE를 반환함 관계대수의 division 연산과 유사함

∘질의9) 5번 부서가 담당하는 모든 프로젝트에 근무하는 사원들의 이름을 검색하시오

∘예문9)
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE ((SELECT PNO FROM WORKS_ON WHERE SSN=ESSN)
CONTAINS (SELECT PNUMBER FROM PROJECT WHERE DNUM=5));
- 두 번째 중첩 질의(외부 질의와는 상관 관계가 없음)는 5번 부서를 담당하는 모든 프로젝트들의 프로젝트 번호를 검색함
- 각 사원 튜플에 대해서 첫 번째 중첩 질의(외부 질의와 상관 관계가 있음)는 그 사원이 일하는 프로젝트 번호들을 구함


∎EXISTS 함수

∘EXISTS는 상관 중첩 질의의 결과가 빈(튜플을 포함하지 않음)것인지 아닌지를 검사하는 데 사용

∘질의10) 부양가족의 성(FNAME)과 같은 사원들의 이름을 검색하시오

∘예문10)
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN AND FNAME=DEPENDENT_NAME);

∘질의11) 부양가족이 없는 사원들의 이름을 검색하시오

∘예문11)
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)


∎명시적 집합

∘중첩 질의 대신에 WHERE 절에 값들의 명시적 집합을 사용할 수 있음

∘질의12) 프로젝트 번호 1,2,3에서 일하는 모든 사원의 주민등록 번호를 검색하시오

∘예문12) SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3);


∎NULL여부 검사

∘SQL은 애트리뷰트의 NULL(널)인지 검사하는 질의들이 있음

∘NULL이란 알려지지 않는 값, 이용할 수 없는 값, 적용할 수 없는 값을 말함

∘SQL에서는 NULL과 비교하기 위해 IS나 IS NOT을 사용 하는데 그 이유는 각 NULL값은 모든 다른 NULL 값과는 다르다고 간주 함

∘"IS NULL" 이라고 작성해야 하며, "= NULL" 형태로 작성하면 않됨

∘질의13) 감독관이 없는 모든 종업원들의 이름을 검색하시오

∘예문13)
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPERSSN IS NULL;

∘조인 조건을 지정했을 때, 조인 애트리뷰트에 대해서 NULL 값을 갖는 튜플들은 결과에 나타나지 않음

⑦ SQL의 SELECT 구문(5)

∎ 다양한 조인구문

∘FROM 절에 조인 연산의 결과를 지정할 수 있음

∘여러 가지 유형의 조인을 명시할 수 있도록 허용

∘JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN 등

∘예)
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE E S
WHERE E.SUPERSSN = S.SSN;
===>
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEES ON E.SUPERSSN=S.SSN);

∘예)
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO;
===>
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO)
WHERE DNAME='Research';
===>
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEE NATURAL JOIN DEPARTMENT AS DEP (DNAME, DNO, MSSN, MSDATE)
WHERE DNAME='Research';

∘조인된 테이블들에서 다중 조인 형태로 취할 수 있음

∘예)
SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM (PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER) JOIN EMPLOYEE ON MGRSSN=SSN))
WHERE PLOCATION='Stafford’;


∎집단함수

∘SQL에서는 COUNT, SUM, MAX, MIN, AVG 등을 포함

∘질의14) 종업원의 봉급의 합, 최고 봉급, 최저 봉급, 평균 봉급을 구하시오

∘예문14)
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE;

∘질의15) ‘Research’ 부서에 있는 모든 종업원들의 봉급의 합과 최고 봉급, 최소 봉급, 평균 봉급을 구하시오

∘예문15)
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY),AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME='Research';

∘질의16) 회사 내의 총 종업원의 수를 구하시오

∘예문16)
SELECT COUNT (*)
FROM EMPLOYEE;

∘질의17) ‘Research’ 부서에 속해 있는 종업원의 수를 검색하시오

∘예문17)
SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME='Research’;

⑧ SQL의 SELECT 구문(6)

∎그룹화

∘많은 경우에, 릴레이션 내에 있는 투플들의 여러 부분 집단으로 나누고 집단 함수를 적용하기도 함

∘투플의 각 부분 집합은 그룹화 애트리뷰트(들)에 대해 값은 투플들로 구성됨

∘각 그룹마다 독립적으로 집단 함수들을 적용할 수 있음

∘SQL은 SELECT 절에 나타나는 애트리뷰트들 중에서 그룹화 애트리뷰트를 GROUP BY절에 명시

∘질의18) 각 부서별 부서번호, 부서 내에 있는 종업원의 수, 평균 봉급을 검색하시오

∘예문18)
SELECT DNO, COUNT (*), AVG (SALARY)
FROM EMPLOYEE
GROUP BY DNO;

∘EMPLOYEE 투플들을 그룹화 애트리뷰트인 DNO 값이 같은 투플들끼리 여러 그룹으로 분할함

∘각 그룹의 투플들에 대하여 COUNT와 AVG 함수를 적용함

∘SELECT 절에는 그룹화 애트리뷰트와 각 투플들의 그룹에 적용할 집단함수들만 포함함

∘조인조건은 그룹화와 함꼐 사용할 수 있음

∘질의19) 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 그 프로젝트에서 근무하는 사원들의 수를 검색하시오

∘예문19)
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME;
- 이 경우, 두 개의 릴레이션을 조인한 후에 그룹화와 집단함수가 적용됨


∎HAVING절

∘HAVING절은 집단함수를 적용할 그룹들을 선택하는 데 사용됨

∘질의20) 두 명 이상의 사원이 근무하는 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 프로젝트에서 근무하는 사원의 수를 검색하시오

∘예문20)
SELECT PNUMBER, PNAME, COUNT(*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT (*) > 2;

⑨ SQL의 SELECT 구문(7)

∎LIKE 비교 연산자

∘LIKE 비교 연산자는 문자열의 일부에 대해 비교조건을 명시함

∘부분 문자열은 두 개의 예약된 문자를 사용

∘‘%’는 0보다 큰 임의의 개수의 문자로 대체함

∘‘-’는 임의의 한 개의 문자로 대체함

∘질의21) 주소가 ‘Houston, Texas’인 모든 종업원을 검색하시오.
- 여기에, ADDRESS 애트리뷰트의 값은 부분 문자열 ‘Houston, TX’을 포함해야 함

∘예문21)
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ADDRESS LIKE '%Houston,TX%’;

∘질의22) 1950년대에 태어난 모든 사원을 검색하시오
- 날짜 형식에 의해서 문자열의 8번째 문자가 ‘5’이어야 한다.
- BDATA 값은 임의의 한 문자를 하나의 밑줄(_)로 대체하여 ‘_______5_‘의 문자열을 사용함

∘예문22)
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE BDATE LIKE '_______5_';


∎산술연산식의 사용

∘SQL 질의 결과에서 표준 산술 연산자 더하기(+), 빼기(-), 곱하기(*), 나누기(/)를 수치값에 적용할 수 있음

∘질의23) ‘ProductX’ 프로젝트에 참여하는 모든 사원의 급여를 10% 올린 경우의 급여를 구하시오

∘예문23)
SELECT FNAME, LNAME, 1.1*SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX’;


∎ORDER BY 절

∘ORDER BY 절은 하나 이상의 애트리뷰트 값 순서로 질의 결과 투플을 정렬할 수 있음

∘질의24) 사원 및 각 사원이 근무하는 프로젝트들의 리스트를 검색하는 데, 부서 이름 순서대로, 그리고 각 부서 내에서는 사원의 성과 이름의 알파벳 순서대로 구하시오

∘예문24)
SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE,WORKS_ON, PROJECT
WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER
ORDER BY DNAME, LNAME;

∘디폴트 정렬은 오름차순임

∘내림차순으로 정렬하고자 한다면 키워드 DESC로 지정함

∘키워드ASC는 오름차순 정렬을 명시적으로 지정할 때 사용함

∘예) ORDER BY DNAME DESC, LNAME ASC, FNAME ASC

※ SQL 질의문 요약

∘SQL 질의문은 일반적으로 6개의 절로 구성됨

∘필수적으로 질의에 나타내야 하는 두개의 절은 SELECT와 FROM 절임

∘6개의 절은 일반적으로 다음 순서로 명시함
SELECT <attribute list>
FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
[HAVING <group condition>]
[ORDER BY <attribute list>]

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

∘FROM 절은 질의에서 필요한 모든 릴레이션(별명)들을 명시함

∘중첩 질의들에 사용되는 릴레이션들은 명시하지 않음

∘WHERE 절은 조인조건을포함하여 FROM 절에 명시된 릴레이션들로부터 투플들을 선택하기 위한 조건들을 명시

∘GROUP BY절은 그룹화 애트리뷰트를 명시

∘HAVING 절은 선택된 투플들의 그룹들에 대한 조건을 명시

∘ORDER BY절은 질의 결과를 출력하는 순서를 명시

∘질의는 WHERE절, GROUP BY절과 HAVING절의 순서로 적용함으로써 평가됨

 

※ SQL문에 사용된 관계데이터베이스 스키마

[ 그림&nbsp; 1] COMPANY&nbsp; 관계 데이터베이스 스키마

 

※ SQL문에 사용된 데이터베이스 상태

[ 그림&nbsp; 2] COMPANY&nbsp; 데이터베이스 상태

3. 트리거, 뷰 및 프로그래밍 기법

 

① ASSERTION, TRIGGER

∎CREATE ASSERTION

∘제약조건 이름을 가지며, 다음에 키워드 CHECK가 오며, 데이터베이스 상태가 주장을 만족하는 여부(참/거짓)에 따라 조건(condition)이 뒤에 옴

∘예) 사원의 급여가 자신이 근무하는 부서의 관리자의 급여보다 많으면 안된다
CREATE ASSERTION SALARY_CONSTRAINT CHECK (NOT EXISTS
(SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE E.SALARY > M.SALARY AND
E.DNO=D.NUMBER AND D.MGRSSN=M.SSN));

∘제약조건 이름 SALARY_CONSTRAINT 뒤에 CHECK가 오며, 데이터베이스 상태가 ASSERT를 만족하면 참이 되는 조건이 그 뒤에 옴. DBMS는 제약조건을 위배하지 않도록 책임짐

∘질의의 결과가 공집합이 되어야 하며, 질의의 결과가 공집합이 아니면 주장은 위배됨


∎TRIGGER

∘트리거는 조건이 발생할 때 데이터베이스를 모니터하기 위해 행동의 유형을 명시하는 것으로서 assertion과 유사한 구문으로 표기되며, 사건(insert, update, delete), 조건, 해당 조건이 만족되면 수행하는 동작 등이 포함됨

∘삽입과 갱신 연산을 하는 동안 사원의 월급을 그의 관리자와 비교하기 위한 트리거의 예
CREATE TRIGGER INFORM_SUPERVISOR
BEFORE INSERT OR UPDATE OF
SALARY, SUPERVISOR_SSN ON EMPLOYEE
FOR EACH ROW
WHEN
(NEW.SALARY> (SELECT SALARY FROM EMPLOYEE
WHERE SSN=NEW.SUPERVISOR_SSN))
INFORM_SUPERVISOR (NEW.SUPERVISOR_SSN, NEW.SSN);
* DBMS에 따라서 문법이 다를 수 있음

※ 트리거에 대한 제한사항

∘트리거는 트랜잭션 제어문인 commit, rollback 등을 사용하지 못함

∘트리거 주요부는 long, long raw 변수를 선언할 수 없음

② 뷰(view)

∘SQL에서 뷰는 다른 테이블들에서 유도된 “가상” 테이블이라고 하며, 뷰에 적용할 수 있는 갱신 연산들은 제한되나 뷰에 대한 질의는 아무런 제한을 받지 않음

∘뷰를 정의하는 SQL 명령은 CREATE VIEW라고 표기함

∘예) WORKS_ON_NEW 뷰의 생성
CREATE VIEW WORKS_ON_NEW AS
SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN=ESSN AND PNO=PNUMBER
GROUP BY PNAME;

∘예) 생성된 뷰에서 SQL 질의를 명시할 수도 있음
SELECT FNAME, LNAME FROM WORKS_ON_NEW
WHERE PNAME=‘Seena’;

∘예) 어떤 뷰가 더 이상 필요하지 않으면 뷰를 제거함
DROP VIEW WORKS_ON_NEW;

∘질의를 위해 뷰를 효율적으로 구현하는 방법 중에 하나가 질의수정(query modification) 방식으로 뷰에 대한 질의를 기본 테이블들에 대한 질의로 변환하여 처리하는 것으로서 그단점은 복잡한 질의로 정의된 뷰들은 비효율적이며, 특히 짧은 시간 내에 뷰에 많을 질의가 적용될 때 비효율적임

∘뷰를 효율적으로 구현하는 또 다른 방법은 뷰의 실체화(view materialization)라는 방식으로 임의 뷰 테이블을 물리적으로 생성하고 유지하는 방식으로, 문제점은 기본 테이블이 갱신되면 뷰 테이블도 변경해야 하는 것으로 그 해결방법은 오버헤드가 적은 점진적 갱신(incremental update)의 개념을 사용함

∘집단함수를 사용하지 않는 단일 뷰의 갱신은 단일 기본 테이블에 대한 갱신으로 사상될 수 있음

∘조인을 포함하는 뷰의 갱신은 기본 릴레이션들에 대한 갱신 동작으로 사상될 수 있음 (항상 가능한 것은 아님)

∘갱신할 수 없는 뷰는 그룹화와 집단함수를 사용하여 정의된 뷰는 갱신할 수 없으며, 일반적으로 다수의 테이블을 조인하여 정의한 뷰는 갱신할 수 없음

∘갱신 시 정의 조건의 정확성을 기하려면 뷰 정의에 WITH CHECK OPTION 절을 추가 함
- 뷰의 갱신여부를 검사할 수 있고, 뷰의 갱신을 위한 실행 계획을 찾아낼 수 있음

※ 뷰의 장단점

∎뷰의 장점

∘논리적 독립성을 제공 - 테이블이 확장, 구조 변경이 있어도 영향을 받지 않음

∘데이터의 접근을 제어 - 뷰를 통해서만 데이터를 접근하므로 데이터의 효율적인 보안관리

∘사용자의 데이터 관리를 단순화 - 필요한 데이터만 뷰로 정리해서 처리하기 때문임

∘여러 사용자에 다양한 데이터 요구를 지원


∎뷰의 단점

∘정의를 변경할 수 없음

∘삽입, 삭제, 갱신 연산에 제한이 많음

③ 데이터베이스 프로그래밍

∘데이터베이스 프로그래밍이란 응용 프로그램에서 데이터베이스 접근하는 것을 말함

∘대부분 데이터베이스 연산은 응용 프로그램을 통해서 실행하며, 웹 인터페이스를 이용함

∘데이터베이스 프로그래밍 접근은 데이터베이스 명령문은 호스트 프로그래밍 언어 내에 내포시키는 방법(내포된 명령문), 호스트 프로그램에서 데이터베이스 호출을 위해 사용하는 방법(데이터베이스 함수 라이브러리, API), 데이터베이스 모델 및 질의어와 호환 가능하도록 새로운 언어의 개발 방법 등이 있음

∘대부분 SQL문은 COBOL, C, Java와 같은 범용 호스트 프로그래밍 언어에 내포될 수 있음

∘내포된 SQL 문장은 EXEC SQL 와 END-EXEC (또는 세미콜론(;))에 의해 호스트 프로그래밍 언어 문장으로 구분함

∘공유 변수들은 SQL 문장 내에서 사용될 때는 콜론(:)을 그 앞에 붙임

∘예) C언어에서 SQLCA와 CQLCODE에 대한 정의
EXEC SQL include SQLCA;
int loop;
EXEC SQL BEGIN DECLARE SECTION;
     varchar dname[16], fname[16], …;
     char ssn[10], bdate[11], …;
     int dno, dnumber, SQLCODE, …;
EXEC SQL END DECLARE SECTION;
- DECLARE 내에 변수들은 SQL 구문에서(콜론(:)을 붙여 사용) 공유될 수 있거나 사용됨
- SQLCODE는 데이터베이스와 프로그램 사이에 오류와 예외 조건들을 전달용

∘예) 내포된 SQL C 프로그래밍 예
loop = 1;
while (loop) {
     prompt (“Enter SSN: “, ssn);
     EXEC SQL
          select FNAME, LNAME, ADDRESS, SALARY
          into :fname, :lname, :address, :salary
          from EMPLOYEE where SSN == :ssn;
          if (SQLCODE == 0) printf(fname, …);
          else printf(“SSN does not exist: “, ssn);
          prompt(“More SSN? (1=yes, 0=no): “, loop);
     END-EXEC
}

 

 

④ CURSOR, 동적 SQL

∘커서(Cursor, 반복자)는 여러 튜플들을 처리하기 위해 필요함

∘커서를 이용한 처리 절차
prompt("Enter the Department Name: ", dname);
EXEC SQL
     select DNUMBER into :dnumber
     from DEPARTMENT where DNAME = :dname;
EXEC SQL DECLARE C1 CURSOR FOR
     select SSN, FNAME, MINIT, LNAME, SALARY
     from EMPLOYEE where DNO = :dnumber
     FOR UPDATE OF SALARY;
EXEC SQL OPEN C1; /*질의문의 실행*/
EXEC SQL FETCH from C1 INTO :ssn, :fname, :minit, :lname, :salary ;
while (SQLCODE == 0) { /* C1으로 접근되는 모든 레코드에 대해 */
     printf("Employee Name is; ", fname, minit, lname);
     prompt("Enter the raise amount: ", raise);
     EXEC SQL
          update EMPLOYEE
          set SALARY = SALARY + :raise
          where CURRENT OF EMP;
     EXEC SQL FETCH from C1 INTO :ssn, :fname, :minit, :lname, :salary ;
}
EXEC SQL CLOSE C1; /*질의 결과에 대한 처리를 종료했음을 나타냄 */

∘FETCH 명령어는 다음 투플로 커서를 이동할 때 사용

∘동적 SQL은 실행 시 새로운 SQL문장 수행하며, 프로그램은 실행 시에 키보드로부터 SQL 문장을 작성하고 마우스로 클릭한 연산에 대해 동적으로 SQL질의를 생성함

∘동적 수정문을 포함하는 것은 비교적 간단하지만 동적 질의는 훨씬 더 복잡함

∘검색된 애트리뷰트의 타입과 개수를 프로그램의 컴파일 시간을 알 수 없음

∘동적 SQL 예)
EXEC SQL BEGIN DECLARE SECTION;
varchar sqlupdatestring[256];
EXEC SQL END DECLARE SECTION;
…
prompt (“Enter update command:“, sqlupdatestring);
EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring;
EXEC SQL EXECUTE sqlcommand;

⑤ JAVA와 SQL

∘Java에서 내포된 SQL 명령
- SQLJ: Java 내에 내포된 SQL의 표준
- SQLJ는 Java에서 SQL 문장으로 변환: JDBC 인터페이스를 통해 실행되야 함
- SQLJ를 처리하기 위해 여러 클래스 라이브러리(예;java.sql)를 가져오는 것이 필요함

∘Java 데이터베이스 연결
- JDBC: Java에서 함수 호출을 사용하여 SQL 데이터베이스를 접근하는 데 사용
- JDBC 함수를 이용한 Java 프로그램을 사용하려면 JDBC 드라이버를 설치하는 것이 필요

∘JDBC는 여러 데이터베이스를 연결하기 위한 프로그램임

∘JAVA 응용프로그램의 수행단계
1. JDBC 데이터베이스 접근 단계
2. JDBC 라이브러리(java.sql.*) 가져오기
- JDBC 드리이버를 로드: Class.forname(“oracle.jdbc.driver.OracleDriver”)
3. 적절한 변수들의 선언
4. 연결 객체 생성 (getConnection 이용)
5. Statement 클래스에서 문장 객체를 생성:
   PreparedStatment
   CallableStatement
6. 물음표(“?”)기호는 문장 매개변수들을 나타냄
- 실행시에 매개변수를 C 변수에 바인딩할 때 결정
7. 모든 매개변수들이 프로그램 변수에 바인드
8. JDBC의 executeQuery 함수를 사용하여 (객체에 의해 참조된)SQL 문장 실행
9. 질의 결과 처리(ResultSet 타입의 객체에 리턴)
- ResultSet은 이차원 배열이나 테이블과 비슷

∘ORACLE에서 Java프로그램 내에 SQL 예제
ssn = readEntry(“Enter a SSN: “);
try {
     #sql{select FNAME< LNAME, ADDRESS, SALARY
     into :fname, :lname, :address, :salary
     from EMPLOYEE where SSN = :ssn};
}
catch (SQLException se) {
     System.out.println(“SSN does not exist: “,+ssn);
    return;
}
System.out.println(fname+“ “+lname+… );

∘SQLJ에서 다중 투플 처리를 위해 SQLJ는 반복자의 두가지 타입을 제공함

∘명명된 반복자(named iterator): 질의 결과에 연관될 때 결과에 있는 애트리뷰트의 이름과 타입들의 목록을 표시함

∘위치 반복자(positional iterator): 질의 결과에 애트리뷰트 타입들의 목록만 표시함

3. 정리하기

 

■ 요약정리

∘SQL에서 데이터 정의 언어(DDL)에는 CREATE, ALTER, DROP 구문이 있으며, 스키마의 생성은 create schema 혹은 create datebase구문을 사용함

∘테이블을 생성, 갱신, 제거를 위해, CREATE TABLE, ALTER TABLE, DROP TABLE 구문을 사용함

∘테이블 정의를 위해 설정해 주는 데이터 유형에는 숫자, 문자열, 비트열, 불리언, 날짜, 타임스템프 등이 있음

∘테이블 정의 시에 제약조건에는 primary key, foreign key, unique 등의 제약조건이 있음

∘정의된 테이블에 데이터 삽입 INSERT INTO 테이블명~ VALUES ~ 문을 주로 사용함

∘테이블에 기존 데이터 갱신은 UPDATE 테이블명 SET ~ WHERE ~ 문을 주로 사용함

∘테이블의 데이터를 삭제할 때는 DELETE FROM 테이블명 WHERE ~ 구문을 주로 사용함

∘테이블의 내용을 질의 검색하는 것은 SELECT ~ FROM ~ WHERE ~ 구문을 주로 사용함

∘SELECT 구문의 애트리뷰터 및 테이블명은 별명을 주어서 처리할 수 있음

∘SELECT 구문을 이용하여 집합연산, 중첩질의, 조인구문, 그룹화, 정렬 등 다양한 질의 표현이 가능함

∘제약조건을 미리 설정해 주기위한 create ASSERTION, 실행중에 모니터하여 사건이 발생하면 수행토록 하는 create TRIGGER구문이 있음

∘create VIEW로 가상테이블을 만들어서 편리하게 사용할 수 있음

∘내포된 프로그램에서 한행 이상을 추출할 때는 반드시 Cursor를 사용해야만 함

 

■ 참고자료

자료명 자료설명
• Elmasri. Navathe, Fundamentals of Database System 3rd Edition, Addison-wesley, 2000


• 황규영 외, 데이터베이스 시스템(4판), ITC, 2004


• 이석호, 데이타베이스 시스템, 정익사, 2005

 • 강의 자료는 저자 Elmasri의 Database System을 주로 참고했으며, 보완 자료로서 저자 이석호의 데이타베이스 시스템의 내용을 참고했음

 

'정보과학 > 데이터베이스특론' 카테고리의 다른 글

정규화  (0) 2023.09.04
설계 및 프로그래밍 실습  (0) 2023.09.04
관계데이터 모델  (0) 2023.09.03
저장장치 구조  (4) 2023.09.02
개체-관계(Entity Relationship) 모델  (0) 2023.09.02

관련글

티스토리툴바