寂寂无言GO 4. 查询与学生张建国同岁的所有学生的学号、姓名和系别。
/*方法一*/ USE jiaoxuedb SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业 FROM Student WHERE Age=ANY GO /*方法二*/ USE jiaoxuedb SELECT s2.Sno,s2.Sname,s2.Dept FROM Student s1, Student s2 WHERE s1.age=s2.age AND s1.Sname='张建国' AND s2.Sname <> '张建国'
(SELECT Age FROM Student WHERE Sname='张建国') AND Sname <> '张建国' ORDER BY Age DESC 5. 查询选修了2门以上课程的学生名单。
USE jiaoxuedb SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业 FROM Student WHERE Sno=ANY GO (SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >=2) 6. 查询至少有一门与“张建国”选课相同的学生的姓名、课程名和系别。
USE jiaoxuedb SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业 FROM Student WHERE Sno=ANY (SELECT Sno FROM SC WHERE Cno=ANY (SELECT Cno FROM SC WHERE Sno=’991103’)) AND Sname <> '张建国' GO 7. 查询成绩比该课程平均成绩高的学生的成绩表。
/*方法一*/ USE jiaoxuedb SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业 FROM Student WHERE Sno=ANY GO /*方法二*/ SELECT * FROM SC sc1 WHERE Score > GO (SELECT AVG(Score) FROM SC sc2) AND sc1.Cno=sc2.Cno; (SELECT Sno FROM SC x WHERE Score >= (SELECT AVG(Score) FROM SC y WHERE y.Sno=x.Sno)); 8. 查询选修课号为01001课程且成绩高于课程01002学生的姓名、此两门课程的k
而成名和成绩。
/*方法一*/ USE jiaoxuedb SELECT Sname,Cname,Score FROM Student ,SC,Course WHERE Student.Sno= GO /*方法二*/ USE jiaoxuedb SELECT Sname,Cname,Score FROM Student ,SC,Course WHERE Student.Sno= (SELECT s1.Sno FROM SC s1,SC s2 WHERE s1.Cno='01001' AND s2.Cno='01002' AND s1.Score>s2.Score AND AND SC.Sno=Student.Sno AND Course.Cno=SC.Cno s1.Sno=s2.Sno) GO (SELECT Sno FROM SC x WHERE Score >= (SELECT AVG(Score) FROM SC y WHERE y.Sno=x.Sno AND x.Cno='01001' AND y.Cno='01002') AND SC.Sno=Student.Sno AND Course.Cno=SC.Cno) 9. 查询所有未修01001号课程的学生名单。
USE jiaoxuedb SELECT Student.Sno,Sname FROM Student WHERE Student.Sno IN GO
(SELECT s1.Sno FROM SC s1 WHERE Sno NOT IN (SELECT s2.Sno FROM SC s2 WHERE Cno='01001')) 10. 查询每个同学各门课程的平均成绩和最高成绩，按降序排列输出姓名、平均成绩、
最高成绩。
USE jiaoxuedb SELECT Sname AS 姓名,AVG(Score) AS 平均,MAX(Score) AS 最高 FROM Student,SC WHERE Student.Sno=SC.Sno GROUP BY Sname ORDER BY Sname DESC GO
11. 查询所有学生都选修了的课程号和课程名。 USE jiaoxuedb SELECT Cname AS 课程号,Cno AS 课程名 FROM Course WHERE NOT EXISTS (SELECT * FROM Student WHERE NOT EXISTS GO
(SELECT * FROM SC WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno)); 12. 查询选修了991102号学生选修了的课程的学生学号和姓名。
use jiaoxuedb SELECT DISTINCT Sno，Sname FROM SC s1 WHERE NOT EXISTS (SELECT Cno FROM SC s2 WHERE Sno='991102' AND NOT EXISTS (SELECT Cno FROM SC s3 WHERE s1.Sno=s3.Sno AND s2.Cno=s3.Cno)) AND s1.Sno!= '991102' GO 13. 建立教材中第2章事例数据库中的3个表，并装入相应数据，执行第3章中的SQL
语句示例，并查看执行结果。
14. 建立教材中第2章习题7中的4个表，并装入相应数据，完成第3章习题4、5、
11，并查看执行结果。
15. 向SC中增加几个选修了英语课但无成绩（grade值为空值）的学生，用SQL语句
实现找出这些无成绩学生，并查看执行结果。
