Ch4_Intermediate_SQL

Chapter 4. Intermediate SQL

한 줄 핵심: 중급 SQL은 조인의 정밀 제어(타입×조건), 뷰로 데이터를 추상화·은닉, 무결성 제약으로 일관성 보장, 데이터 타입·인덱스·권한으로 실제 운용을 떠받친다.

이 챕터가 답하는 핵심 질문

• Q1. 조인은 어떤 두 축(타입·조건)으로 분류되나?
• Q2. natural join은 무엇이고 왜 위험한가?
• Q3. outer join은 무슨 문제를 해결하나? (left/right/full)
• Q4. on 조건과 natural/using 조건은 무엇이 다른가?
• Q5. 뷰(view)란 무엇이고 왜 쓰나?
• Q6. 뷰는 어떻게 정의·중첩·확장되나?
• Q7. 뷰를 갱신(insert/update)할 수 있나? 언제 안 되나?
• Q8. 무결성 제약(not null·unique·check)은 어떻게 거나?
• Q9. 참조 무결성(foreign key)과 cascade 동작은?
• Q10. SQL의 날짜/시간·사용자 정의 타입·도메인은?
• Q11. 인덱스는 왜·어떻게 만드나?
• Q12. 권한(grant/revoke/role/뷰 권한)은 어떻게 관리하나?

---

Q1. 조인은 어떤 두 축으로 분류되나?

A. 모든 조인은 조인 타입(매칭 안 된 튜플을 어떻게 처리) × 조인 조건(어떤 튜플을 매칭)의 조합이다.

• 조인 연산은 두 릴레이션을 받아 새 릴레이션을 반환한다. 본질은 카티션 곱 + 매칭 조건.
• 주로 from 절에서 서브쿼리 표현식으로 쓰인다.
• 조인 타입은 매칭되지 않은 튜플의 처리를, 조인 조건은 어떤 튜플이 매칭되는지를 정의한다.

축	종류	설명
조인 타입	inner join	매칭 튜플만 반환 (기본값)
	left outer join	왼쪽 릴레이션 전부 보존
	right outer join	오른쪽 릴레이션 전부 보존
	full outer join	양쪽 전부 보존
조인 조건	natural	동일 이름 속성 전부로 매칭, 중복 열 1개만
	on <predicate>	임의 조건 매칭, 중복 열 둘 다 유지
	using (A₁, ..., Aₙ)	지정 속성만 매칭, 중복 열 1개만

조인 이름을 읽는 법

course natural left outer join prereq를 보면 두 부분으로 쪼갠다.

• natural = 같은 이름의 컬럼(course_id)으로 자동 매칭한다.
• left outer = 왼쪽 course의 행은 짝이 없어도 반드시 남긴다.

그래서 이 질의는 “모든 과목을 보여주되, 선수과목이 있으면 붙이고 없으면 null로 채워라”라는 뜻이다. 조인 문법이 길어 보여도 항상 조건을 먼저 보고, 그다음 타입을 보면 해석이 쉬워진다.

두 축을 따로 외우면 9가지 조인이 한눈에

조인 이름이 복잡해 보여도 사실 **타입(4가지) × 조건(어떻게 매칭)**의 조합일 뿐이다.

• 타입은 “짝 못 찾은 외톨이 행을 버릴까(inner) 살릴까(outer)“만 정한다. left=왼쪽 외톨이 살림, right=오른쪽 살림, full=양쪽 다 살림.
• 조건은 “무엇을 기준으로 짝을 지을까”만 정한다.

비유: 두 반 학생을 학번으로 짝짓기(조건)할 때, 짝 못 찾은 학생을 명단에서 지울지(inner) / null 파트너와 함께 남길지(outer)(타입)를 고르는 것. 그래서 left outer join ... using(ID)처럼 둘을 자유롭게 조합한다.

---

Q2. natural join은 무엇이고 왜 위험한가?

