16.05

1.

SELECT Product.name, Category.name, Supplier.name
FROM Product
JOIN Delivery ON Delivery.id = Product.id_producer
JOIN Supplier ON Supplier.id = Delivery.id_supplier
JOIN Category ON Category.id = Product.id_category
WHERE Supplier.name LIKE 'Супер закупка' OR Supplier.name LIKE 'Юж поставка'

2.

SELECT Product.name, Supplier.name, Producer.name
FROM Product
JOIN Delivery ON Delivery.id = Product.id_producer
JOIN Supplier ON Supplier.id = Delivery.id_supplier
JOIN Category ON Category.id = Product.id_category
JOIN Producer ON Producer.id = Product.id_producer
WHERE Category.name NOT LIKE 'Крупы' AND Producer.name NOT LIKE '%[АКМ]%'

3.

SELECT Product.name, Supplier.name, Producer.name, Country.name
FROM Product
JOIN Delivery ON Delivery.id = Product.id_producer
JOIN Supplier ON Supplier.id = Delivery.id_supplier
JOIN Category ON Category.id = Product.id_category
JOIN Producer ON Producer.id = Product.id_producer
JOIN Address ON Address.id = Producer.id_address
JOIN City ON City.id = Address.id_city
JOIN Region ON Region.id = City.id_region
JOIN Country ON Country.id = Region.id_country
WHERE Country.name NOT LIKE 'Ukraine' AND Country.name NOT LIKE 'Poland' AND Delivery.price < 50 AND Delivery.date_of_delivery BETWEEN '2023-02-10' AND GETDATE()


4.

SELECT Product.name, Supplier.name, Producer.name
FROM Product
JOIN Delivery ON Delivery.id = Product.id_producer
JOIN Supplier ON Supplier.id = Delivery.id_supplier
JOIN Category ON Category.id = Product.id_category
JOIN Producer ON Producer.id = Product.id_producer
JOIN Sale ON Sale.id_product = Product.id
WHERE Category.name LIKE 'Кондитерские' AND Category.name LIKE 'Безаклкогольные' AND Sale.quantity > 100

5.

SELECT Product.name, Supplier.name, Category.name, Sale.date_of_sale, Delivery.price * Delivery.quantity AS total_cost
FROM Product
JOIN Delivery ON Delivery.id = Product.id_producer
JOIN Supplier ON Supplier.id = Delivery.id_supplier
JOIN Category ON Category.id = Product.id_category
JOIN Sale ON Sale.id_product = Product.id
WHERE Supplier.name LIKE 'Юж поставка' OR Supplier.name LIKE 'Закуп универсал' OR Supplier.name LIKE 'Снабд торг'
ORDER BY Product.name ASC

6.

SELECT Product.name, Producer.name, Country.name + ' ' + Region.name + ' область, ' + ' город ' + City.name, Category.name, Sale.price * Sale.quantity AS 'total cost'
FROM Product
JOIN Producer ON Producer.id = Product.id_producer
JOIN Address ON Producer.id_address = Address.id
JOIN City ON Address.id_city = City.id
JOIN Region ON City.id_region = Region.id
JOIN Country ON Region.id_country = Country.id
JOIN Category ON Product.id_category = Category.id
JOIN Sale ON Product.id = Sale.id_product
WHERE Producer.name NOT IN ('Ферма', 'Ферма2')
ORDER BY 1 DESC