[Oracle] SQL문제풀이 / SUBQUERY

 

목요일 수업 후 받은 문제 25개와

금요일 수업 후 19개의 서브쿼리 문제를 풀었고, 그중 어려웠던 문제와

두 가지 이상의 방법으로 풀어본 문제를 포스팅한다.

 

과제를 위한 워크시트를 따로 제공받음. (대학교의 정보를 담은)

---

 

문제1 

‘음악학과’ 학생들의 평점을 구하려고 한다. 
음악학과 학생들의 "학번", "학생 이름", "전체 평점"을 출력하는 SQL 문장을 작성하시오. 
(단, 평점은 소수점 1자리까지만 반올림하여 표시한다.)

 

1) 인라인뷰 안에 음악학과 컬럼에 별칭 사용

SELECT 
       V.학번
     , V.학생이름 
     , ROUND(AVG(G.POINT),1) "전체 평점"
 FROM (SELECT S.STUDENT_NO 학번
            , S.STUDENT_NAME 학생이름 
         FROM TB_STUDENT S
         JOIN TB_DEPARTMENT D ON(S.DEPARTMENT_NO = D.DEPARTMENT_NO)
       WHERE D.DEPARTMENT_NAME = '음악학과' 
       ) V
 JOIN TB_GRADE G ON (V.학번 = G.STUDENT_NO)                     
 GROUP BY V.학번, V.학생이름
 ORDER BY 학번;

 

2) 인라인뷰 단에 음악학과 정보를 모두 가져온뒤 메인쿼리문에서 컬럼에 별칭사용

SELECT 
       V.STUDENT_NO 학번
     , V.STUDENT_NAME 학생이름 
     , ROUND(AVG(G.POINT),1) "전체 평점"
 FROM (SELECT S.*
         FROM TB_STUDENT S
         JOIN TB_DEPARTMENT D ON(S.DEPARTMENT_NO = D.DEPARTMENT_NO)
       WHERE D.DEPARTMENT_NAME = '음악학과' 
       ) V
 JOIN TB_GRADE G ON (V.STUDENT_NO = G.STUDENT_NO)                     
 GROUP BY V.STUDENT_NO, V.STUDENT_NAME
 ORDER BY 학번

 

 

문제2

대학교 서반아어학과 학생들의 지도교수를 게시하고자 한다.
학생이름과 지도교수 이름을 찾고 만일 지도 교수가 없는 학생일 경우 
"지도교수 미지정‛으로 표시하도록 하는 SQL 문을 작성하시오. 
단, 출력헤더는 ‚학생이름, ‚지도교수 로 표시하며 고학번 학생이 먼저 표시되도록 한다.

 

1) UNION을 사용한 방법

이 때 , CASE문이 생각이 안났고 JOIN조건에서 이미 지도교수가 없는 학생이 걸러져서 어려웠다.

어떻게든 풀자는 생각으로 지도교수가 없는 학생에 대해서 따로 조회하여 결과를 UNION으로 합침.

SELECT 
      S.STUDENT_NAME 학생이름
    , p.professor_name 지도교수 
 FROM TB_STUDENT S
 JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 JOIN tb_professor P ON (s.coach_professor_no= p.PROFESSOR_NO)
 WHERE D.DEPARTMENT_NAME = '서반아어학과'
UNION
SELECT 
      S.STUDENT_NAME 학생이름
    , '지도교수 미지정' AS 지도교수
 FROM TB_STUDENT S
 JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '서반아어학과'
 AND s.coach_professor_no IS NULL;

 

2) CASE문 사용한 방법

팀스터디 팀원분께 혹시 이문제 CASE문으로 풀었는지 물어보고 그렇다고 하시길래

나도 그럼 풀 수 있다 하는 생각으로 다시 도전하여 풂.

SELECT S.STUDENT_NAME 학생이름
      , CASE 
        WHEN s.coach_professor_no IS NULL
        THEN '지도교수미지정' 
        ELSE (SELECT P.PROFESSOR_NAME
                FROM TB_PROFESSOR P 
               WHERE S.COACH_PROFESSOR_NO = P.PROFESSOR_NO
              )
        END 지도교수
  FROM TB_STUDENT S
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '서반아어학과';

 

 

문제3

휴학생이 아닌 학생 중 평점이 4.0 이상인 학생을 찾아 
그 학생의 학번, 이름, 학과 이름, 평점을 출력하는 SQL 문을 작성하시오.

 

1) 스칼라 쿼리를 사용하여 SELECT문 안에 평점을 구하는 코드를 작성함.

조건절에 스칼라쿼리로 구해진 평점을 WHERE절에서 다시 코드를 끌고와 4.0 이상인지 판별해야했다.

SELECT
       S.STUDENT_NO 학번
     , S.STUDENT_NAME 이름
     , D.DEPARTMENT_NAME 학과이름
     , (SELECT TO_CHAR(AVG(G.POINT), '0.0')
          FROM TB_GRADE G
         WHERE S.STUDENT_NO = G.STUDENT_NO ) 평점
 FROM TB_STUDENT S
 JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO=D.DEPARTMENT_NO)
