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