[오라클 DB] 데이터 조작어(DML) - SELECT문

데이터 조작어(DML) 

 

DB 스키마 내에 데이터를 검색, 삽입, 삭제, 수정하는 역할을 수행하는데 사용되는 언어이다.

 

---

 

 

SELECT문

 

- 관계 데이터베이스에서 정보를 검색하는 SQL문

- 관계 대수의 실렉션과 의미가 완전히 다르다.

- 관계 대수의 실렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것

- 관계 데이터베이스에서 가장 자주 사용된다.

 

 

기본적인 SQL 질의

 

SELECT절과 FROM절만 필수적이고, 나머지는 선택 사항이다.

 

SELECT           [DISTINCT} 애트리뷰트(들)
FROM              릴레이션(들)
[WHERE          조건

[GROUP BY     애트리뷰트(들)]
[HAVING          조건]
[ORDER BY     애트리뷰트(들)  [ASC| DESC] ];

 

 

별칭(alias)

 

서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법

 

 

예시

FROM EMPLOYEE AS E, DEPARTMENT AS D

 

튜플 변수란 한 릴레이션에 연관된 변수로서, 이 또한 별칭이라고도 부른다.

한 릴레이션에 여러 개의 튜플 변수를 연관시킬 수 있음.

AS는 생략이 가능하다.

 

위 예시와 같은 표현

FROM EMPLOYEE E, DEPARTMENT D

 

 

SELECT문 예시 질의 모음

 

예시 질의들과 함께 SELECT 문에 사용되는 여러 키워드나 연산자, 조건절 등을 설명하겠다.

 

 

EMPLOYEE 릴레이션

 

DEPARTMENT 릴레이션

 

질의 1 : 모든 사원의 직급을 검색하시오.

 

SELECT TITLE
FROM EMPLOYEE;

 

결과 릴레이션 1

 

관계 대수에서는 중복을 허용하지 않지만, SQL문에서는 따로 명시하지 않으면 중복을 허용한다.

(위 결과 릴레이션에서 볼 수 있듯이, 과장, 사원 값이 중복됨을 알 수 있다.

 

 

질의 2 : 모든 사원의 상이한 직급을 검색하시오

 

SELECT DISTINCT TITLE
FROM EMPLOYEE;

 

결과 릴레이션 2

 

 

특정 애트리뷰트 값에 중복을 허용하지 않기 위해서는 DISTINCT 키워드를 활용하면 된다.

 

 

 

질의 3 : 2번 부서에 근무하는 사원들에 관한 모든 정보를 검색하시오

 

SELECT *
FROM EMPLOYEE
WHERE DNO = 2;

 

결과 릴레이션 3

 

모든 사원에 관한 모든 정보를 검색해야 하므로 SELECT * FROM EMPLOYEE 까지 쿼리문을 작성한다.

하지만 모든 사원이 아닌 2번 부서에 근무하는 사원들에 대한 모든 정보를 검색해야 하므로 WHERE절에 DNO가 2인 튜플을 검색해야 한다는 조건을 추가해준다.

 

 

질의 4 : 이씨 성을 가진 사원들의 이름, 직급, 소속 부서번호를 검색하시오.

 

SELECT EMPNAME, TITLE, DNO
FROM EMPLOYEE
WHERE EMPNAME LIKE '이%';

 

결과 릴레이션 4

문자열 비교를 통해 원하는 튜플 값만을 검색하고 싶다면 LIKE 키워드를 활용하면 된다.

위 쿼리문은 EMPNAME이 '이'로 시작하는 문자열이 맞다면 해당 튜플을 검색하는 조건을 추가한다는 의미이다.

 

LIKE 키워드에 대한 사용방법과 예시는 다음과 같다.

 

사용 예	예시
LIKE '데이터%'	'데이터'로 시작하는 문자열 ('데이터'로 시작하기만 하면 길이는 상관 없음)
LIKE '%데이터'	'데이터'로 끝나는 문자열 (데이터로 끝나기만 하면 길이는 상관 없음)
LIKE '%데이터%'	'데이터'가 포함된 문자열
LIKE '데이터_ _ _'	'데이터'로 시작하는 6자 길이의 문자열
LIKE '_ _한%'	세 번째 글자가 '한'인 문자열

 

 

질의 5 : 직급이 과장이면서 1번 부서에 속하지 않은 사원들의 이름과 급여를 검색하시오.

 

SELECT EMPNAME, SALARY
FROM EMPLOYEE
WHERE TITLE = '과장' AND DNO <> 1;

 

쿼리문에서 사용되는 비교 연산자는 다음과 같다.

연산자	의미
=	같다
<>	다르다
<	작다
>	크다
<=	작거나 같다.
>=	크거나 같다

 

또한 쿼리문에서 사용되는 논리 연산자는 다음과 같다.

연산자	의미
AND	모든 조건이 만족해야 검색한다.
OR	여러 조건 중 한 가지만 만족해도 검색한다.
NOT	조건을 만족하지 않는 것만 검색한다.

 

위 쿼리문은 EMPNAME과 SALARY 애트리뷰트로 구성된 튜플들을 EMPLOYEE 릴레이션에서 검색할 것인데,

TITLE(직급)이 '과장'이면서 DNO(부서 번호)가 1이 아닌 튜플들만을 검색한다는 의미이다.

 

결과 릴레이션 5

 

 

질의 6 : 1번 부서나 3번 부서에 소속된 사원들에 관한 정보를 검색하시오.

 

SELECT *
FROM EMPLOYEE
WHERE DNO IN (1, 3);

 

결과 릴레이션 6

 

IN 연산자를 사용하여, 특정 애트리뷰트의 값이 비교하려는 리스트가 가지고 있는 값과 매칭되는지를 확인한다.

비교하려는 리스트 안에 특정 애트리뷰트 값이 존재하면 해당 튜플을 검색한다.

 

 

 

질의 7 : 아래의 주문 릴레이션에서 apple 고객이 주문했거나 15개 이상 주문된 제품의 주문 제품, 수량, 주문 일자, 주문 고객을 검색하시오.

 

주문 릴레이션

 

SELECT 주문제품, 수량, 주문일자, 주문고객
FROM 주문
WHERE 주문고객 = 'apple' OR 수량 >= 15;

 

주문제품, 수량, 주문일자, 주문고객 애트리뷰트를 주문 릴레이션에서 검색하는데, 'apple' 고객이 주문했거나(OR) 15개 이상 주문된 제품에 대해 검색하는 것이므로 주문고객이 'apple' 또는 수량이 15 이상인 조건 둘 중 하나만 만족해도 해당 튜플을 검색하도록 한다.

 

결과 릴레이션 7

 

 

질의 8 : 아래의 제품 릴레이션에서 단가가 2,000원 이상이면서 3,000원 이하인 제품의 제품명, 단가, 제조업체를 검색하시오.

 

제품 릴레이션

 

SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 >= 2000 AND 단가 <= 3000;

 

단가 애트리뷰트의 값이 2000이상 3000이하라면 해당 튜플을 검색하도록 한다.

 

결과 릴레이션 8

 

 

 

위 쿼리문의 WHERE 절에 다음과 같이 작성해도 같은 결과를 도출한다.

 

SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 BETWEEN 2000 AND 3000;

 

BETWEEN 키워드를 사용하는 방법도 존재한다. (BETWEEN X AND Y = X이상 Y이하의 값인지 확인하는 키워드)

 

 

 

 

질의 9 : 직급이 과장인 사원들에 대하여 이름과 현재의 급여, 급여가 10% 인상됐을 때의 값을 검색하시오.

 

SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY
FROM EMPLOYEE
WHERE TITLE = '과장';

 

결과 릴레이션 9

 

SALARY 애트리뷰트의 값에 1.1을 곱한 값을 담아낼 새로운 애트리뷰트 NEWSALARY를 선언하여 결과 릴레이션에 추가하여 도출해낸다.

 

위의 경우에 추가로, SELECT 절에서 산술 연산자(+, -, *, /)를 사용할 수 있으며 산술 연산자로 연산을 진행하여도 기존의 데이터베이스 값이 변경되는 것은 아니다.

 

 

 

질의 10 : 아래의 고객 릴레이션에서 나이가 이미 입력된 고객의 고객이름을 검색하시오.

 

고객 릴레이션

 

SELECT 고객이름
FROM 고객
WHERE 나이 IS NOT NULL;

 

나이 IS NOT NULL을 WHERE 절에 추가함으로써, 나이 애트리뷰트 값이 없는(NULL) 오형준 고객을 제외한 고객이름들을 결과 릴레이션으로 도출하게 된다.

 

결과 릴레이션 10

 

 

 

 

질의 11 : 2번 부서에서 근무하는 사원들의 급여, 직급, 이름을 검색하여 급여의 오름차순으로 정렬하시오.

 

SELECT SALARY, TITLE, EMPNAME
FROM EMPLOYEE
WHERE DNO = 2
ORDER BY SALARY;

 

 

결과 릴레이션 11

 

 

사용자가 SELECT 문에서 질의 결과의 순서를 명시하지 않으면 릴레이션에 튜플들이 삽입된 순서대로 사용자에게 제시된다.

하나 이상의 특정 애트리뷰트를 기준으로 검색 결과를 정렬하고 싶다면 ORDER BY 절에 조건을 기입하면 된다.

 

ORDER BY 절에서 하나 이상의 애트리뷰트를 사용하여 검색 결과를 정렬할 수 있으며, SELECT 문에서 가장 마지막에 사용되는 절이다.

 

디폴트 정렬 순서는 오름차순(ASC)이며, DESC를 지정하여 정렬 순서를 내림차순으로 지정할 수 있다.

예를 들어 다음과 같이 ORDER BY 절을 작성했다고 가정해보자.

 

ORDER BY DNO, SALARY DESC;

 

위 쿼리문은 DNO 애트리뷰트 값을 기준으로 오름차순으로 정렬하고, 만약 DNO 값은 튜플이 존재한다면 해당 튜플들은 SALARY 값을 기준으로 내림차순 정렬하겠다는 의미이다.

 

 

널 값은 오름차순에서는 가장 마지막에 나타나고, 내림차순에서는 가장 앞에 나타난다.

 

SELECT 절에 명시한 애트리뷰트만 ORDER BY절에 기준으로 제시할 수 있다.

예를 들어, SELECT 절에 사원번호, 사원이름, 직책 애트리뷰트만을 결과 릴레이션에 포함시키겠다고 제시하였는데 ORDER BY 절에 연봉 애트리뷰트를 기준으로 오름차순으로 정렬하겠다 라고 제시한다면 결과 릴레이션에 없는 애트리뷰트를 기준으로 오름차순 정렬을 시도한 것이기에 당연 불가능하다.

 

 

 

질의 12 : 수량이 10개 이상인 주문의 주문고객, 주문제품, 수량, 주문일자를 검색하고, 주문제품은 오름차순으로 수량은 내림차순으로 정렬하시오.

 

SELECT 주문고객, 주문제품, 수량, 주문일자
FROM 주문
WHERE 수량 >= 10
ORDER BY 주문제품, 수량 DESC;

 

결과 릴레이션 12

 

 

 

 

질의 13 : 모든 사원들의 평균 급여와 최대 급여를 검색하시오.

 

SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE;

 

결과 릴레이션 13

 

집단 함수란, 데이터베이스에서 검색된 여러 튜플들의 집단에 적용되는 함수이다.

한 릴레이션의 한 개의 애트리뷰트에 적용되어 단일 값을 반환한다.

SELECT 절과 HAVING 절에만 나타날 수 있다.

 

함수	의미	사용 가능한 속성의 타입
COUNT	속성 값의 개수	모든 데이터
MAX	속성 값의 최댓값
MIN	속성 값의 최솟값
SUM	속성 값의 합계	숫자 데이터
AVG	속성 값의 평균

 

COUNT(*)는 널 값을 포함한 결과 릴레이션의 모든 행들의 총 개수를 구하는 반면에, COUNT(애트리뷰트명)은 해당 애트리뷰트에서 널 값이 아닌 값들의 개수를 구한다.

 

집단 함수 사용 시, 주의할 점으로는 DISTINCT 키워드가 집단 함수 앞에 사용되면 집단 함수가 적용되기 전에 먼저 중복을 제거하는 점이 있다.

 

 

 

질의 14 :  한빛제과에서 제조한 제품의 재고량 합계를 검색하시오

 

SELECT SUM(재고량) AS '재고량 합계'
FROM 제품
WHERE 제조업체 = '한빛제과';

 

결과 릴레이션 14

 

 

 

질의 15 :  모든 사원들에 대해서 사원들이 속한 부서번호별로 그룹화하고, 각 부서마다 부서번호, 평균급여, 최대급여를 검색하시오.

 

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO;

 

결과 릴레이션 15

 

그룹화란, 특정 애트리뷰트의 값이 같은 튜플끼리 모아 그룹으로 묶는다는 의미이며 GROUP BY 절을 사용한다.

그룹화를 위해 기준이 되는 특정 애트리뷰트를 그룹화 애트리뷰트(grouping attribute)라고 부른다.

각 그룹에 대하여 결과 릴레이션에 하나의 튜플이 생성된다. ( 묶은 그룹이 총 3개라면 튜플은 3개라는 뜻 )

 

SELECT 절에는 각 그룹마다 하나의 값을 갖는 애트리뷰트, 집단 함수, 그룹화에 사용된 애트리뷰트들만 나타날 수 있다.

 

GROUP BY 절에 사용된 애트리뷰트는 집단 함수에 사용되지 않은 애트리뷰트여야 한다.

 

 

 

 

질의 16 :  모든 사원들에 대해서 사원들이 속한 부서번호별로 그룹화하고, 평균 급여가 2,500,000원 이상인 부서에 대하여 부서번호, 평균 급여, 최대 급여를 검색하시오.

 

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG(SALARY) >= 2500000;

 

 

일반적인 질의 조건은 WHERE 절에 명시하여 특정 튜플들을 걸러낸다면,

그룹에 대한 질의 조건은 HAVING 절에 명시하여 특정 그룹들을 걸러낸다.

 

결과 릴레이션 16

결과 릴레이션 16 에서 볼 수 있듯이, HAVING 절은 해당 절에 명시한 조건을 기준으로 특정 그룹을 걸러내는 역할을 수행한다.

HAVING 절에 나타나는 애트리뷰트는
반드시 GROUP BY 절에 나타나거나
또는 집단 함수에 포함되어야 한다.

 

 

 

질의 17 :  적립금 평균이 1,000원 이상인 등급에 대해 등급별 고객 수와 적립금 평균을 검색하시오.

 

SELECT 등급, COUNT(*) AS 고객수, AVG(적립금) AS 평균적립금
FROM 고객
GROUP BY 등급
HAVING AVG(적립금) >= 1000;

 

 

결과 릴레이션 17

 

등급 별로 평균 적립금을 알아내기 위해 먼저 등급을 그룹화 애트리뷰트로 선정하여 그룹화를 진행하였고, 그룹 별로 평균 적립금을 알아내기 위해 적립금 애트리뷰트에 집단 함수를 사용하였다.

이후, 평균 적립금이 1000원 미만인 그룹을 걸러내기 위해 HAVING 절에 조건을 추가하여 원하는 결과 릴레이션을 도출하였다.

 

 

 

질의 18 :  제품을 3개 이상 제조한 제조업체별로 제품의 개수와, 제품 중 가장 비싼 단가를 검색하시오.

 

SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가
FROM 제품
GROUP BY 제조업체
HAVING COUNT(*) >= 3;

 

결과 릴레이션 18

 

제조업체 애트리뷰트를 그룹화 애트리뷰트로 선정하여 그룹화를 진행하였으며, 각 그룹별로 제품수를 책정한 이후 HAVING 절에 조건을 추가하여 제품 수가 3개 미만인 그룹을 걸러내여 결과 릴레이션을 도출하였다.

 

 

 

 

질의 19 : 김창섭이 속한 부서이거나 개발 부서의 부서번호를 검색하시오.

 

(SELECT DNO
FROM EMPLOYEE
WHERE EMPNAME = '김창섭')
UNION
(SELECT DEPTNO
FROM DEPARTMENT
WHERE DEPTNAME = '개발');

 

첫 번째 실렉트 문으로 도출된 릴레이션에는 DNO가 2인 튜플만이 존재하고, 두 번째 실렉트 문으로 도출된 릴레이션에는 DNO가 3인 튜플만이 존재한다.

두 릴레이션을 합집합 연산(UNION)하면 다음과 같은 결과 릴레이션이 도출된다.

 

결과 릴레이션 19

 

집합 연산을 적용하려면 두 릴레이션이 합집합 호환성을 가져야 한다.

 

오라클에서 집합 연산을 위해 사용되는 키워드는 다음과 같다.

 

UNION : 각 쿼리의 결과 합을 반환하는 합집합 (중복제거)

UNION ALL : 각 쿼리의 모든 결과를 포함한 합집합 (중복제거 안함)

INTERSECT : 교집합

MINUS : 차집합

 

 

보통 집합 연산보다 조인(JOIN)을 더 많이 쓴다.

 

 

 

질의 20 : 모든 사원의 이름과 이 사원이 속한 부서의 이름을 검색하시오.

 

SELECT EMPNAME, DEPTNAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO = D.DEPTNO;

 

결과 릴레이션 20

 

조인이란, 두 개 이상의 릴레이션으로부터 공통 속성을 이용해 연관된 튜플들을 결합하는 것을 말한다.

 

일반적인 형식은 다음과 같다.

 

SELECT 문과 같이 FROM 절에 두 개 이상의 릴레이션들이 열거되고,

두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE 절에 포함된다.

조인 조건은 두 릴레이션 사이에 속하는 애트리뷰트 값들을 비교 연산자로 연결한다.

 

SELECT ...
FROM R, S
WHERE R.A <비교 연산자> S.B;

 

WHERE 절의 R.A <비교 연산자> S.B 부분이 조인 조건을 의미한다.

만약 조인 조건이 ' = ' 라면 동등 조인이다.

조인 조건을 생략했을 때에는 카티션 곱이 생성된다.

 

 

조인 질의가 수행되는 과정

 

먼저 조인 조건을 만족하는 튜플들을 찾고,

이 튜플들로부터 SELECT 절에 명시된 애트리뷰트들만 프로젝트하고,

필요하다면 중복을 배제하는 순서로 진행된다.

 

조인 조건이 명확해지도록 애트리뷰트 이름 앞에 릴레이션 이름이나 튜플 변수를 사용하는 것이 바람직하다.

두 릴레이션의 조인 애트리뷰트 이름이 동일하다면 반드시 애트리뷰트 이름 앞에 릴레이션 이름이나 튜플 변수를 사용해야 한다.

 

위 쿼리문의 WHERE 절에서의 조인 조건 E.DNO = D.DEPTNO 은 두 릴레이션 E, D에 대해 동등 조인을 하겠다는 의미이며 (E 릴레이션의 DNO 애트리뷰트와 D 릴레이션의 DEPTNO가 같은 도메인과 의미를 가진다는 의미.) 두 릴레이션 동등 조인 결과는 다음과 같다.

 

EMPLOYEE 릴레이션과 DEPARTMENT 릴레이션의 동등 조인 결과

 

 

 

 

질의 21 : 모든 사원에 대해서 사원의 이름과 직속 상사의 이름을 검색하시오.

 

SELECT E.EMPNAME, M.EMPNAME
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER = M.EMPNO;

 

결과 릴레이션 21

 

자체 조인이란, 한 릴레이션에 속하는 튜플을 동일한 릴레이션에 속하는 튜플들과 조인하는 것으로 실제로는 한 릴레이션이 접근되지만 FROM 절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서 그 릴레이션에 대한 별칭을 두 개 지정해야 한다.

 

 

두 릴레이션은 같은 EMPLOYEE 릴레이션(MANAGER 애트리뷰트를 임의로 추가한 EMPLOYEE 릴레이션)으로, MANAGER 애트리뷰트는 특정 사원의 직속 상사의 EMPNO라는 의미이다.

 

 

 

질의 22 : 모든 사원에 대해서 소속 부서이름, 사원의 이름, 직급, 급여를 검색하시오. 부서이름에 대해서는 오름차순, SALARY에 대해서는 내림차순으로 정렬하시오.

 

SELECT DEPTNAME, EMPNAME, TITLE, SALARY
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO = D.DEPTNO
ORDER BY DEPTNAME, SALARY DESC;

 

조인과 ORDER BY 절을 함께 사용한 예시이다.

 

E 릴레이션의 DNO와 D 릴레이션의 DEPTNO가 같다는 조건을 기입하여 두 릴레이션의 동등 조인을 수행하고,

조인을 수행한 결과 릴레이션의 DEPTNAME, EMPNAME, TITLE, SALARY를 검색하였으며

검색 결과 릴레이션에서 DEPTNAME 애트리뷰트를 기준으로 오름차순 정렬, 만일 DEPTNAME 애트리뷰트 값이 같다면 SALARY를 기준으로 내림차순 정렬을 진행하여 최종 결과 릴레이션을 도출하였다.

 

 

결과 릴레이션 22

 

 

 

질의 23 : 나이가 30세 이상인 고객이 주문한 제품의 번호와 주문일자를 검색하시오.

 

SELECT 주문.주문제품, 주문.주문일자
FROM 고객 INNER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객
WHERE 고객.나이 >= 30;

 

결과 릴레이션 23

 

INNER JOIN(내부 조인) 키워드와 ON 키워드를 이용해 두 릴레이션을 조인하는 방법도 제공한다.

위에서 설명한 JOIN 구문 방식(아래)과 같은 의미이다.

SELECT O.주문제품, O.주문일자
FROM 고객 C, 주문 O
WHERE C.나이 >= 30 AND C.고객아이디 = O.주문고객;

 

 

 

질의 24 : 주문하지 않은 고객도 포함해서 고객이름, 주문제품, 주문일자를 검색하시오.

 

SELECT 고객.고객이름, 주문.주문제품, 주문.주문일자
FROM 고객 LEFT OUTER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객;

 

(오형준 고객의 주문일자 애트리뷰트 값 오탈자 존재 : MULL -> NULL)

결과 릴레이션 24

 

외부 조인(Outer join)이란 조인 조건을 만족하지 않는 튜플에 대해서도 검색을 수행하는 것으로,

OUTER JOIN 키워드와 ON 키워드를 이용한다.

 

종류로는 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있다.

 

 

 

고객 릴레이션과 주문 릴레이션을 조인하는데, 주문 내역에 없는 고객의 정보도 포함해야 하므로 내부 조인이 아닌 외부 조인을 사용하였다.

고객 릴레이션의 모든 튜플들이 우선적으로 존재해야 하므로 고객 LEFT OUTER JOIN 주문 ON ~ 의 왼쪽 외부 조인 키워드를 사용하였다.

 

 

 

질의 25 : 부서원이 없는 부서명을 포함해서 부서명과 부서사원명을 검색하시오.

 

SELECT DEPARTMENT.DEPTNAME, EMPLOYEE.EMPNAME
FROM EMPLOYEE RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.DNO = DEPARTMENT.DEPTNO;

 

결과 릴레이션 25

 

부서원이 없는 부서명도 포함해야 하므로, DEPARTMENT 릴레이션의 모든 튜플들이 우선적으로 존재해야 한다. EMPLOYEE RIGHT OUTER JOIN DEPARTMENT ON ~ 의 오른쪽 외부 조인 키워드를 사용하였다.

 

 

 

질의 26 : 부서원이 없는 부서명을 포함해서 부서명과 부서사원명을 검색하시오.

 

SELECT * 
FROM EMPLOYEE CROSS JOIN DEPARTMENT

 

 

교차 조인(Cross join)이란, Cartesian Product(카티션 곱) 의미이며, 조인되는 두 테이블에서 곱집합을 반환한다.

(EMPLOYEE의 카디널리티와 DEPARTMENT의 카디널리티를 곱한 값인 28개의 레코드를 반환한다.)

 

결과 릴레이션 26

 

 

 

---

 

중첩 질의 (Nested query)

 

부질의(subquery) 라고도 한다.

외부 질의의 WHERE 절에 다시 SELECT ... FROM ... WHERE ... 형태로 포함된 SELECT 문을 의미하며 하나의 SQL문 안에 다른 SQL문이 중첩된 nested 질의이다.

 

 

중첩 질의의 결과 릴레이션으로는 3가지 유형이 존재한다.

 

한 개의 스칼라 값(단일 값)

한 개의 애트리뷰트로 이루어진 릴레이션

여러 애트리뷰트들로 이루어진 릴레이션

 

 

 

한 개의 스칼라 값이 반환되는 경우

 

질의 27 : 적립금이 가장 많은 고객의 고객이름과 적립금을 검색하시오.

 

SELECT 고객이름, 적립금
FROM 고객
WHERE 적립금 = (
          SELECT MAX(적립금)
          FROM 고객);

 

 

결과 릴레이션 27

 

내부 질의문의 결과 릴레이션은 MAX(적립금) 애트리뷰트 하나와 최대 적립금 금액 튜플 하나만이 도출된다.

이와 같이 스칼라 값 하나만 내부 질의를 통해 도출되는 경우, 보통 외부 질의에서 특정 애트리뷰트와 비교하는 비교 연산자의 피연산자 기준값이 된다. 

위 쿼리문에서 볼 수 있듯이, 내부 질의를 통해 도출된 최대 적립금 금액 값이 고객 릴레이션의 모든 튜플 중, 적립금 금액이 최대 적립금 금액과 같은지 ( = ) 확인하여 조건에 충족하는 튜플의 고객이름, 적립금만을 결과 릴레이션으로 도출한 모습이다.

 

 

 

질의 28 : 박영권과 같은 직급을 갖는 모든 사원들의 이름과 직급을 검색하시오.

 

SELECT EMPNAME, TITLE
FROM EMPLOYEE
WHERE TITLE = (
          SELECT TITLE
          FROM EMPLOYEE
          WHERE EMPNAME = '박영권');

 

내부 질의를 통해 박영권의 직급을 알아낸 이후, 외부 질의를 통해 해당 직급과 같은 모든 사원의 이름과 직급을 검색하여 릴레이션을 도출한다.

 

결과 릴레이션 28

 

 

 

한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우

 

중첩 질의 결과로 한 개의 애트리뷰트로 이루어진 다수의 튜플들이 반환될 수 있다.

외부 질의 WHERE 절에서 IN, ANY(SOME), ALL, EXISTS.와 같은 연산자를 사용해야 한다.

 

연산자	설명
IN	한 애트리뷰트가 값들의 집합에 속하는가를 테스트할 때 사용됨
ANY 또는 SOME	한 애트리뷰트가 값들의 집합에 속하는 하나 이상의 값들과 어떤 관계를 갖는가를 테스트하는 경우에 사용됨
ALL	한 애트리뷰트가 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는가를 테스트하는 경우에 사용됨
EXISTS	서브 쿼리의 결과가 하나라도 존재하면 참이 되는 연산자

 

IN 연산자 예시

 

ANY 연산자 예시

 

ALL 연산자 예시

 

 

질의 29 : banana 고객이 주문한 제품의 제품명과 제조업체를 검색하시오.

 

SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN (
         SELECT 주문제품
         FROM 주문
         WHERE 주문고객 = 'banana');

 

결과 릴레이션 29

 

내부 질의를 통해 banana 고객이 주문한 주문제품들을 뽑아내고, 해당 주문제품의 제품번호와 제품 릴레이션 각 튜플들 중 제품번호와 일치하는 제품 릴레이션의 튜플들의 제품명, 제조업체 데이터를 뽑아 결과 릴레이션으로 도출하면 된다.

 

 

질의 30 : 영업부나 개발부에 근무하는 사원들의 이름을 검색하시오.

 

SELECT EMPNAME
FROM EMPLOYEE
WHERE DNO IN (
         SELECT DEPTNO
         FROM DEPARTMENT
         WHERE DEPTNAME = '영업' OR DEPTNAME = '개발');

 

결과 릴레이션 30

내부 질의를 통해 다음과 같은 릴레이션을 뽑아낼 수 있다.

 

DNO

1

3

 

EMPLOYEE의 각 튜플들의 DNO가 1 또는 3이라면 해당 튜플의 EMPNAME을 뽑아내어 결과 릴레이션을 도출하면 된다.

 

위의 중첩 질의 대신, 다음과 같은 조인 문장도 사용할 수 있다.

 

SELECT EMPNAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO = D.DEPTNO AND (D.DEPTNAME = '영업' OR D.DEPTNAME = '개발');

 

 

질의 31 : banana 고객이 주문하지 않은 제품의 제품명과 제조업체를 검색하시오.

 

SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 NOT IN (
         SELECT 주문제품
         FROM 주문
         WHERE 주문고객 = 'banana');

 

결과 릴레이션 31

 

 

banana 고객이 주문한 주문제품들 리스트를 뽑아낸 후, 해당 리스트에 존재하지 않는 주문제품들만 튜플들을 걸러내야 하기에 NOT IN 연산자를 사용하였다.

 

 

 

 

질의 32 : 대한식품이 제조한 모든 제품의 단가보다 비싼 제품의 제품명, 단가, 제조업체를 검색하시오.

 

SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 > ALL (
         SELECT 단가
         FROM 제품
         WHERE 제조업체 = '대한식품');

 

결과 릴레이션 32

 

제품 릴레이션에서 제조업체가 대한식품인 모든 제품들의 단가를 추출한다.

해당 모든 단가보다 높은 단가를 지닌 제품들의 제품명, 단가, 제조업체를 결과 릴레이션으로 도출하면 된다.

 

 

 

 

여러 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우

 

중첩 질의의 결과로 여러 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우에는 EXISTS 연산자를 사용하여 중첩 질의의 결과가 빈 릴레이션인지 여부를 검사한다.

EXITST 연산자를 사용하여 중첩 질의의 결과가 빈 릴레이션이 아니면 참이 되고, 그렇지 않으면 거짓이 된다.

 

 

질의 33 : 영업부나 개발부에 근무하는 사원들의 이름을 검색하시오.

 

SELECT EMPNAME
FROM EMPLOYEE E
WHERE EXISTS (
         SELECT *
         FROM DEPARTMENT D
         WHERE E.DNO = D.DEPTNO AND (DEPTNAME = '영업' OR DEPTNAME = '개발') );

 

결과 릴레이션 33

 

위 쿼리문을 설명하기 전, 상관 중첩 질의(correlated nested query)에 대해 알아둘 필요가 있다.

 

 

상관 중첩 질의

 

중첩 질의의 WHERE 절에 있는 프레디키트에서 외부 질의에 선언된 릴레이션의 일부 애트리뷰트를 참조하는 질의이다.

(프레디키트(predicate) : 튜플에서 셀렉션 조건을 만족하는 부분집합을 말하는 단어)

 

중첩 질의의 수행 결과가 단일 값이든, 하나 이상의 애트리뷰트로 이루어진 릴레이션이든 외부 질의로 한 번만 결과를 반환하면 상관 중첩 질의가 아니다.

 

상관 중첩 질의에서는 외부 질의를 만족하는 각 튜플이 구해진 후에 중첩 질의가 수행되므로 상관 중첩 질의는 외부 질의를 만족하는 튜플 수만큼 여러 번 수행될 수 있다.

 

다시 위 쿼리문으로 돌아가보면, 내부 질의의 WHERE 절에 E.DNO = D.DEPTNO이 존재한다.

E.DNO의 E는 외부 질의에서 선언된 별칭을 내부 질의에서 사용한 것으로 설명할 수 있으며,

이는 외부 질의의 EMPLOYEE 릴레이션에서 각 튜플들의 DNO 애트리뷰트 값과 같은 DEPARTMENT 릴레이션의 모든 튜플 중 DEPTNO 애트리뷰트 값이 존재하는지 확인하는 조건식이기 때문에 내부 질의는 외부 질의의 릴레이션 튜플 수 만큼 수행된다.

추가로 내부 질의의 WHERE 절에서 DEPTNAME 애트리뷰트의 값이 '영업' 혹은 '개발'인 튜플만 유효하게 인식하도록 조건을 적었기 때문에 영업이나 개발이 아닌 부서명을 지닌 튜플들에 대해서는 빈 릴레이션이 도출될 수 밖에 없다. 빈 릴레이션이 도출되면 외부 질의의 WHERE 절의 EXISTS 연산자에 의해 FALSE를 반환하여 해당 튜플은 최종 결과 릴레이션에서 포함하지 않게 된다.

 

 

 

질의 34 : 자신이 속한 부서의 사원들과 평균 급여보다 많은 급여를 받는 사원들에 대해 이름, 부서번호, 급여를 검색하시오.

 

질의에 대한 쿼리문과 결과 릴레이션 34