Ch3_Intro_to_SQL

Chapter 3. Introduction to SQL

핵심 요약: SQL은 관계형 데이터베이스의 표준 언어로, DDL(테이블 정의), DML(데이터 조회·수정)을 모두 포함한다. 기본 쿼리 구조는 SELECT-FROM-WHERE이며, 집합 연산(union/intersect/except), 집계 함수(avg, count 등 + group by/having), 중첩 서브쿼리(in, some, all, exists, unique), with 절, 스칼라 서브쿼리 등 풍부한 표현력을 갖는다. 데이터 수정은 delete, insert, update로 수행하며, case 문을 활용한 조건부 업데이트도 지원한다.

---

1. SQL 개요

SQL(Structured Query Language)은 1970년대 IBM에서 개발되었으며, 원래 이름은 SEQUEL이었다. ANSI와 ISO에 의해 표준화되었다 (SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003 등).

SQL의 주요 구성요소:

구성요소	역할
DML	데이터 조회, 삽입, 삭제, 수정
DDL	테이블, 뷰, 인덱스 등의 정의
무결성 제약	데이터의 정확성 보장 규칙
뷰 정의	가상 테이블 정의
트랜잭션 제어	트랜잭션의 시작과 종료
Embedded/Dynamic SQL	범용 프로그래밍 언어(C, Java 등)에 SQL을 내장하는 방법
Authorization	릴레이션과 뷰에 대한 읽기/삽입/삭제/수정 접근 권한 명시

---

2. DDL (Data Definition Language)

DDL은 각 릴레이션에 대해 다음 정보를 명시한다:

• 각 릴레이션의 스키마 (속성 이름과 순서)
• 각 속성에 연결된 값의 타입 (도메인)
• 무결성 제약조건 (primary key, foreign key, not null 등)
• 각 릴레이션에 유지할 인덱스 집합
• 보안 및 권한 정보
• 디스크 상의 물리적 저장 구조

도메인 타입

타입	설명
char(n)	고정 길이 문자열 (길이 n)
varchar(n)	가변 길이 문자열 (최대 길이 n)
int	정수
smallint	작은 정수
numeric(p, d)	고정 소수점 (전체 p자리, 소수점 이하 d자리)
real, double precision	부동 소수점
float(n)	최소 n자리 정밀도의 부동 소수점

테이블 생성

create table department (
    dept_name varchar(20),
    building  varchar(15),
    budget    numeric(12, 2),
    primary key (dept_name)
);
 
create table course (
    course_id varchar(8),
    title     varchar(50),
    dept_name varchar(20),
    credits   numeric(2, 0),
    primary key (course_id),
    foreign key (dept_name) references department
);
 
create table instructor (
    ID        varchar(5),
    name      varchar(20) not null,
    dept_name varchar(20),
    salary    numeric(8, 2),
    primary key (ID),
    foreign key (dept_name) references department
);

• primary key: 기본키 선언 (자동으로 not null + unique)
• foreign key ... references ...: 외래키 선언 (참조 무결성)
• not null: null 값 금지
• SQL은 무결성 제약을 위반하는 업데이트를 방지한다 (예: 존재하지 않는 학과를 참조하는 교수 삽입 거부).

추가 테이블 생성 예시

create table student (
    ID        varchar(5),
    name      varchar(20) not null,
    dept_name varchar(20),
    tot_cred  numeric(3, 0),
    primary key (ID),
    foreign key (dept_name) references department
);
 
create table takes (
    ID        varchar(5),
    course_id varchar(8),
    sec_id    varchar(8),
    semester  varchar(6),
    year      numeric(4, 0),
    grade     varchar(2),
    primary key (ID, course_id, sec_id, semester, year),
    foreign key (ID) references student,
    foreign key (course_id, sec_id, semester, year) references section
);

takes는 복합 기본키(5개 속성)와 복합 외래키(course_id, sec_id, semester, year → section)를 가진다.

기타 DDL 명령:

• drop table r — 릴레이션 r과 스키마를 모두 삭제
• delete from r — 튜플만 모두 삭제 (스키마 유지)
• alter table r add A D — 속성 A(도메인 D) 추가. 기존 튜플의 새 속성 값은 null로 설정된다.
• alter table r drop A — 속성 A 제거. 많은 데이터베이스 시스템에서 지원하지 않는다.

---

3. 기본 쿼리 구조

SQL 이름은 대소문자를 구분하지 않는다 (case insensitive): Name ≡ NAME ≡ name

