--USE NORTHWND
--zad1
/*CREATE VIEW [Products from USA] AS
SELECT Products.productname
FROM Products
INNER JOIN Suppliers
ON Products.supplierID = Suppliers.supplierID
WHere suppliers.country='USA'*/

--zad2
/*SELECT * FROM Customers WHERE Customers.CustomerID Not in (SELECT CustomerID from Orders)*/

--zad3
--SELECT e.firstname, x.lastname from Employees e cross join Employees x

--zad4
/*SELECT c.companyname, p.productname 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*/

--zad5
/*SELECT c.companyname, SUM(od.UnitPrice*od.quantity)AS
"naleznosc" 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
ORDER BY "naleznosc" DESC*/

--zad6
/*SELECT c.companyname from customers c
LEFT OUTER JOIN orders o ON c.customerid = o.customerid
WHERE o.customerid is null*/

--zad7
/*SELECT cat.categoryname, SUM(quantity) AS "ilosc" FROM
Products p
JOIN Categories cat on p.CategoryID = cat.CategoryID
JOIN [Order Details] od on od.ProductID=p.ProductID
JOIN Orders o on o.OrderID=od.OrderID
GROUP BY cat.CategoryName*/

--zad8
/*UPDATE [Order Details] set unitprice = UnitPrice*1.1 WHERE (SELECT count(*) FROM(Select count(*)AS cc from orders o inner join [Order Details] od on o.OrderID=od.OrderID
group by o.CustomerID) AS INN)>=10*/


--zad9
/*SELECT c.companyname, p.productname, e.firstname, e.lastname INTO "Customer Product Employee1" 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 = p.ProductID
JOIN Employees e  on e.EmployeeID = o.EmployeeID*/