SQL의 사용법을 배우자

SQL의 사용법을 배우자(2)

송용준(서울대학교 컴퓨터공학과 박사과정)

지난호에서 기본적인 SQL 사용법을 배운데 이어 이번호에서는 보다 전문적인 SQL 사용법을 다루기로 한다. 먼저 SQL에서 어떤 종류의 값들을 사용할 수 있는지 알아본 다음에 WHERE절에서 데이터 검색 조건을 명시하는데 필수적인 다양한 종류의 술어(predicate)들에 대하여 살펴본다. 이번호에서도 지난호에 이어 <그림1>과 같은 구조의 은행 데이터베이스를 이용하여 SQL 예문들을 작성한다.

SQL에서 사용되는 값의 종류

여러분들 중에서 값이란 무엇인가에 대하여 모르는 사람을 없을 테니까 구태여 설명할 필요는 없겠지만 SQL에서는 이 ‘값’이란 단어가 여러가지 의미로 사용되므로 다소 지루한 글이 될 수도 있지만 그 값의 종류를 하나씩 살펴봄으로써 SQL에서 사용되는 값의 의미를 보다 체계적으로 이해하는데 도움이 될 것이다.

튜플값

테이블 내의 각 튜플의 값으로서 그 값은 다시 컬럼값들로 나눌 수 있다. 이러한 컬럼값은 각 컬럼의 타입에 맞는 값을 갖는데, 그 값은 더 이상 나누어질 수 없는 원소성을 갖는다. 이와 같은 튜플값은 테이블 내에 존재하는 값이기 때문에 실제로는 SQL에서 직접 사용할 수 있는 것이 아니라 SQL을 통해서만 접근이 가능하다는 특징을 갖는다. 이 특징에 대해서는 실제 SQL문을 살펴보면 쉽게 이해할 수 있을 것이다.

문제) 박 영희 고객의 주소를 검색하라.

질의) SELECT 주소 FROM 고객 WHERE 고객명 = ‘박영희’;

위의 예에서 보듯이 질의로 작성한 SQL문에서는 고객 테이블의 튜플값을 직접 접근하기 위한 구문이 전혀 없다. 그러면 테이블 내의 튜플값들을 어떻게 접근하는 것일까? 그것을 알기 위해서 DBMS 내에서의 SQL 처리 과정을 잠깐 살펴보기로 하자. SQL문을 입력받은 DBMS는 먼저 문법적으로 오류가 없는지 검사한 다음에 내부적으로 처리하기 쉬운 포맷으로 변환하고 효율적으로 처리할 수 있는 연산들의 순서를 찾는다. 그런 다음에 대상이 되는 테이블 내의 튜플들을 하나씩 차례로 접근하여 그 튜플이 주어진 조건을 만족하는 경우라면 지정한 작업들을 수행하게 된다. 즉 DBMS 차원에서 SQL문에서 명시한 테이블 내의 모든 튜플들을 하나씩 차례로 접근하여 처리하기 때문에 사용자는 튜플값의 접근에 대해서는 전혀 신경쓸 필요가 없는 것이다. 그 대신에 사용자는 대상이 되는 테이블 이름들과 컬럼 정보를 이용한 검색 조건 및 수행 작업을 SQL문에 명시한다. 이 예에서는 주소, 고객명과 같은 컬럼의 이름을 이용하여 특정 튜플 내의 컬럼값을 접근하게 되는데, 이와 같이 SQL문에 사용된 컬럼의 이름을 컬럼 참조(reference)라 한다. 하나의 테이블만을 접근하는 SQL문에서는 컬럼 참조는 단순히 컬럼 이름만을 사용하는 것이 일반적이지만 같은 이름의 컬럼을 포함하는 둘 이상의 테이블을 접근할 때에는 다음 구문과 같이 테이블 이름을 함께 명시하여 구별한다.

—、 테이블 이름 —、 . —、 컬럼 이름

예를 들어 <그림1>의 고객 테이블과 예금 테이블은 주민등록번호라는 같은 이름의 컬럼을 포함하는데, 그 컬럼이 한 SQL문에서 함께 사용된다면 ‘고객.주민등록번호’와 ‘예금.주민등록번호’와 같이 표현하여 구별하는 것이다.

상수값

상수라는 것은 보이는 그대로의 실제 값을 의미하는 것으로서 흔히 리터럴(literal)이라고도 한다. 각 데이터 타입은 그것에 맞는 상수형을 가지고 있다. 예를 들어 INTEGER 타입의 상수로 0, 1, 100, 1000 등의 값을 가질 수 있으며 CHARACTER 타입의 상수로 ‘가나다라’, ‘ABCD’ 등의 값을 가질 수 있다. 또한 비트 문자열 상수는 B’1001′, B’11’ 등과 같은 포맷을 갖는다. 여기서 볼 수 있듯이 상수값은 작은따옴표(”) 내에 포함된다. 그러면 만일 문자열 상수 내에 작은따옴표를 포함하는 경우는 어떻게 될까? 예를 들어 ‘This’s Jun’s book’이란 문자열을 상수로 지정할 때 단순히 작은따옴표를 양쪽에 붙이면 상수의 처음과 끝을 나타내는 작은따옴표와 실제 문자열 내의 작은 따옴표를 구분할 수 없게 된다. 이와 같은 혼동을 피하기 위해서 SQL에서는 두개의 연속적인 작은따옴표를 이용하여 문자열 내의 작은따옴표를 표현한다. 예를 들면 위의 문자열 상수를 정의하기 위해서는 다음과 같이 표현하면 된다.

‘This”s Jun”s book’

SQL에서 사용되는 또다른 값의 종류로는 SQL문과 호스트 프로그램 사이에 전달을 위한 매개변수(parameter)에 의해 표현되는 값, SQL이 제공하는 함수들에 의하여 생성되는 값, 그리고 기존의 값들과 연산자들로 구성된 표현식(expression)에 의해서 생성되는 값이 있는데, 이제부터 그 각각에 대하여 살펴보도록 하자.

매개변수

