[강의노트_DB]12. SQL-2

INSERT, DELETE, UPDATE문

INSERT문은 기존의 릴레이션에 투플을 삽입하는 질의이다. 참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나 참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있다. 

 

INSERT

INTO 릴레이션(애트리뷰트1, ...., 애트리뷰트n)

VALUES (값1, ... , 값n);

 

DELETE문은 한 릴레이션으로부터 한 개 이상의 투플들을 삭제한다. 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않음

DELETE FROM 릴레이션 WHERE 조건;

 

UPDATE문은 한 릴레이션에 들어 있는 투플들의 애트리뷰트 값들을 수정한다. 기본 키나 외래 키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있다.

UPDATE 릴레이션

SET 애트리뷰트 = 값 또는 식[ , ...]

WHERE 조건;

 

트리거와 주장

트리거란 명시된 이벤트가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문이다. 데이터베이스의 무결성을 유지하기 위한 도구이며, 트리거를 이벤트-조건-동작(Event-Condition, Action) 규칙이라고도 부른다.

CREATE TRIGER <트리거 이름>

AFTER <트리거를 유발하는 이벤트> ON <릴레이션> [WHEN <조건>]

BEGIN <SQL문(들)> END;

 

이벤트의 가능한 예는 테이블에 삽입, 삭제, 수정등이 있다.

 

주장이란 제약조건을 위반하는 연산이 수행되지 않도록 하는 것이다.

CREATE ASSERTION 이름 CHECK 조건;

일반적으로 두 개 이상의 테이블에 영향을 미치는 제약조건을 명시하기 위해 사용된다.

 

SQL문 실습

1. 급여가 $12,000를 넘는 사원의 이름과 급여를 표시

 

SELECT last_name, salary FROM employees WHERE salary>=12000; 

- 급여가 $12,000를 넘어야 하므로 이를 위한 WHERE 조건절을 추가하였다.

 

2. 사원 번호가 176인 사원의 이름과 부서 번호를 표시

 

SELECT last_name,department_id FROM employees WHERE employee_id=176;

- 사원 번호가 176이어야 하므로 이를 위한 WHERE 조건절을 추가하였다.

 

3.급여가 $5,000에서 $12,000 사이에 포함되지 않는 모든 사원의 이름과 급여를 표시

 

SELECT last_name,salary FROM employees WHERE salary<5000 or salary > 12000;

- 5,000미만 12,000초과이므로 or로 조건을 엮어주었다.

 

4. 2007년 2월 20일과 2007년 5월 1일 사이에 입사한 사원의 이름, 업무 ID 및 시작일을 표시하되, 시작일을 기준으로 오름차순으로 정렬하는 질의

 

SELECT last_name,job_id,hire_date FROM employees WHERE hire_date between DATE'2007-02-20' and DATE'2007-05-01' ORDER BY hire_date;

-날짜는 DATE'YYYY-MM-DD'와 같은 꼴로 사용을 해야한다. 따라서 두 범위를 between으로 나타내었고, 이를 order by로 정렬하였다.

 

5. 부서 20 및 50에 속하는 모든 사원의 이름과 부서 번호를 이름을 기준으로 영문자순으로 표시

 

SELECT last_name,department_id FROM employees WHERE department_id=20 or department_id=50 ORDER BY last_name;

-부서 번호 20, 50에 속하는 사원들을 출력해야 해서 WHERE문을 사용했고, 영문자순으로 표시해야하므로 order by를 사용했다.

 

6.급여가 $5,000와 $12,000 사이이고 부서 번호가 20 또는 50인 사원의 이름과 급여를 나열하고, 열 레이블을 Employee와 Monthly Salary로 각각 지정

 

SELECT last_name "Employee", salary "Monthly Salary" FROM employees WHERE (salary between 5000 and 12000 ) and (department_id=20 or department_id=50);

- 열 레이블을 지정할 때 As를 사용하긴 하지만 위와 같이 생략을 했다. 위에서 계속 사용했던 조건들을 and를 사용해서 동시에 해당하도록 하였다.

7.1994년에 입사한 모든 사원의 이름과 입사일을 표시

SELECT last_name,hire_date FROM employees WHERE hire_date between DATE'1994-01-01' and DATE'1994-12-31';

- 1994년만 지정해주려고 했으나 딱히 떠오르지 않아 1월 1일부터 12월 31일까지 범위로 진행하였다.

 

8.관리자가 없는 모든 사원의 이름과 업무 ID를 표시

 

SELECT last_name,job_id FROM employees WHERE manager_id is NULL;

- 관리자 없다는 것은 관리자 번호가 NULL임을 의미한다.

 

9.커미션을 받는 모든 사원의 이름,급여 및 커미션을 급여 및 커미션을 기준으로 내림차순으로 정렬하여 표시

 

SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct is not NULL ORDER BY salary,commission_pct DESC;

- 커미션을 받지 않는 사람들은 NULL로 표시되어 있기에 is not NULL로 값을 찾았고 내림차순으로 정렬해야 했으므로 DESC를 사용했다.

 

10.이름의 세 번째 문자가 a인모든사원의이름을표시

 

SELECT last_name FROM employees WHERE last_name like '__a%';

- 문자열 매칭에서 %는 여러개를 뜻하고, _는 한 글자를 뜻한다.

 

11.이름에 a와 e가 있는 모든 사원의 이름을 표시

 

SELECT last_name FROM employees WHERE last_name like '%a%' and last_name like '%e%';

-이름에 a나 e가 있기만 하면 되므로 양 쪽에 %를 사용해서 찾았다.

 

12. 업무가 영업 사원(SA_REP)또는 사무원(ST_CLERK)이면서급여가 $2,500, $3,500 또는$7,000가 아닌 모든 사원의 이름, 업무 및 급여를 표시

 

SELECT last_name,job_id,salary FROM employees WHERE job_id='SA_REP' or job_id='ST_CLERK')  and (salary not in (2500,3500,7000));

- 급여의 조건이 2,500 , 3,500 , 7,000과 같이 되어 있으므로 in을 사용해서 조건을 주었다.

 

13.커미션 비율이 20%인 모든 사원의 이름, 급여 및 커미션을 표시

 

SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct=0.2;

-커미션 비율이 20%라는 것을 where 절에 주도록 한다.