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

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

 

SELECT(함수) 1. ✅

-- 1. 영어영문학과(학과코드 002) 학생들의 학번, 이름, 입학년도 조회
-- 입학년도가 빠른 순으로 표시
SELECT
       STUDENT_NO 학번
     , STUDENT_NAME 이름
     , TO_CHAR(ENTRANCE_DATE, 'RRRR-MM-DD') 입학년도
  FROM TB_STUDENT
 WHERE DEPARTMENT_NO = '002'
 ORDER BY 3;

 

SELECT(함수) 2. ✅

-- 2. 교수 중 이름이 세 글자가 아닌 교수 조회
-- 세 자리에 대해서만 조건을 달았기 때문에 이름이 네 자인 교수 역시 출력되고 있다.
SELECT 
       PROFESSOR_NAME
     , PROFESSOR_SSN
  FROM TB_PROFESSOR
 WHERE PROFESSOR_NAME NOT LIKE '___';

 

SELECT(함수) 3. ❎✅

처음 작성한 오답이었다: ABS(FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(PROFESSOR_SSN, 1, 6), 'RRMMDD')) / 12))

음수가 뜨기에 절대값으로 묶어준 거였는데 실은 TO_DATE 형변환에서 자릿수를 제대로 맞추지 않아 발생한 오류였다.

2000년 이후 출생자는 없다는 문제 조건이 바로 여기서 활용되는 거였다. 따라서 TO_DATE(SUBSTR('19' || PROFESSOR_SSN, 1, 6), 'RRRRYYDD)')) / 12) 처럼 앞에 문자 '19'를 붙여쓰면 된다.

답안에서는 ORDER BY절에서도 나이, 이름순으로 함께 정렬했다: ORDER BY 2, PROFESSOR_NAME;

-- 3. 남자 교수들의 이름과 나이 조회
-- 단, 이때 나이가 적은 사람에서 많은 사람 순서로 출력한다.
-- 단, 교수 중 2000년 이후 출생자는 없으며 출력 헤더는 "교수이름", "나이"
-- 나이는 만으로 계산한다.
SELECT
       PROFESSOR_NAME 교수이름
     , FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE('19'||SUBSTR(PROFESSOR_SSN, 1, 6),'YYYYMMDD'))/12) 나이
  FROM TB_PROFESSOR
 WHERE SUBSTR(PROFESSOR_SSN, 8, 1) = '1'
 ORDER BY 2, PROFESSOR_NAME;

 

SELECT(함수) 4. ❎✅

이름이 세 글자라면 SUBSTR(PROFESSOR_NAME, 2, 2)로 2자리만 출력하겠다고 조건을 달아도 괜찮겠지만, 이름이 두 자 혹은 네 자인 교수진도 있다. 따라서 SUBSTR(PROFESSOR, 2) 정도로만 작성해 끝자리까지 출력되게끔 하는 것이 바른 작성이겠다.

-- 4. 교수 이름에서 성을 제외한 이름만 출력
-- 출력 헤더는 "이름"으로 하고, 성이 2자인 경우는 없다고 가정한다.
SELECT
       SUBSTR(PROFESSOR_NAME, 2) 이름
  FROM TB_PROFESSOR;

 

SELECT(함수) 5. ✅

답안의 WHERE절 조건식은 다음과 같다. ENTRANCE_DATE와 STUDENT_SSN에서 두 자리씩만 출력해 마이너스 연산한 구조이다: WHERE EXTRACT(YEAR FROM ENTRANCE_DATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(STUDENT_SSN, 1, 2), 'RR'))) > 19;

-- 5. 재수생 입학자 조회
-- 이때 19살에 입학하면 재수를 하지 않은 것으로 간주한다.
SELECT
       STUDENT_NO
     , STUDENT_NAME
  FROM TB_STUDENT
 WHERE EXTRACT(YEAR FROM ENTRANCE_DATE) - LPAD(SUBSTR(STUDENT_SSN, 1, 2), 4, '19') > 19; --LAPD 통해 19**으로 출력된다
-- EXTRACT(YEAR FROM(TO_DATE(SUBSTR(ENTRANCE_DATE, 1, 2), 'RR'))) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(STUDENT_SSN, 1, 2), 'RR'))) > 19
-- ORDER BY EXTRACT(YEAR FROM(TO_DATE(SUBSTR(ENTRANCE_DATE, 1, 2), 'RR'))) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(STUDENT_SSN, 1, 2), 'RR')));

 

SELECT(함수) 6. ✅

요일 구하는 식을 정리하면 다음과 같다: 'DAY': 금요일 'DY': 금 'D': 6

-- 6. 2020년 크리스마스는 무슨 요일인지 조회
SELECT
       TO_CHAR(TO_DATE('201225', 'RRMMDD'), 'DAY')
  FROM DUAL;

 

SELECT(함수) 7. ❎✅

YY는 모두 2000년대, RR은 49이하는 2000년대 / 50이상은 1900년대 취급한다.

