[Oracle] Solution to puzzles

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

Q1

💁‍♀️ 영어영문학과(학과코드 002) 학생들의 학번과 이름, 입학 년도를 입학 년도가 빠른 순으로 표시하는 SQL 문장을 작성하세요.
( 단, 헤더는 "학번", "이름", "입학년도"로 표시)

🚩 Example Output 3 rows selected

학번	이름	입학년도
9973003	김용근	99/03/01
A473015	배용원	04/03/01
A517105	이신열	05/03/01

SELECT
        STUDENT_NO 학번
    ,   STUDENT_NAME 이름
    ,   ENTRANCE_DATE 입학년도
    FROM TB_STUDENT
    WHERE DEPARTMENT_NO = 002
    ORDER BY 3;

---

Q2

💁‍♀️ 춘 기술대학교의 교수 중 이름이 세 글자가 아닌 교수가 두 명 있다고 합니다. 그 교수의 이름과 주민번호를 화면에 출력하는 SQL 문장을 작성해 보세요.

🚩 Example Output 2 rows selected

PROFESSOR_NAME	PROFESSOR_SSN
강혁	601004-1100528
박강아름	681201-2134896

SELECT
        PROFESSOR_NAME
    ,   PROFESSOR_SSN
    FROM TB_PROFESSOR
    WHERE LENGTH(PROFESSOR_NAME) <> '3';

💡 Another Solution

SELECT 
       PROFESSOR_NAME
     , PROFESSOR_SSN
  FROM TB_PROFESSOR
 WHERE PROFESSOR_NAME NOT LIKE '___';

---

Q3

💁‍♀️ 춘 기술대학교의 남자 교수들의 이름과 나이를 출력하는 SQL 문장을 작성하세요. 이때, 나이가 적은 사람에서 많은 사람 순서로 화면에 출력되도록 만드세요.
(단, 교수 중 2000 년 이후 출생자는 없으며 출력 헤더는 "교수이름", "나이"로 합니다. 나이는 ‘만’으로 계산합니다.)

🚩 Example Output 75 rows selected

교수이름	나이
제상철	42
주영상	42
. . . . . .	. . . . . .
정진정	74
이도현	74

SELECT
        PROFESSOR_NAME 교수이름
      ,  FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(19 || SUBSTR(PROFESSOR_SSN, 1, 6), 'RRRRMMDD')) / 12) 나이
    FROM TB_PROFESSOR
    WHERE PROFESSOR_SSN LIKE '_______1%'
    ORDER BY 2;

💬 Overall Comment

* 처음에 주민등록번호 데이터를 가지고 어떻게 나이를 구할 수 있을지 굉장히 많은 고민을 했다. 
  MONTHS_BETWEEN으로 현재 날짜(SYSDATE)에서 생년월일의 차를 구하여 나이를 구해야 했다는 점은 
  알고있었으나, '지정한 월이 부적합합니다.'등의 오류가 발생했고 이는 '19'를 앞에 명시해줌으로써 
  해결되었다. 2000년대 이전 출생의 교수들이므로, 'RRRR'을 사용했고, 게다가 1950년 이전에 출생한 
  교수들이 존재했으므로 50이하의 수는 2000년대로 인식하는 'RRRR'의 특성을 고려하여 반드시 19를 앞에 
  붙여줬어야했다.

---

Q4

💁‍♀️ 교수들의 이름 중 성을 제외한 이름만 출력하는 SQL 문장을 작성하세요. 출력 헤더는 "이름"이 찍히도록 합니다.
(단, 성이 두 자인 경우의 교수는 없다고 가정하세요.)

🚩 Example Output 114 rows selected

이름
진영
윤필
. . . . . .
혁호

SELECT
        SUBSTR(PROFESSOR_NAME, 2) 이름
    FROM TB_PROFESSOR;

---

Q5

💁‍♀️ 춘 기술대학교의 재수생 입학자를 구하려고 합니다. 어떻게 찾아낼까요?
이때, 19살에 입학하면 재수를 하지 않은 것으로 간주합니다.
(출력 헤더는 "학번", "이름", "입학한 나이" 입니다.)

🚩 Example Output 204 rows selected

학번	이름	입학한 나이
A431016	고병훈	20
A431021	구병훈	20
. . . . . .	. . . . . .	. . . . . .
A241053	이희수	20
A241056	이희진	20

SELECT
        STUDENT_NO 학번
    ,   STUDENT_NAME 이름
    ,   FLOOR(MONTHS_BETWEEN(ENTRANCE_DATE, TO_DATE(SUBSTR(STUDENT_SSN, 1, 2), 'RR')) / 12) "입학한 나이"
    FROM TB_STUDENT
    WHERE FLOOR(MONTHS_BETWEEN(ENTRANCE_DATE, TO_DATE(SUBSTR(STUDENT_SSN, 1, 2), 'RR')) / 12) > 19;

💡 Another Solution

SELECT 
       STUDENT_NO
     , STUDENT_NAME
     , EXTRACT(YEAR FROM ENTRANCE_DATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(STUDENT_SSN, 1, 2), 'RR'))) "입학한 나이"
  FROM TB_STUDENT
 WHERE EXTRACT(YEAR FROM ENTRANCE_DATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(STUDENT_SSN, 1, 2), 'RR'))) > 19;

