• 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对表、对列起别名,起别名就是抓过前面的表过来用

  • 相关阅读:
    css背景颜色渐变效果
    manachar算法小结
    hdu--3068 最长回文串(manachar模板)
    ac自动机小结
    hdu--2896 病毒侵袭(ac自动机)
    hdu--1251 统计难题(字典树水题)
    hdu--1540 Tunnel Warfare(线段树+区间合并)
    poj--3667 Hotel(线段树+区间合并)
    hdu--3308 LCIS(线段树+区间合并)
    hdu--5023 A Corrupt Mayor's Performance Art(线段树+区间更新+位运算)
  • 原文地址:https://www.cnblogs.com/sunshuping/p/5579763.html
Copyright © 2020-2023  润新知