2022.10.22 - [ICE/Database Design] - 세미 조인과 안티 조인의 개념과 사용 방법
2022.10.21 - [ICE/Database Design] - 내부 조인/외부 조인의 종류와 분류
위에 정리한 문서들을 토대로 값을 불러오는 예제 문제들을 풀어보았습니다.
다양한 형태의 SELECT 기술을 사용해서 문제를 풀어볼 것인데, 중간중간 개념설명이 끼어 있어 보기 불편할 수 있습니다. 참고해서 읽어주시면 감사하겠습니다 : )
> 각 테이블 전체 데이터 조회 결과 (전체 데이터가 궁금하면 Cntrl + F 후 select * from 테이블명으로 찾아주세요!)
SELECT-FROM-WHERE 예제
1. 이름이 John B.Smith인 사원의 생일(BDATE)와 주소(ADDRESS)를 검색하시오.
2. Research 부서에서 일하는 모든 사원의 이름(FNAME, LNAME)과 주소를 검색하시오.
이렇게 employee와 department, 서로 다른 두 릴레이션을 묶어주는 문제에서는 제약 조건을 찾는 것(Research) 뿐만 아니라 출력할 결과를 filtering하기 위해 department와 employee의 중첩되는 필드(dnumber, dno)를 같게 설정해주는 것이 맞다.
3. 5번 Department에서 일하는 Employee 투플들의 모든 애트리뷰트 값들을 검색하라.
불필요한 테이블의 호출이다.
4. 데이터베이스에서 Employee의 모든 SSN을 검색하라.
JOIN 예제
1. employee의 dnumber와 department의 dno인 모든 투플을 선택하라
2. 모든 employee의 supervisor를 찾아 lname을 나란히 출력하라.
이 예제에서는 왼쪽 테이블(employee)의 name을 전부 출력하고, 이에 맞추어 supervisor가 있는 employee인지 확인하는 것이기 때문에 left outer join 뒤에 오른쪽 테이블의 이름을 적어주었다.
그리고 다음에 on 명령어로 두 테이블의 연산 조건을 써주면 원하는 결과가 왼쪽 테이블을 기준으로 출력된다.
3. 각 프로젝트에 대해 해당 프로젝트를 관리하는 부서의 정보를 함께 출력하라. (자연 조인)
4. 모든 EMPLOYEE의 이름과 그의 DEPARTMENT 이름을 검색하라 (자연조인)
5. 모든 EMPLOYEE의 이름과 그의 SUPERVISOR의 이름을 검색하라 (셀프 조인)
중첩 질의 (Nested Query) 예제
중첩 질의(Nested Query)란 ?
다른 질의의 WHERE 절에 완전한 SELECT 질의가 나타나는 형태. 외부 질의와 내부 질의로 구분된다.
>, >=, <, <= 뒤에 ALL 연산자가 붙으면 하나의 값이 집합 내의 모든 값들과 같으면 참을 반환한다 .
ANY 혹은 SOME 연산자가 붙으면 하나의 값이 집합 내의 어떤 하나의 값과 같으면 참을 반환한다.
외부 질의와 내부 질의에서 사용하는 FROM절에 있는 릴레이션의 애트리뷰트 이름이 동일하면 모호성이 발생하기 때문에 릴레이션의 별명을 사용하는 것이 바람직하다.
내부 질의에서 외부 질의의 릴레이션의 애트리뷰션을 참조하려면 별명을 사용해야만 한다.
* 상관 중첩 질의(correlated nested query)
내부 질의에서 외부 질의에서 사용하는 릴레이션의 일부 애트리뷰트를 참조하면 두 질의를 상관 중첩 질의라고 한다.
이는 비중첩 질의로 변환할 수 있다.
1. 성이 'Smith'인 종업원(일반 직원 혹은 프로젝트를 담당하는 부서의 관리자)이 참여하는 프로젝트의 번호 목록을 작성하시오.
프로젝트 번호를 출력해야한다. 즉 조회할 대상이 프로젝트 번호이고, 이를 조건에 맞게 출력한 Pnumber와 동등 조인하여 원하는 Pnumber 값을 출력할 수 있다.
2. ESSN이 333445555인 사원이 일하는 프로젝트와 일한 시간의 조합이 동일한 사원의 ESSN을 검색하라. (employee 테이블과 엮을 필요 없다. )
(1) works_on에서 essn='333445555' 사원의 (pno, hours) 데이터를 꺼낸다.
(2) works_on에서 (1) 테이블과 동일한 (pno, hours)를 가진 essn을 검색한다.
works_on에서 where, in 절을 사용해서 (pno, hours) 조합이 같은 것을 출력하였다.
이 테이블과 works_on의 전체 테이블을 비교했을 때 당연히 333445555는 원래 동일하므로 여러번 출력될 것이고, (pno, hours)가 동일한 컬럼이 출력될 것이다. 이때 한 사원이 여러 번 겹쳐서 (pno, hours) 조합이 같아질 수 있으므로 distinct로 사원은 한 번만 출력하는 것으로 한다.
3. 5번 부서에 근무하는 모든 사원보다 급여가 많은 사원을 검색하라.
위에서 언급한 집합 비교를 사용하는 예시이다. all을 사용해서 서브 쿼리에서 조회한 모든 결과보다 현재 salary가 큰 것을 조회할 수 있다.
4. 자신의 부양 가족과 이름, 성별이 같은 종업원들의 이름을 검색하라. (중첩 질의)
employee와 dependent에는 sex라는 동일한 attribute가 있다. 따라서 두 릴레이션의 별칭을 지정해서 애트리뷰트를 명시하는 것이 바람직하다.
이 예제의 경우 서브쿼리에서 메인 쿼리의 릴레이션(employee)의 일부 애트리뷰트(fname)을 참조하고 있으므로, 메인 쿼리와 서브 쿼리를 상관된 질의라고 할 수 있다. (= 상관 중첩 질의)
5. 자신의 부양 가족과 이름, 성별이 같은 종업원들의 이름을 검색하라. (비중첩 질의)
위에서 select-from-where 절과 '=' 혹은 in 연산자를 이용해서 질의를 작성한 경우에는 모두 단일 블록 질의로 변경할 수 있다. 간단히, 서브 쿼리에 있던 릴레이션인 dependent를 위로 끄집어내서 메인 쿼리의 릴레이션인 employee와 함께 from 절에 써주고, where 절에서 e와 d의 비교 컬럼이었던 ssn을 포함해 비교 쿼리를 작성해주면 된다.
EXISTS 함수 예제
EXISTS 함수란?
상관된 중첩 질의에서 내부 질의의 결과가 공집합인지 검사한다.
서브 쿼리의 결과에 최소 한 개의 튜플이 있다면 참을 반환한다.
반대로 서브 쿼리의 결과에 아무런 튜플이 없어야 참을 반환하는 NOT EXISTS 함수도 있다.
1. 자신의 부양 가족과 이름, 성별이 같은 종업원들의 이름을 검색하라. (EXISTS 함수 사용)
2. 부양 가족이 없는 종업원의 이름을 검색하시오.
ssn과 essn은 애트리뷰트명이 다르므로 굳이 별칭을 적어 줄 필요는 없다.
3. 부양 가족이 적어도 한 명 이상 있는 관리자의 이름을 검색하시오.
나는 여기서 super_ssn이 관리자라고 가정하고 풀었다. 같은 결과이지만 부서 관리자는 department 테이블의Mgr_ssn에 있음을 기억하자.
4. 5번 부서가 담당하는 "모든 프로젝트"에 근무하는 사원들의 이름을 검색하라.
5번 부서가 담당하는 프로젝트 중 하나라도 참여하지 않는 사원이라면 출력 결과에서 제외한다. 그 외에 모든 사원을 출력한다. 즉 NOT EXISTS를 사용해서 "하나라도 참여하지 않는 프로젝트가 있는 사원"을 제외하여 출력한다.
"하나라도 참여하지 않는 프로젝트가 있는 사원"을 filtering하는 방법에 따라 두 가지 방법이 생긴다.
(1) EXCEPT 사용 (MySQL에서는 지원하지 않아 INNER JOIN으로 구현하여야 한다. )
(2) NOT EXISTS 사용
명시적 집합과 애트리뷰트 재명명
명시적 집합이란 WHERE 절에 조회하고자 하는 값들의 범위를 명시하는 것이다.
1. 프로젝트 번호 1, 2, 3에서 일하는 모든 종업원들의 SSN을 검색하시오.
2. 종업원에 대해, 종업원의 성과 직속 감독자의 성을 검색하시오.
질의 결과 애트리뷰트의 재명명. 그냥 select를 이용해 불러온 e.lname과 s.lname에 as를 붙여 새로운 이름으로 재명명하는 것을 의미한다.
집계함수 (Aggregate functions)
집계함수(Aggregate functions) 이란?
COUNT, AVG, MAX, MIN, SUM 등의 집단 함수를 제공한다.
1. 종업원의 급여의 합, 최고 급여, 최저 급여, 평균 급여를 구하시오.
2. Research 부서에 있는 모든 종업원들의 급여의 합과 최고 급여, 최소 급여, 평균 급여를 구하시오.
from에서 바로 두 릴레이션을 결합하여 사용할 수 있다. join - on 문법을 사용해 department와 employee 릴레이션의 참조를 연결해주고, where 절을 활용해서 Research 부서임을 명시해준다.
3. 회사 내에서 총 종업원의 수를 검색하시오.
4. 'Research' 부서에 속해 있는 종업원의 수를 검색하시오.
5. 중복되지 않은 Salary 값이 몇 개인지 검사하시오.
count 의 인자에 들어가는 값은 계산하려는 값이고, * 를 입력했을 때는 전체 행의 갯수를 출력한다. 특정 컬럼을 입력했을 때 NULL을 제외하고 계산한 값을 반환한다.
6. 둘 이상의 부양 가족이 있는 모든 사원의 이름을 검색하시오.
"둘 이상의 부양 가족" 에 대한 COUNT 쿼리를 서브 쿼리로 두고 where 절에서 2보다 큰 지 검사하여 메인 쿼리에서 사원의 이름을 검색한다.
메인 쿼리의 릴레이션인 employee를 가져와 서브 쿼리에서 사용하는 상관 중첩 질의 기법을 사용하였다.
Grouping : Group by & Having
그룹화(Grouping) 이란?
특정 애트리뷰트들이 값이 같은 튜플들을 모아서 그룹을 생성하고, 집단 함수를 적용하는 것이다.
이 애트리뷰트들을 그룹화 애트리뷰트라고 하고, GROUP BY 절에 지정할 수 있다.
1. 각 부서에 대해서, 부서 번호, 부서 내에 있는 종업원의 수 , 평균 봉급을 구하시오.
employee에는 부서 정보인 dno가 포함되어 있어서 굳이 where 문을 써줄 필요가 없다 !
2. 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 그 프로젝트에서 근무하는 사원들의 수를 검색하라.
pnumber이 중복되는 컬럼이라 단일 애트리뷰트로 grouping이 되지 않을 경우 group by pnumber, pname처럼 여러가지 애트리뷰트로 이를 정의할 수 있다.
3. 세 명 이상의 사원이 근무하는 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 프로젝트에서 근무하는 사원의 수를 검색하라.
⭐4. 6명 이상의 사원이 근무하는 각 부서에 대해서 부서 번호와 40,000달러가 넘는 급여를 받는 사원의 수를 검색하라.
중첩 질의로 구현하였다. 대상 부서(6명 이상의 사원이 근무하는 부서) 에서 부서 번호와 40,000달러가 넘는 사원의 수를 계산해야한다. 메인 쿼리에서는 Employee와 Department를 연관지어서 Where절에 salary > 40,000을 넣어 필터링을 수행해주고, 서브 쿼리에서는 '대상 부서'를 필터링 하기 위해 group by와 having을 사용해 조건에 맞는 dno를 출력하고 메인 쿼리에서 서브 쿼리와 'in' 연산을 통해 where 절에서 6명 이상의 부서만을 '대상 부서'로 지정하도록 하였다.
5. 각 프로젝트에 대해서 프로젝트 번호, 프로젝트 이름, 5번 부서에 속하면서 프로젝트에서 근무하는 사원의 수를 검색하라.
5번 부서에서 속하는 직원을 걸러서 따로 대상 직원을 정하여 거기서 사원의 수를 COUNT하려고 서브 쿼리를 사용했다. 그러나 employee 테이블에는 dno라는 부서 관련 속성이 이미 있으므로 굳이 조인하지 않아도 해결할 수 있다.
Pnumber=30 이 Count되지 않은 탓인가? 따로 결괏값에서 출력되지 않았다. → 강의에서 내용 확인하기
Assertion을 이용한 제약 조건 명시
(Assertion은 MySQL에서는 미지원한다.)
관계 DB에서 제약 조건을 명시하기 위해 사용하는데, CREATE ASSERTION 명령어를 통해 제약조건 이름과 뒤따라는 CHECK 명령어로 조건을 명시해준다.
1. 사원의 급여가 자신이 근무하는 부서의 관리자의 급여가 많을 수 없도록 SALARY_CONSTRINT 제약조건을 명시하시오.
Trigger를 이용한 제약조건 명시
Assertion과 유사하게, 조건이 발생할 때 데이터베이스를 모니터하기 위해 제약 조건 행동의 유형을 명시한다.
1. 삽입과 갱신 연산을 하는 동안 사원의 월급을 그의 관리자와 비교하기 위한 트리거의 사용
'Major Study > Database Design' 카테고리의 다른 글
| 세미 조인과 안티 조인의 개념과 사용 방법 (0) | 2022.10.22 |
|---|---|
| 내부 조인/외부 조인의 종류와 분류 (0) | 2022.10.21 |
