실습문제 풀이 복습

기초

SELECT * 
FROM EMPLOYEE
WHERE BONUS IS NOT NULL;

--2. 
SELECT * 
FROM EMPLOYEE
WHERE BONUS IS NULL;

--3.
SELECT *
FROM EMPLOYEE
WHERE MANAGER_ID IS NULL;

--4. 
SELECT *
FROM EMPLOYEE
ORDER BY SALARY DESC;

--5.
SELECT *
FROM EMPLOYEE
ORDER BY SALARY DESC, BONUS DESC;

--6.
SELECT EMP_ID, EMP_NAME, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
ORDER BY HIRE_DATE;

--7. 
SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE
ORDER BY EMP_ID DESC;

--8
SELECT EMP_ID, HIRE_DATE, EMP_NAME, SALARY -- , DEPT_CODE
FROM EMPLOYEE
ORDER BY DEPT_CODE ASC, HIRE_DATE DESC;

--9.
SELECT SYSDATE FROM DUAL;

--10. 단, 급여는 100만원단위까지의 값만 출력 처리하고 급여 기준 내림차순 정렬) 
SELECT EMP_ID, EMP_NAME, FLOOR(SALARY / 10000) || '만원' AS 급여 
FROM EMPLOYEE 
ORDER BY SALARY DESC;

--11. 사원테이블에서 사원번호가 홀수인 사원들을 조회
SELECT *
FROM EMPLOYEE
WHERE MOD(EMP_ID,2) = 1; -- EMP_ID % 2 == 1

--12. 
SELECT EMP_NAME, HIRE_DATE , EXTRACT(YEAR FROM HIRE_DATE) 년, EXTRACT(MONTH FROM HIRE_dATE) 월
FROM EMPLOYEE;

--13. 
SELECT *
FROM EMPLOYEE
WHERE 9 = EXTRACT(MONTH FROM HIRE_DATE);

--14.
SELECT *
FROM EMPLOYEE
WHERE 1990 = EXTRACT(YEAR FROM HIRE_DATE);

--15. 
SELECT *
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%리';

--16.1
SELECT *
FROM EMPLOYEE 
-- WHERE EMP_NAME LIKE '_이_';
--16.2
WHERE SUBSTR(EMP_NAME,2,1) ='이';

--17
SELECT EMP_ID, EMP_NAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE, 12 * 40) 
FROM EMPLOYEE;

--18
SELECT *
FROM EMPLOYEE
--WHERE ADD_MONTHS(SYSDATE, -12 * 18) >= HIRE_DATE;
-- WHERE FLOOR (MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) >= 216;
WHERE (EXTRACT(YEAR FROM HIRE_DATE) + 18) <= 2024;

--19
SELECT EXTRACT(YEAR FROM SYSDATE)
FROM DUAL;

kh실습문제 2 SELECT & FUNCTION

--1. JOB  테이블의  모든  정보  조회 
SELECT * FROM JOB;
--2. JOB  테이블의  직급  이름  조회 
SELECT JOB_NAME FROM JOB;

--3. DEPARTMENT  테이블의  모든  정보  조회 
SELECT * FROM DEPARTMENT;

--4. EMPLOYEE테이블의  직원명,  이메일,  전화번호,  고용일  조회 
SELECT EMP_NAME, EMAIL, PHONE, HIRE_DATE FROM EMPLOYEE;

--5. EMPLOYEE테이블의  고용일,  사원  이름,  월급  조회 
SELECT HIRE_DATE, EMP_NAME, SALARY FROM EMPLOYEE;

--6. EMPLOYEE테이블에서  이름,  연봉,  총수령액(보너스포함),  실수령액(총수령액  - (연봉*세금  3%))  조회 
SELECT EMP_NAME,
       SALARY * 12 AS 연봉,
       (SALARY + SALARY * NVL(BONUS,0) ) * 12 AS 총수령액,
       (SALARY + SALARY * NVL(BONUS,0) ) * 12 - ( SALARY * 12 * 0.03 ) AS 실수령액 
