dz4

gistfile1.txt

-- 1
SELECT p.name AS Продукт,
    c.name AS Категория,
    pr.name AS Производитель
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Producer pr ON p.id_producer = pr.id
WHERE pr.name = 'Bavaria Milk' OR pr.name = 'Sakura Foods'

-- 2
SELECT p.name AS Продукт,
    c.name AS Категория,
    pr.name AS Производитель,
    s.name AS Постовщик
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Producer pr ON p.id_producer = pr.id
JOIN Delivery d ON d.id_product = p.id
JOIN Supplier s ON d.id_supplier = s.id
WHERE s.name NOT LIKE '%AKM%' AND NOT c.name = 'Snacks'

-- 3
SELECT p.name AS Продукт,
    c.name AS Категория,
    s.name AS Постовщик,
    co.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 ci ON a.id_city = ci.id
JOIN Region r ON ci.id_region = r.id
JOIN Country co ON r.id_country = co.id

WHERE NOT co.name = 'Ukraine'
AND NOT co.name = 'Moldova'
AND NOT co.name = 'Poland'
AND d.price < 50
AND d.date_of_delivery BETWEEN '10-02-2025' AND GETDATE()

-- 4
SELECT p.name AS Продукт,
    c.name AS Категория,
    pr.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 d.id_supplier = s.id
JOIN Producer pr ON p.id_producer = pr.id
JOIN Sale sa ON sa.id_product = p.id

WHERE c.name = 'Beverages' OR c.name = 'Dairy' AND sa.quantity > 100

-- 5
SELECT p.name AS Продукт,
    s.name AS Постовщик,
    c.name AS Категория,
    d.date_of_delivery AS [Дата поставки],
    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 = 'French Delicacies'
OR s.name = 'Tokyo Imports'
OR s.name = 'Guangzhou Trade'
ORDER BY p.name

-- 6
SELECT p.name AS Продукт,
    pr.name AS Производитель,
    c.name AS Категория,
    CONCAT(co.name, ', ', ci.name, ', ', r.name, ', ', a.street) AS Адрес,
    se.date_of_sale AS [Дата продажи],
    se.price * se.quantity AS [Общая стоимость]
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Producer pr ON p.id_producer = pr.id
JOIN Address a ON pr.id_address = a.id
JOIN City ci ON a.id_city = ci.id
JOIN Region r ON ci.id_region = r.id
JOIN Country co ON r.id_country = co.id
JOIN Sale se ON se.id_product = p.id
ORDER BY se.price * se.quantity DESC

==================================================

-- 1
SELECT p.name AS Продукт,
    pr.name AS Производитель
FROM Product p
LEFT JOIN Producer pr ON p.id_producer = pr.id

-- 2
SELECT p.name AS Продукт,
    c.name AS Категория
FROM Product p
LEFT JOIN Category c ON p.id_category = c.id
WHERE c.id IS NULL

-- 3
SELECT p.name AS Продукт,
    s.name AS Поставщик,
    s.date_of_delivery AS [Дата поставки]
FROM Supplier s
LEFT JOIN Delivery d ON s.id_supplier = s.id
LEFT JOIN Product p ON d.id_product = p.id

-- 4
SELECT r.name AS Регион
FROM Region r
EXEPT
SELECT DISTINCT r.name
FROM Region r
JOIN City ci ON r.id = ci.id_region
JOIN Address a ON ci.id = a.id_city
JOIN Producer p ON a.id = p.id_address

-- 5
SELECT c.name AS Категория
FROM Category c
ECXEPT
SELECT DISTINCT c.name
FROM Category c
JOIN Product p ON c.id = p.id_category
JOIN Producer pr ON p.id_producer = pr.id
WHERE pr.name = 'firm'

-- 6
SELECT p.name
FROM Producer pr
EXEPT
SELECT pr.name
FROM Producer pr
JOIN Product p ON p.id_producer = pr.id
JOIN Category c ON p.id_category = c.id
WHERE c.name = 'Milk'