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
