2019-01-02

Chapter 7 SQL-99: 스키마 정의, 기본 제약조건, 질의어

데이터 정의, 제약조건 및 스키마 변경 데이터베이스의 테이블들(릴레이션들)의 생성, 제거, 갱신 위해 사용 CREATE TABLE, DROP TABLE, ALTER TABLE

CREATE TABLE 새로운 기본 릴레이션을 생성하는 데 사용하며, 릴레이션의 이름과 함께 각 애트리뷰트와 데이터 유형을 기술함 데이터 유형 : (INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n)) NOT NULL 제약조건을 각 애트리뷰트에 명시할 수 있음 Example : CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) );

CREATE TABLE SQL2에서, CREATE TABLE 명령은 Primary Key와 Secondary Keys, 그리고 참조 무결성 제약(Foreign Keys)을 명시할 수 있음 Key 애트리뷰트들은 Primary Key와 UNIQUE 절을 통해 명시할 수 있음 Example CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP );

DROP TABLE 릴레이션(기본 테이블)과 그 정의를 제거함 Example: DROP TABLE DEPENDENT; 제거된 릴레이션은 질의(queries), 갱신(updates), 또는 다른 명령어들을 더이상 사용하지 못함 Example: DROP TABLE DEPENDENT;

ALTER TABLE 기본 릴레이션에 하나의 애트리뷰트을 추가하기 위해 사용 이 명령이 실행할 경우, 릴레이션에 포함된 모든 튜플들은 새로 추가된 애트리뷰트에 대해 NULL 값으로 지정됨 따라서, 추가되는 열에 대해 NOT NULL 제약조건을 사용할 수 없음 Example: ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12); 각 EMPLOYEE 튜플에 대해 새로운 애트리뷰트 JOB의 값을 별도로 입력해야 함 이는 UPDATE 명령을 사용하여 수행

SQL2와 SQL-99에서 추가된 특징들 CREATE SCHEMA 명령 참조 무결성 옵션

CREATE SCHEMA 새로운 데이터베이스 스키마는 스키마의 이름과 함께 기술함

참조 무결성 선택사항 참조 무결성 제약조건(foreign keys)에서 RESTRICT, CASCADE, SET NULL또는 SET DEFAULT을 명시할 수 있음 Example CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP ON DELETE SET DEFAULT ON UPDATE CASCADE );

참조 무결성 선택사항 (계속) Example CREATE TABLE EMP ( ENAME VARCHAR(30) NOT NULL, ESSN CHAR(9), BDATE DATE, DNO INTEGER DEFAULT 1, SUPERSSN CHAR(9), PRIMARY KEY (ESSN), FOREIGN KEY (DNO) REFERENCES DEPT ON DELETE SET DEFAULT ON UPDATE CASCADE, FOREIGN KEY (SUPERSSN) REFERENCES EMP ON DELETE SET NULL ON UPDATE CASCADE );

SQL2와 SQL-99에서 추가 데이터 타입들 DATE, TIME, 그리고 TIMESTAMP 데이터 타입을 추가로 가짐 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’

SQL2와 SQL-99에서 추가 데이터 타입들(계속) 기간(INTERVAL): 절대값보다는 상대값으로 명시 기간은 YEAR/MONTH이나 DAY/TIME 기간이 될 수 있음 하나의 절대 시간 값을 더하거나 뺄 경우, 양수나 음수가 될 수 있으며 그 결과는 하나의 절대 시간 값이 됨

SQL에서 검색 질의 SQL은 데이터베이스로부터 정보를 검색하는 문장을 가짐 관계 대수의 SELECT 연산과는 무관함 따라서, SQL 릴레이션(테이블)은 튜플의 집합이 아니라 튜플의 다중집합(multi-set or bag)임 키 제약조건을 선언하거나 DISTINCT 선택사항을 사용하여 SQL 릴레이션들을 집합으로 제한할 수도 있음

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

관계 데이타베이스 스키마—그림4.5

대응 데이타베이스 상태–-그림4.6

