DB05 - More SQL

Agenda

- 중첩 서브쿼리

- 집합 멤버십 (SOME, ALL, EXISTS)

 

Running Examples

- 관계(테이블): instructor, teaches

- 관계(테이블): course, takes

- 관계(테이블): student

 

Nested Subqueries

1. SQL은 서브쿼리를 중첩할 수 있는 메커니즘을 제공한다. 서브쿼리는 또 다른 쿼리 내에 중첩된(is nested) SELECT-FROM-WHERE 표현식이다.

 

2. 중첩은 다음 SQL 쿼리에서 이루어질 수 있다:

SELECT A1, A2, ..., An
FROM r1, r2, ..., rm
WHERE P

 

   1) FROM 절: ri는 유효한 서브쿼리로 대체될 수 있다.

   2) WHERE 절: P는 B <operation> (서브쿼리)의 형태로 표현될 수 있다.

      - B는 속성이고 <연산>은 나중에 정의할 것이다.

   3) SELECT 절: Ai는 단일 값을 생성하는 서브쿼리로 대체될 수 있다(스칼라 서브쿼리).

 

FROM 절의 하위 쿼리

1. 평균 강사 급여를 찾아보자. 평균 급여가 $42,000보다 큰 부서의 평균 강사 급여를 찾는다.

SELECT D.dept_name, D.avg_salary
FROM (
  SELECT dept_name, AVG(salary) AS avg_salary 
  FROM instructor 
  GROUP BY dept_name) AS D
WHERE D.avg_salary > 42000;

- 서브쿼리의 결과를 D라는 별칭(Alias)으로 사용

 

WITH Clause

1. WITH 절은 임시 관계를 정의하는 방법을 제공한다.

   - 관계는 WITH 절이 발생하는 쿼리에서만 사용 가능하다.

 

2. 최대 예산을 가진 모든 부서를 찾는다.

WITH max_budget (value) AS(
  SELECT MAX(budget) 
  FROM department)

SELECT department.dept_name
FROM department, max_budget
WHERE department.budget = max_budget.value;

 

Scalar Subquery

1. 스칼라 서브쿼리는 단일 값이 예상되는 곳에서 사용된다. -> 스칼라 서브쿼리(Scalar Subquery)는 단일 값(하나의 튜플과 하나의 컬럼)을 반환하는 서브쿼리이다.

   - 서브쿼리가 둘 이상의 결과 튜플을 반환하면 런타임 오류가 발생한다.

   - 스칼라 서브쿼리는 WHERE 절, SELECT 절 등 단일 값이 필요한 위치에서 사용된다.

 

2. 각 부서의 강사 수와 함께 모든 부서를 나열한다.

SELECT dept_name,
  (
    SELECT COUNT(*) 
    FROM instructor 
    WHERE department.dept_name = instructor.dept_name
  ) AS num_instructors
FROM department;

 

---

 

Set Membership

1. 2017년 가을 및 2018년 봄에 개설된 과목을 찾는다.

# IN

SELECT DISTINCT course_id
FROM teaches
WHERE semester = 'Fall' AND year= 2017 AND
  course_id IN (
    SELECT course_id 
    FROM teaches 
    WHERE semester = 'Spring' AND year= 2018
  );

 

2. 2017년 가을에 개설되었지만 2018년 봄에는 개설되지 않은 과목을 찾는다.

# NOT IN

SELECT DISTINCT course_id
FROM teaches
WHERE semester = 'Fall' AND year= 2017 AND
course_id NOT IN (
  SELECT course_id 
  FROM teaches 
  WHERE semester = 'Spring' AND year= 2018
);

 

3. "모차르트" 또는 "아인슈타인"이 아닌 모든 강사의 이름을 찾는다.

# 텍스트를 지정하여 IN을 구별할 수 있다.
SELECT DISTINCT name
FROM instructor
WHERE name NOT IN ('Mozart', 'Einstein');

 

4. ID 10101인 강사가 가르친 과목을 수강한 고유 학생 수를 찾는다.

# 튜플 레벨로 비교

SELECT COUNT(DISTINCT ID)
FROM takes
WHERE (course_id, sec_id, semester, year) IN(
  SELECT course_id, sec_id, semester, year 
  FROM teaches WHERE teaches.ID= 10101
);

 

   - 주의: 위 쿼리는 훨씬 간단한 방식으로 작성할 수 있다. 위의 형식은 SQL 기능을 설명하기 위한 것이다.

 

Set Comparison – SOME

1. 생물학 부서의 강사 중 적어도 한 명보다 급여가 높은 강사의 이름을 찾는다.

# SOME : 최소 하나라도!

SELECT DISTINCT T.name
FROM instructor AS T, instructor AS S
WHERE T.salary > S.salary AND S.dept_name = 'Biology';

 

2. SOME 절을 사용한 동일한 쿼리

SELECT name
FROM instructor
WHERE salary > SOME (
  SELECT salary 
  FROM instructor 
  WHERE dept_name = 'Biology'
);

 

Interpretation of SOME

1. 기본 정의 :

: F <comp> SOME r ⟺ ∃t ∈ r such that (F <comp> t)

: F <comp> SOME r는 Ǝt가 r에 속하고 (F <comp> t)인 경우를 의미한다.

