查询课程数mysql_mysql_数据查询练习

course：

teachar:

student

sc:

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

select a.sid from

(select sid,score from sc where cid=001) as a,

(select sid,score from sc where cid=002) as b

where a.score > b.score and a.sid=b.sid;

2、查询平均成绩大于60 分的同学的学号和平均成绩；

select sid,score from sc

group by sid having avg(score);

3、查询所有同学的学号、姓名、选课数、总成绩；

select student.sid,student.sname,count(SC.cid),sum(score)

from student inner join sc on student.sid=sc.sid

group by student.sid,sname;

4、查询姓“刘”的老师的个数；

select count(tName) from teacher

where tName like '刘%';

5、查询没学过“李老师”课的同学的学号、姓名；

select sid,sname from student where

sid not in (select distinct(sc.sid) from teacher,course,sc

where teacher.tName='李老师' and teacher.tid=course.tid and course.cid=sc.cid);

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

select student.sid,student.sname from student,sc

where student.sid=sc.sid and sc.cid=001 and exists(select * from sc as sc2

where sc2.sid=sc.sid and sc2.cid=002);

7、查询学过“李老师”所教的所有课的同学的学号、姓名；

select sid,sname from student

where sid in (select sid from teacher,course,sc

where teacher.tName='李老师' and teacher.tid=course.tid and course.cid=sc.cid

group by sid having count(sc.cid)=(select count(cid) from teacher,course

where teacher.tid=course.tid and teacher.tName='李老师'));

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；

select student.sid,student.sname from student,

(select sid,score from sc where cid=001) as a,

(select sid,score from sc where cid=002) as b

where a.score>b.score and student.sid=a.sid and student.sid=b.sid;

9、查询所有课程成绩小于60 分的同学的学号、姓名；

select sid,sname from student

where sid not in (select student.sid from student,sc

where student.sid=sc.sid and sc.score>60);

10、查询没有学全所有课的同学的学号、姓名；

select student.sid,student.sname from sc,student

where sc.sid=student.sid

group by sid having count(sc.cid)!=(select count(cid) from course);

11、查询至少有一门课与学号为"1001"的同学所学相同的同学的学号和姓名;

select student.sid,student.sname from student,sc

where student.sid=sc.sid

and sc.cid in (select cid from sc where sid=1001)

group by sid;

12、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select course.cid,course.cname,count(sc.cid),

sum(case when sc.score>=85 and sc.score<=100 then 1 else 0 end) '100-85',

sum(case when sc.score>=70 and sc.score<=85 then 1 else 0 end) '85-70',

sum(case when sc.score>=60 and sc.score<=70 then 1 else 0 end) '70-60',

sum(case when sc.score<=60 then 1 else 0 end) '60-0'

from course,sc

where course.cid=sc.cid

group by cid

13、查询出只选修了一门课程的全部学生的学号和姓名

select sc.sid,student.sname from student,sc

where student.sid=sc.sid

group by sc.sid having count(sc.cid)=1

14、查询男生、女生学生人数

select ssex,count(ssex) from student

group by ssex

15、查询姓"张"的学生名单

select sname from student

where sname like '张%'

16、查询同名同性学生名单，并统计同名人数 、

select sname,count(sname) from student

GROUP BY sname,ssex

having count(sname)>1

17、查询1981年出生的学生名单(注：student表中sage列的类型是datetime)

select sname,sage from student

where year(sage)=1981

18、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select cid,avg(score) from sc

GROUP BY cid

ORDER BY avg(score),cid desc

19、查询课程名称为"数据库"，且分数低于60的学生姓名和分数

select student.sname,sc.score from student,course,sc

where course.cname='数据库' and course.cid=sc.cid and sc.score<60 and student.sid=sc.sid

20、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

select student.sname,course.cname,sc.score from student,sc,course

where course.cid=sc.cid and student.sid=sc.sid and sc.score>70