간단한 SQL 질의들 기본 SQL 질의들은 관계 대수의 SELECT, PROJECT, JOIN 연산으로 표현 가능함 이 후의 모든 예제들은 COMPANY 데이터베이스를 사용함 하나의 릴레이션에 대한 간단한 질의 예제 Query 0: ‘John B. Smith’인 종업원의 생일과 주소를 검색하시오. Q0: SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME='John' AND MINIT='B’ AND LNAME='Smith’ 관계대수 연산의 SELECT-PROJECT 쌍과 유사 SELECT 절은 프로젝트 애트리뷰트을 표시하고, WHERE 절은 선택 조건을 표시 그러나, 질의의 결과는 중복된 튜플을 포함

간단한 SQL 질의들 (계속) Query 1: ‘Research’ 부서에서 일하는 모든 종업원들의 이름과 주소를 검색하시오. Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO 관계대수 연산의 SELECT-PROJECT-JOIN과 유사 (DNAME='Research')은 선택 조건이고 관계대수에서 SELECT 연산에 해당함 (DNUMBER=DNO)은 조인조건이고 관계대수의 JOIN 연산에 해당함

간단한 SQL 질의들 (계속) Query 2: ‘Stafford’에 위치한 모든 프로젝트에 대해 프로젝트 번호(PNUMBER), 담당부서 번호(DNUM), 부서 관리자의 성(LNAME), 주소(ADDRESS), 생일(BDATE)을 검색하시오. Q2: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford' Q2에서 두개의 조인조건이 존재 조인조건 DNUM=DNUMBER는 프로젝트와 담당부서를 조인함 조인조건 MGRSSN=SSN은 부서와 그 관리자를 조인

별명(alias), *와 DISTINCT, 빈 WHERE-절 SQL에서는 서로 다른 릴레이션에서 동일한 애트리뷰트가 사용될 수 있음 이 경우 릴레이션 이름과 함께 애트리뷰트 이름을 사용함으로써 모호함을 방지해야 함 SQL 작성시 릴레이션 이름 다음에 (.)을 두고 애트리뷰트 이름을 명시함 예 : EMPLOYEE.NAME, DEPARTMENT.NAME

별명(ALIAS) 어떤 질의들은 동일한 릴레이션을 두번 참조할 필요가 있음 이런 경우, 릴레이션 이름에 별명을 부여해야 함 Query 8: 종업원에 대해 성과 이름, 직속 감독자의 성과 이름을 검색하시오. Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E S WHERE E.SUPERSSN=S.SSN Q8에서, EMPLOYEE 릴레이션에 대해 두 개의 별명(튜플 변수) E와 S를 선언하여 사용함 E와 S를 EMPLOYEE의 두 개의 사본으로 생각할 수 있음 E는 감독을 받는 사원(종업원)을, S는 감독을 하는 사원(감독자)을 나타냄

별명(계속) 별명은 편의를 위해 SQL 질의에 사용될 수 있으며, 또한 키워드 AS를 사용할 수 있음 Example Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.SUPERSSN=S.SSN

WHERE 절의 생략 SQL에서 WHERE 절을 생략하면 튜플 선택에 대한 조건이 없다는 것을 의미함 즉, FROM 절에 있는 릴레이션의 모든 튜플이 조건을 만족함 Query 9: 데이터베이스에서 EMPLOYEE의 모든 SSN을 선택하시오. Q9: SELECT SSN FROM EMPLOYEE 만일 하나 이상의 릴레이션이 FROM 절에 명시되고 조인조건이 없으면, 튜플의 카티션 곱이 검색됨

WHERE 절의 생략 (계속) Example: Q10: SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT WHERE 절에서 모든 선택조건과 조인조건을 명시하는 것은 매우 중요 만일 일부 조건을 빠뜨리면 부정확하거나 매우 큰 릴레이션이 결과 로 생성됨

‘ * ’의 사용 선택된 튜플들의 모든 애트리뷰트 값들을 검색하려면 모든 애트리뷰트 이름을 명시적으로 열거하지 않고 단지 ‘*’을 사용함 Examples: Q1C: SELECT * FROM EMPLOYEE WHERE DNO=5 Q1D: SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNO=DNUMBER

