[Oracle] Solution to puzzles

🧩 함수, JOIN, 그룹화를 활용하여 SQL 쿼리짜기

Q1

💁‍ 학생이름과 주소지를 표시하세요. 단, 출력 헤더는 "학생 이름", "주소지"로 하고, 정렬은 이름으로 오름차순 표시하도록 합니다.

🚩 Example Output 588 rows selected

학생 이름	주소지
감현제	서울강서등촌동691-3부영@102-505
강동연	경기도 의정부시 민락동 694 산들마을 대림아파트 404-1404
. . . . . .	. . . . . .
황형철	전남 순천시 생목동 현대ⓐ 106/407 T.061-772-2101
황효종	인천시서구 석남동 564-4번지

SELECT
        STUDENT_NAME "학생 이름"
    ,   STUDENT_ADDRESS 주소지
    FROM TB_STUDENT
    ORDER BY 1;

---

Q2

💁‍ 휴학중인 학생들의 이름과 주민번호를 나이가 적은 순서로 화면에 출력하세요.

🚩 Example Output 91 rows selected

STUDENT_NAME	STUDENT_SSN
릴희권	871222
황효종	871125
. . . . . .	. . . . . .
최정희	791215
조기환	791002

SELECT
		STUDENT_NAME
    ,   STUDENT_SSN
    FROM TB_STUDENT
    WHERE ABSENCE_YN = 'Y'
    ORDER BY MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(STUDENT_SSN, 1, 6))) / 12;

---

Q3

💁‍ 주소지가 강원도나 경기도인 학생들 중 1900 년대 학번을 가진 학생들의 이름과 학번, 주소를 이름의 오름차순으로 화면에 출력하세요. 단, 출력헤더에는 "학생이름","학번", "거주지 주소" 가 출력되도록 합니다.

🚩 Example Output 9 rows selected

학생이름	학번	거주지 주소
김계영	9919024	경기도 용인시 수지구 풍덕천2동 신정마을 임광 305-1703호
박규상	9931111	경기도 성남시 분당구 탑마을 526 경남아파트 710-1302
. . . . . .	. . . . . .	. . . . . .
조기환	9931312	경기도 시흥시 매화동 194-1 동진아파트 1-305

SELECT
        STUDENT_NAME 학생이름
    ,   STUDENT_NO 학번
    ,   STUDENT_ADDRESS "거주지 주소"
    FROM TB_STUDENT
    WHERE STUDENT_NO NOT LIKE 'A%'
    AND STUDENT_ADDRESS LIKE '%경기도%' 
    OR STUDENT_ADDRESS LIKE '%강원도%'
    ORDER BY 1;

---

Q4

💁‍ 현재 법학과 교수 중 가장 나이가 많은 사람부터 이름을 확인할 수 있는 SQL 문장을 작성하세요. (법학과의 '학과코드'는 학과 테이블(TB_DEPARTMENT)을 조회해서 찾기)

🚩 Example Output 4 rows selected

PROFESSOR_NAME	PROFESSOR_SSN
홍남수	540304-1112251
김선희	551030-2159000
임진숙	640125-1143548
이미경	741016-2103506

SELECT
        PROFESSOR_NAME
    ,   PROFESSOR_SSN
    FROM TB_PROFESSOR
    WHERE DEPARTMENT_NO = '005'
    ORDER BY MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(PROFESSOR_SSN, 1, 6))) / 12 DESC;

💡 Another Solution

SELECT 
       PROFESSOR_NAME
     , PROFESSOR_SSN
  FROM TB_PROFESSOR
  JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
 WHERE DEPARTMENT_NAME = '법학과'
 ORDER BY 2;

---

Q5

💁‍ 2004 년 2 학기에 'C3118100' 과목을 수강한 학생들의 학점을 조회하려고 합니다. 학점이 높은 학생부터 표시하고, 학점이 같으면 학번이 낮은 학생부터 표시하는 구문을 작성해보세요.

🚩 Example Output 7 rows selected

STUDENT_NO	POINT
A352017	4.00
A115270	3.50
. . . . . .	. . . . . .
A331076	2.00

SELECT
        STUDENT_NO
    ,   TO_CHAR(POINT, 99.99) POINT
    FROM TB_GRADE
    WHERE TERM_NO = '200402'
    AND CLASS_NO = 'C3118100'
    ORDER BY 2 DESC, 1 DESC;

💡 Another Solution

