[오라클] 4월 18일 문제 및 풀이

--1) STUDENT 테이블을 참조하여 ST_TEMP 테이블을 만들고 모든 학생의 성적을 4.5만점 기준으로 수정하세요


--2) PROFESSOR 테이블을 참조하여 PF_TEMP 테이블을 만들고 모든 교수의 부임일자를 100일 앞으로 수정하세요


--3) ST_TEMP 테이블에서 화학과 2학년 학생의 정보를 삭제하세요


--4) PF_TEMP 테이블에서 조교수의 정보를 삭제하세요


--5) EMP 테이블을 참조하여 EMP2 테이블을 만들고 DNO = 30인 사원의 보너스를 15프로 상승시킨 값으로 변경하시고 
--   DNO = 20인 사원의 급여를 10프로 상승시킨 값으로 변경하세요.


--6) 화학과 2학년 학생중 기말고사 성적의 등급이 A, B인 정보를 갖는 테이블 SCORE_STGR을 생성하세요.(SNO, SNAME, MAJOR, SYEAR, RESULT, GRADE)


--7) 생물학과 학생중 평점이 2.7이상인 학생이 수강중인 과목의 정보를 갖는 테이블 ST_COURSEPF를 생성하세요. (SNO, SNAME, CNO, CNAME, PNO, PNAME, AVR)

 

 

 

create table st_temp as select * from STUDENT;

select * from st_temp;

update st_temp
set avr = 45/40 * avr;

select * from st_temp;


create table pf_temp as select * from PROFESSOR;

select * from pf_temp;




update pf_temp
set HIREDATE = HIREDATE-100;


delete from st_temp
where MAJOR = '화학' and SYEAR = 2;

select * from st_temp;


delete from pf_temp
where ORDERS = '조교수';

select * from pf_temp;

create table emp2 as select* from emp;

update emp2
set comm = comm*(1.15)
where dno = 30;

update emp2
set sal = sal*(1.1)
where dno = 20;


create table score_stgr(sno varchar2(20), sname varchar2(20), major varchar2(20), syear varchar2(20), result varchar2(20), grade varchar2(20));
;select * from score_stgr;

insert into score_stgr
select student.sno, sname, major, syear, result, grade
from student, SCORE, SCGRADE
where STUDENT.SNO = SCORE.SNO and SCORE.RESULT between SCGRADE.LOSCORE and SCGRADE.HISCORE
and SYEAR=2 and GRADE in ('A','B')




;





create table st_coursepf ( sno varchar2(20), sname  varchar2(20), cno  varchar2(20), cname  varchar2(20), pno  varchar2(20), pname  varchar2(20), avr  varchar2(20))

insert into st_coursepf
select STUDENT.sno, sname, COURSE.cno, cname, PROFESSOR.pno, pname, avr
from student, course, PROFESSOR, score
where STUDENT.SNO = SCORE.SNO and PROFESSOR.PNO = COURSE.PNO and SCORE.CNO = COURSE.CNO


select * from st_coursepf



;
create table st_temp2
as select * from student;
update st_temp2
set AVR = 45/40 * avr;
select * from st_temp2;

create table pf_temp2
as select * from PROFESSOR;

update pf_temp2
set HIREDATE = HIREDATE + 100;

delete from st_temp2
where SYEAR = 2;

delete  from pf_temp2
where ORDERS = '조교수';

create table emp3 as select * from emp;
select * from emp3;

update emp3
set comm = comm*1.15
where dno = 30;

update emp3
set sal = sal*1.1
where dno = 20;

create table score_stgr2 (sno varchar2(20), sname varchar2(20), major varchar2(20), syear varchar2(20), result varchar2(20), grade varchar2(20));

insert into score_stgr2
select student.sno, sname, major, syear, result, grade from student, score, SCGRADE
where STUDENT.SNO = SCORE.SNO and SCORE.RESULT between SCGRADE.LOSCORE and SCGRADE.HISCORE and SYEAR = 2 and GRADE in ('A','B');

select * from score_stgr2
;
create table st_coursepf2(sno varchar2(20), sname varchar2(20), cno varchar2(20), cname varchar2(20), pno varchar2(20), pname varchar2(20), avr varchar2(20) );


insert into st_coursepf2
select STUDENT.sno, sname, COURSE.cno, cname, PROFESSOR.pno, pname, avr
from STUDENT, COURSE, PROFESSOR, score
where STUDENT.SNO = SCORE.SNO and PROFESSOR.PNO = COURSE.PNO and SCORE.CNO = COURSE.CNO
and AVR>=2.7 and MAJOR='생물'

select * from st_coursepf2;