SELECT CASEpref_name
WHEN '인천' THEN '경기도'
WHEN '수원' THEN '경기도'
WHEN '광명' THEN '경기도'
WHEN '안양' THEN '경기도'
WHEN '대구' THEN '경북'
WHEN '경주' THEN '경북'
WHEN '포항' THEN '경북'
ELSE '기타' END AS district,
SUM(population)
FROM PopTbl
GROUP BY CASE pref_name
WHEN '인천' THEN '경기도'
WHEN '수원' THEN '경기도'
WHEN '광명' THEN '경기도'
WHEN '안양' THEN '경기도'
WHEN '대구' THEN '경북'
WHEN '경주' THEN '경북'
WHEN '포항' THEN '경북'
ELSE '기타' END;

DISTRICT SUM(POPULATION)
--------- ------------------
경기도 450
경북 800
기타 450

SELECT pref_name,
SUM( CASEWHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
SUM( CASEWHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
FROM PopTbl2
GROUP BY pref_name;

PREF_NAME CNT_M CNT_F
----------------------- ---------- ----------
경주 20 80
포항 125 125
대구 100 200
광명 100 50
안양 100 100
서울 250 150
인천 60 40
수원 100 100

--조건１．
UPDATE Personnel
SET salary = salary * 0.9
WHERE salary >= 300000;

--조건２．
UPDATE Personnel
SET salary = salary * 1.20
WHERE salary >= 250000 AND salary < 280000;

UPDATE Personnel
SET salary = CASEWHEN salary >= 300000
THEN salary * 0.9
WHEN salary >= 250000 AND salary < 280000
THEN salary * 1.20
ELSE salary END;

COURSE_NAME 6월 7월 8월
------------------ -- -- --
피아노 ○ × ×
태권도 × × ○
미술 ○ × ×
컴퓨터 ○ ○ ○

--IN 이용--

SELECT CM.course_name,
CASEWHEN CM.course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200706) THEN '○'
ELSE '×' END AS "200706",
CASEWHEN CM.course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200707) THEN '○'
ELSE '×' END AS "200707",
CASEWHEN CM.course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200708) THEN '○'
ELSE '×' END AS "200708"
FROM CourseMaster CM;

--EXISTS 이용--

SELECT CM.course_name,
CASEWHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200706
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "200706",
CASEWHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200707
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "200707",
CASEWHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200708
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "200708"
FROM CourseMaster CM;

--조건1 :1개의 클럽에 전념하는 학생을 선택--

select std_id, max(club_id) main_club
from studentclub
group by std_id
having count(*) > 1;

STD_ID MAIN_CLUB
-------- ----------
300 4
400 5
500 6

--조건2 : 복수 클럽에 소속되어 있는 학생을 선택--

select std_id, club_id main_club
from studentclub
where main_club_flg = 'Y'

STD_ID MAIN_CLUB
-------- ----------
100 1
200 3

select std_id,
casewhen count(*) = 1
then max(club_id)
else max(casewhen main_club_flg = 'Y'
then club_id
else null end)
end main_club
from studentclub
group by std_id;

STD_ID MAIN_CLUB
------- ----------
100 1
400 5
500 6
300 4
200 3