My homwork1

test1.sql

-- 1.
SELECT p.name AS [назва продукту], 
c.name AS [назва категорії],
s.name AS [постачальник]
FROM Product p 
JOIN Category c ON p.id_category = c.id
JOIN Delivery d ON p.id = d.id_product
JOIN Supplier s ON s.id = d.id_supplier
WHERE s.name IN ('ООО Самтаймс', 'Картофка')

-- 2.
SELECT p.name AS [назва продукту],
s.name AS [постачальник]
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Delivery d ON d.id_product = p.id
JOIN Supplier s On s.id = d.id_supplier
JOIN Producer pr ON pr.id = p.id_producer
WHERE c.name != 'Крупи'
AND pr.name NOT LIKE '%а%'
AND pr.name NOT LIKE '%л%'
AND pr.name NOT LIKE '%м%'

-- 3.
SELECT p.name AS [назва товару],
c.name AS [категорія],
s.name AS [постачальник],
cn.name AS [країна виробник]
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Delivery d ON d.id_product = p.id
JOIN Supplier s ON d.id_supplier = s.id
JOIN Producer pr ON p.id_producer = pr.id
JOIN Address a ON pr.id_address = a.id
JOIN City ct ON a.id_city = ct.id
JOIN Region r ON ct.id_region = r.id
JOIN Country cn ON r.id_country = cn.id
WHERE cn.name != 'Україна' AND cn.name != 'Молдова' AND cn.name != 'Польша'
OR d.date_of_delivery > '2025.02.10' AND d.date_of_delivery <= '2025.04.07'
OR d.price < 50

-- 4.
SELECT p.name AS [назва товару],
c.name,
s.name AS [постачальник],
pr.name AS [виробник]
FROM Product p 
JOIN Category c ON p.id_category = c.id
JOIN Delivery d ON d.id_product = p.id
JOIN Supplier s ON d.id_supplier = s.id
JOIN Producer pr ON p.id_producer = pr.id
WHERE c.name IN ('Овочі', 'Фрукти')
AND p.quantity > 500

-- 5.
SELECT p.name AS [],
s.name AS [],
c.name AS [категорія],
d.date_of_delivery AS [],
SUM(p.price * p.quantity) AS [загальна вартість]
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Delivery d On d.id_product = p.id
JOIN Supplier s ON d.id_supplier = s.id
WHERE s.name IN ('Смачний Стіл Знову', 'Золоті Колеса', 'Godfather Company')
GROUP BY 
  p.name,
  s.name,
  c.name,
  d.date_of_delivery
ORDER BY p.name;

-- 6.
SELECT p.name AS [назва продукту],
s.name AS [постачальник],
cn.name + ', ' +
ct.name + ', ' +
a.street [повна адреса виробника],
c.name AS [категорія],
d.date_of_delivery AS [дата поставки],
SUM(p.price * p.quantity) AS [загальна вартість]
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Delivery d ON d.id_product = p.id
JOIN Supplier s ON d.id_supplier = s.id
JOIN Producer pr ON p.id_producer = pr.id
JOIN Address a ON pr.id_address = a.id
JOIN City ct ON a.id_city = ct.id
JOIN Region r ON ct.id_region = r.id
JOIN Country cn ON r.id_country = cn.id
WHERE p.name NOT IN ('Дід Тарас', 'Моя Ферма')
GROUP BY
p.name, 
s.name, 
cn.name + ', ' +
ct.name + ', ' +
a.street, 
c.name,
d.date_of_delivery,
(p.price * p.quantity)
ORDER BY SUM(p.price * p.quantity) DESC