[데이터베이스] DB변경, 삽입, 삭제, 변경

# with

--> 가장 많은 예산을 가진 학과
with max_budget (value) as
		(select max(budget)
		from department)
select dept_name, budget
from max_budget,department 
where department.budget = max_budget.value

--> 학과의 총 급여가 평균 학과의 총 급여보다 많은 모든 학과
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

## 데이터베이스의 변경, 삽입, 삭제, 변경

# 삭제 delete

transaction
폭격맞아도 DBMS는 버텨야함
ACID
- Atomicity : all or nothing 데이터. 
- Consistency
- Isolation
- Durability : 

삭제의 단위는 row (행) 별로

 

drop table 은 모두 삭제 (존재자체가 사라짐)

delete from table은 구조는 남아있고 내부의 데이터(행)만 사라짐

--->instructor 릴레이션에서 모든 투플을 삭제. instructor 릴레이션 자체는 존재하지만, 비어있다
delete from instructor

default : auto commint ON

commit : ALL (한개의 sql문장 단위로)

rollback : NOTHING

 

begin transaction ~sql 문장~ commit transaction

비깃과 커밋으로 문장들을 묶을 수 있다.

 

begin transaction ~sql 문장 ~ rollback transaction

begin실행하고 삭제하는 sql을 실행한 뒤 rollback을 실행하면 삭제되었던 데이터들이 다시 복구과 됨 (nothing ,원위치)

--> 재무과의 교수들과 관련된 instructor릴레이션의 모든 투플을 삭제하라
delete from instructor
where dept_name = 'Finance'

--> 급여가 13,000에서 15,000 사이인 모든 교수들을 삭제하라
delete from instructor
where salary between 13000 and 15000

--> Watson 건물에 위치한 학과와 관련된 instructor 릴레이션의 모든 교수를 삭제하라
delete from instructor
where dept_name in (select dept_name
                    from department
                    where building = 'Watson')

 

 

# 삽입 insert

삽입도 삭제와 마찬가지로 행단위로

삽입이 안되는 경우 primary key 유일성을 갖추지 못할 때

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

value값의 순서와 course의 기본 구조의 형식이 다를때는 모두 써줘야함

insert into course(course_id, title, credits, dept_name)
values ('CS-437', 'Database Systems', 4, 'Comp. Sci.')

null 값 넣기, 테이블 작성 파일에서 조건이 not null이 아닐때만 삽입 가능

insert into course
values ('3003', 'Green', 'Finance', null)

--> 컴공 교수들의 봉급을 2배로 올려줌
update instructor
set salary = salary *2
where dept_name = 'Comp. Sci.'

# 갱신 update

--> 모든 교수들의 급여 5퍼세트 인상
update instructor
set salary = salary *1.05

--> 급여가 70000 미만인 교수들만 급여 인상
update instructor
set salary = salary * 1.05
where salary < 70000;

--> 평균보다 적은 급여를 받는 교수들의 급여를 5% 인상하라
update instructor
set salary = salary *1.05
where salary < (select avg(salary)
                from instructor)

 update문의 순서가 중요하다. 따라서 case구문을 이용하기도

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

update student
set tot_cred = (select sum(course.credits) 
                  from takes, course
                  where student.id = takes.id and
				  takes.course_id = course.course_id and
				  takes.grade!='F' and
				  takes.grade is not null)

매년 학생들의 총 이수학점을 업데이트하는데, 이때 금학기에 이수한 학점 F가 아니여야 하고 null값이 아니여야함