Chapter 6: 기본 SQL (구조적 질의 언어)

배경

• SQL

• 튜플 계산법 (또는 관계형 술어)에서 기원
• 튜플 계산법: 최초로 SQUARE 언어로 제안됨

• 원래 SEQUEL (Structured English QUEry Language)이라고 불렸음 [SIGMOD’74]

• 실험적인 관계형 데이터베이스 시스템인 SYSTEM R의 인터페이스로 IBM 연구에서 설계 및 구현됨
• IBM은 SQL이라는 용어에 저작권을 가졌음

• 이제 RDBMS의 표준 언어

• 구문이 있는 관계 데이터 모델의 “비공식적인” 또는 “실용적인” 렌더링

• SQL은 어떻게 발전했는지 알고 싶으면 부록 참고

SQL 데이터 정의 및 데이터 유형

SQL의 스키마와 카탈로그 개념 (데이터 정의 언어: DDL)

• 기본적인 표준 SQL 문법을 다룸

• 기존 RDBMS에서 변형이 있을 수 있음

• SQL 스키마 (일부 시스템에서는 데이터베이스라고 함)
• SQL92 (또는 SQL2)부터 동일한 데이터베이스에 속한 테이블/기타 구조를 그룹화하기 위해 개념이 포함되었음
• 스키마 이름으로 식별됨
• 예) 우리의 연구실을 위해 ‘university’가 생성되었음
• 스키마에는
1. 스키마의 소유자를 나타내는 인증 식별자(예, ‘Joshua’)와
2. 스키마의 각 요소에 대한 설명자(예, ‘CREATE TABLE ...’)가 포함됨

• 스키마 요소 (구조)에는 다음이 포함됨
• 테이블 (정식 용어로 관계), 제약조건, 뷰, 도메인 및 기타 구조체

• SQL의 각 문은 일반적으로 세미콜론 (;)으로 끝남
• 이는 Oracle에 적용되지만, 다른 RDBMS에 대해서는 항상 그럴 수 없음...

• 스키마를 생성하려면 CREATE SCHEMA 문을 사용하면 됨
• 모든 스키마 요소의 정의를 포함할 수 있음
• 예시: CREATE SCHEMA COMPANY AUTHORIZATION ‘Joshua’

• 그러나 모든 사용자가 스키마와 스키마 요소를 생성할 수 있는 것은 아님
• 스키마, 테이블 및 기타 구조를 생성하는 권한은 DBA에 의해 관련 사용자 계정에 명시적으로 부여되어야 함

• 카탈로그: 스키마의 명명된 컬렉션
• 항상 특별한 스키마인 INFORMATION_SCHEMA를 포함함
• 카탈로그 내 모든 스키마 및 이러한 스키마의 모든 요소 설명자에 대한 정보를 제공함
• 정합성 제약조건은 "동일한 카탈로그" 내에 있는 스키마에서만 관계 사이에서 정의될 수 있음
• 동일한 카탈로그 내에서 스키마는 특정 요소를 공유할 수 있음: 타입/도메인 정의.

SQL에서 CREATE TABLE 명령어

• 새로운 관계, 예를 들면 을 지정(정의)함
• 에 이름을 부여함
• 의 속성, 그들의 타입 (도메인), 그리고 초기 제약조건을 지정함
• 속성은 처음에 지정되고 정의됨
• 각 속성에는 이름, 값을 지정하기 위한 데이터 유형, NOT NULL 또는 UNIQUE과 같은 제약이 지정됨

• 키, 엔터티 무결성 및 참조 무결성 제약조건은 속성 선언 후 명령어(문) 내에서 지정될 수 있음
• 제약조건은 필요에 따라 ALTER TABLE 문을 사용하여 나중에 추가될 수 있음

• "스키마"를 선택적으로 지정할 수 있음
• CREATE TABLE COMPANY.EMPLOYEE ... (명시적)
• 또는 간단히 CREATE TABLE EMPLOYEE ... (암시적)

• 기본 테이블 (기본 관계)
• 테이블(관계)과 그 행(튜플)들은 물리적으로 실제로 DBMS에 의해 파일로 생성되고 저장됨

• 가상 관계 (뷰)
• CREATE VIEW 문을 통해 생성됨
• 물리적 파일에 대응할 수도 있고 그렇지 않을 수도 있음
• 뷰에 속한 관련 튜플은 뷰가 참조될 때 계산되지만 물리화되지 않는 한 계산되지 않음
• 왜 필요한가?
• 공간 절약, 가벼운 계산, 복잡한 조인 → 단순 테이블

COMPANY 관계형 스키마 회상

하나의 가능한 데이터베이스 상태

