[DataBase] 03. Introduction to SQL(2)

SQL에서의 집합 연산 (SET Operations in SQL)

• SQL은 테이블 데이터에 대해 수행될 수 있는 집합 연산을 지원합니다.
  • 이는 테이블에 저장된 데이터로부터 특별한 조건 하에 의미 있는 결과를 얻기 위해 사용됩니다.
  • UNION (합집합)
  • UNION ALL
  • INTERSECT (교집합)
  • INTERSECT ALL
  • EXCEPT (차집합)
  • EXCEPT ALL

• 집합 연산 UNION (합집합), INTERSECT (교집합), EXCEPT (차집합)
  • 위의 각 연산은 자동으로 중복을 제거합니다.
• 모든 중복을 유지하려면 다음을 사용하십시오:
  • UNION ALL (모든 합집합)
  • INTERSECT ALL (모든 교집합)
  • EXCEPT ALL (모든 차집합)

Union 연산 (Union operation)

• UNION은 두 개 이상의 SELECT 문 결과를 결합하는 데 사용됩니다.
• 그러나 결과 집합에서 중복 행을 제거합니다.
• UNION을 적용하는 테이블의 열 수와 데이터 유형은 동일해야 합니다.

 

 

UNION ALL 연산 (UNION ALL operation)

• 이 연산은 UNION과 유사합니다.
• 하지만 중복 행도 표시합니다.

 

Intersect 연산 (Intersect operation)

• Intersect 연산은 두 SELECT 문을 결합하는 데 사용되며, 두 SELECT 문에서 공통된 레코드만 반환합니다.
• Intersect의 경우 열 수와 데이터 유형이 동일해야 합니다.

Except 연산 (Except operation)

• Except 연산은 두 SELECT 문 결과를 결합하여 첫 번째 집합에 속하는 결과만 반환합니다.

 

집합 연산의 예 (Example of Set Operations)

• 2017년 가을 또는 2018년 봄에 열린 강좌 찾기

• 2017년 가을과 2018년 봄에 열린 강좌 찾기

• 2017년 가을에 열렸지만 2018년 봄에는 열리지 않은 강좌 찾기

 

NULL 값 (Null Values)

• 튜플의 일부 attribute에 대해 null 값을 가질 수 있습니다.
• null은 알 수 없는 값 또는 값이 존재하지 않음을 나타냅니다.
• null을 포함하는 모든 산술 표현의 결과는 null입니다.
  • 예) 5 + null의 결과는 null입니다.
• is null 조건자는 null 값을 확인하는 데 사용할 수 있습니다.
  • 예) 급여가 null인 모든 강사를 찾습니다.

select name
from instructor
where salary is null

집계 함수 (Aggregate Functions)

• 이러한 함수들은 릴레이션의 열 값의 다중 집합(multiset)에서 작동하며 값을 반환합니다.
  • avg: 평균 값
  • min: 최소 값
  • max: 최대 값
  • sum: 값의 합
  • count: 값의 수

Aggregate function 예제 (Aggregate Functions Examples)

• 컴퓨터 과학 학부의 강사의 평균 급여를 찾습니다.

  • select avg (salary)
    from instructor
    where dept_name = 'Comp. Sci.';

• 2010년 봄 학기에 강의를 진행한 강사의 총 수를 찾습니다.

  • select count (distinct ID)
    from teaches
    where semester = 'Spring' and year = 2018;

• course 릴레이션의 튜플 수를 찾습니다.

  • select count (*)
    from course;

그룹화 (Group By)

• 각 학과의 강사 평균 급여를 찾습니다.

  • select dept_name, avg (salary) as avg_salary
    from instructor
    group by dept_name;

(Keyword as -> SQL 별칭은 테이블 또는 열에 임시 이름을 부여하는 데 사용됩니다.)

 

 

• Aggregate function 외의 select 절의 속성은 group by 목록에 나타나야 합니다.

