0812_Oracle : JOIN을 활용한 문제풀이 [210813 수정]

만들 테이블의 ERD

문제 1

score 테이블과 injeok 테이블을 조인하여 다음의 컬럼을 출력한다.

-- 출력할 컬럼

hak, ban, 구분, name, com, excel, word, tot, ave, 학급석차, 학년석차

-- 처리조건

tot = com+excel+word

ave = (com+excel+word) / 3 -> 소수점 2째자리 반올림 ★ ROUND(ave, 1) 로 바꿔야 한다. (수정 21.08.13) 2의 자리에서 반올림에서 소수점 1자리까지 표시하는 것이다. 밑에 사진은 그냥 소수점 2자리까지 표시인 것으로 두겠다.

구분 : gubun 1 -> 1학기중간, 2 -> 1학기기말, 3 -> 2학기중간, 4 -> 2학기기말

학급 및 학년 석차 : 구분 별 학급 및 학년 석차

SELECT hak, ban, 
	DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분, 
	name, com, excel, word, com+excel+word tot, 
    ROUND((com+excel+word) / 3, 1) ave, 
    RANK() OVER(PARTITION BY hak, gubun, ban ORDER BY com+excel+word DESC) 학급석차,
    RANK() OVER(PARTITION BY hak, gubun ORDER BY com+excel+word DESC) 학년석차
FROM score s
LEFT OUTER JOIN injeok i ON s.hakbeon = i.hakbeon
ORDER BY hak, gubun, ban;
-- LEFT OUTER JOIN을 활용하면, 시험을 치지 않은 학생들도 같이나옴. 
-- 이 자료에서는 시험을 안친 학생이 없어서 상관없음.
-- FOREIGN KEY 의 제약 조건으로 인해 injeok이 없으면 score 자료도 없게 제약조건을 둔 것

-- 하지만 시험을 치지 않은 학생들을 배제하고 나오게 하려면 EQUI JOIN을 쓴다.
SELECT hak, ban, 
	DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분, 
	name, com, excel, word, com+excel+word tot, 
    ROUND((com+excel+word) / 3, 1) ave, 
    RANK() OVER(PARTITION BY hak, gubun, ban ORDER BY com+excel+word DESC) 학급석차,
    RANK() OVER(PARTITION BY hak, gubun ORDER BY com+excel+word DESC) 학년석차
FROM score s
JOIN injeok i ON s.hakbeon = i.hakbeon
ORDER BY hak, gubun, ban;

-- 여기서 학번이 기본키이기 때문에 꼭 학번이 존재한다.
SELECT hak, ban, 
	DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분, 
	name, com, excel, word, com+excel+word tot, 
    ROUND((com+excel+word) / 3, 1) ave, 
    RANK() OVER(PARTITION BY hak, gubun, ban ORDER BY com+excel+word DESC) 학급석차,
    RANK() OVER(PARTITION BY hak, gubun ORDER BY com+excel+word DESC) 학년석차
FROM score s
RIGHT OUTER JOIN injeok i ON s.hakbeon = i.hakbeon
ORDER BY hak, gubun, ban;

 

문제 2

score 테이블과 injeok 테이블을 조인하여 다음의 컬럼을 출력한다.

-- 출력할 컬럼

학년, 반, 구분, 이름, 총점, 평균, 판정

-- 처리조건

구분 : gubun 1-> 1학기중간, 2 -> 1학기기말, 3 -> 2학기중간, 4 -> 2학기기말 로 표시

판정

세 과목 모두 40점 이상이고, 평균 60점 이상 : 합격

세 과목 중 한과목이라도 40점 미만이고, 평균 60점 이상이면 : 과락

그렇지 않으면 불합격으로 처리한다.

SELECT hak 학년, ban 반, DECODE(gubun, 1, '1학기중간', 2, '1학기기말', 3, '2학기중간', 4, '2학기기말') 구분, 
    name 이름, com+excel+word 총점, 
    ROUND((com+excel+word) / 3, 1) 평균, 
    (CASE 
        WHEN com >= 40 AND excel >= 40 AND word >= 40 AND (com+excel+word) / 3 >= 60 THEN '합격'
        WHEN (com+excel+word)/3 >= 60 THEN '과락'
        ELSE '불합격'
    END) 판정
FROM score s
JOIN injeok i ON s.hakbeon = i.hakbeon;

확인을 위해 출력한 테이블