FROM EMPLOYEE;
--7. EMPLOYEE테이블에서  SAL_LEVEL이  S1인  사원의  이름,  월급,  고용일,  연락처  조회 
SELECT EMP_NAME, SALARY, HIRE_DATE, PHONE
FROM EMPLOYEE
WHERE SAL_LEVEL = 'S1';
--8. EMPLOYEE테이블에서  실수령액(6번  참고)이  5천만원  이상인  사원의  이름,  월급,  실수령액,  고용일  조회 
SELECT EMP_NAME, 
       SALARY, 
       (SALARY + SALARY * NVL(BONUS,0) ) * 12 - ( SALARY * 12 * 0.03 ) AS 실수령액 ,
       HIRE_DATE
FROM EMPLOYEE
WHERE (SALARY + SALARY * NVL(BONUS,0) ) * 12 - ( SALARY * 12 * 0.03 ) >= 50000000;

--9. EMPLOYEE테이블에  월급이  4000000이상이고  JOB_CODE가  J2인  사원의  전체  내용  조회 
SELECT *
FROM EMPLOYEE
WHERE SALARY >= 4000000 AND JOB_CODE = 'J2';
--10. EMPLOYEE테이블에  DEPT_CODE가  D9이거나  D5인  사원  중   
--    고용일이  02년  1월  1일보다  빠른  사원의  이름,  부서코드,  고용일  조회 
SELECT EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE 
WHERE (DEPT_CODE = 'D9' OR DEPT_CODE ='D5') AND HIRE_DATE > '02/01/01';

--11. EMPLOYEE테이블에  고용일이  90/01/01 ~ 01/01/01인  사원의  전체  내용을  조회 
SELECT * 
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN '90/01/01' AND '01/01/01';

--12. EMPLOYEE테이블에서  이름  끝이  '연'으로  끝나는  사원의  이름  조회 
SELECT EMP_NAME
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%연';

--13. EMPLOYEE테이블에서  전화번호  처음  3자리가  010이  아닌  사원의  이름,  전화번호를  조회
SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE NOT LIKE '010%';

--14. EMPLOYEE테이블에서  메일주소  '_'의  앞이  4자이면서  DEPT_CODE가  D9  또는  D6이고   
--    고용일이  90/01/01 ~ 00/12/01이고,  급여가  270만  이상인  사원의  전체를  조회 
SELECT *
FROM EMPLOYEE
WHERE EMAIL LIKE '____#_%' ESCAPE '#'
  AND DEPT_CODE IN ('D9', 'D6')
  AND HIRE_DATE BETWEEN '90/01/01' AND '00/12/01'
  AND SALARY >= 2700000;

--15. EMPLOYEE테이블에서  사원  명과  직원의  주민번호를  이용하여  생년,  생월,  생일  조회
SELECT EMP_NAME, EMP_NO, SUBSTR(EMP_NO,1,2) 생년, SUBSTR(EMP_NO,3,2) 생월, SUBSTR(EMP_NO,5,2) 생일
FROM EMPLOYEE;

--16. EMPLOYEE테이블에서  사원명,  주민번호  조회  (단,  주민번호는  생년월일만  보이게  하고, '-'다음  값은  '*'로  바꾸기) 
SELECT EMP_NAME, SUBSTR(EMP_NO,1,6) || '-*******' AS 주민번호
FROM EMPLOYEE;

--17. EMPLOYEE테이블에서  사원명,  입사일-오늘,  오늘-입사일  조회 
--    (단,  각  별칭은  근무일수1,  근무일수2가  되도록  하고  모두  정수(버림),  양수가  되도록  처리) 
SELECT EMP_NAME, ABS(FLOOR(HIRE_DATE - SYSDATE)) "근무일수1" , ABS(FLOOR(SYSDATE - HIRE_DATE)) "근무일수2"
FROM EMPLOYEE;

--18. EMPLOYEE테이블에서  사번이  홀수인  직원들의  정보  모두  조회
SELECT *
FROM EMPLOYEE
WHERE MOD(EMP_ID , 2 ) = 1;

