SQL01_SELECT(Basic)

SELECT DEPARTMENT_NAME "학과 명", CATEGORY "계열"
FROM TB_DEPARTMENT;

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

SELECT STUDENT_NAME
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '001' 
AND ABSENCE_YN = 'Y'
AND SUBSTR(STUDENT_SSN, 8, 1) = '2' ;

SELECT STUDENT_NAME
FROM TB_STUDENT
WHERE STUDENT_NO = 'A513079' 
OR STUDENT_NO = 'A513090' 
OR STUDENT_NO = 'A513091' 
OR STUDENT_NO = 'A513110' 
OR STUDENT_NO = 'A513119';

SELECT DEPARTMENT_NAME, CATEGORY
FROM TB_DEPARTMENT
WHERE CAPACITY >= 20 AND CAPACITY <= 30;

SELECT PROFESSOR_NAME
FROM TB_PROFESSOR
WHERE DEPARTMENT_NO IS NULL;

SELECT STUDENT_NAME
FROM TB_STUDENT
WHERE DEPARTMENT_NO IS NULL;

SELECT CLASS_NO
FROM TB_CLASS
WHERE PREATTENDING_CLASS_NO IS NOT NULL;

SELECT DISTINCT CATEGORY
FROM TB_DEPARTMENT;

SELECT STUDENT_NO, STUDENT_NAME, STUDENT_SSN
FROM TB_STUDENT
WHERE ABSENCE_YN = 'N' 
AND STUDENT_ADDRESS LIKE '전주%'
AND SUBSTR ( ENTRANCE_DATE, 0, 2) = '02';

---

SQL02_SELECT(Function)

SELECT STUDENT_NO, STUDENT_NAME, ENTRANCE_DATE
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '002'
ORDER BY ENTRANCE_DATE ASC;

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

SELECT PROFESSOR_NAME, EXTRACT(YEAR FROM SYSDATE) - SUBSTR(PROFESSOR_SSN, 0, 2) - 1900 "나이"
FROM TB_PROFESSOR
WHERE SUBSTR(PROFESSOR_SSN, 8, 1) = '1'
ORDER BY EXTRACT(YEAR FROM SYSDATE) - SUBSTR(PROFESSOR_SSN, 0, 2) - 1900;

SELECT SUBSTR(PROFESSOR_NAME, INSTR(PROFESSOR_NAME, ' ') + 2) AS "이름"
FROM TB_PROFESSOR;

SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
WHERE SUBSTR(STUDENT_SSN, 0, 2) != '88';

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

2099-10-11, 2049-10-11
1999-10-11, 2049-10-11

SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
WHERE SUBSTR( STUDENT_NO, 0, 1) != 'A';

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

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

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

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

SELECT D.DEPARTMENT_NO AS "학과코드명", COUNT(S.STUDENT_NO) AS "휴학생 수"
FROM TB_DEPARTMENT D
LEFT JOIN TB_STUDENT S ON D.DEPARTMENT_NO = S.DEPARTMENT_NO AND S.ABSENCE_YN = 'Y'
GROUP BY D.DEPARTMENT_NO
ORDER BY D.DEPARTMENT_NO ASC;

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

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

---

SQL03_SELECT(Option)

SELECT STUDENT_NAME, STUDENT_ADDRESS
FROM TB_STUDENT
ORDER BY STUDENT_NAME ASC;

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

SELECT STUDENT_NAME, STUDENT_NO, STUDENT_ADDRESS
FROM TB_STUDENT
WHERE STUDENT_ADDRESS LIKE '강원도%'
OR STUDENT_ADDRESS LIKE '경기도%'
AND STUDENT_NO LIKE '9%';

SELECT PROFESSOR_NAME, PROFESSOR_SSN 
FROM TB_PROFESSOR
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
WHERE DEPARTMENT_NO = '005'
ORDER BY PROFESSOR_SSN ASC;

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

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

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

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

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

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

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

SELECT STUDENT_NAME, TERM_NO
FROM TB_STUDENT
JOIN TB_GRADE USING(STUDENT_NO)
WHERE CLASS_NO = 'C2604100'
AND TERM_NO LIKE '2007%';

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

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

SELECT STUDENT_NO 학번, STUDENT_NAME 이름, DEPARTMENT_NAME "학과 이름", TRUNC(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 1;

SELECT CLASS_NO, CLASS_NAME, TRUNC(AVG(POINT),8)
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;

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

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 평점 DESC)
WHERE ROWNUM = 1;

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;