DISTINCT의 사용 SQL은 일반적으로 집합으로 취급하지 않음 중복된 튜플들이 나타날 수 있음 질의 결과에서 중복된 튜플들을 삭제하려면, 키워드 DISTINCT를 사용해야 함 예를 들어, Q11의 결과는 중복된 SALARY값들을 가지고 있지만 Q11A는 중복된 값들을 가지지 않음 Q11: SELECT SALARY FROM EMPLOYEE Q11A: SELECT DISTINCT SALARY FROM EMPLOYEE

집합 연산 SQL은 일부 집합 연산들을 수용함 SQL에서는 합집합(UNION) 연산, 차집합(EXCEPT) 연산, 교집합(INTERSECT) 연산을 제공함 릴레이션에 대한 집합 연산의 결과는 튜플들의 집합임 즉, 중복된 튜플을 결과에서 제거됨 집합 연산들은 합집합 호환성을 갖는 릴레이션에만 적용 즉, 적용할 두 개의 릴레이션은 동일한 애트리뷰트들을 가지며 이 애트리뷰트는 같은 순서로 나타나야 함

집합 연산 (계속) Query 4: 성이 ‘Smith’인 종업원(일반 직원 혹은 프로젝트를 담당하는 부서의 관리자)이 참여하는 프로젝트의 프로젝트 번호를 작성하시오. Q4: (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 절 내에 명시될 수 있음 이전 질의들의 대부분은 중첩을 사용하는 선택적인 형태에 명시될 수 있음 Query 1: Research에서 근무하는 모든 사원의 이름과 주소를 검색하시오.. Q1: 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 절에 있는 조건에서 외부 질의에 선언된 릴레이션의 애트리뷰트를 참조하는 경우에 두 질의는 상관된 질의라고 함 Query 12: 부양가족의 성명(FNAME)과 같은 이름을 가진 사원들의 이름을 검색하시오. Q12: 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)

상관 중첩 질의 (계속) Q12에서, 중첩 질의는 외부 질의에서의 각 튜플에 대해 서로 다른 결과를 가짐 중첩된 SELECT… FROM… WHERE… 블록과 =과 IN 비교 연산자를 이용해서 작성된 질의는 항상 단일 블록 질의로 변환할 수 있음 예를 들어, Q12는 Q12A로 쓰여질 수 있음 Q12A: SELECT E.FNAME, E.LNAME FROM EMPLOYEE E, DEPENDENT D WHERE E.SSN=D.ESSN AND E.FNAME=D.DEPENDENT_NAME SYSTEM R에서 구현된 원래 SQL에는 중첩 상관 질의와 함께 사용된 CONTAINS 비교 연산자를 포함함 이 연산자는 효율적으로 구현하는 데 어려움이 있기 때문에 SQL에서는 제공하지 않음

상관 중첩 질의 (계속) - skip CONTAINS 연산자는 두 집합을 비교하여 한 집합이 다른 집합 내에 모든 값들을 포함하면 TRUE를 반환함 관계대수의 division 연산과 유사함 Query 3: 5번 부서가 담당하는 모든 프로젝트에 근무하는 사원들의 이름을 검색하시오. Q3: SELECT FNAME, LNAME FROM EMPLOYEE WHERE ( (SELECT PNO FROM WORKS_ON WHERE SSN=ESSN) CONTAINS (SELECT PNUMBER FROM PROJECT WHERE DNUM=5) )

상관 중첩 질의 (계속) - skip Q3에서, 두 번째 중첩 질의(외부 질의와는 상관 관계가 없음)는 5번 부서를 담당하는 모든 프로젝트들의 프로젝트 번호를 검색함 각 사원 튜플에 대해서 첫 번째 중첩 질의(외부 질의와 상관 관계가 있음)는 그 사원이 일하는 프로젝트 번호들을 구함

EXISTS 함수 EXISTS는 상관 중첩 질의의 결과가 빈(튜플을 포함하지 않음)것인지 아닌지를 검사하는 데 사용 다음 장에서 EXISTS를 사용하여 질의 12를 Q12B처럼 다른 형태로 나타낼 수 있음