--19. EMPLOYEE테이블에서  근무  년수가  20년  이상인  직원  정보  조회 
SELECT * 
FROM EMPLOYEE
WHERE  ADD_MONTHS(HIRE_DATE , 12 * 20) <= SYSDATE;

--20. EMPLOYEE  테이블에서  사원명,  급여  조회  (단,  급여는  '\9,000,000'  형식으로  표시) 
SELECT EMP_NAME, TO_CHAR(SALARY,'L999,999,999')
FROM EMPLOYEE;

--21. EMPLOYEE테이블에서  직원  명,  부서코드,  생년월일,  나이(만)  조회 
--    (단,  생년월일은  주민번호에서  추출해서  00년  00월  00일로  출력되게  하며   
--    나이는  주민번호에서  출력해서  날짜데이터로  변환한  다음  계산) 
SELECT  EMP_NAME, 
        DEPT_CODE, 
        SUBSTR(EMP_NO,1,2) || '년 ' || SUBSTR(EMP_NO,3,2) || '월 ' || SUBSTR(EMP_NO,5,2) || '일' 생년월일, -- CHAR -> DATE -> CHAR
        EXTRACT(YEAR FROM SYSDATE) 현재년도,
        EMP_NO,
        EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,6),'RRMMDD')) 출생년도,
        EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,6),'RRMMDD'))
        나이,
        FLOOR((MONTHS_BETWEEN (SYSDATE, TO_DATE(SUBSTR(EMP_NO,1,6), 'RRMMDD' ) ) ) / 12) AS "나이(만)"
FROM EMPLOYEE;

--22. EMPLOYEE테이블에서  부서코드가  D5, D6, D9인  사원만 조회하되  D5면  총무부, D6면  기획부, D9면  영업부로  처리 
--    (단,  부서코드  오름차순으로  정렬) 
--    사번, 이름, 부서, 부서명
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DECODE(DEPT_CODE, 'D5','총무부','D6' , '기획부', '영업부') 부서명
FROM EMPLOYEE
WHERE DEPT_CODE IN ('D5','D6','D9')
ORDER BY DEPT_CODE;

--23. EMPLOYEE테이블에서  사번이  201번인  사원명,  주민번호  앞자리,  주민번호  뒷자리,   
--    주민번호  앞자리와  뒷자리의  합  조회 
SELECT EMP_NAME, 
       SUBSTR(EMP_NO,1,6) "주민번호 앞자리",
       SUBSTR(EMP_NO,8) "주민번호 뒷자리",
       SUBSTR(EMP_NO,1,6) + SUBSTR(EMP_NO,8) "주민번호 합"
FROM EMPLOYEE
WHERE EMP_ID =201
;
--24. EMPLOYEE테이블에서  부서코드가  D5인  직원의  보너스  포함  연봉  합  조회 
SELECT SUM((SALARY + SALARY * NVL(BONUS,0))*12) 끝 
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'; -- 198060000

--25. EMPLOYEE테이블에서  직원들의  입사일로부터  년도만  가지고  각  년도별  입사  인원수  조회 
--    전체  직원  수, 2001년, 2002년, 2003년, 2004년
SELECT 
    COUNT(*) "전체 직원 수",
    COUNT( DECODE(EXTRACT(YEAR FROM HIRE_DATE), 2001, 1, NULL)) "2001년 입사자 수" ,
    COUNT( DECODE(EXTRACT(YEAR FROM HIRE_DATE), 2002, 1, NULL)) "2002년 입사자 수" ,
    COUNT( DECODE(EXTRACT(YEAR FROM HIRE_DATE), 2003, 1, NULL)) "2003년 입사자 수" ,
    COUNT( DECODE(EXTRACT(YEAR FROM HIRE_DATE), 2004, 1, NULL)) "2004년 입사자 수" 
 -- 전체  직원  수, 2001년, 2002년, 2003년, 2004년
FROM EMPLOYEE;

워크북 실습문제 1

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

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

