数据库系统原理——习题

已知关系模式：

学生 S(SNO,SNAME,AGE,SEX,SDEPT)

课程 C(CNO,CNAME,CDEPT,TNAME)

选修 SC(SNO,CNO,GRADE)

写出SQL语句。

1.检索至少选修两门课程的学生学号。

SELECT DISTINCT SC1.SNO FROM SC AS SC1,SC AS SC2
WHERE SC1.SNO = SC2.SNO
AND SC1.CNO <> SC2.CNO;

或者

SELECT SNO FROM SC
GROUP BY SNO
HAVING COUNT(DISTINCT CNO) >= 2;

2.统计有学生选修的课程门数。

SELECT COUNT(DISTINCT CNO) FROM SC; 

3.选修C4课程的学生的平均年龄。

SELECT AVG(AGE) FROM S,SC
WHERE S.SNO = SC.SNO
AND SNO = 'C4';

4.求LIU老师所授课程的每门课程的学生平均成绩。

SELECT SC.CNO,CNAME,AVG(GRADE) FROM SC,C
WHERE SC.CNO = C.CNO
AND TNAME = 'LIU'
GROUP BY SC.CNO,CNAME;

5.统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

SELECT CNO,COUNT(SNO) FROM SC
GROUP BY CNO
HAVING COUNT(*) > 10
ORDER BY 2 DESC,1;

6.检索LIU老师所授课程的课程号，课程名。

SELECT CNO,CNAME FROM C
WHERE TNAME = 'LIU';

7.检索年龄大于23岁的男同学的学号和姓名。

SELECT SNO,SNAME FROM S
WHERE AGE > 23
AND SEX = '男';

8.检索学号为S3学生所学课程的课程名和任课教师名。

SELECT CNAME,TNAME FROM C,SC
WHERE C.CNO = SC.CNO
AND SC.SNO = 'S3';

或者

SELECT CNAME,TNAME FROM C
WHERE CNO IN(
SELECT CNO FROM SC
WHERE SNO = 'S3'
);

9.检索至少选修LIU老师所授课程中一门课的女学生的姓名。

SELECT SNAME FROM S,SC,C
WHERE S.SNO = SC.SNO
AND C.CNO = SC.CNO
AND TNAME = 'LIU'
AND SEX = '女';

10.检索WAN同学不学的课程的课程号。

SELECT CNO FROM C
WHERE CNO NOT IN(
SELECT SC.CNO FROM S,SC
WHERE S.SNO = SC.SNO
AND S.SNAME = 'WAN'
);