Dz6

gistfile1.txt

1111111

SELECT p.name, AVG(s.price) as [средняя цена]
FROM Product p
JOIN Sale s ON p.id = s.id_product
GROUP BY p.name
HAVING AVG(s.price) > 50

22222

SELECT c.name, COUNT(p.id) as [количество продуктов]
FROM Category c
JOIN Product p ON c.id = p.id_category
JOIN Delivery d ON p.id = d.id_product
GROUP BY c.name
HAVING AVG(d.price) > 100

33333

SELECT c.name as [категория], p.name, SUM(s.price * s.quantity) as [общий объем продаж]
FROM Category c
JOIN Product p ON c.id = p.id_category
JOIN Sale s ON p.id = s.id_product
WHERE c.name IN ('Сладости') -- ('Фрукты') ('Конфеты')
GROUP BY c.name, p.name



44444

SELECT pr.name as [производитель], 
       a.street as[улица], 
       c.name as [город],
       COUNT(p.id) as [количество товара],
       SUM(s.price * s.quantity) as [общий объем продаж]
FROM Producer pr

JOIN Product p ON pr.id = p.id_producer
JOIN Sale s ON p.id = s.id_product
JOIN Address a ON pr.id_address = a.id
JOIN City c ON a.id_city = c.id


GROUP BY pr.name, a.street, c.name
HAVING SUM(s.price * s.quantity) BETWEEN 500 AND 2000;


555555

 SELECT c.name, COUNT(p.id) as [количество продуктов]
FROM Category c
LEFT JOIN Product p ON c.id = p.id_category
GROUP BY c.name

HAVING COUNT(p.id) =(SELECT MIN(count_pr)
FROM (

SELECT COUNT(id) as count_pr
FROM Product
GROUP BY id_category
    
	) as counts

)


66666

SELECT c.name as [категория], 
       COUNT(p.id) as [количество продуктов]

FROM Category c

JOIN Product p ON c.id = p.id_category
JOIN Delivery d ON p.id = d.id_product
JOIN Supplier s ON d.id_supplier = s.id

WHERE d.price > 400 AND s.id IN (
    SELECT TOP 3 id 
    FROM Supplier
)


GROUP BY c.name
HAVING COUNT(p.id) > 0;