--3. "국어국문학과"에 다니는 여학생 중 현재 휴학중인 여학생을 찾아 달라는 요청이 들어왔다
--누구인가?( 국문학과의' 학과코드'는 학과 테이블(TB_DEPARTEENT)을 조회해서 찾아내도록 하자)
SELECT STUDENT_NAME
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '001'
  AND SUBSTR(STUDENT_SSN, 8, 1) IN (2,4)
  AND ABSENCE_YN = 'Y';

--4 도서관에서 대출 도서 장기 연체자 들을 찾아 이름을 계시하고자 한다.
--그 대상자들의 학번이 다음과 같을 때 대상자들을 찾는 적절한 SQL구문을 작성 하시오
--'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
WHERE CAPACITY BETWEEN 20 AND 30;

--6 춘 기술대학교는 총장을 제외하고 모든 교수들이 소속 학과를 가지고 있다
--그럼 춘 기술 대학교 총장의 이름을 알아 낼수 있는 SQL문장을 작성 하시오
SELECT PROFESSOR_NAME
FROM TB_PROFESSOR
WHERE DEPARTMENT_NO IS NULL;

--7 혹시 전상상의 착오로 학과가 지정되어 있지 않은 학생이 있는지 확인하고자 한다. 
--어떠한 SQL 문장을 사용하면 될 것인지 작성 하시오
SELECT *
FROM TB_STUDENT
WHERE DEPARTMENT_NO IS NULL;

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

--9 춘 대학에서 어떤 계열(CATEGORY)들이 있는지 조회 해보시오
SELECT DISTINCT CATEGORY
FROM TB_DEPARTMENT;

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

workbook 실습문제 2

-- 1 영어영문학과 (학과코드 002) 
-- 학생들의 학번과 이름, 입학 년도를 입학년도가 빠르 순으로 표시하는 SQL 문작을 작성 하시오.
-- (단, 헤더는 "학번", "이름","입학년도" 가 표시되도록 한다.)

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


-- 2 춘 기술대학교의 교수 중 이름이 세 글자가 아닌 교수가 한 명 있다고 한다
-- 그교수의 이름과 주민번호를 화면에 출력하는 SQL 문장을 작성해 보자
--(이때 올바르게 작성한 SQL 문장의 결과 값이 예상과 다르게 나올수 있다)
 
SELECT PROFESSOR_NAME, PROFESSOR_SSN
FROM TB_PROFESSOR
--WHERE PROFESSOR_NAME NOT LIKE '___';
WHERE LENGTH(PROFESSOR_NAME) != 3;



-- 3 춘 기술대학교의 남자 교수들의 이름과 나이를 출력하는 SQL문장을 작성하시오
-- 단 이때 나이가 적은 사람에서 많은 사람 순서로 화면에 출력되도록 만드시오
--(단, 교수 중 2000년 이후 출생자는 없으며 출력 헤더는 "교수이름""나이"로 한다)
--(나이는 '만' 으로 계산한다)
-- 나이구하는법
-- 오늘날짜 - 태어난 년도(주민번호) 
SELECT PROFESSOR_NAME 교수이름 
     , EXTRACT(YEAR FROM SYSDATE)
     , EXTRACT(YEAR FROM  TO_DATE(19 || SUBSTR(PROFESSOR_SSN,1,2),'YYYY' )) 교수출생년도
     , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM  TO_DATE( 19 || SUBSTR(PROFESSOR_SSN,1,2),'YYYY' )) 나이
     , FLOOR(MONTHS_BETWEEN(SYSDATE , TO_DATE( 19 || SUBSTR(PROFESSOR_SSN,1,6),'YYYYMMDD' )) / 12 ) 만나이
FROM TB_PROFESSOR
WHERE SUBSTR(PROFESSOR_SSN,8,1) = 1
ORDER BY 5;




-- 4 교수들의 이름 중 성을 제외한 이름만 출력하는 SQL 문장을 작성하시오. 
-- 출력 헤더는 "이름" 이 찍히도록 한다. (성이 2자인 경우는 교수는 없다고 가정)

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



