[데베시] 9장 복잡한 SQL

복잡한 SQL

- Nested Queries

- Aggregate Functions

- Grouping

 

Nested Queries(중첩 쿼리)

- 하나의 쿼리의 WHERE절 안에 또다른 완전한 하나의 쿼리가 있는 경우.

- WHERE 절 안에 있는 걸 'Inner query', 밖에 있는 걸 'Outer query'라고 함.

- multiple level로 중첩 가능 - but 남용하면 perfomance 문제가 발생됨.

- nested queries는 항상 un-nested(중첩 해제)할 수 있다. 

 

Q : last name이 Smith인 사람이 직원으로 참여하는 프로젝트이면서 프로젝트의 담당 부서의 매니저로서 참가하는 모든 프로젝트의 번호를 출력하시오.

A : SELECT DISTINCT PnumberFROM PROJECTWHERE Pnumber IN

(SELECT DISTINCT Pnumber

FROM PROJECT, DEPARTMENT, EMPLOYEE

WHERE Dnum=Dnumber AND Mgr_ssn=SSn AND Lname='Smith')

OR Pnumber IN

(SELECT DISTINCT Pnumber

FROM PROJECT, WORKS_ON, EMPLOYEE

WHERE Pnumber=Pno AND Essn=SSn AND Lname='Smith');

 

비교 연산자

- IN Operator

  - value v와 set of values V의 관계에서 v가 V의 원소라면 TRUE 아니라면 FALSE

- ANY (or SOME) Operator

  - v = ANY( or SOME) V 는 v IN V과 동일한 연산자 (v가 어떤 V의 임의의 하나의 원소랑 같다면)

  - 대신 > ANY, >= ANY, < ANY, <= ANY, <> ANY 라면 의미가 있음.

 - ALL Operator

  - v = ALL V (v가 V의 모든 원소와 같다면) <- 잘 안쓰임

  - 다른 비교 연산자와 같이 쓰임. ex) v > ALL V (v가 V의 모든 원소보다 크다면 TRUE)

Q : Dnumber가 5인 부서의 모든 사원보다 연봉이 높은 사람의 이름을 구하시오.

A :

SELECT Lname, Fname

FROM EMPLOYEE

WHERE Salary > ALL (SELECT Salary FROM EMPLOYEE WHERE Dno=5);

 

모호성 피하기 (Avoiding Ambiguities)

- Attribute name에서 모호성 발생 가능 -> Inner query의 FROM 절 내에 있는 Table의 Attribute와 Outer query의 Table의 Attribute의 이름이 같다면? -> 어느쪽의 Attribute name인지 알 수 없음.

-> Reference rule : 아무런 명시가 없을 때라면 가장 내부에 있는 쿼리(innermost nested query)의 relation의 attribute로 간주.

Q : Employee와 해당 Employee의 Dependent 중 First name과 Sex가 같은 Employee가 있을 때 해당 Employee의 이름을 출력하시오.

A1 :

SELECT E.Fname, E.Lname

FROM EMPLOYEE AS E

WHERE E.Ssn IN

(SELECT Essn

FROM DEPENDENT AS D

WHERE Fname=Dependent_name AND E.Sex=D.Sex); <- Qualified Attribute, D.Sex는 D. 생략시 자동으로 DEPENDENT의 Attribute로 고려됨(Reference rule)

 

EXISTS & UNIQUE Functions

- EXISTS Function : nested query의 결과가 비어있다면 FALSE, 1개 이상의 튜플이 있다면 TRUE 반환

Q : 위랑 같은 Query

A2 :

SELECT E.Fname, E.Lname

FROM EMPLOYEE AS E

WHERE EXISTS

(SELECT *

FROM DEPENDENT AS D

WHERE E.Ssn = D.Essn AND Fname=Dependent_name AND E.Sex=D.Sex);

- NOT EXISTS는 반대로 없으면 TRUE 있으면 FALSE

Q : Dependents가 없는 Employee의 이름을 출력

A :

SELECT Fname, Lname

FROM EMPLOYEE

WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE Ssn = Essn);

 

- UNIQUE(Q) Functions :  query Q의 결과에 중복이 있다면 FALSE, 중복이 없다면 TRUE 반환

 

Explicit Sets의 사용 : query의 WHERE절 안에서 explicit set을 만들어 사용 가능

Q : project number가 1, 2, 3 중 하나인 프로젝트에 참가하는 모든 EMPLOYEE의 Ssn을 구하라.

A : SELECT DISTINCT Essn FROM WORKS_ON WHERE Pno IN (1,2,3);

