kocham pastebina blagam o plagiat

--Podpunkt a
--SELECT *
--FROM Products p
--WHERE p.ProductName LIKE '%o%'
--AND EXISTS(
--  SELECT * FROM Orders o
--  JOIN [Order Details] od ON o.OrderID = od.OrderID
--  WHERE o.ShipCountry='Canada' AND od.ProductID = p.ProductID)
--AND NOT EXISTS(
--  SELECT * FROM Orders o
--  JOIN [Order Details] od ON o.OrderID = od.OrderID
--  WHERE o.ShipCountry='Germany' AND od.ProductID = p.ProductID);

----Podpunkt b
--SELECT * FROM(
--  SELECT e.LastName, SUM(CASE WHEN p.ProductName = 'Chang'  THEN od.Quantity ELSE 0 END) AS ChangQuantity
--  FROM Employees e
--  JOIN Orders o ON o.EmployeeID = e.EmployeeID
--  JOIN [Order Details] od on o.OrderID = od.OrderID
--  JOIN Products p on od.ProductID = p.ProductID
--  GROUP BY e.LastName) innertable
--WHERE ChangQuantity > 10;

--Podpunkt c
--SELECT TOP 3 * FROM(
--  SELECT
--      e.EmployeeID,
--      e.BirthDate AS Born,
--      COUNT(o.OrderID) AS OrdersSupervised,
--      SUM(CASE WHEN o.ShipCity = 'Sao Paulo' THEN 1 ELSE 0 END) AS SentToSaoPaulo
--  FROM Employees e
--  JOIN Orders o ON e.EmployeeID = o.EmployeeID
--  GROUP BY e.EmployeeID, e.BirthDate) countedsupervised
--WHERE OrdersSupervised > 3 AND SentToSaoPaulo = 0
--ORDER BY Born DESC;

--Podpunkt d
--SELECT * FROM(
--  SELECT
--      c.CategoryName,
--      SUM(CASE WHEN (o.OrderDate > '1997/06/01' AND o.OrderDate < '1997/12/31') THEN 1 ELSE 0 END) AS SecondHalf1997,
--      SUM(CASE WHEN o.OrderDate > '1998/01/01' AND o.OrderDate < '1998/06/01' THEN 1 ELSE 0 END) AS FirstHalf1998
--  FROM Products p
--  JOIN Categories c ON c.CategoryID = p.CategoryID
--  JOIN [Order Details] od ON od.ProductID = p.ProductID
--  JOIN Orders o ON o.OrderID = od.OrderID
--  GROUP BY c.CategoryName) innertable
--WHERE SecondHalf1997 > FirstHalf1998;

--Podpunkt e
--DECLARE @tmptable TABLE (CompanyName char(50), ProductName char(50), Ordered integer);
--INSERT INTO @tmptable (CompanyName, ProductName, Ordered)
--SELECT
--  c.CompanyName AS CompanyName,
--  p.ProductName AS ProductName,
--  SUM(od.Quantity) AS Ordered
--FROM Customers c
--JOIN Orders o ON c.CustomerID = o.CustomerID
--JOIN [Order Details] od ON od.OrderID = o.OrderID
--JOIN Products p ON p.ProductID = od.ProductID
--GROUP BY c.CompanyName, p.ProductName;

--DECLARE @mintable TABLE (CompanyName char(50), Minimum integer);
--INSERT INTO @mintable (CompanyName, Minimum)
--SELECT
--  CompanyName,
--  MIN(Ordered)
--FROM @tmptable
--GROUP BY CompanyName;


--SELECT
--  t1.CompanyName,
--  t1.ProductName
--FROM @tmptable t1
--JOIN @mintable t2 on t1.CompanyName = t2.CompanyName
--WHERE t1.Ordered = t2.Minimum
--ORDER BY t1.CompanyName;


------------tego nawet nie testuj ziomeczku bo nie dziala
------------SELECT
------------    c.CompanyName,
------------    sumQuery.ProductName
------------FROM
------------Customers c,
------------    (SELECT TOP 1
------------        c.CompanyName AS CompanyName,
------------        p.ProductName AS ProductName,
------------        SUM(od.Quantity) AS Ordered
------------    FROM Customers c
------------    JOIN Orders o ON c.CustomerID = o.CustomerID
------------    JOIN [Order Details] od ON od.OrderID = o.OrderID
------------    JOIN Products p ON p.ProductID = od.ProductID
------------    GROUP BY c.CompanyName, p.ProductName) sumQuery
------------WHERE c.CompanyName = sumQuery.CompanyName
------------GROUP BY c.CompanyName, sumQuery.ProductName;