매개변수는 고정된 값을 갖는 상수와는 달리 필요에 따라 값을 변경할 수 있는 변수로서 일반적으로 프로그래밍 과정에서 함수를 정의할 때 실제 사용될 값을 대신할 목적으로 사용된다. 프로그램을 실행 과정에서 그 함수를 호출할 때 구체적인 값을 지정하여 매개변수의 값으로 사용되도록 한다. 그러면 이러한 매개변수가 SQL에서는 어떻게 사용될까? 현재 이 글에서 다루고 있는 대화형 SQL, 즉 ISQL에서는 모든 값을 구체적으로 명시해야 하기 때문에 매개변수를 사용할 수 없다. 그 대신 ESQL/C와 같이 응용 프로그램과 함께 사용되는 SQL문에서 필요한 값을 수시로 변경할 수 있도록 하는 목적으로 사용된다.

보다 자세한 설명은 ESQL/C를 소개할 때 다루기로 하고 여기서는 간단한 예를 통하여 살펴보자. 각 은행은 일정기간마다 한번씩 모든 고객의 예금에 이자를 가산하는데 이러한 작업을 수행하는 응용 프로그램이 다음과 같은 SQL문들을 포함하고 있다고 가정하자.

UPDATE 예금 SET 잔액 = 잔액 * 1.05 WHERE 은행명 = ‘국민은행’;

UPDATE 예금 SET 잔액 = 잔액 * 1.06 WHERE 은행명 = ‘외환은행’;

…

이 예는 모든 고객의 예금에 대하여 국민은행은 5%의 이자를 지급하고 외환은행은 6%의 이자를 지급한다는 것을 고정적으로 표현한 것이다. 하지만 요즘과 같이 금리가 불안할 때는 각 은행마다 예금의 이율을 수시로 변경하는 경우가 있을 수 있다. 이 예에서와 같이 응용 프로그램 내에서 각 은행의 예금 이율이 고정되어 있다면 향후 이율이 변경될 때마다 관련된 UPDATE문을 일일이 변경하고 다시 컴파일해야 하는 번거러움이 있다. 이러한 문제점을 해결하는 한 방법으로 다음과 같이 매개변수를 사용하면 UPDATE문의 변경 없이도 필요에 따라 값을 변경할 수 있게 된다.

UPDATE 예금 SET 잔액 = 잔액 * :raise WHERE 은행명 = :bankname;

SQL에서의 매개변수는 콜론(:) 뒤에 변수 이름을 붙임으로써 표현되는데, 이 예에서는 변경 비율을 명시하기 위한 ‘:raise’라는 매개변수와 은행 이름을 명시하기 위한 ‘:bankname’이라는 매개변수를 사용한다. 앞에서의 고정된 UPDATE문을 표현하기 위해서 :raise = 1.05, :bankname = ‘국민은행’과 같은 식으로 적당한 값을 매개변수를 통하여 넘겨주면 된다. 이와 같은 매개변수의 사용 결과로 보다 유연한 응용 프로그램 작성이 가능해진다.

SQL에서 제공하는 매개변수의 종류는 다음과 같은 세가지이다.

– 데이터 매개변수 : 응용 프로그램과 DBMS 사이의 데이터 교환을 위한 매개변수로서 앞에서 예로 보인 ‘:raise’나 ‘:bankname’과 같은 매개변수가 여기에 해당된다.

– 상태 매개변수 : SQL문의 실행에 대한 상태를 응용 프로그램에게 알리기 위한 매개변수로서 SQL문이 올바르게 실행되었는지 또는 에러가 발생했다면 어떤 종류의 에러가 발생했는지 나타내는데 사용된다. 그 종류로는 실행 상태를 숫자 코드로 표현하는 SQLCODE 매개변수와 실행 상태를 5자의 문자코드로 표현하는 SQLSTATE 매개변수가 있다.

– 지시자(indicator) 매개변수 : 데이터 매개변수가 유효한지 또는 교환된 데이터가 널값인지와 같은 정보를 응용 프로그램과 DBMS 사이에 교환할 수 있도록 하는 매개변수이다. 또다른 사용 목적으로 DBMS가 검색한 문자열 데이터가 중간에 잘렸는지 여부를 응용 프로그램에 알리는데 사용되기도 한다. 지시자 매개변수는 데이터 매개변수 뒤에 INDICATOR라는 키워드(생략 가능)와 매개변수로 표현된다.

집합 함수

SQL은 데이터베이스를 집합 단위로 다루는데 이러한 데이터 집합에 대하여 몇가지 유용한 함수들을 제공하여 사용자의 편의를 제공한다. 집합 함수로는 COUNT, MAX, MIN, SUM, 그리고 AVG가 있는데, 이러한 집합 함수를 흔히 집계(aggregate) 함수라고도 한다.

COUNT 함수

명시된 테이블의 전체 튜플 수 또는 특정 컬럼의 줄 수를 반환하는 함수로서 다음과 같은 구문을 갖는다.

—、 COUNT —、 ( –+—、 * —————————-+–、 ) —-、

+–、 ALL ——–+ |

+–、 DISTINCT –+–、 컬럼 이름 –+

+——————+

COUNT(*)는 테이블의 전체 튜플 수를 계산하며, COUNT(컬럼 이름)은 테이블 내의 특정 컬럼의 줄 수를 계산한다. 다음은 COUNT 함수가 필요한 문제와 SQL로 작성된 질의 예이다.

문제) 고객 테이블의 전체 튜플 수를 계산하라.

질의) SELECT COUNT(*) FROM 고객;

COUNT 함수를 비롯한 모든 집합 함수는 기본적으로 중복을 포함한 모든 튜플들을 대상으로 계산하므로 ALL은 생략 가능하다.

집합 함수에 대해서도 테이블 내의 특정 튜플들에 대해서만 적용하도록 조건절을 이용할 수도 있다.

문제)예금 테이블에서 잔액이 50,000원 이상인 고객 수를 계산하라.

질의) SELECT COUNT(DISTINCT 주민등록번호) FROM 예금 WHERE 잔액 >= 50000;

어떤 고객은 50,000원 이상의 예금 통장을 여러개 가질 수도 있는데 이 문제에서는 그 통장의 수를 계산하는 것이 아니라 고객의 수를 계산하기 때문에 DISTINCT가 필요하다. 만일 찾고자 하는 것이 만족하는 예금의 수일 때에는 단순히 COUNT(*)를 사용하면 된다.

