데이터베이스 노하우/팁/자료실
by 문학청년 [CUBRID 노하우/팁/자료] 큐브리드 cubrid [2009.11.17 17:30:33]
데이터 조회
ORDER BY절이 포함된 질의
질의 결과를 오름차순이나 내림차순으로 정렬(sorting)하는 경우가 흔히 있다. 사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 DBMS가 레코드들을 검색한 임의의 순서대로 사용자에게 제시된다. ORDER BY 절에서 하나 이상의 컬럼을 사용하여 검색 결과를 정렬할 수 있다. ORDER BY 절은 SELECT문에서 가장 마지막에 사용되는 절이다.
ORDER BY 절의 디폴트 정렬 순서는 오름차순(ASC:ascending)이다. DESC를 지정하여 정렬 순서를 내림차순으로 지정할 수 있다. 널 값도 정렬 결과에 표시된다. 널 값은 오름차순에서 가장 앞에 나타나고, 내림차순에서는 가장 뒤에 나타난다. SELECT절에 명시한 컬럼들을 사용해서 정렬해야 한다.
예제 36
√ 문제
CODE 테이블의 모든 레코드를 오름차순과 내림차순으로 각각 정렬하여 조회하라.
√ SQL문 1
SELECT *
FROM CODE
WHERE ORDER BY F_NAME
√ SQL문 2
SELECT *
FROM CODE
WHERE ORDER BY F_NAME DESC;
√ 결과 1
NO
s_name
f_name
1
X
Mixed
2
W
Woman
3
M
Man
4
B
Bronze
5
S
Silver
6
G
Gold
√ 결과 2
NO
s_name
f_name
1
W
Woman
2
S
Silver
3
X
Mixed
4
M
Man
5
G
Gold
6
B
Bronze
√ 설명
오름차순으로 정렬할 때 ASC는 생략 할 수 있다.
예제 37
√ 문제
CODE 테이블에서 먼저 s_name을 오름차순으로 정렬한 뒤, 이후에 f_name을 내림차순으로 정렬하여 조회하라.
√ SQL문
SELECT *
FROM CODE
WHERE ORDER BY S_NAME, F_NAME DESC
혹은
SELECT *
FROM CODE
WHERE ORDER BY 1, 2 DESC
√ 결과
NO
s_name
f_name
1
B
Bronze
2
G
Gold
3
M
Man
4
S
Silver
5
W
Woman
6
X
Mixed
√ 설명
ORDER BY 절에 여러 컬럼을 나열할 수 있다. 단 SELECT 절에 나타나는 컬럼들만 나열할 수 있다. 컬럼 단위로 오름차순과 내림차순을 별도로 지정할 수 있다. 두 번째 SQL문과 같이 ORDER BY 절에 이름대신 SELECT절에 나타나는 컬럼의 순서를 기술해도 좋다. 만약 ORDER BY절에 숫자로 컬럼 들을 지정하여 정렬할 때, ‘SELECT *’와 같이 SELECT 절에 와일드카드 문자를 명시하면 테이블 생성 문에서 기술한 순서대로 적용된다.
예제 38
√ 문제
CODE 테이블에서 f_name을 오름차순으로 정렬하여 조회하되, 정렬 이전의 순서와 정렬 이후의 순서를 함께 조회하라.
√ SQL문
SELECT ROWNUM, ORDERBY_NUM(), D.*
FROM CODE AS D
WHERE ORDER BY F_NAME
√ 결과
NO
ROWNUM
ORDERBY_NUM()
s_name
f_name
1
4
1
B
Bronze
2
6
2
G
Gold
3
3
3
M
Man
4
1
4
X
Mixed
5
5
5
S
Silver
6
2
6
W
Woman
√ 설명
큐브리드에서는 ORDERBY_NUM 함수를 제공한다. 이 함수는 SELECT문에 ORDER BY절이 나타나지 않으면 사용할 수 없다. ROWNUM 컬럼은 데이터를 정렬하기 이전에 추출된 테이블에 대해서 순서를 매기는 반면에, ORDERBY_NUM 함수는 추출한 데이터를 정렬한 이후에 순서를 매긴 값을 반환한다.
예제 39
√ 문제
PARTICIPANT 테이블에서 GOLD 컬럼을 내림차순 정렬하여 출력하고, 정렬된 데이터의 3행부터 5행까지의 쿼리를 구하라.
√ SQL문 1
SELECT NATION_CODE, GOLD
FROM PARTICIPANT ORDER BY GOLD DESC
√ SQL문 2
SELECT NATION_CODE, GOLD FROM PARTICIPANT ORDER BY GOLD DESC
FOR ORDERBY_NUM() BETWEEN 2 AND 4
√ 결과 1
NO
NATION_CODE
GOLD
1
URS
55
2
EUN
45
3
USA
44
4
USA
37
5
USA
37
6
GDR
37
√ 결과 2
NO
NATION_CODE
GOLD
2
EUN
45
3
USA
44
4
USA
37
집단 함수
5.2절에서 설명한 함수와는 달리 데이터베이스에서 검색된 여러 레코드들의 집단에 적용되는 함수가 집단 함수이다. 집단 함수는 SELECT절과 HAVING절에만 나타날 수 있다. COUNT, MIN, MAX는 숫자형 컬럼과 비 숫자형 컬럼에 모두 적용할 수 있지만, SUM과 AVG는 숫자형 컬럼에만 적용할 수 있다. COUNT(*)를 제외하고는 모든 집단 함수들이 널 값을 제거한 후 남아있는 값들에 대해서 집단 함수의 값을 구한다. COUNT(*)는 널이나 중복된 값들이 나타나는 것에 상관없이 한테이블의 모든 행들의 총 개수를 구하는 반면에 COUNT(컬럼)은 해당 컬럼에서 널 값이 아닌 값들의 개수를 구한다.
기본 집단 함수
예제 40
√ 문제
NATION_CODE 테이블에서 각 국가별 경기장 수를 출력하는 쿼리문을 작성하라.
√ SQL문
SELECT NATION_CODE, COUNT(NAME) AS CITY_COUNT
FROM STADIUM
GROUP BY NATION_CODE
√ 결과
NO
NATION_CODE
CITY_COUNT
1
AUS
29
2
ESP
27
3
GRE
27
4
KOR
32
5
USA
26
√ 설명
국가 코드가 같을 경우 서로 다른 도시의 카운터를 구한다.
예제 41
√ 문제
PARTICIPANT 테이블에서 각 국가별 경기장 수를 출력하는 쿼리 문을 작성하라.
√ SQL문
SELECT AVG(GOLD), SUM(GOLD), COUNT(GOLD)
FROM PARTICIPANT
√ 결과
NO
AVG(GOLD)
SUM(GOLD)
COUNT(GOLD)
1
1
1371
916
√ 설명
GOLD의 평균, 합계, 개수를 구한다.
예제 42
√ 문제
PARTICIPANT 테이블에서 GOLD의 최소 값과 최대 값을 구하라.
√ SQL문
SELECT MIN(GOLD), MAX(GOLD)
FROM PARTICIPANT
√ 결과
NO
MIN(GOLD)
MAX(GOLD)
1
0
55
GROUP BY 절을 포함한 집단 함수
GROUP BY 절에 사용된 컬럼에 동일한 값을 갖는 레코드들이 각각 하나의 그룹으로 묶인다. 이 때 사용된 컬럼을 그룹화 컬럼(grouping column)이라고 한다. GROUP BY 절에 사용되는 컬럼은 집단 함수에 사용되지 않은 컬럼이어야 한다. 각 그룹에 대하여 결과 테이블에 하나의 레코드가 생성된다. 따라서 SELECT 절에는 각 그룹마다 하나의 값을 갖는 컬럼, 집단 함수, 그룹화에 사용된 컬럼만 나타날 수 있다. 만일 SELECT 절에 집단 함수가 포함되어 있고, GROUP BY 절이 없는 경우에는 SELECT 절에 집단 함수에서 참조되지 않는 컬럼이 나타날 수 없다.
예제 43
√ 문제
PARTICIPANT 테이블에서 역대 국가들의 GOLD(금메달)를 획득한 최소 개수와 최대 개수를 구하라. 단, GOLD를 20개 이상 획득한 나라에서만 조회하라.
√ SQL문
SELECT NATION_CODE, MIN(GOLD), MAX(GOLD)
FROM PARTICIPANT
WHERE GOLD > 20
GROUP BY NATION_CODE
√ 결과
NO
NATION_CODE
MIN(GOLD)
MAX(GOLD)
1
CHN
28
32
2
EUN
45
45
3
GDR
37
37
4
GER
33
33
5
RUS
26
32
6
URS
55
55
HAVING 절을 포함한 집단 함수
때로는 어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용할 수 있다. 각 그룹마다 하나의 값을 갖는 컬럼을 사용하여 각 그룹이 만족해야 하는 조건을 명시한다. HAVING 절은 그룹화 컬럼과 같은 값을 갖는 컬럼들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타난다. HAVING 절에 나타나는 컬럼은 반드시 GROUP BY 절에 나타나거나 집단 함수에 포함되어야 한다. WHERE 절과 비슷하지만 WHERE 절은 레코드들을 걸러내고, HAVING 절은 그룹들을 걸러낸다는 점이 다르다.
예제 44
√ 문제
PARTICIPANT 테이블에서 한국과 일본의 GOLD(금메달)를 획득한 최소 개수와 최대 개수를 구하라.
√ SQL문
SELECT NATION_CODE, MIN(GOLD), MAX(GOLD)
FROM PARTICIPANT
GROUP BY NATION_CODE
HAVING NATION_CODE IN(‘KOR’, ‘JPN’)
√ 결과
NO
NATION_CODE
MIN(GOLD)
MAX(GOLD)
1
JPN
3
16
2
KOR
7
12
예제 45
√ 문제
PARTICIPANT 테이블에서 GOLD(금메달)를 획득한 최대 개수 – 최소 개수의 차가 10 이상만 출력되도록 쿼리 문을 작성하라..
√ SQL문
SELECT NATION_CODE, MIN(GOLD), MAX(GOLD)
FROM PARTICIPANT
GROUP BY NATION_CODE
HAVING MAX(GOLD) - MIN(GOLD) >= 10
√ 결과
NO
NATION_CODE
MIN(GOLD)
MAX(GOLD)
1
AUS
3
17
2
CHN
5
32
3
ESP
1
13
4
GBR
1
11
5
GER
13
33
6
JPN
3
16
7
NED
2
12
예제 46
√ 문제
PARTICIPANT 테이블에서 GOLD(금메달)를 획득한 최대 개수 – 최소 개수의 차가 10 이상만 출력하게 하고 GOLD의 합계를 내림차순으로 정렬하라.
√ SQL문
SELECT NATION_CODE, MIN(GOLD), MAX(GOLD), SUM(GOLD)
FROM PARTICIPANT
GROUP BY NATION_CODE
HAVING MAX(GOLD) - MIN(GOLD) >= 10
ORDER BY SUM(GOLD) DESC
√ 결과
NO
NATION_CODE
MIN(GOLD)
MAX(GOLD)
SUM(GOLD)
1
CHN
5
32
97
2
GER
13
33
79
3
AUS
3
17
52
4
JPN
3
16
31
5
ESP
1
13
25
집합 연산
집합 연산을 적용하려면 두 테이블이 합집합과 호환성을 가져야 한다. UNION(합집합), DIFFERENCE(차집합), INTERSECT(교집합)에서는 입력 테이블과 결과 테이블에서 중복된 레코드가 배제된다. UNION ALL(합집합), DIFFERENCE ALL(차집합), INTERSECT ALL(교집합)에서는 입력 테이블과 테이블에 중복된 레코드가 허용된다.
예제 47
√ 문제
STADIUM 테이블에서 NATION_CODE(국가)가 ‘KOR’ 인 것과 ADDRESS가 ‘Busan, Korea’인 쿼리 문을 조회하라
√ SQL문
SELECT NATION_CODE, ADDRESS
FROM STADIUM
WHERE NATION_CODE=’KOR’
UNION
SELECT NATION_CODE, ADDRESS
FROM STADIUM
WHERE ADDRESS =’Busan, Korea’
√ 결과
NO
NATION_CODE
ADDRESS
1
KOR
Busan,
2
KOR
Daegu,
3
KOR
Daejeon,
4
KOR
Goyang,
5
KOR
Gwacheon,
6
KOR
Gwangju,
√ 설명
UNION 대신 한 개의 SELECT문을 사용하면서 WHERE절에 OR 연산자를 사용해도 같은 결과를 얻을 수 있다.
예제 48
√ 문제
STADIUM 테이블에서 NATION_CODE(국가)가 ‘KOR’ 인 것과 ADDRESS가 ‘Busan, Korea’인 쿼리 문을 조회하라
√ SQL문
SELECT NATION_CODE, ADDRESS
FROM STADIUM
WHERE NATION_CODE=’KOR’
UNION ALL
SELECT NATION_CODE, ADDRESS
FROM STADIUM
WHERE NATION_CODE=’Busan, Korea’
√ 결과
NO
NATION_CODE
ADDRESS
4
KOR
Seoul,
5
KOR
Seoul,
6
KOR
Goyang,
7
KOR
Gwacheon,
8
KOR
Seoul,
9
KOR
Seoul,
√ 설명
위의 예제와 비슷하지만 UNION ALL은 출력 값의 중복을 허용한다.
) 버튼을 클릭하여 작성 하시면 됩니다.