數(shù)據(jù)庫原理與應(yīng)用(何玉潔-梁琦編著)第五章課后習(xí)題答案

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 學(xué)生人數(shù)FROM StudentGROUP BY Sdept8. SELECT Cno, COUNT(*) AS 選課人數(shù), MAX(Grade) AS 最高分FROM SCGROUP BY Cno9. SELECT Sno, COUNT(*) AS 選課門數(shù), 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 選課門數(shù)FROM SCGROUP BY SnoORDER BY COUNT(*) DESC19. SELECT TOP 1 WIT

8、H TIES Sdept, COUNT(*) AS 學(xué)生人數(shù)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 = 數(shù)學(xué)系)（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 = 數(shù)據(jù)結(jié)構(gòu))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 = 計算機文化學(xué))34. UPDATE CourseSET Credit = Credit - 1WHERE (Cno = (SELECT TOP 1 o FROM sc GROUP BY cno ORDER BY COUNT(*)附錄資料：不需要的可以自行刪除記敘文基礎(chǔ)知識（一）記敘文的特點1、概念：以記敘、描寫為主要表達(dá)方式，以寫人記事，寫景狀物為主要內(nèi)容的文章。2、分類：A、按樣式分：通訊、回憶錄、游記、新聞、參觀訪問記、民間故事。B、記敘內(nèi)容分：寫人、敘事、寫景、狀物。（二）記敘的要素和人稱記敘的要素：時間、地點、人物、事件的起因、經(jīng)

17、過和結(jié)果。A、時間：一般用時間詞表示；也有的間接表示事件的發(fā)生時間。B、地點：單一地點，事件在一處發(fā)生；還有的事件隨情節(jié)發(fā)展而轉(zhuǎn)換。C、人物：有主要人物和次要人物之分。主要人物往往體現(xiàn)文章的中心思想，次要人物幫助形成完整的情節(jié)，并對主要人物起襯托作用。D、起因：指引發(fā)事件的原因，一般在開頭，也有在后頭（倒敘）E、經(jīng)過：指事件發(fā)展的過程，復(fù)雜事件的經(jīng)過往往包含若干階段，可先概括再綜合。F、結(jié)果：指事件的結(jié)尾，一般也用記敘描寫的表達(dá)方式，它是事件的一部分。如以抒情議論結(jié)尾的不能當(dāng)作結(jié)果。記敘的人稱：A、第一人稱：以“我”、“我們”的身份敘述。“我”是其中的一個人物，文章記敘的人和事都是“我”的所見

18、、所聞、所想，容易使讀者產(chǎn)生真實而親切的感覺。文中如有心理活動描寫必定是“我”的，其他人物的心理活動只能是“我”猜測的，不可直接描寫他人的心理活動。B、第二人稱：出現(xiàn)在用第一人稱或第三人稱的敘述中，出于直接抒情的需要。但記敘文通篇一般不采用第二人稱。C、第三人稱：指作者站在“第三者”的立場上把人物的經(jīng)歷和事件的經(jīng)過告訴讀者。文中每人物都有自己的名字或稱謂，沒有“我”這個代詞。其好處在于人物事件的展開不受時間和空間的限制，有比較廣闊的活動范圍，便于塑造人物形象和表現(xiàn)文章主題，文中每個人物的心理活動都可描寫。（三）記敘的順序記敘的順序指記敘文材料安排方式，一般可分為順敘、倒敘、插敘。1、順敘：記敘

19、的材料按時間推移、地點轉(zhuǎn)移和事件發(fā)展的先后順序來敘述的方法。用順敘寫的文章、層次、段落和事件發(fā)展的過程基本一致，可從時間推移、程序推進、空間轉(zhuǎn)換等標(biāo)志著手，理清思路。2、倒敘：先把事件的結(jié)果寫出，或先把后發(fā)生的事（或是某個突出的片斷或結(jié)局）敘述，然后再按事情的發(fā)生發(fā)展記敘，最后落到結(jié)果，使首尾呼應(yīng)。其注意點：開頭一般先寫結(jié)果，或是由眼前的事物引起回憶；開始追述時，文章一般要用一兩句話表明以下要追述以往的事；追述部分仍按時間順序?qū)憽?、插敘：在順敘過程中插入與中心思想有關(guān)的事件。分析插敘要把握兩點：一是轉(zhuǎn)入插敘的過渡句。二是由插敘轉(zhuǎn)到順敘的過渡句，它們銜接要自然、完美。閱讀時要注意插敘前后的過渡

20、性文字。插敘的作用是對主要情節(jié)或人物描寫起補充、襯托的作用。（四）記敘的中心與材料1、記敘的中心：就是作者憑借記人寫景，給景狀物告訴讀者，自己對生活的看法。2、中心的把握：（1）、內(nèi)容：一是文章所表現(xiàn)的主要內(nèi)容，二是作者的感受、文章的意義。（2）、方法：抓具體材料: 寫人為主的，通過人物的語言、行動，從把握人物的性格特征和思想意義入手；敘事為主的，通過事件的起因、經(jīng)過與結(jié)果進行歸納，從把握文章通過敘寫的事件所要說明的道理入手；寫景狀物為主的，要根據(jù)所描寫景物的特征，把握文章通過寫景狀物抒發(fā)的感情進行分析歸納。抓文章題目抓開頭結(jié)尾抓議論抒情語句記敘的材料(內(nèi)容)：就是根據(jù)需要圍繞中心在寫人記事寫景狀物所敘述的生活內(nèi)容。材料必須為中心服務(wù)。抓記敘的要素：如：寫人為主的材料主要有：人物的外貌、語言行動、心理描寫人物在社會生活中的具體表現(xiàn)人物間的相互關(guān)系作者對人物的態(tài)度感情.記事為主的材料主要有：事件的發(fā)生、發(fā)展、結(jié)果人物在事件中的表現(xiàn)場面、細(xì)節(jié)描寫作者對事件的態(tài)度.寫景狀物為主的材料有：景物特征景物與