--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;
[오라클] 4월 18일 예제 (0) | 2023.04.18 |
---|---|
[오라클]4.17 문제 및 풀이 (0) | 2023.04.17 |
[오라클] 4월 17일 예제 (0) | 2023.04.17 |
[오라클] 피봇 언피봇 매우 어렵다 외우기 (0) | 2023.04.17 |
[오라클]4월 14일 수업예제 (16) | 2023.04.14 |
댓글 영역