1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
--方法一：
select m.* from
(select * from sc a where a.cno='c001') m,--分组课程
(select * from sc b where b.cno='c002') n
where m.sno = n.sno and m.score > n.score;--学号一样，课程成绩比较
--方法二：
select * from sc a
where a.cno='c001'
and exists(select * from sc b where b.cno='c002' and a.score>b.score and a.sno = b.sno)；
--在学号一样时，存在一个b的课程成绩小于a
2、查询平均成绩大于60 分的同学的学号和平均成绩；
select c.sno "学号",avg(c.score) "平均成绩" from sc c group by c.sno having avg(c.score) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select * from sc;
select *from student;
select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno;
--在两表中学号一样的进行学号、总成绩、选课数分组
4、查询姓“刘”的老师的个数；
select count(*) "姓 刘 的老师个数" from teacher t where t.tname like '刘%';
5、查询没学过“谌燕”老师课的同学的学号、姓名；
--放法一：
select s.sno "学号",s.sname "姓名" from student s
where s.sno
not in
(select distinct s.sno --去掉重复学号
from sc s,
(select c.*
from course c ,-- c总课程
(select tno
from teacher t
where tname='谌燕')t -- t是 谌燕 老师的课程
where c.tno=t.tno) b -- b是总课程与谌燕老师交集
where s.cno = b.cno ) --条件 课程一样
select * from teacher ;
select * from course ;
--方法二：
select st.sno "学号",st.sname "姓名" from student st where st.sno not
in(select distinct s.sno --去学号重复的
from sc s join course c on s.cno=c.cno join teacher t on c.tno=t.tno
--要求表sc和表course的课程一样，表course和表teacher的课程编号一样
and tname='谌燕') --也可以用 where
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select st.sno "学号",st.sname "姓名" from sc s
join sc a on s.sno = a.sno --学号是一样的
join student st
on s.sno = a.sno
where s.cno = 'c001' and a.cno = 'c002' and st.sno=s.sno;
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
select st.sno"学号",st.sname "姓名",s.cno "课程" from student st
join sc s on st.sno=s.sno --学号一样
join course c on s.cno=c.cno --再课程一样
join teacher t on c.tno=t.tno --课程编号一样
where t.tname='谌燕';
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select /*st.sno "学号",st.sname "姓名"*/* from student st
join sc s1 on st.sno = s1.sno
join sc s2 on st.sno = s2.sno
where s1.cno ='c001' and s2.cno = 'c002' and s1.score > s2.score;
9、查询所有课程成绩小于60 分的同学的学号、姓名；
select /*st.sno "学号",st.sname "姓名"*/* from student st
join sc s on st.sno = s.sno
/*join course c on s.cno=c.cno*/--也可以加上
where s.score < 60;
10、查询没有学全所有课的同学的学号、姓名；
select stu.sno,stu.sname,count(sc.cno) from
student stu left join sc on stu.sno=sc.sno --student和sc左连接
group by stu.sno,stu.sname
having count(sc.cno)<(select count(distinct cno)from course)
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
select st.* from student st,
(select distinct a.sno from --去掉重复学号
(select * from sc) a,
(select * from sc where sc.sno='s001') b --sc表中学号为s001的作为一个表对象
where a.cno=b.cno) h --a、b表 课程一样
where st.sno=h.sno and st.sno<>'s001';--学号不为s001的
12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；
select * from sc
left join student st --左连接
on st.sno=sc.sno --学号一样
where sc.sno<>'s001' --学号不为s001的
and sc.cno in(select cno from sc where sno='s001'); --查询学号为s001的所有课程信息
13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
update sc c set score=(select avg(c.score) from course a,teacher b
where a.tno=b.tno
and b.tname='谌燕'
and a.cno=c.cno
group by c.cno) --课程分组
where cno in( --限定条件
select cno from course a,teacher b
where a.tno=b.tno
and b.tname='谌燕'); --课程中所教老师为 谌燕
14、查询和“s002”号的同学学习的课程完全相同的其他同学学号和姓名；
select sc.sno,st.sname from student st,sc
where st.sno=sc.sno
group by sc.sno,st.sname --以学号、姓名分组
having count(*)=(select count(*) from sc where sno='s002' group by sno) --与s002课程数量一样的
and sc.sno!='s002'; --除学号为s002的其他同学
15、删除学习“谌燕”老师课的SC 表记录；
deleted from sc
where sc.cno --课程限定
in( select cno from course c
left join teacher t on c.tno=t.tno --左连接，教师编号
where t.tname='谌燕');
select * from teacher;
16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；
select * from sc;
savepoint B; --回滚点B
insert into sc (sno,cno,score) --学号，课程编号，成绩
select distinct st.sno,sc.cno,
(select avg(score) from sc where cno='c002') --去重，加上学号、课程编码、c002的平均成绩
from student st,sc
where not exists --不存在
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';
savepoint A; --回滚点A
rollback B; --回滚到B
17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select s.cno,max(s.score),min(s.score) from sc s group by s.cno;--在表sc中以课程分组
18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select cno "课程编号",avg(score) "课程平均成绩",sum(case when score>=60 then 1 else 0 end)/count(*) "课程及格率"
from sc group by cno
order by avg(score) , /*sum(case when score>=60 then 1 else 0 end)/count(*)*/课程及格率 desc
19、查询不同老师所教不同课程平均分从高到低显示
select max(t.tno),max(t.tname),max(c.cno),max(c.cname),avg(score) --最大教师编号、教师名字、课程编号、课程名字、课程平均分
from sc,course c,teacher t --三个主要表
where sc.cno = c.cno and c.tno = t.tno --课程编号、教师编号一样
group by c.cno --课程编号分组
order by avg(score) desc; --以课程平均分从高到低排序
20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select sc.cno "课程ID",c.cname "课程名称", --课程编号、课程名称
sum(case when score between 85 and 100 then 1 else 0 end) AS "[100-85]",
sum(case when score between 70 and 85 then 1 else 0 end) AS "[85-70]",
sum(case when score >= 60 and score < 70 then 1 else 0 end) AS "[70-60]",
sum(case when score < 60 then 1 else 0 end) AS "[<60]"
from sc, course c
where sc.cno=c.cno
group by sc.cno ,c.cname;
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
/*select * from
(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)
where rn<4;*/
22、查询每门课程被选修的学生数
select s.cno "课程编号",count(s.cno) "选修人数" from sc s group by s.cno;--分组统计
select * from course;
select * from sc;
23、查询出只选修了一门课程的全部学生的学号和姓名
select s.sno "学号",st.sname "姓名" from sc s
join student st on s.sno = st.sno --学号一样
group by st.sname,s.sno --以姓名、学号分组
having count(cno)=1; --统计课程编码为1的
24、查询男生、女生人数
select * from student;
select st.ssex "性别",count(*) "人数" from student st group by st.ssex;
25、查询姓“张”的学生名单
select * from student st where st.sname like '张%';
26、查询同名同性学生名单，并统计同名人数
select st.sname,count(*) from student st
group by st.sname; --姓名分组，统计姓名个数
select st.sname,count(*) from student st
group by st.sname having count(*)>1; --姓名分组，统计同名个数
27、1995 年出生的学生名单(注：Student 表中Sage 列的类型是number)
select sno,sname,sage,ssex from student t
where to_char(sysdate,'yyyy')-sage = 1995;--将日期型转为字符型，相减
28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select sc.cno "课程ID", avg(sc.score) "课程平均成绩" from sc
group by sc.cno --按课程号分组
order by avg(sc.score) asc, sc.cno desc; 先按课程平均成绩升序，再按课程号降序
29、查询平均成绩大于75 的所有学生的学号、姓名和平均成绩
select sc.sno "学号",st.sname "姓名",avg(sc.score) "平均成绩" from sc
join student st on st.sno = sc.sno
group by sc.sno,st.sname --分组要与上面的 学号、姓名 对应
having avg(sc.score) > 75;
30、查询课程名称为“SSH”，且分数低于60 的学生姓名和分数
select * from course;
select * from sc;
select * from student;
select st.sname,sc.score
from sc,student st,course c
where st.sno=sc.sno and sc.cno=c.cno and c.cname='SSH' and sc.score < 60;--相同项作等
31、查询所有学生的选课情况；
select sc.sno "学号",st.sname "姓名",sc.cno "课程ID",c.cname "课程名称",t.tname "授课教师"
from student st, course c,sc,teacher t
where st.sno=sc.sno and sc.cno=c.cno and c.tno = t.tno;
32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
select st.sname "姓名",c.cname "课程名称",sc.score "分数"
from sc,course c,student st
where sc.sno = st.sno and c.cno = sc.cno and sc.score > 70;
33、查询小于75的课程，并按课程号从大到小排列
select c.cno "课程号", c.cname "课程名称",sc.score "课程分数" from sc,course c
where sc.cno = c.cno and sc.score < 75
order by c.cno desc;--按课程号降序
34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
select st.sno "学号",st.sname "姓名",sc.score "分数"
from sc,student st --只需要sc和student表
where sc.sno = st.sno and sc.cno = 'c001' and sc.score > 80;
35、求选了课程的学生人数
select count(distinct sc.sno) from sc; --只需统计sc表中学号不重复的个数
36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select st.sname "学生姓名",sc.score "分数"
from sc,student st,course c,teacher t
where sc.sno = st.sno and c.tno = t.tno and sc.cno = c.cno and t.tname = '谌燕'
and sc.score = (select max(sc.score) from sc where sc.cno = c.cno );
--这里后面必须要用sc.cno = c.cno区别每一个课程
37、查询各个课程及相应的选修人数
select c.cno,count(sc.cno) from course c,sc
where sc.cno = c.cno
group by c.cno;--与22题一样，这里用两个表，需要课程名一样
38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select /*a.sno "学号",a.cno "课程号",a.score "学生成绩" */ a.*
from sc a ,sc b
where a.score = b.score and a.cno<>b.cno; --成绩一样、课程不一样
39、查询每门功课成绩最好的前两名
/*select * from
( select sno,cno,score,row_number() over(partition by cno order by score desc) my_rn from sc t )
where my_rn<=2;*/
40、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，
查询结果按人数降序排列，若人数相同，按课程号升序排列
select cno,count(sno) from sc group by cno
having count(sno)>10 --超过10人的课程
order by count(sno) desc,cno asc; --先按人数降序，再按课程降序
41、检索至少选修两门课程的学生学号
select sc.sno from sc
group by sno having count(cno) > 1;/* having count(sno)>1*/
42、查询全部学生都选修的课程的课程号和课程名
select distinct(c.cno),c.cname from sc,course c
where sc.cno = c.cno;
||
select cno,cname from course c
where c.cno in(select cno from sc group by cno)
43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
select st.sname from student st
where st.sno not in
(select distinct sc.sno from sc,course c,teacher t --学号去重
where sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕')
44、查询两门以上课程大于70分的同学的学号及其平均成绩
select sno,avg(score) from sc
where sno in
(select sno from sc where sc.score > 70 group by sno having count(sno)>1) --学号分组
group by sno;
45、检索“c002”课程分数小于90，按分数降序排列的同学学号
select * from course;
select * from sc ;
select sc.sno "学号",sc.cno "课程号",sc.score "课程分数" from sc
where sc.cno = 'c002' and sc.score < 90 order by sc.score desc; --这里数据少，
46、删除“s002”同学的“c001”课程的成绩
delete from sc where sno='s002' and cno='c001';
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
--方法一：
select m.* from
(select * from sc a where a.cno='c001') m,--分组课程
(select * from sc b where b.cno='c002') n
where m.sno = n.sno and m.score > n.score;--学号一样，课程成绩比较
--方法二：
select * from sc a
where a.cno='c001'
and exists(select * from sc b where b.cno='c002' and a.score>b.score and a.sno = b.sno)；
--在学号一样时，存在一个b的课程成绩小于a
2、查询平均成绩大于60 分的同学的学号和平均成绩；
select c.sno "学号",avg(c.score) "平均成绩" from sc c group by c.sno having avg(c.score) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select * from sc;
select *from student;
select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno;
--在两表中学号一样的进行学号、总成绩、选课数分组
4、查询姓“刘”的老师的个数；
select count(*) "姓 刘 的老师个数" from teacher t where t.tname like '刘%';
5、查询没学过“谌燕”老师课的同学的学号、姓名；
--放法一：
select s.sno "学号",s.sname "姓名" from student s
where s.sno
not in
(select distinct s.sno --去掉重复学号
from sc s,
(select c.*
from course c ,-- c总课程
(select tno
from teacher t
where tname='谌燕')t -- t是 谌燕 老师的课程
where c.tno=t.tno) b -- b是总课程与谌燕老师交集
where s.cno = b.cno ) --条件 课程一样
select * from teacher ;
select * from course ;
--方法二：
select st.sno "学号",st.sname "姓名" from student st where st.sno not
in(select distinct s.sno --去学号重复的
from sc s join course c on s.cno=c.cno join teacher t on c.tno=t.tno
--要求表sc和表course的课程一样，表course和表teacher的课程编号一样
and tname='谌燕') --也可以用 where
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select st.sno "学号",st.sname "姓名" from sc s
join sc a on s.sno = a.sno --学号是一样的
join student st
on s.sno = a.sno
where s.cno = 'c001' and a.cno = 'c002' and st.sno=s.sno;
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
select st.sno"学号",st.sname "姓名",s.cno "课程" from student st
join sc s on st.sno=s.sno --学号一样
join course c on s.cno=c.cno --再课程一样
join teacher t on c.tno=t.tno --课程编号一样
where t.tname='谌燕';
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select /*st.sno "学号",st.sname "姓名"*/* from student st
join sc s1 on st.sno = s1.sno
join sc s2 on st.sno = s2.sno
where s1.cno ='c001' and s2.cno = 'c002' and s1.score > s2.score;
9、查询所有课程成绩小于60 分的同学的学号、姓名；
select /*st.sno "学号",st.sname "姓名"*/* from student st
join sc s on st.sno = s.sno
/*join course c on s.cno=c.cno*/--也可以加上
where s.score < 60;
10、查询没有学全所有课的同学的学号、姓名；
select stu.sno,stu.sname,count(sc.cno) from
student stu left join sc on stu.sno=sc.sno --student和sc左连接
group by stu.sno,stu.sname
having count(sc.cno)<(select count(distinct cno)from course)
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
select st.* from student st,
(select distinct a.sno from --去掉重复学号
(select * from sc) a,
(select * from sc where sc.sno='s001') b --sc表中学号为s001的作为一个表对象
where a.cno=b.cno) h --a、b表 课程一样
where st.sno=h.sno and st.sno<>'s001';--学号不为s001的
12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；
select * from sc
left join student st --左连接
on st.sno=sc.sno --学号一样
where sc.sno<>'s001' --学号不为s001的
and sc.cno in(select cno from sc where sno='s001'); --查询学号为s001的所有课程信息
13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
update sc c set score=(select avg(c.score) from course a,teacher b
where a.tno=b.tno
and b.tname='谌燕'
and a.cno=c.cno
group by c.cno) --课程分组
where cno in( --限定条件
select cno from course a,teacher b
where a.tno=b.tno
and b.tname='谌燕'); --课程中所教老师为 谌燕
14、查询和“s002”号的同学学习的课程完全相同的其他同学学号和姓名；
select sc.sno,st.sname from student st,sc
where st.sno=sc.sno
group by sc.sno,st.sname --以学号、姓名分组
having count(*)=(select count(*) from sc where sno='s002' group by sno) --与s002课程数量一样的
and sc.sno!='s002'; --除学号为s002的其他同学
15、删除学习“谌燕”老师课的SC 表记录；
deleted from sc
where sc.cno --课程限定
in( select cno from course c
left join teacher t on c.tno=t.tno --左连接，教师编号
where t.tname='谌燕');
select * from teacher;
16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；
select * from sc;
savepoint B; --回滚点B
insert into sc (sno,cno,score) --学号，课程编号，成绩
select distinct st.sno,sc.cno,
(select avg(score) from sc where cno='c002') --去重，加上学号、课程编码、c002的平均成绩
from student st,sc
where not exists --不存在
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';
savepoint A; --回滚点A
rollback B; --回滚到B
17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select s.cno,max(s.score),min(s.score) from sc s group by s.cno;--在表sc中以课程分组
18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select cno "课程编号",avg(score) "课程平均成绩",sum(case when score>=60 then 1 else 0 end)/count(*) "课程及格率"
from sc group by cno
order by avg(score) , /*sum(case when score>=60 then 1 else 0 end)/count(*)*/课程及格率 desc
19、查询不同老师所教不同课程平均分从高到低显示
select max(t.tno),max(t.tname),max(c.cno),max(c.cname),avg(score) --最大教师编号、教师名字、课程编号、课程名字、课程平均分
from sc,course c,teacher t --三个主要表
where sc.cno = c.cno and c.tno = t.tno --课程编号、教师编号一样
group by c.cno --课程编号分组
order by avg(score) desc; --以课程平均分从高到低排序
20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select sc.cno "课程ID",c.cname "课程名称", --课程编号、课程名称
sum(case when score between 85 and 100 then 1 else 0 end) AS "[100-85]",
sum(case when score between 70 and 85 then 1 else 0 end) AS "[85-70]",
sum(case when score >= 60 and score < 70 then 1 else 0 end) AS "[70-60]",
sum(case when score < 60 then 1 else 0 end) AS "[<60]"
from sc, course c
where sc.cno=c.cno
group by sc.cno ,c.cname;
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
/*select * from
(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)
where rn<4;*/
22、查询每门课程被选修的学生数
select s.cno "课程编号",count(s.cno) "选修人数" from sc s group by s.cno;--分组统计
select * from course;
select * from sc;
23、查询出只选修了一门课程的全部学生的学号和姓名
select s.sno "学号",st.sname "姓名" from sc s
join student st on s.sno = st.sno --学号一样
group by st.sname,s.sno --以姓名、学号分组
having count(cno)=1; --统计课程编码为1的
24、查询男生、女生人数
select * from student;
select st.ssex "性别",count(*) "人数" from student st group by st.ssex;
25、查询姓“张”的学生名单
select * from student st where st.sname like '张%';
26、查询同名同性学生名单，并统计同名人数
select st.sname,count(*) from student st
group by st.sname; --姓名分组，统计姓名个数
select st.sname,count(*) from student st
group by st.sname having count(*)>1; --姓名分组，统计同名个数
27、1995 年出生的学生名单(注：Student 表中Sage 列的类型是number)
select sno,sname,sage,ssex from student t
where to_char(sysdate,'yyyy')-sage = 1995;--将日期型转为字符型，相减
28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select sc.cno "课程ID", avg(sc.score) "课程平均成绩" from sc
group by sc.cno --按课程号分组
order by avg(sc.score) asc, sc.cno desc; 先按课程平均成绩升序，再按课程号降序
29、查询平均成绩大于75 的所有学生的学号、姓名和平均成绩
select sc.sno "学号",st.sname "姓名",avg(sc.score) "平均成绩" from sc
join student st on st.sno = sc.sno
group by sc.sno,st.sname --分组要与上面的 学号、姓名 对应
having avg(sc.score) > 75;
30、查询课程名称为“SSH”，且分数低于60 的学生姓名和分数
select * from course;
select * from sc;
select * from student;
select st.sname,sc.score
from sc,student st,course c
where st.sno=sc.sno and sc.cno=c.cno and c.cname='SSH' and sc.score < 60;--相同项作等
31、查询所有学生的选课情况；
select sc.sno "学号",st.sname "姓名",sc.cno "课程ID",c.cname "课程名称",t.tname "授课教师"
from student st, course c,sc,teacher t
where st.sno=sc.sno and sc.cno=c.cno and c.tno = t.tno;
32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
select st.sname "姓名",c.cname "课程名称",sc.score "分数"
from sc,course c,student st
where sc.sno = st.sno and c.cno = sc.cno and sc.score > 70;
33、查询小于75的课程，并按课程号从大到小排列
select c.cno "课程号", c.cname "课程名称",sc.score "课程分数" from sc,course c
where sc.cno = c.cno and sc.score < 75
order by c.cno desc;--按课程号降序
34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
select st.sno "学号",st.sname "姓名",sc.score "分数"
from sc,student st --只需要sc和student表
where sc.sno = st.sno and sc.cno = 'c001' and sc.score > 80;
35、求选了课程的学生人数
select count(distinct sc.sno) from sc; --只需统计sc表中学号不重复的个数
36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select st.sname "学生姓名",sc.score "分数"
from sc,student st,course c,teacher t
where sc.sno = st.sno and c.tno = t.tno and sc.cno = c.cno and t.tname = '谌燕'
and sc.score = (select max(sc.score) from sc where sc.cno = c.cno );
--这里后面必须要用sc.cno = c.cno区别每一个课程
37、查询各个课程及相应的选修人数
select c.cno,count(sc.cno) from course c,sc
where sc.cno = c.cno
group by c.cno;--与22题一样，这里用两个表，需要课程名一样
38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select /*a.sno "学号",a.cno "课程号",a.score "学生成绩" */ a.*
from sc a ,sc b
where a.score = b.score and a.cno<>b.cno; --成绩一样、课程不一样
39、查询每门功课成绩最好的前两名
/*select * from
( select sno,cno,score,row_number() over(partition by cno order by score desc) my_rn from sc t )
where my_rn<=2;*/
40、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，
查询结果按人数降序排列，若人数相同，按课程号升序排列
select cno,count(sno) from sc group by cno
having count(sno)>10 --超过10人的课程
order by count(sno) desc,cno asc; --先按人数降序，再按课程降序
41、检索至少选修两门课程的学生学号
select sc.sno from sc
group by sno having count(cno) > 1;/* having count(sno)>1*/
42、查询全部学生都选修的课程的课程号和课程名
select distinct(c.cno),c.cname from sc,course c
where sc.cno = c.cno;
||
select cno,cname from course c
where c.cno in(select cno from sc group by cno)
43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
select st.sname from student st
where st.sno not in
(select distinct sc.sno from sc,course c,teacher t --学号去重
where sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕')
44、查询两门以上课程大于70分的同学的学号及其平均成绩
select sno,avg(score) from sc
where sno in
(select sno from sc where sc.score > 70 group by sno having count(sno)>1) --学号分组
group by sno;
45、检索“c002”课程分数小于90，按分数降序排列的同学学号
select * from course;
select * from sc ;
select sc.sno "学号",sc.cno "课程号",sc.score "课程分数" from sc
where sc.cno = 'c002' and sc.score < 90 order by sc.score desc; --这里数据少，
46、删除“s002”同学的“c001”课程的成绩
delete from sc where sno='s002' and cno='c001';
存笔记