mysql_数据查询练习 course: teachar: student sc: 1、查询“c001”课程比“c002”课程成绩高的所有学生的学号; 2、查询平均成绩大于60 分的同学的学号和平均成绩; 3、查询所有同学的学号、姓名、选课数、总成绩; 4、查询姓“刘”的老师的个数; 5、查询没学过“李老师”课的同学的学号、姓名; 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名; 7、查询学过“李老师”所教的所有课的同学的学号、姓名; 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名; 9、查询所有课程成绩小于60 分的同学的学号、姓名; 10、查询没有学全所有课的同学的学号、姓名; 11、查询至少有一门课与学号为"1001"的同学所学相同的同学的学号和姓名; 12、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 13、查询出只选修了一门课程的全部学生的学号和姓名 1
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