Introduction to SQL

SQL 쿼리 언어

• DML, 무결성, 뷰 정의, 트랜젝션 컨트롤, 임베디드 SQL, 동적 SQL, 인증
• SQL DDL(데이터 정의 언어, Data Definition Language): 릴레이션 관련 정보를 구체적으로 정함

  릴레이션 스키마, 어트리뷰트 타입, 무결성 제약조건, 릴레이션이 가지는 인덱스 집합, 안전성 및 인증 정보, 물리적 스토리지 구조 정보 등을 릴레이션 정보를 구체적으로 정하는 언어

SQL 도메인 타입

• char(n): 고정된 크기의 캐릭터 문자열
• varchar(n): 최대 n 길이까지의 캐릭터 문자열
• int: 정수
• smallint: int보다 더 작은 범위의 정수
• numeric(p,d): 고정된 포인트 넘버로 정확한(precise) 디지트의 개수가 p이고 그 중 소수점 이후의 수(decimal point)가 d라는 뜻이다. 예를 들어 numeric(3,1)은 44.5를 허용한다.
• real, double precision: 실수
• float(n): 최소 n개의 디지트로 표현되는 실수

  사실상 varchar, int, numeric 등을 가장 자주 사용하는 듯하다.

SQL 쿼리 명령어

Create

• 테이블을 생성할 수 있는 명렁어로 create table로 호출한다.
• 릴레이션의 이름, 어트리뷰트의 이름 및 특정 어트리뷰트의 도메인에 대한 데이터 타입을 명시

create table instructor(ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2));

무결성 제약 조건

• 테이블을 생성할 때 본 스키마의 프라이머리 키, 외래키, 널 값이 아님 등을 명시할 수 있다. 특정 어트리뷰트의 데이터 도메인 역시 명시 가능.
• 해당 무결성 제약조건을 침범하는 업데이트는 SQL 언어 자체에서 이루어지지 않는다.

create table instructor(ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2), primary key (ID), foreign key (dept_name) references department);

테이블 튜플 명령어

Insert

• 특정 튜플을 특정 테이블에 삽입하는 명령어

insert into instructor values('10211', 'Smith', 'Biology', 66000);

특정 튜플을 values 뒤에 괄호로 묶은 튜플로 넣어주는데, 이때 무결성 제약 조건이 존재한다면 침범하지 않는 새로운 값만 삽입 가능하다.

Delete

• 튜플을 테이블에서 삭제하는 명령어

delete from student;

• where 조건문을 통해 특정 조건만을 만족하는 튜플만을 없앨 수 있다.

Drop Table

drop table r;

• 테이블을 전체 DB에서 드롭시킨다.

Alter Table

alter table r add A D;
alter talbe r drop A;

• 특정 테이블의 어트리뷰트를 추가하거나 삭제한다.
• 도메인 D를 가진 새로운 어트리뷰트 A가 테이블 r에 추가될 때 기존에 존재하던 다른 튜플들(즉 어트리뷰트 A가 없는 튜플들)의 A는 널 값으로 채워진 채 갱신된다.
• 특정 어트리뷰트를 테이블에서 삭제하는 연산은 지원하지 않을 수 있다.

SQL 쿼리문

select A1, A2, ..., An from r1, r2, ..., rm where P;

• 전형적인 SQL 쿼리문으로 특정 릴레이션에서 제약조건을 만족하는 새로운 릴레이션을 생성하는 데, 이중 선택한 어트리뷰트를 표시하라는 명령어다. 즉 위 SQL 쿼리문으로 만들어지는 결과값 또한 하나의 릴레이션이다.

Select 절

• 생성되는 릴레이션의 모든 어트리뷰트 가운데 선택한 어트리뷰트를 골라 프로젝트 연산
• SQL의 어트리뷰트 이름은 소문자와 대문자를 가리지 않는다.

select distinct dept_name from instructor;
select all dept_name from isntructor;
select * from instructor;

• 중복을 제거하고 싶을 때에는 distinct를 붙이고, 그렇지 않으면 all을 붙인다. all이 디폴트다.
• 모든 어트리뷰트를 얻고 싶을 때에는 * 애스터리스크를 사용한다.

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

• 특정 어트리뷰트 도메인 자체에 사칙연산을 걸 수 있는데, 셀렉트 절 자체에서 쓰면 된다.
• 기존 도메인을 연산해서 만들어진 어트리뷰트에 새로운 이름을 붙일 수 있다.

From 절

• 쿼리 연산을 적용할 릴레이션을 명시하는 주는 절로 릴레이셔널 알제브라의 데카르트 곱 연산과 상동

select * from instructor, teaches;

• 일반적으로 where 절과 함께 사용되는 게 효율적이다.

Where 절

• 결과 릴레이션이 만족해야 하는 제약조건을 명시하는 절이다.
• 논리 부호(AND, OR, NOT) 및 기타 연산(크기 비교 등)을 통해 조건을 명시할 수 있다.

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

