Вывести: maker, средний размер HD.
SELECT product.maker, AVG(pc.hd)
FROM pc, product WHERE product.model = pc.model
AND product.maker IN ( SELECT DISTINCT maker
FROM product
WHERE product.type = 'printer')
GROUP BY maker
Вывести: одна общая средняя цена.
SELECT sum(s.price)/sum(s.kol) as sredn FROM
(SELECT price,1 as kol FROM pc,product
WHERE pc.model=product.model AND product.maker='A'
UNION all
SELECT price,1 as kol FROM laptop,product
WHERE laptop.model=product.model AND product.maker='A') as s
Найдите производителей принтеров, которые производят ПК с наименьшим объемом RAM и с самым быстрым процессором среди всех ПК, имеющих наименьший объем RAM. Вывести: Maker
SELECT DISTINCT maker
FROM product
WHERE model IN (
SELECT model
FROM pc
WHERE ram = (
SELECT MIN(ram)
FROM pc
)
AND speed = (
SELECT MAX(speed)
FROM pc
WHERE ram = (
SELECT MIN(ram)
FROM pc
)
)
)
AND
maker IN (
SELECT maker
FROM product
WHERE type='printer'
)
SELECT model
FROM (
SELECT model, price
FROM pc
UNION
SELECT model, price
FROM Laptop
UNION
SELECT model, price
FROM Printer
) t1
WHERE price = (
SELECT MAX(price)
FROM (
SELECT price
FROM pc
UNION
SELECT price
FROM Laptop
UNION
SELECT price
FROM Printer
) t2
)
Вывести: Maker
SELECT DISTINCT maker
FROM product t1 JOIN pc t2 ON t1.model=t2.model
WHERE speed>=750 AND maker IN
( SELECT maker
FROM product t1 JOIN laptop t2 ON t1.model=t2.model
WHERE speed>=750 )
Вывести: speed, средняя цена.
SELECT pc.speed, AVG(pc.price)
FROM pc
WHERE pc.speed > 600
GROUP BY pc.speed
Вывести: maker, максимальная цена.
SELECT product.maker, MAX(pc.price)
FROM product, pc
WHERE product.model = pc.model
GROUP BY product.maker
Найдите производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей ПК.
SELECT maker, COUNT(model)
FROM product
WHERE type = 'pc'
GROUP BY product.maker
HAVING COUNT (DISTINCT model) >= 3
Для каждого производителя, имеющего модели в таблице Laptop, найдите средний размер экрана выпускаемых им ПК-блокнотов. Вывести: maker, средний размер экрана.
SELECT
product.maker, AVG(screen)
FROM laptop
LEFT JOIN product ON product.model = laptop.model
GROUP BY product.maker
SELECT DISTINCT product.maker, printer.price
FROM product, printer
WHERE product.model = printer.model
AND printer.color = 'y'
AND printer.price = (
SELECT MIN(price) FROM printer
WHERE printer.color = 'y'
)