南京理工大学《数据库系统基础教程》试题和答案

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

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

33

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

34

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

35

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

36