[오라클]4월 19일 숙제 및 답안

--1) 어제 만든 SCORE_STGR 테이블의 SNO 컬럼에 INDEX를 추가하세요.


--2) 어제 만든 ST_COURSEPF 테이블의 SNO, CNO, PNO 다중 컬럼에 INDEX를 추가하세요.

 

 

 

 

 

 

 

 

--뷰 이름은 자유
--1) 학생의 평점 4.5 만점으로 환산된 정보를 검색할 수 있는 뷰를 생성하세요.


--2) 각 과목별 기말고사 평균 점수를 검색할 수 있는 뷰를 생성하세요.


--3) 각 사원과 관리자의 이름을 검색할 수 있는 뷰를 생성하세요.


--4) 각 과목별 기말고사 평가 등급(A~F)까지와 해당 학생 정보를 검색할 수 있는 뷰를 생성하세요.


--5) 물리학과 교수의 과목을 수강하는 학생의 명단을 검색할 뷰를 생성하세요.

 

 

 

 

 

 

 

 

 

 

 

 

--1) 4.5 환산 평점이 가장 높은 3인의 학생을 검색하세요.


--2) 기말고사 과목별 평균이 높은 3과목을 검색하세요.


--3) 학과별, 학년별, 기말고사 평균이 순위 3까지를 검색하세요.(학과, 학년, 평균점수 검색)


--4) 기말고사 성적이 높은 과목을 담당하는 교수 3인을 검색하세요.(교수이름, 과목명, 평균점수 검색)


--5) 교수별로 현재 수강중인 학생의 수를 검색하세요.

 

 

 

 

 

 

 

 

 

 

 

--1) CNO이 PK인 COURSE_PK 테이블을 생성하세요.(1번 방식으로)


--2) PNO이 PK인 PROFESSOR_PK 테이블을 생성하세요.(2번 방식으로)

 

 

프라이머리키 추가, 제거하는거

--3) PF_TEMP 테이블에 PNO을 PK로 추가하세요.


--4) COURSE_PROFESSOR 테이블에 CNO, PNO을 PK로 추가하세요.


--5) BOARD_NO(NUMBER)를 PK로 갖으면서 BOARD_TITLE(VARCHAR2(200)), BOARD_CONTENT(VARCHAR2(2000)), 
--   BOARD_WRITER(VARCHAR2(20)), BOARD_FRGT_DATE(DATE), BOARD_LMDF_DATE(DATE) 컬럼을 갖는 T_BOARD 테이블을 생성하세요.


--6) BOARD_NO(NUMBER), BOARD_FILE_NO(NUMBER)를 PK로 갖으면서 BOARD_FILE_NM(VARCHAR2(200)), BOARD_FILE_PATH(VARCHAR2(2000)),
--   ORIGIN_FILE_NM(VARCHAR2(200)) 컬럼을 갖는 T_BOARD_FILE 테이블을 생성하세요.

 

 

 

 

 

 

 

 

create table score_strgr
(
    sno   number,
    sname varchar2(10)
);
create table st_coursepf
(
    sno number,
    cno number,
    pno number
);

insert into score_strgr
values (1, '김은석');
create index hahaha on score_strgr (sno);
create index hahaha2 on st_coursepf (sno, cno, pno);

create or replace view hazima (sno, sname, ask) as
(
select sno, sname, avr * (45 / 40) ask
from STUDENT);

select *
from hazima;


create or replace view kikkik (cno, cname, ask) as
(
select course.cno, cname, avg(result) ask
from course,
     score
where course.CNO = score.CNO
group by course.cno, cname );


select *
from kikkik;

create or replace view hakik (ename, kaka) as
(


select e.ename, e2.ENAME kaka
from EMP e,
     emp e2
where e.MGR = e2.eno


    );

select *
from hakik;

create or replace view hak(sno, sname, cname, g) as
(
select student.sno, sname, cname, grade
from student,
     course,
     score,
     SCGRADE
where STUDENT.SNO = SCORE.SNO
  and COURSE.CNO = SCORE.CNO
  and SCORE.RESULT between SCGRADE.LOSCORE and SCGRADE.HISCORE)

select *
from hak;

create or replace view hahahak(sno, sname, section) as
(
select distinct STUDENT.sno, sname, section
from STUDENT,
     PROFESSOR,
     score,
     course
where STUDENT.SNO = SCORE.SNO
  and COURSE.CNO = SCORE.CNO
  and PROFESSOR.PNO = COURSE.PNO
  and SECTION = '물리')

select *
from hahahak;

select rownum, A.*
from (select sno, sname, avr from STUDENT order by AVR desc) A;

select rownum, A.*
from (select pname, cname, avg(result) avgres
      from PROFESSOR,
           COURSE,
           SCORE
      where COURSE.PNO = PROFESSOR.PNO
        and COURSE.CNO = SCORE.CNO
      group by pname, cname
      order by avgres desc) A
where rownum < 4;
;



select pname, count(distinct STUDENT.sno)
from PROFESSOR
   , STUDENT
   , score
   , course
where PROFESSOR.PNO = COURSE.PNO
  and SCORE.CNO = COURSE.CNO
  and STUDENT.SNO = SCORE.SNO
group by pname
;


select pname, STUDENT.sno
from PROFESSOR
   , STUDENT
   , score
   , course
where PROFESSOR.PNO = COURSE.PNO
  and SCORE.CNO = COURSE.CNO
  and STUDENT.SNO = SCORE.SNO
  and PNAME = '송강'
;


create table course_pk
(
    cno   number primary key,
    cname varchar2(10)
);

create table professor_pk
(
    pno   number,
    pname varchar2(10),
    constraint haqhhhaha primary key (pno)
);

drop table course_pk;
drop table professor_pk;

create table gg
(
    pno   number,
    pname varchar2(10)
);
alter table gg
    add constraint hahakikkik primary key (pno);
alter table gg
    drop primary key;
alter table gg
    add constraint kikhahaha primary key (pno, pname);

create table t_board
(
    BOARD_NO        number,
    BOARD_TITLE     VARCHAR2(200),
    BOARD_CONTENT   VARCHAR2(2000),
    BOARD_WRITER    VARCHAR2(20),
    BOARD_FRGT_DATE DATE,
    BOARD_LMDF_DATE DATE,
    constraint ewfq primary key (BOARD_NO, BOARD_TITLE)
);

create table t_board
(
    BOARD_NO        number,
    BOARD_TITLE     VARCHAR2(200),
    BOARD_CONTENT   VARCHAR2(2000),
    BOARD_WRITER    VARCHAR2(20),
    BOARD_FRGT_DATE DATE,
    BOARD_LMDF_DATE DATE
);

alter table t_board add constraint ghake primary key (BOARD_NO, BOARD_TITLE);



drop table t_board;