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