create table student(
sno number(10) not null primary key,
sname varchar2(10)
)create table center(
cno number(10) not null primary key,
cname varchar2(10)
)create table sgrade(
sno number(10),
cno number(10),
sgrade number(3)
)
insert into student values(11,'a');
insert into student values(12,'b');
insert into student values(13,'c')select * from studentinsert into center values(21,'aa');
insert into center values(22,'bb');
insert into center values(23,'cc')delete from student where sno =23insert into sgrade values(11,21,54);
insert into sgrade values(12,22,57);
insert into sgrade values(11,21,51);
insert into sgrade values(12,21,36);
insert into sgrade values(11,21,28);
insert into sgrade values(12,22,42);
insert into sgrade values(11,21,59)insert into sgrade values(11,21,79);
insert into sgrade values(12,22,85);
insert into sgrade values(11,21,90);
insert into sgrade values(12,21,96);
insert into sgrade values(11,21,98);
insert into sgrade values(12,22,94);
insert into sgrade values(11,21,99)
select * from sgradeupdate sc set cno =23 where sgrade=45找出两门(含量们)以上不及格的学生姓名以及平均成绩select st.sno ,st.sname,sg.sgrade from student st ,sgrade sg where st.sno=sg.sno and sgrade< 60 //查处成绩小于60的select sno, avg(sgrade) avgs from sgrade group by sno //查询平均成绩select scs.sno,count(sno),scs.stname from (select st.sno ,st.sname stname,sg.sgrade from student st ,sgrade sg where st.sno=sg.sno and sgrade< 60) scs group by sno ,stname
select scss.stname,avgss.avgs
from (select sno, avg(sgrade) avgs
from sgrade group by sno) avgss,
(select scs.sno,count(sno),scs.stname
from (select st.sno ,st.sname stname,sg.sgrade
from student st ,sgrade sg
where st.sno=sg.sno and sgrade< 60) scs
group by sno ,stname) scss
where avgss.sno=scss.sno
sno number(10) not null primary key,
sname varchar2(10)
)create table center(
cno number(10) not null primary key,
cname varchar2(10)
)create table sgrade(
sno number(10),
cno number(10),
sgrade number(3)
)
insert into student values(11,'a');
insert into student values(12,'b');
insert into student values(13,'c')select * from studentinsert into center values(21,'aa');
insert into center values(22,'bb');
insert into center values(23,'cc')delete from student where sno =23insert into sgrade values(11,21,54);
insert into sgrade values(12,22,57);
insert into sgrade values(11,21,51);
insert into sgrade values(12,21,36);
insert into sgrade values(11,21,28);
insert into sgrade values(12,22,42);
insert into sgrade values(11,21,59)insert into sgrade values(11,21,79);
insert into sgrade values(12,22,85);
insert into sgrade values(11,21,90);
insert into sgrade values(12,21,96);
insert into sgrade values(11,21,98);
insert into sgrade values(12,22,94);
insert into sgrade values(11,21,99)
select * from sgradeupdate sc set cno =23 where sgrade=45找出两门(含量们)以上不及格的学生姓名以及平均成绩select st.sno ,st.sname,sg.sgrade from student st ,sgrade sg where st.sno=sg.sno and sgrade< 60 //查处成绩小于60的select sno, avg(sgrade) avgs from sgrade group by sno //查询平均成绩select scs.sno,count(sno),scs.stname from (select st.sno ,st.sname stname,sg.sgrade from student st ,sgrade sg where st.sno=sg.sno and sgrade< 60) scs group by sno ,stname
select scss.stname,avgss.avgs
from (select sno, avg(sgrade) avgs
from sgrade group by sno) avgss,
(select scs.sno,count(sno),scs.stname
from (select st.sno ,st.sname stname,sg.sgrade
from student st ,sgrade sg
where st.sno=sg.sno and sgrade< 60) scs
group by sno ,stname) scss
where avgss.sno=scss.sno
select st.sno ,st.sname ,count(*) 门数
from student st ,sgrade sg
where st.sno=sg.sno and sgrade < 60
group by st.sno ,st.sname
having count(*)>=2查询平均成绩 :
select sno, avg(sgrade) avgs
from sgrade
group by sno //
select (select s.sname from student s where s.sno=g.sno) sname,avg(g.sgrade) from sgrade g
where g.sno in(
select sno from sgrade where sgrade<60 having count(sno)>=2 group by sno)
group by g.sno
select b.sno,b.sname,avg(sgrade)
from sgrade a,student b
where a.sno=b.sno
group by b.sno,b.sname having count(distinct case when sgrade<60 then cno else null end )>2
from sgrade a,student b
where a.sno = b.sno
and a.sgrade < 60
group by a.sno,
b.sname
having count(a.sno) > 1