Oracle查询

--5.查询科目1高于平均分的学生信息
select student.*,score.subjectid,score.scorenumber from student,score where student.stuid=score.stuid and scorenumber>
(select AVG(scorenumber) avgscore from SUBJECT ,SCORE  where subject.subjectid=SCORE.subjectid GROUP BY subject.subjectid having subject.subjectid='2015181031') and score.subjectid='2015181031';

--6.查询所有科目都在所在科目平均分以上的学生信息

--方法一
select student.* from student,score where student.stuid=score.stuid and scorenumber>
(select AVG(scorenumber) avgscore from SUBJECT ,SCORE  where subject.subjectid=SCORE.subjectid GROUP BY subject.subjectid having subject.subjectid='2015181031') and score.subjectid='2015181031' intersect(
select student.* from student,score where student.stuid=score.stuid and scorenumber>
(select AVG(scorenumber) avgscore from SUBJECT ,SCORE  where subject.subjectid=SCORE.subjectid GROUP BY subject.subjectid having subject.subjectid='2015181032') and score.subjectid='2015181032') intersect(
select student.* from student,score where student.stuid=score.stuid and scorenumber>
(select AVG(scorenumber) avgscore from SUBJECT ,SCORE  where subject.subjectid=SCORE.subjectid GROUP BY subject.subjectid having subject.subjectid='2015181033') and score.subjectid='2015181033') intersect(
select student.* from student,score where student.stuid=score.stuid and scorenumber>
(select AVG(scorenumber) avgscore from SUBJECT ,SCORE  where subject.subjectid=SCORE.subjectid GROUP BY subject.subjectid having subject.subjectid='2015181034') and score.subjectid='2015181034');
--方法二
select stu.* from score sc1,student stu where sc1.stuid=stu.stuid and sc1.scorenumber >(
select avg(sc.scorenumber) from score sc group by sc.subjectid having sc.subjectid='2015181031' ) and sc1.subjectid='2015181031' intersect
(select stu.* from score sc1,student stu where sc1.stuid=stu.stuid and sc1.scorenumber >(
select avg(sc.scorenumber) from score sc group by sc.subjectid having sc.subjectid='2015181032' ) and sc1.subjectid='2015181032') intersect
(select stu.* from score sc1,student stu where sc1.stuid=stu.stuid and sc1.scorenumber >(
select avg(sc.scorenumber) from score sc group by sc.subjectid having sc.subjectid='2015181033' ) and sc1.subjectid='2015181033') intersect
select stu.* from score sc1,student stu where sc1.stuid=stu.stuid and sc1.scorenumber >(
select avg(sc.scorenumber) from score sc group by sc.subjectid having sc.subjectid='2015181034' ) and sc1.subjectid='2015181034'

--7.查询总成绩最高的学生信息和最低的学生信息以及总成绩
select student.*, ss.su from student, 
(select score.stuid,sum(score.scorenumber) su from STUDENT,score where student.stuid=score.stuid group by score.stuid having sum(scorenumber) = (
select min(sc.sumsc) from student,(select sum(scorenumber) sumsc ,stuid from score group by stuid)sc where student.stuid=sc.stuid))ss where student.stuid=ss.stuid

union(

select student.*,sd.sf from student,(
select score.stuid,sum(score.scorenumber) sf from STUDENT,score where student.stuid=score.stuid group by score.stuid having sum(scorenumber) = (
select max(sc.sumsc) from student,(select sum(scorenumber) sumsc ,stuid from score group by stuid)sc where student.stuid=sc.stuid))sd where student.stuid=sd.stuid);

--方法二
select * from (
(select s2.stuid,s2.ss from (
select sum(scorenumber) ss,stuid from score group by stuid)s2 
where s2.ss in
((select  max(sa.sums) from 
(select stuid,sum(scorenumber) sums from score group by stuid)sa)
,
(select  min(sa.sums) from 
(select stuid,sum(scorenumber) sums from score group by stuid)sa)))a left join student st on st.stuid=a.stuid)

--8.查各科成绩最高的学生信息。
select max(scorenumber),subjectid from score group by subjectid;
--方法一
select student.* ,score.subjectid from student ,score where score.stuid=student.stuid and scorenumber=(select max(scorenumber) from score where subjectid='2015181031') and subjectid='2015181031' union(
select student.* ,score.subjectid from student ,score where score.stuid=student.stuid and scorenumber=(select max(scorenumber) from score where subjectid='2015181032') and subjectid='2015181032') union (
select student.*,score.subjectid from student ,score where score.stuid=student.stuid and scorenumber=(select max(scorenumber) from score where subjectid='2015181033') and subjectid='2015181033') union (
select student.*,score.subjectid from student ,score where score.stuid=student.stuid and scorenumber=(select max(scorenumber) from score where subjectid='2015181034') and subjectid='2015181034');
--方法二
select * from 
(select sc1.stuid ,sc1.scorenumber ,sc1.subjectid from score sc1,(
select subjectid,max(scorenumber) maxs from score group by subjectid)sa where sa.subjectid=sc1.subjectid and sc1.scorenumber=sa.maxs )sc left join student on student.stuid=sc.stuid

