数据库复习2
一. 简答题（共1题，100分）
1. (简答题)
存在数据库test，数据库中有如下表：
1.学生表
Student(Sno,Sname,Sage,Ssex) --Sno 学号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
主键Sno
2.教师表
Teacher(Tno,Tname) --Tno 教师编号,Tname 教师姓名
主键Tno
3.课程表
Course(Cno,Cname,Tno) --Cno --课程编号,Cname 课程名称,Tno 教师编号
主键Cno 外键Tno参照教师表
4.成绩表
SC(Sno,Cno,score) --Sno 学生编号,Cno 课程编号,score 分数
主键（Sno,Cno） 外键Sno,Cno分别参照学生表和课程表
按要求写出如下SQL语句：
1、查询已选课程的课程号与学生选修人数
2、查询所有课程的课程号、课程名与学生选修人数（考虑未选课程的信息，未选课程就显示0）
3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（要考虑没有选课的同学，没有选课的同学选课数为0，总成绩为0）
4、查询学过"吴大"老师授课的同学的信息
5、查询没学过"吴大"老师授课的同学的信息
6、查询学过编号为"1"并且也学过编号为"2"的课程的同学的学号
7、查询学过编号为"1"并且也学过编号为"2"的课程的同学的学号，姓名，按学号降序。
8、查询学过编号为"1"但是没有学过编号为"2"的课程的同学的学号，姓名，按学号降序。
我的答案：
1.
select Cno，COUNT(Sno) cnt_student
from SC
group by Cno
2.
select co.Cno,co.Cname. ifnull(c.num,0) cnt_student
from Course co
left join（
select Cno，COUNT(Sno) num
from sc
group by Cno
）c
on c.Cno=co.Cno
3.
select Sno,Sname,ifnull(num,0) cnt_score,ifnull(the_score,0) total_score
from student s
left join (
select Sno,COUNT(*) num,SUM(score) the_score
from SC
group by Sno
) c
where s.Sno=c.Sno
4.
select s.*
from student s,Teacher t,Course co,SC c
where s.Sno=c.Sno and c.Cno=co.Cno and co.Tno = t.Tno and Tname='吴大'
5.
select s.*
from student s,SC c
where s.Sno = c.Sno and c.Sno not in(
select s.Sno
from student s,Teacher t,Course co,SC c
where s.Sno=c.Sno and c.Cno=co.Cno and co.Tno = t.Tno and Tname='吴大'
)
6.
select Sno
from SC
where Cno ='1' and Sno in(
select Sno
from SC
where Cno ='2'
)
7.
select s.Sno,s.Sname
from Student s,SC c
where s.Sno=c.Sno and c.Cno = '1' and c.Sno in(
select Sno
from SC
where Cno ='2'
)
order by s.Sno desc
8.
select s.Sno,s.Sname
from Student s,SC c
where s.Sno=c.Sno and c.Cno = '1' and c.Sno not in(
select Sno
from SC
where Cno ='2'
)
order by s.Sno desc
标准参考答案
1、SELECT cno,count(*) from sc GROUP BY cno;
2、SELECT course.Cno,Cname,COUNT(sc.sno) from course LEFT OUTER JOIN sc ON course.cno=sc.Cno
GROUP BY course.Cno;
3、SELECT student.sno,student.Sname, count(sc.sno), IFNULL(sum(sc.score),0)
FROM student LEFT OUTER JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno ;
4、 SELECT student.*
from student,sc,course,teacher
where teacher.Tno=course.Tno and course.Cno=sc.Cno and sc.Sno=student.Sno
and teacher.Tname='吴大';
5、 SELECT s.* from student s
WHERE sno not in (SELECT student.sno
from student,sc,course,teacher
where teacher.Tno=course.Tno and course.Cno=sc.Cno and sc.Sno=student.Sno
and teacher.Tname='吴大');
6、错误：select sno from sc where cno='1' or cno='2';
正确：
select cc1.sno from sc cc1
where cno='1' and EXISTS
(SELECT * from sc cc2 where cc1.sno=cc2.sno and cc2.cno='2');
或：
select sno from sc where cno='1' and sno in(
select sno from sc where cno='2') ;
或者用派生表：
select sno from sc where cno='1' and sno in(
select s_sno from (select sno from sc where cno='2') as s_sc(s_sno));
7、select student.sno,student.Sname from student, sc cc1
where student.sno=cc1.Sno and cno='1' and EXISTS
(SELECT * from sc cc2 where cc1.sno=cc2.sno and cc2.cno='2')
ORDER BY student.Sno DESC;
8、select student.sno,student.Sname from student, sc cc1
where student.sno=cc1.Sno and cno='1' and NOT EXISTS
(SELECT * from sc cc2 where cc1.sno=cc2.sno and cc2.cno='2')
ORDER BY student.Sno DESC;