DZ_BAZA_JOINS

--Inner joins

--№2
--SELECT p.name AS ProductName, c.name AS CategoryName, s.name AS SupplierName
--FROM Product p
--INNER JOIN Supplier s ON p.id_producer = s.id
--INNER JOIN Category c ON p.id_category = c.id
--WHERE s.name NOT LIKE '%[АКМ]%'
--AND c.name <> 'Крупы';

--№4
--SELECT 
--    p.name AS ProductName, 
--    c.name AS CategoryName, 
--    s.name AS SupplierName, 
--    pr.name AS ProducerName, 
--    SUM(d.quantity) AS TotalDelivered
--FROM Product p
--INNER JOIN Category c ON p.id_category = c.id
--INNER JOIN Supplier s ON p.id_producer = s.id
--INNER JOIN Producer pr ON p.id_producer = pr.id
--INNER JOIN Delivery d ON p.id = d.id_product
--WHERE c.name IN ('Кондитерские', 'Безалкогольные')
--GROUP BY p.name, c.name, s.name, pr.name
--HAVING SUM(d.quantity) > 100;

--№1
--SELECT p.name AS ProductName, c.name AS CategoryName
--FROM Product p
--INNER JOIN Supplier s ON p.id_producer = s.id
--INNER JOIN Category c ON p.id_category = c.id
--WHERE s.name IN ('000 "Паньки"', 'ООО «Какие люди»');

-- Outer joins

--№1
--SELECT 
--    p.name AS ProductName, 
--    pr.name AS ProducerName
--FROM Product p
--RIGHT OUTER JOIN Producer pr ON p.id_producer = pr.id;

--№2
--SELECT 
--    c.name AS CategoryName
--FROM Category c
--LEFT OUTER JOIN Product p ON c.name = p.name
--WHERE p.id IS NULL;

--№3
SELECT 
    p.name AS ProductName, 
    d.date_of_delivery AS DeliveryDate, 
    s.name AS SupplierName
FROM Product p
LEFT OUTER JOIN Delivery d ON p.id = d.id_product
LEFT OUTER JOIN Supplier s ON d.id_supplier = s.id;