1.
********************************* select a.* from
(select * from sc a where a.cno='c001') a, (select * from sc b where b.cno='c002') b where a.sno=b.sno and a.score > b.score; ********************************* select * from sc a where a.cno='c001'
and exists(select * from sc b where b.cno='c002' and a.score>b.score and a.sno = b.sno)
********************************* 2.
*********************************
select sno,avg(score) from sc group by sno having avg(score)>60; ********************************* 3.
*********************************
select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno ********************************* 4.
*********************************
select count(*) from teacher where tname like '刘%'; ********************************* 5.
********************************* select a.sno,a.sname from student a where a.sno not in
(select distinct s.sno from sc s,
(select c.*
from course c , (select tno
from teacher t
where tname='谌燕')t where c.tno=t.tno) b where s.cno = b.cno )
*********************************
select * from student st where st.sno not in
(select distinct sno from sc s join course c on s.cno=c.cno join teacher t on c.tno=t.tno where tname='谌燕') *********************************
6.
********************************* select st.* from sc a join sc b on a.sno=b.sno join student st on st.sno=a.sno
where a.cno='c001' and b.cno='c002' and st.sno=a.sno; ********************************* 7.
*********************************
select st.* from student st join sc s on st.sno=s.sno join course c on s.cno=c.cno join teacher t on c.tno=t.tno where t.tname='谌燕'
********************************* 8.
********************************* select * from student st join sc a on st.sno=a.sno join sc b on st.sno=b.sno
where a.cno='c002' and b.cno='c001' and a.score < b.score ********************************* 9.
********************************* select st.*,s.score from student st join sc s on st.sno=s.sno join course c on s.cno=c.cno where s.score <60
********************************* 10.
*********************************
select stu.sno,stu.sname,count(sc.cno) from student stu left join sc on stu.sno=sc.sno group by stu.sno,stu.sname
having count(sc.cno)<(select count(distinct cno)from course) =================================== select * from student where sno in (select sno from
(select stu.sno,c.cno from student stu cross join course c minus
select sno,cno from sc) )
===================================
********************************* 11.
********************************* select st.* from student st, (select distinct a.sno from (select * from sc) a,
(select * from sc where sc.sno='s001') b where a.cno=b.cno) h
where st.sno=h.sno and st.sno<>'s001' ********************************* 12.
********************************* select * from sc left join student st on st.sno=sc.sno where sc.sno<>'s001' and sc.cno in
(select cno from sc where sno='s001')
********************************* 13.
*********************************
update sc c set score=(select avg(c.score) from course a,teacher b where a.tno=b.tno and b.tname='谌燕' and a.cno=c.cno group by c.cno) where cno in(
select cno from course a,teacher b where a.tno=b.tno and b.tname='谌燕')
********************************* 14.
********************************* select* from sc where sno<>'s001' minus (
select* from sc minus
select * from sc where sno='s001' )
********************************* 15.
*********************************
delete from sc where sc.cno in (
select cno from course c
left join teacher t on c.tno=t.tno where t.tname='谌燕' )
********************************* 16.
********************************* insert into sc (sno,cno,score)
select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002') from student st,sc where not exists
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002'; ********************************* 17.
*********************************
select cno ,max(score),min(score) from sc group by cno; ********************************* 18.
*********************************
select cno,avg(score),sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率
from sc group by cno
order by avg(score) , 及格率desc ********************************* 19.
********************************* select max(t.tno),max(t.tname),max(c.cno),max(c.cname),c.cno,avg(score) from sc , course c,teacher t
where sc.cno=c.cno and c.tno=t.tno group by c.cno
order by avg(score) desc
********************************* 20.
********************************* select sc.cno,c.cname,
sum(case when score between 85 and 100 then 1 else 0 end) AS \sum(case when score between 70 and 85 then 1 else 0 end) AS \sum(case when score between 60 and 70 then 1 else 0 end) AS \sum(case when score <60 then 1 else 0 end) AS \from sc, course c where sc.cno=c.cno
group by sc.cno ,c.cname;
********************************* 21.
********************************* select * from
(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc) where rn<4
********************************* 22.
*********************************
select cno,count(sno)from sc group by cno; ********************************* 23.
*********************************
select sc.sno,st.sname,count(cno) from student st left join sc on sc.sno=st.sno
group by st.sname,sc.sno having count(cno)=1; ********************************* 24.
*********************************
select ssex,count(*)from student group by ssex; ********************************* 25.
*********************************
select * from student where sname like '张%'; ********************************* 26.
*********************************
select sname,count(*)from student group by sname having count(*)>1; ********************************* 27.
********************************* select sno,sname,sage,ssex from student t where to_char(sysdate,'yyyy')-sage =1988 ********************************* 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