数据库原理与应用(何玉洁-梁琦编著)第五章课后习题答案(共8页).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 = 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(*)专心-专注-专业
