美文网首页
SQL练习50题

SQL练习50题

作者: weiwei_js | 来源:发表于2020-08-27 13:46 被阅读0次

    50道SQL练习题及答案与详细分析

    1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

    因为需要全部的学生信息,则需要在sc表中得到符合条件的SId后与student表进行join,可以left join 也可以 right join

    select * from Student RIGHT JOIN (

        select t1.SId, class1, class2 from

              (select SId, score as class1 from sc where sc.CId = '01')as t1,

              (select SId, score as class2 from sc where sc.CId = '02')as t2

        where t1.SId = t2.SId AND t1.class1 > t2.class2

    )r

    on Student.SId = r.SId;

    总结:

    join 的使用:left join 以左边为主。right join 以右边为主。join 取交集(效率低1倍)。

    on 的使用:on () 的效率高10倍。

    on student.sid=r.sid; 执行时间:0.01 sec

    on (student.sid=r.sid); 执行时间:0.00 sec

    被join的一定是一个表单,而语句中的表单是要select 生成。select xxx 生成 r。

    选择正确的查询参数:cid。

    1.1 查询同时存在" 01 "课程和" 02 "课程的情况

    select * from

        (select * from sc where sc.CId = '01') as t1,

        (select * from sc where sc.CId = '02') as t2

    where t1.SId = t2.SId;

    1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

    这一道就是明显需要使用join的情况了,02可能不存在,即为left join的右侧或right join 的左侧即可.

    select * from

    (select * from sc where sc.CId = '01') as t1

    left join

    (select * from sc where sc.CId = '02') as t2

    on t1.SId = t2.SId;

    1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

    select * from sc

    where sc.SId not in (

        select SId from sc

        where sc.CId = '01'

    )

    AND sc.CId= '02';

    总结

    not in 的使用:相关的sid放到一个表了,再not in;

    查询结果作为新的查询条件。

    2,查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

    这里只用根据学生ID把成绩分组,对分组中的score求平均值,最后在选取结果中AVG大于60的即可. 注意,这里必须要给计算得到的AVG结果一个alias.(AS ss)

    得到学生信息的时候既可以用join也可以用一般的联合搜索

    select student.SId,sname,ss from student,(

        select SId, AVG(score) as ss from sc

        GROUP BY SId

        HAVING AVG(score)> 60

        )r

    where student.sid = r.sid;

    select Student.SId, Student.Sname, r.ss from Student right join(

          select SId, AVG(score) AS ss from sc

          GROUP BY SId

          HAVING AVG(score)> 60

    )r on Student.SId = r.SId;

    select s.SId,ss,Sname from(

    select SId, AVG(score) as ss from sc

    GROUP BY SId

    HAVING AVG(score)> 60

    )r left join

    (select Student.SId, Student.Sname from

    Student)s on s.SId = r.SId;

    总结

    group by  、 having 的使用:having放后面。

    为查询结果,取名:括号后面直接跟随 r。

    from的理解:from 表1,表2 ,表3 where。

    两个解决方案:

    方案1:from 表1,表2 where  xxx

    方案2:from 表1 left join 表2 on xxx

    3,查询在 SC 表存在成绩的学生信息

    select DISTINCT student.*

    from student,sc

    where student.SId=sc.SId

    select *from student right join

    ( select sid from sc group by sid)r

    on student.sid = r.sid;

    总结

    去重方式:DISTINCT ,group by xxx

    DISTINCT:distinct必须放在要查询字段的开头。

    select distinct sid from student;//返回不重复的学生 sid,默认排序。

    select distinct name,sid from student;//会过滤掉name和id两个字段都重复的记录

    group by xxx:

    select *from sc group by sid ;//返回不重复的学生 sid。

    比较:

    group 是按组查询的,是一种聚合查询,很多时候是为了做统计用,

    例如:select sum(score), sid from sc group by sid; //查询总分并按照sid排序。

    实际中我们往往用distinct来返回不重复字段的条数:count(distinct sid)

    select count(DISTINCT sid)  from sc ;//返回不重复的sid的条数。

    如果要查询不重复的记录,有时候可以用group by 。

    distinct 是查询出来以后再把重复的去掉性能上 group 比 distinct 要好很多

    衣带渐宽终不悔 为伊人消得人憔悴。

    相关文章

      网友评论

          本文标题:SQL练习50题

          本文链接:https://www.haomeiwen.com/subject/kjscsktx.html