数据库原理与应用(何玉洁-梁琦编著)第五章课后习题答案
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1. SELECT *
FROM SC
2. SELECT Sname, Sage
FROM Student
WHERE (Sdept = '计算机系')
3. SELECT Sno, Cno, Grade
FROM SC
WHERE (Grade BETWEEN 70 AND 80)
4. SELECT Sname, Sage
FROM Student
WHERE (Sdept = '计算机系') AND (Ssex = '男')
5. SELECT MAX(Grade) AS c01最高成绩
FROM SC
WHERE (Cno = 'c01')
6. SELECT MAX(Sage) AS 最大年龄, MIN(Sage) AS 最小年龄FROM Student
WHERE (Sdept = '计算机系')
7. SELECT Sdept, COUNT(*) AS 学生人数
FROM Student
GROUP BY Sdept
8. SELECT Cno, COUNT(*) AS 选课人数, MAX(Grade) AS 最高分FROM SC
GROUP BY Cno
9. SELECT Sno, COUNT(*) AS 选课门数, SUM(Grade) AS 总成绩FROM SC
GROUP BY Sno
ORDER BY COUNT(*)
10. SELECT Sno, SUM(Grade) AS 总成绩
FROM SC
GROUP BY Sno
HA VING (SUM(Grade) > 200)
11. SELECT Sname, Sdept
FROM Student
WHERE (Sno IN
(SELECT sno
FROM sc
WHERE cno = 'c02'))
12. SELECT s.Sname, o, SC.Grade FROM Student s INNER JOIN
SC ON s.Sno = SC.Sno
WHERE (SC.Grade > 80)
ORDER BY SC.Grade DESC
13. SELECT Sno, Sname, Sdept
FROM Student
WHERE (NOT EXISTS
(SELECT *
FROM sc
WHERE sc.sno = student.sno))
或者 SELECT Sno, Sname, Sdept
FROM Student
WHERE (Sno NOT IN
(SELECT sno
FROM sc))
14. SELECT Cname, Semester
FROM Course
WHERE (Semester =
(SELECT semester
FROM course
WHERE cname = 'VB'))
或者
SELECT ame, c2.Semester
FROM Course c1 INNER JOIN
Course c2 ON c1.Semester = c2.Semester WHERE (ame = 'VB')
15. SELECT s2.Sname, s2.Sdept, s2.Sage FROM Student s1 INNER JOIN
Student s2 ON s1.Sage = s2.Sage
WHERE (s1.Sname = '李勇') AND (s2.Sname <> '李勇')
或者
SELECT Sname, Sdept, Sage
FROM Student
WHERE (Sage =
(SELECT sage
FROM student
WHERE sname = '李勇')) AND (Sname <> '李勇')
16. SELECT TOP 2 WITH TIES Sname, Sage
FROM Student
WHERE (Sdept = '计算机系')
ORDER BY Sage
17. SELECT Student.Sname, Student.Sdept, SC.Grade
FROM Student INNER JOIN
SC ON Student.Sno = SC.Sno
WHERE (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.Grade FROM Student INNER JOIN
SC ON Student.Sno = SC.Sno INNER JOIN
Course ON o = o
WHERE (ame = 'VB')
ORDER BY SC.Grade DESC
18. SELECT TOP 2 WITH TIES Sno, COUNT(*) AS 选课门数
FROM SC
GROUP BY Sno