lab3-select基础

1.查询全部课程的详细记录;

select * from COURSES;

2.查询所有有选修课的学生的编号;

select distinct sid from choices;

3.查询课时<88(小时)的课程的编号;

select cid from COURSES where hour < 88;

4.请找出总分超过400分的学生;

select sid from CHOICES
group by sid
having SUM(isnull(score,0)) > 400;

5.查询课程的总数;

select COUNT(cid) from COURSES;

6.查询所有课程和选修该课程的学生总数;

select  cname,count(sid) as student_cnt
from COURSES c ,choices ch
where c.cid = ch.cid
group by cname;

7.查询选修成绩超过60的课程超过两门的学生编号;

select sid 
from CHOICES
where score > 60
group by sid
having COUNT(cid)>2;

8.统计各个学生的选修课程数目和平均成绩;

select  sid, COUNT(cid) as coursesnum, AVG(isnull(score,0)) as avg_score 
from CHOICES
group by sid;

9.查询选修Java的所有学生的编号及姓名;

select  distinct s.sid as studetn_id, snamefrom students s ,COURSES c ,choices chwhere s.sid = ch.sid and c.cid = ch.cid and cname = 'java';

10.查询姓名为sssht的学生所选的课程的编号和成绩;

select  cid,scorefrom students s ,choices chwhere sname = 'sssht' and s.sid = ch.sid  ;

11.查询其他课时比课程C++多的课程的名称;

select  c2.cnamefrom COURSES c1, COURSES c2where c1.cname = 'c++' and c2.hour > c1.hour;