数据库原理与应用(何玉洁-梁琦编著)第五章课后习题答案(共8页).doc

《数据库原理与应用(何玉洁-梁琦编著)第五章课后习题答案(共8页).doc》由会员分享，可在线阅读，更多相关《数据库原理与应用(何玉洁-梁琦编著)第五章课后习题答案(共8页).doc（8页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上1. SELECT *FROM SC2. SELECT Sname, SageFROM StudentWHERE (Sdept = 计算机系)3. SELECT Sno, Cno, GradeFROM SCWHERE (Grade BETWEEN 70 AND 80)4. SELECT Sname, SageFROM StudentWHERE (Sdept = 计算机系) AND (Ssex = 男)5. SELECT MAX(Grade) AS c01最高成绩FROM SCWHERE (Cno = c01)6. SELECT MAX(Sage) AS 最大年龄, M

2、IN(Sage) AS 最小年龄FROM StudentWHERE (Sdept = 计算机系)7. SELECT Sdept, COUNT(*) AS 学生人数FROM StudentGROUP BY Sdept8. SELECT Cno, COUNT(*) AS 选课人数, MAX(Grade) AS 最高分FROM SCGROUP BY Cno9. SELECT Sno, COUNT(*) AS 选课门数, SUM(Grade) AS 总成绩FROM SCGROUP BY SnoORDER BY COUNT(*)10. SELECT Sno, SUM(Grade) AS 总成绩FROM

