Orcle数据库查询练习复习：三

一、题目

1.与“张三”同乡的男生姓名

select * from student where snativeplace=(select snativeplace from student where sname='张三') and ssex='男'

2.选修了赵露老师所讲课程的学生人数

select count(*) from mark where cid in(select cid from course where tid=(select tid from teacher where tname='赵露')) group by cid
select count(distinct sid) from mark where cid in(select cid from course where tid=(select tid from teacher where tname='赵露')) 

3.查询没学过“王”姓老师课的同学的学号、姓名

select * from student where sid not in(select sid from mark where cid in(
select cid from course where tid in(select tid from teacher where tname like '王%')))

4.数学”课程得最高分的学生姓名、性别

select * from student where sid in(
select sid from mark where cid=(
select cid from course where cname='数学') 
and cmark>= all(
select cmark from mark where cid=(select cid from course where cname='数学')
))

5.统计每门课程的平均成绩,并按照成绩降序排序

select *from(select (select cname from course where cid=m.cid), avg(cmark) cavg 
from mark m group by cid )order by cavg desc
select * from (select avg(cmark) cavr from mark group by cid)order by cavg desc

6.子查询实现查询‘3-2班’"张立"同学的"英语"成绩 

select cmark 成绩 from mark where sid=(
select sid from student where sname='张三'and sclass='2班')
and cid =(select cid from course where cname='英语')

7.查询所在班级和该班内学生的年龄之和,对该班级中每个人的年龄进行比对，(要求大于20岁的人参与统计)

select sclass ,sum(sage) from student where sage>=20 group by sclass 

8.查询所在班级和该班内学生的年龄之和,(要求该班级中每个人的年龄都大于20岁)

select sclass ,sum(sage) from student group by sclass having min(sage)>20

select sclass ,sum(sage) from student where sclass not in(
select sclass from student where sage<20 group by sclass) group by sclass 

9.用子查询实现查询选修“高等数学”课的全部学生的高等数学总成绩 

select sum(cmark) from mark where cid =(select cid from course where cname='数学')

10.用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩*/

select sum(cmark) from mark where sid in(select sid from mark where cid =(select cid from course where cname='数学'))

11.请用两种方法实现:查找所有成绩都在68分以上的学生姓名*/

select sid,sname from student where sid in (
select sid from mark group by sid having min(cmark)>=68)

select sid,sname from student s where exists (
select sid from mark m where s.sid=m.sid group by sid having min(cmark)>=68)

12.查找至少2门成绩在80分以上的学生姓名

select sname from student where sid in(
select sid from mark where cmark >=80 group by sid having count(*)>=2
)

13.查询至少有一门课与张三同学所学相同的同学的学号和姓名

select sid,sname from student where sid in(
select sid from mark where cid in (
select cid from mark where sid in (
select sid from student where sname='张三')))

14.没有选修“数学”课的学生的姓名*/

select sname from student where sid in (
select sid from mark where sid not in(
select sid from mark where cid in(
select cid from course where cname='数学'
)
)
)

15.查询个人总成绩小于平均总成绩的学生姓名

step1、计算出平均总分
step1.1计算出个人总分表
select sid,sum(cmark) smk from mark group by sid 
step1.2对总分表求平均值
select avg(smk) from (step1.1)
step2、找出在个人总分表中找出谁的总分低于step1
step2.1 select sid from (step1.1)where smk <(step1.2)
step2.2 select sname from student where sid in (step2.1)