-- 5 춘 기술대학교의 재수생 입학자를 구하려고 한다. 어떻게 찾아낼 것인가?
-- 이때, 19살에 입학하면 재수를 하지 않은 것으로 간주한다.
-- 입학년도의 나이가 19살이면 재수 X
-- 19살보다 많으면 재수 O
SELECT
    STUDENT_NO, 
    STUDENT_NAME,
    EXTRACT(YEAR FROM ENTRANCE_DATE) - EXTRACT(YEAR FROM TO_DATE(SUBSTR(STUDENT_SSN,1,2),'RR')) ,
    CEIL(MONTHS_BETWEEN(ENTRANCE_DATE, TO_DATE( SUBSTR(STUDENT_SSN,1,6),'RRMMDD')) / 12) +1
FROM TB_STUDENT
WHERE EXTRACT(YEAR FROM ENTRANCE_DATE) - EXTRACT(YEAR FROM TO_DATE(SUBSTR(STUDENT_SSN,1,2),'RR')) > 19;
     -- (TO_CHAR(ENTRANCE_DATE, 'YYYY') - TO_CHAR('19' || SUBSTR(STUDENT_SSN, 1, 2))) > '19';

 -- 6 2020년 크리스마스는 무슨 요일인가?

SELECT  TO_CHAR(TO_DATE('20201225','YYYYMMDD'),'DAY') 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') 은 각각 몇년 몇월 몇일을 의미할까?

-- 1. 2099 10 11
-- 2. 2049 10 11
-- 3. 1999 10 11
-- 4. 2049 10 11


-- 8춘 기술대학교의 2000년도 이후 입학자들은 학번이 A로 시작하게 되어있다
-- 2000년도 이전 학번을 받은 학생들의 학번과 이름을 보여주는 SQL 문장을 작성하시오

SELECT STUDENT_NO,
       STUDENT_NAME
FROM TB_STUDENT
WHERE SUBSTR(STUDENT_NO, 1,1 ) != 'A';
-- WHERE STUDENT_NO NOT LIKE 'A%';

--9 학번이 A517178 인 한아름 학생의 학점 총 평점을 구하는 SQL문을 작성하시오
-- 단 이때 출력 화면의 헤더는 "평점"이라고 하고 점수는 반올림하여 소수점 이하 한자리 까지만 표시

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


-- 10 학과별 학생수를 구하여 "학과번호", "학생수(명)" 의 형태로 헤더를 만들어 결과값 출력

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


-- 11 지도 교수를 배정받지 못한 삭생의 수는 몇 명 정도 되는지 알아내는 SQL문 작성

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

-- 12 학번이 A112113인 김고운 학생의 년도 별 평점을 구하는SQL문을 작성
-- 단 이떄 출력 헤더는 "년도", "년도 별 평점" 이라 하고, 
-- 점수는 반올림하여 소수점 이하 한 자리까지만 표시한다.

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


-- 13 학과 별 휴학생 수를 파악하고자 한다. 학과 번호와 휴학생 수를 표시하는 SQL 작성

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


-- 14 춘 대학교에 다니는 동명이인 학생들의 이름을 찾는 SQL 문장을 작성

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

-- 15 학번이 A112113인 김고운 학생의 년도, 학기별 평점과 년도 별 누적 평점, 총 평점을 
-- 구하는 SQL 문을 작성(단, 평점은 소수점 1자리까지만 반올림하여 표시)

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

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;

WORKBOOK 실습문제 3

-- 1. 학생이름과 주소지를 표시하시오. 단, 출력 헤더는 "학생이름", "주소지"로 하고
-- (이름으로 오름차순 정렬)
SELECT STUDENT_NAME, STUDENT_ADDRESS
FROM TB_STUDENT
ORDER BY 1;

-- 2. 휴학중인 학생들의 이름과 주민번호를 나이가 적은 순서로 화면에 출력하시오.
SELECT STUDENT_NAME, STUDENT_SSN
FROM TB_STUDENT 
WHERE ABSENCE_YN = 'Y'
ORDER BY 2 DESC;

-- 3. 주소지가 강원도나 경기도인 학생들 중 1990년대 학번을 가진 학생들의 이름과 학번 주소를 출력
-- (이름의 오름차순 정렬, 출력헤더는 "학생이름", "학번","거주지 주소" 로 한다)
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 1;


