[SQL] SELECT ~ SUBQUARY 연습문제

-- << Basic SELECT >> -----------------------------------------------------------------------------

-- 1번
SELECT DEPARTMENT_NAME 학과명, CATEGORY 계열
FROM TB_DEPARTMENT;

--2번
SELECT DEPARTMENT_NAME || '의 정원은 ' || CAPACITY || ' 명 입니다.' "학과별 정원"
FROM TB_DEPARTMENT;

--3번
SELECT STUDENT_NAME
FROM TB_STUDENT
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
WHERE DEPARTMENT_NAME = '국어국문학과'
AND SUBSTR(STUDENT_SSN,8,1) = '2'
AND ABSENCE_YN = 'Y';

--4번
SELECT STUDENT_NAME
FROM TB_STUDENT
WHERE STUDENT_NO IN('A513079','A513090','A513091','A513110','A513119');

--5번
SELECT DEPARTMENT_NAME, CATEGORY
FROM TB_DEPARTMENT
WHERE 20 <= CAPACITY AND CAPACITY <= 30; 

--6번
SELECT PROFESSOR_NAME
FROM TB_PROFESSOR
WHERE DEPARTMENT_NO IS NULL;

--7번
SELECT STUDENT_NO
FROM TB_STUDENT
WHERE DEPARTMENT_NO IS NULL;

--8번
SELECT CLASS_NO
FROM TB_CLASS
WHERE PREATTENDING_CLASS_NO IS NOT NULL;

--9번
SELECT DISTINCT (CATEGORY)
FROM TB_DEPARTMENT;

--10번
SELECT STUDENT_NO, STUDENT_NAME, STUDENT_SSN
FROM TB_STUDENT
WHERE STUDENT_ADDRESS LIKE '전주%' 
AND SUBSTR(ENTRANCE_DATE,1,4) = '2002'
AND ABSENCE_YN = 'N';

 

-- <<Additional SELECT - 함수 >> -----------------------------------------------------------------------------

--1번
SELECT STUDENT_NO 학번 , STUDENT_NAME 이름 , SUBSTR(ENTRANCE_DATE,1,10) 입학년도
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '002'
ORDER BY ENTRANCE_DATE;

--2번
SELECT PROFESSOR_NAME, PROFESSOR_SSN
FROM TB_PROFESSOR
WHERE LENGTH(PROFESSOR_NAME) != 3;

--3번
SELECT PROFESSOR_NAME 교수이름, 
CASE WHEN SUBSTR(PROFESSOR_SSN,1,2) < 50
     THEN EXTRACT(YEAR FROM SYSDATE)+EXTRACT(YEAR FROM TO_DATE(SUBSTR(PROFESSOR_SSN,1,6), 'YY/MM/DD'))-4000
ELSE EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM TO_DATE(SUBSTR(PROFESSOR_SSN,1,6), 'RR/MM/DD'))
END 나이
FROM TB_PROFESSOR
WHERE SUBSTR(PROFESSOR_SSN,8,1) = '1'
ORDER BY 나이;

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

--5번
SELECT STUDENT_NO,STUDENT_NAME
FROM TB_STUDENT
WHERE EXTRACT(YEAR FROM ENTRANCE_DATE) - EXTRACT(YEAR FROM (TO_DATE(SUBSTR(STUDENT_SSN,1,6),'RR/MM/DD'))) >= 20;

--6번
SELECT TO_CHAR(TO_DATE('2020/12/25'),'DAY')
FROM DUAL;

--7번
SELECT 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')
FROM DUAL;

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

--9번
SELECT ROUND(SUM(POINT)/COUNT(POINT),1) 평점
FROM TB_GRADE
JOIN TB_STUDENT USING(STUDENT_NO)
WHERE STUDENT_NO = 'A517178';

--10번
SELECT DEPARTMENT_NO 학과번호, COUNT(STUDENT_NO) 학생수
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO;

--11번
SELECT COUNT(*)
FROM TB_STUDENT
WHERE COACH_PROFESSOR_NO IS NULL;

--12번
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 년도;

--13번
SELECT DEPARTMENT_NO 학과코드명, 
SUM(CASE 
    WHEN ABSENCE_YN = 'Y' THEN 1
    WHEN ABSENCE_YN = 'N' THEN 0
    END) "휴학생 수"
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO;

