卓越考研

南京理工大学《数据库系统基础教程》试题和答案（四），卓越考研特整理南京理工大学考研真题，为广大考生提供有效的信息支持。

应用设计40% 
为某图书发行公司开发一个会员制网上图书订购数据库系统，该系统的基本需求如下： 1． 该公司分为多个下属部门(Department)，如“社科部”，“文艺部”，“计算机部”等。每个部门有若干销售员(Salesman)，每个销售员有唯一的员工号(如“S0025”)和身份证号，也包含性别、出生日期、电话等信息。一个销售员仅属于一个部门，且每个部门有一个销售员担任部门经理。部门和人员可以动态调整。 
2． 管理注册会员(Member)：身份证号、姓名、性别、地址、邮编、电话等。可注册新会员(只有会员才能参与订购图书)，也可注销旧会员。 
3． 管理所有销售的图书(Book)：书名(如“数据库系统基础教程”等)、种类(如“计算机”等)、书号ISBN (如“7-302-03646-2等)、作者(也包括译者，如“史嘉权”)、出版社(如“清华大学出版社”)、定价等。 
4． 一个销售订单(Salesorder)表示一笔销售业务。每个订单有一个订单编号和签订日期，且对应一个会员和一个销售员(作为订单经办人)。一个订单至少销售一种图书，每种图书应确定其销售数量和销售价格(销售价格通常低于定价)，以计算销售金额。 按次序回答下列问题： 
4． 用若干E/R图建立该系统的数据库模型。为每个实体集确定其键属性，并确定每个联系的多重性。注意弱实体集的表示。(8) 
5． 由E/R模型建立该系统的关系模式，并确定每个关系的主键和可能的外键。注意各关系中的函数依赖和多值依赖，并使你的关系能符合更高范式。(8) 6． 基于以上关系模式，用关系代数表示下面计算：(8) 1) 计算名为“张军”的销售员的员工号及其电话号码。 2) 计算所有重名的销售员的姓名及其员工号。 
3) 计算销售过清华大学出版社出版的计算机类图书的所有销售员的员工号及姓名。 4) 未销售过计算机类图书的销售员的姓名及其员工号。
4．使用SQL语言实现上述计算，并使输出结果不重复。(8) 5．使用SQL语言完成下面的计算：(8) 
1) 计算2001年12月签订的所有订单的订单号、签单日期、销售数量和销售金额，
并按销售金额从小到大排列。 
2) 计算2001年12月签订的销售金额最大的订单号、销售员姓名和销售金额。(注意
利用已有查询定义视图) 
3) 计算2001年12月所有销售员的员工号、姓名及其销售金额。(注意使用外连接，
没有订单的销售员的销售金额应为0) 
求员工号为“S0025”的销售员的部门经理的员工号及其姓名 
product(maker,model,type) pc(model,speed,ram,hd,cd,price) laptop(model,speed,ram,hd,screen,price) printer(model, color,type,price)  
p187 4.1.1 
1. What PC models have a speed of at least 150?
P253 5.1.3 
a) Find the model number, speed, and hard-disk size for all PC’s whose price is under $1600. select model,speed,hd from pc 
where price<1600; 
b) Do the same as (a), but rename the speed column megahertz and the hd column gigabytes. select model,speed as megahertz, hd as gigabytes from pc where price<1600; c) Find the manufacturers of printers. select maker from product where type=’printer’; 
d) Find the model number, memory size, and screen size for laptops costing more than $2000. select model,ram,screen from laptop where price>=2000; 
e) Find all the tuples in the Printer relation for color printers. select * from printer where color; 
f) Find the model number, speed, and hard-disk size for those PC’s that have either a 6x or 8x CD and a 
price less than $2000. select model,speed,hd from pc 
where price<2000 and (cd=’6x’ or cd=’8x’);  
p262 5.2.2 
a) Give the manufacturer and speed of laptops with a hard disk of at least one gigabyte? Select laptop.model,maker,speed From laptop, product 
Where laptop.model=product.model and hd>1; 
b) Find the model number and price of all products (of any type) made by manufacturer B. (Select product.model,price,’pc’ as type1 From product,pc 
Where maker=’B’ and product.model=pc.model ) Union 
(Select product.model,price,’laptop’  From product,laptop 
Where maker=’B’ and product.model=laptop.model ) Union 
(Select product.model,price,’printer’  From product,printer 
Where maker=’B’ and product.model=printer.model ) c) Find those manufacturers that sell Laptops, but not PC’s. Select maker  From product,laptop 
Where product.model=laptop.model and maker not in ( 
Select maker From product,pc Where product.model=pc.model); 
d) Find those hard-disk sizes that occur in two or more PC’s. Select p1.hd 
From pc as p1,pc as p2 
Where p1.model<>p2.model and p1.hd=p2.hd; 
e) Find those pairs of PC models that have both the same speed and RAM. Select p1.model,p2.model From pc as p1,pc as p2 
Where p1.model<p2.model and p1.speed=p2.speed and p1.ram=p2.ram; 
f) Find those manufacturers of at least two different computers (PC’s or laptops) with speeds of at least 133. 
Select maker From product,pc 
Where speed>133 and product.model=pc.model and maker IN (Select maker From product,laptop 
Where speed>133 and product.model=laptop.model );  
P269 5.3.1 
a) Find the makers of PC’s with a speed of at least 160. Select maker From product 
Where model in (select model from pc where speed >=160);  
Select maker From product 
Where model =any (select model from pc where speed >=160); 
Select maker From product 
Where exists (select * from pc where speed >=160 and model=product.model); b) Find the printer with the highest price. Select model From printer 
Where price >= all (Select price from printer);  
Select model From printer 
Where not price < any (Select price from printer);  
Select model From printer as p 
Where not exists (select * from printer where price>p.price); c) Find the laptop whose speed is slower than that of any pc. Select model From laptop 
Where speed < all (Select speed From pc);  
Select model From laptop 
Where not exists (Select * From pc where speed<laptop.speed); d) Find the model number of the item with the highest price. (Select model From pc 
Where price>=all (select price from pc) and          price>=all (select price from laptop) and         price>=all (select price from printer)) union 
(Select model From laptop 
Where price>=all (select price from pc) and        price>=all (select price from laptop) and       price>=all (select price from printer)) union 
(Select model From printer 
Where price>=all (select price from pc) and          price>=all (select price from laptop) and         price>=all (select price from printer)); 
e) Find the maker of the color printer with the lowest price. Select maker From product 
Where model in (select model from printer where color  
and price <= all (Select price From printer 
 
 
                       Where color)); 
