[데이터베이스 시스템 ver.7] 챕터3

용어 정리

char vs varchar

책: "char 타입과 varchar 타입을 비교할때 데이터베이스 시스템에 따라 varchar 타입에 여분의 공백이 더해질수도, 아닐수도 있기 때문에 varchar타입을 사용하는걸 추천한다"

"값의 길이가 바뀌면서 업데이트가 빈번하면 char를 써라 " - https://yozm.wishket.com/magazine/detail/2717/

char(5) vs varchar(5) vs varchar(5 char)

	char(5)	varchar(5)	varchar(5 char)
저장방식	항상 5byte 저장	5byte까지 저장가능	5글자까지 저장 가능 (다국어 사용시 유용)
"abc" 저장시	"abc  "	"abc"	"abc"
한글(2byte) "가나다라마"  저장 가능 여부	용량초과로 x	용량 초과로 x	o

 

DB 종류별 한국어 지원

	Oracle	PostgreSQL	MySQL
자료형	VARCHAR2()	VARCHAR()	VARCHAR()
기본 문자셋	AL32UTF8 (유니코드 문자셋)	UTF-8(유니코드 문자셋)	utf8mb4(유니코드 문자셋)
한글 한글자의 byte	3 byte	3 byte	3 byte

 

세 DB 모두 UTF-8을 사용하므로 DB 간의 텍스트 데이터 이동시 큰 문제는 없다. 다만 기본 문자셋이 다르므로 DB간 이동시 이모지나 특수문자가 깨질 수 있다.

 

FLOAT/DOUBLE vs NUMERIC(n,n)

float/double은 부동소수점 숫자를 사용하므로 근사값을 저장한다

numeric()은 고정소수점 숫자를 사용하므로 근사값이 아닌 정확한 값을 저장한다.

따라서 numeric은 소수점 이하를 포함한 계산에서 정밀도 손실이 없으며, 반올림 오류나 부동소수점 오차를 방지할 수 있다.

Query language(쿼리문)

문법적 순서 (쿼리 작성시)

SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
ORDER BY

 

실제 실행 순서

FROM
JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

 

"FROM tableA, tableB" VS "JOIN tableA, tableB"

FROM tableA, tableB는 cross join(카테시안 곱)으로 동작하는 것과 같으며

JOIN tableA, tableB는 보통 inner join으로 동작하는 것과 같다

따라서 카테시안곱을 피하기 위해서는 join을 사용하는 것이 좋다

아래 두 쿼리의 결과는 같다

// join 사용x
select *
from tableA, tableB
where tableA.id = tableB.id

// join 사용o
select *
from tableA, 
join tableB on tableA.id = tableB.id

 

- 카테시안 곱에 의한 from 절 문제

select p.a1
from p, r1, r2
where p.a1=r1.a1 or p.a1=r2.a1

 

위와 같은 쿼리문 실행시 p,r1,r2 셋 중 하나라도 테이블에 튜플값이 없다면 결과값은 아무것도 나오지 않는다

카테시안 곱은 p*r1*r2 와도 같은데, r1의 값이 하나도 없으면 n*0*n 과도 같으므로 결과는 0개가 출력되기 때문이다

이와같은 문제는 join으로  해결이 가능하다.

SELECT p.a1
FROM p
LEFT JOIN r1 ON p.a1 = r1.a1
LEFT JOIN r2 ON p.a1 = r2.a1
WHERE r1.a1 IS NOT NULL OR r2.a1 IS NOT NULL;  // null이 아닌 경우만 결과로 반환되도록 함

 

* where

아래 두 쿼리문은 같다
WHERE I.ID = T.ID and name = "Biology"

WHERE (I.ID, name) = (T.ID, "Biology")

* as 

as로 테이블의 별칭을 만들 수 있다. 이는 아래와 같이 부른다

Correlation name (상관 이름) or correlation variable(상관 변수) or tuple variable(튜플 변수))

* 와일드카드 ( _, % )

_ : 한글자를 의미

% : 0개 이상의 모든 문자를 의미

WHERE building LIKE "%Watson"	// Watson (o), sWatson (o), ssWatson (o), sWatsons (x)

WHERE building LIKE "Watson%"	// Watson (o), sWatson (x), ssWatson (x), sWatsons (x), Watsons (o)

