Database) Chapter4. Intermediate SQL

Chapter 4: Intermediate SQL

Join Operations

inner join => result similar to intersect

Left Outer join => join 컨디션에 맞는 것 중 왼쪽 set의 값 기준에서 오른쪽 set의 표를 더 생성

Right Outer join => join 컨디션에 맞는 것 중 오른쪽 set의 값 기준에서 왼쪽 set의 표를 더 생성

Full Outer join => join 컨디션에 맞는 것 중 왼쪽 set의 값 기준 +　오른쪽 set의 값 기준을 모두 다 봄

Minus => join 컨디션에 맞지 않는 것을 출력

Join Types: inner join, left outer join, right outer join, full outer join

Join Conditions: natural, on <predicate>, using (A1, ... , An)

-natural과 using은 비슷한 의미임 => attribute 자체를 사용하기 때문에 사용된 attribute를 중복적으로 적어줄 이유가 없음

-on 같은 경우에는 attribute 자체가 아닌 attribute내 tuple의 값을 이용하기 때문에 attribute의 정보를 다시 한 번 더 출력해주어야 함

Views: 임시적인 table같은 느낌. from으로부터 끌어다쓰는 Relation과 tuple이 직접적으로 연결되어 있기 때문에 view의 값에 변화가 생길 경우 original relation같은 경우에도 변화가 생겨야함 -> create view v as <query expression>

(ex) create view faculty as / select ID, name, dept_name / from instructor

(ex) create view dept_total_salary(dept_name, total_salary) as

select dept_name, sum (salary) from instructor group by dept_name;

Update of a view

-> insert into v values (‘30765’, ‘Green’, ‘Music’);

(ex) create view instructor_info as / select ID, name, building/ from instructor, department/ where instructor.dept_name = department.dept_name;

insert into instructor_info values (‘69987’,‘White’,‘Taylor’)

=> origin data의 위치를 직관적으로 알지 못할 경우에는 update가 안 됨.

=> Create vies의 제약 조건이 만들 때 뿐만 아니라 지속적으로 적용

=> 그러나 Insert Data는 Origin relation table에 항상 들어감 (제약 컨디션에 맞지 않아도)

=> 다만 primary key가 없는 value는 insert 되지 않음

Integrity Constraints -> guard against accidental damage to the database by ensuring that authorized changes to the database

not null : Declare A to be not null

unique (A1, ..., Am) : form a candidate key

check (P) : where P is a predicate

ex) check (semester in (‘Fall’, ‘Winter’, ‘Spring’, ‘Summer’))

Cascading Actions in Referential Integrity

-> 한 relation에서 update될 경우 해당하는 foreign key를 가진 다른 relation에서도 연속적으로 update

(ex) foreign key (dept_name) references department

on delete cascade / on update cascade,

Index Creation – Indices are data structures used to speed up access to records with specified values for index attributes

-> (ex) create index studentID_index on student(ID)

Type Creation – make a type into a user-defined type

(ex) create type Dollars as numeric (12,2) final

Domains – Type과 비슷하나 not null같은 constraints들을 가질 수 있다.

Authorization Specification in SQL

grant statement is used to confer authorization

(ex) grant <privilege list> on <relation name or view name> to <user list>

grant update(name) on student to myuser@localhost

(ex) - create view geo_instructor as (select * / from instructor / where dept_name = ‘Geology’)

grant select on geo_instructor to geo_staff

=> then, geo_staff member issues => select * from geo_instructor;

------------------------------------------------------------------------------------------------

​

4.2 4.3 4.7 4.9

select * from student natural join takes
union
select ID, name, dept_name, tot_cred, NULL, NULL, NULL, NULL, NULL
from student
where student.ID not in (select takes.ID
                         from takes)

(select * from student natural join takes)
union
(select student.ID, name, dept_name, tot_cred, NULL, NULL, NULL, NULL, NULL
from student
where student.ID not in (select takes.ID
                         from takes))
union
(select takes.ID, NULL, NULL, NULL, course_id, sec_id, semester, year, grade
from takes
where takes.ID not in (select student.ID
                       from student))

4.7

create table emplyee
( person_name char(20),
  street char(30),
  city char(30),
  primary key(person_name)
  foreign key(city) references (city_info)
)