Chapter 5

1. Chapter 5 SQL: 확장된 질의, 주장, 트리거, 뷰

2. 목 차 5.1 더 복잡한 SQL 검색 질의 5.2 주장으로 제약조건 및 트리거로 동작 5.3 SQL에서 뷰 (가상 테이블) 5.4 SQL에서 스키마 변경문

3. 5.1 더 복잡한 SQL 검색 질의 • 널값 비교 • 중첩 질의와 집합 비교 • 상관 중첩 질의 • SQL의 EXIST함수와 UNIQUE함수 • 명시적 집합과 애트리뷰트의 재명명 • 조인된 테이블과 외부 조인 • 집단 함수 • GROUP BY와 HAVING 절

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

5. 중첩 질의와집합 비교 • 완전한 SELECT 질의(중첩 질의)는 다른 질의(외부 질의)의 WHERE 절 내에 명시될 수 있음 • 이전 질의들의 대부분은 중첩을 사용하는 선택적인 형태에 명시될 수 있음 • Query 1: Research에서 근무하는 모든 사원의 이름과 주소를 검색하시오..Q1: SELECT Fname, Lname, Address FROM EMPLOYEE WHERE Dno IN (SELECT Dnumber FROM DEPARTMENT WHERE Dname='Research' )

6. 중첩 질의와집합 비교(계속) • 중첩 질의는 Research 부서의 번호(number)를 선택함 • 외부 질의는 DNO 값이 중첩 질의 결과에 있으면 EMPLOYEE 튜플을 선택함 • 비교 연산자 IN은 하나의 값 v와 값들의 집합 V를 비교함 • v가 V에서 요소들(elements) 중의 하나이면 TRUE이 됨 • 일반적으로 중첩 질의들을 여러 레벨들을 포함할 수 있음 • 모호한 애트리뷰트에 대한 참조 규칙은 가장 안쪽의 중첩 질의에서 선언된 릴레이션에 속함 • 이 예제에서, 중첩 질의는 외부 쿼리에 대해 상관없음

7. 중첩 질의와집합 비교 (계속) • Query 4:일반 직원이든, 프로젝트를 담당하는 부서의 관리자이든 간에 성이 ‘Smith’인 사원을 포함하는 모든 프로젝트에 대해서 프로젝트 번호의 리스트를 검색하라.Q4A: SELECTDISTINCT PnumberFROM PROJECTWHERE Pnumber IN (SELECT Pnumber FROM PROJECT DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = ‘Smith’) OR Pnumber IN (SELECT Pnumber FROM PROJECT DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = ‘Smith’)

8. 중첩 질의와집합 비교 (계속) • 중첩 질의에 여러 개의 애트리뷰트 조합도 사용 가능함 SELECTDISTINCT EssnFROM WORKS_ONWHERE (Pno, Hours) IN (SELECT Pno, Hours FROM WORKS_ON WHERE Essn = ‘123456789’ ) ; • 중첩 질의에 사용 가능한 연산 : IN, NOT IN, ANY(또는 SOME)과 함께 결합하여 >, >=, <, <=, <> 등의 비교 연산자 SELECT Lname, Fname FROM EMPLOYEE WHERE Salary > ALL ( SELECT Salary FROM EMPLOYEE WHERE Dno = 5 ) ;

9. 상관 중첩 질의 • 만약 중첩 질의의 WHERE 절에 있는 조건에서 외부 질의에 선언된 릴레이션의 애트리뷰트를 참조하는 경우에 두 질의는 상관된 질의라고 함 • Query 16:부양가족의이름과 (Fname)과 성별이 같은 사원들의 이름을 검색하시오. Q16: SELECT E.Fname, E.LnameFROM EMPLOYEE AS EWHERE E.Ssn IN (SELECT EssnFROM DEPENDENT AS DWHERE E.Fname=D.Dependent_name AND E.Sex=D.Sex );