COMPANY 스키마를 정의하기 위한 SQL CREATE TABLE 데이터 정의문

CREATE TABLE EMPLOYEE
(Fname VARCHAR(15) NOT NULL,
Minit CHAR,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Sex CHAR,
Salary DECIMAL(10,2),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY (Ssn),

CREATE TABLE DEPARTMENT
( Dname VARCHAR(15) NOT NULL,
Dnumber INT NOT NULL,
Mgr_ssn CHAR(9) NOT NULL,
Mgr_start_date DATE,
PRIMARY KEY (Dnumber),
UNIQUE (Dname),
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn));

CREATE TABLE DEPT_LOCATIONS
( Dnumber INT NOT NULL,
Dlocation VARCHAR(15) NOT NULL,
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber));

CREATE TABLE PROJECT
( Pname VARCHAR(15) NOT NULL, Pnumber INT NOT NULL, 
Plocation VARCHAR(15),
Dnum INT NOT NULL, PRIMARY KEY (Pnumber),
UNIQUE (Pname),
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber));

CREATE TABLE WORKS_ON
(Essn CHAR(9) NOT NULL, 
Pno INT NOT NULL,
Hours
DECIMAL(3,1)
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber));

CREATE TABLE DEPENDENT
(Essn CHAR(9) NOT NULL,
Dependent _name VARCHAR(15) NOT NULL,
Sex CHAR,
Bdate DATE,
Relationship VARCHAR(8),
PRIMARY KEY (Essn, Dependent_name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn));

• 우리 연구실의 Oracle DBMS는 이 구문을 그대로 받아들이지 않음
• 일부 외래 키는 왜 에러를 일으킬까?
• 순환 참조 (Circular references): 예를 들어 EMPLOYEE 테이블의 Super_ssn
• 또는 아직 생성되지 않은 테이블을 참조하기 때문임
• 예를 들어, EMPLOYEE 테이블의 Dno는 아직 생성되지 않은 DEPARTMENT 테이블을 참조함
• 어떻게 처리하나? ALTER TABLE 문을 사용함
• 필요한 경우 동일한 문을 사용하여 나중에 기본 키를 선언할 수 있음

-- Add a foreign key constraint to the EMPLOYEE table
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(55n);
-- Add another foreign key constraint to the EMPLOYEE table
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber);

 

SQL에서의 속성 데이터 유형과 도메인

기본 데이터 유형 - 숫자 데이터 유형

• 여러 크기의 정수: INTEGER, INT 및 SMALLINT
• Oracle에서는 DECIMAL을 대체할 수 있는 NUMBER

• 부동소수점 (실수) 숫자: FLOAT 또는 REAL 및 DOUBLE PRECISION

• 형식화된 숫자는 DECIMAL(i, j) (또는 DEC(i, j) 또는 NUMBERIC(i, j))를 사용하여 선언할 수 있음

• : 정밀도, 소수점 이하의 총 숫자 개수

• : 스케일, 또는 소수점 이하의 숫자 개수
• 스케일의 기본값: 0, 정밀도의 기본값: 구현에 따라 다름

기본 데이터 유형 - 문자열 데이터 유형

• 고정 길이: CHAR(n), CHARACTER(n)
• : 문자의 최대 개수
• 더 짧은 문자열은 오른쪽으로 공백 문자로 채워짐

• 가변 길이: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)
• CLOB (CHARACTER LARGE OBJECT): “문서”와 같은 큰 텍스트 값을 지정하기 위해 사용됨
• 크기: 킬로바이트 (K), 메가바이트 (M) 또는 기가바이트 (G)
• 예) CLOB(20M): 최대 20MBytes까지

기본 데이터 유형 - 비트 문자열 데이터 유형

• 고정 길이: BIT(n)
• : 비트의 최대 개수
• 값을 배치하려면 다음 형식을 사용함: B‘10101’: 왜?

• 가변 길이: BIT VARYING(n)
• BLOB (BINARY LARGE OBJECT): 실행 가능한 코드 또는 자바 객체와 같은 큰 이진 값을 지정하기 위해 사용됨
• 크기: 킬로바이트 (K), 메가바이트 (M) 또는 기가바이트 (G)
• 예) BLOB(20M): 최대 20MBytes까지

기본 데이터 유형 – 불린 데이터 유형

• TRUE 또는 FALSE 또는 NULL의 값

기본 데이터 유형 – 날짜 데이터 유형

• 열 개의 위치
• 구성 요소: 년, 월, 일 형태로 YYYY-MM-DD
• 예) DATE ‘2014-09-27’: Oracle에서 to_date()는 ‘DATE’ 역할을 함

기본 데이터 유형 – 시간 데이터 유형