A. natural join은 이름이 같은 모든 공통 속성으로 자동 매칭하고 공통 열을 하나만 남기는데, 의미가 다른 동명 속성까지 묶어 결과가 잘못 누락될 수 있어 위험하다.

-- 학생 이름 + 수강 과목 ID
select name, course_id from student, takes
where student.ID = takes.ID;          -- 방법 1: 명시적 조건
 
select name, course_id
from student natural join takes;       -- 방법 2: natural join

• 관계대수: student ⋈₍student.ID = takes.ID₎ takes
• 연쇄 가능: from r₁ natural join r₂ natural join ... natural join rₙ

natural join의 위험성

-- 잘못된 버전: student와 course가 둘 다 dept_name을 가짐
select name, title
from student natural join takes natural join course;

dept_name까지 매칭되어, 학생이 자기 학과가 아닌 과목을 수강한 경우가 누락된다.

-- 올바른 버전 1: natural join + where
select name, title
from student natural join takes, course
where takes.course_id = course.course_id;
 
-- 올바른 버전 2: join ... using
select name, title
from (student natural join takes) join course using (course_id);

---

Q3. outer join은 무슨 문제를 해결하나?

A. outer join은 정보 손실 방지를 위한 확장으로, inner join에서 탈락할 비매칭 튜플을 null로 채워 결과에 남긴다.

한 줄 직관: outer join = "inner join 결과 + 버려졌던 외톨이를 null로 되살리기"

inner join은 짝이 맞는 행만 남기므로 “선수과목 정보가 없는 과목”이나 “어느 과목의 선수과목인지 모를 prereq”가 결과에서 사라진다. 보고서에 “선수과목 없음”인 과목도 꼭 나와야 한다면 이 손실이 문제다. outer join은 그 외톨이를 빈 칸(null)을 채워 결과에 다시 포함시킨다. 아래 표에서 굵은 null이 바로 “되살아난 외톨이”다.

예시 데이터: course에는 CS-347이 없고, prereq에는 CS-315가 없다.

course

course_id	title	dept_name	credits
BIO-301	Genetics	Biology	4
CS-190	Game Design	Comp. Sci.	4
CS-315	Robotics	Comp. Sci.	3

prereq

course_id	prereq_id
BIO-301	BIO-101
CS-190	CS-101
CS-347	CS-101

left / right / full

course natural left outer join prereq    -- 왼쪽 전부 보존

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
CS-315	Robotics	Comp. Sci.	3	null

course natural right outer join prereq   -- 오른쪽 전부 보존

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
CS-347	null	null	null	CS-101

course natural full outer join prereq    -- 양쪽 전부 보존

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
CS-315	Robotics	Comp. Sci.	3	null
CS-347	null	null	null	CS-101

---

Q4. on 조건과 natural/using 조건은 무엇이 다른가?

A. on은 임의 조건을 쓸 수 있지만 공통 열이 양쪽 모두 결과에 중복으로 남고, natural/using은 공통 열을 하나만 남긴다.

-- inner join + on: course_id가 양쪽 모두 결과에 나타남 (중복!)
course inner join prereq on course.course_id = prereq.course_id

course_id	title	dept_name	credits	prereq_id	course_id
BIO-301	Genetics	Biology	4	BIO-101	BIO-301
CS-190	Game Design	Comp. Sci.	4	CS-101	CS-190

-- left outer join + on
course left outer join prereq on course.course_id = prereq.course_id

course_id	title	dept_name	credits	prereq_id	course_id
BIO-301	Genetics	Biology	4	BIO-101	BIO-301
CS-190	Game Design	Comp. Sci.	4	CS-101	CS-190
CS-315	Robotics	Comp. Sci.	3	null	null

-- using: 지정 속성으로 매칭, 중복 열 1개만
course full outer join prereq using (course_id)

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
CS-315	Robotics	Comp. Sci.	3	null
CS-347	null	null	null	CS-101

outer join에서 조건을 on에 두느냐 where에 두느냐는 결과가 다르다 (단골 시험·실수 포인트)

inner join에서는 on이든 where든 결과가 같다. 하지만 outer join에서는 다르다.

