sql习题及答案

sql习题：http://www.cnblogs.com/wupeiqi/articles/5729934.html

习题答案参考：https://www.cnblogs.com/wupeiqi/articles/5748496.html  （有些答案有错）

-- SELECT count(*) from score WHERE num>60; -- 查找分数大于60的个数
-- select count(cid),teacher_id from course group by teacher_id;
-- select tid,teacher.tname,course.cname from course left join teacher on course.teacher_id = teacher.tid;
-- select count(sid),gender from student GROUP BY gender  -- 男女生个数

-- 2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
        -- 找出生物成绩，找出物理成绩，联合这两张临时表，找出B.num > P.num
-- select B.student_id,B.cname,B.num as b_num,P.cname,P.num as p_num from 
-- (select * from score left join course on score.course_id = course.cid where cname = '生物') as B
-- inner join
-- (select * from score left join course on score.course_id = course.cid where cname = '物理') as P
-- on B.student_id = P.student_id where B.num > P.num;;
-- 3、查询平均成绩大于60分的同学的学号和平均成绩;(进阶：以及姓名)
            -- 首先选择出平均分大于60分的同学的学号，再和学生表join，选择出姓名
-- SELECT
--     student.sid,
--     student.sname,
--     b1.avg_num 
-- FROM
--     ( SELECT avg( num ) AS avg_num, student_id FROM score GROUP BY student_id HAVING avg( num ) > 60 ) AS b1
--     LEFT JOIN student ON b1.student_id = student.sid;

-- 4、查询所有同学的学号、姓名、选课数、总成绩；（两种解法，另一种是先把表连起来再group by）
                -- 这里注意 count(1)的用法， 类似select age,1 from t1; 会出现列名1，属性全为1
            -- 首先成绩表和学生表连表，再根据学号进行分组，然后选择出学号，姓名，聚合学科数，求和num
-- SELECT
--     student.sid,
--     student.sname,
--     b2.course_num,
--     b2.sum_num 
-- FROM
--     ( SELECT student_id, count( course_id ) AS course_num, sum( num ) AS sum_num FROM score GROUP BY student_id ) AS b2
--     LEFT JOIN student ON b2.student_id = student.sid;

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

-- 5、查询姓“李”的老师的个数；
-- select count(1) from teacher where tname like '李%';

-- 6、查询没学过“李平”老师课的同学的学号、姓名；
            -- 首先课程表和老师表join，选择出李平老师的课程id，然后在成绩表中把选择了 这些课程id的学号选出，用学号分组后，用not in 从学生表中，选择出没有选择过课程的学号和姓名
-- select sid,sname from student where sid not in (select student_id from score where course_id in (select course.cid from course left join teacher on course.teacher_id=teacher.tid where tname = '李平老师') group by student_id    );
-- 

-- 7、查询学过“1”课程并且也学过编号“2”课程的同学的学号、姓名；
--                 先查到既选择001又选择002课程的所有同学
--         根据学生进行分组，如果学生数量等于2表示，两门均已选择
-- select student.sid,student.sname from
-- (select student_id from score where course_id in (1,2) group by student_id having count(*)>1) as b4
-- left join student on b4.student_id = student.sid;

-- 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
-- SELECT sname,sid from student where sid in (select student_id from score where course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='李平老师') group by student_id having count(*) =  (select count(cid) from course left join teacher on course.teacher_id = teacher.tid where tname='李平老师'));

-- 10、查询有课程成绩小于60分的同学的学号、姓名；
                    # distinct 如果有重复项，只选择一个
-- select sid,sname from student where sid in (
-- select distinct student_id from score where num<60);

-- 11、查询没有学全所有课的同学的学号、姓名；
-- select sid,sname from student where sid not in (select student_id from score group by student_id having count(*)=(select count(1) from course));

-- 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
-- select distinct student_id,student.sname from score left join student on score.student_id = student.sid where student_id != 1 and course_id in (select course_id from score where student_id = 1);

--     select student_id,sname, count(course_id)
--         from score left join student on score.student_id = student.sid
--         where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id

-- 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
-- select student_id from score where student_id !=1 and course_id in (select course_id from score where student_id = 1) group by student_id having count(*) >= (select count(course_id) from score where student_id = 1);

-- 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
         #1.课程包含 002，且课程数一样
-- select student_id,sname from score left join student on score.student_id = student.sid where student_id in (
--             select student_id from score  where student_id != 2 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 2)
--         ) and course_id = (select count(1) from score where student_id = 2)


-- 16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
--     思路：
--         由于insert 支持 
--                 inset into tb1(xx,xx) select x1,x2 from tb2;
--         所有，获取所有没上过002课的所有人，获取002的平均成绩
--  
--     insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)
--     from student where sid not in (
--         select student_id from score where course_id = 2)

