oracle3

--1.1
delete 
from SC
where SNO in
(
    select SC.SNO
    from Student, SC
    where Student.Sno = SC.Sno and
          Sname = '宾兆琦'
);
delete from Student
where Sname = '宾兆琦';

--1.2
update Student
set Major = '软件工程'
where Sname = '朱安琪';

--1.3
create table change_major
(
    ID varchar2(10),
    Sno varchar2(20) ,
    Sname varchar2(20),
    cbefore varchar2(20),
    cafter varchar2(20),
    ctime date,
    primary key(ID),
    foreign key (Sno) references Student(Sno)
);
insert into change_major
values (1, '101105', '朱安琪', '信息管理与信息系统', '软件工程',to_date('2016/10/05', 'yyyy/mm/dd'));

--1.3
delete from SC
where Sno in
(
    select Sno
    from SC, Course
    where SC.Cno = Course.Cno and
          Cname = '计算机网络' and
          Grade < 60
);

--1.4
insert into Student
values ('106559', '路前元', '男', '汉族', '共青团员', to_date('1995/11/08', 'yyyy/mm/dd'), null, null);

--1.5
insert into Course
values (382, '数据库原理与应用', 3.5, '选修', 4, 1);
insert into Course
values (383, 'JAVA语言程序设计', 4.5, '选修', 6, 1);

--1.6
create view JSJ_VIEW(Sno, Sname, Cname, Grade)
as select SC.Sno, Sname, Cname, Grade
from Student, SC, Course
where Student.Sno = SC.Sno and
      SC.Cno = Course.Cno and
      Student.Major = '计算机科学与技术';        

--1.7
create view XF_VIEW(Sno, sum_Credit)
as select Sno, sum(credit)
from SC, Course
where SC.Cno = Course.Cno
group by Sno;

--1.8
select sum_Grade
from Student, XF_VIEW
where Student.Sno = XF_VIEW.Sno and
      Student.Sname = '李洋洋' or
      Student.Sname = '李向冲';

--2.1
insert into S 
values ('S9', '英特尔', '西安');
insert into SPJ
values ('S9', 'P5', 'J7', 600);
insert into SPJ
values ('S9', 'P4', 'J4', 500);

--2.2
update SPJ
set QTY = QTY+150    
where Sno in
(
    select S.Sno
    from S, SPJ
    where S.Sno = SPJ.Sno and
          S.city = '北京'
);

--2.3
update P
set color = '黑'
where Pno in
(
    select Pno
    from P
    where color = '红'
);

--2.4
update SPJ
set Sno = 'S1'
where Sno in
(
    select Sno
    from SPJ
    where Sno = 'S5' and
          Jno = 'J4' and
          Pno = 'P6'
);

--2.5
insert into SPJ
values ('S2', 'P4', 'J7', 510);

--2.6
delete from SPJ
where Pno = 'P3';
delete from P
where Pno = 'P3';

--2.7
create view P_ls(Sname, Pname, Weight, Jno, qty)
as select Sname, Pname, Weight, Jno, qty
from SPJ, P, S
where SPJ.Pno = P.Pno and
      SPJ.Sno = S.Sno and
      P.Pname = '螺丝刀';

--2.8
select Sname
from P_ls
where qty = 500;

--2.9
create view SJ_View(Sno, Pno, qty)
as select Sno, Pno, qty
from SPJ, J
where SPJ.Jno = J.Jno and
      J.Jname = '三建';