Oracle之SQL查询练习题（二）

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号

法一：

select s1.sno
from sc s1,sc s2
where s1.sno=s2.sno and s1.sno='c001'
and s2.cno='c002' and s1.score>s2.score;

法二：

select sc1.sno 
from (select * from sc where cno='c001') sc1,
(select * from sc where cno='c001') sc2
where sc1.sno=sc2.sno and sc1.score>sc2.score;

法三：

select sno 
from sc 
where cno='c001' and score>(select max(score) from sc where cno='c002' and sno=sc.sno
);

2、查询平均成绩大于60 分的同学的学号和平均成绩

select sno,avg(score) 
from sc 
group by sno 
having avg(score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩

法一：

select s.sno,s.sname,count(sc.sno),sum(sc.score)
from student s
left join sc on s.sno=sc.sno
group by s.sno,s.sname;

法二：

select sc.sno,(select sname from studentwhere sno=sc.sno) sname,count(cno),sum(score)
from sc
group by sc.sno;

4、查询姓“刘”的老师的个数

select count(*) 
from teacher 
where tname like '%刘%';

5、查询没学过“谌燕”老师课的同学的学号、姓名

法一：

select sno,sname from student
where sno not in(select distinct sc.snofrom scinner join course c on sc.cno=c.cnoinner join teacher t on t.tno=c.tnowhere t.tname='谌燕'
);

法二：

select sno,sname from student s
where not exists(select distinct sc.snofrom scinner join course c on sc.cno=c.cnoinner join teacher t on t.tno=c.tnowhere t.tname='谌燕'and sc.sno=s.sno
);

法三：

select sno,sname 
from student s 
where sno not in(select sno from sc where sc.sno=s.sno and cno in(select cno from course where tno=(select tno from teacher where tname='谌燕'))
);

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名

法一：

select a.sname,a.sno 
from student a,(select sno from sc where cno='c001'intersect select sno from sc where cno='c002') b
where a.sno=b.sno;

法二：

select sno,sname
from student s
where exists(select s1.sno from (select * from sc where cno='c001') s1inner join (select * from sc where cno='c002') s2on s1.sno=s2.snowhere s1.sno=s.sno
);

法三：

select sno,sname
from student
where sno in (select s1.sno from sc s1inner join sc s2on s1.sno=s2.snowhere s1.cno='c001' and s2.cno='c002'   
);

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名

select sno,sname 
from student 
where sno in(select sno from sc join course con sc.cno=c.cnogroup by snohaving count(distinct c.cno)=(select count(cno)from course c1left join teacher ton c1.tno=t.tnowhere tname='谌燕')   
);

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名

法一：

select * from sc
select distinct st.sno,st.sname
from student st,
(select s2.sno
from sc s1,sc s2
where s1.cno='c001'
and s2.cno='c002'
and s2.score <s1.score) s
where st.sno = s.sno;

法二：

select sno,sname
from student
where sno in(select s1.snofrom sc s1,sc s2where s1.sno=s2.sno and s1.cno='c001' and s2.cno='c002' and s1.score>s2.score
);

法三：

select s1.sno,sname
from student s
inner join sc s1 
on s.sno=s1.sno
where cno='c001' and score>(select scorefrom sc where cno='c002' and sno=s1.sno
);

9、查询所有课程成绩小于60 分的同学的学号、姓名

法一：

select st.sname,st.sno
from student st,
(select sno
from sc
group by sno
having max(score) <60)s
where st.sno =s.sno；

法二：

select sno,sname
from student s
where exists (select sno,max(score) from sc group by sno having max(score)<60 and sc.sno=s.sno
);

法三：

select sno,sname 
from student
where sno in (select sno from sc group by snohaving max(score)<60
);

10、查询没有学全所有课的同学的学号、姓名

select sno,sname 
from student 
where sno not in(select sc.snofrom scgroup by sc.snohaving count(cno)=(select count(*) from course)
);

11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名

法一：

select distinct sc.sno,sname 
from student s 
inner join sc 
on s.sno=sc.sno 
where cno in(select cno from sc where sno='s001'
);

法二：

select distinct s.sno,sname
from sc c,student s
where c.sno=s.sno and c.cno in(select cno from sc where sno='s001'
);

12、查询至少学过学号为“s001”同学所有一门课的其他同学的学号和姓名

select distinct sc.sno,sname 
from student s 
inner join sc 
on s.sno=sc.sno 
where sc.sno<>'s001' and cno in(select cno from sc where sno='s001'
);

13、查询和“s001”号的同学学习的课程完全相同的其他同学的学号和姓名

法一：

select sno,sname 
from student
where sno in(select sno from sc where sno<>'s001' group by snohaving count(distinct cno)=(select count(cno)from scwhere sno='s001'group by sno)   
);

法二：

select sno,sname
from student 
where sno in (select sno from sc where sno not in(select sno from sc where cno not in (select cno from sc where sno='s001'))
group by sno 
having count(*)=(select count(*) from sc where sno='s001') and sno <> 's001'
);

法三：

select s.sno,s.sname 
from sc c,student s
where c.sno=s.sno and s.sno!='s001' and s.sno in(select distinct snofrom scwhere cno in (select cno from scwhere sno='s001')intersectselect distinct snofrom scgroup by snohaving count(cno)=(select count(cno) from sc where sno='s001')
);

14、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select cno "课程ID",max(score) "最高分",min(score) "最低分" 
from sc 
group by cno;

15、按各科平均成绩从低到高和及格率的百分数从高到低顺序

select cno,round(avg(score),2) "平均分",sum(case when sc.score > 60 then 1 else 0 end)/count(1) * 100||'%' "及格率" 
from sc 
group by cno 
order by "平均分" asc,"及格率" desc;

16、查询不同老师所教不同课程平均分从高到低显示

法一：

select distinct c.cname,t.tname,nvl(s.pjf,0)
from course c,teacher t,(select cno,avg(score) pjf from sc group by cno
) s
where s.cno(+)=c.cno and c.tno=t.tno(+)
group by t.tname,c.cname,s.pjf
order by 3 desc;

法二：

select c.tno,s.cno,avg(s.score)from sc s,course c where s.cno=c.cnogroup by c.tno,s.cnoorder by avg(s.score) desc;

17、统计各科成绩各分数段人数:课程ID,课程名称, [100-85],[85-70],[70-60],[ <60]

法一：

select cno,cname,
(select count(1) from sc where sc.cno=c.cno and sc.score<=100 and sc.score>85
) "[100-85]",
(select count(1) from sc where sc.cno=c.cno and sc.score<=85 and sc.score>70
) "[85-70]",
(select count(1) from sc where sc.cno=c.cno and sc.score<=70 and sc.score>60
) "[70-60]",
(select count(1) from sc where sc.cno=c.cno and sc.score<=60 and sc.score>=0
) "[60-0]"
from course c;

法二：

select c.cno,cname,
sum(case when score between 85 and 100 then 1 else 0 end
) "[100,85]",
sum(case when score between 70 and 85 then 1 else 0 end
) "[85,70]",
sum(case when score between 60 and 70 then 1 else 0 end
) "[70,60]",
sum(case when score between 5 and 60 then 1 else 0 end
) "[<80]"
from course c
join sc
on c.cno=sc.cno(+)
group by c.cno,cname;

18、查询各科成绩前三名的记录:(不考虑成绩并列情况)

法一：

select a.sno,a.cno,a.score
from sc a
left join sc b 
on a.cno = b.cno and a.score <= b.score 
group by a.sno,a.cno,a.score
having count(b.sno)<=3
order by a.cno,a.score desc

法二：

select sno, cno, score
from (select sno,cno,score,row_number() over(partition by cno order by cno, score desc) rfrom sc
)
where r <= 3;

19、查询每门课程被选修的学生数

法一：

select c.cno,cname,count(sc.sno)
from sc ,course c
where sc.cno(+)=c.cno
group by c.cno,cname;

法二：

select c.cno,c.cname,count(sc.sno)
from course c
left join sc
on sc.cno=c.cno
group by c.cno,c.cname;

20、查询出只选修了一门课程的全部学生的学号和姓名

法一：

select sno,sname
from student
where sno in(select sno from sc group by sno having count(cno)=1
);

法二：

select s.sno,s.sname
from sc c,student s
where s.sno=c.sno
group by s.sno,s.sname
having count(c.cno)=1;

21、查询男生、女生人数

法一：

select ssex,count(*) 
from student 
group by ssex;

法二：

select sum(case when ssex='男' then 1 else 0 end) 男生人数,
sum(case when ssex='女' then 1 else 0 end) 女生人数
from student

22、查询姓“张”的学生名单

select sname 
from student 
where sname like '张%';

23、查询同名同性学生名单，并统计同名人数

法一：

select sname,ssex,count(1) 同名人数 from student
group by sname,ssex having count(1)>1;

法二：

select s1.sname,s1.ssex,count(s1.sname)
from student s1,student s2
where s1.sname=s2.sname
and s1.ssex=s2.ssex
and s1.sno!=s2.sno
group by s1.sname,s1.ssex;

24、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select cno,avg(score) 
from sc 
group by cno 
order by avg(score),cno desc;

25、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩

select st.sno,sname 
from student st,(select sno,avg(score) 平均成绩from scgroup by sno 
) sc
where sc.sno=st.sno and sc.平均成绩>85;

26、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数

select sname,score 
from student s 
join sc on s.sno=sc.sno 
where sc.sno in(select sc.cno from course join sc on sc.cno=course.cno where cname='SQL SERVER 2005' and score<60
);

27、查询所有学生的选课情况

法一：

select sname,c.*
from student s,course c,sc
where s.sno=sc.sno(+) and c.cno(+)=sc.cno;

法二：

select st.sname,sc.*
from student st
left join(select sc.sno,c.cno,c.cname,c.tnofrom sc,course cwhere sc.cno=c.cno
) sc
on st.sno=sc.sno;

28、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数

法一：

select sname,cname,score 
from student s,course c,sc
where s.sno=sc.sno and c.cno=sc.cno and sc.score>70;

法二：

select st.sname,c.cname,sc.score
from student st,course c,(select sno,cno,scorefrom scwhere score>70
) sc
where sc.sno=st.sno and c.cno=sc.cno;

29、查询不及格的课程，并按课程号从大到小排列

select cno 
from sc 
where score<60 
order by cno desc

30、查询课程编号为c001 且课程成绩在80分以上的学生的学号和姓名

法一：

select sno,sname 
from student 
where sno in(select sno from sc where cno='c001' and score>80
);

法二：

select st.sno,st.sname
from student st,(select sno,cno,scorefrom scwhere score>80 and cno='c001'
) sc
where st.sno=sc.sno;

31、求选了课程的学生人数

select count( distinct sno) 
from sc

32、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩

法一：

select distinct sname,score 
from student s 
join sc on s.sno=sc.sno  
join course c on sc.cno=c.cno 
where score in( select max(score) from sc,teacher t,course cwhere sc.cno=c.cno and c.tno=t.tno and  tname='谌燕'group by sc.cno
) and tno=(select tno from teacher where tname='谌燕'
);

法二：

select st.sname,rs.score from(select sc.*,row_number() over(partition by sc.cno order by sc.score desc) r from sc)rs
join student st on st.sno=rs.sno
where rs.r=1 and rs.cno in(select cno from course where tno=(select tno from teacher where tname='谌燕')
);

法三：

select b.sname,b.score,b.cno,b.r 
from(select a.sname,a.score,a.cno,row_number() over(partition by cno order by score desc) rfrom(select s.sname,sc.score,c.cnofrom sc,student s,course c,teacher twhere sc.sno=s.sno and sc.cno=c.cno and c.tno=t.tno and tname='谌燕'order by score desc)a)b
where r=1;  

33、查询各个课程及相应的选修人数

select c.cno,c.cname,count(sc.sno)
from course c
left join sc
on sc.cno=c.cno
group by c.cno,cname;

34、查询不同课程成绩相同的学生的学号、课程号、学生成绩

法一：

select sno,cno,score from sc where score in (select score from sc where cno!=sc.cno
);

法二：

select s1.sno,s1.cno,s1.score
from sc s1,sc s2
where s1.cno!=s2.cno and s1.score=s2.score;

35、查询每门功课成绩最好的前两名

select * from (select s.*,row_number() over(partition by cno order by score desc)rfrom sc s)rs
where rs.r<=2;

36、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select cno,count(*) 
from sc 
group by cno 
having count(sno)>10 
order by count(*) desc,cno;

37、检索至少选修两门课程的学生学号

select sno 
from sc 
group by sno 
having count(cno)>=2;

38、查询全部学生都选修的课程的课程号和课程名

select c.cno,c.cname
from course c
where c.cno in(select cnofrom scgroup by cnohaving count(distinct cno)=(select count(sno) from student)
);

39、查询没学过“谌燕”老师讲授的任一门课程的学生姓名

法一：

select sname 
from student
where sno not in (select distinct sno from sc inner join course c on sc.cno=c.cnowhere tno=(select tno from teacher where tname='谌燕')
);

法二：

select sname 
from student
where sno not in(select distinct snofrom scjoin course c on c.cno=sc.cnojoin teacher t on t.tno=c.tnowhere t.tname='谌燕'
);

40、查询两门以上不及格课程的同学的学号及其平均成绩

select s.sno,s.sname,avg(sc.score)
from student s inner join sc on s.sno = sc.sno
where score<60
group by s.sno, s.sname
having count(distinct cno) >=2;

41、检索“c004”课程分数小于60，按分数降序排列的同学学号

select sno,cno,score
from sc 
where cno='c004' and score<60 
order by score desc;