SQL语言实现数据更新操作

我会带着你远行 2022-08-26 12:14 227阅读 0赞

(1)插入如下学生记录(学号：95030，姓名：李莉，年龄18)  
说明：由于在实验室1中的学号设置为08001-08050则插入的数据只能是这个范围，所以不能插入95030的学号，所以就插入了08015  
insert into student(Sno,Sname,Sage) values('08015','李莉',18)  
(2)插入如下选课记录(95030,1)  
说明：由于在实验室1中的学号设置为08001-08050则插入的数据只能是这个范围，所以不能插入95030的学号，所以就插入了(08016,1)  
insert into sc(sno,cno)  
values('08016','001')  
(3)计算机系学生年龄改成20  
update student set Sage=20 where Sdept='cs'  
(4)数学系所有学生的成绩改为0  
update sc  
set grade=0  
where sno in (  
select sno from student where sdept='ma')  
(5)把低于总平均成绩的女同学成绩提高5分  
update sc set Grade=Grade+5 where grade<(select avg(grade) from sc)and sno in(select sno from student where ssex='女')  
(6)修改2号课程的成绩，若成绩小于75分提高5%，成绩大于75分时提高4%  
update sc set grade=grade\*1.05 where cno='002'and grade<75  
(7)删除95030的学生信息(没有95030的，所以删去08015的学生信息)  
delete from student where sno='08015'  
(8)删除学生表中无成绩的记录  
delete from sc where grade is null  
(9)删除数学系所有学生选课记录  
delete from sc where sno in (select sno from student where sdept='ma')  
数学系的学生学号为08003和08006  
(10)删除不及格的学生选课情况  
delete from sc where sno in(select sno from student where grade<60)  
(11)查询每一门课程成绩都大于等于80分的学生学号，姓名和性别，把值送往另一个已经存在的基本表STU(SNO ,SNAME,SSEX)中。  
create table stu(Sno char(5),Sname char(20),Ssex char(2))  
insert into stu(Sno,Sname,Ssex)select sno,sname,ssex from student where sno in (select sno from sc group by sno having min(grade)>80)  
(12)把所有的学生学号和课程号连接追加到新表中  
select sno+cno as newline into Stu from sc  
(13)所有学生年龄增加1  
Update student set sage=sage+1;  
(14)统计3门以上课程不及格的学生把相应的学生姓名，系别追加到另外一个表中  
select sname,sdept into newtable from student where sno in (select sno from sc where grade<60 group by sno having count(grade)>=3)