WHERE building LIKE "%Watson%"     //  Watson (o), sWatson (o), ssWatson (o), sWatsons (o)

WHERE building LIKE "_Watson"     // Watson (x), sWatson (o), ssWatson (x), sWatsons (x)

WHERE building LIKE "__Watson"     // Watson (x), sWatson (x), ssWatson (o), sWatsons (x), sssWatson (x)

WHERE building LIKE "_Watson_"     // Watson (x), sWatson (x), ssWatson (x), sWatsons (o)

 

 

Set operations (집합연산)

 - union (합집합) vs full outer join

union과 full outer join 모두 중복되는 튜플은 제거한다.

두 쿼리의 결과는 같다

// union을 사용한 경우
SELECT id, name FROM customers
UNION
SELECT id, name FROM suppliers;

// join을 사용한 경우
// COALESCE()로 같은 속성 결합
// FULL OUTER JOIN으로 두 테이블의 모든 데이터 포함
SELECT COALESCE(c.id, s.id) AS id, COALESCE(c.name, s.name) AS name
FROM customers c
FULL OUTER JOIN suppliers s ON c.id = s.id;

 - intersect (교집합)  vs inner join

	intersect	inner join
처리방식	모든 행이 같아야 true	특정 속성이 같으면 true
쿼리 작성시	간단하고 직관적	intersect보단 복잡
인덱스 사용 가능 여부	x	o
처리 속도	느림	빠름
중복 제거 여부	o (자동 DISTINCT)	x (필요하면 DISTINCT 추가)

 

두 쿼리의 결과는 같다

// INTERSECT 사용한 경우
SELECT id, name, city FROM customers
INTERSECT
SELECT id, name, city FROM suppliers;

// INNER JOIN 사용한 경우
SELECT c.id, c.name, c.city, s.city AS supplier_city
FROM customers c
INNER JOIN suppliers s ON c.id = s.id 
and c.name=s.name 
and c.city=s.city

 - except (차집합)

"2017년 가을 학기에는 있지만 2018년 봄 학기에는 없는 모든 과목을 구하라"
두 쿼리의 결과는 같다.

// except 사용한 경우
(select course_id
from section
where semester = "Fall" and year=2017)
except
(select course_id
from section
where semester = "Spring" and year=2018)

// left join 사용한 경우
SELECT c1.course_id, c2.course_id
FROM section c1
LEFT JOIN section c2
ON c1.course_id = c2.course_id AND c2.semester = 'Spring' AND c2.year = 2018
WHERE c1.semester = 'Fall' AND c1.year = 2017 AND c2.course_id IS NULL;

 

* 집합 연산이 나을까? join이 나을까?

위에서 합집합, 교집합, 차집합을 집합연산(union, intersect, except)을 사용한 방법과 join절(inner join, outer join)을 사용한 방법 두가지 모두 보였다.

그럼 두 방법 중 어떤 방법을 사용하는게 좋을까?

작성 난이도와 가독성 측면에서는 join보다 집합연산이 더 좋다

 

서브쿼리 성능관련

아래 두 쿼리의 결과는 같지만 성능은 두번째 방법이 더 좋다.

 

첫번째 방법은 각 행마다 평균을 매번 계산해야 하는 반면,

두번째 방법은 평균값을 한번만 계산해서 테이블 속성에 평균값을 추가하고 그걸 기준으로 한번에 비교한다. 

--1번째 방법
SELECT name
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

--2번째 방법
SELECT e.name
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) d_avg ON e.department_id = d_avg.department_id
WHERE e.salary > d_avg.avg_salary;

 

Aggregate functions (집계함수)

 - avg, min, max, sum, count(개수)

해당 함수는 select, from, having 절에서 모두 사용 가능하다.

where 에서는 사용 불가하다. 단, count는 where에서도 가능하다.

select문에서 사용한 경우 속성이 추가로 만들어진다.

 group by & having

서브쿼리 사용하기 전에 group by 먼저 시도해보자.

having은 group by 로 그룹화된 튜플들에 대해 조건을 적용할때 사용된다.

 

Nested subqueries (중첩 하위 질의)

Set(집합) 비교

- <, <=, >, >=

- some, all

some은 "모든 급여들 중 적어도 한 급여보다는 큰 급여을 가진 사람 구하라" 에 사용됨