MAX 함수와 MIN 함수

이 함수들은 각각 어떤 값들의 집합 중에서의 최대값과 최소값을 계산하는 것으로서 그 집합은 컬럼의 이름으로 표현되는 것이 일반적이며 그 외에도 비교 가능한 값을 생성하는 표현식으로 표현될 수도 있다. 이들 함수들의 구문은 다음과 같다.

+—–、 ALL —–+

–+-、 MAX –+–、 ( –+—————–+–、 표현식 —、 ) —–、

| | +–、 DISTINCT -+

+-、 MIN —+

문제) 국민 은행의 예금들 중에서 가장 많은 잔액을 검색하라.

질의) SELECT MAX(잔액) FROM 예금 WHERE 은행명 = ‘국민은행’;

또한 MIN 함수와 MAX 함수는 숫자형 데이터 타입뿐만 아니라 문자형이나 datetime형 등의 다른 데이터 타입에 대해서도 적용할 수 있다. 다음은 문자열들 사이의 크기 비교가 가능하다는 것을 보여주는 예이다.

문제) 모든 은행의 예금 고객들 중에서 가장 나이가 많은 사람의 주민등록번호를 검색하라.

질의) SELECT MIN(주민등록번호) FROM 예금;

여기서 예금 테이블의 주민등록번호는 문자열 데이터 타입을 갖는데 그 값이 작을수록 출생년도가 빠르므로 결국 나이가 많게 된다.

일반적으로 값의 중복 여부 검사에 많은 비용이 드는데, MAX 함수와 MIN 함수는 다른 집합 함수들과는 달리 중복의 포함 여부가 그 결과 값에는 아무런 영향을 미치지 않는다. 따라서 이 함수들의 경우에는 불필요한 DISTINCT를 사용하지 않는 것이 바람직하다.

SUM 함수

SUM 함수의 구문은 함수 이름만 다를 뿐 MAX 함수와 같은데, 함수 내에 명시한 표현식의 전체 합을 계산한다. 따라서 이 함수는 숫자형 데이터 타입에 대해서만 적용 가능하다. 그리고, SQL-92에서 명시한 또다른 제약으로는 이 함수값이 원래 대상이 되는 데이터 타입의 범위 내에 있어야 한다. 예를 들어 SMALLINT 데이터 타입을 최대값이 2047이라 가정할 때 SMALLINT 타입을 갖는 컬럼의 모든 값들의 합은 2047보다 작거나 같아야 한다는 것이다.

문제) 신림동에 위치한 모든 은행의 고객들의 예금 잔액 합은 계산하라.

질의) SELECT SUM(잔액) FROM 예금 WHERE 지점 = ‘신림동’;

AVG 함수

AVG 함수 또한 함수의 이름만 다를 뿐 MAX 함수와 똑같은 구문을 갖는데, 함수 내에 명시한 표현식의 전체 평균을 계산한다. SUM 함수와 마찬가지로 숫자형 데이터 타입에 대해서만 적용 가능하다.

문제) 모든 은행의 고객들의 예금 잔액 평균을 계산하라.

질의) SELECT AVG(잔액) FROM 예금;

값 함수들

DBMS가 유용한 함수들을 많이 제공하면 할수록 그만큼 사용자가 직접 수행해야 하는 작업의 양이 줄어들기 때문에 사용자들은 시스템이 보다 많은 함수들을 제공하기를 바랄 것이다. SQL-92에서는 앞에서 살펴본 기존의 집합 함수들 이외에도 다음과 같은 세가지 범주의 값 함수들을 제공하도록 정의한다.

– 숫자형 값 함수들

– 문자열 값 함수들

– datetime 값 함수들

숫자형 값 함수들

이 범주의 값 함수들은 실제 적용 대상이 되는 데이터 타입이 다른 타입일 수도 있지만 그 처리 결과 값은 항상 숫자형 값을 갖는다. 숫자형 함수들로는 POSITION, CHARACTER_LENGTH, OCTET_LENGTH, BIT_LENGTH, 그리고 EXTRACT가 있는데 다음과 같은 구문을 갖는다.

–+-、 POSITION —、 ( —、 문자열1 —、 IN —、 문자열2 —、 ) —————-+

+-、 EXTRACT –、 ( -+-、 datetime 필드 -+-、 FROM -+-、 datetime 값 -+-、 ) –+—、

| +-、 timezone 필드 -+ +-、 시간간격 값 -+ |

+-、 CHAR_LENGTH ———-+–、 ( —、 문자열 —、 ) ———————-+

+-、 CHARACTER_LENGTH –+

+-、 OCTET_LENGTH ——–+

+-、 BIT_LENGTH ————+

POSITION 함수

문자열1 내에 문자열2를 포함하는지 여부를 검사하여 만일 포함한다면 그 시작 위치를 반환하고 그렇지 않으면 0을 반환한다.

예) POSITION (‘외환’ IN ‘국민은행과 외환은행’) — 결과값 12 반환

POSITION (‘농협’ IN ‘국민은행과 외환은행’) — 결과값 0 반환

지난 호에서 한글은 문자 하나가 2바이트를 차지한다고 설명하였는데, 첫번째 예에서 한글 5자와 공백문자 하나가 찾으려는 문자열 앞에 있으므로 결국 그 시작 위치는 10 + 1 + 1 = 12가 되는 것이다.

CHARACTER_LENGTH 함수

대상이 되는 문자열의 문자수를 반환하는 것으로서 간단히 CHAR_LENGTH로 줄여 사용하기도 한다.

예) CHARACTER_LENGTH (‘국민은행과 외환은행’) — 결과값 19 반환

OCTET_LENGTH 함수와 BIT_LENGTH 함수

대상이 되는 문자열을 각각 8비트 단위의 옥테트(octet)수와 비트수로 계산한다. OCTET_LENGTH 함수를 일반 문자열에 대하여 적용한다면 그 문자수를 반환하며, 비트 문자열에 대하여 적용한다면 8로 나눈 몫과 만일 나머지가 있다면 1을 더한 값을 반환한다. BIT_LENGTH 함수를 일반 문자열에 대하여 적용한다면 그 문자수의 8배값을 반환하며, 비트 문자열에 대하여 적용한다면 그 비트수 만큼의 값을 반환한다.

