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 sc where sno='s002' and cno='c001'; *********************************
百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Oracle 常用命令之基础使用(5)在线全文阅读。
相关推荐：