oracle口试问题,oracle 口试

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