all 은 "가장 높은 급여를 받는 학과를 구하라" 에 사용됨

 

- exists

exists : 서브쿼리의 결과가 하나라도 있을때 true, 

not exists : 서브쿼리의 결과가 비어있을때 false

exists 사용시에는 상위의 테이블을 서브쿼리에서 받아 사용해야 계산이 의미가 있다

// not exist 사용한 쿼리
SELECT S.company_name 
FROM company AS S 
WHERE NOT EXISTS (
    (
        SELECT city
        FROM company
        WHERE company_name = 'Small Bank Corporation'
    )
    EXCEPT
    (
        SELECT city
        FROM company AS T
        WHERE T.company_name = S.company_name
    )
)

 

- unique

중복이 없는 튜플을 찾아낼때 (unique 사용) or 중복이 있는 튜플을 찾아낼때 (not unique 사용) 쓰인다

"2017년에 딱 한번 개설된 모든 과목을 구하라"

// unique 사용한 구문
select T.course.id
from course as T
where unique (
    select R.course_id
    from section as R
    whrer T.course.id = R.course_id
    and R.year = 2017
)

 

ddl에서 unique를 사용하기도 한다. 이를 통해 insert할때 select 없이 중복 여부 검사 가능하다.

lateral

서브 쿼리에서 부모 쿼리의 별칭을 사용할 수 있도록 한다.

mysql에서는 이 쿼리 안써도 자동으로 가능하다.

"각 고객의 가장 최근 주문 내역을 출력해라"

// lateral 없는 쿼리
SELECT c.customer_id, o.*
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date = (
    SELECT MAX(order_date) 
    FROM orders 
    WHERE customer_id = c.customer_id
);

// lateral 있는 쿼리
SELECT c.customer_id, o.*
FROM customers c
INNER JOIN LATERAL (
    SELECT * FROM orders o 
    WHERE o.customer_id = c.customer_id 
    ORDER BY order_date DESC LIMIT 1
) o ON TRUE;

with절

with절을 사용하면 다른 구문으로 작성하는것보다 상대적으로 이해하기 쉽다.

또한 with절의 서브쿼리 결과를 메모리에 저장하고 재사용함으로써 서브쿼리를 매번 발생시키지 않아도 된다.

서브쿼리 결과 용량이 작고 서브쿼리 자체가 발생하지 않으면 사용하는게 성능 측면에서 좋다.

if-else문

select, update, delete, insert문에서 모두 사용 가능하다

 "END as 별칭" 으로 별칭을 만든다 

// update문에서 사용하는 경우
UPDATE instructor
SET salary =
    CASE
	WHEN 조건문2 THEN salary * 1.05
    	WHEN 조건문2 THEN salary * 1.04
    	ELSE salary * 1.03
   END as 별칭
   
// select 문에서 사용한 경우
SELECT ID, 
    CASE
        WHEN score < 40 THEN 'F'
        WHEN score < 60 THEN 'C'
        WHEN score < 80 THEN 'B'
        ELSE 'A' 
    END as 별칭
FROM marks

 

조건 연산자

- where 속성명 in ("값", "값") / where 속성명 in (서브쿼리)

서브쿼리의 값이 목록 or 부모 쿼리에 하나라도 존재하는지 확인하는 경우

- where 속성명 not in ("값, 값") / where 속성명 not in (서브쿼리)

서브쿼리의 값이 목록 or 부모 쿼리에 하나도 존재하지 않는지 확인하는 경우

 

 

 

---

데이터베이스 변경

- delete from 테이블 + where 쿼리문

전체 튜플 삭제 가능

특정 튜플만 삭제 가능

특정 속성값만 삭제하는건 불가능

- insert into 테이블 values (값, 값, 값, ...) / insert into 테이블 쿼리문

튜플 삽입 가능

"insert into 테이블 쿼리문" 을 통해 쿼리문의 결과 삽입 가능

튜플의 몇몇 속성만 삽입시 나머지는 null 처리 가능

- update 테이블 set 속성명 = 변경할값 

모든 튜플의 한 속성값 수정 가능

"update 테이블 set 속성명=변경할 값 where 쿼리문" 을 통해 튜플의 한 속성값 수정 가능

 

---

연습문제

3.1

a.

select title 
from course
where credits=3 and dept_name="Comp. Sci"

 

b.

