자바 프로그래밍_Day_43_문제풀이

 

 

2021. 10. 19

 

     > 학원에서 준 데이터로 실습문제 + 풀이 몇 문제

 

 

 

-- SQL01 Basic
 
 
-- 2번
SELECT DEPARTMENT_NAME || '의 정원은 ' || CAPACITY || '명 입니다.' AS "학과별 정원"
FROM TB_DEPARTMENT;
 
-- 3번
SELECT TS.STUDENT_NAME
FROM TB_STUDENT TS
JOIN TB_DEPARTMENT TB ON (TS.DEPARTMENT_NO = TB.DEPARTMENT_NO)
WHERE TB.DEPARTMENT_NAME = '국어국문학과'
  AND SUBSTR(STUDENT_SSN, 8, 1) = '2'
  AND TS.ABSENCE_YN = 'Y';
  
-- 6번
SELECT *
FROM TB_PROFESSOR
WHERE DEPARTMENT_NO IS NULL;
 
-- 7번
SELECT *
FROM TB_STUDENT
WHERE DEPARTMENT_NO IS NULL;
 
-- 8번
SELECT C1.CLASS_NO, C1.CLASS_NAME, C1.PREATTENDING_CLASS_NO, C2.CLASS_NAME
FROM TB_CLASS C1
JOIN TB_CLASS C2 ON C1.PREATTENDING_CLASS_NO = C2.CLASS_NO
WHERE C1.PREATTENDING_CLASS_NO IS NOT NULL;
 
-- 10번
SELECT STUDENT_NO,
       STUDENT_NAME,
       STUDENT_SSN,
FROM TB_STUDENT
-- WHERE ENTRANCE_DATE BETWEEN TO_DATE('02/01/01', 'RR/MM/DD') AND TO_DATE('02/12/31', 'RR/MM/DD');
WHERE EXTRACT(YEAR FROM ENTRANCE_DATE) = 2002
-- AND SUBSTR(STUDENT_ADDRESS, 1,2) = '전주%';
  AND STUDENT_ADDRESS LIKE '전주%'
  AND ABSENCE_YN = 'N';
 
-- 10번
SELECT EXTRACT(YEAR FROM ENTRANCE_DATE)
FROM TB_STUDENT;
 
 
 
-- SQL02_Function
-- 1번
SELECT STUDENT_NO AS "학번",
       STUDENT_NAME AS "이름",
       TO_CHAR(ENTRANCE_DATE, 'YYYY-MM-DD') AS "입학년도"
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '002'
ORDER BY ENTRANCE_DATE;
 
-- 2번
SELECT PROFESSOR_NAME, PROFESSOR_SSN
FROM TB_PROFESSOR
-- WHERE LENGHT(PROFESSOR_NAME) != 3;
WHERE PROFESSOR_NAME NOT LIKE '___';
 
-- 3번
SELECT PROFESSOR_NAME AS "이름",
       FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE('19' || SUBSTR(PROFESSOR_SSN, 1, 2), 'YYYY')) / 12) AS "나이"
FROM TB_PROFESSOR
WHERE SUBSTR(PROFESSOR_SSN, 8, 1) = '1'
ORDER BY 2, 1;
 
-- 5번
SELECT STUDENT_NO,
       STUDENT_NAME,
       MONTHES_BETWEEN(ENTRANCE_DATE, TO_DATE(SUBSTR(STUDENT_SSN, 1, 6), 'RRMMDD')) / 12
FROM TB_STUDENT
WHERE MONTHES_BETWEEN(ENTRANCE_DATE, TO_DATE(SUBSTR(STUDENT_SSN, 1, 6), 'RRMMDD')) / 12 > 19
  AND MONTHES_BETWEEN(ENTRANCE_DATE, TO_DATE(SUBSTR(STUDENT_SSN, 1, 6), 'RRMMDD')) / 12 <= 20;  -- 딱 재수만 한 학생들
  
