–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, 결과, 등급)
–7) GPA가 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;