• 최소 여덟 개의 위치
• 구성 요소: HH:MM:SS 형태로 HOUR, MINUTE, SECOND
• 예) TIME ’09:12:47’

• 날짜 및 시간 데이터 유형에 대해서는 유효한 날짜 및 시간만 허용됨

• 날짜/시간 비교에 대해 ‘<’ 또는 ‘>’를 지정할 수 있음
• 예) ‘2018-10-02’ < start_date

기본 데이터 유형 – 타임스탬프 데이터 유형

• DATE 및 TIME 필드를 포함함
• 초의 소수점 이하 최소 여섯 자리
• WITH TIME ZONE은 선택 사항
• 예) TIMESTAMP ‘2014-09-27 09:12:47.648302’
• 날짜, 시간, 타임스탬프, INTERVAL 데이터 유형은 문자열 형식으로 형변환 또는 변환하여 비교할 수 있음

기본 데이터 유형 – INTERVAL 데이터 유형

• 날짜, 시간 또는 타임스탬프의 절대값을 증가 또는 감소시키는 데 사용할 수 있는 상대 값이 지정됨

• 사용하기 위한 기간을 저장함. 예, INTERVAL '40' MONTH = INTERVAL '3-4' YEAR TO MONTH

도메인

• 열에 대한 데이터 유형을 지정하는 대신 선언될 수 있음

• 속성 사양과 함께 이름을 사용할 수 있음

• 장점
1. 도메인에 대한 데이터 유형을 변경하기 쉬움: 예) 우편 코드.
2. 스키마 가독성을 향상시킴

• 예 (Oracle 11g/12c/19c에서의 CREATE TYPE에 동등)
• CREATE DOMAIN SSN_TYPE AS CHAR(9);

• EMPLOYEE의 Super_ssn, DEPARTMENT의 Mgr_ssn, WORKS_ON의 Essn, DEPENDENT의 Essn에 사용될 수 있음

유형

• 사용자 정의 유형 (UDTs)을 만드는 데 사용할 수 있음

• 객체 지향 애플리케이션을 지원함

• 명령어를 사용함 (Oracle에서 지원)

CREATE TYPE type_demo as object (
customer_id
NUMBER(6),
customer_last_name UARCHAR2(20)
);

SQL에서 제약 조건 지정

SQL에서 제약 조건 지정

• 기본 제약 조건을 상기해보면,
1. 키 제약 조건: 기본 키 값은 중복될 수 없음
2. 엔터티 제약 조건: 기본 키 값은 NULL이 될 수 없음
3. 참조 무결성 제약 조건: "외래 키"
• 이미 다른 테이블(들)의 기본 키로 존재하는 값이어야 하거나,
• NULL일 수 있음
• 그렇다면 이러한 제약 조건을 SQL에서 어떻게 지정하나?
• 적절한 예약어 사용: PRIMARY KEY ((1)을 위해), NOT NULL ((2)을 위해), FOREIGN KEY ((3)을 위해)

"속성" 제약 조건 지정하기

• 속성의 기본 값
• DEFAULT <value> 절
• NULL
• 특정 속성에 허용되지 않음: NOT NULL로 표현
• CHECK 절
• Dnumber INT NOT NULL CHECK (Dnumber > 0 and Dnumber < 10)
• Age INT NOT NULL CHECK (Age >= 15 and Age < 65)
• 스키마 기반 제약 조건(시맨틱 제약 조건도 포함)에 의해 지원됨

“키” 제약 조건 지정하기

• PRIMARY KEY 절
• 관계의 기본 키를 구성하는 하나 이상의 속성을 지정함
• Dnumber INT PRIMARY KEY
• PRIMARY KEY(State, Number)

• UNIQUE 절
• 관계형 모델에서 대체(보조) 키인 후보 키를 지정함
• Dnumber VARCHAR(15) UNIQUE
• UNIQUE(State, Number)

"참조 무결성" 제약 조건 지정하기

• FOREIGN KEY 절
• 기본 연산: 위반 시 "거부"(삭제/삽입 포함) 업데이트
• 참조 트리거 액션 절 첨부
• 옵션: SET NULL | CASCADE | SET DEFAULT
• SET DEFAULT: Oracle에서는 지원되지 않음
• SET NULL 또는 SET DEFAULT에 대해 DBMS가 취하는 조치는 ON DELETE와 ON UPDATE 모두에 대해 동일함
• CASCADE 옵션은 참조하는 튜플도 삭제함
• 일관성을 유지하는 것이 중요하다고 판단되는 relationship relation에 적합함

제약 조건에 이름 부여하기

• CONSTRAINT 키워드를 사용하여
• 특정 제약 조건의 이름: 나중에 변경하기 유용
• 예1) CONSTRAINT reg_no_u UNIQUE (State, Number)
• 예2) dno CONSTRAINT fk_deptno REFERENCES Department(Dnumber)

