1. BASIC RETRIEVAL QUERIES IN SQL

1-1. Basic SQL Query Block

1-2. Basic Retrieval Queries in SQL

SQL은 HOW가 아닌 WHAT을 요구하는 non-procedural(비절차적), declarative(선언적) language

• SELECT statement
  • DB로부터 정보 검색 시 사용하는 basic statement
  • relational algebra(절차적 언어)의 "selection" operation이랑 동일하지 않다.
• Practical model(or, SQL)과 formal relational model(or, relational calculus/algebra) 사이의 중요한 구별
  • SQL 쿼리 결과로 table이 모든 attribute 값에서 동일한 두 개 이상의 tuple을 가지는 것을 허용한다. (중복 허용)
    • tuple들의 multiset (= resultset) : bag이라고도 부른다.
    • query result에서 unique하게 row를 식별하기 위해 row ID (artificial identifier)가 사용될 수 있다.

1-3. The SELECT-FROM-WHERE Structure of Basic SQL Queries

• The basic form of the SELECT statement, or a SQL "query"
  

• SELECT절
  • < attribute list >
  • query를 통해 탐색하고자 하는 values를 가지고 있는 attribute 이름 목록을 나타낸다.
  • query에서 선택적으로 가져오고자 하는 속성을 지정한다.
  • 이 작업을 projection이라고 부른다.
• FROM절
  • < table list >
  • query를 처리하는 데 필요한 table name의 목록을 나타낸다.
  • query가 어떤 테이블에서 데이터를 검색하거나 조작할지를 지정한다.
• WHERE절
  • < condition >
  • query에서 가져와야 하는 (condition을 충족한) tuple을 식별하는 조건적인 표현을 나타낸다.
  • query 결과에서 특정 조건을 충족하는 행을 결정하는 데에 사용된다.
  • 이 작업을 selection이라고 부른다.

위에서 보는 바와 같이, SQL 질의는 어떻게 데이터를 찾아오라고 하지 않고 무슨 데이터를 검색하기 원하는지 기술할 수 있게 한다.
그래서 비절차적, 선언적 (non-procedural, declarative) 언어라고 부른다.

• Logical comparision operators
  • =, <, <=, >, >=, and <>
  • For comparing attribute values with other attribute values and with literalconstants:e.g.,“Employee.age >= 20”
• Projection attributes
  • 쿼리 결과에서 검색하려는 속성(열)의 이름 목록을 나타낸다.
  • 어떤 속성을 가져올 지를 지정하는 데에 사용된다.
  • 탐색된 값들을 가지고 있는 attributes
• Selection condition
  • 쿼리 결과에서 검색할 행(Tuple)을 식별하는 조건적인 표현
  • 어떤 탐색된 tuple은 항상 TRUE여아 하는 Boolean 조건문
  • 어떤 Relation이 포함될 때는 Join condition을 포함한다.
• Join condition
  • 여러 관계(Table)이 관련된 경우에 사용된다.
  • 두 개 이상의 테이블 간에 어떻게 관련이 있는 지를 지정하는 조건
  • 두 개 이상의 테이블을 결합하고 서로 연결된 데이터를 검색하는 데에 사용된다.

1-4. Basic Retrieval Queries - Simple (No Join)

• Q. Retrieve the birth date* and address** of the EMPLOYEE(s) whose name is 'John B. Smith'
  

• WHERE절의 조건을 만족하는 FROM절의 EMPLOYEE의 개별 tuples를 선택하고, SELECT절에 있는 Bdate, Address attributes를 projection(특정 열을 선택하여 결과로 반환)한다.

• Query result
  

1-5. Basic Retrieval Queries - 2-way join

• Q. Retrieve the name and address of all EMPLOYEEs who work for the 'Research' DEPARTMENT
  
  • selection condition : 어떤 table을 선택할 것인지 명시한다.
  • join condition : 2개 이상의 table을 join
    • 위의 예시에서는 Dnumber(DEPARTMENT의 PK), Dno(EMPLOYEE의 FK)
• Called a select-project-join(SPJ) query

• Query result
  

1-6. Basic Retrieval Queries - 3-way Join

• Q. For every project located in ‘Stafford’, list
  • the project number,
  • the controlling department number, and
  • the department manager’s last name, address, and birth date.
    
  • Dnum(PROJECT의 FK), Dnumber(DEPARTMENT의 PK) -> 첫 번째 JOIN
  • Mgr_ssn(DEPARTMENT의 FK), Ssn(EMPLOYEE의 PK) -> 두 번째 JOIN

• Query result
  