SELECT 
       STUDENT_NO
     , TO_CHAR(POINT,'9.00') POINT
  FROM TB_GRADE
 WHERE TERM_NO = '200402'
   AND CLASS_NO = 'C3118100'
 ORDER BY POINT DESC, STUDENT_NO;

---

Q6

💁‍ 학생 번호, 학생 이름, 학과 이름을 학생 이름으로 오름차순 정렬하여 출력하는 SQL문을 작성하세요.

🚩 Example Output 588 rows selected

STUDENT_NO	STUDENT_NAME	DEPARTMENT_NAME
A411001	감현제	치의학과
A131004	강동연	디자인학과
. . . . . .	. . . . . .
A411335	황형철	사회학과
A511332	황효종	컴퓨터공학과

SELECT
        STUDENT_NO
    ,   STUDENT_NAME
    ,   DEPARTMENT_NAME
    FROM TB_STUDENT
    JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
    ORDER BY 2;

---

Q7

💁‍ 춘 기술대학교의 과목 이름과 과목의 학과 이름을 출력하는 SQL 문장을 작성하세요.

🚩 Example Output 882 rows selected

CLASS_NAME	DEPARTMENT_NAME
고전시가론특강	국어국문학과
국어어휘론특강	국어국문학과
. . . . . .	. . . . . .
논문지도(공개발표)3	체육학과
체육생리학연구	체육학과

SELECT
        CLASS_NAME
    ,   DEPARTMENT_NAME
    FROM TB_CLASS
    JOIN TB_DEPARTMENT USING (DEPARTMENT_NO);

---

Q8

💁‍ 과목별 교수 이름을 찾으려고 합니다. 과목 이름과 교수 이름을 출력하는 SQL문을 작성하세요.

🚩 Example Output 776 rows selected

CLASS_NAME	PROFESSOR_NAME
도시지리학	박태환
도시지리학	김선필
. . . . . .	. . . . . .
논문지도1	백은정
논문지도2	유용석

SELECT
        CLASS_NAME
    ,   PROFESSOR_NAME
    FROM TB_CLASS
    JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
    JOIN TB_PROFESSOR USING(PROFESSOR_NO);

---

Q9

💁‍ 8 번의 결과 중 ‘인문사회’ 계열에 속한 과목의 교수 이름을 찾으려고 합니다. 이에 해당하는 과목 이름과 교수 이름을 출력하는 SQL 문을 작성하세요.

🚩 Example Output 197 rows selected

CLASS_NAME	PROFESSOR_NAME
인사조직행동 특별연구	강혁
논문지도1	강혁
. . . . . .	. . . . . .
신기술 세미나	황헌중
지식경영과 비지니스 인텔리전스	황헌중

SELECT
        CLASS_NAME
    ,   PROFESSOR_NAME
    FROM TB_CLASS C
    JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
    JOIN TB_PROFESSOR USING(PROFESSOR_NO)
    JOIN TB_DEPARTMENT D ON(C.DEPARTMENT_NO = D.DEPARTMENT_NO)
    WHERE D.CATEGORY = '인문사회'
    ORDER BY 2;

---

Q10

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

🚩 Example Output 7 rows selected

학번	학생 이름	전체 평점
9931310	조기현	4.1
A411116	박현화	3.6
. . . . . .	. . . . . .	. . . . . .
A612052	신광현	4.1

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

---

Q11

💁‍ 학번이 A313047 인 학생이 학교에 나오고 있지 않습니다. 지도 교수에게 내용을 전달하기 위한 학과 이름, 학생 이름과 지도 교수 이름이 필요합니다. 이때 사용한 SQL 문을 작성하세요. 단, 출력헤더는 ‚학과이름‛, ‚학생이름‛, ‚지도교수이름‛으로 출력되도록 합니다.

🚩 Example Output 1 row selected

학과이름	학생이름	지도교수이름
경제학과	손건영	박태환

SELECT
        DEPARTMENT_NAME 학과이름
    ,   STUDENT_NAME 학생이름
    ,   PROFESSOR_NAME 지도교수이름
    FROM TB_DEPARTMENT
    JOIN TB_STUDENT USING(DEPARTMENT_NO)
    JOIN TB_PROFESSOR ON(COACH_PROFESSOR_NO = PROFESSOR_NO)
    WHERE STUDENT_NO = 'A313047';

---

Q12

💁‍ 2007 년도에 '인간관계론' 과목을 수강한 학생을 찾아 학생이름과 수강학기를 표시하는 SQL 문장을 작성하세요.

