[ DB ] 데이터베이스...9(SQL...5)

본 포스팅은 인하대학교 최원익교수님의 "데이터베이스설계" 수업에서 진행한 프론트, 백엔드 실습 관련 정리하는 포스팅입니다. 백엔드, 프론트엔드는 전문 분야가 아니기에 공부용으로 올리는 포스팅이며 오류사항이 있을 수 있습니다.

 

https://jinwoo-jung.tistory.com/107

[ DB ] 데이터베이스...8(SQL...4)

 

---

 

NULL

[데이터베이스...7]에서 설명 한 것처럼, NULL값에 대한 처리는 IS NULL, IN NOT NULL로 해야한다. 

 

SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPER_SSN IS NULL;

 

EMPLOYEE Table에서 SUPER_SSN이 NULL인 Tuple에 대하여 FNAME, LNAME Column을 출력해라는 의미이다. 실제로 MySQL이 동작할 때는 대, 소문자에 대한 구분이 없으니 참고하자.

 

 

 

중첩질의

중첩 질의란 다른 질의의 WHERE절 내에 완전한 SELECT 질의가 나타나는 형태이다.

 

[데이터베이스...7]에서 설명한 In, Exists 역시 여기에 포함되는데, 문제풀이 형태로 다시한번 공부 해 보자.

 

• 성이 'Smith'인 종업원(일반 직원 혹은 프로젝트를 담당하는 부서의 관리자)이 참여하는 프로젝트의 프로젝트 번호 목록을 작성하시오

작성해야 할 목록은 프로젝트의 프로젝트 번호이므로, SELECT DISTINCT PNUMBER FROM PROJECT이다. 이때, 성이 'Smith'여야 하며, 종업원인데 일반 직원일 수도 있고, 프로젝트를 담당하는 부서의 관리자일 수도 있따. 따라서 조건문은 OR로 연결되어야 한다. 

 

 

일반 직원이면서 성이 'Smith'고 프로젝트의 참여하는 종업원의 PNUMBER는, SELECT PNO FROM WORKS_ON, EMPLOYEE WHERE ESSN = SSN AND LNAME = 'Smith' 이다. Pno는 Pnumber를 참조하고 있기에 Pno를 SELECT 하면 되며, 일을 해야 하면 EMPLOYEE의 Ssn과 WORKS_ON의 Essn이 같아야 한다(참조관계).

 

프로젝트를 담당하는 부서의 관리자면서  성이 'Smith'고 프로젝트의 참여하는 종업원의 PNUMBER는, SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE MGR_SSN = SSN AND DNUM = DNUMBER AND LNAME = 'Smith' 이다. 부서의 관리자여야 하므로, DEPARTMENT Table의 Magr_ssn을 Ssn으로 하는 EMPLOYEE 면서, 그 부서 프로젝트를 담당해야 하므로, PROJECT Table의 Dnum을 Dnumber로 하는 DEPARTMENT여야 한다. 

 

따라서 두 조건을 하나로 이으면 다음과 같다.

SELECT DISTINCT PNUMBER FROM PROJECT WHERE PNUMBER IN (SELECT PNO FROM WORKS_ON, EMPLOYEE WHERE ESSN = SSN AND LNAME = 'Smith' ) 
OR PNUMBER IN (SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE MGR_SSN = SSN AND DNUM = DNUMBER AND LNAME = 'Smith');

 

 

잘 나온지는 각 Table의 Tuple을 비교해보면서 검증 해 보자.

 

• SSN이 333445555인 사원이 일하는 프로젝트와 일한 시간의 조합이 동일한 사원의 SSN을 검색하라.

이전보다 쉬운 질의 이다. 사원의 Essn이므로, SELECT SSN FROM WORKS_ON 이다. 조건문의 경우, Essn 이 333445555인 사원이 일하는 프로젝트와 일한 시간의 조합이 동일해야 한다. 먼저 WORKS_ON Talbe에서 Essn이 333445555인 Tuple들을 추려야 하므로 SELECT PNO, HOURS FROM WORKS_ON WHERE ESSN = '333445555' 이다. 이때, PNO, HOURS가 추려진 PNO, HOURS의 조합에 속한 것만 검색해야 하므로 IN 조건을 사용해야 한다. 최종적으론 다음과 같다. 

SELECT ESSN FROM WORKS_ON WHERE(PNO,HOURS) IN (SELECT PNO, HOURS FROM WORKS_ON WHERE ESSN = '333445555');

 

 

여기서 한번 더 DISTINCT의 필요성을 확인할 수 있습니다. 현재 동일한 사원이 다수 검색되었음을 확인할 수 있습니다. WORKS_ON Talble에는 각 사원이 특정 프로젝트에서 일하는 시간이 기술되어 있습니다. 따라서, Essn이 333445555인 사원이 여러 프로젝트에서 일했으면 그에 대한 모든 정보가 포함되어 위와 같이 중복되어 나옵니다. 이때, DISTINCT를 활용해 중복된 값에 대해서는 한번만 나오도록 할 수 있습니다. 

SELECT DISTINCT ESSN FROM WORKS_ON WHERE(PNO,HOURS) IN (SELECT PNO, HOURS FROM WORKS_ON WHERE ESSN = '333445555');

 

 

 

=ALL 연산자

ALL 연산자는 일반적으로 다음과 같이 사용된다.

Collumn 비교연산자ALL (값들 혹은 서브쿼리)

 

비교연산자에는 =,>,>=,<,<=사용된다. 값들 혹은 서브쿼리에 대하여 Column에서 각 비교연산자에 맞는 값들만 반환된다. 만약 <,>의 연산자가 사용되면, <의 경우 값들 혹은 서브쿼리의 최소값과 비교되고, >의 경우 최댓값과 비교됨을 주의하자.

 

