*****************************
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 "[100-85]",
sum(case when score between 70 and 85 then 1 else 0 end) AS "[85-70]",
sum(case when score between 60 and 70 then 1 else 0 end) AS "[70-60]",
sum(case when score <60 then 1 else 0 end) AS "[<60]"
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
*********************** |