Week 6 - Introduction to SQL (2)

Tuple = 릴레이션을 구성하는 각각의 행을 말하며 속성의 모임 = 행, 각각의 데이터

Tuple-Record-Row 헷갈려서 찾아보니 이렇게 있더라. 어디서 사용하는가에 따라 단어 선택

SET Operations in SQL

• UNION
• UNION ALL
• INTERSECT
• INTERSECT ALL
• EXCEPT
• EXCEPT ALL
  ALL → 중복 데이터를 그대로 사용

UNION

• 2개 이상의 Select statement 를 결합한다
• 중복은 제거한다
• 연산을 적용하는 두 테이블의 열 수 와 데이터 유형이 동일해야한다
  
  
• Union All → use duplications
  
  

INTERSECT

• 두 개의 SELECT 절을 묶는다
• 두 절의 공통 부분 만 반환한다
• 연산을 적용하는 두 테이블의 열 수 와 데이터 유형이 동일해야한다
  
  

EXCEPT

• 2 개의 SELECT 절을 받는다
• 입력한 첫번째 테이블에 있는 값만을 출력한다
  
  

Examples

NULL Values

• 일부 속성에 대해서 null 로 표시되는 null 값을 가질 수 있다.
• NULL = 알 수 없는 값, 값이 존재하지 않는 값 (Unknown value, not exist)
• null 을 포함한 산수식은 무조건 결과가 null
• is null 함수를 사용해서 해당하는 값이 null 인지를 확인할 수 있음

Aggregate Functions

Basic Functions

SELECT Problems#6. 평균 일일 대여 요금 구하기

 

GROUP BY

• 특정 칼럼을 기준으로 집계함수를 사용하여 데이터를 추출할 때 사용한다
• 기준 칼럼을 여러개 지정할 수 있다
• Having 절을 사용하면 집계함수를 사용해서 WHERE 절의 조건절 처럼 사용할 수 있다
• 그룹 칼럼을 기준으로 ORDER BY 되지만 명시적으로 써주는 것이 좋다
  
  
• Aggregate 함수 외부의 SELECT 절에 있는 속성은 group by 절에 들어 있어야 한다!
  
  

Having

• 결과에 표시되는 그룹 결과를 필터링 할 수 있다
• Where 절은 그룹을 만들기 전에 적용
• Having 절은 그룹을 만들고 나서 적용

Null Values and Aggregates

SELECT SUM(salary) FROM instructor  

• ignores null amount
• non-null 값들이 없으면 결과는 null 이다.
• Count(*) 를 제외한 모든 집계 함수는 집계된 속성에서 Null 값이 있는 tuple 을 무시
• SELECT COUNT(*) 는 NULL 값과 관계없이 반환된 모든 레코드의 합계를 반환
  → 즉, count 는 null 포함, 이외 집계함수는 Null 미포함

Nested Subquery

[!tip]
하나의 쿼리에서 SELECT 문 (메인쿼리) 안에 또 다른 SELECT 문이 있을 때, 그 안에 포함된 SELECT 문

1. 형태적 측면
   하나의 쿼리 안에 포함된 독립적인 SELECT 문, 소괄호로 둘러싸여 있다
2. 의미적 측면
   서브쿼리가 반환하는 값이 메인쿼리에서 보조 용도로 사용
   서브쿼리의 반환 집합은 메인쿼리의 어디에서 사용되느냐에 따라서
   • 특정 값을 반환
   • 테이블처럼 사용
   • 조건을 확인하는데 사용
   • 등과 같이 여러 방법으로 사용될 수 있다.

서브쿼리가 where 절에서 사용된 경우 Nested 서브쿼리라고 한다.
Nested 서브쿼리는 독립적으로 실행된 후, 메인쿼리는 그 결과를 이용하여 실행되는 형태를 취한다. ( 서브쿼리 1번만 실행 )
→ 서브쿼리 중첩을 위한 메커니즘이라고도 볼 수 있다.
서브쿼리를 사용하면 좀 더 동적이고 데이터 중심(data-driven) 의 쿼리를 작성할 수 있다.

SELECT A1, A2, A3 FROM r1,r2,r3 WHERE P  

• SELECT 절에서 쓰일 경우 : 단일 값을 반환하는데 사용 + 함수처럼 사용 = Scalar Subquery
• FROM 절에서 쓰일 경우 : 일종의 테이블처럼 사용 = Inline View
• WHERE 절에서 쓰일 경우 : 조건을 확인하는데 사용 (Ex : B <operation> (subquery) 의 형식) = Nested Subquery

[!Correlated Subquery]
서브쿼리가 Where 절에 사용되면서, 메인 쿼리와 서로 연관되어 수행되는 것
문법적으로는 메인쿼리의 컬럼이 서브쿼리에 사용되는 경우
Correlated 서브쿼리는 Nested 서브쿼리와는 다르게 본 질의문의 결과에 대해서 한 라인씩 연관되어 수행된다

select empno, ename From emp where EXISTS( select 1 from dept where deptno = emp.deptno and dname = 'sales'); 

서브쿼리 내부에 Top - Level 질의문의 컬럼 (emp.deptno)이 조건값(상수값) 으로 사용되었음
emp 테이블의 결과중 부서번호(emp.deptno) 컬럼값이 서브쿼리의 조건 값으로 대체되어 서브쿼리가 실행
Exists 절을 이용한 Correlated 서브쿼리를 사용하면, 위처럼 영업부서에 그 사람이 존재하는지 여부만 체크하는 목적으로 조건에 맞는 한건이 추출되면 스캔을 멈추므로 매우 유용하게 사용될 수 있다.

