[DB]20230807_workbook

Basic SELECT

-- 1. 춘 기술대학교의 학과 이름과 계열을 표시하시오. 단, 출력 헤더는 "학과 명", "계열"으로 표시
SELECT DEPARTMENT_NAME "학과 명", CATEGORY "계열"
FROM TB_DEPARTMENT;

-- 2. 학과의 학과 정원을 다음과 같은 형태로 화면에 출력하시오
SELECT DEPARTMENT_NAME || '의 정원은 ' || CAPACITY || '명 입니다'
FROM TB_DEPARTMENT;

-- 3. "국어국문학과"에 다니는 여학생 중 현재 휴학중인 여학생을 찾아라
SELECT STUDENT_NAME
FROM TB_DEPARTMENT
NATURAL JOIN TB_STUDENT
WHERE DEPARTMENT_NAME = '국어국문학과'
AND ABSENCE_YN = 'Y'
AND SUBSTR(STUDENT_SSN, 8, 1) = '2' ;

-- 4. 도서관에서 대출 도서 장기 연체자들을 찾아 이름을 게시하려한다.
-- 대상자들의 학번이 다음과 같을 때 대상자들을 찾는 구문을 작성해라
-- A513079, A513090, A513091, A513110, A513119
SELECT STUDENT_NAME
FROM TB_STUDENT
WHERE STUDENT_NO IN ('A513079', 'A513090', 'A513091', 'A513110', 'A513119');

-- 5. 입학정원이 20명 이상 30명 이하인 학과들의 학과 이름과 계열을 출력하시오
SELECT DEPARTMENT_NAME, CATEGORY
FROM TB_DEPARTMENT td
WHERE CAPACITY >= 20 AND CAPACITY <=30;

--6. 춘 기술대학교는 총장을 제외하고 모든 교수들이 소속 학과를 가지고 있다.
-- 춘 기술대학교 총장의 이름을 알아내라
SELECT PROFESSOR_NAME
FROM TB_PROFESSOR
WHERE DEPARTMENT_NO IS NULL;

-- 7. 전산상의 착오로 학과가 지정되어있지 않은 학생이 있는지 확인해라.
SELECT STUDENT_NAME
FROM TB_STUDENT ts
WHERE DEPARTMENT_NO IS NULL;

-- 8. 수강신청을 하려한다. 선수과목 여부를 확인해야하는데,
-- 선수과목이 존재하는 과목들은 어떤 과목인지 과목번호를 조회해라
SELECT CLASS_NO
FROM TB_CLASS tc
WHERE PREATTENDING_CLASS_NO IS NOT NULL;

-- 9. 춘 대학에는 어떤 계열이 있는지 조회해라
SELECT DISTINCT CATEGORY
FROM TB_DEPARTMENT td;

-- 10. 02학번 전주 거주자들의 모임을 만들려한다.
-- 휴학한 사람들은 제외한 재학중인 학생들의 학번, 이름, 주민번호를 출력하는 구문을 작성해라
SELECT STUDENT_NO, STUDENT_NAME, STUDENT_SSN
FROM TB_STUDENT
WHERE STUDENT_ADDRESS LIKE '전주%'
AND ABSENCE_YN = 'N'
AND ENTRANCE_DATE BETWEEN '2002-01-01' AND '2002-12-31';

Additional SELECT-함수

--1. 영어영문학과(학과코드 002)학생들의 학번과 이름, 입학년도를
-- 입학년도가 빠른 순으로 표시하는 SQL문장을 작성해라
-- 헤더는 "학번", "이름", "입학년도"로 표기

SELECT STUDENT_NO 학번, STUDENT_NAME 이름, ENTRANCE_DATE 입학년도
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '002'
ORDER BY STUDENT_NO;

--2. 춘 기술대학교의 교수 중 이름이 세글자가 아닌 교수가 한 명 있다.
-- 교수의 이름과 주민번호를 출력해라
SELECT PROFESSOR_NAME, PROFESSOR_SSN
FROM TB_PROFESSOR
WHERE PROFESSOR_NAME NOT LIKE '___';

-- 3. 남자 교수들의 이름과 나이를 출력하는 문장을 작성해라
-- 단, 나이가 적은 사람에서 많은 사람 순서로 화면에 출력해라
-- 2000년 이후 출생자는 없으며, 출력헤더는 "교수이름", "나이"로 출력하고, 나이는 만으로 계산한다.

SELECT PROFESSOR_NAME 교수이름, SUBSTR(PROFESSOR_SSN, 0, 2) -23 나이
FROM TB_PROFESSOR
WHERE SUBSTR( PROFESSOR_SSN, 8 , 1 ) = '1'
ORDER BY PROFESSOR_SSN;

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

-- 5. 춘 기술대학교의 재수생 입학자를 구하려고한다.
-- 단, 19살에 입학하면 재수하지않은 것으로 간주

SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
-- WHERE SUBSTR( ENTRANCE_DATE , 0, 4 ) - SUBSTR( STUDENT_SSN, 0, 2 ) > 19;

-- 6. 2020년 크리스마스는 무슨 요일인가?
-- SELECT TO_DATE('201231', 'YYMMDD (DY)') FROM DUAL;

-- 7. 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')은 각각 몇년 몇월 몇일을 의미하는가
SELECT TO_DATE('99/10/11', 'YY/MM/DD'), TO_DATE('49/10/11', 'YY/MM/DD')
FROM DUAL; -- 2099-10-11 00:00:00.000 / 2049-10-11 00:00:00.000

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

