Week 17 Homework in IT STEP Academy (OUTER JOIN, EXCEPT)

Week 17 Homework.sql

USE Store
-- https://gist.github.com/sunmeat/59dc33337af869024a7b18602b556b00
-- Бажано побудувати діаграму бази даних за посиланням.

-- 1. Показати назви товарів та їх виробників, а також тих виробників, 
-- у яких немає товарів.

SELECT p.name [Товар], vyr.name [Виробник]
FROM Product p
RIGHT JOIN Producer vyr ON p.id_producer = vyr.id
ORDER BY p.name, vyr.name;


-- 2. Показати лише ті категорії, до яких не належить жоден товар.

SELECT c.id [id категорії], c.name [Категорія]
FROM Product p
RIGHT JOIN Category c ON p.id_category = c.id
WHERE p.id_category IS NULL;


-- 3. Показати назви товарів, дати їх поставки та постачальників, 
-- в тому числі тих постачальників, які нічого не встигли поставити.

SELECT 
    p.name [Товар],
    d.date_of_delivery [Дата поставки],
    s.name [Постачальник]
FROM Supplier s
LEFT JOIN Delivery d ON s.id = d.id_supplier
LEFT JOIN Product p ON d.id_product = p.id;


-- 4. Показати області (регіони), в яких немає жодного виробника (EXCEPT).

SELECT r.id [id області], r.name [Область]
FROM Region r
EXCEPT
SELECT r.id [id області], r.name [Область]
FROM Region r
JOIN City ct ON r.id = ct.id_region
JOIN Address a ON ct.id = a.id_city
JOIN Producer vyr ON a.id = vyr.id_address;

SELECT 
    r.id [id області],
    r.name [Область], 
    ct.name AS [Перевірка міста], 
    a.street AS [Перевірка вулиці],
    vyr.name AS [Перевірка виробника]
FROM Region r
LEFT JOIN City ct ON ct.id_region = r.id
LEFT JOIN Address a ON a.id_city = ct.id
LEFT JOIN Producer vyr ON vyr.id_address = a.id
WHERE vyr.id IS NULL;

-- Є різниця між результатами запитів.
-- Другий запит показує випадки, де виробника немає на певній адресі:
-- 8 штат Маніпур Бішнупур Маллабар Марг, 5 NULL
-- 13 Брестська область Пінск Совєтська, 52 NULL

-- Дванадцяте квітня
-- Класна робота

SELECT r.id [id області], r.name [Область]
FROM Region r
LEFT JOIN City ct ON ct.id_region = r.id
LEFT JOIN Address a ON a.id_city = ct.id
LEFT JOIN Producer vyr ON vyr.id_address = a.id
GROUP BY r.id, r.name
HAVING COUNT(vyr.id) = 0;

-- Результати першого і третього запитів однакові.


-- 5. Показати назви категорій, де немає товарів фірми ООО «Нлі Ю» (EXCEPT).

SELECT c.name [Категорія]
FROM Category c
EXCEPT
SELECT c.name [Категорія]
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Producer vyr ON p.id_producer = vyr.id
WHERE vyr.name = 'Райдуга Овочів';


-- 6. Показати виробників, які не випускають продукти молочної категорії (EXCEPT).

SELECT vyr.name [Виробник]
FROM Producer vyr
EXCEPT
SELECT vyr.name [Виробник]
FROM Product p
JOIN Producer vyr ON p.id_producer = vyr.id
JOIN Category c ON p.id_category = c.id
-- WHERE c.name = 'Молочна продукція';
WHERE c.name IN ('Молочна продукція', 'Масло');

SELECT c.name [Категорія], vyr.name [Виробник]
FROM Category c
LEFT JOIN Product p ON p.id_category = c.id
LEFT JOIN Producer vyr ON p.id_producer = vyr.id
ORDER BY c.name, vyr.name;

-- "Молочник" виробляє консерви, 
-- "Сирзавод Смачно" та "Сирний Рай" – напої,
-- "Сумний Молочник" виробляє(?) овочі для прикриття?
-- "Солодкий врожай", "Золота Бджілка", "Здорові Горішки",
-- квас "Дід Тарас"...