데이터베이스 퀴즈 및 튜닝 문제
스터디 인원에 대한 개인별 스터디 가입현황을 조회하고자 합니다.
1. 테이블은 3개입니다. ID 성명 스터디 1차 2차 3차 참여횟수 001 김개똥 Database O O 2 Java O 1 002 이말똥 Database O O O 3 Java 0 003 박소똥 Database 0 Java O O 2 004 강닭똥 Database O 1 Java 0
- 스터디학생(student), 스터디과목(course), 스터디마스터(study)
2. 작성 조건
- 학생별로 2개행씩(스터티과목수) 조회되어야 합니다.
- ID와 성명은 2행중 첫번째 행만 출력합니다.
- 스터디 차수별로 가입여부에 '○'표로 표시합니다. 3차까지 표시.
- 과목별 참여건수를 표시합니다.
- 서브쿼리 없이 작성하세요.
3. 결과
4. 다음 with절을 이용하여 작성해 보세요.
WITH student AS
(
SELECT '001' s_id, '김개똥' s_nm FROM dual
UNION ALL SELECT '002', '이말똥' FROM dual
UNION ALL SELECT '003', '박소똥' FROM dual
UNION ALL SELECT '004', '강닭똥' FROM dual
)
, course AS
(
SELECT '001' c_id, 'Database' c_nm FROM dual
UNION ALL SELECT '002', 'Java' FROM dual
)
, study AS
(
SELECT '001' s_id, '001' c_id, 1 chasu FROM dual
UNION ALL SELECT '001', '001', 3 FROM dual
UNION ALL SELECT '001', '002', 2 FROM dual
UNION ALL SELECT '002', '001', 1 FROM dual
UNION ALL SELECT '002', '001', 2 FROM dual
UNION ALL SELECT '002', '001', 3 FROM dual
UNION ALL SELECT '003', '002', 1 FROM dual
UNION ALL SELECT '003', '002', 2 FROM dual
UNION ALL SELECT '004', '001', 1 FROM dual
)
-- 이곳에 들어갈 쿼리를 작성해 주세요.
SELECT CASE WHEN b.c_id = '001' THEN a.s_id END AS "ID"
, CASE WHEN b.c_id = '001' THEN a.s_nm END AS "성명"
, b.c_nm AS "스터디"
, MIN(CASE WHEN c.chasu = 1 THEN '○' END) AS "1차"
, MIN(CASE WHEN c.chasu = 2 THEN '○' END) AS "2차"
, MIN(CASE WHEN c.chasu = 3 THEN '○' END) AS "3차"
, COUNT(c.s_id) AS "참여횟수"
FROM student a
CROSS JOIN course b
LEFT OUTER JOIN study c
ON a.s_id = c.s_id
AND b.c_id = c.c_id
GROUP BY a.s_id, a.s_nm, b.c_id, b.c_nm
ORDER BY a.s_id, b.c_id
-- 이곳에 들어갈 쿼리를 작성해 주세요.
이거 맞추면 상품권 있나염?
다음 퀴즈땐 고려해보도록 하겠어염.
AND B.C_ID = C.C_ID(+)
이렇게 쓰면 안되는데...ANSI SQL을 사용하면 되는 건 어떤 차이가 있나요?
1. in 이나 or 조건이 사용안되구요.
2. 두개이상의 테이블과 아우터 조인이 안됩니다.
해결방법은 인라인 뷰를 이용해서 해결해야 합니다.
그러나 ansi표준 쿼리를 이용하게 되면 이러한 제약을 받지 않습니다.
이번 퀴즈의 의도는 이러한 장점을 알려드리기 위함 이었습니다.
,min(decode(y.chasu,1,'O')) first1
,min(decode(y.chasu,2,'O')) second1
,min(decode(y.chasu,3,'O')) third
,sum(decode(y.chasu,null,0,1)) cnt
from (
select a.s_id, a.s_nm, b.c_id, b.c_nm
from student a, course b
) x,
study y
where y.c_id(+) = x.c_id
and y.s_id(+) = x.s_id
group by x.s_id, x.s_nm, x.c_nm
order by x.s_id, x.s_nm, x.c_nm
몇가지 아쉬운점이 있네요.
1. 건수는 다음과 같이 간단하게 바꿀 수 있습니다.
sum(decode(y.chasu,null,0,1)) ===> COUNT(y.chasu)
2. 요구조건인 id, 성명 한번만 표시하기와 서브쿼리 사용안하기 조건이 충족되지 않았네요.
정답은 퀴즈속에 있습니다.
[-- 이곳에 들어갈 쿼리를 작성해 주세요.] 사이의 여백부분을 마우스 드래그해서 확인해 보세요.
인라인뷰라고 하는데 서브쿼리의 일종입니다.
select절에 사용되면 스칼라서브쿼리
from절에 사용되면 인라인뷰
where절에 사용되면 요건 별도의 명칭은 없네요. 그냥 서브쿼리라고 하지요.
DECODE(A.ID2, A.LAG_ID2, NULL, A.ID2) ID
,DECODE(A.ID2, A.LAG_ID2, NULL, A.NM) NM
,A.STUDY
,A.CHASU1
,A.CHASU2
,A.CHASU3
FROM
(
SELECT
A.S_ID ID2
,A.S_NM NM
,A.C_NM STUDY
,LAG(A.S_ID) OVER (ORDER BY A.S_ID) LAG_ID2
,DECODE(COUNT(DECODE(B.CHASU,1,1)),0,NULL,'O') CHASU1
,DECODE(COUNT(DECODE(B.CHASU,2,1)),0,NULL,'O') CHASU2
,DECODE(COUNT(DECODE(B.CHASU,3,1)),0,NULL,'O') CHASU3
FROM
(
SELECT
*
FROM STUDENT A
,COURSE B
)A
,STUDY B
WHERE 0=0
AND A.C_ID = B.C_ID(+)
AND A.S_ID = B.S_ID(+)
GROUP BY
A.S_ID
,A.S_NM
,A.C_NM
)A
ORDER BY
A.ID2
,NM
기본적인 개념은 맞게 작성하셨습니다만 쿼리가 너무 복잡하네요.
정답은 퀴즈속에 있습니다.
[-- 이곳에 들어갈 쿼리를 작성해 주세요.] 사이의 여백부분을 마우스 드래그해서 확인해 보세요.
ㅋㅋㅋㅋ 답변이 그밑에 있는줄도 모르고
제가 생각해도 참 복잡하게 쿼리 만들고 있네요 ^^
,NAME
,co_num
,co_name
,max(chasu_1)
,max(chasu_2)
,max(chasu_3)
,COUNT(num)
FROM (
SELECT std.s_id AS num
,st.s_nm AS NAME
,co.c_id AS co_num
,co.c_nm AS co_name
,CASE
WHEN std.chasu = 1 THEN 'O'
END AS chasu_1
,CASE
WHEN std.chasu = 2 THEN 'O'
END AS chasu_2
,CASE
WHEN std.chasu = 3 THEN 'O'
END AS chasu_3
FROM student st
,course co
,study std
WHERE co.c_id = std.c_id
AND st.s_id = std.s_id
GROUP BY std.s_id ,co.c_id,co.c_nm, std.chasu ,st.s_nm
)
GROUP BY num,co_num,co_name,NAME
ORDER BY num
;
답을봐도 이거 이상 진도가 ^^;;
크로스 조인의 개념을 모르겠네요 ㅠ.ㅠ
퀴즈를 낼 당시엔 몰랐던 내용이 있는데.
기존 오라클 쿼리에서는 아우터 조인에 제약사항이 있었는데...
1. in 이나 or 조건이 사용안되구요.
2. 두개이상의 테이블과 아우터 조인이 안됩니다.
이 두가지 제약이 버전업되면서 풀린 모양입니다.
11G에서는 제약이 없는것 확인했구요.
10G에서도 뒷쪽 버전인 경우에는 제약이 풀렸다고 하네요.(10.2.0.5 부터인걸로 추정됨)
참고하세요.
) 버튼을 클릭하여 작성 하시면 됩니다.