第6章课后2,3题答案

2．对于教学管理数据库的三个基本表 S(SNO,SNAME, SEX, AGE,SDEPT)

SC (SNO,CNO,GRADE)

C(CNO,CNAME,CDEPT,TNAME) 试用T-SQL查询语句表达下列查询：

(1) 查询“王志强”所授课程的课程号和课程名。 (2) 查询年龄大于20岁的男学生的学号和姓名。

(3) 查询学号为S6的学生所学课程的课程名和任课教师名。

(4) 查询至少选修“王志强”老师所授课程中一门课程的女学生姓名。 (5) 查询“李小刚”同学不学的课程的课程号。

(6) 查询至少选修两门课程的学生学号。 解：

(1) USE JXGL

GO

SELECT CNO,CNAME FROM C

WHERE TNAME='王志强' GO

(2) USE JXGL

GO

SELECT SNO,SNAME FROM S

WHERE SEX='M' AND AGE>20 GO

(3) USE JXGL

GO

SELECT CNAME,TNAME

FROM SC JOIN C ON S.SNO='S6'

And SC.CNO=C.CNO

GO

(4) USE JXGL

GO

SELECT SNAME FROM S

WHERE sex=’F’ and SNO IN

(

SELECT SNO FROM SC WHERE CNO IN (SELECT CNO FROM C

WHERE TNAME='王志强' ) )

GO

方法二：

SELECT SNAME

From S join sc on s.sno=sc.sno and s.sex=’F’ Join c on sc.cno=c.cno and c.tname='王志强' (5) USE JXGL

GO

SELECT CNO FROM C EXCEPT SELECT CNO

FROM S JOIN SC ON S.SNO=SC.SNO AND S.SNAME='李小刚' GO

(6) USE JXGL

GO

select SNO,count(CNO) as 选修门数 from sc

group by SNO having count(CNO)>1 GO

3. 试用T-SQL查询语句表达下列对习题2数据库中三个基本表S、SC、C的查询： (1) 统计有学生选修的课程门数。

(2) 求选修C4号课程的学生的平均年龄。

(3) 求“王志强”老师所授课程的每门课程的学生平均成绩。 (4) 统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。 (5) 查询姓“王”的所有学生的姓名和年龄。

(6) 在SC中查询成绩为空值的学生学号和课程号。

(7) 查询年龄大于女同学平均年龄的男学生姓名和年龄。 解：

(1) USE JXGL

GO

SELECT COUNT(DISTINCT CNO) FROM SC GO

(2) USE JXGL

GO

SELECT AVG(AGE)

FROM S JOIN SC ON S.SNO=SC.SNO AND CNO='C4' GO

(3) SELECT SC.CNO,AVG(GRADE)

FROM SC

where cno in (SELECT cno from c where TNAME='王志强') GROUP BY SC.CNO

方法二

USE JXGL

GO

SELECT SC.CNO,AVG(GRADE)

FROM SC JOIN C ON SC.CNO=C.CNO AND TNAME='王志强' GROUP BY SC.CNO GO

(4) USE JXGL

GO

SELECT CNO,COUNT(SNO) FROM SC

GROUP BY CNO HAVING COUNT(*)>10 ORDER BY 2 DESC,1 GO

(5) USE JXGL

GO

SELECT SNAME,AGE FROM S

WHERE SNAME LIKE '王%' GO

(6) USE JXGL

GO

SELECT SNO,CNO FROM SC

WHERE GRADE IS NULL GO

(7) USE JXGL

GO

SELECT SNAME,AGE

FROM S

WHERE SEX='M' AND AGE>(SELECT AVG(AGE) FROM S

WHERE SEX='F')

GO