--9.查询各科成绩都是优秀的学生信息（分数大于80）

select scorenumber,stuid from score where scorenumber>80 ;
select student.*  from student,(
select score.stuid from score,student where scorenumber>80 and score.stuid=student.stuid and subjectid='2015181031' intersect(
select score.stuid from score,student where scorenumber>80 and score.stuid=student.stuid and subjectid='2015181032') intersect(
select score.stuid  from score,student where scorenumber>80 and score.stuid=student.stuid and subjectid='2015181032') intersect(
select score.stuid from score,student where scorenumber>80 and score.stuid=student.stuid and subjectid='2015181032'))sc where student.stuid=sc.stuid;

--方法二
select * from (
(select min(scorenumber),stuid from score group by stuid having min(scorenumber)>=80) sa left join student stu on sa.stuid=stu.stuid)

--10.查询各班各科平均成绩

select classid,subjectid,avg(scorenumber)  from student,score where student.stuid=score.stuid group by classid,subjectid 

--11.查询各班最高分学生的信息
 
 select * from 
 (select * from (
 (select max(sc.sums) maxs,student.classid from student,(
select sum(scorenumber) sums ,score.stuid  from student,score where score.stuid=student.stuid group by score.stuid)sc where sc.stuid=student.stuid and student.classid='20151811' group by student.classid)

left join(select student.stuid ,sum(score.scorenumber) ss from student,score where student.stuid=score.stuid and student.classid='20151811'  group by student.stuid)sg on maxs=sg.ss))sf,student where student.stuid=sf.stuid
union(
select * from 
 (select * from (
 (select max(sc.sums) maxs,student.classid from student,(
select sum(scorenumber) sums ,score.stuid  from student,score where score.stuid=student.stuid group by score.stuid)sc where sc.stuid=student.stuid and student.classid='20151812' group by student.classid)
left join(select student.stuid ,sum(score.scorenumber) ss from student,score where student.stuid=score.stuid and student.classid='20151812'  group by student.stuid)sg on maxs=sg.ss))sf,student where student.stuid=sf.stuid);
 
 

--查询一班的总成绩
select student.stuid,sum(scorenumber) ss from student,score where score.stuid=student.stuid and classid='20151811' group by student.stuid 

--查询二班的总成绩
select student.stuid,sum(scorenumber) ss from student,score where score.stuid=student.stuid and classid='20151812' group by student.stuid 
--查询两个班的最高分
(select max(sc.sums),student.classid from student,(
select sum(scorenumber) sums ,score.stuid  from student,score where score.stuid=student.stuid group by score.stuid
)sc where sc.stuid=student.stuid group by student.classid);

--12.查询所有学生各科成绩按照总成绩降序排列。

--方法一
select  g.stuid "学号",g."科目一",g."科目二",g. "科目三",g."科目四",g.sumsc as "总分" from(
select * from
(select stuid,sum(scorenumber) sumsc from score group by stuid)j left join(
 select * from 
(select * from 
(select stuid stuid3,scorenumber as "科目一" from score where subjectid='2015181031'  )a left join ( 
select stuid stuid1,scorenumber as "科目二" from score where subjectid='2015181032'  )b  on a.stuid3=b.stuid1) e
left join 
(select * from
(select stuid stuid4,scorenumber  as "科目三" from score where subjectid='2015181033')c left join(
select stuid stuid2,scorenumber  as "科目四" from score where subjectid='2015181034')d on c.stuid4=d.stuid2)f on 
e.stuid3=f.stuid4)k on j.stuid=k.stuid4 order by j.sumsc desc) g;

--方法二

SELECT stu.stuid,stu.stuname, a."科目一", b."科目二", c."科目三",d."科目四",a."科目一"+b."科目二"+ c."科目三"+d."科目四" sumscore
                  FROM student stu,
                       (SELECT s.scorenumber "科目一", s.stuid
                          FROM score s
                         WHERE s.subjectid = '2015181031') a,
                       (SELECT s.scorenumber "科目二", s.stuid
                          FROM score s
                         WHERE s.subjectid = '2015181032') b,
                       (SELECT s.scorenumber "科目三", s.stuid
                          FROM score s
                         WHERE s.subjectid = '2015181033') c,
                                                (SELECT s.scorenumber "科目四", s.stuid
                          FROM score s
                         WHERE s.subjectid = '2015181034') d 
                 WHERE stu.stuid = a.stuid
                   AND stu.stuid = b.stuid
                   AND stu.stuid = c.stuid
                                     and stu.stuid=d.stuid
                   order by sumscore desc

