[데이터베이스] CH3. Introduction to SQL

Overview

1. History

---

SQL Parts

• DML
• DDL

Domain Types in SQL

: 스키마를 구성하는 attribute 의 가능한 type들!!

• char(n) : 길이가 n으로 고정된 문자열
• varchar(n) : 최대 길이가 n인 가변적인 문자열
• int : 정수
• smallint : 작은 정수
• numeric(p,d) : 전체 자리수가 p, 소숫점 아래 자리수가 d인 실수
  ex) numeric(3,1)이면 44.5 (0) 3.12(X) 444.5(X)
• real, double, precision
• float(n)

1. Create Table Construct

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

• r 은 relation의 이름
• Ai는 relation r의 schema에 있는 attribute, 속성 이름
• Di는 Ai의 데이터의 데이터 타입

ex.

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

Integrity Constraints in Create Table

Integrity Constraint의 종류에는

• primary key
• foreign key references r
• not null
  등 이 있다.
• ex.

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

• SQL은 데이터베이스를 업데이트 할 때 다음과 같은 integrity constraint와 충돌되지 않도록 한다.

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

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

2. Updates to Table

1. insert
   insert into instructor values('10211', 'Smith', 'Biology', 66000);
2. delete
   : 어떤 table에 있는 모든 튜플 지움
   delete from student;
3. drop table
   : table 자체를 지움
   drop table r;
4. alter table
   : table의 attribute 수정
   alter table r add A D;
   • attribute를 추가한다. (기존 데이터의 새 attribute는 NULL 로 초기화)
   alter table r drop A;
   • attribute를 삭제한다.

---

Basic Query Structure of SQL Queries

가장 기본적인 SQL query의 형태는 다음과 같다.

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

Ai 는 attribute를, Ri 는 relation을, P는 predicate(서술(조건..이라 생각하면 될 듯))를 의미한다.
이 SQL query의 결괏값은 항상 relation이다!

1. Select Clause

1. select clause는 query 결과의 attribute를 나열해서 보여준다.

select name
from instructor;

다음과 같은 쿼리문은 교수 table에서 전체 교수의 name을 찾아 보여준다.

*참고 : SQL 이름은 대소문자를 구분하지 않는다!

2. SQL문은 쿼리 결과뿐 만 아니라 relation 안에서의 중복을 허용한다.
   따라서 중복되는 값들을 제거하려면 select 뒤에 'distinct'라는 값을 적어줘야함

select distinct dept_name
from instructor;

다음과 같은 쿼리문은 서로 겹치는 dept_name을 싹 없애서 깔끔하게 한 번 씩만 나오게 한다.
반면

select all dept_name
from instructor; 

라고 적어주면, 겹치는 dept_name도 전부 출력되어 같은 dept_name이 두 번 이상 존재하게 될 수 있다.

3. • 의 뜻 = "all attributes"위 쿼리는 instructor table을 그대로 가져온다는 그런 뜻!
   • select * from instructor;
4. select문에는 +,-,*,/ 와 같은 연산자를 쓸 수 있다.

select ID, name, salary/12
from instructor;

라는 쿼리를 보낼 경우, 기존 instructor relation에서 salary 값이, 기존 값을 12로 나눈 값으로 나오게 될 것이다.

또, 이렇게 /12 를 한 값 자체를 재정의 할 수도 있다

select ID, name, salary/12 as monthly_salary;

2. Where Clause

1. Where 절은 결과가 되기 위해 충족해야 하는 조건을 지정해준다.
   (relational algebra에서 selection predicate (술어) 부분에 해당한다)

select name

from instructor

where dept\_name = 'Comp. Sci';

위 쿼리의 결과는 comp.sci. 학과에 있는 모든 교수의 이름이다.

2. where 자리에는 and, or, not과 같은 논리 연산자나 비교연산자 사용 가능!

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

3. between comparison operator

select name
from instructor
where salary between 90000 and 100000;

4. Tuple Comparision
5. select name, course_id from instructor, teaches where (instructor.ID, dept_name ) = ( teaches.ID, 'Biology' );

3. From clause

1. from 뒤에 여러 개의 relation이 올 경우, 이는 relation의 Cartesian product와 같다

select *
from instructor, teaches;

위 쿼리에서는 모든 가능한 instructor-teaches 쌍을 생성한다. (두 테이블에 있는 모든 attribute를 불러온다)
이 때 공통적인 attributes가 있는 경우 (relation name.attributes name)으로 이름이 재정의 된다.

ex) instructor.ID