-- 17、按平均成绩从低到高显示所有学生的“生物”、“物理”、“体育”三门的课程成绩，按如下形式显示： 学生ID,生物,物理,体育,有效课程数,有效平均分；
-- SELECT
-- student_id,
-- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
-- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
-- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
-- from score as s1;

-- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
-- select course_id,max(num),min(num),cname,case when min(num) < 10 THEN 0 else min(num) end from score left join course on score.course_id = course.cid group by course_id;

-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
-- select course_id,avg(num),sum(case when num<60 then 0 else 1 end),sum(1),sum(case when num<60 then 0 else 1 end)/sum(1) as b from score group by course_id order by avg(num) asc,b desc;

-- 20、课程平均分从高到低显示（显示任课老师）；
-- select course_id,cname,tname,avg(if(isnull(score.num),0,score.num)) from score 
-- left join course on score.course_id = course.cid 
-- left join teacher on course.teacher_id = teacher.tid
-- group by course_id order by avg(num) desc;

-- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
-- select * from
-- (
-- select 
-- student_id,
-- course_id,
-- num,
-- 1,
-- (select num from score as s2 where s2.course_id = s1.course_id group by s2.num order by s2.num desc limit 0,1),
-- (select num from score as s2 where s2.course_id = s1.course_id group by s2.num order by s2.num desc limit 3,1) as cc
-- 
-- from score as s1 
-- ) as B
-- where B.num > B.cc order by B.course_id desc;

-- 26、查询同名同姓学生名单，并统计同名人数；
-- select sname,count(1) from student group by sname

-- 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
-- select avg(if(isnull(score.num),0,score.num)),course_id from score group by course_id order by avg(num) asc,course_id desc;

-- 29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
-- select student.sname,score.num from score 
-- left join course on score.course_id = course.cid
-- left join student on score.student_id = student.sid
-- where course.cname = '生物' and score.num < 60;

-- 32、查询选修“李平老师”所授课程的学生中，成绩最高的学生姓名及其成绩；
-- select student_id,sname,max(num) from score left join student on score.student_id = student.sid where course_id in (
-- select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师') group by student_id order by max(num) desc limit 0,1;

-- 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
-- 此处要了解笛卡儿积
--     select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;
-- 
-- 38、查询没学过“李平老师”老师讲授的任一门课程的学生姓名；
    #找出选过李平老师的，然后在学生表 not in 
-- select sid,sname from student where sid not in (select student_id from score where course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname='李平老师') group by student_id)

##还可以选择查询语句，不过这个语句结果需要为一个常量 select age,name,(select count(1) from tb) from tb1; 如果不是常量，需要设定条件，否则会变为笛卡儿积了。如下所示
# -- 17、按平均成绩从低到高显示所有学生的“生物”、“物理”、“体育”三门的课程成绩，按如下形式显示： 学生ID,生物,物理,体育,有效课程数,有效平均分；
# -- SELECT
# -- student_id,
# -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
# -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
# -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
# -- from score as s1;

'''
select id,(select * from tb1 where tb1.id = 1) from tb2;
此时 子查询类似一个常量， 每一行就是 id1 常量， id2 常量这样子
'''
'''
#?????? -- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
# select * from
# (
# select
# student_id,
# course_id,
# num,
# 1,
# (select num from score as s2 where s2.course_id = s1.course_id group by s2.num order by s2.num desc limit 0,1),
# (select num from score as s2 where s2.course_id = s1.course_id group by s2.num order by s2.num desc limit 3,1) as cc
#
# from score as s1
# ) as B
# where B.num > B.cc order by B.course_id desc;
'''
#重点 s2.student_id=s1.student_id
# SELECT
# student_id,
# (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
# (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
# (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
# from score as s1;


# case then 条件 else 字段 END
#select course_id,max(num),min(num),cname,
# case when min(num) < 10 THEN 0 else min(num) end from score left join course on score.course_id = course.cid group by course_id;


#-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
# select course_id,avg(num),sum(case when num<60 then 0 else 1 end),sum(1),sum(case when num<60 then 0 else 1 end)/sum(1) from score group by course_id;


#-- 20、课程平均分从高到低显示（显示任课老师）；
# select course_id,cname,tname,avg(if(isnull(score.num),0,score.num)) from score
# left join course on score.course_id = course.cid
# left join teacher on course.teacher_id = teacher.tid
# group by course_id order by avg(num) desc;

#if(isnull(score.num),0,score.num) 如果 score.num是空，那么就是0，否则是它本身

# -- 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
# -- 此处要了解笛卡儿积，连接两张表不加 on 条件，会进行笛卡儿积，就是一张表的首行遍历连接另一张表所有行，然后第二行继续遍历连接.....
# --     select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;