oracle 面试

面试题1、

prompt PL/SQL Developer import file
 prompt Created on 2012年2月4日星期六 by Administrator
 set feedback off
 set define off
 prompt Creating DINGDAN...
 create table DINGDAN
 (
   NAME      VARCHAR2(60),
   CHANPINID VARCHAR2(30)
 )
 tablespace USERS
   pctfree 10
   initrans 1
   maxtrans 255
   storage
   (
     initial 64K
     minextents 1
     maxextents unlimited
   );

 prompt Disabling triggers for DINGDAN...
 alter table DINGDAN disable all triggers;
 prompt Truncating DINGDAN...
 truncate table DINGDAN;
 prompt Loading DINGDAN...
 insert into DINGDAN (NAME, CHANPINID)
 values ('jack', '1');
 insert into DINGDAN (NAME, CHANPINID)
 values ('jack', '2');
 insert into DINGDAN (NAME, CHANPINID)
 values ('jack', '3');
 insert into DINGDAN (NAME, CHANPINID)
 values ('lucy', '2');
 insert into DINGDAN (NAME, CHANPINID)
 values ('lucy', '1');
 insert into DINGDAN (NAME, CHANPINID)
 values ('lucy', '5');
 insert into DINGDAN (NAME, CHANPINID)
 values ('dog', '6');
 insert into DINGDAN (NAME, CHANPINID)
 values ('david', '7');
 commit;
 prompt 8 records loaded
 prompt Enabling triggers for DINGDAN...
 alter table DINGDAN enable all triggers;
 set feedback on
 set define on
 prompt Done.



 select t.*, t.rowid from dingdan t for update
 --     查出订单数量大于3个用户名字 名字：name，产品id chanpinid 
 --方法1
  select tt.name from (select name , count(chanpinid) cc from dingdan dd  group by dd.name) tt where tt.cc>2 








 面试题2、

 prompt PL/SQL Developer import file
 prompt Created on 2012年2月4日星期六 by Administrator
 set feedback off
 set define off
 prompt Creating XUESHENBIAO...
 create table XUESHENBIAO
 (
   CNAME   VARCHAR2(60),
   KECHENG VARCHAR2(60),
   CHENJI  NUMBER
 )
 tablespace USERS
   pctfree 10
   initrans 1
   maxtrans 255
   storage
   (
     initial 64K
     minextents 1
     maxextents unlimited
   );

 prompt Disabling triggers for XUESHENBIAO...
 alter table XUESHENBIAO disable all triggers;
 prompt Truncating XUESHENBIAO...
 truncate table XUESHENBIAO;
 prompt Loading XUESHENBIAO...
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('jack', '课程1', 99);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('jack', '课程2', 98);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('jack', '课程3', 92);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('jack', '课程4', 90);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('jack', '课程5', 88);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('lucy', '课程1', 90);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('lucy', '课程6', 100);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('lucy', '课程7', 99);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('lucy', '课程18', 96);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('lucy', '课程3', 95);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('dog', '课程34', 89);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('dog', '课程2', 21);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('dog', '课程2', 22);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('dog', '课程2', 68);
 insert into XUESHENBIAO (CNAME, KECHENG, CHENJI)
 values ('dog', '课程2', 71);
 commit;
 prompt 15 records loaded
 prompt Enabling triggers for XUESHENBIAO...
 alter table XUESHENBIAO enable all triggers;
 set feedback on
 set define on
 prompt Done.








--  一个学生最多能选5门课。就平均成绩大于90的学生名单 。  学生名字：cname，课程：kecheng，成绩：chenji。
-- 好像 kecheng 字段没有用到。


 -- 一个人最多能选几门课
 (select  max(count(xsb.cname)) from xueshenbiao xsb group by cname)


 -- 求平均成绩
 select wb.cn
   from (select cname cn, sum(xsb.chenji) cj
           from xueshenbiao xsb
          group by cname) wb
  where (wb.cj /
        (select max(count(xsb.cname)) from xueshenbiao xsb group by cname)) > 90