카르테지안 곱은 직접 쓰기엔 쓸 일이 많지 않지만, where-claouse condition과 함께 쓰이면 굉장히 유용하다.

---

Examples

1. 어떤 cources를 가르치고 있는 교수자의 이름과 그 course_id를 찾아라

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

결과적으로 교수이름 - 그 교수가 가르치고 있는 과목의 학수번호 가 출력되게 된다

2. art department에 있고, 어떤 과목이든 가르치고 있는 교수의 이름과 학수번호를 출력하라

select name, course_id
from instructor, teaches#### 3\. From clause
where instructor.ID=teaches.ID and instructor.dept_name='Art;

---

---

Additional Basic Operations

4. Rename Operation

1. SQL은 relation과 attributes를 as 구문을 이용해서 이름을 재정의 할 수 있다.
   old-name as new-name

컴퓨터학과 교수보다 더 높은 연봉을 받는 모든 교수의 이름을 찾는 쿼리를 작성해보자

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

2. as는 생략 가능하다.
   instructor as T == instructor T

5. String Operations

: 문자열 일치 연산자 (string matching operator))

1. percent(%) : matches any substring
2. underscore(_) : matches any character

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

위 커리는 dar이 중간에 들어간 모든 문자열을 찾고

where name like '__dar';

위 커리는 xxdar을 찾는 쿼리이다.

where name like '___%';

위처럼 쓰게 되면 3자 이상의 문자열을 찾는다.

만약 100%를 찾고 싶다면 escape 문자를 쓰면 된다.

like '100\%' escape '\'

위 문장을 보면 backslash \ 를 escape character로 쓴다고 명시했다.

3. pattern은 대소문자를 구분한다.
4. SQL은 다음과 같은 다양한 string operation을 제공한다.
   • concatenation using "||" : 이어 붙이기
   • converting from upper to lower case : 대문자에서 소문자로 변환, 또는 그 반대
   • finding string length, extracting substring etc : 문자열 길이 찾기, substring 추출 등

6. Ordering the Display of Tuples

1. 교수자들의 이름을 알파벳 순서로 정렬하고 싶을 경우 order by를 쓸 수 있다.
2. select distinct name from instructor order by name;
3. 내림차순, 오름차순도 지정 가능하다. 기본적으로 오름차순으로 되어있음
   order by name desc
   라고 해주면 내림차순이 된다.
4. 여러 attributes를 기준으로 정렬할 수도 있다.
   order by dept_name, name

---

Set Operations

7. Set Operations

1. union (합집합)

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

//
FALL 2017과 SPRING 2018 학기에 열린 모든 강의들의 학수 번호

2. intersect (교집합)

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

//
FALL 2017 또는 SPRING 2018 학기에 열린 모든 강의들의 학수 번호

3. except (차집합)

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

//
FALL 2017에 열리고 SPRING 2018 학기에 안 열린 모든 강의들의 학수 번호

기본적으로 set operation들은 중복을 없앤다

만약 중복을 보존하고 싶다면

union all
intersect all
except all

을 쓰면 된다.

---

Null Values

• tuple들을 전부 null value로 채울 수 있다!

1. null은 알 수 없는 unknown value 또는 존재하지 않는 값을 나타냄
2. null을 포함하는 산술식의 결과는 항상 null임
   ex. 5 + null = null
3. is null을 활용해서 null값인지를 체크할 수 있다.
4. 다음과 같이 사용한다.

select name
from instructor
where salary is null;

4. is not null은 null값이 아닐 경우 true
5. null 값이 비교 구문에 포함될 경우 unknown을 뱉어낸다.
   ex. 5 < null or null < null or null = null
   ==> unknown
6. where의 술어부분에서는 Boolean operations 이 작동하므로 unknown이 끼어있을 때 어떻게 할지 결정해야함
   • and : (true and unknown) = unknown,
   • (false and unknown) = false, (unknown and unknown) = unknown
   • or : (unknown or true) = true,
     (unknown or false) = unknown,
     (unknown or unknown) = unknown

where 구문에서 unknown으로 결론날 경우 false로 취급함

---

Aggregate Functions

• SQL에서는 다음과 같은 함수들을 제공한다

avg : average value ; 평균값
min : minimum value ; 최솟값
max : maximum value ; 최댓값
sum : sum of values ; 합
count : number of values ; 개수 세기

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

--> 컴퓨터학과 교수의 평균 급여

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

--> spring 2018 학기에 가르친 모든 교수들의 수를 센다

select count (*)
from course;

--> course relation 안에 있는 모든 tuple의 개수를 센다.

2. Group By

-> 동일한 값끼리 묶어서 보여줌

