数据库复习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;