Intermediate SQL.

1. Join operations.

from 절 안에서 사용할 수 있고, Natural join, Inner join, Outer join 3가지가 있다.

 

natural join <-- style 변경.

/*
1. natural join
동일한 타입과 이름을 가진 column으로 조인 함. 동일 column 은 한번만 선택됨.
*/
select * from takes natural join student;

-- natural join은 아래 조인과 같지만, 동일 column이 한번만 선택된다는 점이 다르다.
select * from takes, student
where takes.ID = student.ID;

-- natural join은 계속해서 연결할 수 있음.
select * from takes natural join student natural join course;

-- natural join 주의할 점.
-- natural join 은 공통된 '모든' 칼럼들이 같다는 조건으로 join을 한다.
-- 그래서 비교를 원하지 않은 칼럼이 있는 경우 natural join은 깔끔하게 포기. 아래는 예시.

select * from student natural join takes, course
where takes.course_id = course.course_id;

-- 위에거에 natural join 사용 한 것. dept_name 도 같다는 조건이 추가되어 버림.
select * from student natural join takes natural join course;


-- 그래도 column이 중복되서 나오는게 싫으면 using을 사용하면 된다.
-- course_id가 같다는 조건으로 join. course_id 가 한번만 나온다. 
select * from (student natural join takes) join course using (course_id);

 

 

join condition

 

-- 아래 두 쿼리는 같다.
select * from student join takes on student.ID = takes.ID;
select * from student, takes where student.ID = takes.ID;


-- using, on+조건문의 차이점!

-- id가 중복되서 선택이 됨.
select * from student join takes on student.ID = takes.ID;

-- id가 한번만 선택됨.
-- using 이 공통된 모든 컬럼에 대해 적용되면 natural 이다.
select * from student join takes using (id);

 

 

outer join

 

column 의 value가 달라도 튜플을 선택하고, 값을 null로 두고 싶을 때 사용하면 된다.

natural left outer join, natural right outer join, natural full outer join 3가지가 있다. outer join의 반대 개념은 inner join 인데, 우리가 이미 사용하는 형태가 inner join이다. column의 value가 다르면 선택하지 않는 것.

 

/*
student와 takes에서 공통되는 column은 id 하나 뿐이다.
column의 value가 달라도 일단 join을 한다.
natural join의 일종이기 때문에, 같은 attribute는 중복되지 않는다.
*/

-- left outer join.
-- 왼쪽에 있는 student 테이블의 컬럼 값은 유지. takes 컬럼 값은 null.
select * from
student natural left outer join takes;

-- right outer join.
-- 오른쪽에 있는 takes 테이블의 컬럼 값은 유지. student 컬럼 값은 null.
select * from
student natural right outer join takes;

-- full outer join.
-- 양쪽 테이블의 모든 컬럼 값을 유지한다. 빈칸은 전부 null로 채운다.
select * from
student natural full outer join takes;


/* 참고 */
-- inner join은 그냥 relation 2개를 , 로 선택하는 것과 같다. inner는 생략 가능.
-- (inner) join

 

 

2. View

 

View는 임시테이블이다. 실제로 테이블이 만들어지지는 않는다. 사용할 때에는 일반 테이블처럼 사용할 수 있다. 선언할 때에는 with 랑 같은데, with 대신 create view를 써주면 끝이다. 작동 원리는 with 에서 처럼 서브질의라고 생각하면 될 것 같다.

 

-- with 대신 create view를 썼다고 생각하면 된다.
create view faculty as
(select ID, name, dept_name
from instructor);

-- with 랑 사용법이 같아서 속성 이름도 지정해 줄 수가 있다.
create view departments_total_salary(dept_name, total_salary) as
(select dept_name, sum(salary)
from instructor group by dept_name);

-- view 에서 view를 만들 수가 있다.
-- cycle이 생기면 recursive 라고 하는데 자기자신을 view로 만드는 것과 같아서 금지됨.
create view faculty_copy as
(select * from faculty);

 

view의 확장.

