데이터베이스시스템 #6 - Advanced Queries

1. Advanced DDL

참조적 행동

• UPDATE 또는 DELETE 작업은 하위 관계에 일치하는 튜플이 있는 상위 관계의 기본 키 값에 영향을 줄 수 있다.
• 자신을 참조하는 관계가 있는 경우 액션을 어떻게 할지 정해줘야 함.
• UPDATE 및 DELETE의 결과는 FOREIGN KEY 제약 조건의 참조 작업에 따라 다르다.

DELETE

• ON DELETE CASCADE
• ON DELETE NO ACTION
• ON DELETE RESTRICT
• ON DELETE SET NULL
• ON DELETE SET DEFAULT

UPDATE

• ON UPDATE CASCADE
• ON UPDATE NO ACTION
• ON UPDATE RESTRICT
• ON UPDATE SET NULL
• ON UPDATE SET DEFAULT

CASCADE

• 부모 관게의 튜플을 삭제하거나 업데이트 시, 부모관계를 참조하고 있는 자식 관계의 튜플을 자동으로 같이 삭제하거나 업데이트 시킨다.

SET NULL

• 부모 관계의 튜플이 삭제하거나 업데이트 시, 자식 관계의 foreign key 속성을 NULL로 만든다.

RESTRICT = NO ACTION

• 부모 관계에 대한 삭제나 업데이트 작업을 거부한다.

+현재 SET DEFAULT는 지원하지 않는다.

2. Advanced DML

Join Query

• 그들 사이의 관련 속성을 기반으로 두 개 이상의 관계에서 튜플을 결합하는 데 사용된다.

SELECT *
FROM employee, department
WHERE department.deptno = employee.dno;

DNO와 DEPTNO가 같은 것을 찾아 양쪽의 정보를 합쳐서 보여주는 형태

SELECT d.deptname, e.empname, e.title, e.salary
FROM employee AS e, deparment AS d
WHERE e.dno=d.deptno
ORDER BY deptname, salary DESC;	 -- deptname을 정렬한 것 중에서 또 salary를 정렬

Self-join

• 관계에 속한 튜플을 동일한 관계에 속한 튜플과 결합시킨다.
• 두 관계가 FROM 문의 참조임을 나타내려면 관계에 대해 두 개의 별칭을 지정해야 한다.

SELECT e.empname, m.empname
FROM employee AS e, employee AS m
WHERE e.manager=m.empno;

다른 타입의 join쿼리

• JOIN : 두 테이블에서 일치하는 값이 있는 레코드를 반환한다.
• LEFT JOIN : 왼쪽 테이블의 모든 레코드를 반환하고 오른쪽 테이블의 일치하는 레코드를 반환한다.
• RIGHT JOIN : 오른쪽 테이블의 모든 레코드를 반환하고 왼쪽 테이블의 일치하는 레코드를 반환한다.
• CROSS JOIN : 두 테이블의 모든 레코드를 반환한다.
  
  

Nested Query

• WHERE 문에 포함된 SELECT 문
• 중첩 쿼리의 결과
  • 스칼라 값(단일 값)
  • 속성이 하나인 관계
  • 여러 속성을 가진 관계

스칼라 값(단일 값)

SELECT EMPNAME, TITLE
FROM EMPLOYEE
WHERE TITLE=(
	SELECT TITLE
    	FROM EMPLOYEE
    	WHERE EMPNAME='박영권');  => '과장'만 리턴함 (단일 값)

(
SELECT TITLE
FROM EMPLOYEE
WHERE EMPNAME='박영권') 가 단일 값을 리턴

• 박영권과 같은 직책을 가진 모든 직원의 이름과 직위를 검색

속성이 하나인 관계

• 하나의 속성을 가진 관계가 반환될 때 WHERE 문에 IN, ANY, ALL 연산자를 사용해라.

SELECT EMPNAME
FROM EMPLOYEE
WHERE DNO IN (
	SELECT DEPTNO		=> DEPTNO 속성 하나만 가진 관계가 리턴
    	FROM DEPARTMENT
    	WHERE DEPTNAME='영업' OR DEPTNAME='개발');  => (1, 3) 가 리턴

• 영업 또는 개발 부서에서 일하는 직원의 이름 검색

여러 속성을 가진 관계

• 속성이 여러 개인 관계가 반환되는 경우 WHERE 문에서 EXIST 연산자를 사용해라.

SELECT EMPNAME
FROM EMPLOYEE E
WHERE EXISTS
	(SELECT *		=> * : 속성이 여러 개인 관계가 리턴됨
	FROM DEPARTMENT D
	WHERE E.DNO=D.DEPTNO
		AND (DEPTNAME='영업' OR DEPTNAME='개발')); 

• 영업 또는 개발 부서에서 일하는 직원의 이름 검색