EXISTS 함수 (계속) Query 12: 부양가족의 성(FNAME)과 같은 사원들의 이름을 검색하시오. Q12B: SELECT FNAME, LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN AND FNAME=DEPENDENT_NAME)

EXISTS 함수 (계속) Query 6: 부양가족이 없는 사원들의 이름을 검색하시오. Q6: SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN) Q6에서, 상관 중첩 질의는 EMPOYEE 튜플과 관계 있는 모든 DEPENDENT 튜플을 검색 만약 DEPENDENT 튜플들이 존재하지 않으면, EMPLOYEE 튜플이 선택됨 EXISTS 함수는 SQL 표현 능력을 높이기 위해 필요함

명시적 집합 중첩 질의 대신에 WHERE 절에 값들의 명시적 집합을 사용할 수 있음 Query 13: 프로젝트 번호 1,2,3에서 일하는 모든 사원의 주민등록 번호를 검색하시오. Q13: SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3)

SQL 질의에서의 NULL SQL은 애트리뷰트의 NULL(널)인지 검사하는 질의들이 있음 알려지지 않는 값, 이용할 수 없는 값, 적용할 수 없는 값 SQL에서는 NULL과 비교하기 위해 IS나 IS NOT을 사용 그 이유는 각 NULL값은 모든 다른 NULL 값과는 다르다고 간주 = 형태의 비교가 적당하지 않음 Query 14: 감독관이 없는 모든 종업원들의 이름을 검색하시오. Q14: SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL Note: 조인 조건을 지정했을 때, 조인 애트리뷰트에 대해서 NULL 값을 갖는 튜플들은 결과에 나타나지 않음

SQL2에서 조인된 릴레이션 특징 FROM 절에 조인 연산의 결과를 지정할 수 있음 다른 릴에레이션처럼 보이지만 조인연산의 결과임 여러 가지 유형의 조인을 명시할 수 있도록 허용 JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN, etc

SQL2에서 조인된 릴레이션 특징 (계속) Examples: Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E S WHERE E.SUPERSSN=S.SSN Q8는 다음과 같이 쓰여질 수 있음: Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN=S.SSN) Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO

SQL2에서 조인된 릴레이션 특징 (계속) 이전 Q1은 다음과 같이 쓰여질 수 있음 : Q1: SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO) WHERE DNAME='Research’ 이거나 : Q1: SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE NATURAL JOIN DEPARTMENT AS DEP (DNAME, DNO, MSSN, MSDATE) WHERE DNAME='Research’

SQL2에서 조인된 릴레이션 특징 (계속) - skip Another Example; Q2 는 조인된 테이블들에서 다중 조인 형태로 취할 수 있음 조인된 테이블의 개념을 사용하여 Q2로 표현 Q2: 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 등을 포함 Query 15: 종업원의 봉급의 합, 최고 봉급, 최저 봉급, 평균 봉급을 구하시오. Q15: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE

집단함수 (계속) Query 16: ‘Research’ 부서에 있는 모든 종업원들의 봉급의 합과 최고 봉급, 최소 봉급, 평균 봉급을 구하시오. Q16: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research'

집단함수 (계속) Queries 17 and 18: (Q17) 회사 내의 총 종업원의 수와, (Q18) ‘Research’ 부서에 속해 있는 종업원의 수를 검색하시오. Q17: SELECT COUNT (*) FROM EMPLOYEE Q18: SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research’

그룹화 많은 경우에, 릴레이션 내에 있는 튜플들의 여러 부분 집단으로 나누고 집단 함수를 적용하기도 함 튜플의 각 부분 집합은 그룹화 애트리뷰트(들)에 대해 값은 튜플들로 구성됨 각 그룹마다 독립적으로 집단 함수들을 적용할 수 있음 SQL은 SELECT 절에 나타나는 애트리뷰트들 중에서 그룹화 애트리뷰트를 GROUP BY절에 명시

