localhost_STUDENT_220303.sql

-- 교수테이블에서소속학과에따라보너스를다르게계산하여t력하여라. 학과번호별로보너스는다음과같이계산™다.
-- 학과번호가101이면보너스는급여의10%, 102이면20%, 201이면30%, 나머지학과는0%이다.
SELECT * FROM PROFESSOR;
SELECT PROFNO, NAME, SAL, DEPTNO FROM PROFESSOR;
SELECT
    PROFNO, NAME, SAL, DEPTNO,
    DECODE(DEPTNO,
        101, SAL * 0.1,
        102, SAL * 0.2,
        201, SAL * 0.3,
        0) BOUNUS,
        CASE 
            WHEN DEPTNO = 101 THEN SAL * 0.1
            WHEN DEPTNO = 102 THEN SAL * 0.2
            WHEN DEPTNO = 201 THEN SAL * 0.3
            ELSE 0
        END BOUNUS2,
        --같은 내용
        CASE DEPTNO
            WHEN 101 THEN SAL * 0.1
            WHEN 102 THEN SAL * 0.2
            WHEN 201 THEN SAL * 0.3
            ELSE 0
        END BOUNUS3
FROM PROFESSOR;

SELECT STUDNO, NAME, BIRTHDATE,
    TO_CHAR(BIRTHDATE,'MM') M,
CASE
        WHEN TO_CHAR(BIRTHDATE,'MM') IN (3, 4, 5) THEN '봄'
        WHEN TO_CHAR(BIRTHDATE,'MM') BETWEEN 6 AND 8 THEN '여름'
        WHEN TO_CHAR(BIRTHDATE,'MM') BETWEEN 9 AND 11 THEN '가을'
        ELSE '겨울'
END SEASON
FROM STUDENT;

SELECT
    STUDNO, NAME,
    CASE
        WHEN TO_CHAR(BIRTHDATE,'MM') IN (3, 4, 5) THEN '봄'
        WHEN TO_CHAR(BIRTHDATE,'MM') BETWEEN 6 AND 8 THEN '여름'
        WHEN TO_CHAR(BIRTHDATE,'MM') BETWEEN 9 AND 11 THEN '가을'
        ELSE '겨울'
    END SEASON
FROM ( SELECT STUDNO, NAME, BIRTHDATE,
    TO_CHAR(BIRTHDATE,'MM') M
FROM STUDENT
);

SELECT STUDNO, NAME, USERID
FROM STUDENT
WHERE USERID >= 'DDDD';


-- SUM AVG MAX MIN COUNT
SELECT DEPTNO, COUNT(DEPTNO)
FROM STUDENT
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO
HAVING COUNT(DEPTNO) <= 5;

--GROUP은 묶는 거라 COUNT 가능한데, DISTINCT는 중복값 제거라 카운트 불가
--SELECT DISTINCT DEPTNO, COUNT(DEPTNO)
--FROM STUDENT;  

SELECT COUNT(DEPTNO)
FROM STUDENT;

-- 101번학과교수중에서보직수당을받는교수의수를t력하여라.
SELECT * FROM PROFESSOR;
-- 개수는 NULL을 제외하고 카운트 해줌
SELECT COUNT(COMM), COUNT(*)
FROM PROFESSOR
WHERE DEPTNO = 101;

SELECT COUNT(*) 
FROM PROFESSOR
WHERE DEPTNO = 101 AND COMM IS NOT NULL;

-- 101번학과학생들의몸무게평균과합계를t력하여라.
SELECT * FROM STUDENT;
SELECT 
    AVG(WEIGHT), SUM(WEIGHT), SUM(WEIGHT) / COUNT(WEIGHT) 
FROM STUDENT
WHERE DEPTNO = 101;

-- 102번학과학생중에서최대키와최소키를t력하여라.
SELECT * FROM STUDENT;
SELECT MAX(HEIGHT), MIN(HEIGHT), MAX(WEIGHT), MIN(WEIGHT) 
FROM STUDENT
WHERE DEPTNO = 102;

SELECT  COUNT(DEPTNO)
FROM STUDENT
GROUP BY DEPTNO;

SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
GROUP BY DEPTNO, POSITION;

-- 교수테이블에서학과별로교수수와보직수당을받는교수수를t력하여라
SELECT * FROM PROFESSOR;
SELECT DEPTNO, COUNT(*), COUNT(COMM)
FROM PROFESSOR
GROUP BY DEPTNO;