SQL 쿼리의 기본 형태:

select A₁, A₂, ..., Aₙ
from r₁, r₂, ..., rₘ
where P;

• select = 관계 대수의 투영(Π)
• from = 관계 대수의 카티션 곱(×)
• where = 관계 대수의 선택(σ)

distinct와 all

select distinct dept_name from instructor;  -- 중복 제거
select all dept_name from instructor;       -- 중복 허용 (기본값)

SQL에서 릴레이션은 기본적으로 **다중집합(multiset)**이다. 중복 튜플이 허용된다.

리터럴 SELECT

select '437';                  -- from 없이 단일 행, 단일 컬럼 '437' 반환
select '437' as FOO;           -- 컬럼 이름을 FOO로 지정
select 'A' from instructor;   -- instructor의 튜플 수(N)만큼 'A'가 반복

산술 연산과 별명

select ID, name, salary/12 as monthly_salary
from instructor;

• select 절에는 +, -, *, / 산술 연산을 상수나 속성에 적용할 수 있다.
• as를 사용하여 속성이나 릴레이션에 별명(alias)을 부여할 수 있다.
• select *는 모든 속성을 선택한다.

Rename 연산 (as를 이용한 자기 조인)

as 절은 릴레이션과 속성의 이름을 변경한다: old-name as new-name

자기 조인(Self-Join): 같은 테이블을 두 번 참조해야 할 때, 각각 다른 별명을 부여하여 구분한다.

-- Comp. Sci. 학과의 어떤 교수보다 급여가 높은 모든 교수의 이름
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci.';

• instructor as T와 instructor as S는 같은 instructor 테이블의 두 복사본이다.
• T의 각 행을 S의 각 행과 비교하여, T의 급여가 S(Comp. Sci. 소속)보다 높은 경우를 찾는다.
• 키워드 as는 선택적이다: instructor as T ≡ instructor T

where 절

select name from instructor
where dept_name = 'Comp. Sci.' and salary > 70000;

비교 연산자 <, <=, >, >=, =, <>와 논리 연산자 and, or, not을 사용한다.

from 절의 여러 릴레이션

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;

from에 여러 릴레이션을 나열하면 카티션 곱을 수행하고, where에서 조인 조건을 지정한다.

참고: from 절의 카티션 곱 자체는 직접적으로 유용하지 않다 (대부분 무의미한 조합). 그러나 where 조건과 결합하면 의미 있는 조인 결과를 얻을 수 있다. 예를 들어 instructor × teaches의 결과에서 instructor.ID = teaches.ID인 행만 선택하면 “누가 어떤 과목을 가르치는지”를 알 수 있다.

---

4. 문자열 연산

• 문자열은 작은따옴표로 감싼다: 'Hello'
• 작은따옴표 자체는 ''로 이스케이프한다

like 패턴 매칭

패턴	의미
%	임의의 문자열(빈 문자열 포함)
_	정확히 한 문자

select name from instructor
where name like '%dar%';    -- 이름에 'dar' 포함
 
where dept_name like '100\%' escape '\';  -- 리터럴 % 매칭

• 패턴은 대소문자를 구분한다 (case sensitive)

패턴 매칭 예시

패턴	매칭 대상
'Intro%'	”Intro”로 시작하는 모든 문자열
'%Comp%'	”Comp”를 포함하는 모든 문자열
'___'	정확히 3글자인 문자열
'___%'	최소 3글자 이상인 문자열

기타 문자열 함수

SQL은 다양한 문자열 연산을 지원한다:

연산	설명
||	문자열 연결 (concatenation)
upper(s)	대문자로 변환
lower(s)	소문자로 변환
length(s)	문자열 길이
substring(s, start, len)	부분 문자열 추출

---

5. 정렬과 범위

order by

select distinct name from instructor
order by name;                    -- 오름차순 (기본)
 
select * from instructor
order by salary desc, name asc;   -- 급여 내림차순, 이름 오름차순

between

select name from instructor
where salary between 90000 and 100000;
-- 동치: salary >= 90000 and salary <= 100000

튜플 비교 (Tuple Comparison)

여러 속성을 동시에 비교할 때 튜플 형태로 묶어서 비교할 수 있다.

-- Biology 학과에서 강의를 하는 교수의 이름과 과목 ID
select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

• (instructor.ID, dept_name) = (teaches.ID, 'Biology')는 instructor.ID = teaches.ID and dept_name = 'Biology'와 동치이다.

---