그룹화 (계속) Query 20: 각 부서에 대해서 부서번호, 부서 내에 있는 종업원의 수 평균 봉급을 검색하시오. Q20: SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO Q20에서, EMPLOYEE 튜플들을 그룹화 애트리뷰트인 DNO 값이 같은 튜플들끼리 여러 그룹으로 분할함 각 그룹의 튜플들에 대하여 COUNT와 AVG 함수를 적용함 SELECT 절에는 그룹화 애트리뷰트와 각 튜플들의 그룹에 적용할 집단함수들만 포함함 조인조건은 그룹화와 함꼐 사용할 수 있음

그룹화 (계속) Query 21: 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 그 프로젝트에서 근무하는 사원들의 수를 검색하시오. Q21: SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME 이 경우, 두 개의 릴레이션을 조인한 후에 그룹화와 집단함수가 적용됨

HAVING 절 때로 어떤 조건들을 만족하는 그룹들에 대해서만 집단함수들의 값을 구하기도 함

HAVING절 (계속) Query 22: 두 명 이상의 사원이 근무하는 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 프로젝트에서 근무하는 사원의 수를 검색하시오. Q22: SELECT PNUMBER, PNAME, COUNT(*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2

부분 문자열 비교 LIKE 비교 연산자는 문자열의 일부에 대해 비교조건을 명시 부분 문자열은 두 개의 예약된 문자를 사용 ‘%’(또는 ‘*’)은 0보다 큰 임의의 개수의 문자로 대체 ‘-’는 임의의 한 개의 문자로 대체

부분 문자열 비교 (계속) Query 25: 주소가 ‘Houston, Texas’인 모든 종업원을 검색하시오. 여기에, ADDRES 애트리뷰트의 값은 부분 문자열 ‘Houston, TX’을 포함해야 함 Q25: SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston,TX%’

부분 문자열 비교 (계속) Query 26: 1950년대에 태어난 모든 사원을 검색하시오. 날짜 형식에 의해서 문자열의 8번째 문자가 ‘5’이어야 한다. BDATA 값은 임의의 한 문자를 하나의 밑줄(_)로 대체하여 ‘________5_‘의 문자열을 사용함 Q26: SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDATE LIKE '_______5_'

산술 연산자 SQL 질의 결과에서 표준 산술 연산자 더하기(+), 빼기(-), 곱하기(*), 나누기(/)를 수치값에 적용할 수 있음 Query 27: ‘ProductX’ 프로젝트에 참여하는 모든 사원의 급여를 10% 올린 경우의 급여를 구하시오. Q27: SELECT FNAME, LNAME, 1.1*SALARY FROM EMPLOYEE, WORKS_ON, PROJECT WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX’

ORDER BY ORDER BY 절은 하나 이상의 애트리뷰트 값 순서로 질의 결과 튜플을 정렬할 수 있음 Query 28: 사원 및 각 사원이 근무하는 프로젝트들의 리스트를 검색하는 데, 부서 이름 순서대로, 그리고 각 부서 내에서는 사원의 성과 이름의 알파벳 순서대로 구하시오. Q28: SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME, LNAME

ORDER BY (계속) 디폴트 정렬은 오름차순임 Example 내림차순으로 정렬하고자 한다면 키워드 DESC로 지정 키워드ASC는 오름차순 정렬을 명시적으로 지정할 때 사용함 Example 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>]

SQL 질의에 대한 요약 (계속) SELECT 절은 결과에 포함될 애트리뷰트들이나 함수를 나열함 FROM 절은 질의에서 필요한 모든 릴레이션(별명)들을 명시함 중첩 질의들에 사용되는 릴레이션들은 명시하지 않음 WHERE 절은 조인조건을포함하여 FROM 절에 명시된 릴레이션들로부터 튜플들을 선택하기 위한 조건들을 명시 GROUP BY절은 그룹화 애트리뷰트를 명시 HAVING 절은 선택된 튜플들의 그룹들에 대한 조건을 명시 ORDER BY절은 질의 결과를 출력하는 순서를 명시 질의는 WHERE절, GROUP BY절과 HAVING절의 순서로 적용함으로써 평가됨

SQL에서 삽입, 삭제, 갱신문 SQL에서 데이터베이스를 갱신하기 위해 사용되는 세가지 명령 INSERT, DELETE, UPDATE

