1. SQL 질의어 개요

1.1 SQL 역사

1970년대 초: IBM이 Sequel이라는 SQL 초기 버전 개발
SQL (Structured Query Language)로 명칭 변경
1986년: ANSI와 ISO가 SQL-86 표준 발표
이후 SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016 표준 발표

1.2 SQL 언어 구성

데이터 정의 언어 (DDL): 릴레이션 스키마 정의, 삭제, 수정
데이터 조작 언어 (DML): 질의, 삽입, 삭제, 수정
무결성 제약조건 (Integrity): 저장 데이터가 만족해야 할 조건 명시
뷰 정의 (View definition): 뷰 생성 명령어 포함
트랜잭션 제어 (Transaction control): 트랜잭션 시작과 끝 명시
내장 SQL (Embedded SQL), 동적 SQL (Dynamic SQL): C, C++, Java 등에서 사용
권한 부여 (Authorization): 릴레이션과 뷰 접근 권한 제어

2. SQL 데이터 정의

2.1 데이터 정의 언어 (DDL)

릴레이션 정의, 속성 타입 지정, 무결성 제약조건 명시, 인덱스 정의, 보안 및 권한 부여 정보, 물리적 저장 구조 설정

2.2 기본 데이터 타입

char(n): 고정 길이 문자열
varchar(n): 가변 길이 문자열
int, integer: 정수
smallint: 작은 정수
numeric(p, d): 고정 소수점 수 (p는 총 자릿수, d는 소수점 이하 자릿수)
real: 부동 소수점 수 (정밀도 시스템에 따라 다름)
double precision: 배정밀도 부동 소수점 수
float(n): 최소 n개의 숫자로 표현 가능한 부동 소수점 수
null 값: 값이 존재하지 않거나 알 수 없는 경우를 표현

2.3 스키마 정의 예시

department 릴레이션 생성:

sql

create table department (
  dept_name varchar(20),
  building varchar(15),
  budget numeric(12,2),
  primary key (dept_name)
);

일반 스키마 구문:

sql

create table r (
  A1 D1,
  A2 D2,
  ...,
  An Dn,
  (integrity-constraint1),
  ...,
  (integrity-constraintk)
);

2.4 무결성 제약조건

primary key (Aj1, ..., Ajn): 주 키 지정, 널 값 불허, 유일성 보장
foreign key (Ak1, ..., Akn) references s: 외래 키 지정, 상대 릴레이션 s의 주 키 참조
not null: 널 값 금지

3. SQL 질의의 기본 구조

3.1 단일 릴레이션 질의

기본 구조:

sql

select 속성 from 릴레이션 where 조건;

모든 교수의 이름:

sql

select name from instructor;

모든 교수의 학과 이름 (중복 제거):

sql

select distinct dept_name from instructor;

급여 인상 표현 (salary * 1.1):

sql

select ID, name, dept_name, salary * 1.1 from instructor;

컴퓨터 과학과 급여 70,000 초과 교수:

sql

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

3.2 복수 릴레이션 질의

교수 이름, 학과 이름, 건물 이름:

sql

select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;

과목을 가르친 교수의 이름과 과목 ID:

sql

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

컴퓨터 과학과 교수의 이름과 과목 ID:

sql

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID and dept_name = 'Comp. Sci.';

4. 부가적인 기본 연산

4.1 재명명 연산

속성 이름 변경:

sql

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

릴레이션 이름 변경 (별칭):

sql

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

자기 자신과의 비교 (급여가 생물학과 교수보다 높은 교수):

sql

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

4.2 문자열 연산

패턴 매칭 (like):

sql

select dept_name
from department
where building like '%Watson%';

패턴 특수문자: % (임의 문자열), _ (한 문자)

4.3 정렬 (order by)

물리학과 교수 이름 정렬:

sql

select name
from instructor
where dept_name = 'Physics'
order by name;

급여 내림차순, 이름 오름차순 정렬:

sql

select *
from instructor
order by salary desc, name asc;

4.4 범위 조건 (between)

급여가 90,000에서 100,000 사이 교수 이름:

sql

select name
from instructor
where salary between 90000 and 100000;

5. 집합 연산

5.1 합집합 (union)

sql

(select course_id from section where semester = 'Fall' and year = 2017)
union
(select course_id from section where semester = 'Spring' and year = 2018);

5.2 교집합 (intersect)

sql

(select course_id from section where semester = 'Fall' and year = 2017)
intersect
(select course_id from section where semester = 'Spring' and year = 2018);

5.3 차집합 (except)

sql

(select course_id from section where semester = 'Fall' and year = 2017)
except
(select course_id from section where semester = 'Spring' and year = 2018);

6. 널 값 처리

널 값 비교 (is null, is not null):

sql

select name from instructor where salary is null;

널 값이 포함된 비교 → 결과는 unknown

7. 집계 함수

평균 급여:

sql

select avg(salary) from instructor where dept_name = 'Comp. Sci.';

과목을 가르친 교수 수 (중복 제거):

sql

select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2018;

각 학과 평균 급여:

sql

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

평균 급여가 42,000 이상인 학과:

sql

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

8. 데이터베이스 변경

8.1 삭제 (delete)

전체 삭제:

sql

delete from instructor;

조건부 삭제:

sql

delete from instructor where dept_name = 'Finance';

서브쿼리 사용 삭제:

sql

delete from instructor
where salary < (select avg(salary) from instructor);

8.2 삽입 (insert)

튜플 삽입:

sql

insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

속성 지정 삽입:

sql

insert into course (course_id, title, dept_name, credits)
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

서브쿼리 결과 삽입:

sql

insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and tot_cred > 144;

8.3 갱신 (update)

모든 교수 급여 5% 인상:

sql

update instructor set salary = salary * 1.05;

조건부 급여 인상:

sql

update instructor
set salary = salary * 1.05
where salary < (select avg(salary) from instructor);

case 문을 통한 급여 인상:

sql

update instructor
set salary = case
  when salary < 100000 then salary * 1.05
  else salary * 1.03
end;