예) OCTET_LENGTH (‘국민은행과 외환은행’) — 결과값 19 반환

OCTET_LENGTH (B’10010010001′) — 비트 문자열에 적용, 결과값 2 반환

BIT_LENGTH (B’10010010001′) — 비트 문자열에 적용, 결과값 11 반환

EXTRACT 함수

datetime이나 interval의 특정 필드를 추출하여 숫자값으로 반환하는 함수이다. 다음은 DATE 타입의 값으로부터 년도를 추출하는 예를 보여준다.

예) EXTRACT (YEAR FROM DATE ‘1998-01-01’) — 숫자 1998 반환

문자열 값 함수들

이 범주의 함수들로는 SUBSTRING, UPPER, LOWER, TRIM, TRANSLATE, 그리고 CONVERT 함수가 있는데, 다음과 같은 구문을 갖는다.

–+-、 SUBSTRING –、 ( –、 문자열 –、 FROM –、 시작 -+—————–+-、 ) -+-、

| +、 FOR –、 길이 -+ |

+-+-、 UPPER —+–、 ( —-、 문자열 —–、 ) ———————————–++

| +-、 LOWER –+ |

+—、 TRIM —、 ( –+-、 BOTH ——++-、 문자 -+、 FROM –、 문자열 –、 ) —-+

| +-、 LEADING –++———+ |

| +-、 TRAILING -+ |

+–、 TRANSLATE —、 ( —、 문자 —、 USING —、 번역 —、 ) ————–+

+–、 CONVERT —、 ( —、 문자 —、 USING —、 변환 —、 ) —————–+

SUBSTRING 함수

이 함수의 구문에서 시작값은 문자열의 몇번째 문자를 나타내는데 대상이 되는 문자열로부터 시작값번째의 문자에서 길이값 만큼의 문자열을 잘라서 반환한다. 이 때, 길이값을 명시하지 않으면 문자열의 시작점으로부터 마지막까지를 반환하고, 만일 문자열의 길이를 벗어나는 시작점을 명시한 경우에는 빈 문자열(”)을 반환한다.

예) SUBSTRING (‘This is test string’ FROM 9 FOR 4) — 결과값으로 ‘test’ 반환

SUBSTRING (‘This is test string’ FROM 20 FOR 4) — 결과값으로 ” 반환

SUBSTRING (‘This is test string’ FROM 9) — 결과값으로 ‘test string’ 반환

UPPER 함수와 LOWER 함수

영어는 한글과는 달리 대소문자의 구별이 있다. UPPER 함수와 LOWER 함수는 각각 대상이 되는 문자열을 모두 대문자와 모두 소문자로 변환한 문자열 값을 반환한다.

예) UPPER(‘Upper Case’) — 결과값으로 ‘UPPER CASE’ 반환

LOWER(‘Lower Case’) — 결과값으로 ‘lower case’ 반환

TRIM 함수

대상이 되는 문자열로부터 첫부분과 끝부분의 특정 문자를 없앤다. LEADING을 사용함으로써 첫부분을, TRAILING을 사용함으로써 끝부분을, 그리고 BOTH를 사용함으로써 첫부분과 끝부분의 특정 문자를 함께 제거할 수 있다. 제거할 특정 문자를 명시하지 않는 경우는 기본적으로 공백 문자(‘ ‘)가 지정된다.

예) TRIM (LEADING ‘ ‘ FROM ‘ TEST ‘) — 결과값으로 ‘TEST ‘ 반환

TRIM (TRAILING FROM ‘ TEST ‘) — 결과값으로 ‘ TEST’ 반환, 특정 문자는 ‘ ‘

TRIM (BOTH ‘T’ FROM ‘TEST’) — 결과값으로 ‘ES’ 반환

TRANSLATE 함수와 CONVERT 함수

TRANSLATE 함수와 CONVERT 함수는 각각 번역 부분과 변환 부분에 명시된 규칙들을 이용하여 대상이 되는 문자열을 다른 문자열로 번역하고 변환하는 작업을 수행하는 것으로서 SQL-92의 국제화(internationalization) 부분과 관련된 함수들이다.

datetime 값 함수들

이 범주의 함수들로는 오늘 날짜, 현재 시각, 또는 오늘 날짜와 현재 시각을 함께 표현하는 timestamp를 얻는데 사용되는 것으로서 차례로 CURRENT_DATE 함수, CURRENT_TIME 함수, 그리고 CURRENT_TIMESTAMP 함수이다. 뒤의 두 함수들은 시각에 포함되는 초의 소수점 이하 유효자리수를 인자로 명시할 수도 있다. 이 함수들의 구문은 다음과 같다.

+—、 CURRENT_DATE ———————————————–+

—–+—、 CURRENT_TIME –+——————————–+————+—–、

| +–、 ( —、 유효자리수 —、 ) —+ |

+—、 CURRENT_TIMESTAMP —+——————————–+—+

+–、 ( —、 유효자리수 —、 ) —+

예) CURRENT_DATE — 오늘 날짜를 결과값으로 반환한다.

— 그 값은 1998-01-01과 같이 DATE 타입으로 표현된다.

CURRENT_TIME(2) — 현재 시각을 결과값으로 반환한다. 예를 들어 현재 시각이

— 12시 정각이라면 12:00:00.00으로 표현될 것이다.

CURRENT_TIMESTAMP(1) — 오늘 날짜와 현재 시각을 함께 반환한다.

— 예를 들면, 1998-01-01:12:00:00.0

표현식 값

표현식은 이제까지 살펴본 값들과 여러 연산자들로 구성된 식으로서 함수의 경우와 마찬가지로 DBMS가 그 식을 계산하여 결과값을 반환한다. SQL-92에서는 다음과 같은 5가지 범주의 표현식을 정의한다.

숫자형 값 표현식

숫자형 값들에 대하여 +, -, *, /의 사칙연산자를 이용하여 표현한 산술식으로서 그 결과값 또한 숫자형 데이터 타입을 갖는다. 피연산자로 사용되는 숫자형 값으로는 상수뿐만 아니라 숫자형 데이터 타입을 갖는 컬럼 참조, 함수, 매개변수, 또다른 숫자형 값 표현식 등이 사용될 수 있다.