• on의 조건: 조인하는 도중에 적용 → 짝짓기 기준일 뿐, 보존되는 외톨이 행은 살아남는다.
• where의 조건: 조인이 끝난 후 적용 → null로 채워진 외톨이 행이 조건에 걸려 도로 사라질 수 있다.

-- (A) on 안에 조건: CS-315 외톨이 행이 살아남음
course left outer join prereq
    on course.course_id = prereq.course_id and prereq.prereq_id = 'CS-101'
-- (B) where로 조건: prereq_id가 null인 CS-315가 'CS-101'≠null 이라 탈락
course left outer join prereq
    on course.course_id = prereq.course_id
where prereq.prereq_id = 'CS-101'

(B)는 사실상 inner join처럼 동작해 버린다. outer join으로 외톨이를 살리려면 그 필터를 on에 넣어야 한다.

---

Q5. 뷰(view)란 무엇이고 왜 쓰나?

A. 뷰는 실제 저장되지 않는 가상 릴레이션(virtual relation)으로, 특정 사용자에게 일부 데이터를 숨기거나 단순화하는 메커니즘이다.

• 모든 사용자가 전체 논리 모델(모든 실제 릴레이션)을 볼 필요는 없다.
• 예: 교수의 이름·학과는 보여주되 급여는 숨기고 싶을 때 → select ID, name, dept_name from instructor만 노출.
• 개념 모델에 없지만 사용자에게 릴레이션처럼 보이는 것이 뷰다.

뷰 = "자주 쓰는 질의에 붙인 별명" + "민감 정보 가리개"

뷰는 데이터를 복사해 저장하는 게 아니라, select 문 자체에 이름을 붙여둔 것이다. 비유하면 엑셀에서 매번 같은 필터를 거는 대신 “필터된 화면”을 저장해 둔 것. 두 가지로 유용하다.

1. 단순화: 복잡한 조인 쿼리를 faculty 같은 짧은 이름으로 재사용.
2. 은닉(보안): 조교에게 instructor의 salary 열만 빼고 보여주고 싶을 때, salary를 제외한 뷰만 권한 부여 → 조교는 급여를 영원히 볼 수 없다.

---

Q6. 뷰는 어떻게 정의·중첩·확장되나?

A. create view v as <질의>로 정의하며, 뷰는 새 릴레이션 생성이 아니라 질의식의 저장이라 사용 시점에 원래 정의로 대입(view expansion)된다. 뷰는 다른 뷰 위에 쌓을 수도 있다.

create view v as <query expression>

• 뷰 정의 ≠ 새 릴레이션 생성. 뷰는 질의식을 저장해 두고, 사용처에서 대입된다.

-- 정의와 사용
create view faculty as
    select ID, name, dept_name from instructor;
 
select name from faculty where dept_name = 'Biology';
 
-- 집계 뷰: 명시적 컬럼명
create view departments_total_salary(dept_name, total_salary) as
    select dept_name, sum(salary) from instructor group by dept_name;

뷰 위에 뷰 쌓기 + 의존 관계

create view physics_fall_2017 as
    select course.course_id, sec_id, building, room_number
    from course, section
    where course.course_id = section.course_id
        and course.dept_name = 'Physics'
        and section.semester = 'Fall' and section.year = 2017;
 
create view physics_fall_2017_watson as
    select course_id, room_number
    from physics_fall_2017 where building = 'Watson';

• v₁이 정의식에서 v₂를 쓰면 v₁은 v₂에 직접 의존(depend directly). 경로가 존재하면 의존(depend on).

뷰 확장 (View Expansion)

repeat
    e₁ 안의 뷰 릴레이션 vᵢ를 찾는다
    vᵢ를 vᵢ의 정의식으로 대체한다
until 더 이상 뷰 릴레이션이 없을 때까지

