gistfile1.txt

SELECT p.name, AVG(s.price)
FROM Product p
JOIN Sale s ON p.id = s.id_product
GROUP BY p.name
HAVING AVG(s.price) > 100



SELECT c.name, p.name, SUM(s.price)
FROM Category c
JOIN Product p ON c.id = p.id_category
JOIN Sale s ON p.id = s.id_product
GROUP BY c.name, p.name
HAVING c.name IN ('Фрукти', 'Цукерки')


SELECT pr.name, a.street, COUNT(p.id), SUM(s.price * s.quantity)
FROM Producer pr
JOIN Address a ON pr.id_address = a.id
JOIN Product p ON pr.id = p.id_producer
JOIN Sale s ON p.id = s.id_product
GROUP BY pr.name, a.street
HAVING SUM(s.price * s.quantity) BETWEEN 500 AND 2000;


SELECT c.name, SUM(p.quantity)
FROM Category c
JOIN Product p ON c.id = p.id_category
GROUP BY c.name
ORDER BY SUM(p.quantity)


SELECT c.name, COUNT(p.id)
FROM Category c
JOIN Product p ON c.id = p.id_category
JOIN Delivery d ON p.id = d.id_product
WHERE d.id_supplier IN (1, 2, 3)
GROUP BY c.name
HAVING AVG(d.price) > 400