10. 상관 중첩 질의 (계속) • Q16에서, 중첩 질의는 외부 질의에서의 각 튜플에 대해 서로 다른 결과를 가짐 • 중첩된 SELECT… FROM… WHERE… 블록과 =과 IN 비교 연산자를 이용해서 작성된 질의는 항상 단일 블록 질의로 변환할 수 있음 • 예를 들어, Q16는 Q16A로 쓰여질 수 있음 Q16A: SELECT E.Fname, E.nameFROM EMPLOYEE AS E, DEPENDENT AS DWHERE E.Ssn=D.Essn AND E.Sex = D. Sex AND E.Fname=D.Dependent_name

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

12. EXISTS 함수 (계속) • Query 16:부양가족의이름과 (Fname)과 성별이 같은 사원들의 이름을 검색하시오. Q12B: SELECT E.Fname, E.Lname FROM EMPLOYEE AS EWHEREEXISTS ( SELECT *FROM DEPENDENT AS DWHERE E.Ssn=D.Essn AND E.Sex=D.Sex AND E.Fname=D.Dependent_name)

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

14. EXISTS 함수 (계속) • Query 7:부양가족을 적어도 한명 이상 가진 관리자의 이름을 검색하라.Q7: SELECT Fname, LnameFROM EMPLOYEEWHEREEXISTS (SELECT * FROM DEPENDENT WHERE Ssn=Essn) AND EXISTS (SELECT * FROM DEPARTMENT WHERE Ssn=Mgr_ssn) ;

15. EXISTS 함수 (계속) • Query 3: 5번 부서가 담당하는 모든 프로젝트에 근무하는 사원들의 이름을 검색하라.Q3A: SELECT Fname, LnameFROM EMPLOYEEWHERE NOT EXISTS ( ( SELECT PnumberFROM PROJECTWHERE Dnum=5 ) EXCEPT (SELECT Pno FROM WORKS_ON WHERE Ssn=Essn) );

16. EXISTS 함수 (계속) • Query 3: 5번 부서가 담당하는 모든 프로젝트에 근무하는 사원들의 이름을 검색하라.Q3B: SELECT Fname, LnameFROM EMPLOYEEWHERE NOT EXISTS ( SELECT *FROM WORKS_ON BWHERE B.Pno IN (SELECT Pnumber FROM WORKS_ON WHERE Dnum=5 ) AND NOT EXISTS (SELECT * FROM WORKS_ON C WHERE C.Essn=Ssn AND C.Pno=B.Pno)));

17. 명시적 집합과애트리뷰트의 재명명 • 중첩 질의 대신에 WHERE 절에 값들의 명시적 집합을 사용할 수 있음 • Query 17:프로젝트 번호 1,2,3에서 일하는 모든 사원의 사회보장 번호를 검색하시오. Q13: SELECT DISTINCT Essn FROM WORKS_ON WHERE Pno IN (1, 2, 3) ;

18. 명시적 집합과애트리뷰트의 재명명 • 질의 결과에 나타나는 임의의 애트리뷰트에 새 이름 부여 Q8A: SELECT E.Lname AS Employee_name, S.Lname AS Supervisor_name FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn = S.Ssn ;

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

20. SQL2에서 조인된 릴레이션 특징 (계속) • 이전 Q1은 다음과 같이 쓰여질 수 있음 :Q1A: SELECT Fname, Lname, Address FROM (EMPLOYEE JOIN DEPARTMENT ON Dnumber=Dno) WHERE Dname='Research’ ;이거나 :Q1B: SELECT Fname, Lname, Address FROM (EMPLOYEE NATURAL JOIN DEPARTMENT AS DEP (Dname, Dno, Mssn, Msdate) WHERE DNAME='Research’ ;

21. SQL2에서 조인된 릴레이션 특징 (계속) • Examples:Q8: SELECT E.Lname, S.LnameFROM EMPLOYEE E SWHERE E.Super_ssn=S.SsnQ8는 다음과 같이 쓰여질 수 있음:Q8: SELECT E.Lname AS Employee_name, S.Lname AS Supervisor_nameFROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S ON E.Super_ssn=S.Ssn) ;

