• 45题的难点


    1.以Cno升序、Degree降序查询Score表的所有记录。
    select *from score order by cno asc,degree desc

    2.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
    select avg (degree) as '平均分'from score where cno in(
    select cno from score group by cno having COUNT(*)>5 and cno like '3%')

    3.表连接,在没有共同列时可以进行列与列的比较

    ps:select sno,cno,degree,[RANK] from score,grade where degree>low and degree <upp

    4.查询score中选学多门课程的同学中分数为非最高分成绩的记录
    select *from score where degree <(select MAX(degree)from score where sno in(select sno from score group by sno having COUNT(*)>1))
    and sno in (select sno from score group by sno having COUNT(*)>1)

    5.查询选修某课程的同学人数多于5人的教师姓名。

    select tname from teacher where tno in(select tno from course where cno in (select cno from score group by cno having COUNT(*)>5))

    6.查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

    select cno,sno,degree from score where cno='3-105'and degree >any(select degree from score where cno='3-245')order by degree asc

    7.查询成绩比该课程平均成绩低的同学的成绩表。
    select * from score as a where degree<all(select avg(degree)from score as b where a.cno=b.cno) 

    8.查询至少有2名男生的班号。
    select class from student where ssex ='男' group by class having COUNT(*)>=2

    9.以班号和年龄从大到小的顺序查询Student表中的全部记录。
    select *, YEAR(GETDATE())-YEAR(sbirthday)as '年龄' from student order by class , YEAR(GETDATE())-YEAR(sbirthday) desc

    注意:having 就是group by 的where条件,对group by生成的结果进行筛选,只能跟聚合函数        

            sql里面as可以简单的理解为重命名,as a对表、对列起别名,起别名就是抓过前面的表过来用

  • 相关阅读:
    重拾安卓_01_安卓开发环境搭建(eclipse)
    重拾安卓_01_安卓开发环境搭建(android studio)
    【BZOJ】1038: [ZJOI2008]瞭望塔
    【BZOJ】2178: 圆的面积并
    【UR #4】元旦三侠的游戏(博弈论+记忆化)
    【BZOJ】1027: [JSOI2007]合金(凸包+floyd)
    【POJ】1151 Atlantis(线段树)
    【POJ】1228 Grandpa's Estate(凸包)
    【POJ】1556 The Doors(计算几何基础+spfa)
    【POJ】1113 Wall(凸包)
  • 原文地址:https://www.cnblogs.com/sunshuping/p/5579763.html
Copyright © 2020-2023  润新知