Ch 06. 더 복잡한 SQL 검색 질의

학교 수업/2-2 데이터베이스 기초 2021. 9. 28. 20:44

1. 널 값을 포함한 비교와 세 값을 갖는 논리

NULL = 명시되지 않은 값을 나타내는데 사용함

알려지지 않은 값(ex 집전화가 없는 사람)
이용할 수 없거나 보류해 둔 값(ex 집전화가 있지만 공개하지 않은 사람)
적용할 수 없는 애트리뷰트(ex 졸업 대학교에 고졸이 값을 넣을 수 없음)

각각의 널 값은 다른 널 값과 서로 다르다고 생각함

널 값 둘이 비교를하면 값이 같은지 다른지 모르므로 UNKNOWN으로 생각함

그래서 SQL에서의 논리는 TRUE, FALSE, UNKNOWN 세 가지 값을 가진 논리를 사용함

SQL은 질의에서 애트리뷰트 값이 널인지 검사할 수 있음. 하지만 널과 비교하기 위해서는 IS나 IS NOT을 사용함(널 값은 서로 다르다고 생각하므로 =, <, >를 사용할 수가 없음)
그래서 조인 조건을 명시했을 때, 외부 조인이 아니면 널 값을 갖는 튜플들은 결과에 나타나지 않음

2. 중첩 질의와 집합 비교

중첩 질의(nested query) : 데이터베이스 내에 존재하는 값들을 검색한 다음, 이를 비교 조건에서 사용하는 것

중첩 질의는 다른 질의의 WHERE 절 내에서 SELECT - FROM - WHERE 형태를 가짐. 이때 다른 질의를 외부 질의(outer query)라고도 함
v IN V: 하나의 값 v를 값들의 집합 V와 비교하여 V의 원소에 v가 있으면 참을 반환함

2중 for문을 생각하면 이해하기 편함

Q4A. 성이 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');

사원 'John Smith'(사회보장번호 '12456789')가 일하는 어떤 프로젝트의 (프로젝트 번호, 시간) 조합과 동일한 모든 사원의 사회보장번호(Essn)을 WORKS_ON 릴레이션에서 구하기

SELECT		DINSTINCT Essn
FROM		WORKS_ON
WHERE		(Pno, Hours) IN (SELECT	Pno, Hours
				 FROM 	WORKS_ON
                                 WEHRE 	Essn='123456789')

ANY, SOME : IN과 같은 역할을 함
v > ALL V : 하나의 값 v가 값들의 집합 V값보다 큰 경우에 참

서로 다른 릴레이션에 같은 이름의 애트리뷰트가 있는데, 이 릴레이션들을 사용해 중첩 질의를 만들면 릴레이션 이름을 쓰지 않고 애트리뷰트만 사용하면, 가장 안쪽에 있는 중첩 질의의 릴레이션이 가진 애트리뷰트라고 간주함
그래서 특정 애트리뷰트를 사용하려면 릴레이션 이름도 같이 적음

Q16) 부양가족의 성(Fname)과 성별(Sex)이 같은 사원들의 이름(Fname, Lname)을 검색

SELECT 	E.Fname, E.Lname
FROM	EMPLOYEE AS E
WHERE	E.Ssn IN (SELECT	D.Essn
		  FROM		DEPENDENT AS D
                  WHERE 	E.Fname = D.Dependent_name AND E.Sex = D.sex);
                  // E.Sex 와 D.Sex로 구분해주었음

3. 상관(correlated) 중첩 질의, EXISTS / UNIQUE 함수

상관된다: 중첩 질의의 WHERE에 있는 조건에서 외부 질의에 선언된 릴레이션의 일부 애트리뷰트를 참조하는 경우

Q16A

SELECT 	E.Fname, E.Lname
FROM	EMPLOYEE AS E, DEPENDENT AS D
WHERE	E.Fname = D.Dependent_name AND E.Sex = D.sex
	AND E.Fname = D.Dependent_name;