6. 집합 연산

SQL의 집합 연산은 자동으로 중복을 제거한다. 중복을 유지하려면 all을 붙인다.

Tip

set연산이 tuple 단위로 적용되는 거니까, intersect 같은 연산이 필요함.
하나의 tuple을 검사할 때, 만약 조건이 and로 where 내부에 정의되어 있었다면 하나의 컬럼에 여러 허용 값을 주는 filter가 불가능하므로.

-- 2017 Fall 또는 2018 Spring에 개설된 과목 (중복 제거)
(select course_id from section where semester='Fall' and year=2017)
union
(select course_id from section where semester='Spring' and year=2018);
 
-- 중복 유지
... union all ...

연산	중복 제거	중복 유지
합집합	union	union all
교집합	intersect	intersect all
차집합	except	except all

all 버전에서의 중복 횟수 규칙:

• union all: r에서 m번 + s에서 n번 → 결과에 m+n번
• intersect all: min(m, n)번
• except all: max(0, m−n)번

집합 연산자가 굳이 필요한가? nested subquery로 다 되지 않나?

맞다 — 대부분은 대체 가능하다. INTERSECT는 IN 서브쿼리로, EXCEPT는 NOT IN으로 동일한 결과를 얻을 수 있고, 실제로 Section 9에서 이 방식으로 같은 쿼리를 재작성하는 예시가 나온다.

그러나 완전한 대체는 불가능하다:

1. UNION은 대체 불가: 서로 다른 테이블/조건의 결과를 합치는 것은 단일 SELECT-FROM-WHERE로 불가능. 같은 테이블이면 OR로 합칠 수 있지만, FROM 절이 다르면 방법이 없다.
2. ALL 변형의 multiset semantics: INTERSECT ALL의 min(m,n)이나 EXCEPT ALL의 max(0,m−n) 중복 횟수를 서브쿼리로 정확히 구현하려면 window function 등 복잡한 로직이 필요하다.
3. 가독성: 집합 연산은 의도를 선언적으로 명확히 전달한다.
4. 최적화: 옵티마이저가 집합 연산에 특화된 실행 계획(hash-based set operation 등)을 사용할 수 있다.

---

7. Null 값

null은 “알 수 없는 값” 또는 “존재하지 않는 값”을 나타낸다.

null과의 연산

• null을 포함하는 산술 연산의 결과는 null이다: 5 + null = null
• null과의 비교 결과는 unknown이다: 5 < null = unknown

3값 논리 (Three-Valued Logic)

연산	결과
true and unknown	unknown
false and unknown	false
unknown and unknown	unknown
true or unknown	true
false or unknown	unknown
not unknown	unknown

Question

TRUE AND NULL?

• where 절의 조건이 unknown으로 평가되면, 해당 튜플은 결과에 포함되지 않는다 (false와 동일 취급).
• null 여부 확인: is null, is not null

select name from instructor where salary is null;

---

8. 집계 함수 (Aggregate Functions)

함수	설명
avg(col)	평균
min(col)	최소
max(col)	최대
sum(col)	합계
count(col)	개수 (null 제외)
count(*)	튜플 수 (null 포함)

select avg(salary) from instructor where dept_name = 'Comp. Sci.';
select count(distinct ID) from teaches where semester='Spring' and year=2018;

group by

그룹별로 집계를 수행한다.

Warning

select에 나오는 비집계 속성은 반드시 group by에 포함되어야 한다.

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;

having

그룹에 대한 조건을 지정한다 (집계 함수 조건에 사용).

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

실행 순서: from → where → group by → having → select → order by

• where는 그룹 형성 전에 개별 튜플에 적용된다.
• having은 그룹 형성 후에 각 그룹에 적용된다.

---

9. 중첩 서브쿼리 (Nested Subqueries)

서브쿼리란 다른 쿼리 안에 중첩된 select-from-where 표현이다. 서브쿼리는 SQL 쿼리의 세 곳에 나타날 수 있다:

select A₁, A₂, ..., Aₙ
from r₁, r₂, ..., rₘ
where P;

위치	서브쿼리 형태
from 절	rᵢ를 임의의 유효한 서브쿼리로 대체 가능
where 절	P를 B <operation> (subquery) 형태로 대체 가능
select 절	Aᵢ를 단일 값을 생성하는 서브쿼리로 대체 가능 (스칼라 서브쿼리)

9.1 집합 멤버십: in / not in