-- 학과별로소속교수들의평균급여, 최소급여, 최대급여를t력하여라.
SELECT * FROM PROFESSOR;
SELECT DEPTNO, AVG(SAL), MIN(SAL), MAX(SAL)
FROM PROFESSOR
GROUP BY DEPTNO;

-- 전체학생을 소속학과별로 나누고, 같은학과 학생은 다시 학년별로 그룹핑하여,
-- 학과와 학년별 인원수, 평균몸무게를 출력하여라, 단, 평균몸무게는 소수점 이하 첫번째자리에서 반올림 한다
SELECT * FROM STUDENT;
SELECT DEPTNO, GRADE, COUNT(DEPTNO), ROUND(AVG(WEIGHT))
FROM STUDENT
GROUP BY DEPTNO, GRADE;

-- 소속학과별로교수급여합계와모든학과교수들의급여합계를t력하여라
SELECT DEPTNO, SUM(SAL)
FROM PROFESSOR
GROUP BY DEPTNO;

SELECT DEPTNO, SUM(SAL)
FROM PROFESSOR
GROUP BY ROLLUP(DEPTNO);

-- ROLLUP 연산자를이용하여학과및직급별교수수, 학과별교수수, †체교수수를t력하여라.
SELECT DEPTNO, POSITION, COUNT(*)
FROM PROFESSOR
--GROUP BY CUBE(DEPTNO, POSITION);
GROUP BY ROLLUP(DEPTNO, POSITION);

SELECT DEPTNO, POSITION, COUNT(*), GROUPING(DEPTNO), GROUPING(POSITION)
FROM PROFESSOR
--GROUP BY CUBE(DEPTNO, POSITION);
GROUP BY ROLLUP(DEPTNO, POSITION);

-- 학생수가4명이상인학년에대해서학년, 학생수, 평균키, 평균몸무게를t력하여라. 
-- 단, 평균키와평균몸무게는소수점Q번째자리에서반올림하고, t력순서는평균키가높은순부터내림차순으로t력하여라.
SELECT * FROM STUDENT;
SELECT GRADE, COUNT(*), ROUND(AVG(HEIGHT)), ROUND(AVG(WEIGHT))
FROM STUDENT
WHERE GRADE IS NOT NULL
GROUP BY GRADE
HAVING COUNT(*) >= 4
ORDER BY AVG(HEIGHT) DESC;

-- 학과별학생의평균몸무게중최대평균몸무게를t력하여라
SELECT * FROM STUDENT;
SELECT MAX(AVG(WEIGHT))
FROM STUDENT
GROUP BY DEPTNO;


SELECT DEPTNO, A_WEIGHT
FROM
    (SELECT DEPTNO, AVG(WEIGHT) A_WEIGHT
    FROM STUDENT
    GROUP BY DEPTNO) A,
    (SELECT MAX(AVG(WEIGHT)) B_WEIGHT
    FROM STUDENT
    GROUP BY DEPTNO) B
WHERE B.B_WEIGHT = A.A_WEIGHT
;

SELECT STUDNO, NAME, D.DEPTNO, DNAME
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO = D.DEPTNO;

-- 내부 조인 : NULL 비포함
-- 외부 조인 : NULL포함

SELECT *
FROM
    (
    SELECT ROWNUM A
    FROM DICT
    WHERE ROWNUM <= 4
    ) A,
    (SELECT (ROWNUM-1) * 2 + 1 B
    FROM DICT
    WHERE ROWNUM <= 3
    ) B
WHERE A.A = B.B;

SELECT (ROWNUM-1) * 3 + 1
FROM DICT
WHERE ROWNUM <=3;

-- ??†인하?학생의학번, 이름, 학과이름그리고학과위치를t력하여라.
SELECT STUDNO, NAME, DNAME, LOC
FROM STUDENT S, DEPARTMENT D
WHERE NAME = '전인하' AND S.DEPTNO = D.DEPTNO;

SELECT STUDNO, NAME, DNAME, LOC
FROM STUDENT
NATURAL JOIN DEPARTMENT
WHERE NAME = '전인하';

-- 학번, 학생이름, 교수번호, 담당교수이름
SELECT STUDNO, S.NAME, P.PROFNO, P.NAME 
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO;

SELECT * FROM PROFESSOR;

SELECT STUDNO, S.NAME, P.PROFNO, P.NAME 
FROM STUDENT S
JOIN PROFESSOR P ON S.PROFNO = P.PROFNO;

SELECT STUDNO, S.NAME, PROFNO, P.NAME 
FROM STUDENT S
JOIN PROFESSOR P USING (PROFNO);