별거 없고 view 대신 view의 정의를 그대로 옮겨 적으면 됨. 서브 쿼리 적는 느낌일듯. 만약 view를 recursive하게 정의를 했다면 확장하는 과정이 무한대로 가게 된다.

 

materialized view

create materialized view 키워드로 생성할 수 있다. 실제로 테이블이 생성되며, 질의 성능이 가상 테이블보다 향상된다. 참고로 일반 View를 사용하면 다시 정의로 치환되어서 서브쿼리 처럼 작동하는데 연산 시간이 조금 더 걸릴 수 밖에 없다. 또한 from에 있던 테이블의 정보가 수정되면 view도 다시 생성해야 한다. 따라서 유지보수가 비용이 크다는 단점이 있다. 대신 from에 있던 테이블의 변화가 적은 경우, 사용하면 좋다.

 

Update of a view

/*
view의 업데이트. view 는 가상테이블이기 때문에 view 에 추가되지는 않는다.
view 생성당시 from에 있었던 instructor가 업데이트 된다.
모자란 속성값은 null 로 들어간다. 그래서 그 자리가 Nullable 이어야 한다.
*/
insert into faculty_copy values('30765', 'Green', 'Music');

-- view 가 join 한 테이블일 경우 insert가 되지 않는다.
-- 어떤 테이블에 어떤 속성을 넣어야 하는지 불분명하기 때문.
create view instructor_info as 
select ID, name, building 
from instructor join department on instructor.dept_name = department.dept_name;
-- insert는 할 수 없다.
insert into instructor_info values('69987', 'White', 'Taylor');

-- view 의 생성당시 조건에 위배되는 insert를 할 경우.
create view history_instructors as
select *
from instructor
where dept_name = 'Histroy';
-- insert는 되지만 이러면 안된다. 학과가 History 경우만 insert 하도록 하자.
insert into history_instructors
	values('25566', 'Brown', 'Biology', 100000);

 

 

사실 view 를 업데이트 하는 건 굉장히 위험한 행동이어서 간단한 view의 경우에만 업데이트를 한다.

join을 하지 않았을 경우, select에 식이나, 집계함수, distinct 가 없는 경우, null 을 입력할 수 있는 경우, group by, having 이 없는 이런 간단한 경우에만 update를 하도록 하자.

 

 

무결성 제약조건(Integrity Constraints)

예시: 계좌에 있는 예금이 $10,000 이상이어야 한다. 최저 임금은 $4.00 / h 여야 한다. 고객의 phone-number는 not-null.. 이러한 제약 조건은 에러 방지를 위해 사용된다. Single Relation에서 사용되는 것들로는,

not null, primary key, unique, check(predicate) 와 같은 것이 있다.

 

1. not null

name varchar(20) not null,

budget numeric(12, 2) not null

이런 식으로 뒤에 not null 이 붙게 되면 null 값을 입력할 수 없다는 제약조건을 추가할 수 있다.

 

2. unique

unique(A, B, C, ..., Z)

여기서 A, B, C 는 속성의 이름이다. 저렇게 하면 괄호안에 있는 속성에는 중복된 값이 들어올 수 없다. 그래서 unique를 하면 자동으로 해당 속성은 candidate key가 된다. 참고로 candidate key 는 null 값이 될 수 있다. primary key는 자동으로 not null이다.

 

unique(A, B)

이렇게 선언하면 A, B 둘의 조합이 유일한지 묻는 질문이 된다. A도 중복이 있고 B도 중복이 있지만, A, B의 조합이 유일하면 되는 것.

 

3. check

create table section_temp
(course_id varchar(8),
semester varchar(6),
primary key (course_id, semester),
-- semester 가 아래 4개 중 하나여야만 한다. 철자 틀리는 거 다 잡아냄.
check (semester in ('Fall', 'Winter', 'Spring', 'Summer')));

이렇게 테이블을 만들면서 check를 사용할 수 있다.

 

 

참조 무결성(Referential Integrity)

