Day.2 练习题

练习题博客地址：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;