[Database] -advancedSql

📕학습목표

• 복잡한 SQL operations
  • null and three-valued logic in SQL
  • Nested queries
  • Correlated nested queries
  • EXITES, UNIQUE functions

 

📕Null as a comparative-value

false> unknown > true

 

• SQL allows queries to check whether an attribute value is NULL
  
  • IS NULL or IS NOT NULL
  • 특정 attribute에 저장된 데이터에 대한 null 여부를 검사
    • IS NULL 
      • null을 포함하는 항을 검색하기 위해 사용
    • IS NOT NULL 
      • NULL을 포함하지 않는 행을 검색하기 위해 사용
  • Null을 "="(equal)를 통해서 비교할 수 없다.
  • = null 의 결과는 항상 unknown이다. 

 

📕Nested Query ( or Subquery)

• query 안에 query가 있는 형태
• UNION과는 다르다. (UNION은 independent queries)

{내부커리}
select Fname, Lname
from employee
where dno in {외부커리} ( select dnumber from department where department.dname='Research');

 

📕Comparison in Nested Queries

 

📗Nested queries

• 다른 쿼리 안에서 완벽한 select-from-where 절이 있는 경우
• 외부 커리와 내부 서브 쿼리 형태

 

📗Comparison operator IN

• v가 V에 원소 중에 하나라면 TRUE를 반환함.

select fname, lname
from employee
where dno in (select dnumber from dept_locations where dlocation='Houston');

• 만약 dno 가 in 안에 있는 쿼리에 속한다면 where 절에 true
• 만약 dno 가 in 안에 있는 쿼리에 속하지 않는다면 where 절에 false를 반환

 

📕Attribute Ambiguity

select fname, lname
from employee
where dno in (select dno from dept_locations where dlocation='Houston');

• 위 쿼리에서 dno 대신 Dnumber를 쓰면 어떻게 될까?
• 위 커리는 where 절을 만족하는 dno를 employee 절에서 뽑는다.
  • (왜냐하면 dept_locations에는 dno가 없기 때문이다. dnumber로 명시되어있다.)
• 만약 dnumber를 쓰게 된다면 dept_location에서 hosuon에 해당하는 dnumber와 employee에 dno가 같은 tuple의 fname과 lname을 뽑아준다.

📕여러가지 tuple이 리턴되는 경우

💡묶어서 가능하다.

select distinct essn 
from works_on
where (pno, hours) in (select pno, hours from works_on where essn = '333445555');

 

📕Nested Queries with Having

• 서브 쿼리들은 Having 안에서도 사용 가능하다.

Select salary, count(*)
from employee
group by salary
having salary < (select salary from employee where fanme='john' and lname='smith');

 

📕Correlated Nested Queries

• 내부 카레에서 외부 카레의 값을 참조할 때이다.
• Correlated Nested Queries는 아웃 쿼리에서 읽은 행을 가지고 실행된다. (일반 서브 쿼리는 inner->outer)

select E.fname, E.lname
from employee as E
where E.ssn in (select D.essn from dependent As D where E fname=D.dependent_name);

• = 또는 in 을 사용해서 더 중첩된 쿼리를 단일 블록으로 축소할 수 있다.

select E.fname, E.lname
from employee as E , dependent as E
where E.ssn = D.essn and E.fname = D.dependent_name;

 

 

📕EXISTS 

• 중첩된 커리문을 하나의 인자로 취급해서 Boolean 연산을 함.
  • 서브 쿼리가 하나라도 아웃풋을 만든다면 exits는 true를 반환
  • 그 반대의 경우 false
  • unknown은 될 수가 없다, 오직 (true or false)

📕EXITST , NOT EXISTS, UNIQUE

• 이러한 연산은 상호연관쿼리를 결합하는 데 사용된다.
• True of False를 반환하는 Boolean functions이다.
• EXISTS는 query가 공집합이 아니라면 true를 반환,
• NOT EXISTS는 query가 공집합이라면 false를 반환
• UNIQUE(Q)
  • query Q에서 복제된 튜플이 없다면 true를 반환

 

📕EXCEPT 

• EXCEPT는 MYSQL에서 지원하지 않는다.

 

📕ALL OPERATOR

• 전체값을 비교해서 만족을 해야 함.

dno가 4인 직원들보다 월급이 많은 사람들의 이름을 검색해라! 인 경우

select DISTINCT fname, salary
from employee, department
where salary > ALL(select salary from employee where dno=4);

더 간단하게 한 경우 모든 월급을 비교할 필요 없이 dno가 4인 직원 중 가장 높은 월급만 비교해도 된다.

select DISTINCT fname, salary
from employee, department
where salary>(select max(salary) from employee where dno=4);

 

📕JOIN Tables

• joined table
  • 사용자가 쿼리의 from 절에서 조인 작업으로 인한 테이블을 지정할 수 있습니다.

