SQL[4]

Subqueries in the From Clause

• SQL은 from 절에서 subquery expression을 사용할 수 있다.

$42,000보다 높은 평균 급여를 갖는 학과의 평균 교수 급여를 찾는다

select 	dept_name, avg_salary
from	(	select	dept_name, avg(salary) as avg_salary
			from instructor
            group by dept_name ) // group별 salary 평균이 있는 2개 column짜리 table을 만든다
where 	avg_salary > 42000;

• 위 코드는 두 가지 방식으로도 또 구현 가능하다

select	dept_name, avg(salary) as avg_salary
from 	instructor
group by dept_name
having avg(salary) > 42000
// 이게 가장 simple하다

select	dept_name, avg_salary
from	(	select dept_name, avg(salary)
			from instructor
            group by dept_name)
            	as dept_avg(dept_name, avg_salary)	// table에 새로운 이름을 준다
where avg_salary > 42000;

---

With Clause

• with 절은 temporary relation을 정의하는 방법을 제공한다. 이 정의는 with 절이 발생하는 query에서만 이용 가능하다.
  즉, 일시적으로 생성되었다가 질의가 끝나면 없어진다

  실제 저장하는 애들은 : CREATE로 만든거

최대 예산을 갖는 학과를 찾아라

with max_budget(value) as // 새로운 table column 하나. tuple도 하나이다.
	(select max(budget)
     from department)
select department.name
from department, max_budget
where deaprtment.budget = max_budget.value
// 이게 종료되면 max_budget은 없어진다.

모든 학과의 총 급여의 평균보다 큰 총 급여를 갖는 학과를 모두 찾아라

with dept_total(dept_name, value) as // 학과별 salary 총합을 나타내는 table
	(select dept_name, sum(salary)
     from instructor
     group by dept_name),
	dept_total_avg(value) as // 하나의 column, 하나의 tuple
    	(select avg(value)
         from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value
// 이게 끝나면 두 개의 temporary table은 끝.

---

Scalar Subquery

• Scalar subquery는 single value가 필요할 때 사용되는 것이다.

각 학과의 교수의 인원수와 함께 모든 학과를 나열해라

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

• 만약 subquery가 두 개 이상의 tuple을 return하면, Runtime error가 발생한다.

---

Modification of the Databas

Deletion

Delete all instructor

delete from instructor

• instructor에 있는 tuple을 모두 삭제한다.
  instructor는 empty table이 된다

Delete all instructors from the Finance department

delete from instructor
where dept_name = 'Finance'

Delete all tuples in the instruction relation for those instructors associated with a department located in the Watson building

delete from instructor
where dept_name in ( select dept_name
					 from department
                     where building = 'Watson');

Delete all instructors whose salary is less than the average salary of instructors

delete from instructor
where salary < (select avg(salary)
				from instructor);

• 만약 delete를 먼저 진행하고 avg를 계산하면 분명 문제가 생긴다
• 그래서, avg를 계산하는 subquery를 먼저 실행시키고, avg를 고정한 후 delete를 진행한다.

---

Insertion

Add a new tuple to course

insert into course
	values('CS-437', 'Database Systems', 'Comp.Sci.', 4);
// 순서가 바뀌지 않도록 주의하자

equivalently,

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

Add a new tuple to student with tot_creds set to null

insert into student
	values('3003', 'Green', 'Finance', null);

Make each student in the Music department who has earned more than 144 credits hours an instructor in the Music department with a salary of $18,000

insert into instructor
	select ID, name, dept_name, 18000
    from student
    where dept_name = 'Music' and total_cred > 144;

• 여기도 마찬가지로 select가 다 끝난 후에, insert를 진행한다.

---

Updates

Give a 5% salary raise to all instructors

update instructor
	set salary = salary * 1.05

Give a 5% salary raise to those instructors who earn less than 70000

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

Give a 5% salary raise to instructors whose salary is less than average

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

• 계속 강조하지만 select가 다 끝난 후에, update가 진행된다

Increase salaries of instructors whose salary is over $100,000 by 3%, and all others by a 5%

• 문제를 보자마자 생각이 나야 한다. 먼저 $100,000을 기준으로 나눈 후에 update를 진행해야지, 왔다갔다 하면 두 번 급여가 인상되는 사람이 생긴다

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

• 라고 생각했는데, 그게 문제가 아니고 여기서는 순서가 중요하다.
• $100,000보다 낮은 애들한테 먼저 인상을 해주면 두 번 인상받는 instructor가 존재할 수 있기 때문에 높은 애들 먼저 급여를 인상시켜준다.
• 이러한 문제는 case statement를 쓰는 게 더 효율적이다

---

Case Statement for Conditional Updates

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

• General form

case
	when pred1 then result1
    when pred2 then result2
    ...
    when predn then resultn
    else result0
end

---

Updates with Scalar Subqueries

Recompute and update tot_creds value for all students

update student
set tot_cred = (select sum(credits) // tot_cred을 새로 계산한다.
				from takes, course
                where takes.course_id = course.course_id and
                	student.ID = takes.ID and
                    takes.grade <> 'F' and
                    takes.grade is not null); // grade가 null인건 지금 수강중인 걸 의미한다.

• 여기에 아직 수강한 course가 없으면 tot_creds를 null로 넣어주는 작업을 추가한다.

update student
set tot_cred = (select case
						when sum(credits) is not null then sum(credits)
                        else 0
                       end
				from takes, course
                where takes.course_id = course.course_id and
                	student.ID = takes.ID and
                    takes.grade <> 'F' and
                    takes.grade is not null);