EXISTS : 상관 중첩 질의의 결과가 빈 것인지 아닌지를 검사를 하는데 사용(튜플이 존재하냐) + NOT EXISTS
UNIQUE : 질의 Q의 결과에 중복된 튜플이 없으면 참, 아니면 거짓을 반환

Q16B - EXISTS(Q)가 참인 값만 E.Fname, E.Lname을 가져옴

SELECT 	E.Fname, E.Lname
FROM	EMPLOYEE AS E, DEPENDENT AS D
WHERE	EXISTS	(SELECT	*
		 FROM	DEPENDENT AS D
                 E.Fname = D.Dependent_name AND E.Sex = D.sex
		 AND E.Fname = D.Dependent_name);

Q6 - NOT EXISTS(Q)가 참인 값만 Fname, Lname을 가져옴

DEPENDENT 릴레이션에서 Ssn != Essn인 EMPLOYEE의 Fname과 Lname을 가져옴(부양가족이 없는 사람)

SELECT	Fname, Lname
FROM	EMPLOYEE
WHERE	NOT EXISTS (SELECT  *
		    FROM    DEPENDENT
                    WHERE   Ssn = Essn);

Q7 - EXIST(Q1) AND EXIST(Q2)이 참인 값만 가져옴

부양 가족이 있으면서(Q1) AND 관리자인 직원(Q2)의 Fname과 Lname을 가져옴

SELECT    Fname, Lname
FROM      EMPLOYEE
WHERE     EXISTS (SELECT    *
                  FROM      DEPENDENT
                  WHERE     Ssn = Essn)
          AND
          EXISTS (SELECT    *
                  FROM      DEPARTMENT
                  WHERE     Ssn = Mgr_ssn);

4. SQL에서 명시적 집합과 애트리뷰트의 재명명

WHERE 절에서 명시적 집합을 사용할 수 있음

SELECT    DISTINCT Essn
FROM      WORKS_ON
WHERE     Pno IN (1, 2, 3);

SELECT문에 AS를 사용하여 새 이름으로 재명명이 가능함. 이것은 질의를 다 처리한 후 결과 애트리뷰트에 저 이름대로 나옴

SELECT    E.Lname AS Employee_name, S.Lname AS Supervisor_name
FROM      EMPLOYEE AS E, EMPLOYEE AS S
WHERE     E.Super_ssn = S.Ssn;

5. SQL에서 조인된 테이블과 외부 조인

'Research' 부서에서 근무하는 모든 사원의 이름과 주소를 검색하는 질의는 FROM문에 EMPLOYEE와 DEPARTMENT 두 개의 릴레이션을 적어야 하는데, JOIN을 이용하여 하나로 합칠 수 있음

Q1A

SELECT    Fname, Lname, Address
FROM      (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber) // 하나의 테이블임
WHERE     Dname = 'Research';

NATURAL JOIN
- 두 개의 릴레이션 R과 S에 같은 이름의 애트리뷰트 쌍이 있으면 자연 조인이 될 수 있음
- 이런 각 애트리뷰트 쌍은 결과 릴레이션에서 하나만 나타남
- 값은 같으나 애트리뷰트 이름이 다르면 AS를 이용하여 이름을 같게 만들어줄 수 있음(Q1B)

Q1B

SELECT    Fname, Lname, Address
FROM      (EMPLOYEE NATURAL JOIN
          (DEPARTMENT AS DEPT (Dname, Dno, Mssn, Msdate)))
WHERE     Dname = 'Research';

INNER JOIN(= JOIN)
- 조인의 디폴트 유형
- 다른 릴레이션에 일치하는 튜플이 존재하는 튜플만 결과에 포함함(우리가 그동안 해왔던 것)
OUTER JOIN
- inner join과 다르게 모든 튜플이 조인 결과에 포함되기를 원하는 경우를 사용
- LEFT OUTER JOIN ( += 로도 표기 )
  - 왼쪽 테이블에 있는 모든 튜플들이 결과에 나오고, 오른쪽 테이블에 일치하는 튜플이 없으면 NULL로 표현
