PL/SQL 综合复习题之答案(1)

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;*/