[ORACLE] USSOFT ORACLE 교육 2일차

📌 학생 테이블

SELECT *
  FROM HS_STUDENTS; 

📌 학생 성적 테이블

SELECT *
  FROM HS_GRADE;

📌 문제 1.

📝 문제설명

학생번호 별 점수 총계, 평균점수, 평균점수 별 순위를 구하세요(학년, 반 상관없이)

💡 풀이

select s.no_seq as 학생번호,
 s.no_korean as 국어, s.no_english as 영어,
 s.no_math as 수학, 
 (s.no_korean+ s.no_english + s.no_math) as 총계,
 round((s.no_korean+s.no_english + s.no_math)/3) as 평균, 
 row_number() over(order by '평균') as 순위
 from hs_students h, hs_grade s
 where h.no_seq = s.no_SEQ
 order by 6 desc;

🤏 TIP

count 함수

sum 함수

round 함수

group by 함수

📌 문제 2.

📝 문제설명

학년, 반별 학생수, 총계, 평균점수를 구하세요

💡 풀이

select h.no_grade as 학년, h.no_class as 반,
  count(h.no_seq) as 학생수, 
  sum(s.no_korean+s.no_english+s.no_math) as 총계, 
  round(avg((s.no_korean+s.no_english+s.no_math)/3)) as 평균
  from hs_students h, hs_grade s
  where h.no_seq = s.no_studentseq
  group by h.no_grade, h.no_class;

🤏 TIP

row_number 함수

📌 문제 3.

📝 문제설명

학생번호별 국어, 수학, 영어, 총계, 평균, 순위와 학년,반별 학생수, 국어평균, 수학평균, 영어평균, 총계, 반평균을 구하세요

💡 풀이

SELECT
    DECODE(GROUPING(no_grade), 1, '학년', no_grade) AS 학년,
    DECODE(GROUPING(no_class), 1, '반', no_class) AS 반,
    DECODE(GROUPING(s.no_seq), 1, COUNT(*) || '명', s.no_seq) AS 학생번호,
    SUM(s.no_korean) AS 국어,
    SUM(s.no_english) AS 영어,
    SUM(s.no_math) AS 수학,
    SUM(s.no_korean + s.no_english + s.no_math) AS 총점,
    ROUND(avg(s.no_korean + s.no_english + s.no_math)/3) AS 평균,
    RANK() OVER (PARTITION BY no_grade, no_class ORDER BY (AVG((s.no_korean + s.no_english + s.no_math)/3)) DESC) AS 순위
 FROM hs_students h, hs_grade s
 WHERE h.no_seq = s.no_seq
 GROUP BY ROLLUP(no_grade, no_class, s.no_seq);

😮❗❗❗❗❗❗
위의 방법으로는 각 반을 포함한 순위가 매겨지는 오류가 있음
아래의 방법은 학년, 반, 학생번호를 각각 GROUPING으로 묶어서 오류를 해결함

SELECT 
-- GROUPING(A.NO_SEQ),
-- GROUPING(A.NO_GRADE),
-- GROUPING(A.NO_CLASS),
  A.NO_SEQ 학생번호,
  A.NO_GRADE 학년,
  A.NO_CLASS 반,
  SUM(B.NO_KOR)국어,
  SUM(B.NO_ENG)영어,
  SUM(B.NO_MAT)수학,
  SUM(B.NO_KOR + B.NO_ENG + B.NO_MAT) 총계,
  ROUND(AVG((B.NO_KOR + B.NO_ENG + B.NO_MAT) / 3), 2) 평균,
  DECODE(
    GROUPING(A.NO_SEQ) + GROUPING(A.NO_GRADE) + GROUPING(A.NO_CLASS), 0,
    RANK() OVER(
      PARTITION BY GROUPING(A.NO_SEQ) + GROUPING(A.NO_GRADE) + GROUPING(A.NO_CLASS), A.NO_GRADE, A.NO_CLASS
    ORDER BY ROUND(AVG((B.NO_KOR + B.NO_ENG + B.NO_MAT) / 3), 2) DESC
    ), NULL
  ) RANKING
  FROM HS_STUDENTS A, HS_SCORE B
  WHERE A.NO_SEQ = B.NO_SEQ
  GROUP BY ROLLUP(A.NO_GRADE, A.NO_CLASS, A.NO_SEQ)
  HAVING GROUPING(A.NO_SEQ) + GROUPING(A.NO_GRADE) + GROUPING(A.NO_CLASS) IN (0, 1)
  ORDER BY A.NO_GRADE, A.NO_CLASS,
  GROUPING(A.NO_SEQ) + GROUPING(A.NO_GRADE) + GROUPING(A.NO_CLASS),
  DECODE(
    GROUPING(A.NO_SEQ) + GROUPING(A.NO_GRADE) + GROUPING(A.NO_CLASS), 0,
    RANK() OVER(
      PARTITION BY A.NO_GRADE, A.NO_CLASS
      ORDER BY ROUND(AVG((B.NO_KOR + B.NO_ENG + B.NO_MAT) / 3), 2) DESC
    ), NULL
  );

🤏 TIP

ROLL UP 함수
GROUPING 함수
RANK 함수
PARTITION BY 함수