세 쿼리 중 어떤 쿼리가 가장 효율적일까?

// join 사용 안한 경우
select distinct takes.ID
from teaches, instructor, takes
where instructor.name="Einstein"
and instructor.ID=teaches.ID
and takes.course_id=teaches.course_id;

// join 사용한 경우
select distinct takes.id
from takes
join teaches on takes.course_id = teaches.course_id
join instructor on instructor.id = teaches.id
where instructor.name = "Einstein";

// 서브쿼리 사용한 경우
SELECT DISTINCT id 
FROM takes
WHERE course_id IN 
(
    SELECT course_id
    FROM teaches INNER JOIN instructor 
        ON teaches.id = instructor.id
    WHERE instructor.name = 'Einstein'
);

 

c.

select max(salary)
from instructor

 

d.

select name
from instructor
where salary IN 
(
    select max(salary)
    from instructor
);

 

e.

서브쿼리로도 가능하지만 group by를 사용하는 것이 더 효율적이다.

서브쿼리 짜기 전에 group by 먼저 시도해보자.

select course_id, sec_id, count(takes.ID)
from takes
where year=2017
and semester="Fall"
group by sec_id, course_id

 

f. 

select max(take_count)
from (
    select sec_id, count(takes.ID) as take_count
    from takes
    where year=2017
    and semester="Fall"
    group by sec_id, course_id
    )

 

g.

select sec_id
from (
select sec_id, max(take_count)
    from (
        select sec_id, count(takes.ID) as take_count
        from takes
        where year=2017
        and semester="Fall"
        group by sec_id, course_id
        )
)

 

3.3

a.

update instructor
set salary = salary*1.1
where dept_name="Comp. Sci."

 

b.

not in 조건 연산자를 이용한 풀이

delete from course
where course_id not in (
	select distinct course_id
	from section
)

 

c.

insert into instructor
select ID, name, dept_name, 10000
from student
where tot_cred > 100

 

3.5

a. 

select ID,
	case
    	when score < 40 then "F"
        when score < 60 then "C"
        when score < 80 then "B"
        else "A"
	end as grade
from marks

 

b.

select grade count(grade)
from (
select ID,
	case
    	when score < 40 then "F"
        when score < 60 then "C"
        when score < 80 then "B"
        else "A"
	end as grade
from marks) as grade_sub
group by grade

 

3.7

위의 "카테시안 곱에 의한 문제"부분에서 설명함

3.9

a.

select employee.person_name, employee.city
from employee, works
where works.company_name = "First Bank Corporation"
and employee.ID = works.ID

b.

select e.ID, e.person_name, e.city
from employee as e, works as w
where w.company_name = "First Bank Corporation"
and w.salary>=10000
and e.ID=w.ID

c.

select ID
from works
where company_name != "First Bank Corporation"

d.

가장 높은 급여를 받는 튜플을 찾아야 하는 경우 max 또는 all을 사용한다

// max를 사용한 경우
select w.ID
from works as w
where w.salary > (
    select max(w.salary)
    from works
    where company_name = "Small Bank Corporation"
)


// all을 사용한 경우
select w.ID
from works as w
where w.salary > all (
    select w.salary
    from works
    where company_name = "Small Bank Corporation"
)

 

e.

"모든 값이 포함"되어야 하는 튜플을 찾아야 하는 경우 having + count 혹은 not exists + except 혹은 join을 사용한다

// having + count 사용한 쿼리
select company_name
from company
where city in (
    select city
    from company
    where company_name="Small Bank Corporation"
)
group by company_name
having count(distinct city) = (
    select count(distinct city)
    from company
    where company_name="Small Bank Corporation"
)

// not exist + except 사용한 쿼리
// (조건 집합) - (조건에 부합하는 집합) = 공집합인 company를 출력한다
SELECT S.company_name 
FROM company AS S 
WHERE NOT EXISTS (
    (
        SELECT city
        FROM company
        WHERE company_name = 'Small Bank Corporation'
    )
    EXCEPT
    (
        SELECT city
        FROM company AS T
        WHERE T.company_name = S.company_name
    )
)

 

f.

최대 값을 가진 튜플의 속성값 구하기

select company_name
from(
    select company_name, count(salary) as salary_count
    from works
    group by company_name
) as salary_counts
where salary_count = (
    select max(salary_count)
    from(
        select company_name, count(salary) as salary_count
        from works
        group by company_name
    ) as max_salary_count
)

 

