21.由于目前学校数据表中女生的数学成绩不足20条,校长希望作为DBA的你能帮忙添加且要求添加的分数在原来女生数学成绩最高分数的基础上逐次加1,同时显示出女生的数学的总分;
1.查询表中行数(数学女生人数) select count(*) from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and s.sex=0 and c.cname="maths" 2.查询女生数学最高分数 select max(grade) from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and s.sex=0 and c.cname="maths" 3.查询最大学号 select max(sid) from s
drop procedure if exists harden; create procedure harden(n int) begin declare i int(6)default( select count(*) from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and s.sex=0 and c.cname="maths"); declare m int(6)default(select max(grade) from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and s.sex=0 and c.cname="maths"); declare xh int(6)default(select max(sid) from s); while i<n do set i=i+1; set m=m+1; set xh=xh+1; insert into sc(sid,cid,grade)values(xh,"c4",m); insert into s(sid,sname,age,sex)values(xh,(contac(user,1)),20,0); end while select sum(grade) from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and s.sex=0 and c.cname="maths"; end
|