1. More Complex SQL Search Query
1) NULL의 3가지 값
SQL 에서는 NULL 값이 3가지 상태를 의미한다.
- 알려지지 않은 값
어떤 사람의 나이는 알려지지 않을 수 있다. - 이용할 수 없거나 보류해둔 값
어떤 사람은 자신의 번호를 가지고 있지만 알려주고싶지 않은 경우가 있음 - 적용할 수 없는 애트리뷰트
고졸인데 대학을 쓰는 애트리뷰티에서는 NULL 값을 써야함
SQL에서는 어떤 애트리뷰트가 NULL 값을 가질 때 위 3가지 경우 중 어떤 경우인지 모르는 경우도 있다.
따라서 SQL에서는 NULL 값에 의미를 두지 않는다.
SQL에서 각 릴레이션에 있는 투플들의 NULL 값은 서로 다르다.
일반적으로 SELECT-FROM-WHERE 문에서 WHERE 문에는 해당 논리식을 참으로 하는 투플들의 조합만 따진다.
만약 NULL 값에 대해서도 투플의 조합을 따지고 싶은 경우에는 2가지 연산자가 제공된다.
예를 들어
WHERE Pnumber IS NULL;
WHERE Pnumber IS NOT NULL;
이렇게 =연산과 <> 연산에 대해 IS NULL연산과 IS NOT 연산을 제공한다. 0
2) 3가지 논리 상태
SQL에서는 true, false, Unknown 3가지 논리 상태가 존재한다.
만약 NULL 값과 비교 연산을 시도하는 경우 UNKNOWN 이라는 논리 타입을 반환한다.
아래 표에서 논리 연산의 결과를 비교한 표를 간단히 작성하였다.
| True & Unknown | Unknown |
| Ture | Unknown | True |
| False | Unknown | Unknown |
| ~Unknown | Unknown |
3) 중첩 질의와 집합 비교 연산자(IN, ALL)
중첩 질의란 select-from-where 절에서 where 절 안에 완전한 select-from-where 형태의 절이 중첩하여 있는 형태를 뜻한다.
겉에 있는 select-from-where문을 외부 질의라고 한다.
- SELECT DISTINCT Essn
FROM WORKS_ON
WHERE (Pno, Hours) IN ( SELECT Pno, Hours
FROM WORKDS_ON
WHERE Essn = '123123');
위 중첩 질의를 분석해보면 WHERE(외부 질의)르 만족 시키는 WORKS_ON(외부 질의)에 있는 Essn을 구하는 것이다.
DISTINECT는 Essn의 값을 결국 구하는데 구한 Essn중에 구별해주는 값만 구하는 것이다.
WHERE (외부 질의)에서 WORKS_ON 의 어트리뷰튜인 Pno, Hours의 값이 참인 값에 대한 외부 Essn을 구하는 것인데
이 Pno, Hours(외부 질의) 는 중첩 질의에서 구해진 Pno, Hours(내부 질의)의 값과 비교를 한다.
IN 연산자는 내부 질의에서 구해진 Pno, Hours의 값들이 외부 질의 WORDKS_ON 에 속한 Pno, Hours 값 쌍의 집합에 속하는 원소인지를 확인하는 것이다.
만약 내부 질의에서 구해진 Pno, Hours가 집합이 아닌 하나의 값만 가진다면 굳이 집합 연산 비교인 IN을 사용하지 않고 고 비교 연산자 "="연산을 사용하면 됨
● SELECT Essn
FROM WORKS_ON
WHERE Hours IN (10.0, 20.0);
IN 연산을 좀 더 쉽게 설명하면 만약 Hours = 10.0 이렇게 하나의 값만 비교할 거면 = 연산자 사용하면 되는데 집합이면 IN을 사용하는 것임, Hours = 10.0 또는 Hours = 20.0 이거랑 같은 의미임
※ IN과 똑같은 연산자로 ANY 또는 SOME이 있다. (걍 셋다 똑같음)
ANY가 있는 이유는 >, >=, <,<=,<> 연산자와 함께 사용하기 위해 있는 것임
또한 ALL도 위 연산자와 함께 사용 가능함
- SELECT Fname
FROM EMPLOYEE
WHERE Salary > ALL ( SELECT Salary
FROM EMPLOYEE
WHERE Dno = 100);
위 예시는 중첩 질의와 동시에 > ALL이라는 새로운 연산이 등장한다.
위 연산은 EMPLOYEE 릴레이션 내에 어트리뷰트 중 Dno의 값이 100인 모든 직원들에 대해
ALL Salary를 먼저 구한다. 구해진 Salary 모든 값보다 더 큰 Salary를 가진 직원 릴레이션의 ALL Lname을 반환한다.
4) 상관 중첩 질의
중첩 질의에서 WHERE 절에 있는 WHERE 절에서 외부 질의에 있는 FROM절의 릴레이션을 참조한다면 이 중첩 질의문은 상관 중첩 질의라고 한다.
상과 중첩질의에서는 아래 5)에서 설명하는 EXISTS 함수와 UNIQUE 함수와 함께 사용된다.
5) EXISTS(NOT EXISTS) 함수, UNIQUE 함수
- SELECT Fname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM DEPEJDENT
WHERE Ssn = Essn);
EXISTS 함수는 TRUE/FALSE를 반환하는 불리언 타입으로 위 예시를 보면 중첩 질의에서 Ssn = Essn을 만족하는 모든 DEPENDENT의 애트리 뷰트 값을 반환하는데 만약 값이 하나라도 없는 경우 NOT EXISTS는 TRUE를 반환한다. 따라서 중첩 질의가 만약 참이라면 EMPLOYEE 내부의 모든 Fname을 출력하도록 한다.
UNIQUE 를 사용하면 중첩 질의의 결과가 종복된 투플이 없다면 TRUE를 반환하고 있다면 FALSE이다.
이것은 중첩 질의의 결과가 다중 집합인지 아닌지를 검사할 때 사용된다.
6-1) FROM 절에 내부 조인 조건
이전까지 조인 조건을 WHERE 절에서만 명시하였다. 가독성이 떨어지며 FROM 절에서 조인 조건을 명시할 수 있도록 SQL은 지원한다.
- SELECT name, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Don = Dnumber)
WHERE Dname = "Research';
위 예시와 같이 JOIN이라는 키워드 양 옆에 실제 join할 두 릴레이션을 쓰고 ON 다음에 두 릴레이션의 조인 조건을 적어준다.
※ 실제로 그냥 JOIN 키워드는 INNER JOIN에서 INNER를 생략한 것이다.
- SELECT name, Address
FROM (EMPLOYEE NATUARL JOIN (DEPARTMENT AS DF (Dname, Dno, m1, m2)))
WHERE Dname = "Research';
위 예시를 보면 우선 (DEPARTMENT AS DF (Dname, Dno, m1, m2) 이 문장 부터 분석하면 DEPARTMENT라는 릴레이션을 재명명하는데 'DF' 라는 이름으로 재명명한다. 그리고 속성의 이름도 Dname, Dno, m1, m2로 재명명한다.
그리고 NATUARL JOIN 키워드 양옆으로 두 릴레이션을 적어주는데 그러면
두 릴레이션에서 애트리뷰트 이름이 같은 애트리뷰트끼리 조인 조건을 명시하게 된다. DEPARTMENT를 재명명한
DF (Dname, Dno, m1, m2)에서 Dno만 EMPLOYEE와 이름이 같은 애트리뷰트라면 위 예시는
조인 조건으로 EMPLOYEE.Dno = DF.Dno 이 조인 조건이 걸려있는 것이다.
6-2) FROM 절에 외부 조인 조건
7-1) 집단 함수: SUM, MAX, MIN, AVG
쉽다. 이건 위 키워드는 단어 뜻 그대로의 함수 역할을 한다.
- SELECT SUM(Salary) As Total, MAX(Salary) AS MAX_Sal, MIN(Salary) AS MIN_Sal, AVG(Salary) As AVG_Sal
FROM EMPLOYEE
SUM은 월급의 합, MAX는 최대값, MIN은 최솟값, AVG는 평균 값을 반환한다. 결과는 여러 투플들의 결과 값이 반영된 하나의 행으로써 나타난다.
7-2) 집단함수: COUNT
COUNT만 따로 빼서 정리한 이유는 몇가지 주의할 점이 있어서이다.
- SELECT COUNT(DISTINCT Salary)
FROM EMPLOYEE;
COUNT (*) 결과로 나온 전체 투플의 개수를 세는 것임.
COUNT(DISTICNT Salary)는 직원들 중 구별되는 Salary의 개수를 세는 것임.
- SELECT Dno, COUNT(*)
FROM EMPLOYEE
WHERE Salary > 100000 AND Dno IN
( SELECT Dno
FROM ~~~
GRPUP BY Dno
HAVING COUNT(*) >5)
GROUP BY Dno;
위 예시를 보면 중첩 질의 안에서 HAVING 조건으로 외부 질의의 그룹핑된 결과 애트리뷰트를 제한할 수 있다. !!
무슨 말이냐면 결국 출력으로 EMPLOYEE 릴레이션안에서 WHERE 조건을 적용한 투플들간에서 Dno으로 그룹핑하고
하는 것이다 .
외부 질의의 WHERE를 보면 월급이 100000이상이고 동시에 Dno이 중첩 질의를 만족시키는 EMPLOYEE의 Dno와 COUNT(*)를 구하는 것이다.
만약 내부 질의의 HAVING 조건이 외부 질의에 있었다면 결과 내용은 완전히 달라진다.
위 코드 처럼 내부 구현에서 HAVING 조건을 걸면 외부 질의에서 Dno로 그룹핑을 하기전에 전체 애트리뷰트에서 먼저 COUNT(*) > 5가 적용되어 다음의 문장이 된다.
"6명 이상이 근무하는 각 부서에서 100000원의 급여가 넘는 사람에 대해~~~"
만약 외부 질의에서 HAVING COUNT(*) > 5이렇게 걸어주면 전체 사원들 중에서 100000원이 넘는 투플들을 먼저 거르고 Dno로 그룹핑을 하고 젤 마지막에 HAVING 조건을 걸게 되어
"6명 이상이 근무하는 각 부서에서 100000원의 급여가 넘는 사람에 대해~~~" 라는 문제에 맞지 않게 된다.
8) 그룹핑 - GROUP BY , HAVING 절
- SELECT Dno, COUNT(*), AVG(Salary) As AVG
FROM EMPLOYEE
GROUP BY name
위 예시에서 GROUP BY를 설명하면 EMPLOYEE 테이블에서 전체 투플에서 DISTINCT name을 'Kim', 'Lee', "Park' 이라고 가정하면 이 3가지를 기준으로 우선 투플들을 그룹핑한다. 아래의 결과로 나온다.
| Dno | Count(*) | AVG |
| 'Kim' | 'Kim' 값을 가진 투플을 가진 개수 | 'Kim' 값을 가진 투플의 Salary 평균 |
| 'Lee' | 'Lee' 값을 가진 투플의 개수 | 'Lee' 값을 가진 투플의 Salary 평균 |
| 'Park' | 'Park' 값을 가진 투플의 개수 | 'Park' 값을 가진 투플의 Salary 평균 |
- SELECT Pname, Pnumber, COUNT(*)
FROM PROJECT, WORKS_ON
WHERE Pnumber = Pno
GROUP BY Pnumber, Pname
HAVING COUNT(*) > 2;
우선 GROUP BY가 Pnumber, Pname 하고 두 개가 있는데 이 것은 Pnumber를 기준으로 그룹핑해서 테이블을 하나 만들고 Pname을 기준으로 그룹핑해서 결과 테이블을 하나 더 만들어란 소리임.
주위할 점은 그룹핑을 할 때 WHERE 절을 만족시키는 투플들을 기준으로 그룹핑을 하란 소리다.
HAVING은 선택된 투플들의 그룹들에 대한 조건을 명시한다. HAVING 조건이 적용되기 전까지 보면
Pnumber를 기준으로 그룹핑한 하나의 결과 테이블1, Pname을 기준으로 그룹핑한 하나의 결과 테이블2 이 있는데 두 그룹핑된 테이블에 대해 애트리뷰트인 COUNT(*)을 제한하는 조건으로 COUNT(*)이 3이상인 값만 거르란 소리이다.
9) WITH, CASE
'전공 > 데이터베이스' 카테고리의 다른 글
| 7-1. 관계대수와 관계해석 : 단항 관계 연산(SELECT, PROECT), 집합 이론 (0) | 2021.10.30 |
|---|---|
| 6-3. SQL 스키마 변경문 (0) | 2021.10.27 |
| 6-2. 사용자 주장으로 제약 조건 만들기 / SQL view (0) | 2021.10.27 |
| 5. SQL 명령어 (0) | 2021.10.12 |
| 4. 관계 데이터 모델 , 관계 데이터 베이스 제약 조건 (0) | 2021.10.05 |