[Oracle/수업 과제 practice] SELECT(Additional - Option)

-- SQL SELECT(Additional - Option)
-- 춘대학시스템
✅ 답안과 비교하여 스스로 SQL 구문 개선점 짚어보기 완료(2022.01.25)

 

SELECT(Option) 1. ✅

-- SQL SELECT(Additional - Option)
-- 1.  학생 이름과 주소지를 표시
-- 단, 출력 헤더는 "학생 이름", "주소지"로 하고, 정렬은 이름으로 오름차순 표시한다.
SELECT
       STUDENT_NAME "학생 이름"
     , STUDENT_ADDRESS "주소지"
  FROM TB_STUDENT
 ORDER BY 1 ASC;

 

SELECT(Option) 2. ✅

-- 2. 휴학 중인 학생들의 이름과 주민번호를
-- 나이가 적은 순서로 화면에 출력
SELECT
       STUDENT_NAME 이름
     , STUDENT_SSN 주민번호
  FROM TB_STUDENT
 WHERE ABSENCE_YN = 'Y'
 ORDER BY 2 DESC;

 

SELECT(Option) 3. ✅

1990년대 학번이라 함은 9로 시작하는 학번을 말한다. 따라서 답안에서는 조건을 다음과 같이 LIKE 비교연산자를 활용해 작성했다.

 WHERE STUDENT_NO LIKE '9%'
    AND (STUDENT_ADDRESS LIKE '경기도%'
      OR STUDENT_ADDRESS LIKE '강원도%')

-- 3. 주소지가 강원도나 경기도인 학생들 중
-- 1990년대 학번을 가진 학생들의 이름, 학번, 주소를
-- 이름의 오름차순으로 화면에 출력
-- 단, 출력헤더는 "학생이름", "학번", "거주지 주소"가 출력되도록 한다.
SELECT
       STUDENT_NAME 학생이름
     , STUDENT_NO 학번
     , STUDENT_ADDRESS "거주지 주소"
  FROM TB_STUDENT
 WHERE SUBSTR(STUDENT_ADDRESS, 1, 3) IN ('강원도', '경기도')
   AND ENTRANCE_DATE BETWEEN '90/01/01' AND '99/12/31' --숫자로 쓰면 자동형변환 X
 ORDER BY 1;

 

SELECT(Option) 4. [ANSI, 오라클] ✅

TB_DEPARTMENT 테이블로부터 조회하라는 의미가 JOIN을 활용하라는 뜻이었다.