-- 2017 Fall과 2018 Spring 모두에 개설된 과목
select distinct course_id from section
where semester = 'Fall' and year = 2017
  and course_id in (select course_id from section
                    where semester = 'Spring' and year = 2018);
 
-- 2017 Fall에는 개설되었지만 2018 Spring에는 아닌 과목
select distinct course_id from section
where semester = 'Fall' and year = 2017
  and course_id not in (select course_id from section
                        where semester = 'Spring' and year = 2018);

in은 열거된 값에도 사용할 수 있다: where name in ('Mozart', 'Einstein')

not in과 열거 값

-- Mozart도 Einstein도 아닌 교수
select distinct name from instructor
where name not in ('Mozart', 'Einstein');

튜플 수준의 in

여러 속성을 묶어서 in 검사를 수행할 수 있다.

-- 교수 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);

9.2 집합 비교: some / all

some: 서브쿼리 결과 중 하나라도 조건을 만족하면 true

형식 정의: F <comp> some r ⟺ ∃ t ∈ r such that (F <comp> t) (여기서 <comp>는 <, ≤, >, ≥, =, ≠)

-- Biology 학과의 어떤 교수보다 급여가 높은 교수
select name from instructor
where salary > some (select salary from instructor
                     where dept_name = 'Biology');

some의 동작 예시

표현	결과	이유
5 < some {0, 5, 6}	true	5 < 6
5 < some {0, 5}	false	0보다 크고, 5보다 작지 않음
5 = some {0, 5}	true	5 = 5
5 ≠ some {0, 5}	true	5 ≠ 0

• = some ≡ in
• ≠ some ≢ not in (주의! — 집합에 하나라도 다른 값이 있으면 true)

---

all: 서브쿼리 결과 모두에 대해 조건을 만족하면 true

형식 정의: F <comp> all r ⟺ ∀ t ∈ r (F <comp> t)

-- Biology 학과의 모든 교수보다 급여가 높은 교수
select name from instructor
where salary > all (select salary from instructor
                    where dept_name = 'Biology');

all의 동작 예시

표현	결과	이유
5 < all {0, 5, 6}	false	5 < 0이 아님
5 < all {6, 10}	true	5 < 6이고 5 < 10
5 = all {4, 5}	false	5 ≠ 4
5 ≠ all {4, 6}	true	5 ≠ 4이고 5 ≠ 6

• ≠ all ≡ not in
• = all ≢ in (주의! — 집합의 모든 값이 같아야 true)

9.3 존재 여부: exists / not exists

형식 정의:

• exists r ⟺ r ≠ ∅ (결과가 비어 있지 않으면 true)
• not exists r ⟺ r = ∅ (결과가 비어 있으면 true)

exists: 서브쿼리 결과가 비어 있지 않으면 true

-- 2017 Fall과 2018 Spring 모두에 개설된 과목 (exists 사용)
select course_id from section as S
where semester = 'Fall' and year = 2017
  and exists (select * from section as T
              where semester = 'Spring' and year = 2018
                and S.course_id = T.course_id);

• 상관 서브쿼리 (Correlated Subquery): 외부 쿼리의 변수(S)를 내부 쿼리가 참조하는 서브쿼리.
• 상관 이름 (Correlation Name): 외부 쿼리에서 정의한 별명(S).

not exists: 서브쿼리 결과가 비어 있으면 true. “모든 것을 만족” 조건에 활용한다.

-- Biology 학과의 모든 과목을 수강한 학생
select distinct S.ID, S.name
from student as S
where not exists (
    (select course_id from course where dept_name = 'Biology')
    except
    (select T.course_id from takes as T where S.ID = T.ID)
);

핵심 논리: X − Y = ∅ ⟺ X ⊆ Y (Biology 과목 전체 ⊆ 학생이 수강한 과목)

참고: 이 쿼리는 = all이나 그 변형으로는 작성할 수 없다. not exists ... except ... 패턴이 “모든 것을 만족”하는 조건을 표현하는 유일한 방법이다.

9.4 중복 검사: unique

서브쿼리 결과에 중복 튜플이 없으면 true.

-- 2017년에 최대 1번 개설된 과목
select T.course_id from course as T
where unique (select R.course_id from section as R
              where T.course_id = R.course_id and R.year = 2017);

---

10. From 절 서브쿼리

from 절에 서브쿼리를 사용하여 임시 릴레이션을 생성할 수 있다.

-- 평균 급여가 42,000 초과인 학과 (having 없이)
select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
      from instructor
      group by dept_name) as dept_avg
