localhost_STUDENT_220304.sql

-- 학생 탄생 계절별 인원 수 구하기
SELECT * FROM STUDENT;
SELECT STUDNO, NAME, BIRTHDATE, TO_CHAR(BIRTHDATE, 'MM'),
    CASE 
        WHEN TO_CHAR(BIRTHDATE, 'MM') IN(3, 4, 5) THEN '봄'
        WHEN TO_CHAR(BIRTHDATE, 'MM') IN(6, 7, 8) THEN '여름'
        WHEN TO_CHAR(BIRTHDATE, 'MM') IN(9, 10, 11) THEN '가을'
        ELSE '겨울'
    END
FROM STUDENT;   -- 못했음....

SELECT STUDNO, NAME, BIRTHDATE, ADD_MONTHS(BIRTHDATE, -2), TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q')
FROM STUDENT;

SELECT 
CASE
    WHEN TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q') = 1 THEN '봄'
    WHEN TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q') = 2 THEN '여름'
    WHEN TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q') = 3 THEN '가을'
    ELSE '겨울'
END SEASON,
COUNT(*)
FROM STUDENT
WHERE BIRTHDATE IS NOT NULL
GROUP BY TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q')
ORDER BY TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q');


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

-- 10101학생의 학번, 이름, 학과번호, 학과이름, 담당교수번호, 담당교수 이름
SELECT * FROM STUDENT;
SELECT * FROM DEPARTMENT;
SELECT * FROM PROFESSOR;

SELECT STUDNO, S.NAME, STUDNO, S.DEPTNO, S.PROFNO, P.NAME
FROM STUDENT S, DEPARTMENT D, PROFESSOR P
WHERE S.PROFNO = P.PROFNO 
AND STUDNO = 10101; -- 또 못함...ㅠㅠㅠ

SELECT STUDNO, S.NAME, S.DEPTNO, DNAME, S.PROFNO, P.NAME
FROM STUDENT S, DEPARTMENT D , PROFESSOR P
WHERE S.DEPTNO = D.DEPTNO
AND S.PROFNO = P.PROFNO
AND STUDNO = 10101;
-- 다른 방법으로
SELECT STUDNO, S.NAME, S.DEPTNO, DNAME, PROFNO, P.NAME
FROM STUDENT S
JOIN DEPARTMENT D ON S.DEPTNO = D.DEPTNO
JOIN PROFESSOR P USING(PROFNO)
WHERE STUDNO = 10101;

SELECT * FROM STUDENT, DEPARTMENT;

SELECT * FROM STUDENT CROSS JOIN DEPARTMENT;

-- 학생테이블과부서테이블을EQUI JOIN하여 학번, 이름, 학과번호, 소속학과이름, 학과 위치를 출력하여라
SELECT STUDNO, NAME, S.DEPTNO, DNAME, LOC
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO = D.DEPTNO;
-- NULL은 비교연산으로 하면 FALSE처리된다
SELECT STUDNO, NAME, DEPTNO, DNAME, LOC
FROM STUDENT
JOIN DEPARTMENT USING(DEPTNO);

--NATURAL JOIN을이용하여교수번호, 이름, 학과번호, 학과이름을t력하여라.
SELECT PROFNO, NAME, DEPTNO, DNAME
FROM PROFESSOR
NATURAL JOIN DEPARTMENT;

-- 교수테이블과 급여등급테이블을 NON-EQUI JOIN하여 교수별로 급여등급을 t력하여라.
SELECT * FROM SALGRADE;
SELECT NAME, SAL, GRADE 
FROM PROFESSOR
JOIN SALGRADE 
--ON SAL <= HISAL AND SAL >= LOSAL;
ON SAL BETWEEN LOSAL AND HISAL;

-- 4행 2열짜리 숫자가 저장되 테이블 2개
CREATE TABLE J1 (
    A NUMBER,
    B NUMBER
);
CREATE TABLE J2 (
    A NUMBER,
    C NUMBER
);

INSERT INTO J1 VALUES (1, 1);
INSERT INTO J1 VALUES (2, 2);
INSERT INTO J1 VALUES (3, 3);
INSERT INTO J1 VALUES (4, 4);

INSERT INTO J2 VALUES (1, 5);
INSERT INTO J2 VALUES (2, 6);
INSERT INTO J2 VALUES (5, 6);
INSERT INTO J2 VALUES (NULL, 7);

SELECT * FROM J1 NATURAL JOIN J2;
SELECT * FROM J1 JOIN J2 ON J1.A != J2.A;

SELECT DISTINCT PROFNO 
FROM STUDENT 
WHERE PROFNO IS NOT NULL
ORDER BY 1;

DELETE STUDENT WHERE STUDNO = 30303 OR STUDNO IS NULL;

-- 총 학생은 16명 , 담당교수가 있는 학생은 10명 없는 학생은 6명

-- 지도 교수가 없는 학생도 출력
SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
LEFT JOIN PROFESSOR P USING(PROFNO);

-- 담당 학생이 없는 교수
SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
RIGHT JOIN PROFESSOR P USING(PROFNO);

-- 지도 교수도 없고 학생도 없는 다
SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
FULL JOIN PROFESSOR P USING(PROFNO);

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

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

SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
FULL JOIN PROFESSOR P USING(PROFNO)
WHERE STUDNO IS NULL OR PROFNO IS NULL;


-- 학생테이블과교수테이블을조인하여이름, 학년, 지도교수의이름, 직급을t력하여라. 
-- 단, 지도교수가배정되지않은학생이름도함께t력하여라.
SELECT S.NAME, GRADE, P.NAME, POSITION
FROM STUDENT S , PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+);


SELECT S.NAME, GRADE, P.NAME, POSITION
FROM STUDENT S , PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+)
UNION ALL
SELECT S.NAME, GRADE, P.NAME, POSITION
FROM STUDENT S , PROFESSOR P
WHERE S.PROFNO(+) = P.PROFNO;


--SELECT D1.DEPTNO, D1.DNAME, D1.COLLEGE, D2.DEPTNO, D2.DNAME, D2.COLLEGE 
SELECT D1.DNAME || '의 상위학과는 ' || NVL2(D2.DNAME, D2.DNAME || '입니다', '없습니다') AS RESULT 
FROM DEPARTMENT D1 LEFT JOIN DEPARTMENT D2 ON D1.COLLEGE = D2.DEPTNO;
--FROM DEPARTMENT D1, DEPARTMENT D2
--WHERE D1.COLLEGE = D2.DEPTNO (+);

--1. 교수테이블에서?†은지?교수의직급검색SQL 명령’K실행
--2. 교수테이블의직급칼럼에서1 에서얻은결과값과동일™직급을가’교수검색명령’K실행
SELECT * 
FROM PROFESSOR 
WHERE POSITION = (
    SELECT POSITION 
    FROM PROFESSOR 
    WHERE NAME = '전은지'
);

-- 1행 1열의 단 하나의 값을 반환하는 서브쿼리 >> 스칼라 서브쿼리