/* 오류가 있는 쿼리 */
select dept_name, ID, avg (salary)
from instructor
group by dept_name;

 

 

Aggregate Functions – Having 절

• HAVING 절은 결과에 표시될 그룹을 필터링하는 조건을 지정할 수 있습니다.
  • 평균 급여가 42000 이상인 모든 학과의 이름과 평균 급여를 찾습니다.

  • select dept_name, avg (salary) as avg_salary
    from instructor
    group by dept_name
    having avg (salary) > 42000;

  • 여기서 실제로 시스템은 salary 를 먼저 읽는다.
• 주의: HAVING 절의 조건자는 그룹 형성 후 적용되며, WHERE 절의 조건자는 그룹 형성 전에 적용됩니다.

---

NULL 값과 Aggregate

• 모든 급여 합산

  • select sum (salary)
    from instructor

  • 위 문장은 null 금액을 무시합니다.
  • non-null 금액이 없으면 결과는 null입니다.
• count(*)를 제외한 모든 집계 연산은 집계 속성의 null 값을 무시합니다.

  • select count (*)
    from course;

  • 이 쿼리는 NULL 값을 포함하여 결과 집합에 반환된 모든 레코드의 총 수를 반환합니다.

---

중첩 서브쿼리 (Nested Subqueries)

• SQL은 서브쿼리의 중첩 메커니즘을 제공합니다.
  • 서브쿼리는 더 동적이고 데이터 중심적인 쿼리를 작성할 수 있게 합니다.
  • 서브쿼리는 다른 쿼리 내에 중첩된 select-from-where 표현식입니다.
  • 다음 SQL 쿼리에서 중첩이 가능합니다.

select A1, A2, ..., An
from r1, r2, ..., rm
where P

• 다음과 같이:
  • From 절: r_i는 유효한 서브쿼리로 대체될 수 있습니다.
    • 쿼리의 결과가 relation이기 때문
  • Where 절: P는 B <operation> (subquery) 형태의 표현식으로 대체될 수 있습니다.
    • 여기서 B는 attribute이고 <operation> 은 연산입니다.
  • Select 절:
    • A_i는 단일 값을 생성하는 서브쿼리로 대체될 수 있습니다.

중첩 서브쿼리의 예 (Example of Subqueries)

• 다음 쿼리는 미국에 위치한 사무실에서 근무하는 직원을 반환합니다.

외부 쿼리와 서브쿼리 또는 내부 쿼리:

SELECT lastname, firstname
FROM employees
WHERE officeCode IN (
  SELECT officeCode
  FROM offices
  WHERE country = 'USA'
);

• 서브쿼리는 미국에 위치한 사무실의 모든 office code를 반환합니다.
• 외부 쿼리는 서브쿼리에 의해 반환된 office code에 해당하는 사무실에서 근무하는 직원의 성과 이름을 선택합니다.

Set Membership

• SQL 집합 멤버십(Set Membership)은 IN 연산자를 사용하여 수행할 수 있습니다.
• IN 연산자는 지정된 값이 목록이나 결과에서 어떤 값과 일치하는지 테스트할 수 있는 논리 연산자입니다.

  SELECT column_name(s)
  FROM table_name
  WHERE column_name IN (value1, value2, ...);
  
  SELECT column_name(s)
  FROM table_name
  WHERE column_name IN (SELECT statement);

•  

Set Membership 예제

• Find courses offered in Fall 2017 and in Spring 2018

• Find courses offered in Fall 2017 but not in Spring 2018

• Name all instructors whose name is neither “Mozart” nor Einstein

• Find the total number of (distinct) students who have taken course sections taught by the instructor with ID 10101

 

• Note: 위의 쿼리는 훨씬 더 간단한 방식으로 작성할 수 있습니다. 위의 공식은 단순히 SQL 기능을 설명하기 위한 것입니다.

Set Comparison

