데이터 베이스(Database) - Introduction to SQL

데이터 베이스(Database) - Introduction to SQL

1. DDL / DML / Integrity / View definition

- DDL (Data-Definition Language)

provides  commands for defining relation schemas (schema를 정의하는 syntax)

- DML (Data-Manipulation Language)

provides the ability to query information from the database

(data 삽입 / 삭제 등 조작하는 syntax)

- Integrity

SQL DDL includes commands for specify integrity constraints that the data must satisfy

(대부분 DDL에서 선언한다.)

- View definition

SQL DDL includes commands for defining views (임시로 사용할 목적인 가상 table)

 

2. SQL Data-Definition Language (DDL)

DDL은 다음 3가지의 정보를 갖는다.

- The schema for each relaiton

- The domain of values associated with each attribute

- Integrity constraints

 

3. Basic Types in SQL DDL

- char(n)

길이가 n으로 고정된 문자열

- varchar(n)

최대 길이가 n인 가변 문자열

- int

정수

- smallint

작은 정수

- numeric(p,d)

전체 자릿수가 p이고 소수점아래 d자릿수인 고정 숫자열

- real, double precision

Floating point and double-precision floating point numbers, with machine-dependent precision

- float(n)

최소 n자릿수인 부동소수점수

- number(n)

전체 자릿수가 n인 고정 숫자열

 

4. Basic Schema Definition - Create Table

- SQL 명령어 사용법 (table 만들기)

create table r ( A1 D1, A2 D2, … An Dn, (integrity-constraint1), … (integrity-constraintk) );

 

ex)

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

※ not null -> Integrity
※ create table, char, varchar, numeric -> keyword

- SQL 명령어 사용법 (data 삽입)

ex)

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

 

5. Integrity Constraint in Create Table

- not null

constraint

- primary key (A1, … , An)

primary key 지정

- foreign key (A1, … , An) references r

relation r을 참조하는 foreign key 지정

ex)

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

※ foreign key (dept_name) references department -> dept_name은 department table의
                                                                               primary key임을 나타냄

 

6. Drop and Alter Table Constructs

- drop table r

table r과 그 안의 모든 데이터를 삭제

- delete from r

table r의 모든 데이터를 삭제하고 table은 그대로 유지

- alter table

alter table r add A D

table r에 도메인이 D인 attribute A를 넣고 A의 모든 tuple들을 null로 셋팅한다.

alter table r drop A

table r에서 attribute A를 삭제한다.

 

7. Basic Sructure of SQL Queries

- SQL query 사용법

select A1,A2,…,An from r1,r2,…,rm where P;

※ A is attribute
    r  is relation
    P is predicate

※ SQL query의 결과는 table (=relation) 이다.

 

8. The select Clause

- select는 relational algebra에서 projection과 대응된다.

- ex)

select name from instructor; // instructor table에서 name attribute만 골라내기

※ SQL의 이름들은 대소문자 구분이 없다. (Name ≡ NAME ≡ name)

- distinct

중복을 제거하는 키워드

select distinct dept_name //중복은 빼라 from instructor;

- all

중복을 포함하는 키워드로써 Oracle에서는 아무것도 쓰지않으면 default all이다.

select all dept_name //중복도 포함해라(생략가능) from instructor;

- asterisk (*)

asterisk (*)는 all attributes라는 뜻이다.

select * //모든 attribute from instructor;

- select절에 수식이 포함되는것도 가능하다.

select ID,name,salary/12 //ID,name과 연봉이 아닌 월급을 골라내기(salary에서 12로 나뉜 값이 출력) from instructor;

 

9. The from Clause

- from은 relational algebra에서 Cartesian product와 대응된다.

select * from instructor,teaches; //instructor와 teaches를 Cartesian product한 table

 

10. The where Clause

- where은 relational algebra에서 selection과 대응된다.

select name from instructor where dept_name = 'Comp. Sci.' and salary > 80000; //dept_name이 Comp. Sci이고 연봉이 80000이상을 selection

 

11. Joins (Cartesion Product)

- 어떤 과목을 가르치는 모든 교수들의 이름과 그들이 가르치는 과목의 ID를 찾아라

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

-  Comp. Sci. department에서 제공되는 강의의 title과 year, semester,
    course ID를 찾아라

select title, year, semester, course_id from section,course where section.course_id = course.course_id and dept_name = 'Comp. Sci';

 

12. Natural Join

- Cartesion Product 후 값이 같은 tuple들만 추려낸 것

- 사용법

select * from instructor natural join teaches;

※ 1) Natural Join시 하나의 table이 생선된다.
    2) Join에 사용된 attribute 중 하나느 사라진다.(중복되기 때문)
    3) Natural Join 결과에 있는 atrribute를 가리키기 위해 원래의 relation 이름을 포함한
        attribute이름 사용 불가(instructor.name 등)

- Example

가르치는 과목의 course ID와 교수의 이름을 찾아라

select name,course_id from instructor natural join teaches;

또는

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

※ 두 query의 결과는 같지만 차이점은?
    일반적인 SQL query의 처리 단계
    1) from절을 먼저 처리하여 새로운 relation을 생성
    2) 1의 과정에서 생성된 새로운 relation에 where절을 적용함
    3) 2의 결과로 얻은 각 tuple들에 대하여 select 실행
    -> Natural Join을 사용한 query에서는 where절이 없으므로 더 효율적이다.

- Natural Join의 주의할 점

attribute의 이름은 같지만 서로 다른 의미로 사용된 경우

ex) 가르치는 과목의 title과 교수의 이름을 찾아라.

<잘못된 query>

select name, title from instructor natural join teaches, course where teaches.course_id = course.course_id;
1) instructor와 teaches의 Natural Join : 공통된 attribute = ID
2) 1의 결과와 course의 Natural Join : 공통된 attribute = course_id, dept_name
    -> course_id 뿐만 아니라 dept_name도 같아야 한다는것을 요구함
    -> 결국 2의 결과는 '교수가 소속된 학과에서 가르치는 과목'만 가져옴
    -> 교수가 다른 학과에서 가르치는 과목은?
    -> instructor에 있는 dept_name과 course에 있는 dept_name은 같은 이름이지만
        의미적으로 다르게 사용돼서 이런 문제가 발생

<올바른 query>

select name, title from (instructor natural join teaches) join course using(course_id);
위의 query는 교수이름과 교수가 강의하는 모든 과목의 과목명을 가져옴.

※ using : Natural Join은 기본적으로 두 relation에 존재하는 모든 공통된 attribute를
               이용하여 Join하기 때문에 Join에 사용할 attribute를 지정하기 위해 사용.

 

13. Rename Operation

- as

old-name as new-name //old-name을 new-name으로 변경

- Example 1

select ID, name, salary/12 as monthly_salary from instructor

- Example 2

Comp. Sci department의 어떤 교수보다 salary가 많은 교수의 이름을 모두 찾아라.

select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = 'Comp. Sci';

※ as는 생략가능하다.(instructor as T ≡ instructor T)
    Oracle에서는 반드시 생략해야함.