恒生电子实施技术笔试-2013

一、数据库方面
1、-- Create table 作者信息表create table AUTHORS
(
AU_ID NUMBER,
AU_NAME VARCHAR2(64),
PHONE VARCHAR2(64),
AGE NUMBER,
ADDRESS VARCHAR2(64),
CITY VARCHAR2(64),
STATE VARCHAR2(64),
ZIP VARCHAR2(64)
)
tablespace USERS
pctfree10
initrans1
maxtrans255
storage
(
initial64
next1
minextents1
maxextents unlimited
);
2、-- Create table 书籍库存表create table BOOKS
(
BOOK_ID NUMBER,
BOOK_NAME VARCHAR2(254), AU_ID NUMBER,
TYPE VARCHAR2(64),
PRICE NUMBER,
QTY NUMBER
)
tablespace USERS
pctfree10
initrans1
maxtrans255
storage
(
initial64
next1
minextents1
maxextents unlimited
);
问题1显示数据编号、书籍名称、作者编号、作者姓名、作者电话、年龄信息答：select b.book_id,b.book_name,a.au_id,a.phone,a.age
from authors a,books b
where b.au_id=a.au_id(+)
注释：书籍库存表的作者可能在作者信息表里没有采用左连接
问题2 汇总省份为浙江省的每个作者的所有书籍的库存量；（显示作者编号、库存总量字段）答：select t.au_id,sum(qty)
from books t
group by au_id
having au_id in (select au_id from authors);
注释：如果要筛选作者信息表作者的库存总量，则添加having子句
问题3 修改数据库存表符合下列条件的书籍的价格为原来的0.8倍：作者姓名为：“鲁迅”；
答：update books set price=0.8*price
where au_id =(select au_id from authors where au_name='鲁迅');
注释：如果作者信息表作者姓名有重复，则
where au_id in(select au_id from authors where au_name='鲁迅'); 问题 4 删除作者信息表中符合下列条件的作者信息：其编写的书籍中，书籍名称包含字符‘oracle’且库存量小于100本的书籍作者；
答：delete from authors
where au_id in(
select au_id
from books
where book_name like '%oracle%' and qty<100
);
问题5 统计书籍库存表中作者个数，且这些作者的年龄大于30岁；
答：select count（*）
from books
where au_id in (select au_id from authors where age >30);
问题6 用一个语句完成下述任务：根据库存表书籍编号为N的书籍库存信息，在书籍库存表中增加书籍库存信息，新增的书籍编号比原来的书籍号大10000，库存数量为0；
答：insert into books(book_id,book_name,au_id,type,price,qty) select book_id+100,book_name,au_id,type,price,0from books;
问题7 汇总书籍库存表中年龄大于30岁或小于18岁每个作者的库存总量(显示作者的编号、年龄、库存总量三个字段)；
答：select b.au_id,a.age,sum(qty)
from books b, authors a
where a.au_id=b.au_id
group by b.au_id,a.age having age>30 or age<18
问题8 列出符合下列条件的作者姓名：这些作者既出过书籍名为"ABC" and "UFO"的书；
答：select au_name from authors
where au_id in (select distinct au_id from books where
book_name='ABC' and book_name='UFO')
问题9 列出符合下列条件的作者姓名:在书籍库存表中，这些作者出过的书籍类别“文学”库存数量大于自己出过的书籍类别为“历史”的的库存数量，大于自己；
答：create tables wbook as select a.au_name ,b.au_id ,sum(qty) from authors a, books b
where a.au_id=b.au_id
group by au_name,au_id,type
having type='文学'
alter table wbooks
add column_name lau_name vchar32(64)
add column_name lqty nuber;
create tables lbook as select a.au_name ,b.au_id ,sum(qty)
from authors a, books b
where a.au_id=b.au_id
group by au_name,au_id,type
having type='文学'
update wbook lqty=(select qty from lbook where wbook.au_id =lbook_id(+))
select a.au_name from authors a where au_id in (select au_id from wbook where qty>lqty)。