• Oracle은 이 뷰를 제약 조건을 관리하기 위해 사용함

CHECK를 사용하여 튜플에 대한 제약 조건 지정하기

• 관계 내 개별 튜플에 대한 추가 제약 조건은 CHECK를 사용하여 지정할 수 있음

• CREATE TABLE 문의 끝에 있는 CHECK 절
• 각 튜플에 개별적으로 적용; 행 기반 제약 조건이라고 불림
• 튜플이 삽입되거나 수정될 때마다 확인됨

• 또한, CHECK는 CREATE ASSERTION 문에서 사용할 수 있음

제약 조건 지정 예제

CREATE TABLE EMPLOYEE
(..., Dno INT NOT NULL DEFAULT 1,
CONSTRAINT EMPPK 
 PRIMARY KEY (Ssn),
CONSTRAINT EMPSUPERFK 
 FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT EMPDEPTFK 
 FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber) ON DELETE SET DEFAULT ON UPDATE CASCADE);

CREATE TABLE DEPARTMENT
(....
Mgr_ssn CHAR(9)NOT NULL DEFAULT 888665555',
... 1
CONSTRAINT DEPTPK 
 PRIMARY KEY (Dnumber),
CONSTRAINT DEPTSK 
 UNIQUE (Dname),
CONSTRAINT DEPTMGRFK
 FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET DEFAULT ON UPDATE CASCADE);

CREATE TABLE DEPT_LOCATIONS
(...,
PRIMARY KEY (Dumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) ON DELETE CASCADE ON UPDATE CASCADE);

SQL에서 기본 검색 쿼리

기본 SQL 쿼리 블록

SELECT <attribute list>
FROM <relation list>
[ WHERE <condition> ]
-- for aggregates
[ GROUP BY <attribute list>]
[ HAVING <condition> ]
[ ORDER BY <attribute list> [DESC] ];

• SELECT 문
• 데이터베이스로부터 정보를 검색하기 위한 기본 문장(그러나 관계 대수에서의 “선택” 연산과 동일하지 않음)

• 실용 모델(또는 SQL)과 공식적인 관계형 모델(또는 관계 해석/대수) 간의 중요한 차이점
• SQL은 쿼리 결과로 모든 속성 값이 동일한 두 개 이상의 튜플을 가진 테이블을 허용합니다
• 튜플의 다중집합(또는 결과 집합)(가방이라고 불림)
• 쿼리 결과에서 행을 고유하게 식별하기 위해 행 ID(인공 식별자)를 사용할 수 있습니다.
• SQL에서 중복을 왜 허용할까?

SQL에서 중복 튜플을 허용하는 이유는 실제 세계의 요구사항과 데이터 관리의 유연성을 반영하기 위해서입니다. 데이터 표현의 유연성: 실세계의 데이터는 복잡하고 다양한 형태를 가질 수 있습니다. 특정 상황에서 동일한 속성 값을 가진 데이터 항목(튜플)이 여러 개 존재할 수 있습니다. 예를 들어, 서로 다른 사용자들이 동일한 제품을 구매한 경우를 생각해볼 수 있으며, 이 경우 각각의 구매 트랜잭션은 별개이지만 동일한 값을 가질 수 있습니다. 성능 및 저장 공간 최적화: 중복 값을 제거하는 과정은 추가적인 계산을 필요로 하며, 때로는 큰 데이터 세트에서 비효율적일 수 있습니다. 중복을 허용함으로써, 시스템은 중복 제거를 위한 추가 작업 없이 데이터를 더 빠르게 검색하고 처리할 수 있습니다. 집계 및 분석 용이성: 데이터베이스에서 집계 함수나 분석 쿼리를 실행할 때, 실제 발생한 모든 이벤트를 반영하는 것이 중요할 수 있습니다. 중복 튜플을 허용함으로써, 데이터베이스는 정확한 집계 및 분석을 제공할 수 있으며, 이는 특히 비즈니스 인텔리전스와 데이터 분석 분야에서 중요합니다. 하지만, 중복 튜플의 존재는 데이터 무결성과 관리 측면에서 문제를 일으킬 수도 있기 때문에, SQL은 'DISTINCT' 키워드를 제공하여 필요에 따라 중복을 제거하고 고유한 값만을 검색할 수 있도록 합니다. 또한, 특정 상황에 맞게 데이터 모델을 설계함으로써 (예: 기본키, 고유한 제약 조건 등을 사용하여) 중복을 방지할 수도 있습니다.

