oracle：sql查询

绝地灬酷狼 2022-02-19 17:21 287阅读 0赞

最近遇到一些查询，自己还不是能保证一口气就写对，经过和好友一起研究，有一两个比较好的思路，尤其是最后两个查询，值得深思。为纪念这一丁丁进步和思维的一小步跨越，现记录如下：

--1.查询所有同学的学号、姓名、选课数、总成绩
    select sc.sno,sname,count(sc.cno),sum(sc.grade) from student,sc
    where student.sno=sc.sno
    group by sc.sno,sname
    order by sc.sno;
    --2.查询姓“赵”的老师的个数
    select count(sno) from student where sname like '赵%';
    --3.查询选课门数大于2的学生的学号和姓名
    select sc.sno,sname,count(sc.sno) from student,sc
    where student.sno = sc.sno
    group by sc.sno,sname
    having count(sc.sno)>2
    order by sc.sno;
    --4.查询每个学生的选课最高分和最低分,列出学号和分数
    select sno, max(grade),min(grade) from sc group by sno;
    --5.查询选课门数最多的2个学生的学号和姓名(有问题)
    select sno,num from (select sc.sno,count(sc.cno) num from sc 
    group by sc.sno
    order by num desc) t
    where rownum<=2
    --6.一句SQL查出所有课程成绩最高和最低的学生及其分数
    select * from (select sc.cno,sc.sno,sc.grade from (select cno,max(grade) gr from sc group by cno) t,sc
    where sc.cno = t.cno and t.gr = sc.grade
    order by cno)
    union all
    select * from (select sc.cno,sc.sno,sc.grade from (select cno,min(grade) gr from sc group by cno) t,sc
    where sc.cno = t.cno and t.gr = sc.grade
    order by cno)
    order by 1 asc,3 desc

实验数据：student表、course表、sc表依次如下图所示：

![20190416215241291.png][]![20190416215311515.png][]![20190416215338357.png][]

[20190416215241291.png]: https://img-blog.csdnimg.cn/20190416215241291.png
[20190416215311515.png]: https://img-blog.csdnimg.cn/20190416215311515.png
[20190416215338357.png]: /images/20220219/1ad645b650754a80a229e8867329085f.png