* 12.18 업데이트
해당 게시물은 한양대학교 컴퓨터소프트웨어학부 김상욱 교수님 데이터베이스시스템 온라인 강의를 듣고 정리한 자료입니다.
오류가 있다면 언제든 알려주세요!
하하 다시 오랜만입니다... 여러모로 바쁜 시기라서 늦게 왔어요>!ㅠㅠ 다시 시작해 봅시당...
● 해당 강의의 목표
1. 복잡한 SQL query에 대해 배운다.
▪ Nested queries
▪ Aggregate functions
▪ Grouping
◆ Nested Queries
▪ Query: DBMS한테 내가 원하는 것이 이런 것인데 가져다달라고 질의하는 것
Nested Queries: Query 안에 query가 있다. 즉, 하나의 select-from-where라는 완전한 형태의 block이 다른 query의 WHERE clause안에 존재하는 것이다. 이때, WHERE 절 안에 있는 query를 inner query, 바깥 쪽에 있는 query를 outer query라고 한다.
▪ nested query의 multiple level을 허용한다.
여러 level의 nesting이 가능하지만 nested query를 처리하는데 상당한 performance problem, overhead 크기 때문에 남용하면 안 된다. 이는 recursion 개념과 비슷하게 생각하면 된다. 개념적으로 유용하지만 성능문제가 있기 때문에 성능이 빠른 것을 원한다면 최대한 피하는 쪽으로 사용한다.
▪ Database로부터 data를 꺼내는데 사용하는 Inner query에서 꺼낸 data를 이용해서 Outer query가 어떤 것을 처리하는 데에 유용하게 사용하기 위해서 Nested Query를 사용한다.
▪ 항상 피하는 쪽이 가능한가? YES! 항상 nested query를 펼칠 수 있다. 그러면 이게 왜 필요한가? recursion이랑 똑같다.
◆ Query 4. Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker on the project or as a manager of the department that controls the project
last name이 Smith인 employee와 관련된 project의 number를 보여줘라. 이때 관련되었다는 뜻은 1. worker로서 project에 참여했거나 2. 그 project를 control하는 department의 manager이라는 뜻이다.
Project의 어떤 tuple이 Pnumber가 1번 혹은 2번 조건을 만족하게 되면 보여줘라. DISTINCT는 중복되어서 나올 수 있으니 한 번만 보여줘라라는 뜻이다. WHERE 절 안에 full select statement가 있으니 nested query가 된다.
1번 Nested Query: table 3개가 2개의 join으로 만나고 마지막으로 Lname이 Smith인 사람을 찾는다. 이는 Smith라는 사람이 manager인 department가 control하는 project를 골라내는 query이다. 따라서 Smith가 manager인 department에서 control하는 project들의 number가 집합으로 나온다.
2번 Nested Query: 어떤 employee가 어떤 project에서 일하는지를 나타내는 join query 중 Smith가 참여하는 것에만 관심있다. 따라서 Smith가 참여하는 project의 number가 집합으로 나온다.
IN은 Pnumber가 찾는 tuple에 member로 있냐를 묻는 것이다.
◆ Comparison Operators
▪ IN operator
어떤 value v와 어떤 집합 V 간의 관계에 의해서 T/F를 return하는 operator이다. True가 return되면 v가 집합 V에 속해있다는 뜻이다. (v IN V)
▪ = ANY (or = SOME) operator
value v가 집합 V의 하나의 value와 값이 같으면 TRUE를 return한다. IN과 동일한 의미이다.
따라서 = ANY보다는 >, >=, <, <=, and <>와 같은 operator들을 사용한다.
▪ = ALL operator
value v가 집합 V의 모든 value와 값이 같으면 TRUE를 return한다. 다만, 집합에 1, 2, 3이 있고 어떤 value v가 있을 때 어떻게 하나의 value가 1, 2, 3과 모두 같을 수 있을까!!
따라서 = ALL은 거의 쓰지 않고 다른 operator >, >=, <, <=, and <>로 바꿔서 사용한다.
> ALL은 집합에 있는 max 값보다 크면 True가 return 되고 > ANY는 집합에 있는 min 값보다 크면 True가 return 된다.
좀 헷갈려서 지피티한테 물어봤다. ㅎㅎ...
- > ALL:
- > ALL은 특정 값이 집합 내 모든 값보다 커야 할 때 사용합니다.
- 예를 들어, X > ALL (SELECT age FROM users)라는 조건이 있다고 해봅시다. 이 조건은 X가 users 테이블에 있는 모든 age 값보다 클 때 True가 됩니다.
- 즉, X가 집합에 있는 가장 큰 값(max 값)보다 크면 True를 반환하게 됩니다.
- > ANY:
- > ANY는 특정 값이 집합 내 일부 값보다 크면 True가 됩니다.
- 예를 들어, X > ANY (SELECT age FROM users)라는 조건이 있을 때, 이 조건은 X가 users 테이블의 어떤 age 값이라도 넘을 때 True가 됩니다.
- 즉, X가 집합에 있는 최소 값(min 값)보다 크면 True가 됩니다.
정리하자면:
- > ALL은 모든 값보다 커야 하기 때문에 집합에서 가장 큰 값보다 크면 True.
- > ANY는 최소한 하나의 값보다 크면 되기 때문에 집합에서 가장 작은 값보다 크면 True.
◆ Query A. Search for the names of employees whose salary is greater than the salary of all the employees in department 5
employee name을 뽑을 건데 그 사람의 salary가 5번 department에서 일하는 모든 employee의 연봉보다 클 때 뽑아줘라.
inner query에서는 5번 department에서 일하는 사람들의 salary를 반환할 것이다.
◆ Avoiding Ambiguities (모호성을 제거하라)
▪ 모호성은 attribute name에서 발생한다.
inner query, outer query에서 모두 FROM 절이 있기 때문에 그 절에 속한 table 내의 attribute 이름이 같을 수 있다. 따라서 inner query, outer query 중 어느 table에 있는 attribute name인지 모호하다는 것이다.
▪ Reference rule로 해소한다.
아무 얘기를 하지 않고 attribute을 썼을 때 참조하는 쪽의 가장 가까이 있는 query에 있는 table의 attribute으로 생각하면 된다. 만약 A라는 attribute을 썼는데 이를 사용하는 곳이 inner query면 inner query의 table에 있는 속성이고, outer query이면 outer query의 table에 있는 속성을 지칭하는 것으로 취급한다.
예를 들어보자!
◆ Query 16. Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee
이름과 성별이 같은 dependent를 가지는 employee의 이름을 보고 싶다.
A: 해당 query 안에서 EMPLOYEE를 E로 rename하겠다. 진짜 table의 이름이 바뀌는 것은 아니다!
Ssn이 하나라서 E.Ssn이라고 하지 않아도 된다. 하지만 성별은 Sex = Sex을 하면 어디의 attribute인지 몰라서 qualify를 해주어야한다. E.Sex = Sex는 가능하다. 가장 안 쪽의 table을 따르기 때문에 D임을 바로 인지 가능하지만 E는 가장 바깥쪽의 table을 참조하는 것이기 때문에 이를 적어놓지 않으면 안 된다.
◆ EXISTS and UNIQUE Functions
▪ EXISTS function
관련된 query에 의해서 return 되는 집합이 비어있는지 아닌지를 check하는 함수이다.
만약 nested query의 결과로 적어도 하나 이상의 tuple이 존재한다면 TRUE를 반환하고 아예 tuple이 없다면 FALSE를 반환한다.
◆ Query 16. Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee
이름과 성별이 같은 dependent를 가지는 employee의 이름을 보고 싶다.
join을 나타내기 위해서 E.Ssn = D.Essn이 들어간다. 이에 해당되는 tuple이 존재한다면 답이 된다.
▪ NOT EXISTS function
EXISTS function과 반대의 결과를 return한다.
만약 nested query의 결과로 tuple이 존재하지 않는다면 TRUE를 반환하고 적어도 하나 이상의 tuple이 존재하면 FALSE를 반환한다.
◆ Query 6. Retrieve the names of employees who have no dependents
dependent가 없는 그런 employee의 tuple을 알고싶다.
nested query가 empty이면 답이 되고 empty가 아니면 답이 안 되는 case이다. 존재하지 않으면 dependent가 존재하지 않는 employee tuple들을 반환한다.
와닿지 않아서 다시 찾아보았다.
WHERE NOT EXISTS: NOT EXISTS는 서브쿼리의 결과가 없을 때만 참이 된다. 즉, 조건을 만족하지 않는 행만 선택한다.
위의 예시에서는 DEPENDENT가 없는 직원의 이름과 성을 찾는 것이다.
▪ UNIQUE Function
nested query Q 안에 duplicated tuple이 존재하는지 여부를 check한다.
duplicate tuple이 존재하지 않는 경우에는 TRUE, 존재하지 않는 경우에는 FALSE를 return하는 function이다.
▪ Explicit Sets and NULL
query안에서 explicit set을 만들어 사용할 수 있다.
WHERE clause 안에 explicit value들을 만들어서 set을 만들고 그 set을 이용한 조건을 넣을 수 있다.
예를 들어보자.
◆ Query 17. Retrieve the social security numbers of all employees who work on project numbers 1, 2, or 3
WORKS_ON이라는 table 내에 attribute Pno가 있을 때 각 tuple의 Pno value가 집합 (1, 2, 3)의 원소인 tuple을 보내고 tuple의 Essn을 보여줘라.
WORKS_ON에는 essn과 pno의 쌍이 있다. 그 중에 project number가 1, 2, 3이라는 뜻은 그 project이 1, 2, 3 중 하나인 경우에 그러한 essn을 보여줘라. 그 뜻은 project 1, 2, 3에서 일하는 employee의 Ssn을 보여달라는 뜻이다. DISTINCT가 있다는 것은 어떤 사람이 1, 2에서 모두 일하면 Ssn이 똑같은 tuple이 두 번 나올 것이기 때문에 중복을 제거하라는 뜻이다.
◆ IS NULL function
결과로 나오는 attribute의 value가 NULL인지 check한다.
만약 NULL이라면 TRUE, NULL이 아니면 FALSE를 반환한다.
▪ 어떤 attribute value가 NULL이고 다른 attribute value가 NULL이면 이 둘이 같은 걸까? 아니다.
NULL끼리는 비교하는 것이 무의미히다. 특히 JOIN할 때 NULL value를 갖는 tuple 간의 join이 결과로 형성되지 않고 모두 제거된다.
◆ Query 18. Retrieve the names of all employees who do not have supervisors
supervisor가 없는 employee의 이름을 보여줘라.
◆ Renaming Attributes
▪ AS라는 qualifier를 사용하면 table의 이름을 rename할 수 있었다. query안에서 attribute의 이름을 바꾸고 싶을 때는 AS 다음에 새로운 이름을 붙여주면 된다.
1. query안에서 새로운 이름으로 쓸 수 있다.
2. query 결과가 보여질 때 그 attribute name으로 결과가 나온다.
단, 원래 table의 attribute 이름이 바뀌는 것은 아니다.
◆ Query 8. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor
각각의 employee에 대해서 본인의 Fname, Lname과 자기 상사의 Fname, Lname의 쌍을 보여줘라.
쌍을 찾으려면 EMPLOYEE와 EMPLOYEE를 JOIN 해야한다. 따라서 하나의 table이 두 개의 역할로 참여했기 때문에 이름을 rename해야한다. 부하직원의 Super_ssn이 상사 직원의 Ssn과 같으면 된다.
이 결과는 Employee_name, Supervisor_name으로 나온다.
FROM EMPLOYEE AS E(ssn) 이런식으로 attribute name을 rename할 수 있다.
◆ Aggregate Functions
◆ Select query의 결과는 기본적으로 tuple들의 집합으로 결과가 나오게 된다. 즉, query의 결과가 table이다. aggregate function은 그렇게 나오는 tuple을 summarize해서 하나의 tuple로 결과를 내는 함수이다.
◆ SQL에서는 built-in aggregate function을 기본적으로 제공한다.
▪ COUNT, SUM, MAX, MIN and AVG
▪ 이 aggregate function의 대상은 query의 결과로 나타나는 tuple들의 집합이다. 이때 COUNT는 결과로 나오는 tuple의 수를 return해주는 function이다.
▪ SUM, MAX, MIN and AVG는 결과로 나오는 tuple들의 어떤 특정한 attribute의 합, max value, min value, avg value를 각각 return 해주는 function이다.
◆ Query 19. Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary
employee의 salary의 합, 최댓값, 최솟값 그리고 평균값을 찾아라.
조건 없이 모든 EMPLOYEE를 대상으로 하는 것이기 때문에 WHERE clause는 없다.
◆ Query 20. Find the sum of the salaries of all employees of the ‘Research’ department, as well as the maximum salary, the minimum salary, and the average salary in this department
Research department에서 일하는 Employee들의 salary sum, max, min, avg를 구해라.
JOIN할 때 뒤에 ON Dno = Dnumber가 허용된다. FROM에서 제외하고 WHERE Dname = 'Research' AND Dno = Dnumber로 작성도 가능하다.
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM EMPLOYEE, DEPARTMENT
Where Dno = Dnumber AND Dname = 'Research';
◆ Query 21 and 22. Retrieve the total number of employees in the company (Q21) and the number of employees in the ‘Research’ department (Q22)
count는 select의 결과로 return되는 tuple의 개수를 return한다.
EMPLOYEE의 table에서 조건없이 COUNT를 return했다. 이는 모든 EMPLOYEE의 수를 조사하는 것이다.
Research department에 속하는 tuple들의 count를 보기위한 query이다. Research department에 몇명의 employee가 일하는지 보는 query이다.
◆ Grouping
지금까지 aggregate function은 Select에서 나오는 tuple들이 있을 때 select에서 나오는 모든 tuple에 대해 aggregate function을 적용했다. 지금부터는 결과로 나오는 tuple를 몇개의 group으로 나누고 각 group에 대해서 aggregate function을 적용한 결과를 본다.
원래는 결과가 한 tuple이 나오는데 이제는 group 수 만큼 요약된 결과 tuple이 나온다.
grouping을 하려면 조건이 있어야한다. 어느 attribute를 기준으로 grouping할지 지정해준다.
▪ Grouping attribute(s)
grouping attribute를 정하면 결과로 나오는 tuple들을 이 grouping attribute가 같은 group끼리 묶어서 결과가 나온다. 그 후 각 그룹에 aggregate function을 적용한다.
- 각 group의 기준이 되는 attribute을 grouping attribute이라고 한다.
- 각각의 group은 grouping attribute의 value값이 모두 같은 tuple들의 집합으로 이루어져있다.
- GROUP BY clause로 정의된다.
- SELECT clause에 반드시 나와야한다. 반대로, select clause에 나오기 위해서는 grouping attribute으로 들어가야한다.
◆ Query 24. For each department, retrieve the department number, the number of employees in the department, and their average salary
각 department에 대해서 department의 번호와 그 부서에서 일하는 사람의 수와 그들의 평균 salary를 보고싶다.
전체 tuple을 Dno가 같은 tuple끼리 group화한다. 그러면 department 수만큼의 group이 나올 것이다. 각 group에 대해서 COUNT와 AVG(Salary)를 보이게 되면 각 group마다 결과가 나온다.
원래 8명이 있었는데 Dno를 기준으로 grouping을 한다. 따라서 group의 수만큼 tuple이 나온다.
◆ Query 25. For each project, retrieve the project number, the project name, and the number of employees who work on that project
위의 query는 먼저 PROJECT와 WORKS_ON을 Pnumber=Pno일 때 JOIN한 것이다. 그러면 누가 어디서 일하는지 결과가 나온다. 그 후 Pnumber와 Pname을 기준으로 grouping하면 같은 project에서 일하는 사람들이 쭉 그룹별로 나올 것이다. 프로젝트 별로 몇 명이 일하는지 확인할 수 있다. 주의할 점은 GROUP BY에 나오는 이름을 SELECT에 써야한다. 또한, SELECT에 쓰기 위해서는 GROUP BY에 나와야한다.
만약 Pnumber만 보고 싶었으면 Join이 필요없어서 PROJECT도 없고 WORKS_ON에 Pnumber로 group by한 다음에 Pnumber를 count 하면 된다.
SELECT Pnumber, COUNT(*)
FROM WORKS_ON
GROUP BY Pnumber;
▪ GROUP BY and HAVING Clauses
◆ HAVING Clauses
앞에서 group by를 통해서 select 문에서 찾아진 tuple들의 전체가 아니라 group을 지어서 각 group에 대한 aggregation을 한 결과를 tuple로 보여준다고 했다. 모든 group에 대한 summary 정보가 아닌 조건을 주고 조건에 만족하는 경우만 결과를 보고 싶은 경우가 있을 수 있다. 그때 그 조건을 명시하기 위한 방법으로 Having Clause가 사용된다.
▪ 이는 항상 GROUP BY를 통해 group을 생성한 후에 aggregation 결과를 보여준다. 모든 그룹이 아니라 일부 그룹을 선택하는 것이기 때문이다.
◆ Query 26. For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project
2명 이상의 employee가 work하는 project의 Pno, Pname과 해당 project에서 일하는 employee의 수를 보여줘라.
PROJECT, WORKS_ON을 Pnumber = Pno인 조건 하에서 JOIN하고 Pnumber와 Pname이 같은 group끼리 group화 하고 그것들 중 tuple이 3개 이상인 것들을 고른다.
▪ HAVING은 어떤 조건을 주고 그 조건을 만족하는 것을 뽑는다는 것이다. 이는 WHERE 절과 조건을 명시한다는 측면에서는 같은 기능을하는 clause이다.
차이는 뭘까?
WHERE: tuple들을 골라내는 조건을 명시한다. (FROM 절에 있는 tuple들 중)
HAVING: group을 골라내는 조건을 명시한다.
따라서 둘을 바꿔쓰지 않도록 유의해야한다!! 시험에 나오면 뒤집어서 쓰면 안된다.
◆ Substring Pattern Matching
where절에서 어떤 attribute 값이 정확히 지정된 것을 명시했다. substring pattern matching은 string을 matching하는데 있어서 string이 포함되는 attribute을 찾아내는 등에 사용된다. 따라서 equal이 아닌 LIKE operator와 같이 사용된다.
◆ LIKE comparison operator
▪ '%' 심볼뒤에는 아무것도 없어도 되고 여러 개의 character가 와도 된다.
▪ '_'를 사용해서 single character를 대체해도 된다.
◆ Query 12. Retrieve all employees whose address is in Houston, Texas
address라는 전체 string에서 Houston,TX가 들어있는 tuple을 골라내는 조건으로 사용된다. %에 아무것도 없어도 되고 아무것도 없어도 된다.
◆ Query 12A. Find all employees who were born during the 1950s
정확히 앞에 2개의 char과 뒤에 7개의 char이 있어야함을 의미한다.
◆ Arithmetic Operators
◆ +, -, *, /와 같은 표준 산술 함수를 SQL query에서 사용할 수 있다는 뜻이다.
◆ Query 13. Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise
ProductX에서 일하고 있는 employee의 salary를 10% 높인 결과를 보여라.
◆ Ordering of Query Results
◆ table은 set of tuples이다. 이 순서는 신경쓰지 않고 마구 내보낸다. 하지만 사용자는 sorting에서 결과를 보여주길 원하는 경우가 많다. 이러한 편의를 위해서 ordering하는 기능이 필요하다.
◆ ORDER BY clause를 통해 attribute을 기준으로 query result를 sorting해서 보여준다.
◆ Specifying order
▪ DESC: descending order
▪ ASC: ascending order
▪ default로 오름차순을 사용한다.
◆ Query 15. Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, ordered alphabetically by last name, then first name
employee와 project 쌍을 보여줘라. department 순서대로, 같은 department에서는 last name, first name 오름차순 순서대로 보여줘라.
같은 department에 속하는 것들부터 나오고 Lname을 기준으로 sorting되고 같다면 Fname을 기준으로 sorting된다. 만약 세개의 기준 모두 같은 사람이 있다면 신경쓰지 않는다.
◆ Summary of Retrieval Queries
SELECT-FROM 필수!
SELECT 뒤에 aggregate function, arthimetic function 들어갈 수 있었다.
WHERE 조건을 만족하는 tuple들만 골라내라.
만약 SELECT에 aggregate function이 있을 때는 전체에 대해 요약 정보를 내는게 아니라 GROUP BY를 통해 그룹별로 요약 정보를 낼 수 있다. group을 정하는 attribute값을 줄 수 있다.
어떤 group에 대한 조건을 HAVING clause뒤에 넣을 수 있다.
GROUP BY와 HAVING은 모두 aggregate function과 함께해야한다.
결과로 나오는 tuple들의 순서는 order by로 지정해 준다.
◆ Summary
1. More complex retrieval queries
▪ Nested queries
▪ Aggregate functions
▪ Grouping
'3-2 > 데이터베이스시스템' 카테고리의 다른 글
| [DB12] Relation Schema를 평가하는 Guideline, Functional Dependency (0) | 2024.11.20 |
|---|---|
| [DB10] INSERT, DELETE, UPDATE SQL 파헤치기 (1) | 2024.11.12 |
| [DB08-2] SQL 알아보기_SELECT/FROM/WHERE (2) | 2024.11.04 |
| [DB08-1] SQL 알아보기_SELECT문 (0) | 2024.10.18 |
| [DB07] Relational Algebra_SELECT, JOIN, PROJECT 등 (0) | 2024.10.15 |