[데이터베이스] 기초 SQL (2)

이전글에서는 기초 SQL문을 소개했고, 마저 집계함수에 이어서 소개하겠습니다.

 

이전글

https://konkukcodekat.tistory.com/213

[데이터베이스] 기초 SQL (1)

---

Aggregate Functions – Group By

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;

• 각 학과(department)별로 instructor들의 평균 급여(salary)를 구하는 SQL 쿼리
• 주의: instructor에 없는 학과는 결과에 나타나지 않음
• 테이블의 전체값을 가지고 통계내는 함수로 group by이용해서 그룹화할 attribute을 설정해야한다.

제약조건

• 주의 사항:
  • select 절에서 aggregate function(집계 함수) 외의 속성(attribute)은 반드시 group by 했을때 결과 목록에 포함되어야 한다.

아래는 오류가 발생하는 쿼리

select dept_name, ID, avg(salary)
from instructor
group by dept_name;

 

한 그룹에 여러 instructor가 있는데, ID를 여러개중 뭐를 채택할지 부정확하므로 포함시키면 안된다.

• <> == != : SQL에서 사용되는 비교 연산자

---

Aggregate Functions – Having Clause

• 학과별 평균 급여가 42,000보다 높은 경우를 찾는 SQL 쿼리

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

having은 where이랑 의미상 같으나, 선후관계에서 다르다.

where을 쓰면 그룹을 만들기 전에 조건을 적용한다. having은 그룹을 만든 후, 각 그룹에 대한 조건을 적용한다.

 

설명:

• having 절의 조건은 group by로 그룹이 생성된 후 적용됨
• 반면, where 절의 조건은 그룹화 이전에 적용됨

---

Null Values and Aggregates

null 값은 집계함수에서 제외된다.

• 전체 instructor들의 급여 총합을 구하는 SQL 쿼리

select sum(salary)
from instructor

• 설명:
  • sum() 함수는 null 값을 무시하고 연산 수행
  • 모든 값이 null일 경우 결과도 null이 됨
  • count(*)를 제외한 모든 집계 함수는 null 값을 포함하지 않음

---

Nested Subqueries

• SQL은 서브쿼리(subquery)를 사용할 수 있는 기능을 제공한다.
• 서브쿼리는 select-from-where 표현식이 다른 쿼리 내부에 포함된 형태
• 주로 집합 멤버십(set membership), 집합 비교(set comparison), 집합 개수(set cardinality) 등을 검사하는 데 사용됨

Example Query

1. Fall 2017과 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);

2. Fall 2017에 개설되었지만 Spring 2018에는 개설되지 않은 과목 찾기

select distinct course_id
from section
where semester = ’Fall’ and year= 2017 and
course_id not in
(select course_id from section
where semester = ’Spring’ and year = 2018);

 

Example Query

ID가 10101인 instructor가 강의한 모든 과목을 수강한 (중복을 제외한) 학생 수 찾기

select count(distinct ID)
from takes
where (course_id, sec_id, semester , year ) in
(select course_id, sec_id, semester, year
    from teaches
    where teaches.ID = 10101);

 

주의: 위의 쿼리는 더 간단하게 작성할 수 있습니다. 다만, 위와 같이 작성한 것은 SQL 기능을 설명하기 위한 것입니다

---

Set Comparison

• CSE 학과의 instructor 중 적어도 한 명보다 급여가 높은 instructor의 이름 찾기
• instructor 테이블을 자기 자신을 카테시안 곱을 한 이후에 where 절을 이용해서 구한 모습니다.

select distinct T .name
from instructor as T, instructor as S
where T.salary> S.salary and S.dept_name= ‘CSE’;

 

• > some 연산자를 이용하여 같은 결과를 도출할 수 있음 더 높은 가독성으로 쿼리를 작성할 수있다.

select name
from instructor
where salary > some (select salary
    from instructor
    where dept_name= ‘CSE’);

이 정의는 “Some Clause”(일부 절)에 대한 수학적 표현으로, 특정 관계  r 에 속하는 어떤 값  t 에 대해 조건이 충족됨을 나타냅니다.

 

즉, 어떤 값  t 가 관계  r 에 존재하여, 주어진 비교 연산을 만족하는 경우를 의미합니다.

 

하나만 충족하면 되는데, 하나도 충족하지 않는 경우만 false를 한다.

 

some = in 이지만, =/= some은 not in과 다르다.

---

all

즉, 집합  r  내의 모든 원소 t에 대해 비교 연산이 성립해야 한다는 뜻이다.

급여가 생물학(Biology)과의 모든 교수들보다 더 높은 교수들의 이름을 찾는다.

all -> max 대체가능한 경우

select name
from instructor
where salary > all (select salary
    from instructor
    where dept_name= ’Biology’);

 

Test for Empty Relations

• exists 연산자는 서브쿼리의 결과가 비어 있지 않으면 true를 반환
• exists r ⇔ r ≠ Ø
• not exists r ⇔ r = Ø

 

Correlation Variables

• Fall 2017과 Spring 2018 두 학기에 모두 개설된 과목을 찾는 또 다른 방법

select course_id
from section as S
where semester = ’Fall’ and year= 2017 and
    exists (select *
        from section as T
        where semester = ’Spring’ and year= 2018
        and S.course_id = T.course_id );

