2. Introduction to SQL

DDL

: 스키마 정의, 데이터들의 타입 지정, 무결성 제약조건 지정, 색인(인덱스) 지정 등 가능.

 

 

도메인 종류

• char(n): 고정된 n의 길이를 가지는  문자열
• varchar(n): n개까지 자유롭게 길이를 가지는 가변 문자열
• smallint: 작은 정수 타입을 위함.
• numeric(p, d): 최대 p개의 자리를 가질 수 있고, 소수점 아래는 d개만 가질 수 있음.
  (고정된 실수 표현 방법)

 

 

drop table r

: 스키마까지 삭제되서 나중에 삽입하려 해도 안됨.

 

alter table r add A D

: D라는 도메인을 가지는 A 속성을 테이블 r에 추가.

 

 

<select 문>

• 관계 대수의 projection 연산과 대응

 

예)

하나의 열과 'A'인 행을 instructor 테이블에서 찾아서 반환

select 'A'
from instructor

 

 

하나의 열과 437인 행 하나를 반환.

select '437'

 

 

이렇게 계산식을 이용할 수도 있음.

select ID, name, salary/12
from instructor

 

 

<where 절>

• 관계 대수의 selection predicate와 대응
• 여러 튜플들로 비교할 수 있음

where (instructor ID , dept_name ) = (teaches ID , 'Biology')

 

이런 것도 가능

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

 

 

<from 절>

• 관계 대수의 Cartesian product 연산과 대응

 

 

<self join>

자신의 테이블과 조인

 

예) Bob의 supervisor의 supervisor 찾기

select s.supervisor
from emp-super as T, emp-super as S
where T.person='Bob' and T.supervisor=s.person;

 

 

<문자 연산>

• % : 여러개의 모든 문자열 가능
• _ : 모든 문자 하나 가능
• \ : 특수문자를 사용하고 싶을 때 특수문자 앞에 사용

 like '100\%' escape '\'

• || : 문자열 합치기

 

 

<Set 연산>

• union, intersect, except
• 집합 연산이므로 자동으로 중복은 제거됨
• 중복을 포함하고 싶다면 뒤에 all 붙이면 됨.
  -> union all, intersect all, except all 

 

 

<집계 함수: Aggregate Functions>

• 집계함수 내에도 distinct 키워드 사용 가능

select count (distinct ID)
from teaches
where semester = 'Spring' and year =2018;

• select절에서 집계함수로 쓰인 칼럼명을 제외하고
  모든 칼럼은 group by로 묶어줘야 함.

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

 

-> 따라서 이 예제는 에러 발생.
그러나 시스템마다 다름.

• having 절은 집계함수로 만들어진 항목중에서 해당 조건으로 걸러줌.
  where절은 먼저 조건으로 걸러주고 집계함수로 거름.

 

 

< With 절>

• 임시 릴레이션을 만드는 명령어
  -> as 뒤에 들어갈 것들을 적음

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

 

이름은 max_budget이고 value가 들어갈 릴레이션을 만듦.

value안에는 department 릴레이션에서 budget의 최댓값이 들어감.

 

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)

 

위처럼 여러개를 한 번에 만들 수도 있음.

 

 

<Scalar Subquery>

• select절에 쓰이는 서브쿼리
  -> 결과로 값 하나가 나와야 함.
• 결괏값이 하나보다 많다면 런타임 에러 발생.

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

 

 

<삭제>

• 조건을 주며 원하는 행만 삭제 가능.
• 모든 행을 삭제하는 것도 가능.

delete from instructor;

 

 

<삽입>

insert into course
	values ('CS 437', 'Database Systems', 'Comp. Sci.', 4);
# 모든 값을 다 삽입할 땐 이렇게

insert into course(course_id , title , dept_name)
	values ('CS 437', 'Database Systems', 'Comp. Sci.');
# 원하는 열만 삽입하고 싶을 땐 이렇게

insert into student
	values ('3003', 'Green', 'Finance', null);
# 모든 열을 삽입하지만 넣고 싶지 않은 값은 null로 기입

 

 

<수정>

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

 

위처럼 쓰면 순서가 중요해진다. 

순서를 신경 쓰고 싶지 않다면 case 문 사용하기.

 

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

 

 

update문의 set절에도 스칼라 서브쿼리 사용 가능.

update student S
set tot_cred = (select sum (credits)
	from takes, course
	where takes.course_id = course.course_id );