SQL实验答案(实验二、三).ppt

,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,实验二答案,单表查询,本次实验用到的数据库,学生选课数据库，表结构如下,学生表：Student(,Sno,，Sname，Ssex，Sage，Sdept),课程表：Course(,Cno,，Cname，Ccredit),学生选课表：SC(,Sno，Cno,，Grade),（1）查询全体学生的学号、姓名、性别、家庭住址。,SELECT Sno,Sname,Ssex,Saddress,FROM Student;,（2）查询学生表中的全部信息,SELECT*,FROM Student;,（3）查询地理信息系统、测绘专业学生的姓名、性别和家庭住址,SELECT Sdept,Sname,Ssex,Saddress,FROM student where Sdept=地理信息系统 or Sdept=测绘工程;,（4）查询所有姓李学生的姓名、学号和性别。,SELECT Sname,Sno,Ssex,FROM Student,WHERE,rtrim(,Sname)LIKE 李%;,（5）查询姓“刘”且全名为三个汉字的学生的姓名。,SELECT Sname,FROM Student,WHERE,rtrim(,Sname)LIKE 刘_ _;,rtrim()作用是去掉结尾的空格，因为Sname会在后面生成空格,（6）查询姓刘最后一个字为阳的学生的姓名。,SELECT Sname,FROM Student,WHERE,rtrim(,Sname)LIKE,刘%阳;,（7）查询所有不姓刘的学生姓名。,SELECT Sname,FROM Student,WHERE,rtrim(,Sname)NOT LIKE 刘%;,（8）查询所有以VB_开头，且倒数第2个字符为 o的课程的详细情况。(VB_Develop),SELECT *,FROM Course,WHERE,rtrim(,Cname)LIKE,VB_%o_ ESCAPE;,（9）查询年龄不在2023岁之间的学生姓名、系和,出生年,。（结果不包括20和23岁）,SELECT Sname,Sdept,Sage,2011-sage,FROM Student,WHERE Sage not between 20 AND 23;,（10）,查询学号在20080112008015之间的学生姓名。,SELECT Sname,FROM Student,WHERE Sno between 2008011 AND 2008015,（16）计算003号课程的学生平均成绩。,SELECT AVG(Grade),FROM Sc,WHERE Cno=003;,（17）查询既选修了002号课程又选修了004号课程的学生学号和课程号,SELECT Sno,Cno,FROM sc,WHERE Sno in,(,SELECT Sno,FROM sc,WHERE Cno=002 AND Sno IN,(SELECT Sno,FROM sc,WHERE Cno=004)and Cno in(002,004);,（18）查询选修了2门以上课程的学生学号。,SELECT Sno,FROM Sc,GROUP BY Sno,HAVING COUNT(*)2;,（19）查询选修了002号课程且成绩不及格的学生的学号,select Sno,from Sc,where Cno=002and Grade70 and max(成绩)=5;,6.查询平均分大于80分的学生的学号、平均成绩。,select 学号,avg(成绩)平均成绩,from sc,group by 学号,having avg(成绩)80;,7.查询95033班每个学生所选课程的平均分，学号。（使用两种做法嵌套查询和连接查询）,select 学号,avg(成绩)平均成绩,from sc,where 学号 in(select 学号 from student where 班级=95033),group by 学号;,select sc.学号,avg(成绩)平均成绩,from sc,student,where sc.学号=student.学号 and 班级=95033,group by sc.学号;,8.以选修 3-105为例,查询成绩高于109号同学的所有同学的学生表所有记录。（此题必须使用嵌套查询,层层深入）,select*,from student,where 学号 in,(select 学号 from sc where,课程号=3-105,and 成绩(select 成绩 from sc where 学号=109 and 课程号=3-105);,9.查询与学号为108的同学同岁的所有学生的学号、姓名和年龄。（此题要使用两种方法解答嵌套查询和自身连接查询）,select x.学号,x.姓名,x.年龄,from student x,student y,where x.年龄=y.年龄 and y.学号=108,select 学号,姓名,年龄,from student,where 年龄in(select 年龄 from student where 学号=108);,10.查询选修其课程的学生人数多于5人的教师姓名。,select 姓名 as 教师姓名,from teacher,where 教师号 in,(select 教师号 from course where 课程号 in(select 课程号 from sc group by 课程号 having count(*)5);,11.查询选修3-105课程的成绩比3-105课程的平均成绩低的学生的学号、课程号、成绩。,select*,from sc,where 成绩=2;,13.查询每门课最高分的学生的学号、课程号、成绩。（假定成绩不重复）,select*,from sc,where 成绩 in(select max(成绩)from sc group by 课程号);,select b.学号,a.课程号,a.成绩,from(select max(成绩)成绩,课程号 from sc group by 课程号)as a,sc as b,where a.课程号=b.课程号 and a.成绩=b.成绩,SELECT t1.学号,t1.课程号,成绩 FROM SC t1 WHERE 成绩 IN(SELECT TOP 1 成绩 FROM SC WHERE t1.学号=学号 ORDER BY score DESC)ORDER BY t1.学号;,14.查询与李军同性别并同班的同学名字。,select 姓名,from student,where 性别=(select 性别 from student where 姓名=李军)and 班级=(select 班级 from student where 姓名=李军);,15.查询选修计算机导论课程的男同学的成绩表。,select*,from sc,where 课程号=(select 课程号 from course where 课程名=计算机导论),and 学号 in(select 学号 from student where 性别=男);,