SQL 실습 (2)

---

널 값을 포함한 비교

널 값의 세 가지 의미

• 첫째: Unknown value, 알려지지 않은 값 (존재하지만 알지 못하는)

• 둘째: Unavailable or withheld value, 이용할 수 없거나 보류해둔 값 (존재하지만 의도적으로 보류한)

• 셋째: Not applicable attribute, 적용할 수 없는 애트리뷰트 (이 투플에는 정의되지 않는)

 

ㅇ애트리뷰트의 값이 NULL인지 검사하는 연산자

• IS NULL / IS NOT NULL

 

상사가 없는 모든 종업원들의 이름을 검색하시오.

SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPER_SSN IS NULL;

SUPER_SSN = NULL과 같이 비교 연산자를 이용하여 NULL과 비교하는 것은 불가능하다.

---

Nested Query

중첩 질의는 다른 질의의 WHERE 절 내에 완전한 SELECT 질의가 나타나는 형태이다. 외부 질의와 내부 질의로 구분된다.

비교 연산자 IN은 외부 질의의 한 투플에 대하여, 이 투플이 임의의 투플 집합의 원소가 되는지 비교하는 연산이다. 집합에 있어서 원소 여부를 확인하는 ∈와 동일한 기능을 한다.

 

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

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

 

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

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

 

ㅇ = ALL 연산자 (집합에서 ∀ 에 해당)

• 하나의 값 v가 집합 V내의 모든 값들과 같으면 참이 된다.

• ALL 앞에 = 대신 >, >=, <, <=를 사용할 수도 있다.

 

ㅇ= ANY(= SOME) 연산자 (집합에서 ∃에 해당)

• 하나의 값 v가 집합 V내의 어떤 하나의 값과 같으면 참이 된다.

• ANY(SOME) 앞에 = 대신 >, >=, <, <=를 사용할 수도 있다.

 

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

SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SALARY > ALL ( SELECT SALARY
		    FROM EMPLOYEE
		    WHERE DNO=5) ;

 

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

SELECT E.FNAME, E.LNAME
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);

위와 같은 상관 중첩질의는 비중첩 질의로 변환할 수 있다. 중첩된 SELECT … FROM … WHERE… 블록과 “=“ 및 IN 비교 연산자를 이용해서 작성한 질의는 항상 단일 블록 질의로 변환할 수 있다.

SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE E.SSN=D.ESSN AND E.FNAME=D.DEPENDENT_NAME AND E.SEX=D.SEX;

---

Exist

상관된 중첩질의에서 내부 질의의 결과가 공집합인가를 검사한다. EXISTS(Q): 질의 Q의 결과에 최소한 한 개의 투플이 있다면 참을 반환한다.

 

위에서 본 문제인 "자신의 부양가족과 이름, 성별이 같은 종업원들의 이름을 검색하시오."를 exist를 이용하면 아래와 같이 고칠 수 있다.

SELECT E.FNAME, E.LNAME
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 EXIST 함수는 상관된 중첩질의에서 내부 질의의 결과가 공집합인가를 검사한다. NOT EXISTS(Q): 질의 Q의 결과에 투플이 없다면 참을 반환한다.

 

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

SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
			FROM DEPENDENT
			WHERE SSN=ESSN) ;

 

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

SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
		 FROM DEPENDENT
		 WHERE SSN=ESSN)
	  AND
	  EXISTS ( SELECT *
		 FROM DEPARTMENT
		 WHERE SSN=MGR_SSN) ;

 

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

각 사원에 대하여 그 사원이 근무하지 않는 5번 부서의 관리 프로젝트가 존재하지 않는 경우에 그 사원을 검색하라.

SELECT Fname, Lname
FROM Employee
WHERE NOT EXISTS ( (SELECT Pnumber
			FROM PROJECT
			WHERE Dno=5)
			EXCEPT (SELECT Pno
				FROM WORKS_ON
				WHERE Ssn= ESsn)

---