📌 Ref.

* EXTRACT : 년, 월, 일 정보를 추출하여 리턴하는 함수

💬 Overall Comment

* 이 문항 또한 굉장한 시간을 요구했다. 처음에는 교수의 만 나이를 구하는 문항을 참고하여 응용하면 되겠다고 
  생각했으나, 이 문항에서는 19살 초과의 나이에 입학한 재수생을 구해야했기 때문에 조금 다른 방식으로 
  접근해야했다. 
  입학 년도와 생년의 차를 구한 뒤, 그것이 19를 초과하면 재수생이라는 쿼리를 짜야했는데, 처음에 내림처리를 
  하지않아 출력되는 행의 갯수가 더 많은가하면 가까스로 알맞은 행의 갯수가 나왔지만 나이의 범위가 이해가 
  되지않았었다.
  제출을 하고난 뒤에도 계속해서 무엇이 문제인지 확인해본 결과, 메인 리즌은 나이를 FLOOR로 내림처리해야했고 
  따라서 19초과 20이하의 범위(왜 이렇게 했을 때 204행이 나왔는지 아직도 이해불가)가 아닌 그저 19초과의 
  범위만 지정해주면 되는 것이었다.
* 또한 EXTRACT을 활용하여 나이를 구하는 방법도 있다는 것을 알게되어 굉장히 값진 문항이었다.

---

Q6

💁‍♀️ 2020년 크리스마스는 무슨 요일일까요?

🚩 Example Output 1 rows selected

TO_CHAR(TO_DATE('201225', 'YYMMDD'), 'DAY')
금요일

SELECT
        TO_CHAR(TO_DATE('201225', 'YYMMDD'), 'DAY')
    FROM DUAL;

📌 Ref.

* TO_CHAR : TO_CHAR(날짜, 'DAY') 를 활용하면 해당 날짜의 요일을 알 수 있음

---

Q7

💁‍♀️ TO_DATE('99/10/11','YY/MM/DD'), TO_DATE('49/10/11','YY/MM/DD') 은 각각 몇 년 몇 월 몇 일을 의미할까요?
또한 TO_DATE('99/10/11','RR/MM/DD'), TO_DATE('49/10/11','RR/MM/DD') 은 각각 몇 년 몇 월 몇 일을 의미할까요?

🚩 Example Output 1 rows selected

('99/10/11','YY/MM/DD')	('49/10/11','YY/MM/DD')	('99/10/11','RR/MM/DD')	('49/10/11','RR/MM/DD')
2099년 10월 11일	2049년 10월 11일	1999년 10월 11일	2049년 10월 11일

SELECT
        TO_CHAR(TO_DATE('99/10/11','YY/MM/DD'), 'YYYY"년 "MM"월 "DD"일"') "('99/10/11','YY/MM/DD')"
    ,   TO_CHAR(TO_DATE('49/10/11','YY/MM/DD'), 'YYYY"년 "MM"월 "DD"일"') "('49/10/11','YY/MM/DD')"
    ,   TO_CHAR(TO_DATE('99/10/11','RR/MM/DD'), 'RRRR"년 "MM"월 "DD"일"') "('99/10/11','RR/MM/DD')"
    ,   TO_CHAR(TO_DATE('49/10/11','RR/MM/DD'), 'RRRR"년 "MM"월 "DD"일"') "('49/10/11','RR/MM/DD')"
    FROM DUAL;

---

Q8

💁‍♀️ 춘 기술대학교의 2000 년도 이후 입학자들은 학번이 A 로 시작하게 되어있습니다. 2000년도 이전 학번을 받은 학생들의 학번과 이름을 보여주는 SQL 문장을 작성하세요.

🚩 Example Output 52 rows selected

STUDENT_NO	STUDENT_NAME
9556017	서성철
9732111	정완렬
. . . . . .	. . . . . .
9911206	임충헌

SELECT
        STUDENT_NO
    ,   STUDENT_NAME
    FROM TB_STUDENT
    WHERE STUDENT_NO NOT LIKE '%A%';

---

Q9

💁‍♀️ 9. 학번이 A517178 인 한아름 학생의 학점 총 평점을 구하는 SQL 문을 작성하세요.
단, 이때 출력 화면의 헤더는 "평점" 이라고 찍히게 하고, 점수는 반올림하여 소수점 이하 한 자리까지만 표시하세요.

🚩 Example Output 1 rows selected

평점
3.6

SELECT 
        ROUND(AVG(POINT), 1) 평점
    FROM TB_GRADE
    WHERE STUDENT_NO = 'A517178'
    GROUP BY STUDENT_NO;

---

Q10

💁‍♀️ 학과별 학생수를 구하여 "학과번호", "학생수(명)" 의 형태로 헤더를 만들어 결과값이 출력되도록 하세요.

🚩 Example Output 62 rows selected

학과번호	학생수(명)
001	14
002	3
. . . . . .	. . . . . .
062	8

