[데이터 베이스] ch6.Basic SQL (Structural Query Language)

Background

• SQL (Structural Query Language)
  • tuple calculus로부터 유래
  • 옛 이름은 SEQUEL(Structed English QUEry Language)
  • IBM에서 실사용
  • DBMS마다 SQL 지원 문법이 조금씩 다르다.

SQL DATA DEFINITION AND DATA TYPES

Schema and Catalog Concepts in SQL (Data Definition Language: DDL)

SQL schema

• 몇 시스템에서는 database라고 한다.
• 같은 DB에 있어야 하는 table + relationship을 의미
• schema name으로 식별되고, 누가 schema를 소유하고 있는지를 나타내는 (i) authorization identifier와 schema에 있는 각 element에 대한 (ii) descriptors를 포함

Schema elements

• Tables(formal하게는 relations), Constraints, Views, Domains(column의 data type) and other construct를 포함

CREATE SCHEMA

• schema를 생성하는 구문
• 모든 schema elements' definitions을 포함가능
• Example: CREATE SCHEMA COMPANY AUTHORIZATION 'Joshua'

하지만 모든 사용자가 schemas & schema elements를 생성할 수 있는 권한이 있는 것은 아니다.

• DBA가 권한을 부여

Catalog

: 명명된 schemas의 collection, 모든 schema의 정보 저장

• Integrity constraints는 relations가 동일한 catalog 내에 존재하는 경우에만 정의될 수 있다.
• 동일한 catalog 내에서, schemas는 특정 elements(type/domain definitions)를 공유할 수 있다. 
• 다른 schema 참조 시에 catalog 사용

The CREATE TABLE Command in SQL

새로운 relation R을 정의

• R에 이름 부여
• R의 attributes, types(domains), and intial constraints(NOT NULL or UNIQUE)를 정의

Atfer attribute declaration

• key, entity integrity, and referential integrity constraints는 attribute declaration이후에 정의될 수 있다.
  • 특정 referential의 경우, 참조할 다른 table의 선언 이후 선언해야 한다. 따라서 ALTER TABLE을 사용하여 이후에 제약조건 추가 (혹은 선언 순서를 바꿔주기)

Base tables(base relations)

• CREATE TABLE
• tables(relations)와 table의 rows(tuples)는 physically, actually하게 생성되고, DBMS에 의해 '파일'로서 저장된다.

Virtual relations(views)

• CREATE VIEW
• 어떠한 물리적 파일과도 일치하지 할 수도, 하지 않을 수도 있다.
  • view에 속한 관련 tuples는 물리적 구체화가 되지 않는 한 view가 참조될 때만 계산된다.
• Why needed?
  • 디스크 낭비를 막기 위함 (하지만 계산 시간이 오래 걸리면 table화 하는 게 이득일 수 있음)
  • 권한을 분리하기 위함

Recall the COMPANY Relational Schema

One Possible Database State

SQL CREATE TABLE Data Definition Statements for Defining a COMPANY Schema

• 몇 foreign keys는 에러를 발생시킬 수 있다.
  • Circular references (순환 참조): e.g. EMPLOYEE table의 Super_ssn
  • 또는 참조할 table이 생성되지 않아 오류 발생
    E.g, EMPLOYEE table의 Dno는 DEPARTMENT table을 참조하는데, 아직 DEPARTMENT가 만들어지지 않은 경우
• ALTER TABLE 구문으로 이를 해결
  • 위의 구문을 이용해 primary key는 후에 정의될 수 있다.
    

    

Attribute Data Types and Domains in SQL (1/7)

Basic data types - Numeric data types

• Integer numbers of various sizes: INTEGER, INT and SMALLINT
  • Oracle에서는 NUMBER 사용(DECIMAL을 대체가능)
• Floating-point (real) numbers: FLOAT, or REAL, and DOUBLE PRECISION
• DECIMAL(i,j) (or DEC(i,j) or NUMBERIC(i,j))로 정형화된 숫자 표현 가능
  • Ex) DEC(4,2) = 12.34

Attribute Data Types and Domains in SQL (2/7)

Basic data types - Character-string data types

• 고정 길이: CHAR(n), CHARACTER(n)
  • n: 최대 문자 길이
  • 왼쪽부터 문자를 채우고, 빈 공간은 공백