🚩 Example Output 2 rows selected

학생이름	수강학기
오진형	200701
이정호	200703

SELECT
        STUDENT_NAME 학생이름
    ,   TERM_NO 수강학기
    FROM TB_STUDENT
    JOIN TB_GRADE USING(STUDENT_NO)
    JOIN TB_CLASS USING(CLASS_NO) 
    WHERE TERM_NO LIKE '2007%'
    AND CLASS_NAME = '인간관계론'
    ORDER BY 1;   

💬 Overall Comment

* 처음에 TB_CLASS를 JOIN하려고 했을 때, TB_STUDENT의 DEPARTMENT_NO로 JOIN을 했다가 
  결과값이 올바르게 나오지 않았다. 그래서 TB_GRADE의 CLASS_NO로 JOIN 방식을 바꾼 결과, 
  올바른 출력문이 나왔다. JOIN을 할 때, 막무가내로 공통되는 컬럼끼리 JOIN을 하면 안된다는 
  교훈을 얻게 된 문항이었다. 항상 무엇을 출력해야하는지 생각을 하고 JOIN을 해야한다.

---

Q13

💁‍ 예체능 계열 과목 중 과목 담당교수를 한 명도 배정받지 못한 과목을 찾아 그 과목 이름과 학과 이름을 출력하는 SQL 문장을 작성하세요.

🚩 Example Output 44 rows selected

CLASS_NAME	DEPARTMENT_NAME
무용기능학	공연예술학과
논문지도1	공연예술학과
. . . . . .	. . . . . .
스포츠산업연구 체육학과
해부학실험	체육학과

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
    ORDER BY 2;

---

Q14

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

🚩 Example Output 14 rows selected

학생이름	지도교수
주하나	허문표
이희진	남명길
. . . . . .	. . . . . .
김정현	박준호
최철현	백양임

SELECT
        STUDENT_NAME 학생이름
    ,   NVL(PROFESSOR_NAME, '지도교수 미지정') 지도교수
    FROM TB_STUDENT S
    LEFT JOIN TB_PROFESSOR ON(COACH_PROFESSOR_NO = PROFESSOR_NO)
    JOIN TB_DEPARTMENT D ON(S.DEPARTMENT_NO = D.DEPARTMENT_NO)
    WHERE DEPARTMENT_NAME = '서반아어학과'
    ORDER BY S.ENTRANCE_DATE;

---

Q15

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

🚩 Example Output 19 rows selected

학번	이름	학과 이름	평점
9811251	김충원	건축공학과	4.11111111
9817035	김소라	토목공학과	4
. . . . . .	. . . . . .	. . . . . .	. . . . . .
A517069	박승환	전파공학과	4
A612052	신광현	음악학과	4.1

SELECT
        STUDENT_NO 학번
    ,   STUDENT_NAME 이름
    ,   DEPARTMENT_NAME "학과 이름"
    ,   ROUND(AVG(POINT), 8) 평점
    FROM TB_STUDENT
    JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
    JOIN TB_GRADE USING(STUDENT_NO)
    WHERE ABSENCE_YN = 'N'
    GROUP BY STUDENT_NO, STUDENT_NAME, DEPARTMENT_NAME
    HAVING AVG(POINT) >= 4.0
    ORDER BY 1;

💬 Overall Comment

* GROUP BY와 HAVING을 적절한 때에 사용하는 법이 긴가민가 했었는데, 이 문항을 풀면서 
  제대로 이해하고 적용시킬 수 있게 되었다. GROUP BY를 사용할 시, 조회할 컬럼 중 
  그룹함수를 제외한 나머지들을 모두 GROUP BY절에 나열해줘야한다. 그렇지 않으면 
  'GROUP BY 표현식이 아닙니다.'라는 오류가 발생한다. 그리고 HAVING절은 그룹에 대한 
  조건이므로 WHERE절에 입력해야할 조건과 혼동하지 않아야한다.

---

Q16

💁‍ 환경조경학과 전공과목들의 과목 별 평점을 파악할 수 있는 SQL 문을 작성하세요.

🚩 Example Output 6 rows selected

CLASS_NO	CLASS_NAME	AVG(POINT)
C3016200	전통계승방법론	3.91666666
C3081300	조경계획방법론	3.55
. . . . . .	. . . . . .	. . . . . .
C4477600	조경시학	3.36666666
C5009300	단지계획및설계스튜디오	3.42857142

