(15)、统计各供应商供应的各种零件数量;
4、用Insert、Delete和Update语句实现如下数据更新 (1)、将全部红色零件的颜色改为蓝色; update p
set color='蓝'
where color='红'; select * from p;
(2)、将工程J3的城市改为上海; update j
set city='上海' where j.jno='J3'; select * from j;
11
(3)、有S5供给J4的零件P6改为由S3供应; update spj
set sno='S3'
where spj.sno='S5' and pno='P6' and jno='J4' select * from spj
(4)、从供应商关系中删除S2的元组,并从供应关系中删除相应元组; delete
from spj
where sno='S2'; delete from s
where sno='S2'; select * from s; select *from spj;
12
(5)、请将S2向工程项目J6供应200个P4零件的信息加入到供应关系; insert into s values('S2','西风','15','北京');
insert into spj values('S2','P4','J6','200'); select * from s; select *from spj;
(6)、请将S6向项目J8供应500个P7零件的信息加入到供应关系; insert into s values('S6','中华','20','上海');
insert into p values('P7','扳手','黑','15'); insert into j values('J8','电力厂','广州'); insert into spj values('S6','P7','J8','600');
5、用Insert、Delete和Update语句实现如下数据更新 create table Student( sno char(5) primary key, sname varchar(10) unique, ssex char(4) not null, sage number(3) default 0, sdept varchar(10));
create table Course( cno char(5) primary key, cname varchar2(15) not null, cpno char(5),
ccredit number(4),
13
foreign key (cpno) references Course(cno));
create table SC (sno char(5), cno char(5),
grade number(3),
primary key (sno,cno),
foreign key (sno) references Student(sno), foreign key (cno) references Course(cno));
alter table Student add jiguan varchar2(20); alter table Student add shijian date; alter table Course modify cno char(4); alter table Course modify cpno char(4); alter table SC modify cno char(4);
alter table Student drop unique(sname);
create unique index Stusname on Student(sname); create unique index Coucname on Course(cname); create index Sgrade on SC(sno asc,grade desc);
insert into Student values('95001','李勇','男'20,'cs','北京','27-2月-05'); insert into Student values('95002','张三','女',21,'cs','天津','27-2月-05'); insert into Student values('95003','李四','女',20,'cs','上海','27-2月-05'); insert into Student values('95004','王五','男',20,'cs','海南','27-2月-05'); insert into Student values('95005','赵六','男',21,'cs','郑州','27-2月-05'); insert into Student values('95006','孟立','女',20,'cs','南通','27-2月-05'); insert into Student values('95007','张飞','男',19,'cs','张家港','27-2月-05'); insert into Student values('95008','赵云','女',20,'cs','连云港','27-2月-05'); insert into Student values('95009','刘备','女',20,'cs','合肥','27-2月-05'); insert into Student values('95010','关羽','男',18,'cs','北京','27-2月-05');
insert into Course values('001','高数','',1); insert into Course values('004','化学','001',1); insert into Course values('003','C++','004',2);
insert into Course values('008','英语读写','003',1); insert into Course values('005','毛概','008',2); insert into Course values('002','物理','005',1); insert into Course values('006','网球','002',2); insert into Course values('007','英语听说','006',2); insert into Course values('010','数字逻辑','006',1); insert into Course values('009','名航概论','010',2);
insert into SC values('95001','003',95);
14
insert into SC values('95001','005',78); insert into SC values('95003','002',88); insert into SC values('95005','003',92); insert into SC values('95008','004',91); insert into SC values('95008','002',66); insert into SC values('95006','001',67); insert into SC values('95003','010',87); insert into SC values('95009','009',89); insert into SC values('95010','004',78);
(1)、对每一个系,求学生的平均年龄,并把结果存入数据库; create table Sin (
sdept char(20),
avgage number default 0, primary key(sdept),
foreign key(sdept)references Student(sdept) );
select sdept,avg(sage) as avgage from student,sc where sdept='CS' and student.sno=sc.sno group by sdept;
(2)、将计算机科学系全体学生的成绩置零; update sc set grade = 0 where 'cs' = (select sdept from Student
where Student.sno=sc.sno);
15
(4)、删除某学生的信息及其选课记录。 delete
from sc
where sno='95010'; delete
from student
where sno='95010';
(3)、删除计算机科学系所有学生的选课记录;
delete from SC;
16