恒生电子实施面试题

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 )
)

2 、 – Create table 书籍库存表

create table BOOKS
(
BOOK_ID NUMBER,
BOOK_NAME  VARCHAR2( 254 ),
AU_ID NUMBER,
TYPE VARCHAR2( 64 ),
PRICE NUMBER,
QTY NUMBER
)

问题 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
where au_id in(select au_id  from  authors where city='浙江')
group by au_id;

注释：如果要筛选作者信息表作者的库存总量，则添加 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, 0 from 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)