1-7. Ambiguous Attribute Names

• 하나의 이름이 두 개 이상의 attribute에서 사용될 수 있다.
  • 다른 table에 있는 attribute에 이름이 중복되는 경우이다.
  • Then any problem ?
    • Join하여 Query 작성시에 어떤 table의 attribute인지 구별하기 힘들다.
• 이러한 모호성을 없애기 위해, 같은 attribute name을 가질경우에는 table name과 함께 fully-qualified attribute name을 사용해야 한다.
• 다음과 같은 상황을 예상해보자
  • In EMPLOYEE: Dno -> Dnumber | Lname -> Name
  • In DEPARTMENT: Dname -> Name
    • -> Join 시에 구분이 어려움

• 모호성 제거를 위해 다음과 같이 기술한다.
  
• But, Table name을 붙임으로써 모호성을 제거하였지만, Query를 작성하는 성능의 저하를 초래할 수 있다.
  -> "Aliasing"이 필요함

1-8. Aliasing and Renaming

모호성을 제거하기 위해 일종의 별칭을 사용한다.

• 같은 관계를 두 번 참조하는 쿼리의 경우에서 attribute의 name이 모호할 수 있다.

  • [EXAMPLE] EMPLOYEE.Ssn, EMPLOYEE.Super_ssn
  • How to remove ambiguity ?
    • 쿼리에 두 번 참조하는 EMPLOYEE table에 대해서, "alternative table names", say E and S called "aliases or tuple variables"를 선언한다.

• Query : "For each employee, retrieve the employes's first and last names and the first and last names of his/her supervisor."
  

  Self-join

• aliasing를 위한 권장 사항

  • Table name : 첫 글자를 별칭으로 사용
  • Attribute name : 별칭이 된 테이블 이름을 접두사로 사용하고 attribute name을 연결
    • [EXAMPLE] E_Fname, S_Fname

• Query result
  

• The attribute names can also be renamed
  

1-9. Unspecified WHERE clause in a Query

• WHERE절이 없는 경우
  • tuple selection에서 아무런 조건을 적용하지 않는 것을 나타낸다.
  • FROM절에서 참조된 table의 Cartesian product의 결과
    • Outcome : 모든 가능한 tuple 조합

SELECT E.Ssn
FROM   EMPLOYEE E

SELECT Ssn, Dname
FROM   EMPLOYEE, DEPARTMENT

1-10. User of the Asterisk (*)

• "Retrieve all the attribute values of the selected tuples"
  • *는 relation name 또는 alias의 접두사로 사용할 수 있다.
  • [EXAMPLE] EMPLOYEE. (E.)
    

1-11. Table as Sets (a Multiset) in SQL

SQL은 query 결과를 automatically하게 동일한 tuple을 제거하지 않는다.

• Formal한 SQL에서는 중복을 제거한다.

• Why not elimination duplicate tuples ?
• 중복 제거의 비용이 크다.
  • tuple들을 모두 정렬하고, 그 후에 중복을 제거한다.
  • tuple의 양이 많을 경우, 정렬 + 제거의 비용이 증가한다.
• Query의 결과에 있는 중복된 tuples를 사용자가 원하는 경우가 존재할 수 있다.
• aggregate function(집계 함수 : sum, avg, max, distinct, ...)이 tuple이 tuple에 적용될 때, 대부분의 경우에서 중복 제거를 원하지 않는다.

• 만약 query result에서 제거를 원한다면 ?
  
  -> 중복이 제거됨

• Set operations : UNION (set union), EXCEPT (set difference), INTERSECT (set intersection)
  • Corresponding multiset operations : UNION ALL, EXCEPT ALL (or MINUS) and INTERSECT ALL
  • 이러한 set operations가 유효하게 만들기 위 양쪽 table은 서로 Type-compatible 해야 한다.
  • Type compatible
    - 2개의 relation은 같은 attrubute(name)을 가져야 한다.
    - 2개의 relation에서 attribute가 등장하는 순서는 같아야 한다.
    

A Query Example
“Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.”

1-12. Several More Features of SQL : Substring Pattern Matching

부분 문자열 패턴 매칭

• LIKE 비교 연산자 : 문자열 패턴 매칭을 위해 사용한다.

  • 부분 문자열은 2개의 문자로 명시될 수 있다.
  • '%'는 0개 이상의 문자를 대체한다.
    • [EXAMPLE] “Retrieve all employees whose address is in Houston, Texas.”

  

  • '_'은 하나의 문자를 대체한다.
    • [EXAMPLE] “Retrieve all male employees that were born during 1990s.”
      