• 가변 길이: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)
  • n은 4K로 한정
  • CLOB(CHARACTER LARGE OBJECT): "document"와 같이 매우 긴 문자열 값을 위함
    • size: K, M, G
    • Ex) CLOB(20M): 20MB까지

Attribute Data Types and Domains in SQL (3/7)

Basic data types - Bit-string data types (이진 코드)

• 고정 길이: BIT(n)
  • n: 최대 bits 수, 4K로 한정
  • 값 지정을 위해서, B`10101`와 같은 형식을 취해야 함
    → 일반 문자열과 구분짓기 위해 B로 bit임을 알려주고, ``안에 있는 값을 사용
• 가변 길이: BIT VARYING(n)
  • BLOB(BINARY LARGE OBJECT): 실행가능한 코드나 java 객체와 같은 매우 큰 binary 값을 위함
    • size: K, M, G
    • Ex) BLOB(20M): 20MB까지

Attribute Data Types and Domains in SQL (4/7)

Basic data types - Boolean data type

• TRUE or FALSE or NULL 값

Basic data types - Date data type

• 10자리
  • Components: YEAR, MONTH, and DAY -> YYYY-MM-DD
  • E.g., DATE '2014-09-27': Oracle에서는 to_date() 함수를 이용해서 전달받은 인자를 날짜형으로 변환
    • Ex) TO_DATE('2021-10-17', 'yyyy-mm-dd')

Basic data types - Time data type

• 적어도 8자리
  • Components: HOUR, MINUTE, and SECOND -> HH:MM:SS
  • E.g., TIME '09:12:47'

Date & TIme data types에서, 유효한 days 와 times만 수용될 수 있다.

• '>', '<'로 date/time 비교 연산 가능

Attribute Data Types and Domains in SQL (5/7)

Basic data types - TimeStamp data type

• DATE & TIME fields를 포함
  • seconds의 소수점 이하 자릿수에 대해 최소 6자리 추가
  • WITH TIME ZONE option
  • E.g., TIMESTAMP '2014-09-27 09:12:47.648302'

Basic data types - INTERVAL data type

• Date, time, or timestamp의 절댓값을 증가 또는 감소시키는데 사용되는 상대값을 지정
• time의 기간을 저장하기 위해 사용:
  e.g., INTERVAL '40' MONTH = INTERVAL '3-4' YEAR TO MONTH
  • YEAR TO MONTH: 연과 월의 기간을 저장하는 type
  • DAY TO SECOND: 일과 시간, 분, 초를 저장하는 type

Date, Time, Timestamp, INTERVAL data types는 비교 연산을 위해 string formats으로 cast or convert될 수 있다.

Attribute Data Types and Domains in SQL (6/7)

Domain:

• 공통의 Domain을 지정 (Data type을 만드는 것과 유사?)
• column마다 data type을 지정하는 대신 선언될 수 있다.
• Name은 attribute specification과 함께 사용될 수 있다.
• 이점
  1) domain에 대한 data type을 변경하는 것이 쉬워진다.
  2) schema 가독성 향상
• Ex)
  • CREATE DOMAIN SSN_TYPE AS CHAR(9);
    • CHAR, NUMBER와 같은 basic data types 대신 사용
  • EMPLOYEE의 Super_ssn, DEPARTMENT의 Mgr_ssn, WORKS_ON의 Essn, DEPENDENT의 Essn과 같이 동일한 data type을 가지는 attribute에 사용할 수 있다.

Attribute Data Types and Domains in SQL (7/7)

Type

• 객체를 저장하는 용도
• UDTs(User Defined Types)를 생성하는데 사용
• 객체 기반 applications 지원
• CREATE TYPE 구문으로 생성

SPECIFYING CONSTRAINTS IN SQL

Specifying Constraints in SQL

Basic constraints

Key constraints
: primary key의 값은 중복될 수 없다.

Entity integrity constraints
: primary key의 값은 NULL일 수 없다.

Referential integrity constraints
: foreign key는 참조하는 table에 존재하는 primary key의 값을 가져야 하고, 혹은 NULL 값을 가질 수도 있다.

• NULL값이 가능한 경우? EMPLOYEE의 Super_ssn이 NULL인 경우=boss

SQL에서 이를 기술하는 방법?

• PRIMARY KEY(Key constraints, +UNIQUE), NOT NULL(Entity integrity constraints), FOREIGN KEY(Referential integrity constraints) 키워드 사용

