一些简单的sql程序及其答案
1、向student增加一条记录<br />（‘021710146’，‘王飞’，‘男’，22，‘信息系’）<br /><br />insert<br />into king(sno,sname,sage,sdept,ssex)<br />values('021710146','王飞','22','信息工程系','男');<br /><br />2、在student表中删除李军同学的所有信息<br />在student表中删除李军同学的所有信息<br />delete<br />from<br />king where<br />sno='021710146' <br />3、查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把查询到的结果送往另一个已存在的基本表student2(sno，sname，sex)<br />insert<br />into aaa(sno,sname,ssex)<br /><br />select sname,sno,ssex<br />from student<br />where sno in(<br />select sno from<br />sc where grade>90)<br /><br />4、在基本表SC中删除没有成绩的选课元组<br />delete <br />from<br />sc<br />where grade is null<br />5、把王二同学的选课记录和成绩全部删去<br />delete<br />from sc<br />where sno in (<br />select sno from<br />student where<br />sname='刘晨')<br />6、把选修maths课不及格的成绩全改为空值<br />update sc<br />set grade=null<br />where sno in(<br /><br />select sno<br />from sc where<br />cno in(<br />select cno from course<br />where cname='数学')<br />)<br />7、把低于总平均成绩的女同学成绩提高5%<br />update sc<br />set grade=1.05*grade<br />where grade in<br />(select grade<br />from sc,student<br />wherestudent.ssex='女'and student.sno=sc.sno and grade< <br />(select avg(grade)<br />from sc))<br />8、在基本表SC中修改3号课程的成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%(用两个UPDATE语句实现)<br /><br /> update sc<br />set grade=(1+0.05)*grade<br />where grade<75;<br />update sc<br />set grade=(1+0.04)*grade<br />where grade>75;<br /><br /><br /><br /><blockquote class="blockquote">From: http://kingguang.uueasy.com/read.php?tid=62&newpost=1Powered by PHPWind.com</blockquote>
页:
[1]