• SELECT 문의 기본 형태, 또는 SQL “쿼리”:
• <속성 목록> FROM <테이블 목록> [WHERE <조건>]
• SELECT Name FROM Employee WHERE Age >= 20 [쿼리의 예]

• SELECT 절:
• <속성 목록>: 쿼리에 의해 검색되어야 하는 속성 이름의 목록(쉼표로 구분)(프로젝션으로 알려짐)

• FROM 절:
• <테이블 목록>: 쿼리를 처리하는 데 필요한 테이블(관계) 이름의 목록

• WHERE 절:
• <조건>: 쿼리에 의해 검색되어야 하는 행(튜플)을 식별하는 조건(불린) 표현식(선택으로 알려짐)
• 참고) 위에서 보는 바와 같이, SQL 쿼리는 어떻게 데이터를 가져올지 지시하지 않고 어떤 데이터를 검색하길 원하는지 기술할 수 있게 합니다. 따라서 비절차적이고 선언적(non-procedural, declarative)입니다.

• 논리 비교 연산자: =, <, <=, >, >=, 그리고 <>
• 속성 값과 다른 속성 값, 그리고 리터럴 상수를 비교하기 위해: 예, “Employee.age >= 20”

• 프로젝션 속성
• 검색되어야 하는 속성의 값

• 선택 조건
• 검색된 튜플에 대해 참이어야 하는 불린 조건을 나타냅니다.
• 여러 관계가 관여되었을 때 “조인” 조건을 포함합니다.

기본 검색 쿼리 - 단순(조인 없음)

• Q0. 이름이 ‘John B. Smith’인 EMPLOYEE의 출생일과 주소를 검색하세요.

SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname = 'John' AND Minit = 'B' AND Lname = 'Smith';

• Q0는 WHERE 절의 조건을 만족하는 EMPLOYEE의 개별 튜플을 FROM 절에서 선택하고, SELECT 절에서 나열된 Bdate와 Address 속성에 결과를 투영합니다.
• 쿼리 결과

Bdate	Address
1965-01-0	731 Fondren, Houston, TX

기본 검색 쿼리 – 2-way 조인

SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = 'Research' -- 선택 조건
AND Dnumber = Dno; -- 조인 조건

• select-project-join (SPJ) 쿼리라고 불림

• 쿼리 결과

Fname	Lname	Address
John	Smith	731 Fondren, Houston, TX
Franklin	Wong	638 Voss, Houston, TX
Ramesh	Narayan	975 Fire Oak, Humble, TX
Joyce	English	5631 Rice, Houston, TX

기본 검색 쿼리 – 3-way 조인

• 프로젝트 번호,

• 관리 부서 번호, 그리고

• 부서 관리자의 성, 주소, 그리고 출생일을 나열하시오.

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber -- PROJECT와 DEPARTMENT 사이의 조인
AND Mgr_ssn = Ssn -- DEPARTMENT와 EMPLOYEE 사이의 조인
AND Plocation = 'Stafford'; -- PROJECT에 대한 선택 조건

• 쿼리 결과

Pnumber	Dnum	Lname	Address	Bdate
10	4	Wallace	291 Berry, Bellaire, TX	1941-06-20
30	4	Wallace	291 Berry, Bellaire, TX	1941-06-20

모호한 속성 이름

• 동일한 이름을 두 개 이상의 속성에 사용할 수 있음
• 속성이 다른 테이블에 있을 때임
• 문제가 있는지?

• 동일한 속성 이름에 의해 발생하는 모호성을 제거하려면 테이블 이름과 함께 완전한 속성 이름을 사용해야 함

• 다음과 같이 일부 속성의 이름이 바뀌었다고 가정해보면
• EMPLOYEE에서: Dno -> Dnumber | Lname -> Name
• DEPARTMENT에서: Dname -> Name
• 이러한 모호성은 어떻게 제거할 수 있을지?

SELECT Fname, EMPLOYEE.Name, Address
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT Name = 'Research'
AND DEPARTMENT,Dnumber = EMPLOYEE.Dnumber;

별명과 이름 바꾸기

• 속성 이름의 모호성은 쿼리가 동일한 관계를 두 번 참조하는 경우에도 발생함

• 모호성을 어떻게 제거할지? “대체 테이블 이름”을 선언하여, 즉 와 라고 하여, 쿼리에서 EMPLOYEE 테이블을 두 번 참조하기 위해 별칭이나 튜플 변수를 사용함

• 그 쿼리에 대해 별칭 사용 또는 이름 변경 메커니즘을 사용함

• 쿼리: “각 직원에 대해, 직원의 이름과 성, 그리고 그/그녀의 상사의 이름과 성을 검색” 와 각각은 무엇을 나타내는지?

SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE (AS) E, EMPLOYEE (AS) S
WHERE E.Super_ssn = S.ssn;

• 별칭 사용에 대한 추천된 관행
• 테이블 이름: 그것의 첫 글자를 별칭으로 사용함
• 속성 이름: 별칭이 지정된 테이블 이름을 접두어로 하고 속성 이름을 연결함 (예: E_Fname, S_Fname).

• 쿼리 결과

E.Fname	E.Lname	S.Fname	S.Lname
John	Smith	Franklin	Wong
Franklin	Wong	James	Borg
Alicia	Zelaya	Jennifer	Wallace
Jennifer	Wallace	James	Borg
Ramesh	Narayan	Franklin	Wong
Joyce	English	Franklin	Wong
Ahmad	Jabbar	Jennifer	Wallace

• 속성 이름 역시 다음과 같이 변경(재명명)될 수 있음
• FROM 절에서

EMPLOYEE (AS) E (Enm, Mi, Lnm, Ssn, Bd, Addr, Sex, Sal, Sssn, Dn)

=> 이제 EMPLOYEE 관계(relation)는 E라는 변수 이름, 또는 튜플 변수(tuple variable)를 가짐
• SELECT 절에서

SELECT E.Fname AS E_Fn, E.Lname AS E_Ln, S.Fname AS 5_Fn, S.Lname AS S_Ln

=> 이제 결과 세트(resultset)는 E라는 변수 이름, 또는 튜플 변수를 가짐

쿼리에서 지정되지 않은 WHERE절

• 누락된 WHERE 절
• 튜플 선택에 조건이 없음을 나타냄
• 모든 가능한 튜플 조합의 결과를 가져옴
• FROM 절에서 참조된 테이블의 카르테시안 곱(Cartesian product)을 결과로 가져옴
• 결과: 가능한 모든 튜플 조합

SELECT E.Ssn FROM EMPLOYEE E

SELECT Ssn, Dname FROM EMPLOYEE, DEPARTMENT

별표(*)의 사용

• "선택된 튜플의 모든 속성 값을 검색함"

• ‘*’는 관계 이름(또는 그 별칭)에 의해 접두어로 사용될 수 있음: 예를 들면, EMPLOYEE.* (또는 E.*)

SELECT * FROM EMPLOYEE WHERE Dno = 5;

SQL에서의 테이블(다중 집합)

• 중복 튜플을 왜 제거하지 않는지?
1. 중복 제거는 비용이 많이 드는 작업임. 어떻게 제거?
• 먼저 튜플을 정렬한 다음 중복을 제거함
2. 사용자는 쿼리의 결과에서 중복 튜플을 “보고” 싶을 수 있음
3. 집계 함수(예: sum, avg, max, distinct 등)가 튜플에 적용될 때 대부분의 경우 우리는 중복을 제거하고 싶지 않음

• 만약 결과에서 중복을 제거하고 싶다면, 어떻게 해야 하는지?

SELECT DISTINCT Salary FROM EMPLOYEE

• 위 쿼리의 결과는?

• 집합 연산: UNION (합집합), EXCEPT (차집합), INTERSECT (교집합)

• 해당 다중집합 연산: UNION ALL, EXCEPT ALL (또는 MINUS), INTERSECT ALL

• 이러한 집합 연산을 유효하게 만들려면, 두 개의 (결과 집합) 테이블이 자료형과 관련하여 호환되어야 함
• 유형-호환
1. 두 관계는 동일한 속성(이름)을 가져야 하며,
2. 속성들은 두 관계에서 동일한 순서로 나타나야 함

• "‘Smith’라는 성을 가진 직원이 참여하는 모든 프로젝트 번호 목록을 만듦. 해당 직원은 프로젝트의 작업자 또는 프로젝트를 관리하는 부서의 관리자일 수 있음”

(-- worker
SELECT DISTINCT Pnumber
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber = Pno AND Essn = Ssn
	AND Lname = 'Smith')
UNION
(-- manager
SELECT DISTINCT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dum = Dumber AND Mgr_ssn = Ssn
	AND Lame = 'Smith');

SQL의 몇 가지 추가 기능

부분 문자열 패턴 매칭

• LIKE 비교 연산자: 문자열 패턴 매칭에 사용됨

• 두 개의 예약 문자를 사용하여 부분 문자열을 지정할 수 있음
1. ‘%’는 임의의 수의 문자를 대체함
예) "텍사스 주 휴스턴에 거주하는 모든 직원을 검색하시오”

SELECT Fname, Lname FROM EMPLOYEE WHERE Address LIKE '*Houston, TX%'

2. ‘_’는 단일 문자를 대체함
예) "1990년대에 태어난 모든 남성 직원을 검색하시오”