-- 6번
SELECT TO_CHAR(TO_DATE('21/12/25', 'RR/MM/DD'), 'DAY')
FROM DUAL;
 
-- 11번
SELECT COUNT (*)
FROM TB_STUDENT
WHERE COACH_PROFESSOR_NO IS NULL;
 
-- 12번
SELECT SUBSTR(TERM_NO, 1, 4) AS "년도", ROUND(AVG(NVL(POINT, 0)), 1) AS "년도 별 평점" 
FROM TB_GRADE
WHERE STUDENT_NO = 'A112113'
GROUP BY SUBSTR(TERM_NO, 1, 4)
ORDER BY 1;
 
-- 13번
SELECT DEPARTMENT_NO, COUNT(DECODE(ABSENCE_YN, 'Y', ABSENCE_YN, NULL))
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO;
 
-- 15번
SELECT SUBSTR (TERM_NO, 1, 4) AS "년도",
       SUBSTR(TERM_NO, 5) AS "학기",
       ROUND(AVG(NVL(POINT, 0)), 1) AS "평균"
FROM TB_GRADE
WHERE STUDENT_NO = 'A112113'
GROUP BY ROLLUP(SUBSTR (TERM_NO, 1, 4), SUBSTR(TERM_NO, 5))
ORDER BY SUBSTR (TERM_NO, 1, 4);
 
 
 
 
-- SQL02_Option
-- 6번
SELECT S.STUDENT_NO,
       S.STUDENT_NAME,
       D.DEPARTMENT_NAME
FROM TB_STUDENT S
JOIN TB_DEPARTMENT D ON (S.DEPARTMENT_NO = D.DEPARTMNET_NO)
ORDER BY S.STUDENT_NAME;
 
-- 8번
SELECT * FROM TB_CLASS_PROFESSOR;
SELECT * FROM TB_CLASS;
SELECT * FROM TB_PROFESSOR;
SELECT * FROM TB_DEPARTMENT;
 
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 1;
 
-- 9번
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 (P.DEPARTMENT_NO = D.DEPARTMENT_NO)
WHERE D.CATEGORY = '인문사회'
ORDER BY 2, 1;
 
-- 10번
SELECT S.STUDENT_NO AS "학번",
       S.STUDENT_NAME AS "학생 이름",
       ROUND(AVG(NVL(POINT, 0)), 1) AS "전체 평점"
FROM TB_GRADE G
JOIN TB_STUDENT S ON (G.STUDENT_NO = S.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 S.STUDENT_NAME;
 
 
-- 13번 (수정님 쿼리문)
SELECT C.CLASS_NAME, D.DEPARTMENT_NAME
FROM TB_CLASS C
INNER JOIN TB_CLASS_PROFESSOR CP USING(CLASS_NO)
INNER JOIN TB_DEPARTMENT D USING(DEPARTMENT_NO)
WHERE D.CATEGORY = '예체능'
  AND PROFESSOR_NO IS NULL
ORDER BY 1;
 
 
-- 15번
SELECT S.STUDENT_NO,
       S.STUDENT_NAME,
       D.DEPARTMENT_NAME,
       ROUND(AVG(NVL(POINT, 0)), 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 S.ABSENCE_YN = 'N'
GROUP BY S.STUDENT_NO, S.STUDENT_NAME, D.DEPARTMENT_NAME
HAVING AVG(NVL(POINT, 0)) >= 4;
 
-- 18번
SELECT * FROM TB_DEPARTMENT WHERE DEPARTMENT_NAME = '국어국문학과';
SELECT * FROM TB_STUDENT WHERE DEPARTMENT_NO = 001;
SELECT * FROM TB_GRADE;
 
-- 서브쿼리
SELECT MAX(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;
 
 
SELECT S.STUDENT_NO, S.STUDENT_NAME
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
HAVING AVG(POINT) = (
    SELECT MAX(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
);
 
-- 인라인 뷰
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;