22. SQL2에서 조인된 릴레이션 특징 (계속) • Another Example; • Q2 는 조인된 테이블들에서 다중 조인 형태로 취할 수 있음 • 조인된 테이블의 개념을 사용하여 Q2A로 표현 Q2A: SELECT Pnumber, Dnum, Lname, Address, Bdate, FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber) JOIN EMPLOYEE ON Mgr_ssn=Ssn) WHERE Plocation='Stafford’ ;

23. SQL2에서 조인된 릴레이션 특징 (계속) • 오라클에서는 LEFT, RIGHT, FULL OUTER JOIN을 위해 +=, =+, +=+ 연산자를 각각 사용한다.Q8C: SELECT E.Lname, S.Lname FROM EMPLOYEE E, EMPLOYEE S WHERE E.Super_ssn += S.Ssn ;

24. SQL에서 집단함수 • SQL에서는 COUNT, SUM, MAX, MIN, AVG 등을 포함 • Query 19:종업원의 봉급의 합, 최고 봉급, 최저 봉급, 평균 봉급을 구하시오. Q19: SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary) FROM EMPLOYEE ;

25. SQL에서 집단함수 (계속) • Query 20: ‘Research’ 부서에 있는 모든 사원들의 봉급의 합과 최고 봉급, 최소 봉급, 평균 봉급을 구하시오. Q20: SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber )WHERE DNAME='Research'

26. SQL에서 집단함수 (계속) • Queries 21 and 22: (Q21) 회사의 총 tk원의 수와, (Q22) ‘Research’ 부서에 근무하는 총 사원의 수를 검색하시오. Q21: SELECT COUNT (*) FROM EMPLOYEE Q22: SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENTWHERE Dno=Dnumber AND Dname='Research’

27. SQL에서 집단함수 • Query 23:데이터베이스에서 서로 다른 급여들의 개수를 구하라. Q19: SELECT COUNT (DISTINCT Salary) FROM EMPLOYEE ; • Query 5:둘 이상의 부양 가족이 있는 모든 사원의 이름을 구하라. Q5: SELECT Lname, Fname FROM EMPLOYEE WHERE ( SELECT COUNT(*) FROM DEPENDENT WHERE Ssn=Essn ) >= 2;

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

29. 그룹핑: GROUP BY와 HAVING 절(계속) • Query 24:각 부서에 대해서 부서번호, 부서에 속한 사원들의 수, 각 부서에 소속된 사원들의 평균 급여를 구하라. Q24: SELECT Dno, COUNT (*), AVG (Salary)FROM EMPLOYEEGROUP BY Dno ; • Q24에서, EMPLOYEE 튜플들을 그룹화 애트리뷰트인 DNO 값이 같은 튜플들끼리 여러 그룹으로 분할함 • 각 그룹의 튜플들에 대하여 COUNT와 AVG 함수를 적용함 • SELECT 절에는 그룹화 애트리뷰트와 각 튜플들의 그룹에 적용할 집단함수들만 포함함 • 조인조건은 그룹화와 함꼐 사용할 수 있음

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

31. 그룹핑: GROUP BY와 HAVING 절(계속) • 때로 어떤 조건들을 만족하는 그룹들에 대해서만 집단함수들의 값을 구하기도 함 • HAVING절은 집단함수를 적용할 그룹들을 선택하는 데 사용됨

32. 그룹핑: GROUP BY와 HAVING 절(계속) • Query 26:두 명 이상의 사원이 근무하는 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 프로젝트에서 근무하는 사원의 수를 검색하시오. Q26: SELECT Pnumber, Pname, COUNT(*) FROM PROJECT, WORKS_ON WHERE Pnumber=Pno GROUP BY Pnumber, Pname HAVING COUNT (*) >2 • WHERE 절에 있는 선택 조건은 집단 함수를 적용할 투플들을 한정하고 • HAVING 절은 집단 함수를 적용할 그룹들을 선택한다

