http://www.cnblogs.com/linhaifeng/articles/7895711.html   （egon@5565487）

1 查询所有的课程的名称以及对应的任课老师的姓名

use db01;
select course.cname,teacher.tname from course
inner join  一旦两张表建立了链接，第一个select 就可以用第二张表的字段了
teacher
on
course.cid=teacher.tid  两张表建立链接的条件

 2 查询学生表中男女各有多少人

use db01;
select gender,count(1) count_gender from student group by gender;

 3 查询物理成绩等于100的学生的姓名

select sname from student
where 
student.sid 
in 
(
select student_id from score
inner join
course
on
score.course_id=course.cid
where
course.cname="物理" and score.num=100
)

 4 查询所有学生的学号，姓名，选课数，总成绩（注意，对于那些没有选修任何课程的学生也算在内）

select sid,sname,t1.course_num,t1.total_num from student
left join
(select student_id,count(course_id) course_num,sum(num) total_num from score
group by student_id) as t1
on
student.sid=t1.student_id

 5 查询平均成绩大于80分的同学的姓名和平均成绩

select sname,t1.avg_num from student
inner join
(
select student_id,avg(num) avg_num from score group by student_id
having avg(num)>80
) as t1
on 
student.sid=t1.student_id

 6 查询姓李的老师的个数

select count(tid) from teacher 
where
 tname like "李%"

7 查询没有报李平老师课的学生姓名（找出报名李平老师课程的学生，然后取反就可以了） 

select sname from student
where 
student.sid not in
(
select distinct student_id from score
where
score.course_id in
(
select cid from course
inner join
teacher
on 
course.teacher_id=teacher.tid
where
teacher.tname="李平老师"
)
)

 8 查询物理课程比生物课程高的学生的学号（分别得到物理成绩表与生物成绩表，然后连表即可）

select t1.student_id from
(
select student_id,num from score
inner join
 course
 on
 score.course_id=course.cid
 where course.cname="物理"
 ) as t1
 inner join
 (
select student_id,num from score
inner join
 course
 on
 score.course_id=course.cid
 where course.cname="生物"
 ) as t2
 on
 t1.student_id=t2.student_id
 where
 t1.num>t2.num

 9 查询没有同时选修物理课程和体育课程的学生姓名

（没有同时选修指的是选修了一门的，思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count（课程）=1）

select sname from student
where student.sid in
(
select student_id from score
where
score.course_id in
(
select student_id from course
where
cname="物理" or
cname="体育"
)
group by student_id
having
count(course_id)=1
)

 10 查询挂科超过两门，包括两门的学生姓名和班级

（思路：求出<60表，然后对学生进行分组，统计课程数目>=2）

select sname,class.caption from student
inner join
(
select student_id from score
where num<60
group by student_id
having
count(course_id)>=2
) as t1
inner join
class
on
student.sid=t1.student_id
and 
student.class_id=class.cid

 11 查出选修了所有课程的学生姓名

（思路：先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可）

select sname from student
where
student.sid in
( 
select student_id from score
group by student_id
having
count(course_id)=(select  count(cid) from course)
)

 12 查询李平老师 的课程的所有成绩记录

select * from score
where course_id in
(select cid from course
inner join
teacher
on course.teacher_id=teacher.tid
where teacher.tname="李平老师")

 13 查询全部学生都选修了的课程号和课程名

（思路：取所有学生数，然后基于score表的课程分组，找出count(studnet_id)等于学生数即可）

SELECT
    cid,
    cname
FROM
    course
WHERE
    cid IN (
        SELECT
            course_id
        FROM
            score
        GROUP BY
            course_id
        HAVING
            COUNT(student_id) = (
                SELECT
                    COUNT(sid)
                FROM
                    student
            )
    );

 14 查询只选修了一门课程的学生姓名和学号

select sid,sname from student
where student.sid
in
(
select student_id from  score
group by student_id
having
count(course_id)=1
)

 15 查询每门课程被选修的次数

select count(student_id),course_id from score
group by course_id

 16 查询所有学生考出的成绩并按照从高到低排序（成绩去重）

select distinct num from score
order by num desc

 17 查询平均成绩大于85的学生姓名和平均成绩

select sname,avg_num from student
inner join
(
select student_id,avg(num) avg_num from score
group by student_id
having avg(num)>85
) as t1
on student.sid=t1.student_id