INSERT INSERT의 간단한 형식은 한 릴레이션에 튜플 한 개를 추가하는 데 사용 애트리뷰트 값들의 순서는 CREATE TABLE 명령에서 명시한 애트리뷰트들의 순서와 같아야 함

INSERT (계속) Example: U1: INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini', '653298653', '30-DEC-52', '98 Oak Forest,Katy,TX', 'M', 37000,'987654321', 4 ) INSERT 명령의 두 번째 형식에서는 새로운 튜플에서 명시한 값에 대응하는 애트리뷰트 이름들을 명시적으로 나타낼 수 있음 Null이 허용된 애트리뷰트에는 값을 명시하지 않아도 됨 Example: FNAME, LNAME, DNO, SSN 애트리뷰트의 값만 알고 있는 새로운 사원 튜플을 EMPLOYEE 릴레이션에 삽입하시오. U1A: INSERT INTO EMPLOYEE (FNAME, LNAME, SSN) VALUES ('Richard', 'Marini', '653298653')

INSERT (계속) Important Note: 데이터베이스에서 갱신이 될 때, DBMS는 DDL 명령에서 명시된 무결성 제약조건을 지원해야 함 INSERT 명령의 한 유형은 한 질의의 결과로 검색되는 다수의 튜플을 릴레이션에 삽입할 수 있음

INSERT (계속) Example: 부서이름, 각 부서의 사원 수, 각 부서의 총급여 액수를 갖는 임시 테이블을 생성하시오. U3A에서 DEPTS_INFO 테이블을 생성하고 U3B의 질의로 데이터베이스에서 검색한 요약 정보를 이 테이블에 적해함 U3A: CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(10), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER); U3B: 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 ;

INSERT (계속 ) Note: DEPTS_INFO 테이블은 최신정보을 가지고 있지 않을 수도 있음 U3B를 수행한 후에 DEPARTMENT나 EMPLOYEE 릴레이션에서 튜플들을 갱신한다면 DEPTS_INFO에 있는 정보는 이런 변경을 반영하지 않는 상태가 됨 DEPTS_INFO 테이블을 최신정보로 유지하려면 뷰를 생성해야 함

DELETE 릴레이션에서 튜플들을 제거하는 명령 삭제할 튜플들의 조건을 나타내는 WHERE절을 포함함 한번에 한 테이블 내의 튜플들만 삭제함 (CASCADE가 참조 무결성 제약조건에 명시되어 있지 않으면) WHERE 절을 생략한 경우에는 테이블 내의 모든 튜플을 삭제 테이블은 데이터베이스 내에서 빈 테이블로 남게 됨 WHERE 절의 조건을 만족하는 튜플 수에 따라 삭제함

DELETE (계속) Examples: U4A: DELETE FROM EMPLOYEE WHERE LNAME='Brown’ U4B: DELETE FROM EMPLOYEE WHERE SSN='123456789’ U4C: DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research') U4D: DELETE FROM EMPLOYEE

UPDATE 하나 이상의 튜플들의 애트리뷰트 값을 수정하기 위해 사용 WHERE절은 릴레이션에서 수정할 튜플들을 선택하는 데 사용됨 SET절은 변경할 애트리뷰트와 그들의 새로운 값을 명시함 UPDATE 명령은 같은 릴레이션 내에서 여러 튜플을 수정할 수 있음

UPDATE (계속) Example: 프로젝트 번호 10인 튜플에 대해 PLOCATION을 ‘Bellaire’로 변경하고 담당부서인 DNUM을 5로 변경하시오. U5: UPDATE PROJECT SET PLOCATION = 'Bellaire', DNUM = 5 WHERE PNUMBER=10

UPDATE (계속) Example: ‘Research’ 부서에 있는 모든 종업원들의 봉급을 10% 인상하시오. U6: UPDATE EMPLOYEE SET SALARY = SALARY *1.1 WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research') 변경된 SALARY 값은 원래 SALARY 값에 영향을 받음 오른쪽 SALARY 애트리뷰트는 수정되기 전의 SALARY 값을 왼쪾 SALARY 애트리뷰트는 수정된 후의 새로운 SALARY 값을 의미함