예) -10000 — 음의 부호(-)와 상수

10000 / 3 — 상수들의 나눗셈

잔액 * 1.05 — 컬럼 참조와 상수의 곱셈

잔액 * :raise — 컬럼 참조와 매개변수의 곱셈

50000 + 50000 * ( 0.05 – 0.003 ) — 또다른 숫자형 값 표현식(0.05-0.003) 포함

문자열 값 표현식

SQL-92에서는 문자열 값 표현식에서 사용될 수 있는 연산자로 피연산자로 지정한 두 문자열 값을 하나로 연결시키는 접합(concatenation) 연산자(||) 하나만을 정의한다. 피연산자로 사용되는 문자열 값으로는 문자열 상수뿐만 아니라 문자형 데이터 타입을 갖는 컬럼 참조, 함수, 매개변수, 또다른 문자열 값 표현식 등이 사용될 수 있다.

예) ‘국민은행 ‘ || ‘신림동’ — ‘국민은행 신림동’ 값 생성

은행명 || ‘ ‘ || 지점 — 컬럼 참조를 이용한 문자열 값 표현식으로서

— 은행명과 지점명을 포함하는 하나의 문자열 생성

datetime 값 표현식

이 표현식은 DATE, TIME, 그리고 TIMESTAMP 타입과 같이 datetime에 관련된 데이터의 값을 명시하거나 그 값에 특정 시간 간격을 더하거나 빼서 그 결과로 또다른 datetime 값을 얻는다.

예) CURRENT_DATE + INTERVAL ‘1’ DAY — 결과값으로 내일 날짜를 반환

TIME ’10:45:00′ AT LOCAL — 지역 시간으로 10시 45분임을 명시

TIME ’10:45:00′ AT TIME ZONE INTERVAL ‘+09:00’ HOUR TO MINUTE

— 그리니치 표준시로 정확하게 +9시간 차이나는 지역(한국)의 10시 45분임을 명시

간격 값 표현식

어떤 datetime 값에서 다른 datetime 값을 빼면 (시간) 간격 값을 얻을 수 있는데, 간격 값 표현식은 이러한 시간 간격 값을 명시하거나 그 간격 값들 사이의 합이나 차이를 표현하여 그 결과로 datetime 값을 얻는다. 또한 시간 간격 값에 숫자 상수를 곱하거나 나누어 새로운 시간 간격 값을 얻을 수도 있다.

예) CURRENT_DATE – 예금날짜

— 예금날짜가 DATE 타입을 가질 때, 며칠 전에 예금했는지 그 기간을 얻음

INTERVAL ‘6’ DAY – INTERVAL ‘1’ DAY — 5일이라는 시간 간격을 얻음

INTERVAL ‘7’ DAY * 4 — 28일이라는 시간 간격을 얻음

조건 표현식

일반적으로 SQL과 같은 데이터 부속어에는 프로그래밍을 위한 기능들이 많이 부족하기 때문에 실제로 응용 프로그램을 개발할 때에는 많은 부분을 고급 프로그래밍 언어를 사용해야만 한다. SQL-92에서는 이와 같이 과다한 응용 프로그래머들의 부담을 조금이나마 줄이기 위해서 SQL 차원에서 몇가지 기능들을 추가하였는데, 그 대표적인 것으로 조건 표현식을 들 수 있다. 조건 표현식은 어떤 조건에 따라 서로 다른 표현식이 필요할 때 모든 경우를 하나의 SQL문으로 표현할 수 있도록 하는데, 다음 예에서와 같이 ‘CASE WHEN 검색조건1 THEN 결과1 … ELSE 결과n END’ 구문을 갖는다.

예) UPDATE 예금 SET 잔액 = CASE

WHEN 은행명 = ‘국민은행’

THEN 잔액 * 1.05

WHEN 은행명 = ‘외환은행’

THEN 잔액 * 1.06

ELSE 잔액 * 1

END

술어

술어란 값에 대한 사실을 판단할 수 있는 일종의 표현식으로서 그 사실이 옳으면 참(true), 틀리면 거짓(false)이 되는데 만일 DBMS가 사실의 옳고 그름을 결정할 수 없을 때에는 알 수 없슴을 나타내는 널값을 갖는다. 이러한 술어를 사용하여 SELECT문에서의 검색 대상, DELETE문에서의 삭제 대상, 그리고 UPDATE문에서의 변경 대상 등의 목적으로 테이블 내의 특정 데이터를 선택할 수 있다. 이제부터 SQL이 제공하는 다양한 술어들에 대하여 하나씩 살펴보도록 하자.

비교 술어

먼저 가장 많이 사용되는 비교 술어는 이미 지난호에서 설명한 것과 같이 ‘=, <>, <, >, <=, >=’와 같은 6개 비교 연산자를 이용하여 두 개의 피연산자 값들을 비교한다. 각 연산자의 의미는 대부분의 프로그래밍 언어에서 사용되는 것과 똑같으므로 여기서는 더 이상의 설명은 생략하기로 하자.

BETWEEN 술어

BETWEEN 술어는 주어진 값이 특정 범위 내에 존재하는지를 검사하는 것으로서 다음과 같은 구문을 갖는다.

값1 BETWEEN 값2 AND 값3

그 의미는 값1이 값2와 값3의 범위 내에 속하면 참이고 그렇지 않으면 거짓이 된다. 다시 말해서 값1이 값2 이상이면서 값3 이하인지를 비교하는 것과 같은 의미를 갖는다. 따라서 이 술어는 다음과 같은 비교 술어로 표현할 수 있다.

값1 >= 값2 AND 값1 <= 값3 이 때 BETWEEN 술어에 사용되는 값2와 값3은 순서대로 해석된다는 사실에 유의해야 한다. 다음 예에서와 같이 두 값의 순서에 따라 그 결과가 달라질 수가 있다. 5 BETWEEN 1 AND 10 — 5 >= 1 AND 5 <= 10, 결과는 참 5 BETWEEN 10 AND 1 — 5 >= 10 AND 5 <= 1, 결과는 거짓
문제) 예금 잔액이 30,000원 이상, 100,000원 이하인 고객의 주민등록번호를 검색하라.