where avg_salary > 42000;

from 절 서브쿼리를 사용하면 having 절 없이도 집계 조건을 표현할 수 있다.

서브쿼리에 명시적 컬럼 이름 부여

서브쿼리 결과의 릴레이션 이름과 컬럼 이름을 동시에 지정할 수 있다:

-- 위와 동일한 쿼리의 다른 표현
select dept_name, avg_salary
from (select dept_name, avg(salary)
      from instructor
      group by dept_name)
      as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;

• as dept_avg (dept_name, avg_salary): 서브쿼리 결과에 릴레이션 이름(dept_avg)과 각 컬럼 이름을 명시적으로 부여한다.

---

11. With 절

with는 쿼리 내에서만 사용할 임시 릴레이션을 정의한다.

-- 최대 예산을 가진 학과
with max_budget(value) as
    (select max(budget) from department)
select department.name
from department, max_budget
where department.budget = max_budget.value;

복수의 임시 릴레이션도 정의할 수 있다:

-- 총 급여가 전체 학과 평균 총 급여를 초과하는 학과
with dept_total(dept_name, value) as
        (select dept_name, sum(salary)
         from instructor group by dept_name),
     dept_total_avg(value) as
        (select avg(value) from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;

---

12. 스칼라 서브쿼리 (Scalar Subquery)

단일 값을 반환하는 서브쿼리. 단일 값이 기대되는 곳 어디서든 사용 가능하다.

-- 각 학과와 소속 교수 수
select dept_name,
       (select count(*) from instructor
        where department.dept_name = instructor.dept_name)
       as num_instructors
from department;

서브쿼리가 두 개 이상의 튜플을 반환하면 런타임 에러가 발생한다.

---

13. 데이터베이스 수정

Delete (삭제)

delete from instructor;                              -- 모든 튜플 삭제
delete from instructor where dept_name = 'Finance';  -- 조건부 삭제
 
-- Watson 건물에 있는 학과의 교수 삭제
delete from instructor
where dept_name in (select dept_name from department
                    where building = 'Watson');
 
-- 평균 급여 이하 교수 삭제
delete from instructor
where salary < (select avg(salary) from instructor);

평균 급여 삭제의 경우, SQL은 먼저 avg를 계산하고 삭제 대상을 결정한 후 삭제를 수행한다 (삭제 중 avg 재계산 없음).

Insert (삽입)

insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
 
-- 속성을 명시적으로 지정
insert into course (course_id, title, dept_name, credits)
    values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
 
-- null 값 삽입
insert into student values ('3003', 'Green', 'Finance', null);
 
-- select 결과를 삽입
insert into instructor
    select ID, name, dept_name, 18000
    from student
    where dept_name = 'Music' and total_cred > 144;

select from where는 결과가 삽입되기 전에 완전히 평가된다. 따라서 insert into table1 select * from table1 같은 쿼리도 안전하다.

Update (갱신)

-- 모든 교수 급여 5% 인상
update instructor set salary = salary * 1.05;
 
-- 조건부 인상
update instructor set salary = salary * 1.05 where salary < 70000;
 
-- 평균 미만 교수만 인상
update instructor
set salary = salary * 1.05
where salary < (select avg(salary) from instructor);

case 문을 이용한 조건부 업데이트

-- 급여 100,000 이하는 5% 인상, 초과는 3% 인상
update instructor
set salary = case
                when salary <= 100000 then salary * 1.05
                else salary * 1.03
             end;

만약 두 개의 update 문으로 나눠서 실행하면 순서에 따라 결과가 달라지는 문제가 발생한다:

-- 잘못된 순서: salary가 100,000인 교수가 먼저 5% 인상 → 105,000이 되어
-- 두 번째 update에서 3% 인상 대상에서 빠짐
update instructor set salary = salary * 1.03 where salary > 100000;
update instructor set salary = salary * 1.05 where salary <= 100000;

case를 사용하면 이 순서 문제를 방지할 수 있다.

스칼라 서브쿼리를 이용한 업데이트

-- 모든 학생의 tot_cred 재계산
update student S
set tot_cred = (select sum(credits)
                from takes, course
                where takes.course_id = course.course_id
                  and S.ID = takes.ID
                  and takes.grade <> 'F'
                  and takes.grade is not null);

수강 이력이 없는 학생은 tot_cred가 null이 된다. 이를 0으로 처리하려면:

case
    when sum(credits) is not null then sum(credits)
    else 0
end

---

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