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

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 最大年龄, MIN(Sage) AS 最

2、小年龄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 Sn

3、oHAVING (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 *

4、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.

5、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 TI

6、ES 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 T

7、OP 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 WIT

8、H 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

9、 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.

10、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

11、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

12、 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

13、 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

14、 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 cour

15、se 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 cn

16、o FROM course WHERE cname = 计算机文化学)34. UPDATE CourseSET Credit = Credit - 1WHERE (Cno = (SELECT TOP 1 o FROM sc GROUP BY cno ORDER BY COUNT(*)附录资料：不需要的可以自行删除记叙文基础知识（一）记叙文的特点1、概念：以记叙、描写为主要表达方式，以写人记事，写景状物为主要内容的文章。2、分类：A、按样式分：通讯、回忆录、游记、新闻、参观访问记、民间故事。B、记叙内容分：写人、叙事、写景、状物。（二）记叙的要素和人称记叙的要素：时间、地点、人物、事件的起因、经

17、过和结果。A、时间：一般用时间词表示；也有的间接表示事件的发生时间。B、地点：单一地点，事件在一处发生；还有的事件随情节发展而转换。C、人物：有主要人物和次要人物之分。主要人物往往体现文章的中心思想，次要人物帮助形成完整的情节，并对主要人物起衬托作用。D、起因：指引发事件的原因，一般在开头，也有在后头（倒叙）E、经过：指事件发展的过程，复杂事件的经过往往包含若干阶段，可先概括再综合。F、结果：指事件的结尾，一般也用记叙描写的表达方式，它是事件的一部分。如以抒情议论结尾的不能当作结果。记叙的人称：A、第一人称：以“我”、“我们”的身份叙述。“我”是其中的一个人物，文章记叙的人和事都是“我”的所见

18、、所闻、所想，容易使读者产生真实而亲切的感觉。文中如有心理活动描写必定是“我”的，其他人物的心理活动只能是“我”猜测的，不可直接描写他人的心理活动。B、第二人称：出现在用第一人称或第三人称的叙述中，出于直接抒情的需要。但记叙文通篇一般不采用第二人称。C、第三人称：指作者站在“第三者”的立场上把人物的经历和事件的经过告诉读者。文中每人物都有自己的名字或称谓，没有“我”这个代词。其好处在于人物事件的展开不受时间和空间的限制，有比较广阔的活动范围，便于塑造人物形象和表现文章主题，文中每个人物的心理活动都可描写。（三）记叙的顺序记叙的顺序指记叙文材料安排方式，一般可分为顺叙、倒叙、插叙。1、顺叙：记叙

19、的材料按时间推移、地点转移和事件发展的先后顺序来叙述的方法。用顺叙写的文章、层次、段落和事件发展的过程基本一致，可从时间推移、程序推进、空间转换等标志着手，理清思路。2、倒叙：先把事件的结果写出，或先把后发生的事（或是某个突出的片断或结局）叙述，然后再按事情的发生发展记叙，最后落到结果，使首尾呼应。其注意点：开头一般先写结果，或是由眼前的事物引起回忆；开始追述时，文章一般要用一两句话表明以下要追述以往的事；追述部分仍按时间顺序写。3、插叙：在顺叙过程中插入与中心思想有关的事件。分析插叙要把握两点：一是转入插叙的过渡句。二是由插叙转到顺叙的过渡句，它们衔接要自然、完美。阅读时要注意插叙前后的过渡

20、性文字。插叙的作用是对主要情节或人物描写起补充、衬托的作用。（四）记叙的中心与材料1、记叙的中心：就是作者凭借记人写景，给景状物告诉读者，自己对生活的看法。2、中心的把握：（1）、内容：一是文章所表现的主要内容，二是作者的感受、文章的意义。（2）、方法：抓具体材料: 写人为主的，通过人物的语言、行动，从把握人物的性格特征和思想意义入手；叙事为主的，通过事件的起因、经过与结果进行归纳，从把握文章通过叙写的事件所要说明的道理入手；写景状物为主的，要根据所描写景物的特征，把握文章通过写景状物抒发的感情进行分析归纳。抓文章题目抓开头结尾抓议论抒情语句记叙的材料(内容)：就是根据需要围绕中心在写人记事写景状物所叙述的生活内容。材料必须为中心服务。抓记叙的要素：如：写人为主的材料主要有：人物的外貌、语言行动、心理描写人物在社会生活中的具体表现人物间的相互关系作者对人物的态度感情.记事为主的材料主要有：事件的发生、发展、结果人物在事件中的表现场面、细节描写作者对事件的态度.写景状物为主的材料有：景物特征景物与