--14번
SELECT STUDENT_NAME, COUNT(STUDENT_NAME)
FROM TB_STUDENT S1
WHERE STUDENT_NAME IN (SELECT STUDENT_NAME
                      FROM TB_STUDENT S2
                      WHERE S1.STUDENT_NO != S2.STUDENT_NO
                      AND S1.STUDENT_NAME = S2.STUDENT_NAME)
GROUP BY STUDENT_NAME
ORDER BY STUDENT_NAME;


--15번 : NULL값에 공백 넣는 방법? (ROLL UP 사용하면됨 ~)
SELECT SUBSTR(TERM_NO,1,4) 년도,
       SUBSTR(TERM_NO,5,2) 학기,  
       ROUND(AVG(POINT),1) 평점
FROM TB_GRADE
JOIN TB_STUDENT USING (STUDENT_NO)
WHERE STUDENT_NO = 'A112113'
GROUP BY ROLLUP(SUBSTR(TERM_NO,1,4), SUBSTR(TERM_NO,5,2))
ORDER BY 년도;


-- << Additional SELECT - Option>> -----------------------------------------------------------------------------

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

--2번
SELECT STUDENT_NAME, STUDENT_SSN
FROM TB_STUDENT
WHERE ABSENCE_YN = 'Y'
ORDER BY STUDENT_SSN DESC;

--3번
SELECT STUDENT_NAME 학생이름, STUDENT_NO 학번,  STUDENT_ADDRESS "거주지 주소"
FROM TB_STUDENT
WHERE (STUDENT_ADDRESS LIKE '강원도%' OR STUDENT_ADDRESS LIKE '경기도%')
      AND STUDENT_NO LIKE '9%'
ORDER BY STUDENT_NAME;

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

--5번
SELECT STUDENT_NO, POINT
FROM TB_STUDENT
JOIN TB_GRADE USING (STUDENT_NO)
WHERE TERM_NO = 200402 AND CLASS_NO = 'C3118100'
ORDER BY POINT DESC, STUDENT_NO ASC;

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

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

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

--9번
SELECT CLASS_NAME, PROFESSOR_NAME
FROM TB_CLASS
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
JOIN TB_PROFESSOR USING(PROFESSOR_NO)
WHERE CATEGORY = '인문사회';

--10번
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 STUDENT_NO;

--11번
SELECT D.DEPARTMENT_NAME 학과이름, 
       S.STUDENT_NAME 학생이름, 
       P.PROFESSOR_NAME 지도교수이름
FROM TB_DEPARTMENT D, TB_STUDENT S, TB_PROFESSOR P
WHERE D.DEPARTMENT_NO = S.DEPARTMENT_NO
AND S.COACH_PROFESSOR_NO = P.PROFESSOR_NO
AND S.STUDENT_NO = 'A313047';

--12번
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 ='인간관계론';


--13번 
SELECT CLASS_NAME, DEPARTMENT_NAME
FROM TB_CLASS
LEFT JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
WHERE CATEGORY = '예체능'
AND PROFESSOR_NO IS NULL;


--14번
SELECT STUDENT_NAME 학생이름 , NVL(TO_CHAR(PROFESSOR_NAME),'지도교수 미지정') 지도교수
FROM TB_STUDENT S, TB_PROFESSOR P, TB_DEPARTMENT D
WHERE COACH_PROFESSOR_NO = PROFESSOR_NO(+)
AND S.DEPARTMENT_NO = D.DEPARTMENT_NO(+)
AND DEPARTMENT_NAME ='서반아어학과'
ORDER BY STUDENT_NO;

--15번
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
ORDER BY STUDENT_NO;

       

--16번
SELECT CLASS_NO, CLASS_NAME, TRUNC(AVG(POINT),8)
FROM TB_DEPARTMENT
JOIN TB_CLASS USING (DEPARTMENT_NO)
JOIN TB_GRADE USING (CLASS_NO)
WHERE DEPARTMENT_NAME = '환경조경학과' AND CLASS_TYPE LIKE '전공%'
GROUP BY DEPARTMENT_NAME, CLASS_TYPE, CLASS_NO, CLASS_NAME;


--17번
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 = '최경희');


--18번
SELECT STUDENT_NO, STUDENT_NAME
FROM (SELECT STUDENT_NO, STUDENT_NAME, ROUND(AVG(POINT),2) 평점
      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 평점 DESC)
WHERE ROWNUM = 1;


--19번
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
ORDER BY 1;