1.41.分别写出Oracle数据库和MySQL数据库对t_employee表分页的SQL语句,要求每页输出20条
参考答案:
1.Oracle数据库实现的分页,SQL语句如下所示:
SELECT * FROM(
SELECT A.*,ROWNUM RN
FROM(SELECT * FROM t_employee) A
WHERE ROWNUM <=40
)
WHERE RN >= 21
2.MySQL数据库实现的分页,SQL语句如下所示:
select * from t_employee limit 20,20;
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)___;
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.查询所有负责销售编号为p00001的产品和销售商的编号,名称和联系电话,SQL语句如下所示:
select t2.salesid,t2.salesid,t2.salesphone
from table1 t1
join table3 t3 on t1.productid=t3.productid
join table2 t2 on t3.salesid=t2.salesid
and t1.productid='p00001';
2.查询各种产品的销售的数量,SQL语句如下所示:
select t3.productid,count(t3.productid)
from table1 t1
join table3 t3 on t1.productid=t3.productid
group by t3.productid;
3.查询编号为s001的销售商所不经销的产品的编码和名称,SQL语句如下所示:
select productid,productname from table1
where productid not in(select t1.productid
from table1 t1
join table3 t3 on t1.productid=t3.productid
join table2 t2 on t3.salesid=t2.salesid
and t2.salesi='s001');
1.44.数据库编程
账户表
CustNo | CustName | AccNo | AccBalance | LastModAmt | LastModDate |
---|---|---|---|---|---|
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.现有一个客户新开户,客户号21890005,客户姓名刘帅,账号100006,同时该客户往账户中存了10元钱,请以此写一条insert语句;
insert into customer values(21890005,'刘帅',100006,10,0,to_char(sysdate,'yyyymmdd'));
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;
3.选出客户号一样的账户信息,并算出该客户所有卡的积累余额的SQL语句如下所示:
select * from custmer c join
(select custno,sum(accbalance) from customer group by custno having count(custno)>1) b
on c.custno=b.custno;