SELECT
        DEPARTMENT_NO "학과번호"
    ,   COUNT(DEPARTMENT_NO) "학생수(명)"
    FROM TB_STUDENT
    GROUP BY DEPARTMENT_NO
    ORDER BY 1;

---

Q11

💁‍♀️ 지도 교수를 배정받지 못한 학생의 수는 몇 명 정도 되는지 알아내는 SQL 문을 작성하세요.

🚩 Example Output 1 rows selected

COUNT(*)
9

SELECT
        COUNT(*)
    FROM TB_STUDENT
    WHERE COACH_PROFESSOR_NO IS NULL
    GROUP BY COACH_PROFESSOR_NO;

---

Q12

💁‍♀️ 학번이 A112113 인 김고운 학생의 년도 별 평점을 구하는 SQL 문을 작성하세요.
단, 이때 출력 화면의 헤더는 "년도", "년도 별 평점" 이라고 찍히게 하고, 점수는 반올림하여 소수점 이하 한 자리까지한 표시합니다.

🚩 Example Output 5 rows selected

년도	년도 별 평점
2001	2.8
2002	2.3
2003	4
2004	3.5
	3.2

SELECT
        SUBSTR(TERM_NO, 1, 4) 년도
    ,   ROUND(AVG(POINT), 1) "년도 별 평점"
    FROM TB_GRADE
    WHERE STUDENT_NO = 'A112113'
    GROUP BY ROLLUP(SUBSTR(TERM_NO, 1, 4));

---

Q13

💁‍♀️ 학과 별 휴학생 수를 파악하고자 합니다. 학과 번호와 휴학생 수를 표시하는 SQL 문장을 작성하세요.

🚩 Example Output 62 rows selected

학과코드명	휴학생 수
001	2
002	0
. . . . . .	. . . . . .
062	2

SELECT
        DEPARTMENT_NO 학과코드명
    ,   COUNT(DECODE(ABSENCE_YN, 'Y', '조이')) "휴학생 수"
    FROM TB_STUDENT
    GROUP BY (DEPARTMENT_NO)
    ORDER BY 1;

💬 Overall Comment

* 처음에는 COUNT(ABSENCE_YN)로 작성하고 HAVING절에 ABSENCE_YN = 'Y'로 그룹에 조건을 걸어 실행했다. 
  하지만 휴학생 수가 없는 002 등의 학과코드들이 출력 자체가 되지 않았고 어떻게든 출력하려 다른 조건도 
  걸어보고 NULL값을 포함시키는 COUNT(*)로 사용해보기도 했다.
  하지만 결국 레시피를 참고하여 HAVING절이 아닌 DECODE를 이용해서 휴학생('Y')만을 '조이'라는 값을 
  갖도록 하고(무슨 값이 들어가던 상관X) 재학생('N')은 NULL 값을 갖도록 했다. 때문에 NULL값을 제외한 
  갯수를 세는 COUNT(컬럼명)은 휴학생 수만 카운트하여 출력한다.
  함수들이 저마다 굉장히 다양한 기능들을 갖추고 있는데, 이를 기억하고 정말 잘 활용해야겠다고 생각한 
  계기가 되었다. 특히 DECODE의 기능을 이용한 저 부분이 굉장히 인상깊고 꼭 내 것으로 만들어서 사용해보고싶다.

---

Q14

💁‍♀️ 춘 대학교에 다니는 동명이인 학생들의 이름을 찾고자 합니다. 어떤 SQL 문장을 사용하면 가능할까요?

🚩 Example Output 20 rows selected

동일이름	동명인 수
김경민	2
김명철	2
. . . . . .	. . . . . .
조기현	2
최효정	2

SELECT
        STUDENT_NAME 동일이름
    ,   COUNT(STUDENT_NAME) "동명인 수"
    FROM TB_STUDENT
    GROUP BY STUDENT_NAME
    HAVING COUNT(STUDENT_NAME) NOT IN 1
    ORDER BY 1;

💡 Another Solution

SELECT 
       STUDENT_NAME AS 동일이름
     , COUNT(*) AS "동명인 수"
  FROM TB_STUDENT
 GROUP BY STUDENT_NAME
 HAVING COUNT(*) > 1
 ORDER BY 1;

---

Q15

💁‍♀️ 학번이 A112113인 김고운 학생의 년도, 학기 별 평점과 년도 별 누적 평점 , 총 평점을 구하는 SQL 문을 작성하세요.
(단, 평점은 소수점 1 자리까지만 반올림하여 표시합니다.)

🚩 Example Output 14 rows selected

년도	학기	평점
2001	01	2.5
2001	02	3
2001		2.8
. . . . . .	. . . . . .	. . . . . .
2004	01	4
2004	02	3
2004		3.5
		3.2

SELECT
        SUBSTR(TERM_NO, 1, 4) 년도
    ,   SUBSTR(TERM_NO, 5, 2) 학기
    ,   ROUND(AVG(POINT), 1) 평점
    FROM TB_GRADE
    WHERE STUDENT_NO = 'A112113'
    GROUP BY ROLLUP(SUBSTR(TERM_NO, 1, 4), SUBSTR(TERM_NO, 5, 2))
    ORDER BY 1;

---