-- 확장 후: physics_fall_2017이 정의로 치환됨
create view physics_fall_2017_watson as
    select course_id, room_number
    from (select course.course_id, building, room_number
          from course, section
          where course.course_id = section.course_id
              and course.dept_name = 'Physics'
              and section.semester = 'Fall' and section.year = '2017')
    where building = 'Watson';

뷰 정의가 재귀적이지 않은 한 이 루프는 반드시 종료한다.

---

Q7. 뷰를 갱신할 수 있나? 언제 안 되나?

A. 뷰 갱신은 기반 릴레이션에 반영되어야 하지만 변환이 모호한 경우가 많아, 대부분의 SQL은 단일 릴레이션 기반의 단순 뷰(simple view)에 대해서만 갱신을 허용한다.

-- 단순한 경우: salary가 없어 → 거부하거나 null로 채워 삽입
create view faculty as select ID, name, dept_name from instructor;
insert into faculty values ('30765', 'Green', 'Music');
-- → instructor에 ('30765','Green','Music', null) 삽입

-- 올바르게 변환 불가: 조인 뷰
create view instructor_info as
    select ID, name, building from instructor, department
    where instructor.dept_name = department.dept_name;
insert into instructor_info values ('69987', 'White', 'Taylor');
-- → Taylor 건물 학과가 없으면, 삽입 후에도 뷰에 안 보일 수 있음

-- 뷰 조건 불만족 삽입
create view history_instructors as
    select * from instructor where dept_name = 'History';
insert into history_instructors values ('25566', 'Brown', 'Biology', 100000);
-- → instructor엔 들어가지만 dept_name='History'가 아니라 뷰엔 안 보임

뷰 갱신이 허용되는 조건 (simple view)

1. from 절에 데이터베이스 릴레이션이 하나만
2. select 절에 속성 이름만 (수식·집계·distinct 금지). 미포함 속성은 null 설정 가능
3. group by·having 절 없음

왜 이런 제약이 붙나 — "거꾸로 되돌릴 수 있어야 갱신 가능"

뷰 갱신의 본질은 “뷰에 한 변경을 원본 테이블의 어떤 변경으로 번역할 수 있는가”이다. 번역이 애매하면 금지한다.

• 조인 뷰(테이블 2개): 뷰에 한 행을 넣을 때 그게 어느 원본 테이블의 행인지, 빈 칸은 어떻게 채울지 모호 → 금지.
• 집계 뷰(avg(salary)): 평균을 60000으로 바꾸라 하면 원본의 각 급여를 얼마로 되돌릴지 무한히 많음 → 불가능.
• 수식(salary/12): 월급을 고치면 연봉은? 가능은 해도 모호 → 금지.

즉 “뷰의 한 행 ↔ 원본의 한 행”이 1:1로 깔끔히 대응될 때만 갱신을 허용한다고 이해하면 된다.

---

Q8. 무결성 제약은 어떻게 거나?

A. 무결성 제약은 우발적 데이터 손상을 방지하며, 단일 릴레이션에는 not null·primary key·unique·check(P)를 쓴다.

예: 잔액 ≥ $10,000,시급\ge$4.00, 전화번호는 non-null.

제약	설명
not null	null 금지
primary key	기본키 (자동 not null + unique)
unique (A₁,...,Aₘ)	후보키 선언. 후보키는 null 허용 (primary key와 차이)
check (P)	모든 튜플이 P를 만족해야 함

name varchar(20) not null
budget numeric(12, 2) not null
unique (A₁, A₂, ..., Aₘ)

check 절 (서브쿼리 가능)

create table section (
    ...
    semester varchar(6),
    primary key (course_id, sec_id, semester, year),
    check (semester in ('Fall', 'Winter', 'Spring', 'Summer'))
);

-- 복합 check: 서브쿼리 포함 가능
check (time_slot_id in (select time_slot_id from time_slot))

이 조건은 section에 튜플이 삽입/수정될 때뿐 아니라, time_slot 릴레이션이 변경될 때도 검사되어야 한다.

---

Q9. 참조 무결성과 cascade 동작은?