어떤 속성이 다른 테이블에 있는 속성을 참조하려는 경우, 다른 테이블에 해당 값이 무조건 존재해야 한다는 것이다. 보통 foreign key로 선언을 한다. foreign key는 보통 다른 테이블의 primary key를 선택하게 된다.

 

만들 테이블은 department 테이블을 참조하고 dept_name을 foreign key로 선언.

foreign key (dept_name) references department

foreign key (dept_name) references department (dept_name)

primary key의 속성 이름을 적어줘도 되고 안 적어줘도 된다. 안 적으면 알아서 primary key로 선택한다.

 

 

Cascading Actions in referential intergiry.

테이블을 만들 때, on delete cascade, on update cascade 를 추가해서 참조 무결성을 지킬 수 있다. 참고로 oracle 에서는 on update cascade를 지원하지 않는다. 계속했는데 안되서 당황.

create table courtsesss(
name varchar(10),
dept_name varchar(20),
primary key (name),
foreign key (dept_name) references department
   		  on delete cascade
		--on update cascade
        )

 

cascade 대신 set null, set defualt 를 사용할 수 있다. 참조 할 것이 없으면 그냥 null값을 넣는다. set default 는 참조할 것이 없어지면 default 값을 넣는다.

 

on delete set null

on delete set default

저렇게 적어 주면 된다. on update set null 역시 oracle에서는 지원하지 않는 것 같다.

 

 

무결성이 위배되는지 체크해보기.

create table person(
    ID char(10),
    name char(40),
    mother char(10),
    father char(10),
    primary key (ID),
    foreign key (father) references person,
    foreign key (mother) references person);

 

테이블을 만들 때 이런식으로 자기 자신을 참조할 수도 있다. 

mother, father를 먼저 추가하고 그 이후에 person을 추가해야 한다. 만약 mother, father를 not null 로 바생해버리면 제일 위에 있는 mother, father를 추가할 수가 없다. 솔직히 이런 경우에는 일단 제한조건 체크를 잠깐 미루고 입력을 끝낸 후에 체크를 하는게 좋다.

 

 

check (time_slot_id in (select time_slot_id from time_slot))

이런 식으로 check 안에 서브쿼리를 넣을 수도 있다. foreign key로 안하는 이유는 time_slot_id가 primary key가 아니기 때문이다. 만약 3개가 합쳐서 primary key인데 그 중 하나만 참조를 하게 된다면 foreign key 로 선언을 못한다. 그러면 저런식으로 무결성 체크를 해주면 된다. 다이어그램에서 서브쿼리 이용 무결성 체크의 경우 화살표 두개로 표시함. foreign key는 화살표 하나.

 

 

Assertions(주장)

학생의 tot_cred은 무조건 50을 넘어야 한다는 조건을 생성하였다.

create assertion cred_constraint check (not exists (select * from student where tot_cred < 50));

이런식으로 테이블을 만들 때 체크를 하는 것이 아니라 테이블 밖에서 체크를 해줄 수도 있다. 하지만 oracle live SQL 에서는 지원을 안하는 듯 하다. 실행이 안된다.

 

 

내장 데이터 타입.

1. date:

예시. date '2005-7-27'

2. time:

예시. time '09:00:30'

3. timestamp:  date + time of day

예시. timestamp '2005-7-27 09:00:30.75'

4. interval:

예시. intveral '1' day.

interval 구하는 방법은 date끼리, time끼리, timestamp끼리 빼서 구할 수 있다.

 

 

Large-Object Types

1. clob

매우 긴 텍스트 파일 예를들어 메모장 파일 같은 것은 clob이라는 타입으로 사용하면 된다.

2. blob

clob을 제외한 객체들은 싹다 blob이다. 예를 들면 동영상, 사진 같은 것들이다.

이렇게 큰 객체들을 DB에 넣으면 안된다. 얘네들은 따로 저장을 하고 대신 포인터를 DB에 저장한다.

 

 

사용자 정의 타입. 아래처럼 사용가능. oracle에서는 작동하지 않는다. C++에서 typedef 느낌인듯.

create type dollars as numeric(12, 2) final;

 

create table department

(dept_name varchar(20),

budget dollars);