• Attribute value가 '%'나 '_'를 포함하고 있는 경우 앞에 '\'를 추가한다.
  
  

1-13. Several More Features of SQL : Arithmetic Operations

산술 연산

• Standard arithmetic operators : +, -, *, /
  • SELECT 절의 부분으로써 포함될 수 잇다.
  • “Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise.”
    

• BETWEEN comparison operator
  
  • 이상, 이하를 의미한다.

1-14. Ordering of Query Results

질의 결과 정렬

• Use ORDER BY clause
  • 일반적으로 query의 끝에 위치한다.
    
• Keyword
  • DESC : 내림차순
  • ASC : 오름차순
• 개별식 정렬 방식도 지정이 가능하다.
  

1-15. Summary : Basic SQL Query Block

2. INSERT, DELETE, AND UPDATE STATEMENTS IN SQL

2-1. Commands for Database Modification

• Insert
  • 일반적으로 relation(talbe)에 tuple(row)를 삽입한다.
• UPDATE
  • 조건에 만족하는 relation(table)에 있는 tuple을 update시킨다.
  • DELETE + INSERT로도 실행이 가능
• DELETE
  • 조건을 만족하는 relation(table)에 있는 tuple을 update 시키는 것과 동일하다.

2-2. The INSERT Command

• 쉽게 말하면, 테이블에 하나 이상의 행을 추가할 때 사용한다.

• attribute는 CREATE TABLE에서 지정된 순서대로 나열해야 한다.

• data type에 대한 제약 조건은 자동으로 관찰된다.

  • 잘못된 값이 포함되어 있다면, insertion이 reject된다.

• DDL statement에 정의된 무결성 제약 조건이 강제된다.

  • [EXAMPLE] Key, Entity, not-null, unique ....

• NULL값을 포함한 모든 값들이 attribute 순서에 맞게 제공되어야 한다.

• 다음과 같이 사용자가 Command에서 제공한 값에 해당하는 attribute에 지정할 수도 있다.

  • [EXAMPLE] If we only know an employee’s first name, last name, its department number, and SSN, how to enter such a tuple into EMPLOYEE?
    
    

• 명시되지 않은 attribute에 대해서는 DEFAULT or NULL로 set된다.

• 여러 tuple을 insert하며 새로운 테이블을 만들 때 query result에서 값을 가져와 넣을 수도 있다.

• 많은 tuples를 "bulk-loading(대용량 적재)"하는 경우에도 사용할 수 있다.
• 새로운 table (say, D5EMPS)은 이미 존재하는 table(say, EMPLOYEE)와 동일한 attribute를 가진다.
  이 때, LIKE and WITH DATA를 이용해서 생성한다.
• 또한, 전체 데이터를 적재할 수 있다.
• Table 생성과 적재를 동시에 수행했다.
• EMPLOYEE table을 그대로 만들고, query의 결과를 data로 load하며 Insert를 하는 Query

2-3. The DELETE Command

• Relation에서 tuples를 삭제
• WHERE절을 통해 제거할 tuple을 선택
• 한 번에 한 테이블에서만 tuple을 삭제한다. 즉, 특정 테이블에서만 행을 제거할 수 있다.
• Referential integrity를 준수해야 한다.
  • 만약, referential integrity constraint에 CASCADE가 명시되어 있다면 삭제할 tuple을 참조하는 모든 tuple도 삭제된다.
• WHERE절이 없는 경우
  • 테이블의 모든 행이 삭제되어 테이블이 비어진 상태가 된다.
  • 테이블을 삭제하는 방법은 ?
    • DROP TABLE table_name
  • How many tuples to be deleted ?
    • WHERE절을 만족하는 table의 rows의 갯수에 의존한다.
      

2-4. The UPDATE Command

• 한 개 이상의 선택된 tuple의 attribute value를 수정하기 위해 사용한다.
• 각 command는 같은 relation에 있는 tuple를 수정한다.
• WHERE절로 수정할 tuple을 선택한다.
• 추가적인 SET절을 사용하여 수정할 속성 및 그들의 새로운 값들을 지정한다. (UPDATE할 값을 지정하는 역할)
  • 참조 무결성 제약 조건의 DDL에서 referential triggered action이 지정된 경우, PK를 UPDATE할 경우 다른 table의 tuple의 FK값에도 전파될 수 있다.
• [EXAMPLE] Change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively.
  
  

  P.Dnum = 5를 참조하는 다른 tuple의 attribute 값도 변경해야 한다.

Reference
Database System Concepts | Abraham Silberschatz
데이터베이스 시스템 7th edition