Specifying "Attribute" Constraints

Attribute의 기본값 설정

• DEFAULT <value> 구문
• NULL
  • 특정 attribute에 대해서는 적용되지 않는다. (primary key -> NOT NULL 사용)
• CHECK 구문
  • Dnumber INT NOT NULL CHECK (Dnumber > 0 and Dnumber < 10)
  • Age INT NOT NULL CHECK (Age >= 15 and Age < 65)
  • DBMS가 제약조건에 대한 충족여부를 확인 -> 충족해야만 data insert 가능
  • schema-based constraint로 인해 지원

Specifying "Key" Constraints

PRIMARY KEY 구문

• relation의 primary key를 구성하는 하나 이상의 attributes를 지정
• Dnumber INT PRIMARY KEY
• PRIMARY KEY(State, Number)

UNIQUE 구문

• relational model에서, candidate keys로 불리는 alternate(secondary) keys를 지정 (중복되면 안되는 attribute)
• Dnumber VARCHAR(15) UNIQUE
• UNIQUE(State, Number)

Specifying "Referential Integrity" Constraints

FOREIGN KEY 구문

• Default operation: violation을 일으키는 update를 "reject"
• referential triggered action 구문 추가 (참조 트리거 액션 절)
  • Options: SET NULL | CASCADE | SET DEFAULT
    • SET DEFAULT는 oracle에서 지원하지 않는다.
  • SET NULL or SET DEFAULT을 처리하는 DBMS의 과정은
    ON DELETE & ON UPDATE에서 동일하다.
  • CASCADE는 참조하는 tuples를 모두 삭제한다.
    • 일관성을 유지하는 것이 중요한 relationship relations에 적절

Giving Names to Constraints

CONSTRAINT 키워드

• constraint에 이름 부여: 후에 altering시 유용
  • Ex1) CONSTRAINT reg_no_u UNIQUE(State, Number)
  • Ex2) dno CONSTRAINT fk_deptno REFERENCE Department(Dnumber)

Specifying Constraints on Tuples Using CHECK

• relation에 있는 개별 tuples에 대한 추가적인 constraint는 CHECK를 사용하여 지정 가능하다.

CREATE TABLE 문 끝에 CHECK

• 각 tuple에 개별적으로 적용; row-based constraints
  • tuple이 삽입 또는 수정될 때마다 check

Example of Specifying Constraints

BASIC RETRIEVAL QUERIES IN SQL

Basic SQL Query Block

condition -> column 이름 operation value
ORDER BY -> DESC: 내림차순, ASC: 오름차순 (default)

Basic Retrieval Queries in SQL

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

SELECT statement

: database로부터 정보 검색 시 사용하는 basic statement
(relational algebra(procedual language)의 "selection" operation과 동일하지 않음)

Practical model(or, SQL)과 Relational model(or, relational calculus/algebra) 사이의 중요한 구별

• SQL은 query 결과로 table이 모든 attribute 값에서 동일한 두 개 이상의 tuple을 가지는 것을 허용한다. (결국 중복을 허용한다는 의미)
  • tuple들의 multiset (a.k.a resultset) (bag이라고도 부른다)
  • query 결과에서 unique하게 row를 식별하기 위해 row ID(artificial identifier)가 사용될 수 있다.

The SELECT-FROM-WHERE Structure of Basic SQL Queries

SELECT statement의 기본 형태

SELECT <attribute list>
FROM <table list>
[WHERE <condition>]

• SELECT 절:
  • <attribute list>: query를 통해 탐색하고자 하는 values를 가지고 있는 attribute names의 list
• FROM 절:
  • <table list>: query를 처리하는데 필요한 table(relation) names의 list
• WHERE 절:
  • condition을 충족한 tuple들만 result에 추가
  • <condition>: query를 통해 탐색하려는 rows(tuples)를 식별하는 조건식
• Logical comparison operators: =,<,≤,>,≥,<>
• Projection attributes
  • 탐색된 값들을 가지고 있는 attributes
• Selection condition -> 'WHERE' 
  • 어떠한 탐색된 tuple은 항상 TRUE여야 하는 Boolean 조건문
  • 여러 relations이 포함될 때는 "join" condition을 포함

Basic Retrieval Quries - Simple (No Join)