SELECT Fname, Lname FROM EMPLOYEE WHERE Bdate LIKE '199_____-1______ -- during 1990s

• 만약 "%" 또는 "_"를 포함한 속성 값을 찾는다면 어떻게 될까?

Percent LIKE '2_\%'
Underscore LIKE '%\_%'

산술 연산

• 표준 산술 연산자: +, -, *, /
• SELECT 절의 일부로 포함될 수 있음
• “‘ProductX’ 프로젝트에서 일하는 모든 직원에게 10퍼센트 인상된 급여를 보여주시오”

SELECT E.Fname, E. Lname, 1.1 * E.Salary AS Increased_sal
FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE E.Ssn=W.Essn AND W.Pno=P.Pnumber AND P.Pname='ProductX';

• BETWEEN 비교 연산자

SELECT *
FROM EMPLOYEE 
WHERE (Salary BETWEEN 30000 AND 40000)
AND Dno = 5;

쿼리 결과 정렬

• ORDER BY 절 사용
• 일반적으로 쿼리의 마지막에 배치됨

SELECT D.Dname, E. Lname, E.Fname, P.Pname
FROM DEPARTMENT D, EMPLOYEE E, WORKS_ON W, PROJECT P -- 4-way join
WHERE D. Dumber = E.Dno AND E.Ssn = W.Essn AND W.Pno = P. Pnumber
ORDER BY D.Dname, E.Lname, E. Fname;

• 내림차순의 값에 대해 DESC 키워드를 사용하여 결과를 확인함
• 오름차순의 값에 대해 ASC 키워드를 사용하여 결과를 확인함 (기본값)

요약: 기본 SQL 쿼리 블록

SELECT <attribute list>
FROM <relation list>
[ WHERE <condition> ]
-- for aggregates
[ GROUP BY <attribute list>]
[ HAVING <condition> ]
[ ORDER BY <attribute list> [DESC] ];

SQL에서의 INSERT, DELETE, 및 UPDATE 문

데이터베이스 수정을 위한 명령어

• INSERT
• 일반적으로 관계(테이블)에 튜플(행)을 삽입함

• UPDATE
• 조건을 만족하는 관계(테이블)의 여러 튜플(행)을 업데이트할 수 있음; (DELETE + INSERT로 구현될 수 있음

• DELETE
• 조건을 만족하는 관계(테이블)의 여러 튜플(행)을 업데이트할 수도 있음

INSERT 명령어

• 가장 간단한 형태에서는 하나 이상의 행을 테이블에 추가하는 데 사용됨

• 속성 값은 CREATE TABLE 명령에서 지정된 속성과 동일한 순서로 나열되어야 함

• 데이터 유형에 대한 제약조건은 자동으로 적용됨
• 목록에 잘못된 값이 있는 경우 삽입이 거부됨

• DDL문의 일부로서의 무결성 제약조건이 적용됨
• 예: Key, entity, not-null, unique 등...

• 튜플에 대한 관계 이름과 값 목록을 지정함
• NULL 포함 모든 값이 제공되어야 함

INSERT INTO EMPLOYEE -- 속성이 구체화되지 않음
VALUES ('Richard', 'K', 'Marini', '653298653', '1962-12-30', '98 Oak Forest Katy, TX', 'M', 37000, '653298653', 4);

• 두 번째 형태는 명령에서 제공된 값에 해당하는 명시적인 속성 이름을 사용자가 지정할 수 있게 함
• 직원의 이름, 성, 부서 번호, 그리고 SSN만 알고 있다면, EMPLOYEE에 이러한 튜플을 어떻게 입력하는지?

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)
VALUES ('Richard', 'Marini', 4, '653298653');

→ 지정되지 않은 속성은 DEFAULT 값 또는 NULL로 설정됨

• 아래의 변형은 쿼리 결과로부터 값을 로드하는 새 테이블에 여러 튜플을 삽입함

CREATE TABLE WORKS_ON_INFOC
	Emp_name VARCHAR(15),
	Proi_name VARCHAR(15),
	Hours_per_ week DECIMAL(3, 1));
INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week)
	SELECT E.Lname, P.Pname, W.Hours
	FROM PROJECT P, WORKS_ON W, EMPLOYEE E
	WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;

-- When WORKS_ON_INFO is absent
CREATE TABLE WORKS_ON_INFO AS
SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;

• 여러 튜플을 테이블에 "대용량 적재"하는 데 사용됨

• 새 테이블(예: D5EMPS)은 기존 테이블(예: EMPLOYEE)과 동일한 속성을 사용하여 LIKE 및 WITH DATA 구문에서 생성할 수 있음

• 또한 전체 데이터로 로드될 수도 있음