- RIGHT OUTER JOIN ( =+ 로도 표기 )
  - 오른쪽 테이블에 있는 모든 튜플들이 결과에 나오고, 왼쪽 테이블에 일치하는 튜플이 없으면 NULL로 표현
- FULL OUTER JOIN ( +=+ 로도 표기 )
  - 양쪽 테이블에 있는 모든 튜플들이 결과에 나오고, 양쪽에 일치하는 튜플이 없으면 각각 NULL로 표현
- LEFT, RIGHT, FULL을 사용하면 OUTER를 생략 가능(LEFT JOIN, RIGHT JOIN, FULL JOIN)
- 조인할 때 양쪽 테이블에서 같은 애트리뷰트 이름이 존재하면 왼쪽에 NATURAL도 붙일 수 있음

Q8B

E.Super_ssn = S.Ssn에 맞추고, 왼쪽 테이블(E)에 있는 모든 튜플들이 결과에 나타낼 때, E.Lname과 S.Lname을 가져옴

이러면 S.Lname이 NULL인 경우도 존재함

SELECT    E.Lname AS Employee_name, S.Lname AS Supervisor_name
FROM      (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S ON E.Super_ssn = S.Ssn);

Q2A - 다중 조인

SELECT    Pnumber, Dnum, Lname, Address, Bdate
FROM      ((PROJECT JOIN DEPARTMENT ON Dnum = Dnumber)
           JOIN EMPLOYEE ON Mgr_ssn = Ssn)
WHERE     Plocation = 'Stafford';

6. SQL에서의 집단 함수(Aggregate Function)

집단 함수 : 여러 튜플의 정보를 요약하여 하나의 튜플로 요악하는데 사용함

그룹화(grouping) : 요악하기 전에 튜플들을 부분 그룹으로 나누기 위해 사용함

COUNT : 질의에서 검색된 튜플이나 값들의 개수
SUM, MAX, MIN, AVG : 합, 최댓값, 최솟값, 평균값

Q19) 모든 사원의 급여의 합, 최고 급여, 최저 급여, 평균 급여를 구하라

SELECT    SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
FROM      EMPLOYEE;

Q20) 'Research' 부서에 근무하는 모든 사원의 급여의 합, 최고 급여, 최소 급여, 평균 급여를 구하라

SELECT    SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
FROM      (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)
WHERE     Dname = 'Research';

Q21) 회사의 총 사원수를 구하라

SELECT    COUNT(*)
FROM      EMPLOYEE

Q23) 데이터베이스에서 서로 다른 급여들의 개수를 구하라

SELECT    COUNT(DISTINCT Salary)
FROM      EMPLOYEE;

COUNT(Salary)는 NULL값을 포함하지 않음
COUNT(*)는 널값을 포함해서 개수를 세서 보여줌

Q5) 둘 이상의 부양 가족이 있는 모든 사원의 이름을 검색하라

SELECT    Lname, Fname
FROM      EMPLOYEE
WHERE     (SELECT    COUNT(*)
           FROM      DEPENDENT
           WHERE     Ssn = Essn) >= 2;

7. 그룹핑 : GROUP BY와 HAVING 절

GROUP BY: 릴레이션을 서로 겹치지 않는 튜플들의 부분집합으로 분할하는 것
- 각 그룹은 그룹화 애트리뷰트라고 부르는 애트리뷰트의 값이 같은 튜플들로 구성되어 있음
- 그런 후 각 그룹마다 독립적으로 집단 함수들을 적용하여 각 그룹별로 요약된 정보를 얻을 수 있음

Q24) 각 부서에 대해 부서 번호, 부서에 속한 사람 수, 각 부서에 속한 사원들의 평균 급여를 구하라

SELECT    Dno, COUNT(*), AVG(Salary)
FROM      EMPLOYEE
GROUP BY  Dno;

GROUP BY를 사용한다면 SELECT에는 GROUP BY에 있는 애트리뷰트 이름과 집단 함수만 사용할 수 있음

Q25) 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 그 프로젝트에 근무하는 사원들의 수를 검색하라