• E.g.) 부서가 컴퓨터과학이고 연봉이 칠만 달러 이상인 강사의 이름만을 보고 싶을 때 사용하는 명령어다.

Rename 연산

• 기존 이름을 새로운 이름으로 변경할 수 있는 연산

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

• 특정 릴레이션 내에서 비교를 할 때 유용하게 사용된다.

String 연산

• like 연산을 통해 찾으려는 문자열이 어떤 속성을 띠고 있는지 명시할 수 있다.
• %: 퍼센트 = 서브스트링. 캐릭터가 아니라 서브스트링이기 때문에 문자 개수는 상관없다.
• _: 언터스코어 = 캐릭터. 서브스트링이 아니라 캐릭터이기 때문에 정확한 개수가 중요하다.

select name from instructor where name like '%dar%';

• 찾으려는 문자열은 그 단어 내 dar이 서브스트링으로 존재하는 단어다.
• E.g.) Intro% - 시작 문자열이 Intro여야 하는 모든 단어가 리턴된다. %Comp% - 단어 내에 Comp가 서브스트링으로 들어있기만 하면 된다. ___ - 캐릭터 수가 정확히 3개인 단어가 리턴된다. ___% - 캐릭터 수가 적어도 3개인 단어가 리턴된다.

튜플 정렬 키워드

• 리턴한 릴레이션의 튜플을 조건에 따라 정렬할 수 있다.

select distinct name from instructor order by name;
select distinct name from instructor order by name desc;

• 특정 어트리뷰트 기준으로 오름차순, 내림차순 정렬을 할 수 있다. 오름차순 asc가 디폴트

Where 절 술어

• between이라는 비교 연산자 사용 가능: 부등호를 두 개 사용해도 된다.

select name from instructor where salary between 90000 and 100000;

Set 연산

• 합집합은 union, 교집합은 intersect, 차집합은 except 연산을 사용한다.
• 집합 연산이기 때문에 디폴트는 중복을 제거하는데, all 키워드를 통해 중복 튜플을 계속해서 가지고 있을 수 있다.

널 값

• null: 특정 어트리뷰트 값이 미정이거나 존재하지 않는 상태
• null이 들어간 산수 연산의 결과값은 언제나 null이다.
• null이 들어간 비교 연산의 결과값은 언제나 unknown이다.
• is null이라는 프리디케이트를 통해 체크할 수 있다. 이때 결과값은 true 또는 false이다. 즉 확인 가능하다는 데 주의.

select name from isntructor where salary is null;

• where절에서 null과 함께 특정 불리언 연산이 사용되면 논리 연산에 따라 결과값이 달라질 수 있다. (1). false가 and 연산에 사용된다면 언제나 false (2). true가 or 연산에 사용된다면 언제나 true

Aggregate Functions

• 릴레이션의 특정 어트리뷰트 값의 연산 결과를 리턴
• avg, min, max, sum, count를 사용할 수 있다.

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

1. 컴퓨터학과의 강사 평균 연봉을 쿼리
2. 2018년 봄학기 강의를 가르쳤던 강사의 수. 즉 중복이 없어야 함
3. 등록되어 있는 강의의 개수

• Group By를 통해 특정 어트리뷰트 도메인 값에 속한 튜플들을 따로 모을 수 있다.

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

• 위 쿼리문은 강사의 부서별 평균 연봉을 리턴한다.
• group by에 대한 조건문으로 having을 줄 수 있다.

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

having 절은 그룹이 만들어진 뒤에 선언되어야 하고, where 절은 그룹이 만들어지기 전에 선언되어야 한다는 데 주의하자.

안긴 서브쿼리 Nested Subqueries

• 서브쿼리는 다른 쿼리 문안에 또 다시 쿼리문이 사용되는 경우다.

select A1, A2, ..., An from r1, r2, ..., rm where P;

• 서브쿼리 또한 하나의 쿼리문이기 때문에 위 일반적인 쿼리문으로 대체 가능하다.

Set Membership

• 특정 어트리뷰트가 특정 어트리뷰트 안에 있는지 여부를 판단할 수 있다.

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

• 2018년 봄 학기 섹션의 모든 코스 아이디에 2017년 가을 학기 코스 아이디가 들어간 경우의 코스 아이디를 리턴한다. 즉 2017년 가을, 2018년 봄에 중복으로 들어 있는 모든 코스 아이디를 리턴하는 쿼리문이다.

Set Comparison

Some 절

• 특정 릴레이션 중 적어도 하나의 튜플과 비교할 때 사용하는 절이다.
• E.g.) 적어도 한 명의 생물학 강사보다 연봉이 높은 모든 강사의 이름을 찾는 쿼리문을 쓰기

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

• some의 의미가 '적어도 하나'임에 주의해서 비교 연산을 사용하자.

All 절

