--zad1
CREATE PROCEDURE [Pomiedzy_datami] @data1 DATETIME, @data2 DATETIME
AS
SELECT *
  FROM Orders
  WHERE
  Orders.OrderDate > @data1 AND
  Orders.OrderDate < @data2

EXEC Pomiedzy_datami '1996.07.03', '1996-08-05'
--zad2
CREATE PROCEDURE ZAD2 @fraza nvarchar(40)
AS
SELECT OrderID, b.CustomerID, CompanyName, ContactName
    FROM Orders a INNER JOIN Customers b ON a.CustomerID=b.CustomerID
    WHERE b.CompanyName LIKE '%'+@fraza+'%' OR b.ContactName LIKE '%'+@fraza+'%'

EXEC ZAD2 'Alfred'

--zad3
DROP TRIGGER printer
CREATE TRIGGER printer ON Products AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @name VARCHAR(30), @name2 VARCHAR(30)
    SELECT TOP 1 @name=b.CompanyName, @name2=a.ProductName FROM Products a INNER JOIN Suppliers b ON a.SupplierID=b.SupplierID
    ORDER BY a.ProductID DESC
    PRINT 'DOSTAWCA:   ' + @name +'   PRODUKT:   '+ @name2
END

INSERT [dbo].[Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES ('ASS', 1, 1, N'10 boxes x 20 bags', 18.0000, 39, 0, 10, 0)

--zad4
CREATE PROCEDURE rabat @Wartosc INT, @Rabat REAL
AS
    UPDATE [ORDER Details]
    SET Discount = @Rabat
    WHERE Quantity*UnitPrice > @Wartosc



--zad5
ALTER TABLE Orders ADD LastModified DATETIME

CREATE TRIGGER zmiana ON [ORDER Details] AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    UPDATE Orders
    SET Orders.LastModified = GETDATE()
    WHERE Orders.OrderID IN (SELECT OrderID FROM inserted) OR Orders.OrderID IN (SELECT OrderID FROM deleted)
END

--test nie działa
UPDATE Orders
SET OrderDate = 0
WHERE ShipCity='Lyon'


--zad6

CREATE TRIGGER towary ON Customers AFTER INSERT
AS
    SELECT * FROM Products a JOIN Suppliers b ON a.SupplierID = b.SupplierID
    WHERE b.Country IN (SELECT Country FROM inserted)