예를 들어 각 과의 교수님들의 평균 salary를 알고 싶다면,

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

요렇게 쓸 수 있다.
평균을 구할 때 dept_name을 기준으로 그룹화하는 것!

이렇게 과별로 묶여서 평균을 구할 수 있다.

• group by 를 하려면 반드시 select에 존재해야 한다.

3. Having Clause

평균 salary가 42000보다 큰 모든 과를 찾아서 이름을 보여줘보자!

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

얼핏보면 where이랑 사용이 굉장히 비슷하지만, where절의 술어는 group을 형성하기 전에 적용되고, having 절의 술어는 그룹을 형성한 이후 적용된다. 만약 where절을 썼다면 전체 과 교수님의 평균에 대해 계산했을 것이다.
having 절을 썼기 때문에, 그룹화 한 후, 그룹 평균에 대해 계산하여 보여줄 수 있는 것!

---

Nested Subqueries

• 다른 쿼리 내에 쿼리를 중첩해서 쓰는 것.
• subquery란 다른 쿼리 안에 중첩되어 있는 select-from-where 구문을 말한다.

기존 기본 query

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

에서

• From clause : ri 는 어느 valid subquery로도 대체될 수 있음
• Where clause : P는 다음과 같은 형식으로 대체될 수 있음
  B < operation > (subquery)
• Select clause : Ai 는 어떤 single value를 만들어내는 subquery라면 대체될 수 있음

---

Set Membership

1. Fall 2017 과 Spring 2018에 둘 다 열린 courses 찾기 (in)

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

union을 사용했을 땐 아래처럼 구현했었다.

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

2. Fall 2017 에는 열렸지만 Spring 2018 에는 열리지 않은 courses 찾기(not in을 사용했다.)

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

차집합 (except)를 사용했을 땐 아래처럼 구현했었다.

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

3. 교수자의 이름이 "Mozart"도 아니고 "Einstein"도 아닌 instructors의 이름 찾기 (not in)

select distinct name
from instructor
where name not in ('Morzart', 'Einstein');

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

ID가 10101인 교수자의 수업 id, 분반, 학기, 년도를 가져와서 그 해당 수업을 듣는 학생의 ID를 세는 방식이다.

---

Set Comparision

1. "some" clause (= or)

• biology 학과에 있는 교수 중 아무나 한 명보다 자신의 salary가 더 큰 사람의 이름을 구해보자
  즉, biology 학과에 있는 교수의 salary 중 가장 낮은 salary보다 자신의 salary가 큰 사람!

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

• some 구문을 사용하면 똑같이 구현 가능하다

select name
from instructor
where salary > some (select salary
                     from instructor
                     where dept_name="Biology");

위 그림처럼 하나라도 ok 면 true이다!

2. "all" clause ( = and)

• biology 학과에 있는 모든 교수의 salary보다도 더 큰 salary를 받는 instructors 이름을 찾아보자!
  즉, biology 학과에 있는 교수의 salary 중 가장 큰 salary 보다도 자신의 salary가 더 큰 사람

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

all은 비교하는 모든 대상에 대해 성립해야 true이다.

some == in 이고

!all == not in 이지만

!some != not in 이고 all != in 이다.

3. "Exists" and "not exists" Clause

• exists 는 해당 subquery가 nonempty하면 true를 리턴한다.

1. Fall 2017 semester과 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) ;

설명을 해보자면 일단

1. section을 S로 불러와서 S 안에서 2017 fall에 열리는 과목을 찾아두고
2. section을 다시 T로 불러와서 그 T안에서 2018 spring에 열리고, 2017 fall에 열린 그 과목 course_id와 같은게 있는지 찾아본다.
3. 같은 게 있다면 그 과목은 두 번 다 열린 게 맞으므로 해당 course_id 반환

이런 매커니즘이다.

2. Biology 학과에서 열리는 모든 과목을 들은 학생을 찾아보자

select distinct S.ID, S.name
from student as S
where not exists ( (selelct course_id
                    from course
                    where dept_name = 'Biology')
                  except
                   (select T.course_id
                    from takes as T
                    where S.ID = T.ID));              

요것도 설명을 해보자면, 일단 student table에서 이름을 가져올거다.
근데 이제 where 이하 절에 대해서 만족하는 사항이 아예 없는 사람(not exists)을 가져올거다.

우선 student table에서 사람 한 명을 선택한 상태라고 가정해보자.
이 사람에 대해서 where절을 검사해주자.