• 5번 부서에 근무하는 모든 사원보다 급여가 많은 사원을 검색하라.

앞서 언급 한 것처럼, 최댓값과 비교되기에 "모든 사원보다 급여가 많은"에는 >ALL 연산자를 사용하면 된다. 부서명과 급여 모두 Employee Table에 있다.

select Lname, Fname from Employee Where Salary >ALL(Select Salary from Employee where Dno = 5);

 

 

Attribute Name의 모호성

질의는 크게 내부질의와 외부질의로 나뉘는데, 앞서 소개한 질의의 경우 Where뒤에 ()내에 나오는 질의가 내부 질의, () 앞까지가 외부 질의이다. 만약, 외부 질의의 From 절에 있는 Table과 내부 질의의 From 절에 있는 Table의 Attribute Name이 같고, 내부 질의에서 해당 Attribute가 사용된다면 어느 Table인지 모호성이 발생한다. 특히, 내부 질의에서 외부 질의에 명시된 Table의 Attribute를 참조하려면 별명을 사용해야 한다. 

 

• 자신의 부양가족과 이름, 성별이 같은 종업원들의 이름을 검색하시오.

별명을 붙이는 방법은, From Table명 AS 별명이다. 따라서, 별명을 붙인 Table의 Attribute에 접근하는 경우, 별명.AttributeName의 방식으로 접근하면 된다. 

 

외부 질의는 종업원들의 이름을 검색하는 것이다. 이때, 자신의 부양가족과 이름, 성별이 같은 종업원에 대하여 이름을 검색해야 하므로 IN 조건을 활용할 수 있으며, 부양가족은 종업원과 Ssn이 참조되는 관계이다. 

 

select Lname, Fname 
from Employee as E 
where E.Ssn in (select Essn from dependent as D where E.Fname = D.Dependent_name and E.Sex = D.Sex);

 

이름과 성별이 같은 경우 Alice가 있지만, 해당 부양가족이 아니여서 Empty가 반환된다.

 

이처럼 내부 질의의 WHERE절에 있는 조건에서 외부 질의에 선언된 Relation(Table)의 일부 Attribute를 참조하는 경우에 두 질의를 상관된 질의라고 한다.

 

이러한 상관된 질의의 경우 단일 블록 질의로 변환할 수 있다. 

select E.Lname, E.Fname
from employee as E, dependent as D
where E.Ssn = D.Essn and E.Fname = D.Dependent_name and E.Sex = D.Sex;

 

EXISTS 함수

 

• 자신의 부양가족과 이름, 성별이 같은 종업원들의 이름을 검색하시오.

앞서 IN 조건의 경우 Where 뒤에 오는 Ssn(Employee Table)이 내부 질의에서 나온 집합에 포함되는지로 본 질의를 수행하였다. 이를 동일하게 EXISTS로 수행 해 보자. EXISTS의 경우, 상관된 중첩질의에서 내부 질의의 결과가 공집합인가를 검사한다. 

 

select E.Lname, E.Fname
from employee as E
where EXISTS(select * from dependent as D where E.Ssn = D.Essn and E.Sex = D.Sex and E.Fname = D.Dependent_name);

 

NOT EXISTS의 경우, 내부 질의의 결과에 Tuple이 없다면 참을 반환한다.

 

• 부양가족이 없는 종업원들의 이름을 검색하시오.

Employee Table의 Ssn이 Dependent Table의 Essn과 같지 않는 경우를 출력하는 것이다. 

 

• 부양가족이 적어도 한 명 이상 있는 관리자의 이름을 검색하라.

관리자는 Department Table의 Mgr_ssn을 Ssn으로 가지는 Employee Table의 Tuple을 반환하면 되고, 부양가족이 있어야 하므로 Dependent Table의 Essn을 Ssn으로 가지는 Employee Table의 Tuple이 반환된다. 따라서 두 Exists문이 and로 연결 되어 있으므로 두 집합의 공집합이 반환 된다. 

select Fname, Lname from Employee as E
where Exists( select * from dependent as D where E.Ssn = D.Essn)
and Exists( select * from Department as Dep where E.Ssn = Dep.Mgr_ssn);

 

• 5번 부서가 담당하는 "모든 프로젝트"에 근무하는 사원들의 이름을 검색하라

항상 "모든"이라는 단어가 붙으면 반대로 해석하는 것이 문제를 해결하는데 편한 경우가 많다. 내부 질의를 보면 "5번 부서가 담당하는 모든 프로젝트 에서 근무"이다. 반대로 해석하면, "근무하지 않는 5번 부서가 관리하는 프로젝트가 존재하지 않는다"로 해석할 수 있다. 

 

존재하지 않는다 즉, 공집합이 없다이므로 NOT EXISTS()로 내부 질의는 시작된다. 이후 구성해야 하는 공집합은 5번 부서가 관리하는 프로젝트 넘버 집합과, 해당 집합에 포함된 프로젝트에 근무하지 않는 집합으로 구성된다. 그리고, 프로젝트에 근무하지 않는 집합 역시 NOT EXISTS()로 표현할 수 있다. 

 

select Lname, Fname from Employee
where not exists (select * from works_on as B
  					    where (B.Pno
                                            In (select Pnumber from Project where Dnum = 5)
                                            and not exists ( select * from works_on as C
                                            				where C.Essn = ssn and C.Pno = B.Pno)));

 

 

명시적 집합

WHERE 절에 들어가는 값들의 명시적 집합 사용이 가능하다. 

 

• 프로젝트 번호 1,2,3에서 일하는 모든 종업원들의 Ssn을 검색하시오

select distinct essn from works_on where pno in (1,2,3);