s4 mysql基础 练习题

MySQL一（练习题）

____oldboy fullstack4

参考资料：
    mysql表操作练习题：
        http://www.cnblogs.com/wupeiqi/articles/5729934.html
    练习题表：
        http://images2015.cnblogs.com/blog/425762/201608/425762-20160803224643778-2071849037.png
    MySQL练习题参考答案    
        https://www.cnblogs.com/wupeiqi/articles/5748496.html


- 练习题
    7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
    -- select score.student_id,student.sname from score
    --
    -- left join student on score.student_id=student.sid
    --
    -- where course_id =1 or course_id =2 GROUP BY student_id HAVING count(course_id) > 1


    8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
    -- select student_id from score where course_id in (
    --     select cid from course left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师"
    -- ) GROUP BY student_id having count(course_id) =  (select count(cid) from course
        left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师")
    --
    --
    10、查询有课程成绩小于60分的同学的学号、姓名；
    -- select student_id from score where num < 60 GROUP BY student_id
    -- select DISTINCT student_id from score where num < 60

    -- 查询没有学全所有课的同学的学号、姓名；

    11、查询没有学全所有课的同学的学号、姓名；
    -- select student_id,count(1) from score GROUP BY student_id  HAVING count(1) < (select count(cid) from course);
    --

    -- 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
    -- select course_id from score where student_id = 1;
    -- 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

    -- 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
    -- select course_id from score where student_id = 1;
    -- select student_id,count(1) 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(1) = (select count(course_id) from score where student_id = 1)


    -- 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

    -- 获取和方少伟选课个数相同的通许
    -- select count(1) from score where student_id = 1;
    --

    -- select student_id from score where  student_id in (
    --     select student_id from score where student_id !=1 GROUP BY student_id HAVING count(1) = (select count(1) 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(1) = (select count(1) from score where student_id = 1)
    --
    --
    -- insert into tb(student_id,course_id,num)
    --
    -- select student_id,2,(SELECT AVG(num) from score where course_id = 2) from score where course_id != 2

    -- 17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
    -- 1  90  80   99
    -- 2  90  80   99
    -- 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),min(num)+1,case when min(num) <10 THEN 0 ELSE min(num) END as c  from score 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 jgl
    from score GROUP BY course_id order by AVG(num) asc,jgl desc;