• 값과 목록 또는 쿼리의 결과를 비교하여 참인지 평가합니다.
  • some 절
  • all 절

Set Comparison (some clause)

• some은 목록 또는 쿼리의 결과에서 각 값을 비교하여 내재된 쿼리 결과가 최소 한 행을 포함하면 참으로 평가합니다.
  • some은 서브쿼리에서 최소 한 행과 일치해야 하며 비교 연산자가 앞에 와야 합니다.
  • some을 사용하여 greater than (>)를 사용하는 경우, 적어도 하나의 값보다 큽니다.

Set Comparison (some clause) 예제

• 다음 조건을 따르는 agent_code, agent_name, working_area, commission를 agent table로 부터 얻기 위해서 
  • agent_code는 'customer' 테이블의 agent_code 중 하나여야 합니다. 아래 조건을 만족하는 경우:
    • cust_country는 UK여야 합니다.

• Agents table

• Customer table

• Result

• Example
  • 생물학과의 일부(최소 한 명) 강사보다 급여가 높은 강사의 이름을 찾습니다.

• some clause를 사용한 query

 

 

Set Comparison (all clause)

• ALL은 SELECT 문의 모든 레코드를 선택하는 데 사용됩니다.
  • ALL은 쿼리의 결과 목록이나 결과에 있는 모든 값과 값을 비교합니다.
  • ALL은 비교 연산자 앞에 와야 하며, 쿼리가 아무런 행도 반환하지 않으면 TRUE로 평가됩니다.
  • 예를 들어, ALL은 모든 값보다 크다는 의미로, 최대값보다 크다는 것을 의미합니다.

구문

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);

예시

예시

SELECT dis_date, dis_amount, ord_amount
FROM dispatch
WHERE dis_amount > ALL(SELECT ord_amount
		FROM orders
		WHERE ord_amount >= 2000);

• dispatch 테이블의 dis_amount 값이 orders 테이블에서 ord_amount 값이 2000 이상인 조건을 만족하는 ord_amount 값보다 큰 경우, dis_date, dis_amount, ord_amount 열을 선택합니다.

예시

SELECT name
FROM instructor
WHERE salary > ALL (SELECT salary
FROM instructor
WHERE dept_name = 'Biology');

• Biology 부서의 모든 강사보다 급여가 높은 강사의 이름을 찾습니다.

예시

EXISTS Clause

• EXISTS는 SQL에서 상관 중첩 쿼리의 결과가 비어 있는지(튜플이 없는지) 확인하는 데 사용됩니다.
  • EXISTS의 결과는 TRUE 또는 FALSE의 부울 값입니다.
  • EXISTS 구성은 인수 서브쿼리가 비어 있지 않으면 TRUE를 반환합니다.

구문

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name(s)
FROM table_name
WHERE condition);

 

예시(EXISTS with SELECT)

고객 Customers와 주문 Orders 관계를 고려하십시오.

SELECT fname, lname
FROM Customers
WHERE EXISTS (SELECT *
FROM Orders
WHERE Customers.customer_id = Orders.c_id);

• 최소 하나의 주문을 한 고객의 이름과 성을 가져옵니다.

예시 2(NOT EXISTS)

SELECT lname, fname
FROM Customer
WHERE NOT EXISTS (SELECT *
FROM Orders
WHERE Customers.customer_id = Orders.c_id);

• 주문을 하지 않은 고객의 성과 이름을 가져옵니다.

 

데이터베이스 수정 (Modification of the Database)

• 튜플 삭제 (Deletion): 주어진 관계(relation)에서 튜플을 삭제합니다.
• 튜플 삽입 (Insertion): 주어진 관계(relation)에 새로운 튜플을 삽입합니다.
• 값 업데이트 (Updating): 주어진 관계(relation)의 일부 튜플의 값을 업데이트합니다.

---

삭제 (Deletion)