• Q0. Retrieve the birth date and address of the EMPLOYEE(s) whose name is ‘John B. Smith’
  • Q0은 WHERE절의 조건을 만족하는 FROM절의 EMPLOYEE의 개별 tuples을 선택하고, SELECT절에 있는 Bdate, Address attributes를 project한다.
  • Result:

Basic Retrieval Queries - 2-way Join

2개의 table이 join에 참여

• Q1. 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)
  • Select-Project-Join(SPJ) query
  • Result:

Basic Retrieval Queries - 3-way Join

• Q2. 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's FK), Dnumber(DEPARTMENT's PK) => 첫 번째 join
• Mgr_ssn(DEPARTMENT's FK), Ssn(EMPLOYEE's PK) => 두 번째 join
  • Result:

Ambiguous Attribute Names

• 2개 또는 그 이상의 attribute에 대해, 동일한 이름이 사용될 수도 있다.
  • 서로 다른 tables에 있는 경우에도 마찬가지
  • 어떠한 문제가 발생할 수 있는가? Join하여 query 작성 시 어떤 attribute인지 구별이 어려움
• 이러한 모호성을 없애기 위해, table name과 함께 fully-qualified attribute name을 사용해야 한다.
• 아래의 경우를 생각해보자.
  • In EMPLOYEE: Dno -> Dnumber | Lname -> Name 
  • In DEPARTMENT: Dname -> Name
    => Join 시에 구분이 어려움
  • 모호성 제거를 위해 아래와 같이 기술
  • Table name을 붙임으로써 모호성을 제거되었지만, Query를 작성하는 성능의 저하를 초래 => "Aliasing" 필요

Aliasing, and Renaming

모호성 제거를 위해 별칭을 사용

• attribute name의 모호성은 동일한 relation에 두 번 참조하는 query의 경우에도 발생 (EMPLOYEE.Ssn, EMPLOYEE.Super_ssn의 경우?)
  • 이를 해결하기 위해, "alternative table names"를 선언한다.
    EMPLOYEE table을 query 안에서 두 번 참조하는 경우, aliases 또는 tupe variable인 E & S를 선언
• Query: For each employee, retrieve the employee’s first & last name and the first & last name of his/her supervisor.
  • Self-join
  • Result:

    

• Attribute name은 아래의 위치에서도 rename될 수 있다.
  • FROM 절
    

    

  • SELECT 절
    

    

    • E_Fn, E_Ln, S_Fn, S_Ln은 result set에 뜨는 name

Unspecified WHERE clause in a Query

WHERE 절이 없는 경우

• tuple selection에서 아무 조건 없이 적용함을 의미
• FROM 절에서 참조된 table의 Cartesian product의 결과
  • Outcome: 모든 가능한 tuple combinations

Use of the Asterisk(*)

• “Retrieve all the attribute values of the selected tuples.”
  • * 는 relation name이나 alias의 접두사로 사용할 수 있다.
    • e.g., EMPLOYEE.* (E.*)

Table as Sets(a Multiset) in SQL (1/4)

SQL은 자동적으로 query results에 있는 중복 tuples를 제거하지 않는다.

• 중복제거의 비용이 크기 때문
• Formal한 SQL에서는 중복을 제거

Table as Sets(a Multiset) in SQL (2/4)

• 왜 중복된 tuple을 제거하지 않는가 ?
  1) 중복 제거는 비용이 크다. tuple들을 먼저 정렬하고, 그 이후에 중복을 제거
  → tuple의 양이 많은 경우, 정렬 + 제거의 비용이 증가
  2) query의 결과에 있는 중복된 tuples을 사용자가 원하는 경우가 존재할 수 있다.
  3) aggregate function(집계 함수: sum, avg, max, distinct, ...)이 tuples에 적용될 때, 대부분의 경우에 중복 제거를 원치 않는다.
• 중복 제거를 원하는 경우, DISTINCT 키워드를 사용

Table as Sets(a Multiset) in SQL (3/4)

Set operations

: UNION(set union), EXCEPT(set difference), INTERSECT(set intersection)

• multiset operations(중복 다중 집합): UNION ALL, EXCEPT ALL(or MINUS), and INTERSECT ALL
• 이러한 set operations가 유효하기 위해서, resultset tables가 서로 Type-compatible해야 한다.
  1) 두 relations가 동일한 attributes(names)를 가지고 있어야 하고, (AS 키워드로 rename이 필요할 수도 있다.)
  2) 두 relations에서 attributes가 같은 순서로 나타나야 한다.