• Correlated subquery(연관 서브쿼리)를 활용
  • 내부쿼리가 외부쿼리를 참조하는것
• Correlation name 또는 Correlation variable 사용

 

Not Exists

• Biology 학과에서 개설된 모든 과목을 수강한 학생 찾기

select distinct S .ID , S .name
from student as S
where not exists ( (select course_id
	from course
	where dept_name= ’Biology’)
	except
		(select T .course_id
		from takes as T
		where S .ID = T .ID ));

---

Test for Absence of Duplicate Tuples

• unique 연산자는 서브쿼리의 결과에 중복된 튜플이 있는지 확인
• (결과가 빈 집합이면 true로 평가됨)
• 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);

 

 

Subqueries in the From Clause

• SQL에서는 from 절에서 서브쿼리 표현식을 사용할 수 있음
• 평균 급여가 3,000,000 이상인 학과들의 instructor 평균 급여 찾기

select dept_name , avg_salary
from (select dept_name, avg(salary) as avg_salary
	from instructor
	group by dept_name)
where avg_salary > 3000000;

• 또 다른 방법 (서브쿼리의 select 절의 as를 가장 밑줄 테이블 정의에서 alias 적용해준 모습)

select dept_name , avg_salary
from (select dept_name, avg(salary)
	from instructor group by dept_name )
as dept_avg(dept_name, avg_salary) where avg_salary > 3000000;

---

With Clause - 임시 뷰를 만들 수 있다.

서브 쿼리가 반복되면 이렇게 등록 후 재사용한다.

• with 절을 사용하여 특정 쿼리 내에서만 사용할 수 있는 임시 뷰(temporary view) 정의 가능
• 예제: 최대 예산을 가진 모든 학과 찾기

with max_budget(value) as
	(select max(budget)
	from department)
select name
from department , max_budget
where department.budget = max_budget.value;

---

Complex Queries using With Clause

WITH CTE이름(열1, 열2, ...) AS (
    SELECT ...
    FROM ...
    WHERE ...
),
다른CTE이름 AS (
    SELECT ...
)
SELECT ...
FROM CTE이름
JOIN 다른CTE이름 ...

 

• with 절은 복잡한 쿼리를 작성할 때 유용함
• 대부분의 데이터베이스 시스템에서 지원하지만, 약간의 문법 차이가 있을 수 있음
• 예제: 총 급여 합이 전체 학과들의 평균 총 급여보다 높은 학과 찾기

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;

---

Scalar Subquery

• 단일 값(scalar value)이 필요한 곳에서 사용되는 서브쿼리

%% count로 스칼라성 쿼리 %%
select dept_name,
	(select count(*)
	from instructor
	where department.dept_name = instructor.dept_name)
	as num_instructors
from department;

%% 서브 쿼리가 결과가 두개 이상 나오면 에러 %%
select name
from instructor
where salary * 10 >
	(select budget from department
	where department.dept_name = instructor.dept_name)

Modification of the Database

• 데이터베이스 수정 작업
  • 특정 관계(relation)에서 튜플 삭제
  • 특정 관계에 새로운 튜플 삽입
  • 일부 튜플의 값을 업데이트

Deletion

• 모든 instructor 삭제
  • delete from instructor
• Finance 학과 소속 instructor 삭제
  • delete from instructor where dept_name = 'Finance';

 

Watson 건물에 위치한 학과와 관련된 instructor 삭제

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

 

delete 제약조건

 

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

• 평균 급여보다 적은 급여를 받는 instructor 삭제
• 문제점: 튜플을 삭제할 때 평균 급여가 변할 수 있음
• 해결책: SQL이 알아서 내부적으로 해결해준다.
  1. 먼저 평균 급여를 계산하고 삭제할 튜플을 찾음
  2. 그 후, 위에서 찾은 튜플들을 한 번에 삭제 (평균 급여를 다시 계산하지 않음)

 

Modification of the Database – Insertion

• course 관계에 새로운 튜플 추가
• student 관계에 새로운 튜플 추가 (tot_creds를 null로 설정)

%%  Add a new tuple to course %%
insert into course
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

%% or 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 );

 

insert 제약 조건

한 테이블의 서브쿼리를 이용해서 다른 테이블에 튜플들을 insert한다.

instructor테이블에 있는 모든 튜플을 student 테이블에 insert 한다.

insert into student
select ID, name, dept_name , 0
from instructor

• 모든 instructor를 student 관계에 추가하고 tot_creds를 0으로 설정
• select-from-where 구문은 완전히 실행된 후 결과가 삽입됨
• 즉 select와 insert는 영향을 주지 않는다.
• 그렇지 않으면 같은 테이블에서 하는 경우 insert into table1 select * from table1 같은 쿼리가 무한 삽입을 유발할 수 있음 (기본 키가 정의되지 않은 경우)

---

Modification of the Database – Updates

• 급여 인상 적용
  • 급여가 100,000 초과인 instructor → 3% 인상
  • 그 외 모든 instructor → 5% 인상

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

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

• Write문은 대체로 업데이트 순서가 중요 : 순서가 바뀌면 두번 인상되는 사람도 생길수가 있다.
• CASE문을 사용하는것이 더 좋은 선택이다.

---

Case Statement for Conditional Updates

• 같은 업데이트 작업을 case 문을 이용하여 실행 가능

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