[DB] 실습과제4_SELECT

SQL01_SELECT(Basic)

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

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

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

SELECT 
	STUDENT_NAME
FROM 
	TB_STUDENT
WHERE
	STUDENT_NO IN ('A513079', 'A513090', 'A513091', 'A513110', 'A513119')
ORDER BY 
	1 DESC;

SELECT 
	DEPARTMENT_NAME, CATEGORY
FROM
	TB_DEPARTMENT
WHERE 
	CAPACITY BETWEEN 20 AND 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
ORDER BY 1;

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

SQL02_SELECT(Function)

SELECT 
	STUDENT_NO 학번,
	STUDENT_NAME 이름,
	TO_CHAR(ENTRANCE_DATE, 'YYYY-MM-DD') 입학년도
FROM 
	TB_STUDENT
WHERE
	DEPARTMENT_NO = '002'
ORDER BY 입학년도;

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

★

SELECT 
	PROFESSOR_NAME 교수이름,
    TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19' || SUBSTR(PROFESSOR_SSN, 1, 6), 'RRMMDD')) / 12) 나이
FROM 
	TB_PROFESSOR
WHERE 1 = 1
  AND SUBSTR(PROFESSOR_SSN, 8, 1) = '1'
ORDER BY
	나이, 교수이름;

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

★

SELECT 
	STUDENT_NO,
	STUDENT_NAME,
	TRUNC(MONTHS_BETWEEN(ENTRANCE_DATE,TO_DATE('19' || SUBSTR(STUDENT_SSN,1,6),'YYYYMMDD')) / 12) + 1
FROM
	TB_STUDENT
WHERE 
	TRUNC(MONTHS_BETWEEN(ENTRANCE_DATE,TO_DATE('19' || SUBSTR(STUDENT_SSN,1,6),'YYYYMMDD')) / 12) + 1 > 19;

SELECT 
	TO_CHAR(TO_DATE('2020-12-25', 'YYYY-MM-DD'), 'DAY')
FROM
	DUAL;

SELECT 
	TO_DATE('99/10/11', 'YY/MM/DD'), -- 2099-10-11
	TO_DATE('49/10/11', 'YY/MM/DD'), -- 2049-10-11
	TO_DATE('99/10/11', 'RR/MM/DD'), -- 1999-10-11
	TO_DATE('49/10/11', 'RR/MM/DD')  -- 2049-10-11
FROM
	DUAL;

SELECT
	STUDENT_NO, STUDENT_NAME
FROM
	TB_STUDENT
WHERE 
	ENTRANCE_DATE < TO_DATE('2000-01-01', 'YYYY-MM-DD');

SELECT 
	ROUND(SUM(POINT) / COUNT(POINT), 1) 평점
--	ROUND(AVG(POINT), 1) 평점
FROM 
	TB_GRADE 
WHERE 
	STUDENT_NO = 'A517178'
GROUP BY
	STUDENT_NO

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

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

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 
	DEPARTMENT_NO 학과코드명,
	SUM(DECODE(ABSENCE_YN, 'Y', 1, 0)) "휴학생 수"
FROM
	TB_STUDENT
GROUP BY 
	DEPARTMENT_NO
ORDER BY 1;

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

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, 2;

SQL03_SELECT(Option)

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

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 SUBSTR(STUDENT_NO, 1, 1) = '9'
ORDER BY 1;

SELECT 
	PROFESSOR_NAME,
	PROFESSOR_SSN
FROM
	TB_PROFESSOR
WHERE
	DEPARTMENT_NO = '005'
ORDER BY
	PROFESSOR_SSN;

SELECT 
	STUDENT_NO,
	TO_CHAR(POINT, '0.00') "POINT"
FROM 
	TB_GRADE
WHERE 1 = 1
  AND TERM_NO = '200402'
  AND CLASS_NO = 'C3118100'
ORDER BY
	POINT DESC, STUDENT_NO;

SELECT 
	A.STUDENT_NO,
	A.STUDENT_NAME,
	B.DEPARTMENT_NAME
FROM 
	TB_STUDENT A,
	TB_DEPARTMENT B
WHERE 1 = 1
  AND A.DEPARTMENT_NO = B.DEPARTMENT_NO;

SELECT 
	A.CLASS_NAME,
	B.DEPARTMENT_NAME
FROM
	TB_CLASS A,
	TB_DEPARTMENT B
WHERE 1 = 1
  AND A.DEPARTMENT_NO = B.DEPARTMENT_NO;

SELECT 
	A.CLASS_NAME,
	B.PROFESSOR_NAME
FROM
	TB_CLASS A,
	TB_PROFESSOR B,
	TB_CLASS_PROFESSOR C
WHERE 1 = 1
  AND A.CLASS_NO = C.CLASS_NO
  AND C.PROFESSOR_NO = B.PROFESSOR_NO;

SELECT 
	A.CLASS_NAME,
	B.PROFESSOR_NAME
FROM
	TB_CLASS A,
	TB_PROFESSOR B,
	TB_CLASS_PROFESSOR C,
	TB_DEPARTMENT D
WHERE 1 = 1
  AND A.CLASS_NO = C.CLASS_NO
  AND C.PROFESSOR_NO = B.PROFESSOR_NO
  AND A.DEPARTMENT_NO = D.DEPARTMENT_NO
  AND D.CATEGORY = '인문사회';

SELECT 
	A.STUDENT_NO 학번,
	A.STUDENT_NAME "학생 이름",
	ROUND(AVG(B.POINT), 1) "전체 평점"
FROM
	TB_STUDENT A,
	TB_GRADE B
WHERE 1 = 1
  AND A.STUDENT_NO = B.STUDENT_NO
  AND A.DEPARTMENT_NO = '059'
GROUP BY 
	A.STUDENT_NO,
	A.STUDENT_NAME
ORDER BY 1;

SELECT 
	B.DEPARTMENT_NAME 학과이름,
	A.STUDENT_NAME 학생이름,
	C.PROFESSOR_NAME 지도교수이름
FROM 
	TB_STUDENT A,
	TB_DEPARTMENT B,
	TB_PROFESSOR C
WHERE 1 = 1
  AND A.STUDENT_NO = 'A313047'
  AND A.DEPARTMENT_NO = B.DEPARTMENT_NO
  AND A.COACH_PROFESSOR_NO = C.PROFESSOR_NO;

SELECT 
	A.STUDENT_NAME,
	B.TERM_NO "TERM_NAME"
FROM
	TB_STUDENT A,
	TB_GRADE B,
	TB_CLASS C
WHERE 1 = 1
  AND SUBSTR(TERM_NO, 1, 4) = '2007'
  AND A.STUDENT_NO = B.STUDENT_NO
  AND B.CLASS_NO = C.CLASS_NO
  AND C.CLASS_NO = 'C2604100'
ORDER BY 1;