5. SQL part2

Yona·2021년 10월 26일

index

Aggregate Function

Group by

특수경우) single-row subquery

Examples

🌙 CS_DataBase

목록 보기

8/10

index

aggregate function // 집계함수
joined relations // join 관계
nested subqueries
ranking
more feautes...

Aggregate Function

종류
- avg
- min
- max
- sum
- count : 몇개
예시
- Select count(*) from student;
- Select avg(salary),max(salary),min(salary) from professor where deptName='CS'
- Select count(distinct pID) from teaches where semester='Spring' and year=2010;

Group by

지금까지는 테이블 전체에 대하여 집계함수를 적용하였다.
전체 테이블을 특정 속성 값으로 터플을 분류하고,
나누어진 각 그룹에 대하여 집계 함수를 적용하는것이 Group by

Find the number of professors in each department

Select deptName, count(*) // 3. subgroup 별로 모든 Tuple의 숫자를 셈
from professor // 1. professor 테이블에서
group by deptName; // 2. 학과별로 subgroup 나눈뒤

Find the average salary of professors in each department

Select deptName, avg(salary) // 3. 각 그룹에 대해 salary 들의 평균을 구한다.
from professor // 1. professor 테이블에서
group by deptName; // 2. 학과별로 subgroup을 나눈뒤에

주의사항
”group by“ 절을 사용할 때, select절에는 ”group by“ 절에 나온 속성과 그 집계 함수만 나올 수 있다

// wrong code
Select deptName, pID, avg(salary)
from professor
group by deptName; // pID가 없으므로 에러

그룹당 deptName과 집계함수값이 생성되는것에 반하여, PID값은 그룹 수보다 많이 존재해야 해서 보여줘야하는 pID값을 선정할 수 없다.

Find the average salary of professors in each department

Select avg(salary) // 3. 각 그룹에 대해 salary 들의 평균을 구한다.
from professor // 1. professor 테이블에서
group by deptName; // 2. 학과별로 subgroup을 나눈뒤에

다만, 어떤 그룹에 대한 평균값인지 알 수 없게 된다.
(7000, 6500, 7500)만 반환됨.

Having

“group by” 절 결과로 생성된 그룹에 대하여 임의 조건을 명시하는데 사용된다

전제조건 : group by 와 함께 사용

having과 where절은 다르게 사용된다

Find the names and average salaries of all departments whose average salary is greater than 6900

Select deptName, avg(salary)
from professor
group by deptName
having avg(salary) > 6900;

where절과 having절의 차이
- where절 = 각 튜플에 적용하여 조건 만족하는 튜플이 다음 단계로 이관
- having절 = 각 그룹에 적용하여 이를 만족하는 그룹만이 다음 단계로 이관
- For each department having more than five employees, retrieve the department name and the number of employees making more than $40,000
  - ```
  // wrong version
  Select dname, count(*) 
  from department, empoloyee 
  where dnumber=dno and salary>40000 
  group by dname
  having count(*)>5;```
```
- ```
// correct version
Select dname, count(*)
from department, employee
where dnumber=dno and salary>40000 and 
dno in (select dno from employee
        group by dno
        having count(*)>5)
