[Oracle / SQLdeveloper] 03_SELECT(OPTION) 예제 (11번~19번)

▼ 예제 파일과 테이블 스크립트

춘대학시스템_워크북.pdf

0.31MB

춘대학시스템_스크립트(UTF-8).txt

0.94MB

SELECT 기본 문법 및 연산자
함수

[춘대학시스템_워크북] 테이블 자료를 통해 예제를 풀어보았다.

※ 자료가 오래되어 문제 속 보기와는 다르다. 따라서 총 갯수가 맞는지 아닌지를 보고 내 답이 정답인지 파악 할 것!

▼ 정답 확인 ▽

-- 11. 학번이 A313047인 학생이 학교에 나오고 있지 않다.
-- 지도 교수에게 내용을 전달하기 위한 학과 이름, 학생 이름과 지도 교수 이름이 필요하다.
-- 이때 사용할 SQL 문을 작성하시오.
-- 단, 출력헤더는 "학과이름", "학생이름", "지도교수이름"으로 출력되도록 한다.
SELECT
	   DEPARTMENT_NAME "학과이름"
	 , STUDENT_NAME "학생이름"
	 , PROFESSOR_NAME "지도교수이름"
  FROM TB_STUDENT
  JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
  JOIN TB_PROFESSOR ON (COACH_PROFESSOR_NO = PROFESSOR_NO)
 WHERE STUDENT_NO = 'A313047';

▼ 정답 확인 ▽

-- 12. 2007년에 '인간관계론' 과목을 수강한 학생을 찾아 학생이름과 수강학기를 표시하는 SQL 문장을 작성하시오.
SELECT
	   S.STUDENT_NAME
	 , G.TERM_NO TERM_NAME
  FROM TB_STUDENT S
  JOIN TB_GRADE G ON (G.STUDENT_NO = S.STUDENT_NO)
  JOIN TB_CLASS USING (CLASS_NO)
 WHERE G.TERM_NO LIKE '2007%'
   AND CLASS_NAME = '인간관계론';

▼ 정답 확인 ▽

-- 13. 예체능 계열 과목 중 과목 담당교수를 한 명도 배정받지 못한 과목을 찾아
-- 그 과목 이름과 학과 이름을 출력하는 SQL 문장을 작성하시오.
SELECT
	   CLASS_NAME
	 , DEPARTMENT_NAME
  FROM TB_CLASS
  JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
  LEFT JOIN TB_CLASS_PROFESSOR USING (CLASS_NO)
 WHERE CATEGORY = '예체능' AND PROFESSOR_NO IS NULL;

▼ 정답 확인 ▽

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

▼ 정답 확인 ▽

-- 15. 휴학생이 아닌 학생 중 평점이 4.0 이상인 학생을 찾아
-- 그 학생의 학번, 이름, 학과이름, 평점을 출력하는 SQL 문을 작성하시오.
SELECT
	   S.STUDENT_NO 학번
	 , S.STUDENT_NAME 이름
	 , D.DEPARTMENT_NAME "학과 이름"
	 , ROUND(AVG(POINT),8) 평점
  FROM TB_STUDENT S 
  JOIN TB_DEPARTMENT D ON (D.DEPARTMENT_NO = S.DEPARTMENT_NO)
  JOIN TB_GRADE G ON (G.STUDENT_NO = S.STUDENT_NO)
 WHERE ABSENCE_YN = 'N'
 GROUP BY S.STUDENT_NO, S.STUDENT_NAME, D.DEPARTMENT_NAME
 HAVING ROUND(AVG(POINT), 8) >= 4.0
 ORDER BY 학번;

▼ 정답 확인 ▽

-- 16. 환경조경학과 전공과목들의 과목별 평점을 파악할 수 있는 SQL 문을 작성하시오.
SELECT
	   CLASS_NO
	 , CLASS_NAME
	 , ROUND(AVG(POINT), 8) "AVG(POINT)"
  FROM TB_CLASS
  JOIN TB_GRADE USING (CLASS_NO)
  JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
 WHERE DEPARTMENT_NAME = '환경조경학과' AND CLASS_TYPE LIKE '전공%'
 GROUP BY CLASS_NO, CLASS_NAME
 ORDER BY CLASS_NO;

▼ 정답 확인 ▽

-- 17. 춘 기술대학교에 다니고 있는 최경희 학생과 같은 과 학생들의 이름과 주소를 출력하는 SQL문을 작성하시오.
SELECT
	   STUDENT_NAME
	 , STUDENT_ADDRESS
  FROM TB_STUDENT
 WHERE DEPARTMENT_NO = (SELECT
							   DEPARTMENT_NO
						  FROM TB_STUDENT
						 WHERE STUDENT_NAME = '최경희')
 ORDER BY STUDENT_NAME DESC;

▼ 정답 확인 ▽

-- 18. 국어국문학과에서 총 평점이 가장 높은 학생의 이름과 학번을 표시하는 SQL문을 작성하시오.
SELECT
       STUDENT_NO
	 , STUDENT_NAME
  FROM (SELECT
			   STUDENT_NO
			 , STUDENT_NAME
		  FROM TB_STUDENT
		  JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
          JOIN TB_GRADE USING (STUDENT_NO)
		 WHERE DEPARTMENT_NAME = '국어국문학과'
         GROUP BY STUDENT_NO, STUDENT_NAME
		 ORDER BY AVG(POINT) DESC)
 WHERE ROWNUM = 1;

▼ 정답 확인 ▽

-- 19. 춘 기술대학교의 "환경조경학과"가 속한 같은 계열 학과들의 
-- 학과 별 전공과목 평점을 파악하기 위한 적절한 SQL 문을 찾아내시오.
-- 단, 출력헤더는 "계열 학과명", "전공평점"으로 표시되도록 하고, 평점은 소수점 한 자리까지만 반올림하여 표시되도록 한다.
SELECT
	   DEPARTMENT_NAME "계열 학과명"
	 , ROUND(AVG(POINT), 1)	"전공평점"
  FROM TB_DEPARTMENT
  JOIN TB_CLASS USING(DEPARTMENT_NO)
  JOIN TB_GRADE USING(CLASS_NO)
 WHERE CATEGORY = (SELECT
						  CATEGORY
					 FROM TB_DEPARTMENT
					WHERE DEPARTMENT_NAME = '환경조경학과'
                  )
   AND CLASS_TYPE LIKE '전공%'
 GROUP BY DEPARTMENT_NAME, CATEGORY
 ORDER BY "전공평점" DESC;