WHERE S.ABSENCE_YN = 'N'
  AND (SELECT ROUND(AVG(G.POINT),2)
         FROM TB_GRADE G
        WHERE S.STUDENT_NO = G.STUDENT_NO )>= 4.0;

 

2) 인라인뷰를 사용한 방법 
평점이 4.0 이상인 조건을 판별하기 위해 위 코드는 평점에 대한 코드를 두번 작성해야 함.
평점을 뷰 안에서 구하고, WHERE절에서 별칭으로 간단하게 작성할 수 있다. 

SELECT
       V.학번
     , V.이름
     , V.학과이름
     , V.평점
  FROM (SELECT S.STUDENT_NO 학번
             , S.STUDENT_NAME 이름
             , D.DEPARTMENT_NAME 학과이름
             ,(SELECT ROUND(AVG(G.POINT),2)
                 FROM TB_GRADE G
                WHERE S.STUDENT_NO = G.STUDENT_NO) 평점
        FROM TB_STUDENT S
        JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO=D.DEPARTMENT_NO)
        WHERE S.ABSENCE_YN = 'N'
     ) V --휴학생이 아닌 학생들의 학번,이름,학과이름,평점
WHERE 평점 >= 4.0;

 

-- 전체 학생들 평균 구하는 코드 (서브쿼리)

SELECT S.STUDENT_NAME 이름
     , ROUND(AVG(G.POINT),2)
  FROM TB_STUDENT S
  JOIN TB_GRADE G ON (G.STUDENT_NO = S.STUDENT_NO)
 WHERE S.STUDENT_NO = G.STUDENT_NO
 GROUP BY S.STUDENT_NAME;

이런 식으로 메인쿼리안에 작성하기 전에 서브쿼리를 먼저 작성하고 아래에 남겨두었는데,

여러번의 코드 작성 때 수정과 오류가 나서 기본 틀을 다 지우고 새로 구할 때.........

기본 포맷은 이것이라는 걸 잊지 않기 위해 남겨두었다.

 

막힐 때 마다 좀 스스로한테 짜증이 났는데, 서브쿼리문 실행해보고 결과를 보면서

아 그래도 여기까지는 구했잖아 하면서 긍정적인 마음을 먹고 다시 생각해보기 좋았다..

 

 

문제4

국어국문학과에서 총 평점이 가장 높은 학생의 이름과 학번을 표시하는 SQL문을 작성하시오.

SELECT 
       V.학번
     , V.학생이름 
 FROM (SELECT 
             S1.STUDENT_NO 학번
           , S1.STUDENT_NAME 학생이름
           , AVG(G.POINT) 평점
         FROM TB_STUDENT S1
         JOIN TB_DEPARTMENT D ON(S1.DEPARTMENT_NO = D.DEPARTMENT_NO)
         JOIN TB_GRADE G ON (S1.STUDENT_NO = G.STUDENT_NO)   
        WHERE D.DEPARTMENT_NAME = '국어국문학과' 
        group by S1.STUDENT_NO, S1.STUDENT_NAME 
        ORDER BY 평점 DESC
       ) V                        
 WHERE ROWNUM <= 1;

ROWNUM은 WHERE절에서 기술하므로 정렬전에 행의 순서가 매겨지니

인라인뷰 안에서 미리 ORDER BY 해야함!

평점순으로 정렬된 테이블에서 첫번째 행은 평점이 가장 높은 학생의 결과가 나온다.

 

 

문제6

대학교의 "환경조경학과"가 속한 같은 계열 학과들의 학과 별 전공과목 평점을 파악하기 위한

적절한 SQL 문을 찾아내시오. 
단, 출력헤더는 "계열 학과명", "전공평점"으로 표시되도록 하고,

평점은 소수점 한 자리까지만 반올림하여 표시되도록 한다.

SELECT 
      V.DEPARTMENT_NAME "계열 학과명"
    , ROUND(AVG(G.POINT),1) "전공평점"
FROM (SELECT D.*
        FROM TB_DEPARTMENT D
       WHERE D.CATEGORY = (SELECT D.CATEGORY
                             FROM TB_DEPARTMENT D
                            WHERE D.DEPARTMENT_NAME = '환경조경학과')
        ) V --환경조경학과와 같은 계열의 학과 뷰
  JOIN TB_CLASS C ON(V.DEPARTMENT_NO = C.DEPARTMENT_NO)
  JOIN TB_GRADE G ON (C.CLASS_NO=G.CLASS_NO)
 WHERE C.CLASS_TYPE LIKE '전공%' 
 GROUP BY V.DEPARTMENT_NAME;


-- 한경조경학과 계열 학과들
 SELECT 
        D.*
   FROM TB_DEPARTMENT D
  WHERE D.CATEGORY = (SELECT D.CATEGORY
                        FROM TB_DEPARTMENT D
                        WHERE D.DEPARTMENT_NAME = '환경조경학과'); 

-- 환경조경학과 계열  
SELECT D.CATEGORY
  FROM TB_DEPARTMENT D
 WHERE D.DEPARTMENT_NAME = '환경조경학과' ;