g.

select distinct company_name
from works
where salary > (
    select avg(salary)
    from works
    where company_name="First Bank Corporation"
)

 

3.11

a. 

select distinct ID, name
from student
where ID in (
    select ID
    from takes as T, course as C
    where T.course_id = C.course_id
    and C.dept_name = "Comp. Sci." 
)

b.

select distinct S.ID, name
from student as S, takes as T
where T.ID = S.ID
and T.course_id not in (  
    select course_id
    from takes
    where year<2017
)

 

c.

select dept_name, max(salary) as max_salary
from instructor
group by dept_name

d.

with를 사용하여 가독성을 높인다

// 일반 쿼리
select min(max_salary)
from (
    select dept_name, max(salary) as max_salary
    from instructor
    group by dept_name
) as max_salary_table

// with 사용하여 가독성 높인 쿼리
WITH maximum_salary_within_dept(dept_name, max_salary) AS (
    SELECT dept_name, MAX(salary)
    FROM instructor
    GROUP BY dept_name 
) 
SELECT MIN(max_salary) 
FROM maximum_salary_within_dept

 

3.13

create table person(
	driver_id int not null,
    name varchar(50),
    address varchar(100),
    primary key (driver_id)
);

create table car(
	license_plate varchar(50),
    model varchar(50),
    year int,
    primary key (license_plate)
);

create table accident(
	report_number varchar(50),
	year int,
    location varchar(100),
	primary key (report_number)
);

create table owns(
	driver_id int not null,
    license_plate varchar(50),
	foreign key (driver_id) reference driver_id,
	foreign key (license_plate) reference car
);

create table participated (
	report_number varchar(50),
    license_plate varchar(50),
	driver_id int,
    damage_amount int,
    foreign key (report_number) reference accident,
    foreign key (license_plate) reference car
)

 

3.15

b.

select sum(amount)
from loan

 

c.

some을 사용하여 해결

select branch_name
from branch
and assets >= some (
    select assets
    from branch
    where branch_city="Brooklyn"
)

 

3.17

a.

update works
set salary = salary * 1.1
where company_name="First Bank Corporation"

b.

update works
set salary = salary * 1.1
where company_name="First Bank Corporation"
and id IN (
    select manager_id
    from manages
)

c.

delete from works
where company_name="First Bank Corporation"

 

3.19

1. 튜플을 삽입할 때 특정 속성의 값이 없는 경우 null을 사용한다.

2. null은 수학적 계산에서 무시되기 때문에 0이나 -1같은 값을 사용하는것보다 연산의 오류가 없다

3.21

a. 

select memb_no, name
from member
where memb_no in (
    select memb_no
    from book, borrowed
    where publisher="McGraw-Hill"
    and book.isbn=borrowed.isbn
) as b

b,

// having + count 사용한 경우
// 이 경우는 중복 대여가 허용되는 경우 count값이 커지므로 정확한 결과 나오지x
select m.memb_no, name
from member as m, borrowed
group by memb_no
having count(isbn) = (
    select count(isbn)
    from book
    where publisher="McGraw-Hill"
)


// not exists + except 사용한 경우
select memb_no, name
from member as m
where not exists (
    (
        select isbn
        from borrowed
        where memb_no = m.memb_no
    )
    except
    (
        select isbn
        from book
        where publisher="McGraw-Hill"
    )
)

c.

join문 사용하는게 가장 효율적임

d. 

count 결과값을 avg로 계산하기 위해 count 결과값을 속성으로 새로 추가하는 방법

// "인당 총 권수" 속성을 새로 추가하여 avg 처리하는 방법
// 총 퀀수가 0권이면 속성에 0 대입
WITH number_of_books_borrowed(memb_no, memb_name, number_of_books) AS (
    SELECT memb_no, name, (
        CASE
            WHEN NOT EXISTS (SELECT * FROM borrowed WHERE borrowed.memb_no = member.memb_no) THEN 0
            ELSE (SELECT COUNT(*) FROM borrowed WHERE borrowed.memb_no = member.memb_no) 
        END
    )
    FROM member
)
SELECT AVG(number_of_books) AS average_number_of_books_borrowed_per_member
FROM number_of_books_borrowed