SQL(1)

Data Definition Language (DDL)

The SQL DDL allows the specification(명세) of information about relations

• the schema for each relation
• the type of values associated with each attribute
• the Integrity constraints
• the set of indices to be maintained for each relation
• security and authorization information for each relation
• the physical storage structure of each relation on disk

Domain types

• char(n) : fixed length character string
• varchar(n) : variable length character string
• int
• smallint
• numeric(p, d)
• real, double precision
• float(n) : at least n digits

create table

create table instructor(
	ID char(5)
	name varchar(20),
	dept_name varchar(20),
	salary numeric(8, 2)
)

Integrity constraints (무결성 제약조건)

• primary key
• foreign key references r (또 다른 table)
• not null

  create table instructor(
  	ID char(5),
     name varchar(20) not null,
     dept_name varchar(20),
     salary numeric(8, 2),
     primary key(ID),
     foreign key(dept_name) references department
  );

### Insert

```sql
insert into instructor values ('10211', 'Smith', 'Biology', 66000);

Delete

delete from student

Remove all tuples from the student relation.
-> Empty table이 되기 때문에 insert 가능하다

Drop

drop table r

table 자체가 없어지기 때문에 insert 불가능.

Alter

table의 스키마를 바꾼다. 그렇게 바람직한 방법은 아니다.

alter table r add A D

A : name of the attribute to be added to relation r
D : domain of A

• domain : 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합
  (student grade - type : 정수형, domain : 1~4)
  All exiting tuples in the relation are assigned null as the value for the new attribute

alter table r drop A
// 위험성이 높기 때문에 대부분의 DB에서 지원하지 않는다.

A : name of an attribute of relation r

---

Basic Query Structure

A typical SQL query has the form :

``` sql
	select A1, A2, ,,, An
    from r1, r2, ,,, rm
    where P
  ```

Ai : attribute
ri : relation (관계형 DB에서 table = relation)
P : predicate (조건)

The result is a relation. 결과도 테이블이다.

The select Clause

The select clause lists the attributes desired in the result of a query

// find the name of all instructors
select name
from instructor

SQL names are case insensitive(대소문자 구분이 없다)
NAME ≡ name ≡ Name

select를 그냥 사용하면 중복된 데이터들이 그대로 출력된다. 이를 삭제하기 위해서 distinct를 사용한다.

// find the department names of all instructors, and remove duplicates
select distinct dept_name
from instructor

반대로 all을 사용하면 중복된 데이터들이 삭제되지 않는다.

select all dept_name
from instructor

asterist를 사용하면 모든 속성을 다 모은다

select *
from instructor

from을 사용하지 않고 attribute를 literal로 쓸 수 있다

// a table with one column and a single row with value '437'
select '437'

// give the column name 
select '437' as FOO

from을 사용하고 attribute를 literal로 쓸 수 있다. 별로 의미는 없다

// a table with one column and N rows(number of tuples in instructor table), each row with value 'A'
select 'A'
from instructor

select clause(절)에 arithmetic expression(+, -. *, /)을 사용할 수 있다.

// salary는 연봉이니까 월급을 보고 싶을 때
select ID, name, salary/12
from instructor
// salary/12 이름이 별로면 rename 할 수 있다
select ID, name, salary/12 as monthly_salary

The where Clause

The where clauses specifies conditions that the result must satisfy.

// To find all instructors in Computer Science dept
select name
from instructor
where dept_name = 'Comp.Sci.'

logical connectives(and, or, not)과 comparison operator(<, <=, =, >=, >, <>)를 사용할 수 있다. Comparisons can be applied to results of arithmetic expressions

// To find all instructors in Comp.Sci. dept with salary > 7000
select name
from instructor
where dept_name = 'Comp.Sci' and salary > 7000

The from Clause

The from clause lists the relations involved in the query

// Find the Cartesian product instructor x teaches
select *
from instructor, teaches
// 모든 (instructor, teaches) pair를 모은다. 
// ID와 같은 attribute는 이름은 relation name을 사용하여 rename된다. (e.g. instructor.ID)

Examples

1. Find the names of all instructors who have taught some course and the course_id.

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID

2. Find the names of all instructors in the Art department who have taught some course and the course_id

select name, course_id
from instructor
where instructor.dept_name = 'Art'