데이터베이스 노하우/팁/자료실
데이터 조회
연산자와 함수
데이터를 가공할 때 쓰이는 연산자와 함수는 SELECT절이나 WHERE절에 사용할 수 있다.
문자형 상수, 연산자, 함수
예제 11
√ 문제
CODE 테이블을 이용하여 ‘Hello, world!’를 대문자와 소문자로 표현하라.
√ SQL문
SELECT UPPER(‘Hello, world!’) AS “UPPER”
LOWER(‘Hello, world!’) AS “LOWER”
FROM CODE
√ 결과
NO
UPPER
LOWER
1
HELLO,WORLD!
hello,world!
2
HELLO,WORLD!
hello,world!
3
HELLO,WORLD!
hello,world!
4
HELLO,WORLD!
hello,world!
5
HELLO,WORLD!
hello,world!
6
HELLO,WORLD!
hello,world!
√ 설명
UPPER/LOWER 함수는 모든 소문자 ASCII 또는 ISO 8859-1 라틴 문자를 대문자/소문자로 변환한 형태의 문자열을 반환한다.
예제 12
√ 문제
DB_ROOT 테이블을 이용하여 ‘Hello, world!’를 대문자와 소문자로 표현하라.
√ SQL문
SELECT UPPER(‘Hello, world!’) AS “UPPER”
LOWER(‘Hello, world!’) AS “LOWER”
FROM DB_ROOT
√ 결과
NO
UPPER
LOWER
1
HELLO,WORLD!
hello,world!
√ 설명
DB_ROOT 테이블은 단 하나의 레코드만을 가지고 있는 테이블로서, SELECT문에는 반드시 FROM절이 존재해야 하므로 본 예제와 같이 간단한 연산이나 함수를 적용할 때 유용하게 사용할 수 있다.
예제 13
√ 문제
SUBSTR을 사용하여 문자열 ‘CUBRID’의 부분 문자열을 조회하되, 3번째 바이트부터 4바이트, 3번째 바이트부터 10바이트, 뒤에서 세 번째 바이트부터 3바이트를 각각 조회하라.
√ SQL문
SELECT SUBSTR('CUBRID', 3, 4),
SUBSTR('CUBRID', 3, 10),
SUBSTR('CUBRID', -3, 3)
FROM DB_ROOT
√ 결과
NO
SUBSTR('CUBRID',3,4)
SUBSTR('CUBRID',-3,3)
SUBSTR('CUBRID',
1,-1)
1
BRID
RID
NULL
null
√ 설명
SUBSTR 함수는 문자열의 일부를 반환하는 함수로써 SUBSTR(컬럼, 시작 바이트, 바이트 크기)의 형식을 지닌다. 시작 바이트는 1부터 시작되며, 두 번째 컬럼처럼 문자열의 전체 길이가 10바이트 미만인데도 10바이트를 요청하면 컬럼이 가지고 있는 크기까지만 반환한다. 또한 시작 바이트가 음수 값을 가지면 뒤에서부터 시작 바이트를 찾아내며 바이트 크기가 0이면 빈 문자열을 반환한다. 마지막으로 바이트 크기가 음수이면 NULL 값을 반환한다.
예제 14
√ 문제
다음은 문자열 ‘United Arab Emirates’ 중 문자열 ‘Arab’ 의 위치를 반환하는 쿼리 문을 작성하라.
√ SQL문
SELECT INSTR('United Arab Emirates', 'Arab', 1) AS INSTR_1,
INSTR('United Arab Emirates', 'Arab',8) AS INSTR_2,
INSTR('United Arab Emirates', 'Arab',9) AS INSTR_3
FROM DB_ROOT
√ 결과
NO
INSTR_1
INSTR_2
INSTR_3
1
8
8
0
√ 설명
INSTR 함수는 POSITION 함수와 유사하게 한 스트링 내에서 다른 스트링의 위치를 반환하는 함수이나, 탐색을 시작할 위치를 지정할 수 있다. INSTR_3의 경우 시작할 위치를 해당 문자열의 위치보다 높게 설정하였기 때문에 0으로 출력된다.
숫자형 상수, 연산자, 함수
예제 15
√ 문제
15 나누기 2, 15 나누기 2.0 값을 조회하라.
√ SQL문
SELECT 15/2, 15/2.0
FROM DB_ROOT
√ 결과
NO
15/2
15/2.0
1
7
7.500000000
√ 설명
소수점이 없는 숫자 상수는 INTEGER 타입으로 간주한다. 2.0과 같이 소수점을 사용하면 NUMERIC 타입으로 간주한다.
예제 16
√ 문제
123.456을 소수점 첫째 자리에서 반올림한 값, 둘째 자리에서 반올림한 값, 십의 자리에서 반올림한 값을 각각 구하라.
√ SQL문
SELECT ROUND(123.456, 0), ROUND(123.456, 1), ROUND(123.456, -1)
FROM DB_ROOT
√ 결과
NO
ROUND(123.456,0)
ROUND(123.456,1)
ROUND(123.456,-1)
1
123.000
123.500
120.000
√ 설명
결과 테이블의 첫 번째 컬럼과 두 번째 컬럼은 모두 소수점 첫째 자리에서 반올림한 값을 의미한다. 내림한 값을 구하려면 TRUNC 함수를 사용한다.
예제 17
√ 문제
5를 3으로 나눈 나머지, 2의 3승, 임의의 정수를 각각 구하라.
√ SQL문
SELECT MOD(5, 3), POWER(2, 3), RAND(), RANDOM()
FROM DB_ROOT
√ 결과
NO
MOD(5,3)
POWER(2,3)
RAND()
RANDOM()
1
2
8
19169
24432
√ 설명
RANDOM/RAND 함수는 구간 [ 0, 231]에서 균일 분포(uniform distribution) 임의 값을 정수로 반환한다. RAND 함수는 출력하는 행의 개수와 상관없이 한 개의 구문에서는 한 개의 값을 생성하고, RANDOM 함수는 출력하는 행마다 다른 값을 생성한다.
날짜형 연산자, 함수
예제 18
√ 문제
시스템의 현재 날짜, 현재 시간, 현재 날짜와 시간을 구하라.
√ SQL문
SELECT SYSDATE, SYSTIME, SYSTIMESTAMP, SYSDATETIME
FROM DB_ROOT
혹은
SELECT SYS_DATE, SYS_TIME, SYS_TIMESTAMP, SYSDATETIME
FROM DB_ROOT
√ 결과
NO
SYSDATE
SYSTIME
SYSTIMESTAMP
SYSDATETIME
1
2009-10-28
10:57:58
2009-10-28_10:57:58
2009-10-28_10:57:58
√ 설명
TIMESTAMP는 허용되는 값의 범위가 GMT로 1970년 1월 1일 0시 0분 0초부터 2038년 1월 19일 03시 14분 07초로 다소 제한적이지만 DATETIME은 0001년 01월 01일 0시 0분 0초부터 9999년 12월 31일 23시 59분 59초까지로 제한 범위가 매우 크다.
예제 19
√ 문제
시스템의 현재 날짜와 현재 날짜에서 180일 후의 날짜를 구하라.
√ SQL문
SELECT SYSDATE, SYSDATE + 180
FROM DB_ROOT
√ 결과
NO
SYSDATE
SYSDATE+180
1
2009-10-28
2010-04-26
예제 20
√ 문제
현재 날짜에서 1년이 지난 날짜를 조회하라
√ SQL문
SELECT ADD_MONTHS(SYSDATE, 12)
FROM DB_ROOT
√ 결과
NO
ADD_MONTHS(SYS_DATE,12)
1
2010-10-28
√ 설명
ADD_MONTHS 함수는 주어진 날짜에 개월을 더한 날짜를 반환한다. 계산된 날짜가 해당 월의 마지막 날보다 크면 마지막 날이 반환된다. 그 외에는 같은 날이 반환된다.
예제 21
√ 문제
OLYMPIC 테이블에서 1988년 이후에 개최한 국가와 개최 날짜를 조회하라.
√ SQL문
SELECT HOST_NATION, OPENING_DATE
FROM OLYMPIC
WHERE EXTRACT(YEAR FROM OPENING_DATE) >= 1988
√ 결과
NO
HOST_NATION
OPENING_DATE
1
Greece
2004-08-13
2
Australia
2000-09-15
3
USA
1996-07-19
4
Spain
1992-07-25
5
Korea
1988-09-17
√ 설명
EXTRACT 함수는 DATE나 DATETIME, TIMSTAMP 형의 데이터에서 연(YEAR), 월(MONTH), 일(DAY), 시(HOUR), 분(MINUTE), 초(SECOND))의 값을 꺼내어 사용 한다.
데이터 타입 변환 함수
예제 22
√ 문제
STADIUM 테이블의 AREA 컬럼의 값을 정수로 형 변환(소수 -> 정수)하여 조회하라.
√ SQL문
SELECT NATION_CODE, CAST(AREA AS INT)
FROM STADIUM
√ 결과
NO
NATION_CODE
CAST(AREA_AS_INT)
1
GRE
7024
2
GRE
21000
3
GRE
12800
4
GRE
27000
5
GRE
23000
6
GRE
86300
7
GRE
20000
8
GRE
54700
9
GRE
31000
10
GRE
9000
√ 설명
실제로 출력해보면 소수 자리까지 출력이 되겠지만 INT형으로 변환하여 출력하였기 때문에 소수점은 생략됐다.
예제 23
√ 문제
√ SQL문
SELECT CHAR_LENGTH(CAST(‘큐브리드’ AS CHAR(10))) AS CAST_CHAR,
CHAR_LENGTH(CAST(‘큐브리드’ AS VARCHAR(10))) AS CAST_VARCHAR
FROM DB_ROOT
√ 결과 1
√ 결과 2
NO
CAST_CHAR
CAST_VARCHAR
1
10
8
√ 설명
한글 1글자는 2바이트를 사용하므로 위의 예에서 8은 ‘큐브리드’를 저장하기 위해 사용하는 실제 바이트 수이다.
예제 24
√ 문제
OLYMPIC 테이블의 OPENING_DATE(개최일)을 다양한 유형의 날짜로 조회하라.
√ SQL문 1
SELECT TO_DATE('2007-08-09', 'YYYY-MM-DD') AS "NORMAL",
TO_DATE('AUG/09/2007', 'MON/DD/YYYY', 'En_US') AS "ENG",
TO_DATE('2007-8월-9', 'YYYY-MONTH-DD', 'Ko_KR') AS "KOR"
FROM DB_ROOT
√ SQL문 2
SELECT TO_DATETIME('10:15:20 2009/06/15', 'HH:MI:SS YYYY/MM/DD') AS NORMAL
FROM DB_ROOT
√ 결과 1
NO
NORMAL
ENG
KOR
1
2007-08-09
2007-08-09
2007-08-09
√ 결과 2
NO
NORMAL
1
2009-06-15
√ 설명
TO_DATE 함수는 문자형 데이터로부터 날짜 형 데이터로 데이터 타입을 변환하는 데 사용된다. 응용 프로그램에서 입력 받은 문자열 데이터를 큐브리드의 날짜형 컬럼에 입력할 때 유용하다. (TO_DATETIME, TO_TIMESTAMP 함수와 사용 방법 동일)
예제 25
√ 문제
OLYMPIC 테이블의 OPENING_DATE(개최일)을 다양한 유형의 날짜로 조회하라.
√ SQL문
SELECT OPENING_DATE,
TO_CHAR(OPENING_DATE, 'YYYYMMDD') AS "YYYYMMDD",
TO_CHAR(OPENING_DATE, 'YYYY-MON') AS "YYYY-MON",
TO_CHAR(OPENING_DATE, 'MM"월" DD"일" "("DY")"', 'Ko_KR') AS "MM월 DD일 (DY)"
FROM DB_ROOT
√ 결과
NO
OPENING_DATE
YYYYMMDD
YYYY-MON
MM월DD일(DY)
1
2004-08-13
20040813
2004-AUG
08월13일(금)
2
2000-09-15
20000915
2000-SEP
09월15일(금)
3
1996-07-19
19960719
1996-JUL
07월19일(금)
4
1992-07-25
19920725
1992-JUL
07월25일(토)
5
1988-09-17
19880917
1988-SEP
09월17일(토)
√ 설명
TO_CHAR 함수는 TO_DATE 함수와 반대로 날짜 데이터 타입을 문자열로 바꾸는 기능을 제공한다. 기본적으로 날짜 및 시간에 붙여 쓰는 구분자 { -, /, ‘, ., ;, : } 외의 기호를 구분자로 사용하려면 네 번째 컬럼과 같이 이중따옴표(“)로 감싸주어야 한다. TO_CHAR 함수는 DATE형 뿐만 아니라, TIME, TIMESTAMP 타입에도 적용할 수 있다.
예제 26
√ 문제
HISTORY 테이블의 VARCHAR 타입의 SCORE 컬럼을 숫자 형으로 바꾸어 출력하라.
√ SQL문
SELECT TO_NUMBER(SCORE)
FROM HISTORY
WHERE UNIT='score';
√ 결과
NO
TO_NUMBER(SCORE)
1
8893
2
124
3
125
4
144
5
596
기타 연산자, 함수
예제 26
√ 문제
OLYMPIC 테이블에서 MASCOT가 NULL인 컬럼의 내용을 ‘None’으로 바꾸어 HOST_YEAR, HOST_NATION 컬럼과 같이 출력하라.
√ SQL문
SELECT HOST_YEAR, HOST_NATION, NVL(MASCOT, 'None')
FROM OLYMPIC
√ 결과
NO
HOST_YEAR
HOST_NATION
MASCOT
5
1988
Korea
HODORI
6
1984
USA
Sam
7
1980
USSR
Misha
8
1976
Canada
Amik
9
1972
Germany
Waldi
10
1968
Mexico
None
11
1964
Japan
None
12
1960
Italy
None
13
1956
Australia
None
14
1952
Finland
None
√ 설명
NVL 함수는 첫 번째 매개변수가 NULL 값이면 두 번째 매개변수를 반환하는 함수이다.
예제 27
√ 문제
OLYMPIC 테이블에서 MASCOT가 NULL이면 ‘YES’를 NULL이 아니면 ‘NO’ 를 출력하는 컬럼을 추가하라.
√ SQL문
SELECT HOST_YEAR, HOST_NATION, NVL2(MASCOT, 'YES', ‘NO’)
FROM OLYMPIC
√ 결과
NO
HOST_YEAR
HOST_NATION
Null
Value
Check
5
1988
Korea
YES
null
null
6
1984
USA
YES
null
null
7
1980
USSR
YES
null
null
8
1976
Canada
YES
null
null
9
1972
Germany
YES
null
null
10
1968
Mexico
NO
null
null
11
1964
Japan
NO
null
null
12
1960
Italy
NO
null
null
13
1956
Australia
NO
null
null
14
1952
Finland
NO
null
null
√ 설명
NVL2 함수는 첫 번째 매개변수가 NULL 값이 아니면 두 번째 매개변수로, NULL 값이면 세 번째 매개변수를 반환하는 함수이다.
예제 28
√ 문제
OLYMPIC 테이블에서 HOST_CITY가 ‘Seoul’ 인 것만 Local Number(지역번호)를 ‘02’ 설정하는 쿼리 문을 작성하라.
√ SQL문
SELECT HOST_CITY, DECODE(HOST_CITY, 'Seoul', '02', 'Others') AS "Local Number"
FROM OLYMPIC
√ 결과
NO
HOST_YEAR
HOST_NATION
1
Athens
Others
2
Sydney
Others
3
Atlanta
Others
4
Barcelona
Others
5
Seoul
02
6
Los
Angeles
7
Moscow
Others
8
Montreal
Others
9
Munich
Others
10
Mexico
City
√ 설명
DECODE 함수는 첫 번째 매개변수와 두 번째 매개변수를 비교하여 같으면 세 번째 매개변수를, 거짓이면 네 번째 매개변수를 반환하는 함수이다.
예제 29
√ 문제
PARTICIPANT 테이블에서 GOLD 수가 30개 이상인 국가의 레벨을 1, GOLD 수가 20개 이상인 국가의 레벨을 2, GOLD 수가 20개 미만인 국가의 레벨을 3 으로 표현하는 컬럼을 추가하여 조회하라. (단, GOLD 수가 15개 이상인 국가만 출력함.)
√ SQL문
SELECT HOST_YEAR, NATION_CODE, GOLD
CASE WHEN GOLD >= 30 THEN 1,
WHEN GOLD >= 20 THEN 2
WHEN GOLD < 20 THEN 3
END AS LEVEL
FROM OLYMPIC
√ 결과
NO
HOST_YEAR
NATION_CODE
GOLD
LEVEL
1
2004
JPN
16
3
2
2004
AUS
17
3
3
2004
RUS
27
2
4
2004
CHN
32
1
5
2004
USA
36
1
6
2000
RUS
32
1
7
2000
USA
37
1
8
2000
AUS
16
3
9
2000
CHN
28
2
10
1996
CHN
16
3
√ 설명
CASE절은 WHEN <표현식> THEN <값>의 형식을 반복하여 조건 별로 값을 반환한다. WHEN절에서 모든 조건을 만족하지 않을 때는 ELSE절의 값을 반환한다.
예제 30
√ 문제
PARTICIPANT 테이블에서 NATION_CODE가 ‘KOR’ 또는 ‘JPN’ 인 GOLD 컬럼을 출력하라.
√ SQL문
SELECT HOST_YEAR, NATION_CODE,
FROM OLYMPIC
WHERE NATION_CODE IN ('KOR', 'JPN')
√ 결과
NO
HOST_YEAR
NATION_CODE
GOLD
1
2004
JPN
16
2
2000
JPN
5
3
1996
JPN
3
4
1992
JPN
3
5
1988
JPN
4
6
2004
KOR
9
7
2000
KOR
8
8
1996
KOR
7
9
1992
KOR
12
10
1988
KOR
12
√ 설명
IN 함수는 소괄호 안에 나열된 값들의 집합에서 컬럼의 값이 존재하면 참을 반환하는 함수이다. IN 함수는 OR 연산자로 바꾸어 표현할 수 있다.
예제 31
√ 문제
ATHLETE 테이블에서 성이 Hong인 운동 선수를 조회하라.
√ SQL문
SELECT NAME, GENDER, NATION_CODE, EVENT
FROM ATHLETE
WHERE NAME LIKE ‘Hong %’
√ 결과
NO
NAME
GENDER
NATION_CODE
EVENT
1
Hong
Sung
Sik
M
2
Hong
Jeong-Ho
W
KOR
3
Hong
Cha
Ok
W
4
Hong
Sung-Heon
M
KOR
√ 설명
LIKE 함수를 사용할 때 ‘%’ 문자는 문자열 내에서 와일드카드 문자로 사용된다. 물론 문자열의 앞에서도 사용이 가능하며, WHERE절의 문자열 내에서 ‘%’를 기호로 사용하려면 역슬래쉬(‘\’를 앞에 붙인 후 ESCAPE ‘\’를 추가하면 된다.
예제 32
√ 문제
HISTORY 테이블에서 HOST_YEAR 컬럼의 내용이 ’20__’ 형태인 것을 출력하라.
√ SQL문
SELECT ATHLETE, HOST_YEAR
FROM HISTORY
WHERE TO_CHAR(HOST_YEAR) LIKE ‘20__’
√ 결과
NO
ATHLETE
HOST_YEAR
1
Phelps
Michael
2
Peirsol
Aaron
3
Hackett
Grant
4
Thorpe
Ian
5
Thorpe
Ian
6
Van
Den
√ 설명
‘_’ 문자는 한 글자를 의미하는 와일드카드 문자이다. 한글과 영문 모두 한 글자를 의미하는 것으로서 한 바이트를 의미하는 것은 아니다. 참고로 HOST_YEAR 컬럼의 데이터 타입이 INT 형이기 때문에 문자 형으로 변환하여 ‘_’ 와일드 카드를 활용하여 조회하였다.
) 버튼을 클릭하여 작성 하시면 됩니다.