A. 외래키(foreign key)는 한 릴레이션의 값이 다른 릴레이션의 기본키에 반드시 존재하도록 보장하고, 위반 시 기본은 거부지만 cascade/set null/set default로 연쇄 처리할 수 있다.

• 정의: 속성 집합 A가 R, S 모두에 있고 A가 S의 기본키일 때, R의 A 값이 모두 S에도 나타나면 A는 R의 외래키.

-- 기본: 참조 테이블의 기본키 자동 참조
foreign key (dept_name) references department
-- 명시적 참조 속성 지정
foreign key (dept_name) references department (dept_name)

cascade 동작

create table course (
    ...
    dept_name varchar(20),
    foreign key (dept_name) references department
        on delete cascade
        on update cascade,
    ...
);

동작	설명
cascade	참조 대상이 삭제/갱신되면 참조 튜플도 함께 삭제/갱신
set null	외래키 값을 null로 설정
set default	외래키 값을 기본값으로 설정

트랜잭션 중 무결성 위반 (자기 참조)

create table person (
    ID char(10), name char(40), mother char(10), father char(10),
    primary key (ID),
    foreign key (father) references person,
    foreign key (mother) references person
);

해결 3가지: ① 부모를 먼저 삽입 후 자식, ② father/mother를 null로 삽입 후 update (not null이면 불가), ③ 제약 검사 지연(defer) (트랜잭션 종료 시 검사).

---

Q10. SQL의 날짜/시간·사용자 정의 타입·도메인은?

A. SQL은 date/time/timestamp/interval 내장 타입과, create type(타입 안전성)·create domain(제약 부착) 두 가지 사용자 정의 타입을 제공한다.

내장 데이터 타입

타입	설명	예시
date	날짜 (4자리 연도, 월, 일)	date '2005-7-27'
time	시:분:초	time '09:00:30', time '09:00:30.75'
timestamp	날짜 + 시간	timestamp '2005-7-27 09:00:30.75'
interval	기간	interval '1' day

• date/time/timestamp 뺄셈 → interval 반환. interval은 date/time/timestamp에 더하거나 뺄 수 있다.

사용자 정의 타입 (create type)

create type Dollars as numeric(12, 2) final
create type Pounds  as numeric(12, 2) final
 
create table department (
    dept_name varchar(20),
    building  varchar(15),
    budget    Dollars
);

• 둘 다 numeric 기반이지만 서로 다른 타입. Dollars 값을 Pounds 변수에 대입하면 컴파일 타임 오류 → 타입 안전성.

도메인 (create domain, SQL-92)

create domain person_name char(20) not null
 
create domain degree_level varchar(10)
    constraint degree_level_test
        check (value in ('Bachelors', 'Masters', 'Doctorate'));

• 타입과 유사하나 도메인은 not null 등 제약을 직접 부착할 수 있다.
• constraint로 제약에 이름 부여, value는 대입되는 값을 가리키는 특수 키워드.

---

Q11. 인덱스는 왜·어떻게 만드나?

A. 인덱스는 특정 속성 값으로 튜플을 전체 스캔 없이 빠르게 찾는 데이터 구조로, create index로 만든다.

• 대부분의 쿼리는 전체 레코드 중 극히 일부만 참조 → 전수 스캔은 비효율.

create index <index-name> on <relation-name> (attribute);
 
create index studentID_index on student(ID);

-- 이 쿼리는 인덱스로 전체 스캔 없이 빠르게 처리됨
select * from student where ID = '12345';

---

Q12. 권한은 어떻게 관리하나?

A. grant로 부여, revoke로 회수하며, role(역할)로 권한을 묶어 사용자·역할에 상속시키고, 뷰 권한은 기반 릴레이션 권한과 별도로 관리된다.

권한(privilege)의 종류

특권	설명
Read / select	읽기·뷰 질의 (수정 불가)
Insert	삽입 (기존 데이터 수정 불가)
Update	수정 (삭제 불가)
Delete	삭제
all privileges	모든 허용 특권의 축약형

grant / revoke