--13.查询韩梅梅老师所带课程的成绩

select student.stuid,stuname ,classid,sa."成绩" from student, (
select stuid,scorenumber as "成绩"from score where subjectid=(
select subjectid from subjectteacher where teacherid=(select teacherid from teacher where t_name='韩梅梅')))sa where sa.stuid=student.stuid 

--14.查询各班分数在前3位的学生姓名信息以及各科成绩和总分数，并按照总分数降序排列
--一班的前三
select * from (
select student.stuid,stuname, m."科目一",m."科目二",m. "科目三",m."科目四",m.sumsc summ, student.classid from student ,(
select  g.stuid5,g."科目一",g."科目二",g. "科目三",g."科目四",g.sumsc from(
select * from
(select stuid stuid5,sum(scorenumber) sumsc from score group by stuid)j left join(
 select * from 
(select * from 
(select stuid stuid3,scorenumber as "科目一" from score where subjectid='2015181031'  )a left join ( 
select stuid stuid1,scorenumber as "科目二" from score where subjectid='2015181032'  )b  on a.stuid3=b.stuid1) e
left join 
(select * from
(select stuid stuid4,scorenumber  as "科目三" from score where subjectid='2015181033')c left join(
select stuid stuid2,scorenumber  as "科目四" from score where subjectid='2015181034')d on c.stuid4=d.stuid2)f on 
e.stuid3=f.stuid4)k on j.stuid5=k.stuid4 order by j.sumsc desc) g)m where student.stuid=m.stuid5)p where p.classid='20151811' and rownum<=3 order by p.summ desc

--二班的前三
select * from (
select student.stuid,stuname, m."科目一",m."科目二",m. "科目三",m."科目四",m.sumsc summ, student.classid from student ,(
select  g.stuid5,g."科目一",g."科目二",g. "科目三",g."科目四",g.sumsc from(
select * from
(select stuid stuid5,sum(scorenumber) sumsc from score group by stuid)j left join(
 select * from 
(select * from 
(select stuid stuid3,scorenumber as "科目一" from score where subjectid='2015181031'  )a left join ( 
select stuid stuid1,scorenumber as "科目二" from score where subjectid='2015181032'  )b  on a.stuid3=b.stuid1) e
left join 
(select * from
(select stuid stuid4,scorenumber  as "科目三" from score where subjectid='2015181033')c left join(
select stuid stuid2,scorenumber  as "科目四" from score where subjectid='2015181034')d on c.stuid4=d.stuid2)f on 
e.stuid3=f.stuid4)k on j.stuid5=k.stuid4 order by j.sumsc desc) g)m where student.stuid=m.stuid5)p where p.classid='20151812' and rownum<=3 order by p.summ desc;

--15.查询各个班级的科目一的优秀率

select classid, count(*)/5 as"班级一科目一的优秀率" from(
select student.stuid ,classid from student,(select stuid,scorenumber from score where subjectid='2015181031' and scorenumber>80)sa where sa.stuid=student.stuid and classid='20151811') group by classid
union
(select  classid, count(*)/5 as"班级二科目一的优秀率" from(
select student.stuid,classid from student,(select stuid,scorenumber from score where subjectid='2015181031' and scorenumber>80)sb where sb.stuid=student.stuid and classid='20151812') group by classid)

--16.查询各个班级总成绩的优秀率
select * from(
select classid,count(*)/5 as"班级一总成绩的优秀率" from (
select student.stuid ,classid from student,(
select stuid,sum(scorenumber)  from score group by stuid having sum(scorenumber)>320 )sa where sa.stuid=student.stuid and classid='20151811') group by classid
union
(select classid,count(*)/5 as"班级二总成绩的优秀率" from (
select student.stuid ,classid from student,(
select stuid,sum(scorenumber)  from score group by stuid having sum(scorenumber)>320 )sa where sa.stuid=student.stuid and classid='20151812') group by classid))

 

Oracle去重

--删除重复的

delete from subject a where rowid not in (select max(rowid) from subject b where a.subjectname = b.subjectname );  

--分页

 --方法一 select * from(  select rownum r,subject.* from subject where rownum<5) t where t.r >1

--方法二  select * from (select rownum r,subject.* from subject )t where t.r between 2 and 4

CLASS表结构

STUDENT表结构

Score表结构

SUBJECTTEACHER表结构

SUBJECT表结构

TEACHER表结构

数据