-- 4. 현재 법학과 교수 중 가장 나이가 많은 사람부터 이름을 확인하는 SQL문장 작성
-- (법학과의 '학과코드'는 학과 테이블(TB_DEPARTMENT)을 조회해서 찾으시오)
SELECT 
    PROFESSOR_NAME,
    PROFESSOR_SSN
FROM TB_PROFESSOR 
WHERE DEPARTMENT_NO = (
    SELECT DEPARTMENT_NO FROM tb_department 
    WHERE DEPARTMENT_NAME = '법학과'
)
ORDER BY 2;

-- 5. 2004년 2학기에 'C3118100' 과목을 수강한 학생들의 학점을 조회하시오
-- 학점이 높은순으로 표시하고 학점이 같으면 학번이 낮은 학생부터 표시하는 구문을 작성하세요
SELECT STUDENT_NO , 
       POINT
FROM TB_GRADE 
WHERE CLASS_NO = 'C3118100'
  AND TERM_NO ='200402'
ORDER BY 2 DESC , 1
;

-- 6. 학생 번호, 학생 이름, 학과 이름을 학생 이름으로 오름차순 정렬하여 출력하는 SQL문 작성하시오
SELECT
    STUDENT_NO,
    STUDENT_NAME,
    DEPARTMENT_NAME
FROM TB_STUDENT 
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO);

-- 7. 춘 기술대학교의 과목이름과 과목의 학과 이름을 출력하는 SQL 문장을 작성하시오.
SELECT CLASS_NAME, DEPARTMENT_NAME
FROM TB_CLASS
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO);

-- 8. 과목별 교수 이름을 찾아 과목이름과 교수이름을 출력하는 SQL 문을 작성하시오.
SELECT CLASS_NAME, PROFESSOR_NAME
FROM TB_CLASS 
JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
JOIN TB_PROFESSOR USING(PROFESSOR_NO);

-- 9. 8번의 결과 중 '인문사회' 계열에 속한 과목의  교수 이름을 찾으려고 한다
-- 이에 해당하는 과목 이름과 교수 이름을 출력하는 SQL 문을 작성하시오.
SELECT CLASS_NAME, PROFESSOR_NAME
FROM TB_CLASS C
JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
JOIN TB_PROFESSOR  USING(PROFESSOR_NO)
JOIN TB_DEPARTMENT D ON C.DEPARTMENT_NO = D.DEPARTMENT_NO
WHERE CATEGORY = '인문사회';

-- 10. '음악학과' 학생들의 평점을 구하려고 한다, 음악학과 학생들의 "학번", "학생 이름", "전체 평점"을 
-- 출력하는 SQL 문장을 작성하시오.(단, 평점은 소수점 1자리까지만 반올림하여 표시한다)
SELECT STUDENT_NO, STUDENT_NAME , ROUND(AVG(POINT),1) 전체평점
FROM TB_STUDENT
JOIN TB_GRADE USING(STUDENT_NO)
WHERE DEPARTMENT_NO = (SELECT DEPARTMENT_NO FROM TB_DEPARTMENT WHERE DEPARTMENT_NAME ='음악학과')
GROUP BY STUDENT_NO, STUDENT_NAME;

-- 11. 학번이 A313047 인 학생이 학교에 나오지 않고 있다.
-- 지도교수에게 내용을 전달하기 위한 학과이름, 학생 이름과 지도 교수 이름이 필요하다
-- 출력헤더는 "학과이름", "학생이름", "지도교수이름" 으로 출력되는 SQL문을 작성 하시오
SELECT DEPARTMENT_NAME , STUDENT_NAME, PROFESSOR_NAME
FROM TB_STUDENT
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
JOIN TB_PROFESSOR ON PROFESSOR_NO = COACH_PROFESSOR_NO 
WHERE STUDENT_NO = 'A313047';

