SQL(3)

Aggregate Functions

Basic (avg, min, max, sum, count)

These functions operate on the multiset of values of a column of a relation, and return a value

• avg : average value
• min : minimm value
• max : maximum value
• sum : sum of values
• count : number of values

Examples

// Find the average salary of instructors in the Comp.Sci. department
select avg(salary)
from instructor
where dept_name = 'Comp. Sci';

• 첫 문장에 select ID, avg(salary)를 하면, 위 결과와 마찬가지로 tuple 하나만 출력된다. 이 때 출력되는 ID는 임의의 값을 하나 가져온 듯 싶다.

// Find the total number of instructors who teach a course in the Spring 2018 semester
select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2018;

• 2018 봄학기에 수업이 여러 개 있는 instructor도 있을 수 있기 때문에 teaches의 tuple과는 tuple 개수가 다를 수 있다.

// Find the number of tuples in the couse relation
select count(*)
from course;

• 전체 tuple의 개수가 출력된다.

---

Group By

각 group에 대해 select를 진행한다

// Find the average salary of instructors in each department
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;

• 소속 학과별로 salary 평균을 출력한다.
• (주의) select clause에 사용하는 attribute 중 aggregate functions(avg, ...) 안에 있지 않은 attribute는 무조건 group by list에 있어야 한다.

  /*erroneous query*/
  select dept_name, ID, avg(salary)
  from instructor
  group by dept_name;

  • ID가 group by 에 없기 때문에 출력할 수가 없다.
  • 만약, group by dept_name, ID라고 정상적으로 입력한다면?
    (dept_name, ID)는 모든 tuple에 대해 unique 하기 때문에 instructor relation의 모든 tuple이 출력될 것이다.

---

Having Clause

HAVING 절은 WHERE 절과 비슷하지만 그룹 전체 즉, 그룹을 나타내는 결과 집합의 행에만 적용된다는 점에서 차이가 있습니다. 반면, WHERE 절은 개별 행에 적용됩니다. 쿼리에는 WHERE 절과 HAVING 절이 모두 포함될 수 있습니다

Predicate in the having : after the formulation of groups
Predicate in the where : before forming groups

// Find the names and average salaries of all departments whose average salary is greater than 42000
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

---

Nested Subqueries

중첩 질의 처리

SQL provides a mechanism for the nesting of subqueries.
A subquery is a select-from-where expression that is nested within another query

select A1, A2, ... An
from r1, r2, ... rm
where P

as follows :

• Select clause :
  Ai can be replaced be a subquery that generates a single value.
  set of value는 불가능하다.
  aggregate function의 결과는 역시 가능하다.

• From clause :
  ri can be replaced by any valid subquery
  애초에 from clause 에 오는 건 table이기 때문에 별 제약조건이 없다.

• Where clause :
  P can be replaced with an expression of the form :
  B <'operation'> (subquery)

  • B : attribute
  • operation : 나중에 배운다

---

Set Membership

Set에 원하는 값이 있는지 없는지 확인한다.

// Find courses offered in Fall 2017 and in Spring 2018
select distinct course_id
from section
where semester = 'Fall' and year = 2017 and
	course_id in (  select course_id
    				from section
                    where semester = 'Spring' and year = 2018);
                    
// Find courses offered in Fall 2017 but not in Spring 2018
select distinct course_id
from section
where semester = 'Fall' and year = 2017 and
	course_id not in (  select coures_id
    					from section
                        where semester = 'Spring' and year = 2018);

• in, not in 뒤에 set을 준다.

// Name all instructors whose name is neither "Mozart" nor "Einstein"
select distinct name
from instructor
where name not in ('Mozart', 'Einstein')

• not in 뒤에 직접 값을 준다.

// Find the total number of (distinct) students who have taken course sections taugt by the instructor with ID 10101
select count (distict ID)
from takes
where (course_id, sec_id, semester, year) in
	(   select course_id, sec_id, semester, year
    	from teaches
        where teaches.ID = 10101);
        
// 위의 코드를 더 간단하게 해보기
select count (distinct S.ID)
from takes as S, teaches as T
where ((S.course_id, S.sec_id, S.semester, S.year)
		= (T.course_id, T.sec_id, T.semester, T.year) )
       and T.ID = 10101

위에 예시로 든 query들은 충분히 간단하게 작성할 수 있다.
The formulation above is simply to illustrate SQL features.

---

Set Comparison

Some

F 'comp' some r
⇔ ∃ t∈ r such that (F 'comp' t)
: 하나라도 만족하는 게 있으면 true 를 return

ex).
5 < some {0, 5, 6} => true
5 < some {0, 5} => false
5 = some {0, 5} => true
5 != some {0, 5} => true

(= some) ≡ (in) 서로 동치이다.
But, (!= some)과 (not in)은 서로 동치가 아니다.

// Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

// Same query using > some clause
select name
from instructor
where salary > some (	select salary
						from instructor
                        where dept_name = 'Biology')

---

All

F 'comp all r
⇔ ∀t ∈ r such that (F 'comp' t)

ex).
5 < all {0, 5, 6} => false
5 < all {6 10} => true
5 = all {4, 5} => false
5 != all {4, 6} => true

(!= all) ≡ (not in) 서로 동치이다.
But, (= all) 과 (in)은 서로 동치가 아니다.

// Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department
select names
from instructor
where salary > all (	select salary
						from instructor
                        where dept_name = 'Biology');

---

Exist

Yet another way of specifying

// Find all courses taught in both the Fall 2017 semester and in the Spring 2018 semester
select course_id
from section as S
where semester = 'Fall' and year = 2017 and
	exist (	select *
    		from section as T
            where semester = 'Spring' and year = 2018
            	and S.course_id = T.course_id);

• 2중 for loop 같은 느낌이다. 먼저 S에서 하나를 fix하고, T를 훑는다. 다음 S에서 하나를 fix하고, 또 T를 훓는다.
• 이런 방식으로 하면 worst case N^2을 갖는다. 반면, intersect를 이용하는 코드를 보면 2*N임을 확인할 수 있다.

---

Not exist

// Find all students who have taken all courses offered in the Biology department
select distinct S.ID, S.name
from student as S
where not exist    (select coures_id
					from course
                    where dept_name = 'Biology')
                   except
                   (select T.course_id
                    from takes as T
                    where S.ID = T.ID) );

• 첫 번째 nested query는 Biology학과의 모든 수업을 꺼낸다

• 두 번째 nested qeury는 특정 학생이 수강한 모든 수업을 꺼낸다

• 즉, 첫 번째와 두 번째는 전혀 상관이 없는 relation이다.
  
  

• 따라서 첫 번째 에서 두 번째를 빼면 biology 수업 중 그 학생이 수강하지 않은 수업들이 나온다.

• 만약 이게 not exist를 만족하려면 공집합이어야 하고, 결국 이를 만족하는 학생은 Biology 학과의 모든 수업을 수강한 학생이 된다.

  X - Y = ∅ ⇔ X⊂Y

  Cannot write this query using = all and its variants.

---

Unique

The unique construct tests wheter a subquery has any duplicate tuples in its result.
The unique construct evaluates to 'true' if a given subquery contains no duplicates.

// Find all courses that were offered at most once in 2017
select T.course_id
from course as T
where unique (	select R.course_id
				from section as R
                where T.course_id = R.course_id
                	and R.year = 2017);
                    
select T.course_id
from course as T
where 1 >= (	selet count(R.course_id)
				from section as R
                where T.course_id = R.course_id 
                	and R.year = 2017);