Complex Query 2

WITH 문과 CASE 문

WITH : 특정 query 에서 만 사용할 테이블을 정의하게 해준다
Query: For each department that has more than 3 employees, retrieve the 
department number and the number of its employees who are making more than 
$40000

WITH BIGDEPTS(Dno) AS	
	( SELECT Dno
	FROM EMPLOYEE
	GROUP BY Dno
	HAVING COUNT(*) > 3)
SELECT Dno, COUNT(*)
FROM EMPLOYEE
WHERE Salary > 40000 AND Dno IN BIGDEPTS
GROUP BY Dno;

Case 문: 조건을 부여해서 각각 상황에 맞는 처리
ex) 

UPDATE EMPLOYEE
SET Salary =
CASE WHEN Dno = 5 THEN Salary + 2000
     WHEN Dno = 4 THEN Salary + 1500
     ELSE Salary + 0;

Recursive Queries in SQL

ex) 

WITH RECURSIVE SUP_EMP (SupSsn, EmpSsn) AS
	(SELECT Super_ssn, Ssn
	 FROM EMPLOYEE
	 UNION
	 SELECT S.SupSsn, E.Ssn
	 FROM EMPLOYEE AS E, SUP_EMP AS S
	 WHERE E.Super_ssn = S.EmpSsn)
SELECT *
FROM SUP_EMP;

Assertions

제한조건이라 볼 수 있다
다음과 같은 제한 조건이 존재한다 가정.
The salary of an employee must not be greater than the salary of the manager 
of the department that the employee works for

CREATE ASSERTION SALARY_CONSTRAINT
	CHECK ( NOT EXISTS
			(SELECT *
			 FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
			 WHERE E.Salary > M.Salaray AND
			 E.Dno = D.Dnumber AND D.Mgr_ssn = M.Ssn));

CHECK 문은 튜플이 삽입, 갱신될 때 실행된다

SQL Triggers

Trigger 는 다음 형식으로 구성된다
- Event
- Condition
- Action
다음은 Trigger 의 한 예이다
Trigger to compare and employee's salary to his/her supervisor during insert 
or update operations

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_SUPERVIOSR (NEW.Supervisor_ssn, NEW.Ssn);

VIEW

가상 테이블과 같은 개념

해당 테이블에서 컴퓨터 관련 테이블만 뽑아내고 싶다면?

CREATE VIEW COM_STUDENT(Sno,Sname,Year)
AS SELECT Sno, Sname, Year
   FROM STUDENT
   WHERE Dept='컴퓨터';

View 예제 1

CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
   FROM EMPLOYEE, PROJECT, WORKS_ON
   WHERE Ssn=Essn AND Pno=Pnumber;

View 예제 2

CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal)
AS SELECT Dname, COUNT(*), SUM(Salary)
   FROM DEPARTMENT, EMPLOYEE
   WHERE Dnumber=Dno
   GROUP BY Dname;

Query 예제
Retrieve the last name and first name of all employees who work on 'ProjectX'

View 가 있는경우

SELECT Fname, Lname 
FROM WORKS_ON1
WHERE Pname='ProjectX';

View 가 없는 경우

SELECT Fname, Lname
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Pname='ProjectX' AND Ssn=Essn AND Pno=Pnumber;

View 를 사용하는 이유
	query를 간단하게 만들 수 있다
	보안, 허가를 쉽게 관리
	VIEW는 항상 최신 데이터를 반영하게 된다
	VIEW 의 base table 이 삭제된다면 VIEW 또한 삭제된다
	View를 삭제하고 싶다면 DROP 사용

VIEW 가 처음 생성될때 일시적인 view table 이 생성된다
	Incremental Update
		-> Base Table이 변경된다면, view table 또한 변경된다
		-> View 가 계속 참조되면 삭제되지 않는다
		-> 일정 시간동안 참조되지 않는다면 시스템이 자동으로 삭제할 수도 있다

Column subset view

CREATE VIEW SVIEW1
AS SELECT Sno, Dept 
FROM STUDENT;

CREATE VIEW SVIEW2
AS SELECT Sname, Dept
FROM STUDENT;

QUERY 예제:
Update the PNAME attribute of 'John Smith' from 'ProductX' to 'Product

UPDATE WORKS_ON1
SET Pname = 'ProductZ'
WHERE Lname='Smith' AND Fname='John' AND Pname='ProductX'

위와 같은 질의를 수행할 때 베이스 테이블에서 어떻게 업데이트 가 될까??
	-> 모호성
	다음 두가지 의미가 내포

Summary..
	Single Defining Table 즉, 하나의 view 에 대한 테이블은 updatable
		만약 view 가 primary key를 포함한다면
	여러 테이블을 join 하는 view 는 보통 not updatable
	Grouping 과 Aggregate 함수가 적용된 view 는 보통 not updatable
	만약 updatable 이지만 update 를 했을때 문제가 생긴다면?
		WITH CHECK 사용:


다음 예제를 살펴보자

CREATE VIEW EMP_D5
AS SELECT * FROM EMPLOYEE WHERE Dno=5; 


INSERT INTO EMP_D5 VALUES('Andrew',,'Park',123456777,...,3);

위 경우에서 EMP_D5는 Dno = 5 인 사람만 있다
하지만 새롭게 추가 하려는 사람의 Dno = 3이다
그러면 오류,,,
	그래서 WITH CHECK OPTION 을 줄 수 있다
	이 OPTION을 넣게되면 뷰 정의를 위반하는 질의는 허가하지 않는다


VIEW as AUTHORIZATION Mechansm
	SQL 에는 두개의 authorization statement 가 존재한다
		GRANT
		REVOKE
		허가받지 않은 유저들로부터 특정 정보들을 숨길때 사용한다

CREATE VIEW DEPT5EMP AS
SELECT *
FROM EMPLOYEE
WHERE Dno = 5;

CREATE VIEW BASIC_MAP_DATA AS
SELECT Fname,Lname, Address
FROM EMPLOYEE;