: 여기서 <comp>는 다음의 비교 연산자가 될 수 있다: <, ≤, >, =, ≠

   1) 예시

 

      - (5 < SOME [0,5,6]) = true → 관계에서 5보다 큰 값(6)이 존재하므로 참

      - (5 < SOME [0,5]) = false → 관계에서 5보다 큰 값이 없으므로 거짓

      - (5 = SOME [0,5]) = true → 관계에서 5와 같은 값이 존재하므로 참

      - (5 ≠ SOME [0,5]) = true → 관계에서 5와 다른 값(0)이 존재하므로 참

   2) 중요한 특징:

      - (= SOME) ≡ IN → "같은 것이 존재한다"는 의미의 SOME은 IN 연산자와 동일

      - (≠ SOME) ≢ NOT IN → "다른 것이 존재한다"는 의미의 SOME은 NOT IN과 동일하지 않음

      3) 추가

      : 이는 데이터베이스 쿼리나 논리적 연산에서 SOME 연산자가 어떻게 동작하는지를 보여주는 중요한 개념이다.

Set Comparison – ALL

 

1. 생물학 부서의 모든 강사보다 급여가 높은 모든 강사의 이름을 찾는다.

# ALL : 서브쿼리의 모든 값이 참이 되어야 한다.
SELECT name
FROM instructor
WHERE salary > ALL (
  SELECT salary 
  FROM instructor 
  WHERE dept_name = 'Biology'
);

 

Interpretation of ALL

1. 기본 정의

: F <comp> ALL r ⟺ ∀t ∈ r (F <comp> t)

: F <comp> ALL r는 ∀t ∈ r (F <comp> t)와 동치이다.

: 여기서 모든 요소(t)에 대해 조건이 성립해야 한다.

   1) 예시들

      - (5 < ALL [0,5,6]) = false → 모든 값이 5보다 크지 않으므로(5는 5보다 크지 않음) 거짓

      - (5 < ALL [6,10]) = true → 모든 값(6,10)이 5보다 크므로 참

      - (5 = ALL [4,5]) = false → 모든 값이 5와 같지 않으므로(4는 5가 아님) 거짓

      - (5 ≠ ALL [4,6]) = true → 모든 값(4,6)이 5와 다르므로 참

   2) 중요한 특징:

      - (≠ ALL) ≡ NOT IN → "모두와 다르다"는 의미의 ALL은 NOT IN 연산자와 동일

      - (= ALL) ≢ IN → "모두와 같다"는 의미의 ALL은 IN과 동일하지 않음

   3) 추가

   : 이는 SOME 연산자와 대비되는 개념으로, ALL은 집합의 모든 요소가 조건을 만족해야 하는 반면, SOME은 하나의 요소만 만족하면 된다. 이러한 차이는 데이터베이스 쿼리나 논리적 연산에서 매우 중요한 의미를 가진다.

 

Test for Empty Relations

1. EXISTS 구성은 인수 서브쿼리가 비어 있지 않으면 true 값을 반환한다.

   - EXISTS r <=> r ≠ Ø

   : EXISTS r는 r ≠ Ø와 동치이다.

   - NOT EXISTS r <=> r = Ø 

   : NOT EXISTS r는 r = Ø와 동치이다.

 

Use of EXISTS

1. "2017년 가을 학기와 2018년 봄 학기에 개설된 모든 과목을 찾는다"는 쿼리를 지정하는 또 다른 방법이다.

SELECT course_id
FROM teaches AS S
WHERE semester = 'Fall' AND year = 2017 AND
EXISTS (
  SELECT * 
  FROM teaches AS T 
  WHERE semester = 'Spring' AND year= 2018 AND S.course_id = T.course_id
);

 

Use of NOT EXISTS

1. 음악 부서에서 개설된 모든 과목을 수강한 학생을 찾는다.

SELECT DISTINCT S.ID, S.name
FROM student AS S
WHERE NOT EXISTS (
  SELECT course_id 
  FROM course 
  WHERE dept_name = 'Music' AND course_id NOT IN (
    SELECT T.course_id 
    FROM takes AS T 
    WHERE S.ID = T.ID
  )
);

 

 

2. 주의: 특정 맥락에서 AS의 이름 변경은 선택적이다.

SELECT DISTINCT ID, name
FROM student
WHERE NOT EXISTS (
  SELECT course_id 
  FROM course 
  WHERE dept_name = 'Music' AND course_id NOT IN (
    SELECT course_id 
    FROM takes 
    WHERE student.ID = takes.ID
  )
);

 

   1) 예외: 다음 쿼리는 빈 관계를 결과로 반환한다.

SELECT DISTINCT name
FROM instructor
WHERE salary > salary AND dept_name = 'Biology';

 

3. 일부 시스템은 EXCEPT 절을 지원하지만 MySQL은 지원하지 않는다.

 

4. 음악 부서에서 개설된 모든 과목을 수강한 학생을 찾는다.

SELECT DISTINCT S.ID, S.name
FROM student AS S
WHERE NOT EXISTS (
  (
    SELECT course_id 
    FROM course 
    WHERE dept_name = 'Music'
  ) EXCEPT (
    SELECT T.course_id 
    FROM takes AS T 
    WHERE S.ID = T.ID
  )
);

 

Test for Absence of Duplicate Tuples

1. UNIQUE 구성은 서브쿼리가 결과에 중복 튜플이 있는지 테스트한다.

   1) UNIQUE는 주어진 서브쿼리에 중복이 없으면 "true"로 평가된다.

   2) MySQL은 UNIQUE 테스트를 지원하지 않는다(UNIQUE는 MySQL에서 제약 조건 지정자이다).

 

2. 2017년에 최대 한 번 개설된 모든 과목을 찾는다.

SELECT T.course_id
FROM course AS T
WHERE UNIQUE (
  SELECT R.course_id 
  FROM teaches AS R 
  WHERE T.course_id = R.course_id AND R.year = 2017
);

 

EOF

- 다음 내용: SQL DDL