grant <privilege list> on <relation or view> to <user list>
grant select on department to Amit, Satoshi;
grant select on instructor to U1, U2, U3;
 
revoke <privilege list> on <relation or view> from <user list>
revoke select on student from U1, U2, U3;

• <user list>: 사용자 ID / public(모든 유효 사용자) / role.
• 뷰 권한 부여가 기반 릴레이션 권한까지 주지는 않는다.
• 부여자(grantor)는 해당 특권을 이미 갖고 있거나 DBA여야 한다.

회수 규칙:

• all → 가진 모든 특권 회수. public 포함 시 명시적으로 받은 사용자 외 전원이 잃음.
• 같은 특권을 서로 다른 부여자에게서 받았으면 한쪽 회수 후에도 유지될 수 있음.
• 회수되는 특권에 의존하는 모든 특권도 함께 회수 (cascading revocation).

         U1 ───→ U4
        ↗        ↘
DBA ──→ U2 ───→ U5
        ↘
         U3

DBA가 U1을 회수하면 U1이 부여한 U4도 회수. 단 U5가 U1·U2 양쪽에서 받았다면 U2 경로가 남아 유지.

cascading revocation 직관: "권한의 족보(출처)를 따라간다"

권한은 누가 누구에게 줬는지 출처가 기록된다. 위 그림에서 화살표는 “권한을 물려준 방향”이다.

• 회수의 원칙: 어떤 사람이 권한을 잃으면, 오직 그 사람한테서만 권한을 받았던 사람도 연쇄적으로 잃는다 (도미노).
• 살아남는 조건: 회수 후에도 DBA까지 닿는 다른 경로가 하나라도 남아 있으면 유지된다.

사용자	권한 출처(경로)	U1 회수 후
U4	U1 ← DBA 뿐	❌ 잃음 (유일한 줄이 끊김)
U5	U1 ← DBA, U2 ← DBA	✅ 유지 (U2 경로 생존)

비유: 소문(권한)을 두 친구에게서 들었다면, 한 친구가 입을 닫아도 다른 친구를 통해 여전히 알 수 있다.

역할 (Roles)

create role instructor;
grant instructor to Amit;            -- 사용자에 역할 부여
grant select on takes to instructor; -- 역할에 특권 부여
 
-- 역할 간 상속 (chain)
create role teaching_assistant;
grant teaching_assistant to instructor;  -- instructor가 TA 특권 상속
 
create role dean;
grant instructor to dean;
grant dean to Satoshi;  -- Satoshi = dean + instructor + teaching_assistant 특권

뷰에 대한 권한

create view geo_instructor as
    (select * from instructor where dept_name = 'Geology');
grant select on geo_instructor to geo_staff;

• geo_staff는 instructor 권한이 없어도 뷰 권한만으로 뷰 결과를 볼 수 있다.
• 따라서 시스템은 뷰를 정의로 치환하기 전에 뷰 자체 권한을 먼저 검사한다.
• 뷰의 **생성자(creator)**는 기반 릴레이션(instructor)에 대한 select 권한을 가져야 한다.

---

부록. 요약 비교표

조인 타입별 동작

조인 타입	왼쪽 비매칭	오른쪽 비매칭
inner join	탈락	탈락
left outer join	null 보존	탈락
right outer join	탈락	null 보존
full outer join	null 보존	null 보존

조인 조건별 차이

조건	매칭 기준	중복 열
natural	동일 이름 속성 전부 (자동)	하나만
on <predicate>	임의 조건 (명시적)	양쪽 모두
using (A₁,...)	지정 속성만 (명시적)	하나만

무결성 제약 비교

제약	null 허용	중복 허용	범위
primary key	X	X	단일 릴레이션
unique	O	X	단일 릴레이션
not null	X	O	단일 속성
check (P)	-	-	단일 릴레이션 (서브쿼리 가능)
foreign key	-	-	릴레이션 간 참조

---

출처: Database System Concepts, 7th Edition (Silberschatz, Korth, Sudarshan)