-- 7. 각각 몇 년 몇 월 며칠을 의미하는지 조회
SELECT
       TO_CHAR(TO_DATE('99/10/11', 'YY/MM/DD'), 'YYYY') --2099/10/11
     , TO_CHAR(TO_DATE('49/10/11', 'YY/MM/DD'), 'YYYY') --2049/10/11
     , TO_CHAR(TO_DATE('99/10/11', 'RR/MM/DD'), 'RRRR') --1999/10/11
     , TO_CHAR(TO_DATE('49/10/11', 'RR/MM/DD'), 'RRRR') --2049/10/11
  FROM DUAL;

 

SELECT(함수) 8. ✅

-- 8. 2000년도 이전 학번을 받은 학생들의 학번과 이름 조회 
-- 2000년도 이후 입학자들은 학번이 A로 시작한다.
SELECT 
       STUDENT_NO
     , STUDENT_NAME
  FROM TB_STUDENT
 WHERE STUDENT_NO NOT LIKE 'A%';

 

SELECT(함수) 9. ✅

-- 9. 학번이 A517178인 한아름 학생의 학점 총 평점 조회
-- 단, 이때 출력 화면의 헤더는 "평점"이고
-- 점수는 반올림하여 소수점 이하 한 자리까지만 표시한다.
SELECT
       ROUND(AVG(POINT), 1) 평점
  FROM TB_GRADE
 WHERE STUDENT_NO = 'A517178';

 

SELECT(함수) 10. ✅

학과번호는 TB_STUDENT 테이블에서도 조회 가능하다. 따라서 굳이 JOIN하지 않고 구할 수 있다.

-- 10. 학과별 학생수 조회
-- "학과번호", "학생수(명)" 형태로 헤더를 만든다.
SELECT
       DP.DEPARTMENT_NO 학과번호
     , COUNT(*) "학생수(명)"
  FROM TB_DEPARTMENT DP
  JOIN TB_STUDENT ST ON(DP.DEPARTMENT_NO = ST.DEPARTMENT_NO)
  GROUP BY DP.DEPARTMENT_NO
  ORDER BY 1;

 

SELECT(함수) 11. ✅

-- 11. 지도 교수를 배정 받지 못한 학생의 수 조회
SELECT
       COUNT(*) "COUNT(*)"
  FROM TB_STUDENT
 WHERE COACH_PROFESSOR_NO IS NULL;

 

SELECT(함수) 12. ✅

-- 12. 학번이 A112113인 김고운 학생의 연도별 평점 조회
-- 단, 이때 출력 화면의 헤더는 "년도", "년도 별 평점"
-- 점수는 반올림하여 소수점 이하 한 자리까지만 표시한다.
SELECT
       SUBSTR(TERM_NO, 1, 4) 년도
     , ROUND(AVG(POINT), 1) "년도 별 평점"
  FROM TB_GRADE
 WHERE STUDENT_NO = 'A112113'
 GROUP BY SUBSTR(TERM_NO, 1, 4)
 ORDER BY 1;

 

SELECT(함수) 13. ❎✅

그냥 COUNT(ABSENCE_YN) 조회하면 NULL이 아닌 값이 들어있는 행의 수를 모두 카운트한다.

휴학생이라는 조건을 만들기 위해 COUNT(DECODE(ABSENCE_YN), 'Y', 1))로 고쳐써야 한다. 이때 1은 카운팅을 위한 것일뿐 다른 문자 기호여도 상관 없다.

-- 13. 학과별 휴학생 수 조회
-- 학과 번호와 휴학생 수를 표시한다.
SELECT
       DEPARTMENT_NO 학과코드명
     , COUNT(DECODE(ABSENCE_YN, 'Y', 1)) "휴학생 수"
  FROM TB_STUDENT
 GROUP BY DEPARTMENT_NO
 ORDER BY 1;

 

SELECT(함수) 14. ❎✅

ST1.STUDENT_NO <> ST2.STUDENT_NO, ST1.STUDENT_NAME = ST2.STUDENT_NAME... WHERE절 조건을 나열해가며 풀었는데 HAVING절로 간단하게 할 수 있는 문제였다: HAVING COUNT(*) > 1

-- 14. 동명이인 학생들의 이름 조회
SELECT 
       STUDENT_NAME AS 동일이름
     , COUNT(*) AS "동명인 수"
  FROM TB_STUDENT
 GROUP BY STUDENT_NAME
 HAVING COUNT(*) > 1
 ORDER BY 1;

 

SELECT(함수) 15. ✅

답안에서는 NULL값을 공백으로 바꾸지 않았다.

그리고 결과가 동일하긴 하지만, 끝에 ORDER BY SUBSTR(TERM_NO,1,4);를 명시하기도 했다.

-- 15. 학번이 A112113인 김고운 학생의 년도, 학기별 평점, 년도별 누적 평범, 총 평점을 조회
-- 단, 평점은 소수점 1자리까지만 반올림하여 표시한다.
SELECT
       NVL(SUBSTR(TERM_NO, 1, 4), ' ') 년도
     , NVL(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));