Copyright 2016 Ramez Elmasri and Shamkant B Navathe
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
CHAPTER 6 SQL 추가 기능: 복잡한 질의, 트리거, 뷰, 및 스키마 갱신 Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 2
널과 세가지-값 논리를 포함하는 비교 (cont’d. ) 6. 1 Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 6
널과 세가지-값 논리를 포함하는 비교 (cont’d. ) n SQL은 애트리뷰트 값이 NULL 인지 확인하는 질의 를 허용함 n IS 또는 IS NOT NULL Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 7
중첩 질의 (cont’d. ) Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 9
중첩 질의 (cont’d. ) n 비교에 값의 튜플(짝)을 사용 n 괄호 안에 이들을 위치시킴 Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 10
상관(Correlated) 중첩 n n n = 또는 IN 비교 연산자를 사용하는 중첩된 쿼리들 은 하나의 단일 블록으로 합쳐질 수 있음: ㅖ를 들 어, Q 16은 다음과 같이 쓸 수 있습니다: Q 16 A: 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=D. Dependent_name; 상관 중첩 질의 n 외부 질의의 각 튜플에 대해 한번씩 평가(계산)됨 Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 13
EXISTS 사용 Q 7: SELECT Fname, Lname FROM Employee WHERE EXISTS (SELECT * FROM DEPENDENT WHERE Ssn= Essn) AND EXISTS (SELECT * FROM Department WHERE Ssn= Mgr_Ssn) Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 15
SQL에서 “for all”을 달성하기 위한 이 중 부정 n Q 3 B: SELECT Lname, Fname FROM EMPLOYEE WHERE NOT EXISTS ( SELECT * FROM WORKS_ON B WHERE ( B. Pno IN ( SELECT Pnumber FROM PROJECT WHERE Dnum=5 AND NOT EXISTS (SELECT FROM WORKS_ON C WHERE C. Essn=Ssn AND C. Pno=B. Pno ))); * 위 질의는 더 직접적으로 표현한 것임: L사원이 참여하지 않는, 5번 부서에서 관리하는 프로젝트가 존재하지 않는 사원들의 이름을 검색하시오. Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 17
NATURAL JOIN (자연조인) n NATURAL JOIN을 사용하여 서로 조인할 수 있도록, 한 릴 레이션의 애트리뷰트를 재명명함: Q 1 B: SELECT Fname, Lname, Address FROM (EMPLOYEE NATURAL JOIN (DEPARTMENT AS DEPT (Dname, Dno, Mssn, Msdate))) WHERE Dname=‘Research’; 위 질의는 EMPLOYEE. Dno = DEPT. Dno 라는 묵시적인 조 인 조건을 포함하고 있음 Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 21
Example: LEFT OUTER JOIN SELECT E. Lname AS Employee_Name S. Lname AS Supervisor_Name FROM Employee AS E LEFT OUTER JOIN EMPLOYEE AS S ON E. Super_ssn = S. Ssn) ALTERNATE SYNTAX: SELECT E. Lname , S. Lname FROM EMPLOYEE E, EMPLOYEE S WHERE E. Super_ssn + = S. Ssn Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 23
FROM절에서 다중 JOIN n n FULL OUTER JOIN – LEFT 및 RIGHT OUTER JOIN 결과를 합친 것 다중 조인을 위해 중첩 JOIN을 명시: Q 2 A: SELECT Pnumber, Dnum, Lname, Address, Bdate FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber) JOIN EMPLOYEE ON Mgr_ssn=Ssn) WHERE Plocation=‘Stafford’; Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 24
집계 결과의 재명명 n 아래 질의는 EMPLOYEE 테이블에서 계산된 값들로 이루 어진 단일 행을 리턴함 : Q 19: n SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary) FROM EMPLOYEE; 이 결과에 새로운 애트리뷰트 이름을 표시할 수 있음 : Q 19 A: SELECT SUM (Salary) AS Total_Sal, MAX (Salary) AS Highest_Sal, MIN (Salary) AS Lowest_Sal, AVG (Salary) AS Average_Sal FROM EMPLOYEE; Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 26
SQL에서 집계 함수들 (cont’d. ) n 집계 함수를 특정 열에 적용할 때, NULL 값을 버림 Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 27
GROUP BY의 예 n 그룹화 애트리뷰트들은 SELECT 절내에 나타나야 함: Q 24: FROM GROUP BY n n SELECT EMPLOYEE Dno; Dno, COUNT (*), AVG (Salary) 그룹화 애트리뷰트에 가능한 값으로 NULL이 있으면, NULL값을 위한 별도의 그룹을 생성함 (예, 위 질의에서 Dno가 널인 그룹을 생성함) GROUP BY 가 JOIN 결과에 적용될 수 있음: Q 25: SELECT Pnumber, Pname, COUNT (*) FROM PROJECT, WORKS_ON WHERE Pnumber=Pno GROUP BY Pnumber, Pname; Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 30
그룹화: GROUP BY 및 HAVING 절 (cont’d. ) n HAVING 절 n n 그룹을 선택하거나 거절하기 위한 조건을 제공함 : Query 26. 두명 이상의 사원이 참여하는 각 프로젝트에 대해, 프로젝 트 번호, 프로젝트 이름, 및 그 프로젝트에 참여하는 사원들의 수를 검 색하시오. Q 26: FROM WHERE GROUP BY HAVING SELECT Pnumber, Pname, COUNT (*) PROJECT, WORKS_ON Pnumber=Pno Pnumber, Pname COUNT (*) > 2; Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 31
WHERE 및 HAVING 절의 결합 n n 고려할 질의: 5명 이상의 사원이 일하는 부서들에 대해, 이 들 각 부서에서 $40, 000 이상의 월급을 받는 사원들의 전 체 숫자를 계산하시오. 틀린 질의: SELECT FROM WHERE GROUP BY HAVING Dno, COUNT (*) EMPLOYEE Salary>40000 Dno COUNT (*) > 5; Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 32
WHERE 및 HAVING 절의 결합 (continued) 올바른 표현의 질의: n 주의: WHERE 절은 튜플별로 적용되지만, HAVING은 튜플들의 전체 그룹에 적용됨. Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 33
WITH의 예 n Q 28을 수행하는 또다른 방법: n Q 28’: WITH BIGDEPTS (Dno) AS ( SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING COUNT (*) > 5) SELECT Dno, COUNT (*) FROM EMPLOYEE WHERE Salary>40000 AND Dno IN BIGDEPTS GROUP BY Dno; Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 35
CASE의 사용 예 n n 아래 예제는 사원들이 서로 다른 부서별로 서로 다 른 급여 인상을 받을 경우를 보여준다. (수정 U 6의 변형임) U 6’: UPDATE EMPLOYEE SET Salary = CASE WHEN Dno = 5 THEN WHEN Dno = 4 THEN WHEN Dno = 1 THEN Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Salary + 2000 Salary + 1500 Salary + 3000 Slide 7 - 37
SQL에서 재귀적인 질의 n n n 동일한 타입의 튜플들간의 재귀적 관계 recursive relationship의 예는 사원과 감독자(멘토)간의 관 계이다. 이 관계는 EMPLOYEE 릴레이션의 외래키 Super_ssn에 의해 표현된다. An example of a recursive operation is to retrieve all supervisees of a supervisory employee e at all levels—that is, all employees e directly supervised by e, all employees e ’ directly supervised by each employee e , all employees e directly supervised by each employee e , and so on. Thus the CEO would have each employee in the company as a supervisee in the resulting table. Example shows such table SUP_EMP with 2 columns (Supervisor, Supervisee(any level)): Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 38
재귀적인 질의의 예 n n Q 29: WITH RECURSIVE SUP_EMP (Sup. Ssn, Emp. Ssn) AS SELECT Supervisor. Ssn, Ssn FROM EMPLOYEE UNION SELECT E. Ssn, S. Sup. Ssn FROM EMPLOYEE AS E, SUP_EMP AS S WHERE E. Supervisor. Ssn = S. Emp. Ssn) SELECT * FROM SUP_EMP; The above query starts with an empty SUP_EMP and successively builds SUP_EMP table by computing immediate supervisees first, then second level supervisees, etc. until a fixed point is reached and no more supervisees can be added Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 39
EXPANDED Block Structure of SQL Queries Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 40
Specifying Constraints as Assertions and Actions as Triggers n n Semantic Constraints: The following are beyond the scope of the EER and relational model CREATE ASSERTION n n Specify additional types of constraints outside scope of built-in relational model constraints CREATE TRIGGER n Specify automatic actions that database system will perform when certain events and conditions occur Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 41
Specifying General Constraints as Assertions in SQL n CREATE ASSERTION n n Specify a query that selects any tuples that violate the desired condition Use only in cases where it goes beyond a simple CHECK which applies to individual attributes and domains Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 42
Introduction to Triggers in SQL n CREATE TRIGGER statement n n Used to monitor the database Typical trigger has three components which make it a rule for an “active database “ (more on active databases in section 25. 1) : n n n Event(s) Condition Action Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 43
USE OF TRIGGERS n AN EXAMPLE with standard Syntax. (Note : other SQL implementations like Postgre. SQL use a different syntax. ) R 5: 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) Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 44
ALTER table 명령문 n Alter table actions include: n n 열 (애트리뷰트)의 추가 또는 제거 열 정의의 변경 테이블 제약조건 추가 또는 제거 Example: n ALTER TABLE COMPANY. EMPLOYEE ADD COLUMN Job VARCHAR(12); Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 56
컬럼과 디폴트 값의 제거 n 열을 제거할 때 n n CASCADE 또는 RESTRICT 선택 CASCADE w는 뷰로 부터 열을 제거할 수 있으며, 만약 그 뷰를 참조하는 다른 뷰들이 없으면 RESTRICT도 가능함 ALTER TABLE COMPANY. EMPLOYEE DROP COLUMN Address CASCADE; n 디폴트 값을 제거 또는 변경할 수 있음: ALTER TABLE COMPANY. DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT; ALTER TABLE COMPANY. DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT ‘ 333445555’; Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 58
Table 6. 2 Syntax Summary of SQL 6. 2 continued on next slide Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 59
Table 6. 2 (continued) Summary of SQL Syntax 6. 2 Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 60
Summary n Complex SQL: n n Nested queries, joined tables (in the FROM clause), outer joins, aggregate functions, grouping Handling semantic constraints with CREATE ASSERTION and CREATE TRIGGER CREATE VIEW statement and materialization strategies Schema Modification for the DBAs using ALTER TABLE , ADD and DROP COLUMN, ALTER CONSTRAINT etc. Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7 - 61
- Slides: 61