----针对S_T数据库，完成以下操作：

----1. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；

select cno 课程号 ,count() 总人数,max(grade) 最高成绩,min(grade) 最低成绩,(sum(grade)/count()) 平均成绩

from sc

group by cno

order by cno desc;

----2. 列出有二门以上课程（含两门）不及格的学生的学号及不及格门数；

select sno 学号,count(*) 不及格门数

from sc

where grade<60

group by sno

having count(*)>=2;

----3. 查询名字中第2个字为‘勇’的学生姓名和学号及选修的课程号、课程名；

select sname 姓名,a.sno 学号,c.cno 课程号, cname 课程名

from student a,course b,sc c

where a.sno=c.sno and b.cno =c.cno and a.sno in (

select sno

from student

where sname like ‘_勇%’

);

----4. 查询至少选修了一门间接先行课为“5”号课程的学生姓名；

select sname

from student

where sno in (select sno

from sc

where cno in (select cno

from course

where cpno in (select cno

from course

where cpno=‘5’)));

----5. 查询选修了“数据库”和“数学”两门课程的学生的学号；

select sno

from sc

where cno in(select cno

from course

where cname =‘数据库’)

intersect

select sno

from sc

where cno in

(select cno

from course

where cname =‘数学’)

----6. 找出至少选修了“200515004”号同学所选修课程的学生学号；

select sno 学号

from sc a

where a.sno=‘200515004’ and not exists

(select *

from sc b

where b.sno=‘200515004’ and not exists

(select *

from sc c

where a.sno=c.sno and b.cno=c.cno))

----7. 找出“数据库系统”这门课成绩最高的学生学号，姓名；

select sno 学号,sname 姓名

from student

where sno in(

select sno

from sc

where cno in (select cno

from course

where cname=‘数据库系统’) and grade >=

(select max(grade)

from sc

where cno in

(select cno

from course

where cname=‘数据库系统’)

group by cno));

----8. 找出选修了“2”课程但没有选修“1”课程的学生姓名；

select sname

from student

where sno in

(select sno

from sc

where cno=‘2’)and sno not in (select sno

from sc

where cno=‘1’);

----9. 找出被所有同学选修了的课程号；–反义：没有一门课程没有被所有同学选修

select distinct cno 课程号

from sc as a

where not exists

(select *

from sc as b

where not exists(select *

from sc as c

where a.cno=c.cno and b.sno=c.sno));

----10. 查询没有选课的学生姓名。

select sname

from student

where sno not in

(select sno

from sc);