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表结构
数据