20101231：JAVA企业面试题精选 数据库41-44

1.41.分别写出Oracle数据库和MySQL数据库对t_employee表分页的SQL语句,要求每页输出20条

参考答案:

1.Oracle数据库实现的分页,SQL语句如下所示:

SELECT * FROM(SELECT A.*,ROWNUM RNFROM(SELECT * FROM t_employee) AWHERE ROWNUM <=40)WHERE RN >= 21

• 1
• 2
• 3
• 4
• 5
• 6

2.MySQL数据库实现的分页,SQL语句如下所示:

select * from t_employee limit 20,20;

• 1

1.42.阅读以下说明,回答问题1至问题4:

某宾馆需要建立一个住房管理系统,部分的需求分析结果如下:

1.一个房间有多个床位,同一房间内的床位具有相同的收费标准;不同房间的床位收费标准可能不同.

2.每个房间有房间号(如201,202等),收费标准,床位数据等信息.

3.每位客人有身份证号码,姓名,性别,出生日期和地址等信息.

4.对每位客人的每次住宿,应该记录其入住日期,退房日期和预付款额信息.

5.管理系统可查询出客人所住房间号.

根据以上的需求分析结果,设计一种关系模型下图所示:

1.根据上述说明和实体-关系图,得到该住房管理系统的关系模式如下所示,请在下划线处补充住宿关系

房间(房间号,收费标准,床位数目)

客人(身份证号,姓名,性别,出生日期,地址)

住宿(_,_,入住日期,退房日期,预付款额)

2.请给出问题1中住宿关系的主键和外键.

3.若将上述各关系直接实现为对应的物理表,现需查询在2005年1月1日到2005年12月31日期间,在该宾馆住宿次数大于5次的客人身份证号,并且按照入住次数进行降序排列.下面是实现该功能的SQL语句,请填补语句中的空缺.

SELECT 住宿.身份证号,count(入住日期) FROM住宿,客人WHERE 入住日期>='20050101' AND 入住日期<='20051231'AND 住宿.身份证号 = 客人.身份证号GROUP BY ___(1)______(2)___count(入住日期)>5___(3)___;

• 1
• 2
• 3
• 4
• 5
• 6

4.为加快SQL语句的执行效率,可在相应的表上创建索引.根据问题3中的SQL语句,除主键和外键外,还需要在哪个表的哪些属性上创建索引,应该创建什么类型的索引,请说明原因.

参考答案:

题目中,各问题的参考答案如下:
1.房间号,身份证号.
2.房间表的房间号做主键,客人表的身份证号做主键,住宿表的房间号和身份证号做外键.
3.(1)处:住宿.身份证号
(2)处:having
(3)处:order by count(住宿.身份证号) desc
在住宿表的入住日期上创建非唯一性索引,用于提高查询效率.

1.43.假如有一个制造商生产若干种产品,并由不同的销售负责销售这款产品,产品和销售商之间是多对多的关系,数据库结构如下:

Table1:制造商制造的产品表

字段名称字段类型字段长度字段含义

ProductID	文本	6	产品编号(Primary Key)
ProductName	文本	20	产品名称
ProductMeno	文本	50	产品说明

Table2:销售商表

字段名称字段类型字段长度字段含义

SalesID	文本	4	销售商编号(Primary Key)
SalesName	文本	20	销售商名称
SalesPhone	文本	15	销售商联系电话

Table3:销售商销售的产品表

字段名称字段类型字段长度字段含义

SalesID	文本	4	销售商编号
ProductID	文本	6	产品编码

请用SQL语句实现以下功能:

1.查询所有负责销售编号为p00001的产品的销售商的编号,名称和联系电话;
2.查询各种产品的销售的数量;
3.查询编号为s001的销售商所不经销的产品的编码和名称.

参考答案:

在oracle数据库中,创建表和测试数据的SQL语句如下所示:

create table table1( productid varchar2(6) primary key, productname varchar2(20), productmemo varchar2(50));insert into table1 values('p00001','apple','香甜可口');insert into table1 values('p00002','pear','清心润肺');insert into table1 values('p00003','banana','富含营养');create table table2( salesid varchar2(4) primary key, salesname varchar2(20), salesphone varchar2(15));insert into table values('s001','marry','1234567890');insert into table values('s002','tom','1234509876');create table table3( salesid varchar2(4) references table2(salesid), productid varchar2(6) references table1(productid));insert into table3 values('s001','p00001');insert into table3 values('s001','p00002');insert into table3 values('s001','p00003');insert into table3 values('s002','p00001');insert into table3 values('s002','p00002');

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29

1.查询所有负责销售编号为p00001的产品和销售商的编号,名称和联系电话,SQL语句如下所示:

select t2.salesid,t2.salesid,t2.salesphonefrom table1 t1join table3 t3 on t1.productid=t3.productidjoin table2 t2 on t3.salesid=t2.salesidand t1.productid='p00001';

• 1
• 2
• 3
• 4
• 5

2.查询各种产品的销售的数量,SQL语句如下所示:

select t3.productid,count(t3.productid)from table1 t1join table3 t3 on t1.productid=t3.productidgroup by t3.productid;

• 1
• 2
• 3
• 4

3.查询编号为s001的销售商所不经销的产品的编码和名称,SQL语句如下所示:

select productid,productname from table1where productid not in(select t1.productidfrom table1 t1join table3 t3 on t1.productid=t3.productidjoin table2 t2 on t3.salesid=t2.salesidand t2.salesi='s001');

• 1
• 2
• 3
• 4
• 5
• 6

1.44.数据库编程

账户表

CustNoCustNameAccNoAccBalanceLastModAmtLastModDate

21890001	Huawuque	100001	1000	+500	20110201
21890001	Huawuque	100004	1500	-100	20110101
21890002	Xiaoyuer	100002	2000	+200	20110301
21890003	Zhangwuji	100003	3000	+1000	20110401
21890004	Zhouxingchi	100005	5000	-300	20101231

注:以客户号和卡号为联合主键.请实现以下功能:
1.现有一个客户新开户,客户号21890005,客户姓名刘帅,账号100006,同时该客户往账户中存了10元钱,请以此写一条insert语句;
2.假设客户huawuque今天用银行卡100004在ATM机上却走了500元,写一条update语句更新该卡的余额信息;
3.请写一条select语句选出客户号一样的账户信息,并算出该客户所有卡的积累余额;

参考答案:

在Oracle数据库中,创建表和测试数据的SQL语句如下所示:

create table customer( custno number(8), custname varchar2(20), accno number(6), accbalance number(10,2), lastmodamt varchar2(20), lastmoddate varchar2(8), primary key(custno,accno));insert into customer values(21890001,'Huawuque',100001,1000,'+500','20110201');insert into customer values(21890001,'Huawuque',100004,1500,'-100','20110101');insert into customer values(21890002,'Xiaoyuer',100002,2000,'+200','20110301');insert into customer values(21890003,'Zhangwuji',100003,3000,'+1000','20110401');insert into customer values(21890004,'Zhouxinghi',100005,5000,'-300','20101231');

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

1.现有一个客户新开户,客户号21890005,客户姓名刘帅,账号100006,同时该客户往账户中存了10元钱,请以此写一条insert语句;

insert into customer values(21890005,'刘帅',100006,10,0,to_char(sysdate,'yyyymmdd'));

• 1

2.假设客户huawuque今天用银行卡100004在ATM机上却走了500元,写一条update语句更新该卡的余额信息的SQL语句如下所示:

update customer set accbalance=accbalance-500,lastmodamt='-500',lastmoddate=to_char(sysdate,'yyyymdd')where custname'huawuque' and accno=100004;

• 1
• 2

3.选出客户号一样的账户信息,并算出该客户所有卡的积累余额的SQL语句如下所示:

select * from custmer c join(select custno,sum(accbalance) from customer group by custno having count(custno)>1) bon c.custno=b.custno;

• 1
• 2
• 3