33. 그룹핑: GROUP BY와 HAVING 절(계속) • Query 27:각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 5번 부서에 근무하면서 프로젝트에서 근무하는 사원의 수를 검색하라. Q27: SELECT Pnumber, Pname, COUNT(*) FROM PROJECT, WORKS_ON, EMPLOYEE WHERE Pnumber=Pno AND Ssn=Essn AND Dno=5 GROUP BY Pnumber, Pname • WHERE 절에서 명시한 조건, 즉 5번 부서에 근무하는 투플들로만 제한함 • 따라서 집계함수 COUNT에는 5번 부서에 근무하는 사원의 수만 계산됨

34. 그룹핑: GROUP BY와 HAVING 절(계속) • 질의28 (잘못 작성된 예): 6명 이상의 사원이 근무하는 부서에 대해서 각 부서 번호와 40,000 달러가 넘는 급여를 받는 사원의 수를 검색하라. SELECT Dname, COUNT(*) FROM DEPARTMENT, EMPLOYEE WHERE Dnumber=Dno AND Salary > 40000 GROUP BY Dname HAVINGCOUNT (*) > 5; • WHERE 절에서 급여가 40,000만 달러가 넘는 투플들을 한정하였고, 따라서 HAVING절의 COUNT 함수는 40,000만 달러가 넘는 사원들의 수가 6명 이상인 그룹만 선택함  원하는 결과와 다름. • 즉, WHERE절을 먼저 적용한 후에 GROUP BY, HAVING 절을 적용함. • 질의 Q28처럼 작성해야 제대로 된 결과를 얻을 수 있음.

35. 그룹핑: GROUP BY와 HAVING 절(계속) • 질의28: 6명 이상의 사원이 근무하는 부서에 대해서 각 부서 번호와 40,000 달러가 넘는 급여를 받는 사원의 수를 검색하라. Q28: SELECT Dnumber, COUNT(*) FROM DEPARTMENT, EMPLOYEE WHERE Dnumber=Dno AND Salary > 40000 AND ( SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING COUNT (*) >5 ) ; • 위의 질의가 제대로 된 결과를 생성함.

36. 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>]

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

38. 5.2 주장으로제약조건 및 트리거로 동작 명시 • 관계 모델의 기본적인 제약조건들을 제외한 추가적인 제약 조건들을 명시할 때 사용 • CREATE ASSERTION • CREATE TRIGGER

39. 주장으로 일반 제약조건 명시하기 • 일반적인 제약 조건을 선언적 주장을 통해 명시함 • (예) “사원의 급여가 자신이 근무하는 부서의 관리자의 급여보다 많으면 안 된다”라는 제약 조건 CREATE ASSERTION SALARY_CONSTRAINT CHECK ( NOT EXISTS ( SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.Salary > M.Salary AND E.Dno=D.Dnumber AND D.Mgr_ssn=M.Ssn ) );

40. 주장으로 일반 제약조건 명시하기 (계속) • 주어진 조건을 위해하는 질의를 명시 • NOT EXISTS절 내에 포함시킴 • 질의의 결과가 공집합이 되어야 함 • 이 질의의 결과가 공집합이 아니면 주장은 위배됨

41. SQL의트리거에 대한 소개 • 목적: 조건이 발생할 때 데이터베이스를 모니터하기 위해 행동의 유형을 명시 • 트리거는 주장과 유사한 구문으로 표기되며 다음 사항을 포함함 • 이벤트 (e.g., DB 갱신 연산) : BEFORE, AFTER 키워드와 함께 사용 • 조건: 트리거 이벤트 발생시검사할 선택적인 조건 명시 • 동작: 해당 조건이 만족되면 수행