Table as Sets(a Multiset) in SQL (4/4): 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."

Several More Features of SQL: Substring Pattern Matching

부분 문자열 패턴 매칭

• LIKE 비교 연산자: 문자열 패턴 매칭을 위해 사용
  • 부분 문자열은 2개의 문자로 명시될 수 있다:
    1) '%'는 0개 이상의 문자를 대체
    E.g., “Retrieve all employees whose address is in Houston, Texas."
    

    

    
    2) '_'는 1개의 문자를 대체
    E.g., “Retrieve all male employees that were born during 1990s.”
    

    

• attribute value가 '%'나 '_'를 포함하고 있는 경우 '\'문자 사용

Several More Features of SQL: Arithmetic Operations

산술 연산

• 기본 arithmetic operators: +, -, *, /
  • “Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise.”
    

    

• BETWEEN 비교 연산자:
  

  

  • 이상, 이하를 의미

Ordering of Query Results

질의 결과 정렬

• ORDER BY 구문 사용
  

  

  

  • DESC: 내림차순, ASC: 오름차순
  • 개별적 정렬방식 지정 가능

Summary: Basic SQL Query Block

INSERT, DELETE, AND UPDATE STATEMENTS IN SQL

Commands for Database Modification

INSERT

relation에 tuple을 insert

UPDATE

조건을 만족하는 relation에 있는 tuples를 update (DELETE+INSERT로 구현 가능)

DELETE

조건을 만족하는 relation에 있는 tuples를 update하는 것과 유사

The INSERT Command

• CREATE TABLE command로 생성한 attributes의 순서와 동일하게 insert해야 함
• data types에 대한 제약조건은 자동적으로 관찰되어, 위반하는 경우 insertion을 reject
• NULL값을 포함한 모든 값들이 attributes 순서에 맞게 제공되어야 한다.
  

  

• 혹은 아래와 같이, 원하는 attributes에 대한 값만을 insert할 수도 있다.
  

  

  • 명시되지 않은 attributes에 대해서는 DEFAULT or NULL로 set
• SELECT 의 결과를 아래와 같이 INSERT 할 수도 있다.
  

  

• table이 없는 경우 아래와 같이 생성하고 SELECT 의 결과를 적재할 수도 있다.
  

  

• 많은 tuples를 "bulk-loading"(대용량 적재)하는 경우에도 사용될 수 있다.
• 새로운 table(say, D5EMPS)는 이미 존재하는 table(say, EMPLOYEE)과 동일한 attributes를 가진다. 이때, LIKE & WITH DATA 를 이용해서 생성할 수 있다.
  

  

  • Table 생성과 적재를 동시에 수행
  • EMPLOYEE table을 그대로 이용하고, query의 결과를 data로 사용하여 INSERT

The DELETE Command

relation에서 tuples 삭제

• 삭제할 tuples를 선택하기 위해 WHERE 절 포함
  • WHERE 절을 만족하는 tuple의 수에 따라 삭제되는 tuple 수가 결정됨
• tuples는 한 번에 한 table에서만 삭제가 가능
• Referential integrity만 위반될 가능성이 존재
  • 만약 CASCADE가 referential integrity constraint에 의해 명시되었다면, 참조하는 모든 tuple들 또한 삭제된다.
• WHERE 절이 없는 경우
  • table의 모든 tuples가 삭제되고 텅 빈 table만 남게 된다.
  • table을 제거하는 방법은 ?
• Ex)

The UPDATE Command

• 하나 이상의 선택된 tuples의 attribute value를 수정하기 위해 사용
• 각 command는 동일한 relation에 있는 tuples를 수정
• WHERE 절로 수정할 tuples를 선택
• 추가적으로 붙는 SET-절은 UPDATE할 값을 지정하는 역할
  • referential triggered action이 DDL의 referential integrity constraint에서 지정된 경우, PK 값을 update하는 것은 다른 tables에 있는 tuples의 FK 값에도 전파될 수 있다.
  • data의 일관성을 유지하기 위함
• Ex) Change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively.
  

  

  • P.Dnum = 5 -> 참조하는 다른 tuple의 attribute 값도 변경해야 함.