-- 12. 2007년도에 '인간관계론' 과목을 수강한 학생을 찾아 학생이름과 수강학기를 표시하는 SQL 문장을 작성하시오
SELECT STUDENT_NAME , TERM_NO
FROM TB_STUDENT 
JOIN TB_GRADE USING(STUDENT_NO)
JOIN TB_CLASS USING(CLASS_NO)
WHERE CLASS_NAME ='인간관계론'
  AND SUBSTR(TERM_NO,1,4) = '2007';

-- 13. 예체능 계열 과목 중 과목 담당교수를 한 명도 배정받지 못한 과목을 찾아 그과목 이름과 학과 이름을 출력하는 SQL 문장을 작성하시오
SELECT CLASS_NAME , DEPARTMENT_NAME
FROM TB_CLASS 
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
LEFT JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
LEFT JOIN TB_PROFESSOR USING(PROFESSOR_NO)
WHERE CATEGORY = '예체능'
  AND PROFESSOR_NO IS NULL;

-- 14. 춘 기술대학교 서반아어학과 학생들의 지도교수를 게시하고자 한다.
-- 학생이름과 지도교수 이름을 찾고 만일 지도 교수가 없는 학생일 경우 "지도교수 미지정"으로 표시하는 SQL문을 작성하시오
-- 단, 출력헤더는 "학생이름", "지도교수" 로 표시하며 고학번 학생이 먼저 표시되도록 한다.
SELECT STUDENT_NAME, NVL(PROFESSOR_NAME, '지도교수 미지정')
FROM TB_STUDENT S
LEFT JOIN TB_PROFESSOR P ON (COACH_PROFESSOR_NO = PROFESSOR_NO)
JOIN TB_DEPARTMENT D ON D.DEPARTMENT_NO = S.DEPARTMENT_NO
WHERE DEPARTMENT_NAME ='서반아어학과';

-- 15. 휴학생이 아닌 학생 중 평점이 4.0 이상인 학생을 찾아 그 학생의 학번, 이름, 학과 이름, 평점을 출력하는 SQL문을 작성하시오
SELECT STUDENT_NO, STUDENT_NAME, DEPARTMENT_NAME, AVG(POINT)
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.0;

-- 16. 환경조경학과 전공과목들의 과목 별 평점을 파악 하는 SQL문을 작성하시오
SELECT CLASS_NO,
       CLASS_NAME,
       AVG(POINT)
FROM TB_CLASS
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
JOIN TB_GRADE USING(CLASS_NO)
WHERE DEPARTMENT_NAME ='환경조경학과' 
  AND CLASS_TYPE LIKE '전공%'
GROUP BY CLASS_NO, CLASS_NAME;

-- 17. 춘 기술대학교에 다니고 있는 최경희 학생과 같은 과 학생들의 이름과 주소를 출력하는 SQL문을 작성하시오
SELECT STUDENT_NAME , STUDENT_ADDRESS
FROM TB_STUDENT 
WHERE DEPARTMENT_NO = (SELECT DEPARTMENT_NO FROM TB_STUDENT WHERE STUDENT_NAME='최경희');

-- 18 국어국문학과에서 총 평점이 가장 높은 학생의 이름과 학번을 표시하는 SQL문을 작성하시오
SELECT STUDENT_NO, STUDENT_NAME 
FROM (
    SELECT STUDENT_NO , STUDENT_NAME , AVG(POINT)
    FROM TB_STUDENT
    JOIN TB_GRADE USING(STUDENT_NO)
    WHERE DEPARTMENT_NO = (SELECT DEPARTMENT_NO FROM TB_DEPARTMENT WHERE DEPARTMENT_NAME = '국어국문학과')
    GROUP BY STUDENT_NO ,STUDENT_NAME
    ORDER BY 3 DESC
) E
WHERE ROWNUM = 1;

-- 19. 춘 기술대학교의 "환경조경학과" 가 속한 같은 계열 학과들의 학과 별 전공과목 편점을 파악하는 SQL 문을 작성하시오
-- 단, 출력헤더는 "계열 학과명", "전공평점" 으로 표시되도록 하고, 평점은 소수점 한자리까지 반올림하여 표시하도록 한다.
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;