42. SQL의트리거에 대한 소개 (계속) • 삽입과 갱신 연산을 하는 동안 사원의 월급을 그의 관리자와 비교하기 위한 트리거의 사용 R5: CREATE TRIGGER SALARY_VIOLATION 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 );

43. 5.3 SQL에서 뷰 (가상 테이블) • SQL에서 뷰는 다른 테이블들에서 유도된 “가상” 테이블 • 뷰에 적용할 수 있는 갱신 연산들은 제한됨 • 물리적인 형태로 저장되지는 않기 때문에 • 뷰에 대한 질의는 아무런 제한을 받지 않음 • 몇개 연산들을 뷰로 표현하여 사용하는데 편리함

44. SQL에서 뷰 (가상 테이블) (계속) • 뷰를 정의하는 SQL 명령: CREATE VIEW • 뷰의 정의는 (가상) 테이블 이름 • 애트리뷰트 이름들의 목록 • 함수나 산술 연산들을 적용하거나, 기본 릴레이션의 애트리뷰트 이름과 다른 이름을 사용하고자 할 때 이용 • 뷰의 내용을 나타내는 질의

45. SQL에서 뷰 (가상 테이블) (계속) • 뷰 (가상 테이블)의 예제 V1:CREATE VIEWWORKS_ON1 ASSELECT Fname, Lname, Pname, Hours FROM EMPLOYEE, PROJECT, WORKS_ON WHERE Ssn=Essn AND Pno=Pnumber ; V2: CREATE VIEW DEPT_INFO(Dept)name, No_of_emps, Total_sal) ASSELECT Dname, COUNT(*), SUM(Salary) FROM DEPARTMENT, EMPLOYEE WHERE Dnumber=Dno GROUP BY Dname ;

46. SQL에서 뷰 (가상 테이블) (계속) • 생성된 뷰 (가상 테이블) 에 대해 SQL 질의를 명시함 QV1: SELECT Fname, Lname FROM WORKS_ON1 WHERE Pname=‘ProductX’; • 어떤 뷰가 더 이상 필요하지 않으면 뷰를 제거함 DROP VIEW WORKS_ON1;

47. 뷰의구현, 뷰 갱신, 및 온라인 뷰 • 질의수정(query modification) 방식 • 뷰에 대한 질의를 기본 테이블들에 대한 질의로 변환하여 처리 • 단점: 복잡한 질의로 정의된 뷰들은 비효율적 • 특히 짧은 시간 내에 뷰에 많을 질의가 적용될 때 • QV1 질의의 수정 예: SELECT Fname, Lname FROM EMPLOYEE, PROJECT, WORKS_ON WHERE Ssn=Essn AND Pno=Pnumber AND Pname=‘ProductX’ ;

48. 뷰의구현, 뷰 갱신, 및 온라인 뷰(계속) • 뷰의 실체화(view materialization) • 임의 뷰 테이블을 물리적으로 생성하고 유지하는 방식 • 가정: 뷰에 다른 질의들이 사용됨 • 문제점: 기본 테이블이 갱신되면 뷰 테이블도 변경해야 함 • 해결방법: 오버헤드가 적은 점진적 갱신(incremental update)기법 필요

49. 뷰의구현, 뷰 갱신, 및 온라인 뷰(계속) • 집단함수를 사용하지 않는 단일 뷰의 갱신 • 뷰의 갱신은 단일 기본 테이블에 대한 갱신으로 사상될 수 있음 • 조인을 포함하는 뷰의 갱신 • 기본 릴레이션들에 대한 갱신 동작으로 사상될 수 있음 • 경우에 따라 여러가지 갱신 방법으로 사상될 수 있음

50. SQL에서 뷰 (가상 테이블) (계속) • 뷰 갱신 질의의 예: UV1: UPDATE WORKS_ON1 SET Pname=‘ProductY’ WHERE Lname=‘Smith’ AND Fname=‘John’ AND Pname=‘ProductX’;