• 삭제 구문 (Delete statement)
  • SQL에서 DELETE 구문은 테이블에서 기존 레코드를 삭제하는 데 사용됩니다.
  • WHERE 절에 명시한 조건에 따라 단일 레코드 또는 여러 레코드를 삭제할 수 있습니다.
• 구문 (Syntax)
  • 주의: WHERE 절에 제공한 조건에 따라 단일 또는 여러 레코드를 삭제할 수 있습니다. WHERE 절을 생략하면 모든 레코드가 삭제되고 테이블이 비게 됩니다.

 

예제 (Example)

• 예제 1: 단일 레코드 삭제 (Deleting single record)
  • NAME이 "Ram"인 행을 삭제합니다.

    DELETE FROM Student WHERE NAME = 'Ram';

 

• 예제 2: 여러 레코드 삭제 (Deleting multiple records)
  • 나이가 20인 행을 삭제합니다.

    DELETE FROM Student WHERE Age = 20;

• 예제 3: 모든 레코드 삭제 (Delete all records)
  • 두 가지 쿼리를 사용할 수 있습니다.

    DELETE FROM Student;
    DELETE * FROM Student;

  • 출력: 테이블의 모든 레코드가 삭제되며, 더 이상 표시할 레코드가 없습니다.

삽입 (Insertion)

• 삽입 구문 (Insert into statement)
  • SQL의 INSERT INTO 구문은 테이블에 새로운 행을 삽입하는 데 사용됩니다.
  • 삽입 구문에는 두 가지 방법이 있습니다:
    • 값만 삽입하는 방법:

      INSERT INTO table_name 
      VALUES (value1, value2, value3, ...);

    • 열 이름과 값을 모두 삽입하는 방법:

      INSERT INTO table_name (column1, column2, column3, ...)
      VALUES (value1, value2, value3, ...);

---

예제 (Example)

• 예제 1: 값만 삽입 (Inserting only values)

INSERT INTO Student
VALUES('5', 'HARSH', 'WEST BENGAL', 'XXXXXXXXX', '19');

 

• 예제 2: 지정된 열에만 값 삽입 (Inserting values in only specified columns)
  • 주의: 제공되지 않은 값의 열은 null로 채워집니다. 이는 해당 열의 기본값입니다.

INSERT INTO Student(ROLL_NO, NAME, Age)
VALUES('5', 'PRATIK', '19');

 

INSERT INTO와 SELECT 구문 (INSERT INTO with SELECT statement)

• INSERT INTO 구문과 SELECT 구문을 사용하여 한 테이블의 행을 복사하여 다른 테이블에 삽입할 수 있습니다.
  • SELECT 구문은 다른 테이블에서 데이터를 선택하는 데 사용됩니다.
• INSERT INTO와 SELECT 구문을 사용하는 다양한 방법:
  • 테이블의 모든 열 삽입 (Inserting all columns of a table)
  • 특정 열 삽입 (Inserting specific columns of a table)
  • 특정 행 복사 (Copying specific rows from a table)

• 테이블의 모든 열 삽입 (Inserting all columns of a table)
  • 우리는 테이블의 모든 데이터를 복사하여 다른 테이블에 삽입할 수 있습니다.
  • 구문 (Syntax)
  • INSERT INTO first_table SELECT * FROM second_table;
  • 우리는 SELECT 구문을 사용하여 한 테이블의 데이터를 복사하고 INSERT INTO 구문을 사용하여 다른 테이블에 삽입합니다.

• 테이블의 모든 열 삽입 예제 (Example of inserting all columns of a table) 
  • 이 쿼리는 StudentB 테이블의 모든 데이터를 StudentA에 삽입합니다.

    INSERT INTO StudentA SELECT * FROM StudentB;

 