group by dname;
```
- 상기는 “group by” 절을 사용했을 시에 where절과 having절의 차이를 보여주는 예제이다. 상기 스키마에서 dnumber, dname은 department의 number, name을 의미한다. employee.dno 속성은 departmenet.dnumber을 참조하는 외래키이다. // 사용자가 원하는 질의는 다섯 명 이상의 종업원을 가진 부서에 대하여 4만불 이상을 소득을 가진 종업원 수를 구하는 것인데, 상기에 표현된 잘못된 질의는 4만불 이상 소득을 가진 종업원을 5명 이상을 가진 부서의 종업원 명수를 구한다. 총무처 부서에 종업원 수가 6명이고 그 중 2명이 4만불 이상 소득을 가진다고 가정하면, <총무처, 2> 터플이 결과 관계에 속하여야 하나, 잘못된 질의에서는 총무처 터플은 결과관계에서 배제된다. 올바른 질의는 두 번째 SQL 문장에 나와 있다. 중첩질의 형태이며, 중첩질의는 후에 자세히 언급한다. // 두 번째 SQL 문장도 한 가지 문제점이 있다. 만약 관리처 부서의 종업원이 10명이고 모두가 4만불 이하의 봉급을 받는다고 하면, <관리처, 0> 터플이 결과 테이블에 속하여야 하나, 두 번째 SQL 문장에서는 관리처 터플이 결과에 전혀 속하지 않는다. 그 이유는 where절에서 봉급이 40,000 이하 조건을 적용하므로 관리처의 모든 직원 터플이 where 절 조건을 survive 하지 못하며, 그 결과 “group by” 절에는 관리처 직원 터플이 아예 없기 때문이다.

집계함수 + NULL

기본적으로 Null값은 무시

모든 값이 Nll인경우 count는 0반환
나머지 집계함수는 Null값 무시
이런 함수가 아니라, +, - 등의 연산의 경우 NUll이 하나라도 있으면 NUll을 리턴
예시

joined relations

사용
- where절에서 사용
- from 절에서 join테이블 생성후, 이에 대한 추가적 연산을 where에서 명시
join 종류
- inner join
- left outer join
- right outer join
- full outer join
outer join
- 일차적으로 조인 연산을 수행
- 조인 연산에서 제외된 터플을 널 값을 이용하여 결과 테이블에 첨가한다.
on <predicate>
- 조건 명시
- 매칭되는 요소 찾기
__using (A1, ..., An)
- 속성 명시

예시

예시상황

Nested Subqueries

select절 내 subquery : select 문장의 결과는 테이블이므로 select 문장에서 테이블이 위치하는 장소에는 select-from-where 표현이 이론적으로 가능

보통 subquery : 보통 중첩 질의문은 where절 또는 from절에 위치하고 있다.

특수경우) single-row subquery

특수한 경우(=결과테이블에 값이 하나만 존재할 때) 가능한 케이스

Get the name of professor who has the same salary as the professor with pID 10

Select name
from professor
where salary = (select salary
                from professor
                where pID='10') 
                and pID <> '10'; // 다른 당사자 제외

Get the name of professor who earns the maximum salary of the CS department

Select name
from professor
where salary = (select max(salary)
                from professor
                where deptName='CS');

바람직하지 않은 query. = 으로 비교하려면, 동등해야하는데 ‘salary’ 자체랑 동등하려면
Salary는 값 하나고, select의 결과물은 테이블(집합)이므로 값과 집합이 같다는걸 비교해야함.
특수한 상황에서만 비교 가능 (집합에 값 하나만 있을경우 = Single-row subquery )

Examples

Find course numbers offered in Fall 2009 and in Spring 2010

// ver1
Select distinct cID
from teaches
where semester='Fall' and year=2009 and
cID in (select cID from teaches 
        where semester='Spring' and year=2010);

// ver2
(select cID from teaches where semester='Fall\ and year = 2010) intersect (select cID from teaches where semester='Fall' and year=2009)

Find course numbers offered in Fall 2009 but not in Spring 2010

Select distinct cID
from teaches
where semester='Fall' and year=2009 and cID not in (select cID from teaches where semester = 'Spring' and year=2010);

Find the total number of distinct students who have taken any courses taught by the professor with pID 10

// ver.1
// () = 임시로 튜플을 생성하는 연산자
Select count(distinct sID)
from takes
where (cID, semester, year) in (select cID, semester, year
                                from teaches
                                where pID=10);
// ver.2
Select count(distinct sID)
from takes, teaches
where pID='10' and teaches.cID=takes.cID and teaches.semester=takes.semester and teacehs.year=takes.year