SQL활용 #6 - 학사관리시스템 실습

-------------------------------------------------------------------
-------------------------------------------------------------------

-- 전체 학생목록 (검색어가 포함, 페이지네이션)

-- 시퀀스 : SEQ_PRO_CODE
-- 교수코드가 일치하는 교과목 조회  <= 교수페이지
    -- (교과목 코드, 교과목명, 학생이름)
    
-- 시퀀스 : SEQ_STD_CODE    
-- 학번이 일치하는 교과목 조회  <= 학생페이지
    -- (교과목 코드, 교과목명, 교수명)
    
-- 시퀀스 : SEQ_CLS_CODE     
-- 강의실 코드별 교과목 조회   <= 강의실페이지
    -- (교과목 코드, 교과목명, 강의실)

-------------------------------------------------------------------
-------------------------------------------------------------------

-- 학생 5명
-- 교수 3명
-- 교과목 3개

-- 전체 학생목록 (검색어가 포함, 페이지네이션)
-- 검색
SELECT * FROM STUDENT1
    WHERE STDNAME LIKE '%'||'김'||'%'
    ORDER BY STDNO DESC;

-- 페이지네이션
SELECT * FROM     
    (SELECT 
        S.*, 
        ROW_NUMBER() OVER (ORDER BY STDNO ASC) ROWNO
        FROM 
            STUDENT1 S) STUDENTROWS
    WHERE ROWNO BETWEEN 1 AND 3;       

-- 섞어
SELECT * FROM     
    (SELECT 
        S.*, 
        ROW_NUMBER() OVER (ORDER BY STDNO ASC) ROWNO
        FROM 
            STUDENT1 S
        WHERE STDNAME LIKE '%'||'김'||'%')
    WHERE ROWNO BETWEEN 1 AND 3;


-- 시퀀스 : SEQ_PRO_CODE
-- 교수코드가 일치하는 교과목 조회  <= 교수페이지
    -- (교과목 코드, 교과목명, 학생이름)
-- 1. 교수 X 교과목
CREATE OR REPLACE VIEW PROSUB AS
SELECT prof1.profcode, subject1.subtitle, subject1.subcode FROM PROF1, SUBJECT1 WHERE PROF1.PROFCODE = subject1.profcode;

SELECT * FROM PROSUB;  

-- 2. 수강 X 학생
CREATE OR REPLACE VIEW SUBSTD AS
SELECT course1.crscode, course1.stdno, COURSE1.SUBCODE, student1.stdname FROM COURSE1, STUDENT1 WHERE course1.stdno = student1.stdno;

SELECT * FROM SUBSTD; 
    
---- 2. 수강 X (교수 X 교과목)
--SELECT course1.stdno 
--    FROM COURSE1, (SELECT prof1.profcode, subject1.subtitle, subject1.subcode FROM PROF1, SUBJECT1 WHERE PROF1.PROFCODE = subject1.profcode) PROSUB
--    WHERE COURSE1.SUBCODE = prosub.subcode;

-- 3. (교수 X 교과목) X (수강 X 학생)
SELECT prosub.profcode, prosub.subcode, prosub.subtitle, substd.stdname FROM
    PROSUB,
    SUBSTD
WHERE PROSUB.SUBCODE = SUBSTD.SUBCODE;

    
-- 시퀀스 : SEQ_STD_CODE    
-- 학번이 일치하는 교과목 조회  <= 학생페이지
    -- (교과목 코드, 교과목명, 교수명)
-- 1. 학생 X 수강
SELECT student1.stdno 학번, course1.subcode 교과목코드 FROM STUDENT1, COURSE1
    WHERE student1.stdno = course1.stdno;

-- 2. 교수 X 교과목
SELECT prof1.profcode 교수코드, prof1.profname 교수명, subject1.subcode 교과목코드 FROM PROF1, SUBJECT1
    WHERE prof1.profcode = subject1.profcode;
    
-- 3. 섞어
SELECT * FROM
    (SELECT student1.stdno 학번, course1.subcode 교과목코드 FROM STUDENT1, COURSE1
    WHERE student1.stdno = course1.stdno) STDCRS,
    PROSUB
WHERE STDCRS.교과목코드 = prosub.subcode;    
    
    
-- 시퀀스 : SEQ_CLS_CODE     
-- 강의실 코드별 교과목 조회   <= 강의실페이지
    -- (교과목 코드, 교과목명, 강의실)

-- 마저해라