과제2. SQL Basic 작성해보기 (피드백 있음)

Q. 아래 표를 참고하여 다음 물음에 답하시오.

Q1. 5번 부서에서 근무하고, 급여(salary)가 30000 이상인 모든 직원의 성명(Fname, Lname)을 검색하라.

SELECT Fname, Lname 
FROM Employee 
WHERE Dno = 5 
	AND salary >= 30000;

결과: 

Fname	Lname
John	Smith
Franklin	Wong
Ramesh	Narayan

 

Q2. ‘ProductX’ 프로젝트에서 주당 10시간 이상 작업한 5번 부서에서 근무하는 모든 직원의성명(Fname, Lname)을 검색하라.

SELECT Fname,Lname 
FROM PROJECT , WORKS_ON, EMPLOYEE 
WHERE EMPLOYEE.Ssn = WORKS_ON.Essn 
AND Hours >= 10 
    AND WORKS_ON.Pno=PROJECT.Pnumber 
    AND Project.Pname= 'ProductX' 
    And Dno=5;

결과:

Fname	Lname
John	Smith
Joyce	English

 

Q3. 자신과 동일한 이름(Fname)을 가진 부양가족(dependent)이 있는 직원의 성명(Fname, Lname) 을 모두 검색하라.

SELECT Fname, Lname 
FROM Employee, Dependent 
WHERE Employee.Ssn = dependent.Essn 
	AND dependent.Dependent_name = employee.Fname;

결과: null

 

Q4. ‘Franklin Wong’이 직접 관리하는 직원의성명(Fname, Lname) 을 모두 검색하라.

SELECT E.Fname, E.Lname 
FROM Employee as E, Employee as S 
WHERE S.Fname = 'Franklin' 
    AND S.Lname = 'Wong' 
    AND S.Ssn = E.Super_ssn;

결과:

Fname	Lname
John	Smith
Ramesh	Narayan
Joyce	English

 

Q5. ‘Research’ 부서에서 근무하는 모든 직원의 성명(Fname, Lname) 을 검색하라. 단, 3번 프로젝트에 참가하고 있는 직원은 제외하도록 한다.

SELECT Fname, Lname 
FROM Employee, Department, Project 
WHERE Employee.Dno = Department.Dnumber
    AND department.Dname = 'Research'	 
    AND Department.Dnumber = Project.Dnum 
    AND Project.Pnumber not in (3);

결과:

Fname	Lname
John	Smith
Joyce	English

 

-> 5번에 대한 피드백:

위 쿼리는 3번 프로젝트에 참여하는 부서를 제외한 것이다.

문제는 3번 프로젝트에 참여하는 직원을 제외하라고 했으므로 아래와 같이 쿼리를 구성해야 한다.

select fname, lname
from employee, department
where dno=dnumber and dname = 'research'
minus
select fname, laname
from employee, works_on
where pno=3 and essn = ssn;

위 쿼리 중 minus 아래 부분은 AND Ssn NOT IN {'666884444', '333445555'} 와 같은 효과이며, 저 ssn은 select essn from workds_on where pno = 3;인 쿼리를 저 {} 안에 넣어주면 된다.

(MINUS는 지원하지 않는 DBMS가 있다.)

SELECT Fname, Lname
FROM Employee, Department
WHERE Dname = 'Research' AND
	Dno = Dnumber AND
	ssn not in (SELECT essn FROM works_on WHERE pno =3);

즉, not in 뒤에 works_on 테이블에 대한 데이터를 검열한 뒤에 not in 으로 처리하게 되므로, works_on테이블을 굳이 FROM 뒤에 넣거나 WHERE 뒤에 조건을 달아서 join을 발생시킬 필요가 없다.

데이터베이스 연산 중에서 JOIN 연산이 비용이 가장 비싸다.
따라서, 조인은 최소화할 수 있다면 최소화하는 것이 합리적인 선택이다.

하지만, DBMS의 query optimizer가 선언된 join을 더 효율적인 join 방식으로 최적화할 순 있지만, not in {} 내부의 서브쿼리를 넣어버리면 DBMS는 해당 부분을 최적화할 수 없어 냅다 실행할 수 밖에 없다. 따라서 DBMS의 쿼리 작성 가이드를 보면 일반적으로 nested query는 사용하지 말라는 문구를 확인할 수 있다.

+++

 

만약 동명이인이 있을 경우에는 의도치 않은 연산 결과를 얻을 수 있으므로, select 뒤에 유일한 값을 갖는 칼럼을 설정해주면 해당 문제를 방지할 수 있을 것이다.