SELECT    Pnumber, Pname, COUNT(*)
FROM      PROJECT, WORKS_ON
WHERE     Pnumber=Pno
GROUP BY  Pnumber, Pname;

HAVING : 그룹화 애트리뷰트에 같은 값을 갖는 튜플들의 그룹에 대한 요약된 정보의 조건을 나타내고, 이런 조건을 만족하는 그룹들만 질의 결과로 검색됨

Q26) 두 명 이상의 사원이 근무하는 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 프로젝트에서 근무하는 사원의 수를 검색하라

SELECT    Pnumber, Pname, COUNT(*)
FROM      PROJECT, WORKS_ON, EMPLOYEE
WHERE     Pnumber=Pno AND Ssn=Essn AND Dno=5
GROUP BY  Pnumber, Pname;

* WHERE절이 먼저 실행되기 때문에 HAVING에 COUNT(*)를 쓸 수 없음. 사용하려면 WHERE절에 사용해야함

Q28) 6명 이상의 사원이 근무하는 각 부서에 대해서 부서 번호와 40,000 달러가 넘는 급여를 받는 사원의 수를 검색하라

SELECT    Dno, COUNT(*)
FROM      EMPLOYEE
WHERE     Salary>40000 AND Dno IN
          (SELECT    Dno
           FROM      EMPLOYEE
           GROUP BY  Dno
           HAVING    COUNT(*) > 5)
GROUP BY  Dno

8 . 다른 SQL 기능들: WITH와 CASE, 순환 질의(RECURSIVE)

WITH : 특정한 질의에만 사용될 테이블을 정의할 때 사용함(나중에 나올 한 질의에만 사용되고 삭제되는 뷰와 비슷)
- 모든 SQL 기반 DBMS들에 사용할 수 있는 것은 아님(SQL: 99에 처음 소개)

Q28') Q28 내용을 WITH을 사용하여 표현

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

CASE : 어떤 값이 조건에 따라 서로 달라질 때 사용하는 기능
- 검색, 삽입, 갱신을 포함하여 SQL의 어떤 부분에도 사용될 수 있음

U6') 부서에 따라 급여를 다르게 올려줌

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

RECURSIVE : 재귀와 같은 것
- ex) 한 상사 e의 부하 직원 e'들 -> 각 e'의 부하직원 e''들 -> ... 을 모두 검색하기

Q29) 사원 e 하위레벨에 있는 모든 직원들 검색하기

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

9. SQL 질의에 대한 논의와 요약

SQL에서 하나의 검색 질의는 최대 6개의 절을 갖지만, 필수적으로 질의에 나타내야하는 절은 SELECT와 FROM절임
- SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
실행순서 : FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
SQL에서 같은 질의를 다양한 방법으로 표현할 수 있는데 장단점이 존재함
- 장점 : 질의를 표현할 때 사용자가 가장 편한 방법을 선택할 수 있음
- 단점
  - 사용자가 혼동을 일으킬 수 있음
  - 같은 질의를 표현하는 어떤 방법이 다른 표현 방법보다 질의를 더 효율적으로 실행시키는 것이 가능함

10. ASSERTION(주장)으로 제약 조건 및 트리거로 동작 명시

CREATE ASSERTION

일반 제약 조건 명시하기, 내용은 WHERE 절과 유사한 조건 형태임

ex) 사원의 급여가 자신이 근무하는 관리자의 급여보다 많으면 안된다라는 제약조건

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

CREATE TRIGGER

특정 사건이 발생하고 특정 조건이 만족될 때 취해야 할 행동의 유형을 명시할 때 사용
- 요소 3가지 : 이벤트, 동작을 실행해야 할 때의 조건, 실행 동작

ex) 1에서 사원의 급여가 자신이 근무하는 관리자의 관리자의 급여보다 많을 때

// Oracle 데이터베이스 시스템 구문 사용
R5: 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_SUPERVISOR(NEW.Supervisor_ssn, NEW.Ssn);

11. 뷰(View)