+JOIN 보다 엑세스 하는 데이터의 양이 줄어드는 경우와 같은 Correlated 서브쿼리의 장점이 없다면 JOIN을 사용하는 것이 좋다.

[!note]
subquery는 일반적으로 다른 테이블과의 비교가 필요할때 즉, query에서 어떤 테이블과 먼저 비교를 하고다른 테이블과 최종적으로 비교가 필요할때 사용한다. 혹은 같은 테이블 내에서 두 튜플을 비교해야할 때도 table을 두번 부를 수 없기 때문에 subquery를 사용한다.

---

Set Membership

[!IN 연산자]
특정 값이 목록이나 결과의 값과 일치하는지 여부를 테스트 할 수 있는 논리 연산자

IN 연산자와 함께 이용될 수 있다

쿼리문은 정말 다양하게 표현될 수 있기 때문에 각 SQL 문을 잘 이해하고 어떤 문장이 효율적인지 항상 고민하기

Set Comparison

-- Examples using comparison clauses SELECT [column_name | expression1 ] FROM [table_name] WHERE expression2 comparison_operator {ALL|SOME} (subquery) 

Some 절

SELECT * FROM Scott.emp WHERE SAL > SOME(1000,1500) ORDER BY SAL  

쿼리나 결과에 있는 값들을 비교
내부쿼리(서브쿼리) 결과에 찾는 행이 하나 이상 포함된 경우 참으로 출력

 

위 쿼리에 대한 결과

 

이런 식으로 같은 구문을 다른 절을 써서 구현할 수 있다.
→ 문제 풀이 할 때 같은 작동 다른 구문 생각해보기

---

all 절

쿼리가 반환하는 리스트나 결과의 모든 값과 비교
ALL 절 앞에 비교 연산자가 와야한다
쿼리가 행을 반환하지 않으면 TRUE 반환

 

→ 여기서 생각할 것
70 > ALL = 테이블의 모든 값이 70보다 작은가 가 아니라 최댓값보다 70이 크냐! 로 생각
70 ≤ ALL = 테이블의 모든 값이 70보다 큰가 가 아니라 70이 최솟값보다 작냐! 로 생각

: order 테이블에서 amount 가 2000 이상인 ord_amount 를 뽑기
→ dis_amount 의 값이 서브쿼리에서 뽑아온 결과 중의 최댓값보다 큰 dis_amount 값만
dispatch 테이블에서 dis_date, dis_amount, ord_amount 를 가져온다

• 예시2
  
  : dept_name 이 ‘Biology’ 인 tuple 을 instructor 테이블에서 가져와서 salary 를 반환
  서브쿼리에서 반환한 salary 의 최댓값보다 높은 salary 를 갖는 값을 instructor 테이블에서 골라 name 을 출력한다.
  

---

EXIST 절

• Nested query (서브쿼리) 의 결과가 비어있는지를 확인 (튜플이 포함되지 않음 = 조건에 맞는 데이터가 없음)
• 결과 - True/False → 결과가 비어있지 않으면 True 반환
  
  table 에서 condition 을 가진 column_name 반환 - 반환한 값이 존재하면 table_name 에서 column_name 출력
  

1. Using EXISTS with SELECT statements
   : 최소 한번 이상 주문한 고객의 이름과 성을 가져오는 쿼리
   
2. Using NOT EXISTS
   

---

Modification of the DB

• Deletion of tuples from a given relation
• Insertion of new tuple into a given relation
• Updating of values in some tuples in a given relation

Deletion

• To delete existing records from a table
• Delete a single record or multiple records
  
• WHERE 절에 넣는 조건에 다라서 단일~다수 레코드 삭제 가능
  
• WHERE 절이 없으면 모든 레코드를 삭제하고 테이블만 존재
  

---

Insertion

• INSERT INTO statemetn of SQL → insert new row in a table

1. Insert only Values
   
2. Insert Column names and values both
   

INSERT INTO with SELECT

SELECT 문과 함께 사용해서 한 테이블의 행을 복사하여 다른 테이블에 삽입할 수 있다

1. Inserting all columns of a table
2. Inserting specific columns of a table
   B에서 roll_no, name, age 가져와서 A 에 그대로 입력
   Q: 그럼 만약에 이름을 다르게 가져와서 넣고 싶으면 어떻게 쿼리를 바꿔줘야하지?
3. Copying specific rows from a table
   왜 첫번째 row 만 가져오는 건지 잘 이해를 못했다.

• 🛫 2023-10-21 : 관련 내용 찾아보기

---

Update

• Can Update Single Columns as well as Multiple Columns
• SET 문은 특정 Column 에 새 값을 설정하는 데 사용되며
  WHERE 절은 Column을 업데이트 해야하는 Row 를 선택하는 데 사용
  WHERE 절을 사용하지 않으면 모든 row 가 업데이트된다.
  
  → Age 가 20 인 row 의 이름을 “JONGWOO” 로 변경
  
  → ROLL_NO 가 1 인 Row 의 NAME 과 ADDRESS 를 각각 “JISU”, “New York” 로 변경

---

References

Row,Tuple,Record 차이
서브쿼리 개념
서브쿼리 종류 설명
some, any, all 절 예시

---

“이 글은 Obsidian 에서 작성되어 업로드 되었습니다”