面试题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.








--