질의) SELECT 주민등록번호 FROM 예금 WHERE 잔액 BETWEEN 30000 AND 100000;

BETWEEN 술어 앞에 NOT을 추가하여 그 이름에서 알 수 있듯이 BETWEEN 술어의 반대 의미로 사용할 수도 있다. 즉, 값1이 값2와 값3의 범위 내에 속하면 거짓이고 그렇지 않을 때 참이 되는 것이다. 다시 말해서 값1이 값2 미만 또는 값3 초과인지 비교하는 것과 같은 의미를 가지므로 NOT BETWEEN 술어는 다음과 같은 비교 술어로 표현할 수 있다.

값1 < 값2 OR 값1 > 값3

NULL 술어

NULL 술어는 특정 컬럼이 널값을 갖는지를 검사하는 것으로서 다음과 같은 구문을 갖는다.

컬럼명 IS NULL

이 술어에서는 만일 명시된 컬럼이 널값을 가지면 참이고 그렇지 않으면 거짓이 된다.

문제) 주소가 기재되어 있지 않은 고객들의 이름을 검색하라.

질의) SELECT 고객명 FROM 고객 WHERE 주소 IS NULL;

다음 구문과 같이 NULL 술어에 NOT 연산자를 사용함으로써 명시된 컬럼이 널값이 아닐 때 참이 되도록 할 수도 있다.

컬럼명 IS NOT NULL

IN 술어

IN 술어는 어떤 값이 특정 집합 내의 원소인지 검사하는 술어로서 다음과 같은 두가지 종류의 구문으로 표현될 수 있다.

값t IN (값1, 값2, …, 값n) 또는 값t IN 부질의

첫번째 형태의 구문에서는 값t가 값1, 값2, …, 값n 중의 하나일 때 참이 된다.

문제) 거래 은행이 국민은행, 외환은행, 농협의 모든 예금 고객의 주민등록번호를 검색하라.

질의) SELECT DISTINCT 주민등록번호 FROM 예금

WHERE 은행명 IN (‘국민은행’, ‘외환은행’, ‘농협’);

두번째 형태의 구문에서 부질의(subquery)라는 것은 다른 SQL문 내에 포함될 수 있는 SELECT문을 의미하는데, 부질의의 처리 결과로 얻어진 데이터를 다른 SQL문에서 사용할 수 있도록 함으로써 결국 여러 테이블들로부터 얻어진 정보를 서로 연관시킬 수 있게 된다. 부질의를 포함하는 IN 구문에서는 값t가 부질의의 처리 결과로 얻어진 데이터에 속할 때 참이 된다.

문제) 국민은행이 있는 지역에 지점을 갖는 은행들을 그 지역과 함께 검색하라.

질의) SELECT 은행명, 지점 FROM 은행

WHERE 은행명 <> ‘국민은행’ AND 지점 IN

( SELECT 지점 FROM 은행

WHERE 은행명 = ‘국민은행’ );

위의 질의에서는 전체 질의의 결과가 국민은행의 지점들을 포함할 필요가 없으므로 첫번째 비교 술어가 사용되었으며 다음으로 국민은행의 모든 지점을 검색하기 위해서 부질의를 포함한 IN 술어를 사용하여 현재 튜플의 지점 컬럼값이 국민은행이 있는 지역인지 검사한다.

부질의는 여기서와 같이 SELECT문 뿐만 아니라 UPDATE문, DELETE문, 그리고 INSERT문의 WHERE절에서도 사용될 수 있다. 특히 INSERT문에서는 부질의의 결과값을 바로 새로운 튜플값으로 테이블에 삽입할 수 있다. 예를 들어 농협에서 모든 예금 통장에 대하여 새로운 대출 통장을 하나씩 만들어 준다고 할 때 다음과 같이 부질의를 이용한 INSERT문을 사용할 수 있다.

INSERT INTO 대출 (계좌, 대출액, 주민등록번호, 은행명, 지점)

SELECT 계좌, 0, 주민등록번호, ‘농협’, 지점 FROM 예금

WHERE 은행명 = ‘농협’;

위의 질의에서는 생성된 대출 계좌에 대하여 잔액을 0원으로 명시하였고, 계좌번호는 일단 기존의 예금 통장과 같도록 지정하였는데 추후에 다른 계좌번호를 부여하는 작업이 당연히 필요할 것이다.

IN 술어에 대해서도 NOT을 앞에 붙여 반대 의미로 사용할 수 있다.

LIKE 술어

이 술어는 문자열 데이터가 특정 패턴을 가지는지 검사하는 목적으로 사용되는데, 임의의 문자열을 의미하는 ‘%’와 임의의 한 문자를 의미하는 ‘_’ 문자를 이용하여 패턴을 명시한다.

예) ‘서울%’ — 서울로 시작하는 모든 문자열. 예를 들어 서울 주소

‘%관악구%’ — 관악구를 포함하는 모든 문자열. 예를 들어 관악구 주소

‘_유미’ — 유미로 끝나는 모든 문자열. 예를 들어 성에 관계없이 ‘유미’라는 이름

‘__12%’ — 두세번째 문자가 ’19’인 문자열.

— 예를 들어 12월에 태어난 사람의 주민등록번호

문제) 강남구에 살고 있는 모든 고객의 이름을 검색하라.

질의) SELECT 고객명 FROM 고객 WHERE 주소 LIKE ‘%강남구%’;

만약 예를 들어 ‘100%’, ‘Underscore is _’에서와 같이 여러분들이 ‘%’나 ‘_’ 문자를 포함하는 문자열을 찾으려 할 때는 그 패턴을 어떻게 표현할 수 있을까? 일반적으로 패턴을 표현하는데 사용되는 특수 문자 자체를 그 패턴 내에 표현하려할 때는 또다른 특수 문자를 사용하여 그 문자 바로 뒤에 나타나는 특수 문자를 보통 문자로 해석하도록 한다. 이와 같이 기존의 특수 문자를 일반 문자로 취급하도록 하는 특수 문자를 escape 문자라 하는데, SQL에서는 고정된 escape 문자를 제공하는 것이 아니라 사용자가 escape 문자를 지정할 수 있도록 하여 필요에 따라 변경할 수 있다. SQL에서의 escape 문자는 ESCAPE라는 키워드 뒤의 문자로서 표현된다. 예를 들어 ‘\’를 escape 문자로 사용하려 할 때 ‘100%’라는 문자열을 포함하는 패턴을 찾기 위한 LIKE 술어는 다음과 같이 표현된다.