차집합을 해줄 건데 먼저 Biology 학과에서 열린 모든 과목 course_id를 가져온다.
그리고 이 course_id 집합에서 지금 검사 중인 학생이 듣고 있는 수업을 아래 select절을 통해 구해서 빼준다.
그러면 이제 biology 학과에서 열린 과목 중 학생이 듣지 않은 수업만이 남아 not exists 검사로 가겠지?
이 때 이 부분이 공집합이라는 얘기는 모든 과목을 다 들었다는 이야기이므로 not exists가 true가 되어 그 student 를 반환한다.

• X - Y = Φ<=> X⊆Y
• 이 쿼리를 = 을 사용해서 바꿀 수 없음 (이게뭔소리야)

---

Test for Absence of Duplicate Tuples - unique

: subquery에 중복된 튜플이 있는지 여부를 검사하여 중복이 없을 경우 true를 반환한다.

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

---

Subqueries in the From Clause

• 평균 연봉이 42000보다 큰 과들의 평균 연봉을 구해보자

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

• 위의 경우 굳이 having clause를 쓸 필요가 없다. from 안의 절이 먼저 처리가 될 것!
• 다른 방식으로는 결과를 table화 할 수도 있다. (from ~ as 이용)

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

With Clause

• 쿼리 안에서 임시로 작동하는 임시적인 table 을 만든다!
• 가장 많은 예산을 가진 모든 departments를 찾기

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

Complex Queries using With Clause

• 총 급여가 모든 부서의 총 급여의 평균보다 큰 부서를 찾아보자

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;

dept_name 과 부서의 salary의 총 합을 저장하고 있는 dept_total이라는 table과
dept_total의 value 즉 각 부서별 salary의 총 합을 평균낸 값을 저장하고 있는 dept_total_avg를 새로 만든다.

그리고 이 두 테이블에서 value 값을 비교해 부서를 찾는다.

---

Scalar Subquery - in select clause

• 결과값이 하나일 것으로 예상될 때 사용한다. 이런 것만 select에 쓸 수 있다.
• 모든 부서를 각 부서의 교수자의 수와 함게 열거해보자

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

• 만약 subquery가 한 개 이상의 tuple을 반환할 경우 runtime error 가 발생한다.

Modification of the Database

1. Deletion

1. 모든 tuple을 다 지울 때
   delete from instructor;
2. tuple 중 특정 조건을 만족하는 튜플만 지울 때
   delete from instructor where dept_name = 'Finance';
   • Watson building에 위치한 아파트에 사는 instructors을 지울 때

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

위 경우 평균을 매번 구하게 되면 delete를 할 때마다 기준이 되는 평균값이 달라질 수 있으므로
먼저 구해서 값을 고정한 다음에 지울 대상을 찾아본다. 그 다음 찾은 대상을 한 번에 쫙 지운다. (avg를 다시 계산하거나 비교를 다시 하지 않는다)

2. Insertion

1. 새로운 튜플을 삽입할 때
   ` insert into course혹은
   ` insert into course(course_id, title, dept_name, credits)
2. values ('CS-437', 'Database Systems', 'Comp.sci.', 4); `
3. values ('CS-437', 'Database Systems', 'Comp.sci.', 4); `
4. 만약 일부 항목을 빈칸으로 채우고 싶다면
   insert into student values ('3003', 'Green', 'Finance', null);
   다음과 같이 null을 적어준다.

• 144학점 이상 들은 음악과에 있는 학생들을 연봉 18000인 교수로 추가하자.

insert into instructor  
select ID, name, dept\_name, 18000  
from student  
where dept\_name = 'Music' and total\_cred>144;

• select, from, where statement 는 relation에 삽입되기 전에 다 계산이 완료된다. 그리고 그 결과를 삽입하게 되는것

그렇지 않으면 다음 쿼리를 수행할 때 문제가 생기게 된다!

insert into table1 select * from table1;

3. Updates

: 말그대로 몇몇 요소들 update!

• 모든 교수들의 연봉을 5% 인상

update instructor  
set salary = salary\*1.05;

• 70000원보다 적게 버는 교수 5% 연봉 인상

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

• 평균보다 낮은 교수 연봉 인상

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

• 100000원이 넘는 사람은 3% 인상하고 나머지는 5% 인상한다.

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

또는 case statement를 쓸 수 있다.

update instructor  
set salary = case  
when salary <= 100000 then salary\_1.05  
else salary\_1.03  
end;

(앞의 방법보다 case statement 쓰는게 더 좋음)

Updates with Scalar Subqueries

• tot_creds 재계산 및 업데이트

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)

sum(credits) 대신 아래 코드를 쓰라는데 뭐가 다른건지 잘 모르겠다

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