Modification of the Database

상위 문서: SQL

개요

해당 문서에서는 SQL을 사용하여 데이터베이스의 정보를 delete, insertion, update하는 방법을 설명한다.

Deletion

튜플의 deletion는 query와 거의 비슷한 방식으로 작성된다. 이때 전체 튜플이 삭제되며, 특정 속성의 값만 삭제되지는 않는다. 이는 아래와 같이 표현된다:

delete from r where P;

여기서 P는 조건(predicate)을, r은 릴레이션(relation)을 나타낸다. 이 delete 문은 먼저 r 릴레이션에서 P(t)가 참인 모든 튜플 t를 찾고, 그런 다음 해당 튜플들을 r로부터 삭제한다. 이때 where 절은 생략할 수 있으며, 이 경우 r에 있는 모든 튜플이 삭제된다. 또한 delete 명령은 한 번에 오직 하나의 릴레이션에만 작동하며, 이에 따라 여러 릴레이션에서 튜플을 삭제하려면 릴레이션마다 별도의 delete 명령을 사용해야 한다. 하지만 where 절 안의 서브쿼리에서는 여러 릴레이션을 참조할 수 있다. 예를 들어, 대학 평균 급여보다 낮은 급여를 가진 모든 교수의 정보를 삭제하고 싶다고 하자:

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

위에서는 delete가 한 릴레이션에서만 동작하더라도, where절 안에서는 다른 릴레이션들을 자유롭게 참조할 수 있다는 것을 보여준다. 또한 위 예제는 avg(salary)를 고정된 값으로 사용해야 한다는 것을 보여준다. 만약 avg(salary)의 값이 튜플을 delete함에 따라 바뀐다면, 바뀐 평균값에 따라 원래는 지워지지 말아야 할 튜플이 delete될 수도 있다.

Insertion

릴레이션에 데이터를 삽입(insert)하기 위해서는 어떤 튜플을 삽입해야 할지 SQL에 알려주어야 한다. 이를 위해서 삽입할 튜플을 명시하거나, 삽입될 튜플 집합을 반환하는 쿼리를 사용할 수 있다. 이때, 삽입되는 튜플의 속성 값들은 각 속성의 도메인을 만족해야 하며, 튜플의 속성 수 또한 릴레이션의 스키마와 일치해야 한다. 이는 다음과 같은 두 예제를 통해서 알아볼 수 있다:

insert into course
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
--위와 동일한 작업을 수행하는 insert 코드 / 또한 속성 이름을 지정한다.
insert into course (course_id, title, dept_name, credits)
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

insert into instructor 
--튜플을 명시적으로 지정하지 않고 select 문을 사용해 튜플 집합을 구성하고 이를 릴레이션에 삽입한다.
select ID, name, dept_name, null --각 튜플은 ID, 이름, 학과(Music), 급여 null 값을 가진다.
from student
where dept_name = 'Music' and tot_cred > 144;

이때 시스템은 insert 수행 전에 select 문을 완전히 실행해야 한다. 만약 select 실행 중에 insert가 병행되면, 예를 들어 다음 쿼리는 무한 루프처럼 동작할 수도 있다:

insert into student
select *
from student;

만약 student에 primary key constraint가 없다면, 같은 튜플이 여러개 있어도 DB가 이를 막지 않는다. 만약 student 릴레이션에 튜플 하나가 존재하고 위 쿼리를 실행시킨 다면, select * from student는 튜플 하나를 가져오고 이를 삽입한다. 이때 student 테이브에는 동일한 튜플이 2개 존재한다. 다음 반복에서는 select *가 두 개의 튜플을 가져오고 이러한 과정을 반복하며 무한 루프에 빠지게 된다. 이는 select 결과가 insert로 인해 동적으로 바뀌기 때문에 문제를 야기할 수 있다.

Update

특정 상황에서는 튜플의 모든 값들을 변경하지 않고 튜플 내의 특정 값만 변경하고 싶을 수도 있으며, 이를 위해, update 문을 사용할 수 있다. Insert 및 delete 문에서 그랬던 것처럼, 쿼리를 사용하여 갱신할 튜플들을 선택할 수 있다. 예를 들어 연봉ㅇ 70,000달러 미만인 교수들에게만 연봉을 5% 인상하려는 경우, 아래와 같이 작성할 수 있다:

update instructor
set salary = salary * 1.05
where salary < 70000;

이때, update 문 안의 where 절은 where 절에서 사용 가능한 어떠한 구성 요소라도 포함할 수 있다. 예를 들어, 연봉이 평균보다 낮은 교수들에게 5% 급여 인상을 하기 위해서는 아래와 같이 작성할 수 있다:

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

Case Statement for Conditional Updates

만약 연봉이 100,000달러 초과인 교수들은 3% 인상을 받고, 그 외의 교수들은 5% 인상을 하기 위해서는 아래와 같이 두개의 update 문을 활용하여 작성할 수 있다.

--100,000달러 초과하는 봉급을 받는 교수
update instructor
set salary = salary * 1.03
where salary > 100000;
--100,000달러 이하의 봉급을 받는 교수
update instructor
set salary = salary * 1.05
where salary <= 100000;

이때 두 update 문의 순서는 매우 중요하다. 만약 두 문장의 순서를 바꾼다면 급여가 100,000달러 바로 아래인 교수는 8% 이상의 인상을 받을 수도 있다. SQL은 이러한 문제를 방지하기 위해 case 구문을 재공한다. 이를 사용하여 위의 코드를 하나의 update 문으로 작성할 수 있다.

update instructor
set salary = case
  when salary <= 100000 then salary * 1.05 --봉급이 100,000달러 이하이면 5% 인상한 값 반환 
  else salary * 1.03 --그렇지 않은 경우 3% 인상한 값 반환
end;

이때 case 구문의 일반적인 형식은 아래와 같다.

case
  when pred1 then result1
  when pred2 then result2
  ...
  when predn then resultn
  else result0
end

위는 pred1, pred2, ..., predn 중 처음으로 만족하는 조건 predi에 해당하는 resulti를 반환한다.

Updates with Scalar Subqueries

Scalar subquery는 SQL의 update 문에서도 유용하게 사용되며, 이는 set 절 안에서 활용될 수 있다. 예를 들어 아래와 같은 예시를 살펴보자:

update student
set tot_cred = (
  select sum(credits)
  from takes, course
  where student.ID = takes.ID and
        takes.course_id = course.course_id and
        takes.grade <> 'F' and
        takes.grade is not null
);

위 코드는 student 릴레이션의 각 학생에 대해, takes와 course 릴레이션을 참조하여 학생이 성공적으로 이수한 총 학점을 계산한다. 이때 어떤 학생이 성공적으로 이수한 과목이 없다면, 위의 문장은 tot_cred 속성값을 null로 설정한다. 이때 null 대신 0을 사용하고자 한다면, 위의 subquery에서 select sum(credits)를 다음과 같은 case 구문으로 바꿀 수 있다:

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

이때, 위의 case 구문 대신 coalcase 함수를 사용할 수도 있다. coalcase 함수는 null 값을 다른 값으로 간결하게 대체할 수 있게 한다. 위의 예제에서는 select sum(credits)를 아래와 같이 대체할 수 있다.

coalesce(sum(credits), 0)