Day 8~9
一、查询语句子句
1、查询语句子句的优先级
- select --5
- from --1
- where --2
- group by --3
- having --4
- order by --6
1、select
- select后可以跟一个/多个属性
- 属性间隔符是英文逗号","
- 属性后面可以加别名，中间空格隔开
- 用distinct可以使属性去重
2、from
- 生成实例
- 生成笛卡尔乘积
- for循环
3、where
- 可以比大小：分别是大于、大于等于、小于、小于等于、等于、不等于（符号表示为> | >= | < | <= | = | != | <> ，最后两张都是不等于）
- 数值可以比大小，字符串也可以比大小
- 条件可以是多个，条件之间的关系有三种：与或非（and or not）
- 条件之间也有优先级： () > not > and > or ，括号优先级最高
- xx between ’ y’ and ’ z’ 等价于 xx>‘y’ and xx<‘z’ 一般用在连续的数值关系上
- 如果数值之间间断的，可以用 xx in (x,y,z) 表示在x和y和z中
- 模糊查询 like，%表示多个任意字母，_表示任意一个字母
- 对不存在的数据进行对比，xx is null，表示查询 xx 属性不存在的数据，is not null （not is null 也行）表示查询存在的
4、group by
- group by 首先根据分组特征创建出多个分组，然后通过for循环遍历每个小组，将其交给后续执行的子句
- 分组语句只能查询分组特征，分组的目的在于统计，所有的统计（聚合/聚集）函数一共有5个，分别是最大、最小、平均、总和以及组内成员数目（max、min、avg、sum、count）
- 支持多属性分组
- 当统计数目时，若为空则不纳入统计
- 1、找出各个班级中的人数总和
select sclass,count(*)
from student
group by sclass
- 2、请用查询语句找出各个班级的同学分别来自多少个不同的地区
select sclass 班级,count(distinct snativeplace) 不同的地区
from student
group by sclass
- 3、找出各个班级的男生分别有多少个不同的名族
select sclass,count(distinct snative)
from student
where ssex='男'
group by sclass
- 没有group by语句实际上是默认分组，默认分组不可以查询任何显示属性
- 先分组，再统计，参考查询语句子句优先级，所以where语句中不能出现统计函数，能够出现统计函数的子句有select、having、order by。
5、having
- having和where一样是条件过滤语句
where语句过滤的是每次from循环交给where的一条记录
having语句过滤的是每次group by循环交给having的一个小组 - 1、请查询出每个学生（sid）和其均分、最低分和最高分
select sid,avg(cmark),min(cmark),max(cmark)
from mark
group by sid
- 2、找出没有不及格成绩的学生（sid）=哪些学生最低分大于等于60
select sid
from mark
group by sid
having min(cmark)>=60
- 3、哪些地区的男生人数大于2
select snativeplace,count(*)
from student
where ssex='男'
group by snativeplace
having count(sid)>2
select snativeplace,count(*)
from student
group by snativeplace,ssex
having ssex='男' and count(sid)>2
6、order by
- 排序特征
- 默认由小到大（升序）排序
- 逆序（降序）排序 desc
- 顺序（升序）排序 asc 可省略
- 根据均分降序排序显示每个学生的sid和平均分
select sid,avg(mark)
from mark
group by sid
order by avg(mark) desc
/*起别名*/
select sid,avg(mark) amk
from mark
group by sid
order by amk desc
- 可以根据多个属性排序
- eg.现根据年龄排序，再根据学号排序，只有一阶排序分不出大小的时候才会用到二阶排序
select *
from student
order by sage desc,sid asc
二、单表查询习题
1、找出张三和李四所在班级
select distinct sclass
from student
where sname in('张三','李四')
2、找出各个地区的男生分别来自多少个不同的民族
select snativeplace,count(distinct snative)
from student
where ssex='男'
group by snativeplace
3、哪些地区男生的平均年龄>20岁
select snativeplace,avg(sage)
from student where ssex='男'
group by snativeplace
having avg(sage)>20
4、由高到低显示各个班级的男生人数,显示班级名称和男生人数
select sclass,count(*) cnt
from student where ssex='男'
group by sclass
order by cnt desc
三、多表查询
1、多表组合
1.1 多表合并
- 多表合并的基本思想是将两张或者两张以上的表格合并为一张表格，然后进行单表查询
- 笛卡尔乘积带来的大部分结果都是没有意义的
- 1、找出张三的2001号课程成绩
select *
from student,mark
where student.sid = mark.sid and sname='张三' and cid=2001
- 2、找出张三数学成绩
select cmark
from student,mark,course
where student.sid=mark.sid and mark.cid=course.cid
and sname='张三' and cname='数学'
- 3、找出各个地区的数学均分
将三表组合
先用where过滤出cname=‘数学’
根据snativeplace进行分组
统计出各个地区的avg(cmark)
select snativeplace,avg(cmark)
from student s,mark m,course c
where cname='数学' and s.sid=m.sid and m.cid=c.cid
group by snativeplace
- 4、找出各个地区的男生各科均分
select snativeplace,avg(cmark)
from student s,mark m,course c
where s.sid=m.sid and m.cid=c.cid
group by snativeplace,c.id,ssex
having ssex='男'
- 5、找出每个学生的平均分,要求显示姓名和均分
将student表和mark组合成单表
将这张单表根据sname进行分组
投影sname和avg(cmark)
select sname,avg(cmark)
from student s,mark m
where s.sid=m.sid
group by s.sid,snam
1.2 多表连接
- 特殊的多表组合场景
查询的特征决定了不同的行中的数据是无法比对的！
比对有三种方式:
1:将一个常量和一个常量进行相比
where 1=0
2:将一个变量和一个常量进行相比
where sage=19
3:将一个变量和一个变量进行比对
必须让这两个变量处于同一行才有比对的可能
- 找出哪些学生的2001号课程成绩比2002号课程成绩高?只要显示这些学生的sid即可
select a.sid
from mark a,mark b
where a.sid=b.sid
and a.cid=2001 and b.cid=2002
and a.cmark>b.cmark
- 10002号学生的哪些课程成绩比10003高?找出课程名称
select cname
from mark a,mark b,course c
where a.cid=b.cid and a.sid=10002 and b.sid=10003
and a.cid=c.cid and a.cmark>b.cmark
- 张三哪些课程成绩李四高?找出课程名称
select cname
from mark a,mark b,student s1,student s2,course c
where a.cid=b.cid and a.sid=s1.sid and b.sid=s2.sid
and s1.sname='张三' and s2.sname='李四'
and a.cid=c.cid and a.cmark>b.cmark
2、子查询
- 子查询又叫代入查询
- 根据代入的语句分为：条件子查询 表格子查询 select子查询 相关子查询
找出张三的平均分[条件子查询]
select sid from student where sname='张三'
select avg(cmark) from mark where sid=1002
/*分多次查询的过程中消耗了无谓的延时和cpu*/
select avg(cmark) from mark
where sid=(select sid from student where sname='张三'
)
找出年龄最大的学生姓名
--方法一[条件子查询]
step1:找出最大年龄
select max(sage) from student
step2:找最大年龄对应的学号
select * from student where sage=(step1)
--方法二：将学生根据年龄降序排序，排序后取第一条记录
select * from(
select * from student order by sage desc
) where rownum=1
--方法三：我的年龄大于等于学生表中所有人（包括自己），那么我就一定是年龄最大
select * from student where sage>=all(
select sage from student where sage is not null
)
-- 方法四：年龄不是最大的是合法集，而年龄最大的是非法结果，取反得到
select * from student where not sage<any(
select sage from student where is not null
)
--方法五：如果我的年龄是最大，那么一定不可能有人年龄比我大
select * from student s where not exists (
select * from student where sage>s.sage
)
找出年龄最大的女生姓名
step1:找出最大年龄的女生
select max(sage) from student where sage='女'
step2:找姓名
select sname from student where sage=(step1)
找出张三的最高分对应的课程名[条件子查询]
--step1:找出张三的sid
select sid from student where sname='张三'
--step2:找最高分
select max(cmark) from mark where sid=(step1)
--step3:找最高分对应的cid
select cid from mark where sid=(step1) and cmark=(step2)
--step4:找cid对应的cname
select cname from course where cid in(step3)
找出最高均分[表格子查询 from语句]
step1:select sid,avg(cmark) amk from mark group by sid
step2:select max(amk) from (step1)
找出均分最高的男生[表格子查询]
--方法一：条件子查询+表格子查询
step1:在student表中找出所有男生的sid
select sid from student where ssex='男'
step2:在mark表中根据男生的sid，找出平均分表
select sid,avg(cmark) amk from mark where sid in(step1) group by sid
step3:在step2的表中找出最高平均分
select max(amk) from (step2)
step4:在step2的表中找均分等于step3的sid
select sid from (step2) where amk=(step3)
step5:在student表中将sid换成sname
select sname from student where sid in(step4)
--方法二：我的均分大于等于表中所有人的均分
--思路上和方法一的前两步相同
step1:在student表中找出所有男生的sid
select sid from student where ssex='男'
step2:在mark表中根据男生的sid，找出平均分表
select sid,trunc(avg(mark),2) amk from mark where sid in(step1) group by sid
step3:根据男生均分表，用all实现最大均分的查找
select sid from (step2) where amk>=all(
select amk from (step2)
)
step4:将学号代入student表，找出姓名
select sname from student where sid in(step3)
3、多表集合运算
找出各科成绩均比张三高的学生姓名
这里各科是指和张三重合的课程
未知数和未知数的对比
逆向思维
step1:找出张三的sid
select sid from student where sname='张三'
step2:找出张三所选的所有课程的id
select cid from mark where sid=(step1)
step3:将所有其他人和张三同一门课进行组合，笛卡尔，从中选出至少一门课成绩低于张三的学生sid
select a.sid from mark a,mark b
where a.cid=b.cid and b.sid=(step1) and a.cmark<=b.cmark
step4:将step3找到的学生在成绩表中排除，并且这些学生和张三选课记录至少有一门课是重合的
select sid from mark where sid not in(step3) and cid in(step2)
step5:将学号换成姓名
select sname from student where sid in(step4)
找出没有选数学课的学生姓名
step1:找出数学cid
select cid from course where cname='数学'
step2:找出选了数学课的学生id
select sid from mark where cid=(step1)
step3:在student表中将step2排除
select sname from student where sid not in(step2)
哪些地区的学生数学均分高于英语均分
step1:
select snativeplace sn,cid,avg(cmark) amk
from student s,mark m
where s.sid=m.sid
group by snativeplace,cid
step2:
select a.sn from step1 a,step1 b
where a.sn=b.sn and a.cid=(select cid from course where cname='数学')
and b.cid=(select cid from course where cname='英语') and a.amk>b.amk
哪些地区的男生数学和英语成绩均高于80
select snativeplace from student s,mark m
where s.sid=m.sid and ssex='男' and m.cid in(
select cid from course where cname in('数学','英语'))
group by snativeplace
having min(cmakr)>80
两种方法找出各科成绩均在80分以上的学生姓名
-- 方法一：至少一门成绩不在80分以上
step1:有成绩在80分以下的学生sid
select sid from mark where cmark<80
step2:在student表中排除step1中的学生后剩下的学生姓名
select sname from student where sid not in(step1)
-- 方法二：所有成绩都在80分以上
step1:
select sid from mark group by sid having min(cmark)>80
step2:
select sname from student where sid in(step1)
找出张三的数学成绩和英语成绩，要求显示姓名，数学成绩，英语成绩，共三列【相关子查询】
select sname,(
select cmark from mark where sid=(select sid from student where sname='张三')
and cid=(select cid from course where cname='数学')
) 数学成绩,(
select cmark from mark where sid=(select sid from student where sname='张三')
and cid=(select cid from course where cname='英语')
) 英语成绩
from student s
where sname='张三'
找出所有男生的数学成绩和英语成绩，要求显示姓名，数学成绩，英语成绩，共三列【相关子查询】
select sname,(
select cmark from mark where sid=s.sid
and cid=(select cid from course where cname='数学')
) 数学成绩,(
select cmark from mark where sid=s.sid
and cid=(select cid from course where cname='英语')
) 英语成绩
from student s
where sname='张三'
找出各个地区的数学均分和英语均分
select snativepalce,(
select avg(cmark) from mark where sid in(
select sid from student where snativeplace=s.snativeplace and cid=(
select cid from course where cname='数学'
)
)
) 数学均分,(
select avg(cmark) from mark where sid in(
select sid from student where snativeplace=s.snativeplace and cid=(
select cid from course where cname='英语'
)
)
) 英语均分
from student s
group by snativeplace
找出选修了所有课程的学生
select * from student s where not exists(
select * from course c where not exists(
select * from student where sid=s.sid and cid=c.cid
)
)
整理成绩表
显示的过程中有3个条件：
1：学生将自己的所有选课记录集中显示
2：不要求学号排序
3：同一个学生的所有选课记录按成绩降序排序显示
--row_number() 相同的不并列，随机排序
select m.*,row_number() over (partition by sid order by cmark desc)
from mark m
--rank() 相同的并列，但后面的排序不连续
select m.*,rank() over (partition by sid order by cmark desc)
from mark m
--dense_rank() 相同的并列，后面的排序也连续
select m.*,dense_rank() over (partition by sid order by cmark desc)
from mark m
--sum() 同组的聚合
select m.*,sum() over (partition by sid order by cmark desc)
from mark m
找出所有学生的最高分选课记录
select m.*,row_number() over (partition by sid order by cmark desc) rid
from mark m
where rid=1
统计学生均分
select sname||case
when amk is null then '没有选课'
when amk>=85 then '的均分为'||amk||'分，成绩优秀'
when amk>=85 then '的均分为'||amk||'分，成绩普通'
else ''|amk|
end
from student s left join(
select sid,trunc(avg(cmark),2) amk from mark group by sid
) m on s.sid=m.sid
统计学生选课数目
select sname,case
when c is null then 0
else c
end
from student s left join (
select sid,count(*) c from mark group by sid
) m on s.sid=m.sid
四、课后习题
Day 8
1、找出年龄降序排序的5-8名学生信息
降序
取前8条记录,逆转这8条记录的顺序
取前4条记录,再逆转顺序
select * from (
select * from (
select * from student order by sage desc
) where rownum<=8 order by sage asc
) where rownum<=4 order by sage desc;
2、找出均分由高到低排序中第10-15名学生的姓名和其均分
找出均分降序排列的学生sid和avg(cmark)
取前15条记录，逆转这15条记录的顺序
取前5条记录，再逆转顺序
select sname,amk
from (
select sname,amk
from (
select sid,avg(cmark) amk from mark group by sid order by amk desc
) s1,student where s1.sid=student.sid and rownum<=15 order by amk asc
) s2
where rownum<=5
order by amk desc
Day 9
1-10
- 1、与“张三”同乡的男生姓名
select sname from student where snativeplace=(
select snativeplace from student where sname='张三');
- 2、选修了赵露老师所讲课程的学生人数
--方法一
select count(*)
from student s,mark m,course c,teacher t
where s.sid=m.sid and m.cid=c.cid and c.tid =t.tid
and t.tname='赵露';
--方法二
select count(*) from student where sid in (
select sid from mark where cid in(
select cid from course where tid=(
select tid from teacher where tname='赵露'
)
)
);
- 3、查询没学过“王”姓老师课的同学的学号、姓名
select sid,sname from student where sid in(
select sid from mark where cid not in(
select cid from course where tid in(
select tid from teacher where tname like '王%'
)
)
);
- 4、“数学”课程得最高分的学生姓名、性别
select sname,ssex
from student
where sid in(
select sid from mark where cid=(
select cid from course where cname='数学'
) and cmark=(
select cmark from (
select * from mark where cid=(
select cid from course where cname='数学'
) order by cmark desc
) where rownum=1
)
);
- 5、统计每门课程的平均成绩,并按照成绩降序排序
select avg(cmark) amk
from mark
group by cid
order by amk desc;
- 6、子查询实现，查询‘3-2班’"张立"同学的"英语"成绩
select cmark
from mark
where cid=(
select cid from course where cname='英语'
) and sid in(
select sid from student where sname='张立' and sclass='3-2班'
);
- 7、查询“福建”地区学生所选修的全部课程名称
select distinct(cname)
from student,mark,course
where student.sid=mark.sid and mark.cid=course.cid and snativeplace='福建';
- 8、查询所在班级和该班内学生的年龄之和，对该班级中每个人的年龄进行比对，(要求大于20岁的人参与统计)
--查询所在班级和该班内学生的年龄之和
select sclass,sum(sage)
from student
where sage>20
group by sclass;
--对该班级中每个人的年龄进行比对->我的理解是比较同班同学的年龄
select a.sclass,a.sname,a.sage,b.sclass,b.sname,b.sage
from student a,student b
where a.sage >20 and b.sage>20 and a.sid!=b.sid and a.sclass=b.sclass
order by a.sclass,a.sid;
- 9、查询所在班级和该班内学生的年龄之和,(要求该班级中每个人的年龄都大于20岁)
select sclass,sum(sage)
from student
where sage>20
group by sclass;
- 10、用子查询实现查询选修“高等数学”课的全部学生的高等数学总成绩
select sum(cmark) from mark where cid in(
select cid from course where cname='数学'
);
11-20
- 11、用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩
select sum(cmark) from mark where sid in(
select sid from mark where cid in(
select cid from course where cname='数学'
)
)
- 12、请用两种方法实现:查找所有成绩都在68分以上的学生姓名
--方法一
select sname from student where sid not in (
select sid from mark where cmark<=68
);
--方法二
select sname from student where sid in(
select sid from mark group by sid having min(cmark)>68
);
- 13、查找至少2门成绩在80分以上的学生姓名
select sname from student where sid in(
select sid from mark where cmark>80 group by sid having count(*)>=2
);
- 14、查询至少有一门课与张三同学所学相同的同学的学号和姓名
select sid,sname from student where sid in(
select sid from mark where cid in(
select cid from mark where sid=(
select sid from student where sname='张三'
)
) and sname!='张三'
);
- 15、没有选修“数学”课的学生的姓名
select sname from student where sid not in(
select sid from mark where cid=(
select cid from course where cname='数学'
)
);
- 16、查询个人总成绩小于平均总成绩的学生姓名
select sname
from student
where sid in(
select sid
from mark
group by sid
having sum(cmark)<(
select avg(sc) from (select sid,sum(cmark) sc from mark group by sid)
)
);
- 17、用子查询实现张三在其各科成绩中最高分成绩所对应的课程号和成绩
select cid,cmark from mark where cmark=(
select max(cmark) from mark where sid=(
select sid from student where sname='张三'
) group by sid
) and sid=(
select sid from student where sname='张三'
);
- 18、找出张三的最高分和最低分以及对应的课程名
select cmark,cname from course,(
select cmark,cid from mark,(
select max(cmark) maxc,min(cmark) minc from mark where sid=(
select sid from student where sname='张三'
) group by sid
) where (maxc=cmark and sid=(select sid from student where sname='张三'))
or (minc=cmark and sid=(select sid from student where sname='张三'))
) cc
where cc.cid=course.cid
order by cmark desc;
- 19、哪些学生的各科成绩均高于张三
select sname from student where sid in(
select sid from mark where sid not in(
select a.sid from mark a,mark b where a.cid=b.cid and b.cid in(
select cid from mark where sid=(
select sid from student where sname='张三'
)
) and a.cmark<=b.cmark
) and cid in(
select cid from mark where sid=(
select sid from student where sname='张三'
)
)
);
- 20、按平均成绩从高到低显示所有学生的“数学”、“英语”、“语文”三门的课程成绩(按如下形式显示:学生ID,高等数学,计算机数学,英语,有效课程数,有效平均分 )
select sid 学生id ,(
select cmark from mark where cid=(
select cid from course where cname='数学'
) and sid=sc.sid
) 数学 ,(
select cmark from mark where cid=(
select cid from course where cname='英语'
)and sid=sc.sid
) 英语,count(*) 有效课程数,avg(cmark) 有效均分
from mark sc
group by sid;
21-30
- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
课程名 第一名 第二名 第三名
不会
- 22、查找至少2门成绩在80分以上的学生姓名
select sname from student where sid in(
select sid from mark where cmark>80 group by sid having count(*)>=2
);
- 23、查询没有选修数学课的上海学生姓名
select sname
from student
where snativeplace='上海' and sid not in(
select sid from mark where cid in(
select cid from course where cname='数学'
)
)
- 24、找出和张三所学课程完全相同的学生姓名
select sname from student where sid in(
select distinct(sid) from mark where sid not in(
select sid from mark where sid=(
select sid from student where sname='张三'
)
)
group by sid
having count(cid)=(select count(cid) from mark where sid=(
select sid from student where sname='张三'
)
)
)
- 25、查询只选了数学和英语课的学生姓名
select sid from mark
where sid in(
select a.sid from mark a,mark b
where a.cid=(select cid from course where cname='英语')
and b.cid=(select cid from course where cname='数学')
and a.sid=b.sid
) group by sid
having count(*)=2
- 26、找出计算机专业中均分最高的男生姓名
select sname from student where sid in(
select sid from (
select sid,avg(cmark) amk from mark where sid in(
select sid from student where ssex='男' and smajor='计算机'
)
) where amk=(
select max(amk) from (
select sid,avg(cmark) amk from mark where sid in(
select sid from student where ssex='男' and smajor='计算机'
)
group by sid
)
)
)
- 27、男生人数最多的地区中哪个学生年龄最大
select sid,sname,sage,snativeplace from student where snativeplace=(
select snativeplace from(
select snativeplace,count(*) c from student where ssex='男' group by snativeplace
) where c=(
select max(c) from (
select snativeplace,count(*) c from student where ssex='男' group by snativeplace
)
)
) and sage=(
select max(sage) from student
where snativeplace=(
select snativeplace from(
select snativeplace,count(*) c from student where ssex='男' group by snativeplace
) where c=(
select max(c) from (
select snativeplace,count(*) c from student where ssex='男' group by snativeplace
)
)
)
)
- 28、数学状元的同班同学中男生均分最高的学生姓名
select sname from student where sid in (
select sid from(
select sid,avg(cmark) am FROM MARK where sid in (
select sid from student where SCLASS=(
select SCLASS from student where sid=(
select sid from(
select sid,cmark from MARK where CID=(
select cid from COURSE where CNAME='数学'
)
)where cmark=(
select max(cmark) from (
select sid,cmark from MARK where CID=(
select cid from COURSE where CNAME='数学'
)
)
)
)
) and SSEX='男' and sid!=(
select sid from(
select sid,cmark from MARK where CID=(
select cid from COURSE where CNAME='数学'
)
) where cmark=(
select max(cmark) from (
select sid,cmark from MARK where CID=(
select cid from COURSE where CNAME='数学'
)
)
)
)
) group by sid
)where am=(
select max(am) from (
select sid,avg(cmark) am FROM MARK where SID in (
select sid from student where SCLASS=(
select SCLASS from student where SID=(
select sid from(
select sid,cmark from MARK where CID=(
select cid from COURSE where CNAME='数学'
)
)where cmark=(
select max(cmark) from (
select sid,cmark from MARK where CID=(
select cid from COURSE where CNAME='数学'))))) and SSEX='男' and sid!=(
select sid from(
select sid,cmark from MARK where CID=(select cid from COURSE where CNAME='数学'
)
) where cmark=(
select max(cmark) from (
select sid,cmark from MARK where CID=(
select cid from COURSE where CNAME='数学'
)
)
)
)
) group by sid
)
)
);
- 29、请显示张三同学的数学和英语成绩,要求显示姓名，数学成绩，英语成绩
select sname,(
select cmark from mark where sid=(select sid from student where sname='张三')
and cid=(select cid from course where cname='数学')
) 数学成绩,(
select cmark from mark where sid=(select sid from student where sname='张三')
and cid=(select cid from course where cname='英语')
) 英语成绩
from student s
where sname='张三'
- 30、找出数学和英语均分最高的男生姓名
select SNAME from STUDENT where Sid in (
select sid from (
select sid,avg(amk) am from (
select sid,avg(CMARK) amk from MARK m where CID=(
select CID from COURSE where CNAME='数学'
)group by SID union
select sid,avg(CMARK) amk from MARK m where CID=(
select CID from COURSE where CNAME='英语'
)group by SID
)group by SID) where am=(
select max(am) from (
select sid,avg(amk) am from (
select sid,avg(CMARK) amk from MARK m where CID=(
select CID from COURSE where CNAME='数学'
)group by SID union
select sid,avg(CMARK) amk from MARK m where CID=(
select CID from COURSE where CNAME='英语'
)group by SID
)
group by SID
)
)
);
31-35
- 31、找出个人均分大于总均分（所有人所有课程的均分）的学生姓名
select sname from student where sid in(
select sid from mark group by sid having avg(cmark)>(
select avg(amk) from (
select avg(cmark) amk from mark group by sid
)
)
)
- 32、上海地区哪门课的均分比福建差
select cname from course where cid in (
select a.cid from (
select cid,avg(cmark) amk from mark where sid in (
select sid from STUDENT where SNATIVEPLACE='上海'
) group by cid
) a,(
select cid,avg(CMARK) amk from MARK where sid in (
select sid from STUDENT where SNATIVEPLACE='福建'
) group by cid
) b where a.cid=b.cid and a.amk<b.amk
)
- 33、求各门课程去掉一个最高分和最低分后的平均分
select cid,avg(cmark) from (
select m1.cid,cmark from mark m1,(
select cid,max(cmark) a ,min(cmark) b from mark group by cid) m2 where m1.cid=m2.cid and m1.cmark!=m2.a and cmark!=m2.b
)group by cid;
- 34、谁的各科成绩均高于各科均分
select sname from student where sid in(
select sid from (
select m1.sid,count(*) c from mark m1,(
select cid,avg(cmark) am from mark m group by cid
) m2 where m1.cid=m2.cid and m1.cmark>m2.amk group by m1.sid
) where c=7
);
- 35、查询各科成绩前三名的记录:要求显示：课程名 sid cmark
不会
扫一扫
5459
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
