Question

Consider the following database schema:

               Product(maker, model, type)

               PC(model, speed, ram, hd, rd, price)

               Laptop(model, speed, ram, hd, screen, price)

               Printer(model, color, type, price)

Give SQL statement for each of the following:

(Grouping and Aggregation) Write the following queries in SQL:

1. Find the average speed of laptops costing over $2000.
2. Find the average price of PC’s and laptops made by manufacturer “D”.
3. Find, for each manufacturer, the average screen size of its laptops.
4. Find the manufacturers that make at least three different models of PC.
5. Find, for each manufacturer who sells PC’s, the maximum price of a PC.
6. Find, for each speed of PC above 800, the average price.
7. Find the average hard-disk size of a PC for all those manufacturers that make printers.

Answer 1

1a)

select avg(speed)
from laptop
where price::numeric > 2000;

2a)

select avg(A.price::decimal)
from
   (( select pc.price
       from pc, product
       where pc.model = product.model and product.maker = 'D'
      )
   UNION
   ( select laptop.price
       from laptop, product
       where laptop.model = product.model and product.maker = 'D'))
as A;

3a)

select product.maker, avg(laptop.screen)
from laptop, product
where product.model = laptop.model
group by product.maker;

4a)

select product.maker
from pc,product
where pc.model = product.model
group by maker
having count(maker) >= 3;

5a)

select product.maker
from pc,product
where pc.model = product.model
and pc.price >= ALL(select pc.price from pc);

6a)

select speed, avg(pc.price::decimal)
from pc
where speed > 2.0
group by speed;

7a)

select avg(B.hd)
from (
   (select product.maker
   from printer, product
   where printer.model = product.model)
INTERSECT
   (select product.maker
   from pc,product
   where pc.model = product.model)) as A,
   (select product.maker, pc.hd
   from product, pc
   where product.model = pc.model) as B
where A.maker = B.maker;