3、SCGROUP BY SnoHAVING (SUM(Grade) 200)11. SELECT Sname, SdeptFROM StudentWHERE (Sno IN (SELECT sno FROM sc WHERE cno = c02)12. SELECT s.Sname, SC.Cno, SC.GradeFROM Student s INNER JOIN SC ON s.Sno = SC.SnoWHERE (SC.Grade 80)ORDER BY SC.Grade DESC13. SELECT Sno, Sname, SdeptFROM StudentWHERE (NOT EXIS

4、TS (SELECT * FROM sc WHERE sc.sno = student.sno)或者 SELECT Sno, Sname, SdeptFROM StudentWHERE (Sno NOT IN (SELECT sno FROM sc)14. SELECT Cname, SemesterFROM CourseWHERE (Semester = (SELECT semester FROM course WHERE cname = VB)或者 SELECT c2.Cname, c2.SemesterFROM Course c1 INNER JOIN Course c2 ON c1.S

5、emester = c2.SemesterWHERE (c1.Cname = VB)15. SELECT s2.Sname, s2.Sdept, s2.SageFROM Student s1 INNER JOIN Student s2 ON s1.Sage = s2.SageWHERE (s1.Sname = 李勇) AND (s2.Sname 李勇)或者 SELECT Sname, Sdept, SageFROM StudentWHERE (Sage = (SELECT sage FROM student WHERE sname = 李勇) AND (Sname 李勇)16. SELECT

6、TOP 2 WITH TIES Sname, SageFROM StudentWHERE (Sdept = 计算机系)ORDER BY Sage17. SELECT Student.Sname, Student.Sdept, SC.GradeFROM Student INNER JOIN SC ON Student.Sno = SC.SnoWHERE (SC.Grade IN (SELECT TOP 2 WITH ties grade FROM sc WHERE cno = (SELECT cno FROM course WHERE cname = VB) ORDER BY grade DES

7、C)或者 SELECT TOP 2 WITH TIES Student.Sname, Student.Sdept, SC.GradeFROM Student INNER JOIN SC ON Student.Sno = SC.Sno INNER JOIN Course ON SC.Cno = Course.CnoWHERE (Course.Cname = VB)ORDER BY SC.Grade DESC18. SELECT TOP 2 WITH TIES Sno, COUNT(*) AS 选课门数FROM SCGROUP BY SnoORDER BY COUNT(*) DESC19. SEL

8、ECT TOP 1 WITH TIES Sdept, COUNT(*) AS 学生人数FROM StudentGROUP BY SdeptORDER BY COUNT(*) DESC20. （1）SELECT Sname, SdeptFROM StudentWHERE (Sno IN (SELECT sno FROM sc WHERE cno = c01)（2） SELECT Student.Sno, Student.Sname, SC.Cno, SC.GradeFROM Student INNER JOIN SC ON Student.Sno = SC.SnoWHERE (Student.S

9、no IN (SELECT sno FROM sc WHERE grade 80 AND sno IN (SELECT sno FROM student WHERE sdept = 数学系)（3）SELECT Student.SnameFROM Student INNER JOIN SC ON Student.Sno = SC.SnoWHERE (SC.Grade = (SELECT MAX(grade) FROM sc WHERE sno IN (SELECT sno FROM student WHERE sdept = 计算机系)（4）SELECT Student.Sname, Stude

10、nt.Sdept, SC.Cno, SC.GradeFROM Student INNER JOIN SC ON Student.Sno = SC.SnoWHERE (SC.Grade = (SELECT MAX(grade) FROM sc WHERE cno = (SELECT cno FROM course WHERE cname = 数据结构)21. SELECT Sname, SdeptFROM StudentWHERE (Sno NOT IN (SELECT sno FROM sc WHERE cno = (SELECT cno FROM course WHERE cname = V

11、B)22. SELECT Sname, SsexFROM StudentWHERE (Sno NOT IN (SELECT sno FROM sc) AND (Sdept = 计算机系)或者SELECT Sname, SsexFROM StudentWHERE (NOT EXISTS (SELECT * FROM sc WHERE sno = student.sno) AND (Sdept = 计算机系)23. SELECT Student.Sname, Course.CnameFROM Student INNER JOIN SC ON Student.Sno = SC.Sno INNER J

12、OIN Course ON SC.Cno = Course.CnoWHERE (Student.Sno = (SELECT TOP 1 WITH ties sno FROM sc GROUP BY sno HAVING sc.sno IN (SELECT student.sno FROM student WHERE sdept = 计算机系) ORDER BY AVG(grade) ASC)24. SELECT Cname, Semester, CreditFROM CourseWHERE (Cno IN (SELECT TOP 1 WITH ties cno FROM sc GROUP BY

13、 cno ORDER BY COUNT(*) ASC) AND (Semester BETWEEN 1 AND 5)25. （1）INSERT INTO test_t (COL2)VALUES (B1) （2）INSERT INTO test_t (COL1, COL2, COL3)VALUES (1, B2, C2) （3）INSERT INTO test_t (COL1, COL2)VALUES (2, B3)26. DELETE FROM SCWHERE (Grade 50)27. DELETE FROM CourseWHERE (Cno NOT IN (SELECT cno FROM

14、sc) 或者 DELETE FROM CourseWHERE (NOT EXISTS (SELECT * FROM sc WHERE cno = o)28. DELETE FROM SCWHERE (Grade 60) AND (Sno IN (SELECT sno FROM student WHERE sdept = 计算机系) AND (Cno IN (SELECT cno FROM course WHERE cname = VB)29. DELETE FROM SCWHERE (Grade = (SELECT MIN(grade) FROM sc WHERE cno = (SELECT

15、cno FROM course WHERE cname = VB)30. UPDATE CourseSET Credit = Credit + 2WHERE (Semester = 2)31. UPDATE CourseSET Credit = 3WHERE (Cname = VB)32. UPDATE StudentSET Sage = Sage + 1WHERE (Sdept = 计算机系)33. UPDATE SCSET Grade = Grade + 5WHERE (Sno IN (SELECT sno FROM student WHERE sdept = 信息系) AND (Cno IN (SELECT cno FROM course WHERE cname = 计算机文化学)34. UPDATE CourseSET Credit = Credit - 1WHERE (Cno = (SELECT TOP 1 o FROM sc GROUP BY cno ORDER BY COUNT(*)专心-专注-专业