[Database] Basic SQL | Week 3

Nested Subqueries

 

query는 select-from-where의 어디든 subquery로서 중첩될 수 있습니다.

 

다음의 질의가 들어온경우 다음과 같은 쿼리가 필요합니다:

Find course offered in Fall 2017 and in Spring 2018

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

 

Find courses offered in Fall 2017 but not in Spring 2018

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

 

Find the total number of (distinct) students who have taken course sections taught by the instructor with ID 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);

 

Set comparison

 

some과 all을 통해 테이블간의 집합 요소들 간의 비교연산도 가능합니다.

 

some

 

만약 다음과 같은 질의가 들어온경우 다음과 같이 쿼리를 짜면 계산비용이 너무 비쌉니다:Find names of instructors with salary greater than that of some (at least one) instructor in the CSE department

select distinct T.name
from instuctor as T, instructor as S # Cartesian product -> TOO EXPENSIVE!!!
where T.salary > S.salary and S.dept_name = 'CSE';

 

위 쿼리 대신 다음과 같은 쿼리를 사용하면 더 빠르게 수행 가능합니다:

select name
from instructor
where salary > some(select salary from instructor where dept_name = 'CSE');

 

이는 some을 통해 CSE에 소속되어있는 강사들의 연봉들 중 임의의 연봉들보다 큰 연봉을 갖는 강사의 이름을 찾는 것입니다. 이렇게 최소 하나나, 임의의 같은 표현이 필요한 경우 some이 필요할 수 있습니다.

 

Definition of Some Clause

 

some을 사용하면 집합을 비교연산자와 사용할 수 있습니다. 아래의 그림은 해당 진리값에 대한 예시입니다:

Definition of Some Clause

 

all

 

all절을 통해서 다음과 같은 쿼리를 짤 수 있습니다:

Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department

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

 

Definition of all Clause

 

all을 사용하면 집합을 비교연산자와 사용할 수 있습니다. 대소비교를 하는 경우 all은 min이나 max처럼 동작합니다.

Definition of all Clause

 

Test for Empty Relations

 

exists절은 입력이 공집합이면 false를 공집합이 아니라면 true를 반환하는 간단한 연산자입니다. not exists는 반대입니다.

 

이를 활용하면 다음과 같은 쿼리를 만들 수 있습니다:

Find all courses taught in both the Fall 2017 and in the Spring 2018 semester

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

 

이때 table S를 Correlated subquery라고 부릅니다. subquery에서 참고한 table이라는 뜻입니다.

 

not exists를 활용하면 다음과 같은 쿼리를 만들 수 있습니다:

Find all students who have taken all courses offered in the Biology department

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

 

Test for Absence of Duplicate Tuples

 

unique구조를 이용해서 서브 쿼리에 중복이 있는지를 확인할 수 있습니다. 만약 중복이 존재하는 경우 False를 반환합니다. *단 unique는 많은 DBMS에서는 지원하지 않습니다.

 

unique를 이용하면 다음과 같은 쿼리를 만들 수 있습니다:

Find all courses that were offered at most once in 2017

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

 

한편 group by와 having을 사용하면 다르게도 표현할 수 있습니다.

select course_id
from section
where year = 2017
group by course_id
having count(*) = 1

 

Subqueries in the From clause

 

SQL은 from절에도 서브 쿼리를 지원합니다.

 

Find the average instructors' salaries of those departments where the average salary is greater than $3,000,000

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

 

With clause

 

임시적인 테이블이 필요한 경우 with절을 사용할 수 있습니다. with절에서 만들어진 테이블은 쿼리가 끝난 후 사라집니다.

 

Fine all department with the maximum budget

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

 

Find all department where the total salary is greater than the average of the total salary at all departments

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;

 

scalar subquery

 

하나의 값을 갖는 한 개의 row와 한 개의 column을 갖는 테이블을 scalar라고 합니다. 이를 이용하여 다양한 쿼리를 만들 수 있습니다.

 

Modification of the Database

 

DB에서 table을 수정하는 방법은 크게 3가지입니다:

• Deletion of tuples from a given relation
• Insertion of new tuples into a given relation
• Updating values in some tuples in a given relation

 

Modification of the Database - Deletion

 

앞선 글에서 DB table의 튜플을 삭제하는 기능을 살펴본적이 있습니다. 그러나 이전의 Delete는 모든 tuple들을 지우는 명령이었습니다. 지금은 특정 조건에 해당하는 튜플들을 삭제하는 것에 대해 알아보겠습니다.

 

사실 간단합니다. where절을 사용하면됩니다.

 

Delete all instructors from the Finance department

delete from instructor
where dept_name = 'Finance';

 

Delete all tuples in the instructor relation for those instructors associated with a department located in the Watson building

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

 

Delete all instructors whose salary is less than the average salary of instructors

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

 

이때 주의해야하는 것은 위 예시처럼 삭제한 후 avg() 같은 집계함수를 사용하는 경우 삭제하는 튜플에 의해 avg() 값이 변경될 수 있다는 점입니다. 그렇기에 위 예시같은 경우는 미리 avg() 값을 계산한 후 따로 저장해놓고, 그 값을 기준으로 delete하는 방법을 사용해야 안전합니다.

 

Modification of the Database - Insertion

 

insert into를 통해 테이블에 튜플을 추가할 수 있습니다.

 

Add a new tuple to course

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

 

이때 튜플의 순서는 테이블의 attr.의 순서를 반드시 지켜야합니다.

 

or equivalently

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

 

아니면 위 예시처럼 attr.의 이름을 적어줘도 같은 기능을 수행합니다.

 

Add a new tuple to student with tot_creds set to null

insert into student
    values ('3003', 'Green', 'Finance', null);

 

Add all instructors to the student relation with tot_creds set to 0

insert into student
    select ID, name, dept_name, 0
    from instructor

 

insert를 할 때에는 그 테이블에 대한 계산이 모두 끝난 후 추가하는 것이 안정적입니다.

 

Modification of the Database - Updates

 

DB의 일부 튜플들의 값을 수정할 수 있습니다.

 

Increase salaries of instructors whose salary is over $100,000 by 3%, and all other receive a 5% raise

update instructor
    set salary = salary * 1.03
    where salary > 100000;
    
update instructor
    set salary = salary * 1.05
    where salary <= 100000;

 

하지만 이때는 계산의 순서가 중요합니다. 만약 두 update의 순서가 반대였다고 가정했을 때, $99,999의 연봉을 갖는 튜플은 두 번 계산될 수 있습니다(1. $99,999는 $100,000보다 작기 때문에 5%가 인상됨. 2. 인상된 연봉은 $100,000보다 크기 때문에 3%가 인상됨).

 

그래서 case문을 사용하기도 합니다.

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