查询作业1

----针对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 distinct 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);