• 특정 릴레이션 중 모든 튜플과 비교해야 할 때 사용하는 절이다.
• E.g.) 모든 생물학 강사보다 연봉이 높은 모든 강사의 이름을 찾는 쿼리문을 쓰기

select name from instructor wher salary > all (select salary from instructor where dept_name = 'Biology');

• all의 의미가 '모든'임에 주의하자.

Empty Relation

• 집합의 공집합 개념과 비교해서 존재한다면 true를, 그렇지 않다면 false를 리턴한다.

Exists 절

• E.g.) 2017년 가을, 2018년 봄 학기 모두 열린 강의들을 모두 찾는 쿼리문 쓰기

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

• 바깥 쿼리의 S는 corrleation name, 안쪽 쿼리는 correlated subquery라고 불린다. 안쪽 쿼리에서 바깥 쿼리에서 선언된 이름 T를 where 절의 조건으로 줄 수 있다는 데 주의하자.

Not Exists 절

• E.g.) 생물학 부서가 제공하는 모든 코스를 들은 모든 학생들을 찾는 쿼리문 쓰기

select distinct S.ID, S.name
fron 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));

• 첫 번째 nested 쿼리문 $X$: 생물학 부서가 제공하는 모든 강의 리턴
• 두 번째 nested 쿼리문 $Y$: 특정 학생이 들은 모든 코스를 리턴
• $X - Y = \empty \Leftrightarrow X \subseteq Y$: $X$에서 $Y$를 차집합 연산한 값이 공집합인 튜플을 리턴하기 때문에 $X \subseteq Y$, 즉 모든 생물학 부서 제공 강의를 들은 학생이 리턴된다.

중복 튜플 제거

• unique 키워드를 사용해 특정 서브 쿼리가 중복 튜플을 가지고 있는지 확인할 수 있다.

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

• E.g.) 2017년 최대 한 번 제공되었던 모든 강의를 찾기

From 절 안의 서브쿼리

• SQl 쿼리문이 리턴하는 결과값은 하나의 릴레이션이기 때문에 SQL의 From 절에 사용될 수 있음

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

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;

With 절

• with 절이 일어난 쿼리에서만 유효한 임시 릴레이션을 만들 수 있다.

with max_budget (value) as
	(select max(budge)
    from deparment)
select department.name
from department, max_budget
where department.budget = max_budget.value;

• E.g.) 부서 예산의 최댓값을 max_budget으로 먼저 계산한다. 그리고 예산이 최댓값과 같은 부서의 이름을 리턴한다.

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;

• E.g.) dept_total이라는 부서명 + 값과 dept_total_avg이라는 값을 with 절로 선언한 뒤, 평균값이 넘는 부서의 이름만 리턴하는 쿼리문이다.

스칼라 서브쿼리

• 리턴되는 값이 하나일 때 적용 가능

select dept_name,
		( select count(*)
        	from instructor
            where department.dept_name = instructor.dept_name)
            as num_instructors
from department;

• 스칼라 서브쿼리 연산은 결과 튜플이 1개보다 많다면 런타임 에러가 난다.

데이터베이스 수정

튜플 삭제

• 모든 튜플 삭제

delete from instructor;

• 특정 조건을 만족하는 튜플만 삭제

delete from instructor
where dept_name = 'Finance';
delete from instructor
where dept_name in (select dept_name
					from department
                    where buidling = 'Watson');

1. 재무부 강사만을 삭제하는 명령어
2. 왓슨 건물을 가진 부서의 모든 강사만을 삭제하는 명령어

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

• 현재 평균 연봉보다 적게 받는 강사 튜플은 삭제하는 명령어

  평균 등 aggregate 연산은 현 시점에 존재하는 데이터에 영향을 받는데, delete 연산으로 인해 값이 바뀔 수 있다면 에러. SQL은 먼저 연산을 시작하는 시점의 값을 고정한 뒤, 그 값을 계속해서 사용한다.

튜플 삽입

• 새로운 튜플을 삽입

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

• 각 값의 어트리뷰트를 직접 명시할 수 있다. 명시하지 않을 때에는 스키마에 적힌 어트리뷰트 순서대로 도메인에 맞는 타입인지 확인해야 한다.

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

• 셀렉트 쿼리 문으로 만들어진 릴레이션을 구성하는 어트리뷰트가 삽입하는 릴레이션의 어트리뷰트와 동일하다면 이를 그대로 삽입할 수 있다. 이때 어트리뷰트 개수, 타입 등이 달라진다면 에러 발생.

특정 튜플의 값 갱신

• 특정 조건을 만족하는 튜플의 특정 어트리뷰트 값을 갱신 가능

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 문을 사용한다.

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

• case ~ end 중 when, when, when... else로 이루어지는 데 주의하자.
• 스칼라 서브쿼리를 사용할 수도 있다.

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

• E.g.) F 제외 지금까지 들은 총 학점을 계산해서 tot_cred라는 학생 어트리뷰트 값을 업데이트하는 쿼리문이다.