-- 8. 2000년도 이후 입학자들은 학번이 A로 시작하게 되어있다.
-- 2000년도 이전 학번을 받은 학생들의 학번과 이름을 보여달라
SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
WHERE SUBSTR( STUDENT_NO, 0, 1) != 'A';

-- 9. 학번이 A517178인 한아름 학생의 학점 총 평점을 구하는 SQL문을 만드시오
-- 헤더는 "평점"이라고 작성하고, 점수는 반올림하여 소수점 이하 한자리만 표시
SELECT ROUND(AVG(POINT) , 1) 평점
FROM TB_GRADE
WHERE STUDENT_NO = 'A517178';

-- 10. 학과별 학생수를 구하여 "학과번호", "학생수(명)"의 형태로 헤더를 만들어 출력해라
SELECT DEPARTMENT_NO "학과번호", COUNT(*) "학생수(명)"
FROM TB_STUDENT GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO;

-- 11. 지도 교수를 배정받지 못한 학생의 수는 몇 명 정도 되는지 알아내라
SELECT COUNT(*)
FROM TB_STUDENT
WHERE COACH_PROFESSOR_NO IS NULL;

-- 12. 학번이 A112113인 김고운 학생의 년도 별 평점을 구하는 SQL문을 작성해라.
-- 단, 헤더는 "년도", "년도 별 평점"으로 나타내고, 점수는 반올림해 소수점 1자리까지 표시
SELECT SUBSTR(TERM_NO, 0, 4) "년도", ROUND( AVG(POINT), 1) "년도 별 평점"
FROM TB_GRADE
WHERE STUDENT_NO = 'A112113'
GROUP BY SUBSTR(TERM_NO, 0, 4) ;

-- 13. 학과 별 휴학생 수를 파악하고자 한다. 학과번호와 휴학생 수를 나타내라

SELECT DEPARTMENT_NO "학과코드명", COUNT(*) "휴학생 수"
FROM TB_STUDENT
WHERE ABSENCE_YN = 'N'
GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO ;

-- 14. 춘 대학교에 다니는 동명이인 학생들의 이름을 찾아라
SELECT STUDENT_NAME "동일이름", COUNT() "동명인 수"
FROM TB_STUDENT
GROUP BY STUDENT_NAME
HAVING COUNT() > 1
ORDER BY STUDENT_NAME;

-- 15. 학번이 A112113인 김고운 학생의 년도, 학기 별 평점과
-- 년도 별 누적 평점, 총 평점을 구해라. 평점은 소수점 1자리까지 반올림

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

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 = 'N'
ORDER BY STUDENT_SSN DESC;

-- 3. 주소지가 강원도나 경기도인 학생들 중
-- 1990년대 학번을 가진 학생들의 이름과 학번, 주소를
-- 이름의 오름차순으로 화면에 출력.
-- 출력 헤더는 "학생이름" "학번" "거주지 주소"로 출력

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

-- 4. 현재 법학과 교수 중 가장 나이가 많은 사람부터 이름을 확인할 수 있도록 작성해라
-- 법학과의 '학과코드'는 학과테이블(TB_DEPARTMENT)을 조회해서 찾아라
SELECT PROFESSOR_NAME, PROFESSOR_SSN
FROM TB_PROFESSOR
NATURAL JOIN TB_DEPARTMENT
WHERE DEPARTMENT_NAME = '법학과'
ORDER BY PROFESSOR_SSN;

-- 5. 2004년 2학기에 'C3118100' 과목을 수강한 학생들의 학점을 조회해라
-- 학점이 높은 학생부터 표시하고, 학점이 같으면 학번이 낮은 학생부터 표시해라

SELECT STUDENT_NO, POINT
FROM TB_STUDENT
NATURAL JOIN TB_GRADE
WHERE CLASS_NO = 'C3118100'
AND TERM_NO = '200402'
ORDER BY POINT;

-- 6. 학생 번호, 학생 이름, 학과 이름을 학생이름 오름차순 정렬해라
SELECT STUDENT_NO, STUDENT_NAME, DEPARTMENT_NAME
FROM TB_STUDENT
NATURAL JOIN TB_DEPARTMENT
ORDER BY STUDENT_NAME DESC;

-- 7. 춘 기술대학교의 과목 이름과 학과 이름을 출력해라
SELECT CLASS_NAME, DEPARTMENT_NAME
FROM TB_DEPARTMENT
NATURAL JOIN TB_CLASS;

-- 8. 과목별 교수 이름을 찾으려한다. 과목이름과 교수이름을 출력해라
SELECT CLASS_NAME, PROFESSOR_NAME
FROM TB_CLASS
NATURAL JOIN TB_PROFESSOR;

-- 9. 8번의 결과 중 '인문사회' 계열에 속한 과목의 교수 이름을 찾아라
SELECT CLASS_NAME, PROFESSOR_NAME
FROM TB_CLASS
NATURAL JOIN TB_PROFESSOR
NATURAL JOIN TB_DEPARTMENT
WHERE CATEGORY = '인문사회';

-- 10. '음악학과'학생들의 평점을 구하려한다.
-- 학생들의 "학번", "학생 이름", "전체 평점"을 출력해라
-- 평점은 소수점 1자리까지 출력

SELECT STUDENT_NO "학번", STUDENT_NAME "학생 이름", ROUND( AVG(POINT), 1) "전체 평점"
FROM TB_STUDENT
NATURAL JOIN TB_GRADE
NATURAL JOIN TB_DEPARTMENT
WHERE DEPARTMENT_NAME = '음악학과';