select fname, lname, address
from (employee join department On dno= dnumber )
where Dname='Research';

두 쿼리의 결과 값은 같다.

 

📕Types of Join

1. NATURAL JOIN
2. INNER JOIN ( versus OUTER JOIN)
3. LEFT OUTER JOIN
4. RIGHT OUTER JOIN

 

📕Natural Join

• 두 개의 릴레이션 R과 S에 Natural join을 한다고 가정할 때
  • 두 릴레이션의 겹치는 attribute에 동일한 값이 있다면 결과가 나옴.
  • Natural join 은 EQUIJOIN과 암묵적으로 동일하다고 하는 듯(?!)

 

select * 
from R natural Join S;

R과 S에 공통적으로 존재하는 attribute인 DNO을 비교해서 같은 값이 있다면 그 튜플은 전체 출력한다.

즉 서로 DNO가 1인 튜플이 있으므로 그 튜플들을 출력한다.

 

📕Left Outer Join

• 왼쪽에 있는 테이블을 결과로 표시함.
• 일치하는 튜플이 없을 경우 오른쪽 테이블의 값을 null로 설정한다.

모든 직원의 이름과 그들의 상사의 이름을 검색하라!

 

R의 집합을 직원의 집합 S의 집합을 상사의 집합이라고 하자.

R의 super_ssn을 타고 들어가서 s의 fname을 출력하고 R의 fname을 출력할 수 있다.

Select R.fname, S.fname
from employee as R, left outer join employee as S
on R.super_ssn=S.ssn;

 

 

📕Right Outer Join

• 오른쪽 테이블에 있는 모든 튜플이 결과에 나온다.
• 만약 매칭 되는 튜플이 없다면 오른쪽 테이블의 속성은 null로 처리한다.

 

상사의 이름과 그의 부하직원의 이름을 출력해라!

select R.fname, S.fname
from employee R right outer join Employee S
on R.super_ssn=S.ssn;

이렇게 되면 S에 있는 모든 ssn에 대해서 R에 super_ssn에 매핑을 하는데 그 값이 없다면 왼쪽 테이블에는 null값이 들어가게 된다.

 

📕Full Outer Join

• 왼쪽과 오른쪽의 합집합.
• left join과 outer join의 합집합

Select R.fname, S.fname
from employee as R left outer join employee as S
on R.super_ssn=S.ssn;
UNION
select R.fname, S.fname
from employee R right outer join Employee S
on R.super_ssn=S.ssn;

 

📕Aggregate functions

• 다수의 튜플을 하나의 튜플로 요약할 때 사용함.
• 예시
  • Count, Sum, Max, Min, Avg
• Grouping
  • 요약하기 전의 튜플의 서브그룹을 만듦.
• Having
  • 그룹을 선택하는 데 사용됨.
• select 절에 사용되거나 having 절에 사용됨.
• 집계 함수를 열에 적용할 경우 null 값이 삭제됩니다.

select count(*) as Researchers //column 이름이 바뀜.
from employee, department
where dno= dnumber and dname ='Research';

 

📕Group by 

• Partition relation into subset of tuples
  • attributes를 기반으로 그룹화함.
  • 각 그룹의 독립적으로 function을 적용함.
• Group by clause
  • 그룹화할 attribute를 지정함.
• Count(*)
  • group에 row의 숫자를 셈.

각 부서별로 직원의 수와 평균 임금을 구해라.

select Dno, count(*), avg(salary)
from employee 
group by dno;

 

Having과 함께 쓴 group by

각 프로젝트 별로 참여하는 직원이 3명 이상인 프로젝트들에 대해서 pnumber, pname, 참여 직원 수를 구해라!

select pnumber, pname, count(*)
from project,works_on
where pnumber=pno
group by pnumber
having count(*)>2;

 

📕TRIGGER logic

• follow the Event-Conditon-Action model
  • Event : 트리거가 실행되는 동작
    • insert, update , delete
    • Time
      • Before : Event 전에 트리거가 검사를 함.
      • After  : Event 후에 트리거가 검사를 함.
  • Condition (optional) : 조건을 충족하면 트리거가 실행됨.
  • Action : 실행되는 sql 문

직원의 월급은 상사의 월급보다 많을 수 없다.

jaehan의 월급은 80000이고 상사의 월급은 30000이라 insert 전에 trigger가 검사를 해서 insert를 거부했다.

 

📕Views(Virtual Tables) in SQL

• 다른 테이블에서 파생된 테이블
• 가상 테이블로 간주.
• Create View command
  • give table name, list of attribute names, 내용을 특정하는 쿼리로 구성.
• Drop View <view_name>
  • Drops the view
• view가 정의되어있다면, sql 쿼리에 from절에서 view를 사용 가능하다.
• view는 항상 업데이트된다.