[Oracle] 데이터 갱신과 트랜잭션 제어 연습문제

기본설정

create table enrol1
as select * from enrol ;

create table student1 
as select * from student where stu_grade in (1, 2);

create table subject1
as select * from subject ; 

select * from enrol1 ;​

select * from student1 ;

select * from subject1 ;

 

 

1. 

insert into student1 
	values ('20101059', '조병준', '컴퓨터정보', 1, 'B', 'M', 164, 70 ) ;

2.

insert into student1 (stu_no, stu_name, stu_dept, stu_grade, stu_class, stu_gender)
	values ('20102038', '남지선', '전기전자', 1, 'C', 'F');

3.

insert into student1 (stu_no, stu_name, stu_dept, stu_grade, stu_class)
	values ('20103009','박소신','기계',
    	(select stu_grade from student1 where stu_no = '2009075'),
        (select stu_class from student1 where stu_no = '2009075')) ;

4.

insert into student1 
select * 
from student
where stu_grade = 3 ;

5.

update student1
set stu_class = 'B'
where stu_no = '20071001' ;

6.

update student1 
set stu_height = stu_height + 2 
where stu_no = 20072088 ; 

7.

update student1 
set stu_grade = stu_grade + 1 ; 

8. 

update student1 
set stu_dept = '컴퓨터정보', stu_no = '20061021'
where stu_no = '20062021';

9.

update enrol1 
set enr_grade = enr_grade - 10 
where sub_no = (select sub_no 
				from subject1
                where sub_name = '전자회로실험');

10.

update enrol1
set enr_grade = 0
where stu_no = (select stu_no
				from student1
                where stu_no='20061062');

11.

delete from student1
where stu_no = '20093088' ; 

12.

insert into subject1
	values ('112', '자동화시스템', ' 고종민', 3, '기계') ; 

13.

update subject1 
set sub_no = 501
where sub_no = 110 ; 

14.

delete from subject1 
where sub_no = 101 ; 

15. 

update enrol1
set sub_no = 999
where sub_no not in (select sub_no from subject1) ; 

16.

update enrol1
set stu_no = 99999999
where stu_no not in (select stu_no from student1) ; 

17.

delete from enrol1 
where sub_no = 999 ; 

18.

delete from enrol1
where stu_no = 99999999 ; 

19.

delete from enrol1
where sub_no not in (select sub_no from subject1) ; 

20.

drop table enrol1 ;