**********
28.
*********************************
select cno,avg(score) from sc group by cno order by avg(score)asc,cno desc;
*********************************
29.
*********************************
select st.sno,st.sname,avg(score) from student st
left join sc
on sc.sno=st.sno
group by st.sno,st.sname having avg(score)>85;
*********************************
30.
*********************************
select sname,score from student st,sc,course c
where st.sno=sc.sno and sc.cno=c.cno and c.cname='
Oracle' and sc.score<60
*********************************
31.
*********************************
select st.sno,st.sname,c.cname from student st,sc,course c
where sc.sno=st.sno and sc.cno=c.cno;
*********************************
32.
*********************************
select st.sname,c.cname,sc.score from student st,sc,course c
where sc.sno=st.sno and sc.cno=c.cno and sc.score>70
*********************************
33.
*********************************
select sc.sno,c.cname,sc.score from sc,course c
where sc.cno=c.cno and sc.score<60 order by sc.cno desc;
*********************************
34.
*********************************
select st.sno,st.sname,sc.score from sc,student st
where sc.sno=st.sno and cno='c001' and score>80;
*********************************
35.
*********************************
select count(distinct sno) from sc;
*********************************
36.
*********************************
select st.sname,score from student st,sc ,course c,teacher t
where
st.sno=sc.sno and sc.cno=c.cno and c.tno=t.tno
and t.tname='谌燕' and sc.score=
(select max(score)from sc where sc.cno=c.cno)
*********************************
37.
*********************************
select cno,count(sno) from sc group by cno;
*********************************
38.
*********************************
select a.* from sc a ,sc b where a.score=b.score and a.cno<>b.cno
*********************************
39.
*********************************
select * from (
select sno,cno,score,row_number()over(partition by cno order by score desc) my_rn from sc t
)
where my_rn<=2
*********************************
40.
*********************************
select cno,count(sno) from sc group by cno
having count(sno)>10
order by count(sno) desc,cno asc;
*********************************
41.
*********************************
select sno from sc group by sno having count(cno)>1;
||
select sno from sc group by sno having count(sno)>1;
*********************************
42.
*********************************
select distinct(c.cno),c.cname from course c ,sc
where sc.cno=c.cno
||
select cno,cname from course c
where c.cno in
(select cno from sc group by cno)
*********************************
43.
*********************************
select st.sname from student st
where st.sno not in
(select distinct sc.sno from sc,course c,teacher t
where sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕')
*********************************
44.
*********************************
select sno,avg(score)from sc
where sno in
(select sno from sc where sc.score<60
group by sno having count(sno)>1
) group by sno
*********************************
45.
*********************************
select sno from sc where cno='c004' and score<90 order by score desc;
*********************************
46.
*********************************
delete from s |