练习题博客地址:http://www.cnblogs.com/wupeiqi/articles/5729934.html
挑选10-15个小题进行练习:
1.查询名字中有“e”的老师的个数;
select count(*) from teacher where tname like '%e%';
2.查询男生、女生的人数;
select gender,count(*) from student group by gender;
3.查询姓“山”的学生名单;
select * from student where sname like '山%';
4.查询有课程成绩小于60分的同学的学号、姓名;
select student.sid,sname from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.tid where number<60;
5.课程平均分从高到低显示(显示任课老师);
select course_id,avg(number),tname from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.tid group by course_id order by avg(number) desc;
6.查询没学过“alex”老师课的同学的学号、姓名;
select student.sid,sname from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.tid where tname!='alex' group by student.sid;
7.查询学过“egon”老师所教的所有课的同学的学号、姓名;
select student.sid,sname from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.tid where tname='egon' group by sname,student.sid;
8.查询学过“1”并且也学过“2”课程的同学的学号、姓名;
select student.sid,sname from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.tid where course_id in (1,2) group by student.sid having count(course_id)>1;
9.查询和2号的同学学习的课程完全相同的其他同学学号和姓名;
select student.sid,sname from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.tid where course_id in (select course_id from score where student_id=2) and student.sid!=2;
10.查询出只选修了一门课程的全部学生的学号和姓名;
select student.sid,sname from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.tid group by student.sid having count(course_id)=1;
11.查询各个课程及相应的选修人数;
select cname,COUNT(*) from score left join student on score.student_id = student.sid left join course on score.course_id = course.cid left join class on student.class_id = class.cid left join teacher on course.teacher_id=teacher.tid GROUP BY cname;
12.求选了课程的学生人数;
select COUNT(*) from (select COUNT(*) from score GROUP BY student_id) as new_table;
13.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
SELECT course_id,MAX(number),MIN(number)FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN class ON student.class_id = class.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid GROUP BY course_id;