- IS NULL : attribute가 NULL이면 TRUE, 아니면 FALSE

  - 주의할 점 : NULL value끼리는 비교 연산을 할 수 없다.(하나의 NULL이 다른 NULL과 같다고 할 수 없다.) -> JOIN 연산을 할 때 NULL value가 있는 tuple이 포함된 쌍은 생성되지 않는다.

Q : Supervisor가 없는 Employee의 이름을 출력하라

A : SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL;

 

AS - 쿼리 내부에서 Attribute의 이름을 다른 이름으로 rename (원래 table의 이름이 바뀌는 것은 아님)

 

Aggregate Functions

- mulltiple tuples를 single-tuple로 요약한 정보를 보여주는데 사용하는 함수

- Built-in aggregate 함수 : COUNT, SUM, MAX, MIN, AVG

Q : 모든 직원의 salary의 합, 최대 salary, 최소 salary, 평균 salary를 출력하시오

A : SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary) FROM EMPLOYEE;

Q : Reasearch 부서에서 일하는 사람의 salary의 합, 최대 salary, 최소 salary, 평균 salary를 출력하시오

A :

SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)

(EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)

WHERE Dname='Research';

Q : 모든 직원의 수 출력 / Research 팀의 직원 수

A : SELECT COUNT(*) FROM EMPLOYEE; / SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT WHERE Dno=Dnumber AND Dname='Research';

 

그루핑

- relation을 여러 그룹의 tuple들로 나눔. + 각 그룹에 대해 독립적으로 aggregate function을 적용할 수 있음.

- Grouping attributes : grouping의 기준이 되는 attribute. 같은 value을 같은 그룹으로 묶음. 반드시 SELECT 절 안에서 GROUP BY로 정의.

Q : 각 부서마다 부서번호, 부서 직원수, 부서 직원의 평균 연봉을 출력하시오.

A : SELECT Dno, COUNT(*), AVG(Salary) FROM EMPLOYEE GROUP BY Dno;

Q : 각 프로젝트마다 Project number, Project Name, 그 프로젝트에서 일하는 직원의 수 를 출력하시오

A :

SELECT Pnumber, Pname, COUNT(*)

FROM PROJECT, WORKS_ON

WHERE Pnumber=Pno

GROUP BY Pnumber, Pname;

 

- HAVING : GROUP BY와 함께 쓰는 조건절. group에 대한 조건

Q : 2명 초과의 직원이 참가한 프로젝트마다 Project number, Project Name, 그 프로젝트에서 일하는 직원의 수 를 출력하시오

A :

SELECT Pnumber, Pname, COUNT(*)

FROM PROJECT, WORKS_ON

WHERE Pnumber=Pno

GROUP BY Pnumber, Pname

HAVING COUNT(*) > 2;

- WHERE은 tuples을 고르는 조건, HAVING은 groups of tuples를 고르는 조건

 

 

Substring Pattern Matching

- LIKE comparison operator : 패턴 매칭.

- %는 0개 또는 1개 이상의 임의의 문자들 / _ 은 하나의 문자

Q : 주소가 "Houston, Texas"에 있는 직원들을 출력하시오

A : SELECT Fname, Lname FROM EMPLOYEE WHERE Address LIKE '%Houston, TX%";

Q : 1950년대에 태어난 직원을 모두 출력하시오

A : SELECT Fname, Lname FROM EMPLOYEE WHERE Bdate LIKE '__5______';

 

산술 연산자 - +, -, *, /

Q : ProductX의 프로젝트에 참가하는 직원의 이름과 해당 직원의 Salary를 10% 인상하여 추력하시오.

A :

SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal

FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P

WHERE E.Ssn = W.Essn AND W.Pno = P.Pnumber AND P.Pname='ProductX';

 

Ordering of Query Results

- 쿼리 결과를 순서대로 정렬할 때 ORDER BY 절을 이용

- 정렬 순서 - 오름차순 ASC, 내림차순 DESC , Default는 오름차순(ASC)

- 정렬 우선순위 - 여러 정렬 조건이 있을 때 쉼표로 구별하고 앞쪽에 있을수록 우선적으로 정렬

Q : 부서와 직원과 프로젝트명을 출력할 때 부서명, 이름, 성 순으로 오름차순으로 정렬해서 출력하시오

A :

SELECT D.Dname, E.Lname, E.Fname, P.Pname

FROM DEPARTMENT AS D, EMPLOYEE AS E, WORKS_ON AS W, PROJECT P

WHERE D.Dnumber=E.Dno AND E.Ssn=W.Essn AND W.Pno=P.Pnumber

ORDER BY D.Dname, E.Lname, E.Fname;