컬럼명 LIKE ‘%100\%%’ ESCAPE ‘\’

EXISTS 술어

EXISTS 술어는 ‘EXISTS 부질의’와 같이 부질의와 함께 사용되는 구문을 갖는데, 부질의의 결과가 하나라도 존재하면 참이고 그렇지 않은 경우에는 거짓이 된다. EXISTS 술어에 대해서도 NOT을 앞에 붙여 반대 의미로 사용할 수 있다.

문제) 전체 고객들 중에서 예금 통장을 전혀 갖고 있지 않은 사람을 검색하라.

질문) SELECT 고객명 FROM 고객

WHERE NOT EXISTS ( SELECT * FROM 예금

WHERE 예금.주민등록번호 = 고객.주민등록번호 );

위의 부질의에서는 이제까지 살펴본 SQL문들과는 달리 ‘예금.주민등록번호 = 고객.주민등록번호’라는 두 테이블 사이의 컬럼 비교가 사용되고 있다. 지난 연재에서 E-R 다이어그램으로부터 테이블 구조를 결정할 때 관계를 위한 테이블을 따로 생성하는 방법을 사용하였는데, 이 때 관계 테이블에는 관계 속성들 외에도 관련된 개체 테이블에 포함되는 기본키 컬럼들을 함께 포함하였다. 이와 같이 관계형 데이터 모델에서는 같은 속성을 나타내는 컬럼을 두 테이블에 포함하도록 한 후에 그 값들을 비교함으로써 그들 간의 관계를 처리하게 된다. 여기서 두 컬럼 사이의 값 비교를 죠인(join)이라 하는데 자세한 설명은 다음호에서 다루기로 하고, 여기서는 위 SQL문의 의미에 대해서만 간단히 설명하고 넘어가자.

고객 테이블에서 한명의 고객을 의미하는 각 튜플에 대하여 부질의는 그 고객의 주민등록번호와 같은 주민등록번호를 갖는 예금주를 검색하는데, 만일 그런 예금주가 있으면 거짓이 되고 그렇지 않으면 참이 되어 결과적으로 전체 고객들 중에서 예금 통장을 갖고 있지 않은 사람들만을 검색하게 되는 것이다.

UNIQUE 술어

‘UNIQUE 부질의’의 구문을 갖는 술어로서 그 의미는 부질의의 결과에 중복된 튜플들이 존재하는지를 검사하여 만일 서로 똑같은 튜플들이 한쌍이라도 존재한다면 거짓이 되며, 그렇지 않고 모든 튜플들이 서로 다르다면 참이 된다.

문제) 하나의 예금 통장만을 갖고 있는 고객의 주민등록번호를 검색하라.

질의) SELECT S.주민등록번호 FROM 예금 S

WHERE UNIQUE ( SELECT T.주민등록번호 FROM 예금 T

WHERE T.주민등록번호 = S.주민등록번호 );

이 질의는 예금 테이블의 각 튜플에 대하여 같은 주민등록번호를 갖는 예금 테이블의 튜플들을 검색하는 부질의를 적용하여 주민등록번호를 검색하도록 하는 것인데, 부질의의 결과로 같은 주민등록번호를 갖는 튜플들을 그 사람이 가지고 있는 예금 통장 개수만큼 생성할 것이다. 이 예에서는 같은 테이블의 튜플들 간의 값의 비교 방법을 보여주고 있는데, 앞에서 살펴본 EXISTS 술어 예에서 사용했던 테이블 이름과 컬럼 이름을 명시하는 방법으로는 서로 구별할 수가 없다. 따라서, 이 예의 S와 T처럼 SQL에서는 FROM절에 테이블을 명시할 때 그 뒤에 새로운 변수를 하나 선언하고 그것을 이용하여 그 테이블의 튜플들을 접근하도록 하는 방법을 제공하는데 이와 같은 변수를 튜플 변수라 한다. 튜플 변수가 사용되는 또다른 경우로 테이블의 이름이 아주 길고 그것이 자주 사용되어야 한다면 그 테이블 이름 대신에 간단한 튜플 변수를 지정하여 사용하는 편이 바람직할 것이다.

OVERLAPS 술어

‘간격 정보 OVERLAPS 간격 정보’와 같은 구문을 갖는 OVERLAPS 술어는 두개의 시간 간격이 서로 겹치는지 검사하는데 사용되는 것으로 조금이라도 겹치게 되면 참이 된다. 비교하려는 한 시간 간격의 끝나는 시각과 다른 시간 간격이 시작되는 시각이 같을 수도 있는데, 이 때에는 서로 겹치는 것이 아니므로 거짓이 된다. 간격 정보는 ‘(시작 시각, 지속 시간)’ 또는 ‘(시작 시각, 종료 시각)’으로 표현되는데, 양쪽이 같은 형태일 필요는 없다. 또한 어떤 특정 시각이 다른 시간 간격에 포함되는지를 검사할 수도 있는데, 그것은 간격 정보에 시작 시각만을 명시하고 지속 시간 또는 종료 시각을 NULL로 지정하면 된다.

이 술어를 유용하게 사용할 수 있는 예를 들면 A라는 사람이 B라는 사람과 오늘 오후 2시부터 3시까지 만나기를 원할 때 OVERLAPS 술어를 이용하여 B의 스케줄이 잡혀 있는 모든 시간과 겹치는지 비교하여 모두 거짓임을 확인한다면 약속을 정할 수 있을 것이다.

SOME, ANY, 그리고 ALL 술어

이 술어들은 다음 구문에서와 같이 비교 연산자, 부질의와 함께 사용되어 명시한 튜플값과 부질의의 결과값들의 비교 결과를 검사하는 것으로서 이러한 타입의 술어들을 정량적(quantified) 비교 술어라 한다.

+–、 = –+

