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

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 最大年龄, MIN(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 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 EXISTS (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.Semester = 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 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 DESC)或者 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. SELECT 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.Sno 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, Student.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 = VB)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 JOIN 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 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 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 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 = Grad