-- 4. 현재 법학과 교수 중 가장 나이가 많은 사람부터 이름 조회
-- 법학과의 '학과코드'는 학과 테이블(TB_DEPARTMENT) 조회해서 찾아라.
-- ANSI
SELECT
       P.PROFESSOR_NAME
     , P.PROFESSOR_SSN
  FROM TB_PROFESSOR P
  JOIN TB_DEPARTMENT D ON (P.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '법학과'
 ORDER BY 2;
-- ORACLE
SELECT
       P.PROFESSOR_NAME
     , P.PROFESSOR_SSN
  FROM TB_PROFESSOR P
     , TB_DEPARTMENT D
 WHERE P.DEPARTMENT_NO = D.DEPARTMENT_NO
   AND D.DEPARTMENT_NAME = '법학과'
 ORDER BY 2;
-- 최초 작성한 답
SELECT
       DEPARTMENT_NO
  FROM TB_DEPARTMENT
 WHERE DEPARTMENT_NAME = '법학과';
SELECT
       PROFESSOR_NAME
     , PROFESSOR_SSN
  FROM TB_PROFESSOR
 WHERE DEPARTMENT_NO = '005'
 ORDER BY 2;

 

SELECT(Option) 5. ❎✅

지문에 주어진 컬럼 헤더를 잘 살피자. POINT였다.

학번순으로 출력되고 있어 직접 적진 않았는데, 답안에서는 ORDER BY절을 다음과 같이 명시해서 썼다: ORDER BY POINT DESC, STUDENT_NO;

-- 5. 2004년 2학기에 'C3118100' 과목을 수강한 학생들의 학점 조회
-- 학점이 높은 학생부터 표시하고, 학점이 같으면 학번이 낮은 학생부터 표시
SELECT
       STUDENT_NO
     , TO_CHAR(POINT, '9.99') POINT
  FROM TB_GRADE
 WHERE TERM_NO = '200402'
   AND CLASS_NO = 'C3118100'
 ORDER BY 2 DESC;

 

SELECT(Option) 6. [ANSI, 오라클] ✅

--정렬 확인 필요했으나, 지문과 다를 수 있다고 한다.

-- 6. 학생 번호, 학생 이름, 학과 이름을 학생 이름의 오름차순으로 정렬
-- ANSI
SELECT
       S.STUDENT_NO
     , S.STUDENT_NAME
     , D.DEPARTMENT_NAME
  FROM TB_STUDENT S
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 ORDER BY S.STUDENT_NAME;
-- ORACLE
SELECT
       S.STUDENT_NO
     , S.STUDENT_NAME
     , D.DEPARTMENT_NAME
  FROM TB_STUDENT S
     , TB_DEPARTMENT D
 WHERE S.DEPARTMENT_NO = D.DEPARTMENT_NO
 ORDER BY S.STUDENT_NAME;
-- 최초 작성한 답
SELECT
       S.STUDENT_NO
     , S.STUDENT_NAME
     , D.DEPARTMENT_NAME
  FROM TB_STUDENT S
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 ORDER BY 2;

 

SELECT(Option) 7. [ANSI, 오라클] ✅

--정렬 확인 필요했으나, 지문과 다를 수 있다고 한다.

-- 7. 춘 기술대학교의 과목 이름과 과목의 학과 이름을 출력
-- ANSI
SELECT
       C.CLASS_NAME
     , D.DEPARTMENT_NAME
  FROM TB_CLASS C
  JOIN TB_DEPARTMENT D USING (DEPARTMENT_NO);
-- ORACLE
SELECT
       C.CLASS_NAME
     , D.DEPARTMENT_NAME
  FROM TB_CLASS C
     , TB_DEPARTMENT D
  WHERE C.DEPARTMENT_NO = D.DEPARTMENT_NO;
-- 최초 작성한 답
SELECT
       C.CLASS_NAME
     , D.DEPARTMENT_NAME
  FROM TB_CLASS C
  JOIN TB_DEPARTMENT D ON (C.DEPARTMENT_NO = D.DEPARTMENT_NO)
  ORDER BY C.DEPARTMENT_NO;

 

SELECT(Option) 8. [ANSI, 오라클] ✅

--정렬 확인 필요했으나, 지문과 다를 수 있다고 한다.

-- 8. 과목별 교수 이름 조회
-- 과목 이름과 교수 이름을 출력
-- ANSI
SELECT
       C.CLASS_NAME
     , P.PROFESSOR_NAME
  FROM TB_CLASS C
  JOIN TB_CLASS_PROFESSOR CP ON (C.CLASS_NO = CP.CLASS_NO)
  JOIN TB_PROFESSOR P ON (CP.PROFESSOR_NO = P.PROFESSOR_NO)
  ORDER BY C.DEPARTMENT_NO, P.PROFESSOR_NO;
-- ORACLE
SELECT
       C.CLASS_NAME
     , P.PROFESSOR_NAME
  FROM TB_CLASS C
     , TB_CLASS_PROFESSOR CP
     , TB_PROFESSOR P
 WHERE C.CLASS_NO = CP.CLASS_NO
   AND CP.PROFESSOR_NO = P.PROFESSOR_NO
 ORDER BY C.DEPARTMENT_NO, P.PROFESSOR_NO;
-- 최초 작성한 답
SELECT
       C.CLASS_NAME
     , P.PROFESSOR_NAME
  FROM TB_CLASS C
  JOIN TB_CLASS_PROFESSOR CP ON (C.CLASS_NO = CP.CLASS_NO)
  JOIN TB_PROFESSOR P ON (CP.PROFESSOR_NO = P.PROFESSOR_NO)
 ORDER BY C.DEPARTMENT_NO ASC, P.PROFESSOR_NO ASC;

답안에서는 JOIN 통해 간단히 답을 구했다. 쓴 게 아까워서 같이 올려놓는다...

-- FROM절 서브쿼리 인라인뷰 사용
SELECT
       V.CLASS_NAME
     , V.PROFESSOR_NAME
  FROM (SELECT CP.CLASS_NO
             , CP.PROFESSOR_NO
             , C.CLASS_NAME
             , C.DEPARTMENT_NO
             , P.PROFESSOR_NAME
          FROM TB_CLASS_PROFESSOR CP
          JOIN TB_CLASS C ON (CP.CLASS_NO = C.CLASS_NO)
          JOIN TB_PROFESSOR P ON (CP.PROFESSOR_NO = P.PROFESSOR_NO)
        ) V
 ORDER BY V.DEPARTMENT_NO ASC, V.PROFESSOR_NO ASC;

 

SELECT(Option) 9. [ANSI, 오라클] ✅

--정렬 확인 필요했으나, 지문과 다를 수 있다고 한다.

-- 9. 8번 결과 중 '인문사회' 계열에 속한 과목의 교수 이름 조회
-- ANSI
SELECT
       C.CLASS_NAME
     , P.PROFESSOR_NAME
  FROM TB_CLASS C
  JOIN TB_CLASS_PROFESSOR CP ON (C.CLASS_NO = CP.CLASS_NO)
  JOIN TB_PROFESSOR P ON (CP.PROFESSOR_NO = P.PROFESSOR_NO)
  JOIN TB_DEPARTMENT D ON (C.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.CATEGORY = '인문사회'
 ORDER BY C.DEPARTMENT_NO ASC, P.PROFESSOR_NO ASC;
-- ORACLE
SELECT
       C.CLASS_NAME
     , P.PROFESSOR_NAME
  FROM TB_CLASS C
     , TB_CLASS_PROFESSOR CP
     , TB_PROFESSOR P
     , TB_DEPARTMENT D
 WHERE C.CLASS_NO = CP.CLASS_NO
   AND CP.PROFESSOR_NO = P.PROFESSOR_NO
   AND C.DEPARTMENT_NO = D.DEPARTMENT_NO
   AND D.CATEGORY = '인문사회'
 ORDER BY C.DEPARTMENT_NO ASC, P.PROFESSOR_NO ASC;
-- 최초 작성한 답
SELECT
       C.CLASS_NAME
     , P.PROFESSOR_NAME
  FROM TB_CLASS C
  JOIN TB_CLASS_PROFESSOR CP ON (C.CLASS_NO = CP.CLASS_NO)
  JOIN TB_PROFESSOR P ON (CP.PROFESSOR_NO = P.PROFESSOR_NO)
  JOIN TB_DEPARTMENT D ON (C.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.CATEGORY = '인문사회'
 ORDER BY C.DEPARTMENT_NO ASC, P.PROFESSOR_NO ASC;

 

SELECT(Option) 10. [ANSI, 오라클] ✅

-- 10. '음악학과' 학생들의 평점 조회
-- "학번", "학생 이름", "전체 평점"을 출력
-- 단, 평점은 소수점 1자리까지만 반올림하여 표시한다.
-- ANSI
SELECT
       S.STUDENT_NO 학번
     , S.STUDENT_NAME "학생 이름"
     , ROUND(AVG(G.POINT), 1) "전체 평점"
  FROM TB_STUDENT S
  JOIN TB_GRADE G ON (S.STUDENT_NO = G.STUDENT_NO)
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '음악학과'
 GROUP BY S.STUDENT_NO, S.STUDENT_NAME
 ORDER BY 1;
-- ORACLE
SELECT
       S.STUDENT_NO 학번
     , S.STUDENT_NAME "학생 이름"
     , ROUND(AVG(G.POINT), 1) "전체 평점"
  FROM TB_STUDENT S
     , TB_GRADE G
     , TB_DEPARTMENT D
 WHERE S.STUDENT_NO = G.STUDENT_NO
   AND S.DEPARTMENT_NO = D.DEPARTMENT_NO
   AND D.DEPARTMENT_NAME = '음악학과'
 GROUP BY S.STUDENT_NO, S.STUDENT_NAME
 ORDER BY 1;
-- 최초 작성한 답
SELECT
       S.STUDENT_NO 학번
     , S.STUDENT_NAME "학생 이름"
     , ROUND(AVG(G.POINT), 1) "전체 평점"
  FROM TB_STUDENT S
  JOIN TB_GRADE G ON (S.STUDENT_NO = G.STUDENT_NO)
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '음악학과'
 GROUP BY S.STUDENT_NO, S.STUDENT_NAME
 ORDER BY 1;

 

SELECT(Option) 11. [ANSI, 오라클] ✅

-- 11. 학번이 A313047인 학생이 학교에 나오고 있지 않다.
-- 지도 교수에게 내용을 전달하기 위해 학과 이름, 학생 이름, 지도 교수 이름 조회
-- 단, 출력헤더는 "학과이름", "학생이름", "지도교수이름"으로 출력되도록 한다.
-- ANSI
SELECT
       D.DEPARTMENT_NAME 학과이름
     , 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 S.STUDENT_NO = 'A313047';
-- ORACLE
SELECT
       D.DEPARTMENT_NAME 학과이름
     , S.STUDENT_NAME 학생이름
     , P.PROFESSOR_NAME 지도교수이름
  FROM TB_STUDENT S
     , TB_DEPARTMENT D
     , TB_PROFESSOR P
 WHERE S.DEPARTMENT_NO = D.DEPARTMENT_NO
   AND S.COACH_PROFESSOR_NO = P.PROFESSOR_NO
   AND S.STUDENT_NO = 'A313047';
-- 최초 작성한 답
SELECT
       D.DEPARTMENT_NAME 학과이름
     , S.STUDENT_NAME 학생이름
     , P.PROFESSOR_NAME 지도교수이름
  FROM TB_DEPARTMENT D
  JOIN TB_STUDENT S ON (D.DEPARTMENT_NO = S.DEPARTMENT_NO)
  JOIN TB_PROFESSOR P ON (S.COACH_PROFESSOR_NO = P.PROFESSOR_NO)
 WHERE S.STUDENT_NO = 'A313047';

 

SELECT(Option) 12. [ANSI, 오라클] ✅

-- 12. 2007년에 '인간관계론' 과목을 수강한 학생을 조회
-- 학생이름과 수강학기이름 표시
-- ANSI
SELECT
       S.STUDENT_NAME
     , G.TERM_NO "TERM_NAME"
  FROM TB_STUDENT S
  JOIN TB_GRADE G ON (S.STUDENT_NO = G.STUDENT_NO)
  JOIN TB_CLASS C ON (G.CLASS_NO = C.CLASS_NO)
 WHERE G.TERM_NO LIKE '2007%'
   AND C.CLASS_NAME = '인간관계론';
-- ORACLE
SELECT
       S.STUDENT_NAME
     , G.TERM_NO "TERM_NAME"
  FROM TB_STUDENT S
     , TB_GRADE G
     , TB_CLASS C
 WHERE S.STUDENT_NO = G.STUDENT_NO
   AND G.CLASS_NO = C.CLASS_NO
   AND G.TERM_NO LIKE '2007%'
   AND C.CLASS_NAME = '인간관계론';
-- 최초 작성한 답
SELECT
       S.STUDENT_NAME
     , G.TERM_NO "TERM_NAME"
  FROM TB_STUDENT S
  JOIN TB_GRADE G ON (S.STUDENT_NO = G.STUDENT_NO)
  JOIN TB_CLASS C ON (G.CLASS_NO = C.CLASS_NO)
 WHERE G.TERM_NO LIKE '2007%'
   AND C.CLASS_NAME = '인간관계론';

 

SELECT(Option) 13. [ANSI, 오라클] ✅

--정렬 확인 필요했으나, 지문과 다를 수 있다고 한다.

-- 13. 예체능 계열 과목 중 담당교수를 한 명도 배정 받지 못한 과목 조회
-- 그 과목 이름과 학과 이름 출력
-- ANSI
SELECT
       C.CLASS_NAME
     , D.DEPARTMENT_NAME
  FROM TB_CLASS C
  LEFT JOIN TB_CLASS_PROFESSOR CP ON (C.CLASS_NO = CP.CLASS_NO)
  JOIN TB_DEPARTMENT D ON (C.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE CP.PROFESSOR_NO IS NULL
   AND D.CATEGORY = '예체능';
-- ORACLE
SELECT
       C.CLASS_NAME
     , D.DEPARTMENT_NAME
  FROM TB_CLASS C
     , TB_CLASS_PROFESSOR CP
     , TB_DEPARTMENT D
 WHERE C.CLASS_NO = CP.CLASS_NO(+)
   AND C.DEPARTMENT_NO = D.DEPARTMENT_NO
   AND CP.PROFESSOR_NO IS NULL
   AND D.CATEGORY = '예체능';
-- 최초 작성한 답
SELECT
       C.CLASS_NAME
     , D.DEPARTMENT_NAME
  FROM TB_CLASS C
  JOIN TB_DEPARTMENT D ON (C.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE C.CLASS_NO NOT IN (SELECT CP.CLASS_NO
                            FROM TB_CLASS_PROFESSOR CP
                          )
   AND D.CATEGORY = '예체능';

 

SELECT(Option) 14. [ANSI, 오라클] ✅

답안에서는 고학번을 입학 날짜 기준으로 봤다: ORDER BY S.ENTRANCE_DATE;

-- 14. 춘 기술대학교 서반아어학과 학생들의 지도 교수 게시 위해
-- 학생 이름과 지도 교수 이름을 찾고,
-- 만일 지도 교수가 없는 학생일 경우 "지도교수 미지정"으로 표시
-- 단, 출력헤더는 "학생이름", "지도교수"로 표시하며 고학번 학생이 먼저 표시되도록 한다.
-- ANSI
SELECT
       S.STUDENT_NAME 학생이름
     , NVL(P.PROFESSOR_NAME, '지도교수 미지정') 지도교수
  FROM TB_STUDENT S
  LEFT JOIN TB_PROFESSOR P ON (S.COACH_PROFESSOR_NO = P.PROFESSOR_NO)
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '서반아어학과'
 ORDER BY S.ENTRANCE_DATE;
-- ORACLE
SELECT
       S.STUDENT_NAME 학생이름
     , NVL(P.PROFESSOR_NAME, '지도교수 미지정') 지도교수
  FROM TB_STUDENT S
     , TB_PROFESSOR P
     , TB_DEPARTMENT D
 WHERE S.COACH_PROFESSOR_NO = P.PROFESSOR_NO(+)
   AND S.DEPARTMENT_NO = D.DEPARTMENT_NO
   AND D.DEPARTMENT_NAME = '서반아어학과'
 ORDER BY S.ENTRANCE_DATE;
-- 최초 작성한 답
SELECT
       S.STUDENT_NAME 학생이름
     , NVL(P.PROFESSOR_NAME, '지도교수 미미정') 지도교수
  FROM TB_STUDENT S
  LEFT JOIN TB_PROFESSOR P ON (S.COACH_PROFESSOR_NO = P.PROFESSOR_NO)
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '서반아어학과'
 ORDER BY S.STUDENT_NO;

 

SELECT(Option) 15. [ANSI, 오라클] ✅

-- 15. 휴학생이 아닌 학생 중 평점이 4.0 이상인 학생을 찾아
-- 그 학생의 학번, 이름, 학과 이름, 평점을 출력
-- ANSI
SELECT
       S.STUDENT_NO 학번
     , S.STUDENT_NAME 이름
     , D.DEPARTMENT_NAME "학과 이름"
     , TRUNC(AVG(G.POINT), 8) 평점
  FROM TB_STUDENT S
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
  JOIN TB_GRADE G ON (S.STUDENT_NO = G.STUDENT_NO)
 WHERE S.ABSENCE_YN = 'N'
 GROUP BY S.STUDENT_NO, S.STUDENT_NAME, D.DEPARTMENT_NAME
HAVING AVG(G.POINT) >= 4.0
 ORDER BY 1;
-- ORACLE
SELECT
       S.STUDENT_NO 학번
     , S.STUDENT_NAME 이름
     , D.DEPARTMENT_NAME "학과 이름"
     , TRUNC(AVG(G.POINT), 8) 평점
  FROM TB_STUDENT S
     , TB_DEPARTMENT D
     , TB_GRADE G
 WHERE S.DEPARTMENT_NO = D.DEPARTMENT_NO
   AND S.STUDENT_NO = G.STUDENT_NO
   AND S.ABSENCE_YN = 'N'
 GROUP BY S.STUDENT_NO, S.STUDENT_NAME, D.DEPARTMENT_NAME
HAVING AVG(G.POINT) >= 4.0
 ORDER BY 1;
-- 최초 작성한 답
SELECT
       S.STUDENT_NO 학번
     , S.STUDENT_NAME 이름
     , D.DEPARTMENT_NAME "학과 이름"
     , TRUNC(AVG(G.POINT), 8) 평점
  FROM TB_STUDENT S
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
  JOIN TB_GRADE G ON (S.STUDENT_NO = G.STUDENT_NO)
 WHERE S.ABSENCE_YN = 'N'
 GROUP BY S.STUDENT_NO, S.STUDENT_NAME, D.DEPARTMENT_NAME
HAVING AVG(G.POINT) >= 4.0
 ORDER BY 1;

 

SELECT(Option) 16. [ANSI, 오라클] ✅

-- 16. 환경조경학과 전공과목들의 과목별 평점 조회
-- ANSI
SELECT
       C.CLASS_NO
     , C.CLASS_NAME
     , TRUNC(AVG(G.POINT), 8) "AVG(POINT)"
  FROM TB_CLASS C
  JOIN TB_GRADE G ON (C.CLASS_NO = G.CLASS_NO)
  JOIN TB_DEPARTMENT D ON (C.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '환경조경학과'
   AND C.CLASS_TYPE LIKE '전공%'
 GROUP BY C.CLASS_NO, C.CLASS_NAME
 ORDER BY 1;
-- ORACLE
SELECT
       C.CLASS_NO
     , C.CLASS_NAME
     , TRUNC(AVG(G.POINT), 8) "AVG(POINT)"
  FROM TB_CLASS C
     , TB_GRADE G
     , TB_DEPARTMENT D
 WHERE C.CLASS_NO = G.CLASS_NO
   AND C.DEPARTMENT_NO = D.DEPARTMENT_NO
   AND D.DEPARTMENT_NAME = '환경조경학과'
   AND C.CLASS_TYPE LIKE '전공%'
 GROUP BY C.CLASS_NO, C.CLASS_NAME
 ORDER BY 1;
-- 최초 작성한 답
SELECT
       C.CLASS_NO
     , C.CLASS_NAME
     , TRUNC(AVG(G.POINT), 8) "AVG(POINT)"
  FROM TB_CLASS C
  JOIN TB_GRADE G ON (C.CLASS_NO = G.CLASS_NO)
  JOIN TB_DEPARTMENT D ON (C.DEPARTMENT_NO = D.DEPARTMENT_NO)
 WHERE D.DEPARTMENT_NAME = '환경조경학과'
   AND C.CLASS_TYPE LIKE '전공%'
 GROUP BY C.CLASS_NO, C.CLASS_NAME
 ORDER BY 1;

 

SELECT(Option) 17. ✅

--정렬 확인 필요했으나, 지문과 다를 수 있다고 한다.

-- 17. 춘 기술대학교에 재학 중인 최경희 학생과
-- 같은 과 학생들의 이름과 주소 출력
SELECT
       S.STUDENT_NAME
     , S.STUDENT_ADDRESS
  FROM TB_STUDENT S
 WHERE S.DEPARTMENT_NO = (SELECT S2.DEPARTMENT_NO
                            FROM TB_STUDENT S2
                           WHERE S2.STUDENT_NAME = '최경희');

 

SELECT(Option) 18. 

답안에서는 FROM절에 서브쿼리, 즉 인라인뷰를 사용해 ROWNUM을 썼다: WHERE ROWNUM <= 1;

또, 인라인뷰 안에서 WHERE 조건으로 단일행 서브쿼리를 한 번 더 활용하기도 했다.

-- 18. 국어국문학과에서 총 평점이 가장 높은 학생의 이름과 학번 조회
SELECT
       STUDENT_NO
     , STUDENT_NAME
  FROM (SELECT S.STUDENT_NO
             , S.STUDENT_NAME
             , AVG(G.POINT)
          FROM TB_STUDENT S
          JOIN TB_GRADE G ON (S.STUDENT_NO = G.STUDENT_NO)
          JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
         WHERE D.DEPARTMENT_NAME = '국어국문학과'
         GROUP BY S.STUDENT_NO, S.STUDENT_NAME
         ORDER BY AVG(G.POINT) DESC
         )
 WHERE ROWNUM = 1;

-- 최초 작성한 답(서브쿼리 HAVING절 사용)
-- 18. 국어국문학과에서 총 평점이 가장 높은 학생의 이름과 학번 조회
SELECT
       S.STUDENT_NO
     , S.STUDENT_NAME
  FROM TB_STUDENT S
  JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
  JOIN TB_GRADE G ON (S.STUDENT_NO = G.STUDENT_NO)
 WHERE D.DEPARTMENT_NAME = '국어국문학과'
 GROUP BY S.STUDENT_NO, S.STUDENT_NAME
HAVING AVG(G.POINT) = (SELECT MAX(AVG(G2.POINT))
                         FROM TB_GRADE G2
                         JOIN TB_STUDENT S2 ON (G2.STUDENT_NO = S2.STUDENT_NO)
                         JOIN TB_DEPARTMENT D2 ON (S2.DEPARTMENT_NO = D2.DEPARTMENT_NO)
                        WHERE D2.DEPARTMENT_NAME = '국어국문학과'
                        GROUP BY S2.STUDENT_NO
                       );

 

SELECT(Option) 19. [ANSI, 오라클] ✅

'전공'으로 한정하지 않으면 평점 수치가 달라진다. 지문을 제대로 파악하고 적용하자.

-- 19. 춘 기술대학교의 "환경조경학과"가 속한 같은 계열 학과들의
-- 학과별 전공과목 평점 조회
-- 단, 출력 헤더는 "계열 학과명", "전공평점"으로 표시되도록 하고
-- 평점은 소수점 한 자리까지만 반올림하여 표시한다.
-- ANSI
SELECT
       D.DEPARTMENT_NAME "계절 학과명"
     , ROUND(AVG(G.POINT), 1) "전공평점"
  FROM TB_DEPARTMENT D
  JOIN TB_CLASS C ON (D.DEPARTMENT_NO = C.DEPARTMENT_NO)
  JOIN TB_GRADE G ON (C.CLASS_NO = G.CLASS_NO)
 WHERE D.CATEGORY = (SELECT D2.CATEGORY
                       FROM TB_DEPARTMENT D2
                      WHERE D2.DEPARTMENT_NAME = '환경조경학과')
   AND C.CLASS_TYPE LIKE '전공%'
 GROUP BY D.DEPARTMENT_NAME
 ORDER BY 1;
-- ORACLE
SELECT
       D.DEPARTMENT_NAME "계절 학과명"
     , ROUND(AVG(G.POINT), 1) "전공평점"
  FROM TB_DEPARTMENT D
     , TB_CLASS C
     , TB_GRADE G
 WHERE D.DEPARTMENT_NO = C.DEPARTMENT_NO
   AND C.CLASS_NO = G.CLASS_NO
   AND D.CATEGORY = (SELECT D2.CATEGORY
                       FROM TB_DEPARTMENT D2
                      WHERE D2.DEPARTMENT_NAME = '환경조경학과')
   AND C.CLASS_TYPE LIKE '전공%'
 GROUP BY D.DEPARTMENT_NAME
 ORDER BY 1;
-- 최초 작성한 답
SELECT
       D.DEPARTMENT_NAME "계열 학과명"
     , ROUND(AVG(POINT), 1) "전공평점"
  FROM TB_DEPARTMENT D
  JOIN TB_CLASS C ON (D.DEPARTMENT_NO = C.DEPARTMENT_NO)
  JOIN TB_GRADE G ON (C.CLASS_NO = G.CLASS_NO)
 WHERE D.CATEGORY = (SELECT D2.CATEGORY
                       FROM TB_DEPARTMENT D2
                      WHERE D2.DEPARTMENT_NAME = '환경조경학과')
   AND C.CLASS_TYPE LIKE '전공%'
 GROUP BY D.DEPARTMENT_NAME
 ORDER BY 1;