SELECT
        CLASS_NO
    ,   CLASS_NAME
    ,   TRUNC(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 1;

---

Q17

💁‍ 춘 기술대학교에 다니고 있는 최경희 학생과 같은 과 학생들의 이름과 주소를 출력하는 SQL 문을 작성하세요. (선생님 코드)

🚩 Example Output 17 rows selected

STUDENT_NAME	STUDENT_ADDRESS
김석민	경기도안산시상록구2동664번지투루지오2차@205/601
양선주	경기도안양시만안구박달동107벽산@101-1801
. . . . . .	. . . . . .
기혜미	대전시 유성구 덕진동 한국원자력안전기술원 행정부장 T.042-863-2820
김희훈	인천시 부평구 십정 1동 323- 19호

SELECT
        STUDENT_NAME
    ,   STUDENT_ADDRESS
    FROM TB_STUDENT
    JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
    WHERE DEPARTMENT_NAME = (SELECT
                                    DEPARTMENT_NAME
                                FROM TB_DEPARTMENT
                                JOIN TB_STUDENT USING(DEPARTMENT_NO)
                                WHERE STUDENT_NAME = '최경희'
                                );

💡 Another Solution

SELECT 
       STUDENT_NAME
     , STUDENT_ADDRESS
  FROM TB_STUDENT
 WHERE DEPARTMENT_NO = (SELECT 
                               DEPARTMENT_NO
                          FROM TB_STUDENT
                         WHERE STUDENT_NAME = '최경희');

💬 Overall Comment

* 나는 JOIN을 남용하는 버릇이 있는 것 같다. 더욱 효율적인 쿼리를 짤 수 있도록 데이터베이스를 
  연관관계를 잘 이해하고 확인한 후 쿼리문을 작성해야겠다고 생각하게 된 문항이었다.

---

Q18

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

🚩 Example Output 1 row selected

STUDENT_NO	STUDENT_NAME
9931165	송근우

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;

💡 Another Solution

SELECT 
       STUDENT_NO
     , STUDENT_NAME
  FROM (SELECT 
               STUDENT_NO
             , STUDENT_NAME
             , AVG(POINT)
          FROM TB_GRADE
          JOIN TB_STUDENT USING(STUDENT_NO)
         WHERE DEPARTMENT_NO = (SELECT 
                                       DEPARTMENT_NO
                                  FROM TB_DEPARTMENT
                                 WHERE DEPARTMENT_NAME = '국어국문학과')
      GROUP BY STUDENT_NO, STUDENT_NAME
      ORDER BY AVG(POINT) DESC)
 WHERE ROWNUM <= 1;

💬 Overall Comment

* 평점이 가장 높은 학생만 출력하는 방법이 어떤게 있을까 고민하던 중, 정렬을 오름차순으로 
  한 쿼리를 서브쿼리(인라인 뷰)로 가져서 ROWNUM을 1만 출력하는 방식으로 풀어내면 되겠다고 
  생각했다. 아직 서브쿼리를 유연하게 사용하기 어렵지만 이런 식으로 꾸준히 사용하는 법을 
  터득한다면 실력은 나날히 늘어날 것이다 !

---

Q19

💁‍ 춘 기술대학교의 "환경조경학과"가 속한 같은 계열 학과들의 학과 별 전공과목 평점을 파악하기 위한 적절한 SQL 문을 찾아내세요. 단, 출력헤더는 "계열 학과명", "전공평점"으로 표시되도록 하고, 평점은 소수점 한 자리까지만 반올림하여 표시되도록 합니다.

🚩 Example Output 20 rows selected

계열 학과명	전공 평점
간호학과	3.3
물리학과	3.4
. . . . . .	. . . . . .
환경응용과학과	3.5
환경조경학과	3.5

SELECT
        DEPARTMENT_NAME "계열 학과명"
    ,   ROUND(AVG(POINT), 1) "전공 평점"
    FROM TB_DEPARTMENT
    JOIN TB_STUDENT USING(DEPARTMENT_NO)
    JOIN TB_GRADE USING(STUDENT_NO)
    JOIN TB_CLASS USING(DEPARTMENT_NO)
    WHERE CATEGORY = (SELECT -- 환경조경학과가 속한 계열 찾는 서브 쿼리
                            CATEGORY
                        FROM TB_DEPARTMENT
                        WHERE DEPARTMENT_NAME = '환경조경학과'
                        )
    AND CLASS_TYPE LIKE '전공%'
    GROUP BY DEPARTMENT_NAME
    ORDER BY 1;

---