f) Find the maker of the PC with the fastest processor among all those PC’s that have the smallest amount of RAM. Select maker From product, pc 
Where product.model=pc.model and ram<=all (Select ram From pc)  
and speed >= all ( Select speed From pc  
where ram<=all (Select ram From pc));
p278 5.5.1 
a) Find the average speed of PC’s. select avg(speed) from pc; 
b) Find the average speed of laptops costing over $2500. select avg(speed) from laptop where price>2500; 
c) Find the average price of PC’s made by manufacturer “A”. select avg(price) from product, pc 
where product.model=pc.model and maker=’A’; 
d) Find the average price of PC’s and laptops made by manufacturer “D”.   select 
(sum(pc.price)/count(distinct 
laptop.model)+ 
sum(laptop.price)/count(distinct 
pc.model))/(count(distinct laptop.model)+count(distinct pc.model))   from pc, laptop 
  where pc.model in (select model from product where maker=’D’)         and laptop.model in (select model from product where maker=’D’);  
create view pd as 
((select product.model, price from product, pc 
where product.model=pc.model and maker=’D’) union 
(select product.model, price from product, laptop 
where product.model=laptop.model and maker=’D’));
select avg(price) from pd; 
e) Find, for each different speed the average price of PCs. select speed, avg(price) from pc group by speed; 
f) Find for each manufacturer, the average screen size of its laptops. select maker, avg(screen) from product, laptop 
where product.model= laptop..model group by maker; 
g) Find the manufacturers that make at least three different models of PC. select maker, count(distinct model) as num from product where type=’pc’ group by maker having num>=3; 
h) Find for each manufacturer the maximum price of PCs. select maker, max(price) from product, pc 
where product.model= pc.model group by maker; 
i) Find for each speed of PC above 150 the avera