CREATE TABLE D5EMPS LIKE EMPLOYEE
(SELECT E.*
FROM EMPLOYEE E
WHERE E.Dno=5) WITH DATA;

DELETE 명령어

• 관계에서 튜플을 제거함
• 제거할 튜플을 선택하기 위해 WHERE절을 포함함
• 한 번에 하나의 테이블에서만 튜플이 삭제됨
• 참조 무결성이 강제되어야 함
• 참조 무결성 제약조건에서 CASCADE가 지정되면, 삭제될 튜플을 참조하는 모든 튜플도 함께 제거됨
• 누락된 WHERE절: 테이블의 모든 행이 삭제되어야 하며, 그 결과로 빈 테이블이 생성됨. 그렇다면 테이블을 어떻게 삭제하는지?
• 삭제될 튜플의 수는 얼마인지?
• "WHERE절을 만족하는 테이블의 행 수"에 따라 다름
• 관계에서 튜플을 제거함
• 예시

DELETE FROM EMPLOYEE
WHERE Lname = 'Brown';

DELETE FROM EMPLOYEE
WHERE Ssn = '123456789';

DELETE FROM EMPLOYEE
WHERE Dno = 5;

DELETE FROM EMPLOYEE;

UPDATE 명령어

• 하나 이상의 선택된 튜플의 속성 값을 수정하는 데 사용됨

• 각 명령은 동일한 관계에서 튜플을 수정함

• WHERE절은 수정될 튜플을 선택함

• 추가적인 SET절은 수정될 속성과 그들의 새로운 값을 지정함
• PK 값의 업데이트는 DDL의 참조 무결성 제약조건에 지정된 참조 트리거 작업이 있다면, 다른 테이블의 튜플의 FK 값으로 전파될 수 있음

• 예시: 프로젝트 번호 10의 위치와 컨트롤링 부서 번호를 각각 ‘Bellaire’와 5로 변경함

UPDATE PROJECT p
SET p.Plocation = 'Bellaire', p.Dnum = 5
WHERE p.Pnumber = 10

요약

• SQL
• 관계형 데이터베이스 관리를 위한 종합적인 언어
• 데이터 정의, 쿼리, 업데이트, 제약조건 명세, 그리고 뷰 정의

• 다룸:
• 테이블을 생성하기 위한 데이터 정의 명령어
• 제약조건 명세를 위한 명령어
• 간단한 검색 쿼리
• 데이터베이스 업데이트 명령어
 

부록

SQL 연대기

• SQL의 표준화: 다음 기관의 공동 노력에 의해 이루어짐
• 미국 국립 표준 협회 (ANSI)
• 국제 표준화 기구 (ISO)

• SQL은 많은 표준을 거쳐왔음
• SQL-86 (SQL1)으로 시작
• SQL-92 (SQL2)로 수정 및 크게 확장
• SQL-99로 발전, 최초로 SQL3으로 시작: 현재의 표준
• 추가 업데이트와 함께 SQL-2003
• XML 기능 추가 및 일부 다른 업데이트와 함께 SQL-2006
• 2008년, 객체지향 기능 추가
• SQL-2011로 더욱 업데이트; SQL-2016
• SQL-2023으로 속성 그래프 쿼리, JSON과 관련된 새로운 기능 등 (최신)
• 나중의 표준들 (SQL-99부터)은 다음으로 나뉨
• 핵심 사양: 모든 RDBMS에 의해 구현되어야 함
• 전문 확장: 다양한 애플리케이션을 위해 구현됨 (예, OLAP, 공간/시간 데이터, 멀티미디어)

SQL의 추가 기능

• 복잡한 검색 쿼리를 지정하는 기술 (Ch.7 참조)

• SQL 문을 포함한 다양한 프로그래밍 언어로 프로그램 작성: 내장 및 동적 SQL, SQL/CLI (Call Level Interface) 및 이전 버전인 ODBC, SQL/PSM (Persistent Stored Module) (Ch.10 참조)

• 물리적 데이터베이스 디자인 매개 변수, 관계를 위한 파일 구조, 및 액세스 경로, 예를 들면, CREATE INDEX를 지정하기 위한 명령어 세트

• 트랜잭션 제어 명령어 (Ch.20)

• 사용자에게 권한을 부여하고 취소하는 명세 (Ch.30)

• 트리거를 생성하기 위한 구조 (Ch.26)

• 객체-관계형으로 알려진 향상된 관계형 시스템은 클래스로 관계를 정의함. CREATE TYPE과 함께 지원되는 추상 데이터 유형 (사용자 정의 유형- UDTs로 불림)

• SQL 버전에 XML (Ch.13) 및 OLAP (Ch.29)과 같은 새로운 기술이 추가되었음