SQL에서의 뷰는 다른 테이블들에서 유도된 단일 테이블(이것은 기본 테이블이거나 이전에 정의한 뷰일 수도 있음)

튜플들이 물리적으로 저장되어 있는 기본테 테이블과는 달리 가상 테이블로 간주함
갱신 연산(update operation)에 제한을 받지만, 뷰에 대한 질의는 아무런 제한을 받지 않음

CREATE VIEW : 뷰를 정의하는 명령

DROP VIEW 이름 : 뷰를 삭제하는 명령

V1) 사원의 이름과 그 사원이 참여하는 프로젝트의 이름, 근무 시간을 구하는 질의를 뷰로 만들기

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

V2) 부서의 이름, 사원의 수, 총 급여를 뷰로 만들기

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;

뷰의 장점

질의들을 간단하게 작성할 수 있음
보안 기법과 권한 부여 메커니즘에 사용될 수 있음
항상 최신 정보를 가지고 있음(기본 테이블 값이 수정되면 자동으로 변경댐)

12. 뷰의 구현, 뷰 갱신

효율적인 질의를 위해 어떻게 뷰를 효율적으로 구현할 수 있는가 하는 문제는 복잡함

-> 그래서 2가지 주요 접근 방식이 제한

12-1. 질의 수정(query modification)

뷰에 대한 질의를 기본 테이블들에 대한 질의로 수정 또는 변경하는 것
단점 - 시간이 많이 소모되는 복잡한 질의로 정의된 뷰에서 짧은 시간에 동일한 뷰에 많은 뷰 질의가 적용될 때 비효율적임

12-2. 뷰의 실체화(view materialization)

뷰에 처음 질의 요구가 있을 때 임시적인 뷰 테이블을 물리적으로 생성하는 것
- 뷰에 대한 다른 질의들이 이어질 것이라는 가정하에 테이블을 유지하는 것
- 최신 상태로 유지하기 위해서는 기본 테이블이 갱신될 때 뷰 테이블을 자동으로 갱신하는 효율적인 방법이 필요함
점진적 갱신(Incremental Update)
- 정의된 기본 테이블들 중 하나에 데이터베이스 변경이 적용될 때 -> 삽입, 삭제, 갱신할 튜플을 선택
- 이 뷰는 질의가 되고 있는 동안에는 실체화된 테이블로 유지됨
- 일정시간 뷰에 징의가 없으면 실체화된 테이블을 삭제하고, 다음에 그 뷰를 참조하는 질의가 있을 때 다시 재구성함

12-3. 뷰 갱신(view update)

하나의 기본 릴레이션을 사용해서 정의된 뷰가 그 기본 릴레이션의 기본키의 모든 애트리뷰트들을 포함하고 있으면 뷰의 갱신이가능함
일반적으로 다수의 테이블상에서 조인을 사용하여 정의된 뷰는 갱신할 수 없음
그룹화와 집단 함수를 사용하여 정의된 뷰들은 갱신할 수 없음
뷰의 갱신이 가능하려면 뷰 정의 끝에 WITH CHECK OPTION절을 추가함

13. 권한 관리 관점의 뷰

SQL 질의 권한 관리 문장은 GRANT와 REVOKE가 있음

뷰는 무단 사용자들로부터 특정 애트리뷰트나 튜플들을 숨기는데 사용될 수 있음
뷰는 사용자들에게 특정 컬럼들만 보도록 제한할 수 있음(제약)

저작자표시

'학교 수업 > 2-2 데이터베이스 기초' 카테고리의 다른 글

Ch 08. 관계 대수와 관계 해석 (0)	2021.10.12
[Oracle SQL] ON UPDATE CASCADE 대체 방법 (1)	2021.10.12
Ch 05. 기본 SQL (0)	2021.09.14
Ch 04. 관계 데이터 모델과 관계 데이터베이스 제약 조건 (0)	2021.09.12
Ch 02. 데이터베이스 시스템 개념과 아키텍처 (0)	2021.09.12

ABOUT ME