дз 4

gistfile1.txt

111111

SELECT DISTINCT  
		Product.name AS Продукт,
		Category.name AS Категория

FROM Product 
INNER JOIN Category ON Product.id_category = Category.id
INNER JOIN Producer ON Product.id_producer = Producer.id
INNER JOIN Delivery ON Product.id = Delivery.id_product
INNER JOIN Supplier ON Delivery.id_supplier = Supplier.id

WHERE Supplier.name IN ('ООО Люкс', 'ОПТ Товар')



222222


SELECT DISTINCT 
    Product.name AS 'Товар',
    Supplier.name AS 'Поставщик'
FROM Product 
    INNER JOIN Category ON Product.id_category = Category.id
    INNER JOIN Producer ON Product.id_producer = Producer.id
    INNER JOIN Delivery ON Product.id = Delivery.id_product
    INNER JOIN Supplier ON Delivery.id_supplier = Supplier.id
WHERE 
    Producer.name NOT LIKE '%AKM%' 
    AND Category.name != 'Крупы'
    
    
    
    
    3333333
    
    
    SELECT DISTINCT
    Product.name AS 'Товар',
    Category.name AS 'Категория',
    Supplier.name AS 'Поставщик',
    Country.name AS 'Страна производителя'
FROM Product
    INNER JOIN Category ON Product.id_category = Category.id
    INNER JOIN Producer ON Product.id_producer = Producer.id
    INNER JOIN Address ON Producer.id_address = Address.id
    INNER JOIN City ON Address.id_city = City.id
    INNER JOIN Region ON City.id_region = Region.id
    INNER JOIN Country ON Region.id_country = Country.id
    INNER JOIN Delivery ON Product.id = Delivery.id_product
    INNER JOIN Supplier ON Delivery.id_supplier = Supplier.id
WHERE 
    Country.name NOT IN ('Украина', 'Молдова', 'Польша')
    AND Delivery.price < 50
    AND Delivery.date_of_delivery >= '2025-02-10'
    
    
    
    444444
    
    SELECT DISTINCT
    Product.name AS 'Товар',
    Cateame AS 'Категория',
    Producer.name AS 'Производитель',
    Supplier.name AS 'Поставщик',
    Sale.quantity AS 'Количество продаж'
FROM Product
    INNER JOIN Category ON Product.id_category = Category.id
    INNER JOIN Producer ON Product.id_producer = Producer.id
    INNER JOIN Delivery ON Product.id = Delivery.id_product
    INNER JOIN Supplier ON Delivery.id_supplier = Supplier.id
    INNER JOIN Sale ON Product.id = Sale.id_product
WHERE 
    Category.name IN ('Сладости', 'Вода')
    AND Sale.quantity > 100gory.n
  
  
  
555555



SELECT DISTINCT
    Product.name AS 'Товар',
    Supplier.name AS 'Поставщик',
    Category.name AS 'Категория',
    Delivery.date_of_delivery AS 'Дата поставки',
    (Delivery.price * Delivery.quantity) AS 'Общая стоимость'
FROM Product
    INNER JOIN Category ON Product.id_category = Category.id
    INNER JOIN Delivery ON Product.id = Delivery.id_product
    INNER JOIN Supplier ON Delivery.id_supplier = Supplier.id
WHERE 
    Supplier.id IN (14, 15, 16)
ORDER BY 
    Product.name ASC
    
    
    
    6666666
    
    SELECT DISTINCT
    Product.name AS 'Товар',
    Producer.name AS 'Производитель',
    (Country.name + ', ' + City.name + ', ' + Address.street) AS 'Полный адрес',
    Category.name AS 'Категория',
    Sale.date_of_sale AS 'Дата продажи',
    (Sale.price * Sale.quantity) AS 'Общая стоимость'
FROM Product
    INNER JOIN Producer ON Product.id_producer = Producer.id
    INNER JOIN Address ON Producer.id_address = Address.id
    INNER JOIN City ON Address.id_city = City.id
    INNER JOIN Region ON City.id_region = Region.id
    INNER JOIN Country ON Region.id_country = Country.id
    INNER JOIN Category ON Product.id_category = Category.id
    INNER JOIN Sale ON Product.id = Sale.id_product
WHERE 
    Producer.id NOT IN (1, 2) 
ORDER BY 
    (Sale.price * Sale.quantity) DESC
    
    
    
    =======================================================
    1111111
    
    SELECT DISTINCT
    Product.name AS 'Товар',
    Producer.name AS 'Производитель'
FROM Product
    RIGHT OUTER JOIN Producer ON Product.id_producer = Producer.id
    
    
    2222222
    
    
SELECT DISTINCT
    Category.name AS 'Категория'
FROM Category
    LEFT OUTER JOIN Product ON Category.id = Product.id_category
WHERE 
    Product.id IS NULL


333333
SELECT DISTINCT
    Product.name AS 'Товар',
    Delivery.date_of_delivery AS 'Дата поставки',
    Supplier.name AS 'Поставщик'
FROM Product
    RIGHT OUTER JOIN Delivery ON Product.id = Delivery.id_product
    RIGHT OUTER JOIN Supplier ON Delivery.id_supplier = Supplier.id
    
    
    
    44444
SELECT Region.name AS 'Регионы без производителей'
FROM Region
EXCEPT
SELECT DISTINCT Region.name
FROM Region
    JOIN City ON Region.id = City.id_region
    JOIN Address ON City.id = Address.id_city
    JOIN Producer ON Address.id = Producer.id_address
    
    
    555555
    
    
    SELECT Category.name AS 'без ООО "Нил Ю"'
FROM Category
EXCEPT
SELECT DISTINCT Category.name
FROM Category
    JOIN Product ON Category.id = Product.id_category
    JOIN Producer ON Product.id_producer = Producer.id
WHERE Producer.name = 'ООО «Нил Ю»'




666666
SELECT Producer.name AS 'Произ без молочных продуктов'
FROM Producer
EXCEPT
SELECT DISTINCT Producer.name
FROM Producer
    JOIN Product ON Producer.id = Product.id_producer
    JOIN Category ON Product.id_category = Category.id
WHERE Category.name = 'Молочние Изделия';