HW_GroupBy

gistfile1.txt

1 
SELECT p.name AS Товар
FROM Sale s
INNER JOIN Product p ON s.id_product = p.id
GROUP BY p.name
HAVING AVG(s.price) > 50

2 
SELECT c.name AS Категория, COUNT(p.id) AS Количество_товаров, AVG(d.price) AS Средняя_цена_поставки
FROM Product p
INNER JOIN Category c ON p.id_category = c.id
INNER JOIN Delivery d ON p.id = d.id_product
GROUP BY c.name
HAVING AVG(d.price) > 100


3
SELECT c.name AS Категория, p.name AS Товар, SUM(s.quantity * s.price) AS Общая_сумма_продаж
FROM Sale s
INNER JOIN Product p ON s.id_product = p.id
INNER JOIN Category c ON p.id_category = c.id
WHERE c.name IN ('Фрукти', 'Сухофрукті й горішки')
GROUP BY c.name, p.name


4 
SELECT pr.name, (co.name + ', ' + ci.name + ', ' + a.street) AS Полный_адрес, COUNT(p.id) AS Количество,SUM(s.quantity * s.price) AS Общая_сумма_продаж
FROM Sale s
INNER JOIN Product p ON s.id_product = p.id
INNER JOIN Producer pr ON p.id_producer = pr.id
INNER JOIN Address a ON pr.id_address = a.id
INNER JOIN City ci ON a.id_city = ci.id
INNER JOIN Region r ON ci.id_region = r.id
INNER JOIN Country co ON r.id_country = co.id
GROUP BY pr.name, co.name, ci.name, a.street
HAVING SUM(s.quantity * s.price) BETWEEN 500 AND 2000

5
SELECT TOP 1 c.name AS Категория, COUNT(p.id) AS Количество_товаров
FROM Product p
INNER JOIN Category c ON p.id_category = c.id
GROUP BY c.name

6 
SELECT c.name AS Категория, COUNT(DISTINCT p.id) AS Количество_товаров
FROM Product p
INNER JOIN Category c ON p.id_category = c.id
INNER JOIN Delivery d ON p.id = d.id_product
INNER JOIN Supplier s ON d.id_supplier = s.id
WHERE d.price > 400
AND s.name IN ('Везе Тобі Все', 'Котигорошко', 'Біжубіжу')  
GROUP BY c.name