PL/SQL 综合复习题之答案(1)
一
--1 select b.produce,sum(a.num) from commod1 a,commod2 b where a.id=b.idd group by b.produce; --2 select sum(num) from commod1 where num between 10 and 20; --3 select sort,max(price) from commod1 group by sort; --4 select s.stu_name from s_student s,x_course x where x.stu_id=s.stu_id and x.cour_id in (select x.cour_id from x_course x,s_student s where x.stu_id=s.stu_id and stu_name='@ml'); --5 select nvl(score,-1) from x_course;
二
--1 select a.sname from s a,sc b where a.sno=b.sno and b.cno <>(select cno from c where cteacher='李明'); --2 select a.sname,avg(b.scgrade) from s a,sc b where a.sno=b.sno and b.scgrade in (select scgrade from sc where scgrade<60) group by a.sname; --3 方法1： / *select a.sname from s a,( select a.sno from sc a,sc b where a.sno=b.sno and a.cno='01' and b.cno='02')aa where a.sno in aa.sno ;*/ 方法2： /* select a.sname from s a, sc b where a.sno = b.sno and b.cno='01' intersect select a.sname from s a, sc b where a.sno = b.sno and b.cno='02';*/ 方法3： /*select c.sname from (select sno from sc where cno = '01')a, ( select sno from sc where cno = '02')b, s c where a.sno = b.sno and a.sno = c.sno and b.sno = c.sno; */
--4 /*select c.sno,c.sname from (select scgrade,sno from sc where cno='01')a, (select scgrade from sc where sno='2' and cno='01')b, s c where c.sno= a.sno and a.scgrade>b.scgrade;*/ --5 /*select c.sno,a.scgrade,b.scgrade from (select scgrade,sno from sc where cno='01')a, (select scgrade,sno from sc where cno='02')b, s c where c.sno=a.sno and c.sno=b.sno and a.scgrade>b.scgrade;*/
三
--1 /* select deptno,avg(sal) from emp where sal>=600 group by deptno;*/ --2 /* select e.empno,d.dname from emp e,dept d where e.deptno=d.deptno and e.empno='001';*/ --3 /* update emp set sal=sal*0.1+sal where deptno=30 and sal<2000;*/