• 테이블의 특정 열 삽입 (Inserting specific columns of a table)
  • 우리는 복사하고자 하는 특정 열만 다른 테이블에 삽입할 수 있습니다.
  • 구문 (Syntax)

    INSERT INTO first_table(names_of_columns1) 
    SELECT names_of_columns2 
    FROM second_table;

  • 우리는 SELECT 구문을 사용하여 두 번째 테이블의 선택된 열만 복사하고 INSERT INTO 구문을 사용하여 첫 번째 테이블에 삽입합니다.

• 테이블의 특정 열 삽입 예제 (Example of inserting specific columns of a table)
  • 이 쿼리는 StudentB 테이블의 ROLL_NO, NAME, Age 열의 데이터를 StudentA 테이블에 삽입합니다.
  • StudentA 테이블의 나머지 열은 기본값인 null로 채워집니다.

    INSERT INTO StudentA(ROLL_NO, NAME, Age) 
    SELECT ROLL_NO, NAME, Age 
    FROM StudentB;

 

• 테이블의 특정 행 복사 (Copying specific rows from a table)
  • WHERE 절을 사용하여 특정 행을 선택하여 다른 테이블에 삽입할 수 있습니다.
  • 특정 행을 선택하기 위해 WHERE 절에 적절한 조건을 제공해야 합니다.
  • 구문 (Syntax)

    INSERT INTO table1 
    SELECT * 
    FROM table2 
    WHERE condition;

• 테이블의 특정 행 복사 예제 (Example of copying specific rows from a table)
  • 이 쿼리는 StudentB 테이블에서 나이가 18인 첫 번째 행만 선택하여 StudentA 테이블에 삽입합니다.

    INSERT INTO StudentA 
    SELECT * 
    FROM StudentB 
    WHERE Age = 18;

업데이트 (Update)

• 업데이트 구문 (Update statement)
  • SQL의 UPDATE 구문은 데이터베이스의 기존 테이블 데이터를 업데이트하는 데 사용됩니다.
  • 요구 사항에 따라 단일 열 또는 여러 열을 UPDATE 구문을 사용하여 업데이트할 수 있습니다.
  • 구문 (Syntax)
  • UPDATE table_name
  • SET column1 = value1, column2 = value2,...
  • WHERE condition;
  • 주의: 위의 쿼리에서 SET 구문은 특정 열에 새로운 값을 설정하는 데 사용되고, WHERE 절은 열을 업데이트할 행을 선택하는 데 사용됩니다.
    • WHERE 절을 사용하지 않으면 모든 행의 열이 업데이트됩니다.
    • 따라서 WHERE 절은 특정 행을 선택하는 데 사용됩니다.

---

업데이트 (Update)

• 업데이트 구문 예제 (Example of update statement)
  • 테이블 데이터:

 

• 단일 열 업데이트 예제 (Example of updating single column)
  • 나이가 20인 모든 행에서 NAME 열의 값을 "JONGWOO"로 설정합니다.

    UPDATE Student 
    SET NAME = 'JONGWOO' 
    WHERE Age = 20;

  • 이 쿼리는 두 개의 행을 업데이트하며, 테이블 student는 다음과 같이 보이게 됩니다.

• 여러 열 업데이트 예제 (Example of updating multiple columns)
  • ROLL_NO가 1인 행에서 NAME 열을 'JISU'로, ADDRESS 열을 'NewYork'으로 설정합니다.

    UPDATE Student 
    SET NAME = 'JISU', ADDRESS = 'NewYork' 
    WHERE ROLL_NO = 1;

  • 이 쿼리는 첫 번째 행의 두 열을 업데이트하며, 테이블 student는 다음과 같이 보이게 됩니다.

• WHERE 절 생략 예제 (Example of omitting WHERE clause)
  • 업데이트 쿼리에서 WHERE 절을 생략하면 모든 행이 업데이트됩니다.

    UPDATE Student 
    SET NAME = 'JIHYUCK';

  • 테이블 student는 다음과 같이 보이게 됩니다.