1 --1.1
2 delete
3 from SC
4 where SNO in
5 (
6 select SC.SNO
7 from Student, SC
8 where Student.Sno = SC.Sno and
9 Sname = '宾兆琦'
10 );
11 delete from Student
12 where Sname = '宾兆琦';
13
14 --1.2
15 update Student
16 set Major = '软件工程'
17 where Sname = '朱安琪';
18
19 --1.3
20 create table change_major
21 (
22 ID varchar2(10),
23 Sno varchar2(20) ,
24 Sname varchar2(20),
25 cbefore varchar2(20),
26 cafter varchar2(20),
27 ctime date,
28 primary key(ID),
29 foreign key (Sno) references Student(Sno)
30 );
31 insert into change_major
32 values (1, '101105', '朱安琪', '信息管理与信息系统', '软件工程',to_date('2016/10/05', 'yyyy/mm/dd'));
33
34 --1.3
35 delete from SC
36 where Sno in
37 (
38 select Sno
39 from SC, Course
40 where SC.Cno = Course.Cno and
41 Cname = '计算机网络' and
42 Grade < 60
43 );
44
45 --1.4
46 insert into Student
47 values ('106559', '路前元', '男', '汉族', '共青团员', to_date('1995/11/08', 'yyyy/mm/dd'), null, null);
48
49 --1.5
50 insert into Course
51 values (382, '数据库原理与应用', 3.5, '选修', 4, 1);
52 insert into Course
53 values (383, 'JAVA语言程序设计', 4.5, '选修', 6, 1);
54
55 --1.6
56 create view JSJ_VIEW(Sno, Sname, Cname, Grade)
57 as select SC.Sno, Sname, Cname, Grade
58 from Student, SC, Course
59 where Student.Sno = SC.Sno and
60 SC.Cno = Course.Cno and
61 Student.Major = '计算机科学与技术';
62
63 --1.7
64 create view XF_VIEW(Sno, sum_Credit)
65 as select Sno, sum(credit)
66 from SC, Course
67 where SC.Cno = Course.Cno
68 group by Sno;
69
70 --1.8
71 select sum_Grade
72 from Student, XF_VIEW
73 where Student.Sno = XF_VIEW.Sno and
74 Student.Sname = '李洋洋' or
75 Student.Sname = '李向冲';
76
77 --2.1
78 insert into S
79 values ('S9', '英特尔', '西安');
80 insert into SPJ
81 values ('S9', 'P5', 'J7', 600);
82 insert into SPJ
83 values ('S9', 'P4', 'J4', 500);
84
85 --2.2
86 update SPJ
87 set QTY = QTY+150
88 where Sno in
89 (
90 select S.Sno
91 from S, SPJ
92 where S.Sno = SPJ.Sno and
93 S.city = '北京'
94 );
95
96 --2.3
97 update P
98 set color = '黑'
99 where Pno in
100 (
101 select Pno
102 from P
103 where color = '红'
104 );
105
106 --2.4
107 update SPJ
108 set Sno = 'S1'
109 where Sno in
110 (
111 select Sno
112 from SPJ
113 where Sno = 'S5' and
114 Jno = 'J4' and
115 Pno = 'P6'
116 );
117
118 --2.5
119 insert into SPJ
120 values ('S2', 'P4', 'J7', 510);
121
122 --2.6
123 delete from SPJ
124 where Pno = 'P3';
125 delete from P
126 where Pno = 'P3';
127
128 --2.7
129 create view P_ls(Sname, Pname, Weight, Jno, qty)
130 as select Sname, Pname, Weight, Jno, qty
131 from SPJ, P, S
132 where SPJ.Pno = P.Pno and
133 SPJ.Sno = S.Sno and
134 P.Pname = '螺丝刀';
135
136 --2.8
137 select Sname
138 from P_ls
139 where qty = 500;
140
141 --2.9
142 create view SJ_View(Sno, Pno, qty)
143 as select Sno, Pno, qty
144 from SPJ, J
145 where SPJ.Jno = J.Jno and
146 J.Jname = '三建';
