[오라클]4월21일 숙제문제

--1) 과목번호, 과목이름, 교수번호, 교수이름을 담을 수 있는 변수들을 선언하고 
--   유기화학 과목의 과목번호, 과목이름, 교수번호, 교수이름을 출력하세요.


--2) 위 데이터들을 레코드로 선언하고 출력하세요.


--3) 과목번호, 과목이름, 과목별 평균 기말고사 성적을 갖는 레코드의 배열을 만들고
--   기본 LOOP문을 이용해서 모든 과목의 과목번호, 과목이름, 과목별 평균 기말고사 성적을 출력하세요.


--4) 학생번호, 학생이름과 학생별 평균 기말고사 성적을 갖는 테이블 T_STAVGSC를 만들고
--   커서를 이용하여 학생번호, 학생이름, 학생별 평균 기말고사 성적을 조회하고 
--   조회된 데이터를 생성한 테이블인 T_STAVGSC에 저장하세요.

 

 

 

declare type haha is record
                     (
                         cno   course.cno%type,
                         cname course.cname%type,
                         pno   professor.pno%type,
                         PNAME professor.pname%type
                     );
    ha haha;
begin
    select cno, cname, PROFESSOR.pno, PNAME
    into ha
    from PROFESSOR,
         course
    where PROFESSOR.PNO = COURSE.PNO
      and cname = '유기화학';

    DBMS_OUTPUT.PUT_LINE(ha.cno);
    DBMS_OUTPUT.PUT_LINE(ha.cname);
    DBMS_OUTPUT.PUT_LINE(ha.pno);
    DBMS_OUTPUT.PUT_LINE(ha.pname);

end;


create table haha as
select course.cno, cname, avg(result) avg
from course,
     score
where course.CNO = score.cno
group by course.cno, cname;


------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
------------------------------------------------------4번문제품
create table kilkil
(
    sno   number,
    sname varchar2(10),
    avg   number
);
select *
from haha;
--
declare
    cursor curst is select student.sno, sname, avg(result)
                    from student,
                         score
                    where student.SNO = SCORE.SNO
                    group by student.sno, student.sname;
    strow kilkil%rowtype;

begin
    open curst;
    loop
        fetch curst into strow;
        insert into kilkil values strow;
        DBMS_OUTPUT.PUT_LINE(strow.sno);
        DBMS_OUTPUT.PUT_LINE(strow.sname);
        DBMS_OUTPUT.PUT_LINE(strow.avg);
        exit when curst%notfound;
    end loop;
    close curst;
end;

select *
from kilkil;
------------------------

declare type hahak is record (cno course.cno%type, cname course.cname%type, avg number);
    type hahaarr is table of hahak
        index by pls_integer;
    arr hahaarr;
    idx number := 1;
begin


    for strow in ( select course.cno, cname, avg(result) as avg
                   from course,
                        score
                   where SCORE.CNO = COURSE.cno
                   group by course.cno, cname)

        loop

            arr(idx) := strow;
            DBMS_OUTPUT.PUT_LINE(arr(idx).cno);
            DBMS_OUTPUT.PUT_LINE(arr(idx).cname);
            DBMS_OUTPUT.PUT_LINE(arr(idx).avg);

        end loop;

end ;
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다
------------------------------내방법으로 풀었지만 도움을 받았다


---------------------------다시한번 풀어보자


declare type hahak is record (cno course.cno%type, cname course.cname%type, avg number);
    type hahaarr is table of hahak
        index by pls_integer;
    arr hahaarr;
    idx number := 1;
begin




        loop
arr(idx) =



        end loop;

end ;

declare cno number;
    cname varchar2(20);
    pno number;
    pname varchar2(20);
    begin
    select cno, cname, course.pno, pname into cno, cname, pno, pname from course, PROFESSOR where COURSE.PNO = PROFESSOR.PNO and cname = '유기화학';

    DBMS_OUTPUT.PUT_LINE(cno);
    DBMS_OUTPUT.PUT_LINE(cname);
    DBMS_OUTPUT.PUT_LINE(pno);
    DBMS_OUTPUT.PUT_LINE(pname);

end;

declare type haha is record
                     (
                         cno   course.cno%type,
                         cname course.cname%type,
                         pno   professor.pno%type,
                         pname professor.pname%type
                     );
    h haha;

begin

    select cno, cname, PROFESSOR.pno, pname
    into h
    from PROFESSOR,
         COURSE
    where PROFESSOR.PNO = COURSE.PNO
      and CNAME = '유기화학';
    DBMS_OUTPUT.PUT_LINE(h.cno);
    DBMS_OUTPUT.PUT_LINE(h.cname);


end;

select *
from student;


create table t_stavgsc as
select student.sno, sname, avg(result) a
from student,
     score
where student.SNO = score.SNO
group by student.sno, sname;

select *
from t_stavgsc;

delete
from t_stavgsc;

declare
    cursor curst is select student.sno, sname, avg(result)
                    from student,
                         score
                    where student.SNO = SCORE.SNO
                    group by student.sno, sname;

begin

    for strow in curst
        loop
            DBMS_OUTPUT.PUT_LINE(strow.SNO);
            DBMS_OUTPUT.PUT_LINE(strow.sname);


            insert into t_stavgsc values strow;
        end loop;
end;



declare
    cursor curst is select student.sno, sname, avg(result)
                    from student,
                         score
                    where student.SNO = SCORE.SNO
                    group by student.sno, sname;
    type haha is record (sno student.sno%type, sname student.sname%type, avg number);
    h haha;

begin
    open curst;

    loop

        fetch curst into h;
insert into t_stavgsc values h;


        exit when curst%notfound;


    end loop;
close curst;
end;

declare  cursor curst is select student.sno, sname, avg(result)
                         from student,
                              score
                         where student.SNO = SCORE.SNO
                         group by student.sno, sname;

    strow t_stavgsc%rowtype;

    begin
    open curst;
    loop
        fetch curst into  strow;
        exit when curst%notfound;
        insert into t_stavgsc values strow;

    end loop;
end;
select * from t_stavgsc;