+–、 < --+ +--、 ALL --+ —、 튜플값 –+–、 > –+—+ +—、 부질의

+–、 <= --+ +--、 SOME --+ +–、 >= –+ +–、 ANY –+

+–、 <> –+

먼저 SOME과 ANY는 존재 정량자(existential quantifier)라 하는데 비교 결과가 참인 것이 하나라도 존재하면 참이 된다. SOME과 ANY의 처리 결과는 동일한데 SQL문 내에서 문맥상 보다 자연스러운 것을 사용하는 것이 일반적인 선택이다. 다음으로 ALL 술어는 전체 정량자(universal quantifier)라 하는데 모든 비교 결과가 참일 때 비로소 참이 된다.

이 술어들에서 튜플값과 부질의의 결과를 비교하기 위해서는 서로 같은 컬럼수를 가져야하고 다시 대응되는 각 컬럼값은 서로 비교 가능한 타입을 가져야 하는데, 대부분의 경우 이들은 하나의 컬럼으로 구성되는 것이 일반적이다. 이 때, 만일 그 컬럼값이 널이라면 이 술어의 결과는 알 수 없슴으로 처리된다. 또한 이 술어들에 대해서는 NOT을 함께 사용할 수 없는데 그 이유는 굳이 NOT을 사용하지 않더라도 정반대의 비교 연산자를 사용함으로써 같은 의미를 표현할 수 있기 때문이다. 예를 들어 ‘= NOT SOME’ 대신 ‘<> SOME’과 같이 표현하는 것이다.

문제) 국민은행의 모든 고객의 예금 잔액보다 높은 예금을 한 고객들의 주민등록번호를 검색하라.

질의) SELECT 주민등록번호 FROM 예금

WHERE 잔액 > ALL ( SELECT 잔액 FROM 예금

WHERE 은행명 = ‘국민은행’ );

이 예에서의 부질의는 국민은행의 모든 예금 잔액을 검색하는 것으로 결국 ALL 술어에 의해 부질의의 모든 결과값들보다 큰 예금을 갖는 고객 튜플을 찾게 된다. 이 문제는 다음과 같은 또다른 SQL문으로도 표현가능하다.

질의) SELECT 주민등록번호 FROM 예금

WHERE 잔액 > ( SELECT MAX(잔액) FROM 예금

WHERE 은행명 = ‘국민은행’ );

MATCH 술어

SQL-92에서는 다음호에서 다룰 참조 무결성 제약조건에 관련된 다음과 같은 구문의 MATCH 술어를 제공하는데 특정 튜플값이 부질의의 결과 집합 내에서 명시한 제약조건을 만족하는지 검사한다.

—、 튜플값 —、 MATCH –+————-+-+—————-+-、 부질의 —、

+-、 UNIQUE -+ +-、 FULL ——+

+-、 PARTIAL –+

UNIQUE은 부질의로 생성된 결과 테이블의 튜플들 중에서 명시한 튜플값과 같은 튜플이 하나만 존재해야 한다는 제약조건을 의미하고, FULL은 명시한 튜플값에 부분적인 널값을 허용하지 않는다는 점에서 PARTIAL과 다르다.

MATCH 술어는 일반적으로 새로운 튜플을 기존의 테이블에 추가할 때 그 테이블에 이미 같은 값을 갖는 튜플이 존재하는지를 미리 검사하는데 사용된다.

검색 조건

SQL에서는 이제까지 소개한 다양한 술어들을 AND, OR, 그리고 NOT 논리 연산자들 이용하여 복합적으로 사용할 수 있으며, 아울러 전체 결과값이 참, 거짓 또는 알 수 없슴인지까지 검사할 수 있도록 다음과 같은 구문의 검색 조건을 제공한다.

+——————————– OR 。———————————+

| +—————————— AND 。—————————–+ |

–〃-〄-〄+———-++-、 술어 ———++———————————–+-+—+-+-、

| +-、 NOT -++-、 ( –+ +-、 ) –++–、 IS –+———–++–、 TRUE ——-+ |

+————————+ | +-、 NOT –++–、 FALSE ——+ |

| +–、 UNKNOWN -+ |

+—————————————————-+

각 술어는 처리 결과로 참, 거짓 또는 널값을 가질 수 있는데, 그것들 간의 AND, OR, NOT 논리 연산에 따라 전체 검색 조건의 결과가 결정된다. 여기서 각 값들의 조합에 대한 논리 연산자 적용 결과는 첫번째 연재에서 이미 소개한 진리표를 따른다.

세번째 연재를 마치며

이번호에서는 SQL이 제공하는 다양한 종류의 값들과 술어들에 대하여 살펴보았다. 본 연재는 SQL-92를 기본으로 설명하기 때문에 여러분들이 사용하고 있는 DBMS가 본문에서 소개한 모든 기능들을 제대로 지원하지 않을 수도 있다. 실제로 필자가 사용하는 PC Oracle의 SQL Plus3.3에서도 UNIQUE 술어나 조건 표현식 등을 제공하지 않고 있었다. 이번호를 마친 여러분들은 SQL을 사용하여 어느 정도 원하는 작업을 표현할 수 있을 것이다. 하지만 이제까지 살펴본 대부분의 예에서는 SQL의 적용범위를 하나의 테이블로 제한하였기 때문에 여러분들이 둘 이상의 테이블들을 동시에 접근해야 하는 대부분의 실제 데이터베이스에서 사용하기에는 답답함을 느꼈을 것이다. 다음호에서는 ISQL에 관한 마지막 연재로서 SQL의 가장 중요한 연산이라 할 수 있는 둘 이상의 테이블들을 대상으로 작업하는데 필수적인 죠인 연산에 대하여 소개하고, 뷰와 다양한 제약 조건 등 이제까지 설명을 미뤄왔던 ISQL의 나머지 기능들을 다룰 것이다.

참고 문헌 :

– J. Melton and A.R. Simon, “Understanding The New SQL : A Complete Guide”, Morgan Kaufmann, 1993.

– H.F. Korth and A. Silberschartz, “Database System Concepts”, McGraw Hill, 1991.

– 이석호, “데이터베이스 시스템”, 정익사, 1995.

필자 연락처 : yjsong@oopsla.snu.ac.kr