Homwork

test2.sql

-- 1. Товари та виробники.
SELECT p.name AS [назва товару]],
pr.name AS [виробник]
FROM Product p 
RIGHT OUTER JOIN Producer pr ON p.id_producer = pr.id

-- 2. Категорії без товару.
SELECT name
FROM Category
EXCEPT
SELECT DISTINCT c.name AS [категорії]
FROM Product p 
JOIN Category c ON p.id_category = c.id

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

-- 4. Регіони без виробника.
SELECT name AS [Регіон]
FROM Region 
EXCEPT
SELECT DISTINCT r.name
FROM Product p
JOIN Producer pr ON p.id_producer = pr.id
JOIN Address a ON pr.id_address = a.id
JOIN City c ON a.id_city = c.id
JOIN Region r ON c.id_region = r.id

-- 5. Категорії без фірми ''.
SELECT name AS [Ктегорії]
FROM Category
EXCEPT
SELECT c.name
FROM Category c 
JOIN Product p ON c.id = p.id_category
JOIN Producer pr ON pr.id = p.id_producer
WHERE pr.name = 'Зелений Врожай'

-- 6.Виробники без молочних продуктів.
SELECT name
FROM Producer
EXCEPT
SELECT pr.name
FROM Product p 
JOIN Category c ON c.id = p.id_category
JOIN Producer pr ON pr.id =p.id_producer
WHERE c.name = 'Молочна продукція'