--podzapytania cwiczenia koncowe
--1. Wybierz nazwy i numery telefonów klientów , którym w 1997 roku przesyłki dostarczała firma United Package.
select companyname, phone from customers
where CustomerID in(

select Orders.CustomerID from Orders
inner join Shippers
on Shippers.ShipperID=Orders.ShipVia
where Shippers.CompanyName= 'United Package' and year(Orders.OrderDate)=1997)

--2. Wybierz nazwy i numery telefonów klientów, którzy kupowali produkty z kategorii Confections.
SELECT Customers.CompanyName, Customers.ContactName
FROM     Orders INNER JOIN
                  Customers ON Orders.CustomerID = Customers.CustomerID
WHERE  Orders.OrderID in(
			SELECT [Order Details].OrderID
			FROM     Categories INNER JOIN
							  Products ON Categories.CategoryID = Products.CategoryID INNER JOIN
							  [Order Details] ON Products.ProductID = [Order Details].ProductID
			WHERE  (Categories.CategoryName = 'Confections'))

--3.Wybierz nazwy i numery telefonów klientów, którzy nie kupowali produktów z kategorii Confections..
SELECT Customers.CompanyName, Customers.ContactName
FROM     Orders INNER JOIN
                  Customers ON Orders.CustomerID = Customers.CustomerID
WHERE  Orders.OrderID not in(
			SELECT [Order Details].OrderID
			FROM     Categories INNER JOIN
							  Products ON Categories.CategoryID = Products.CategoryID INNER JOIN
							  [Order Details] ON Products.ProductID = [Order Details].ProductID
			WHERE  (Categories.CategoryName = 'Confections'))

--ćwiczenie 2
--1.Dla każdego produktu podaj maksymalną liczbę zamówionych jednostek

select t1.ProductName, t2.maks
from 
(SELECT ProductName, ProductID
FROM     dbo.Products) as t1,

(SELECT MAX(Quantity) AS maks, ProductID
FROM     dbo.[Order Details]
GROUP BY ProductID)as t2
where t1.ProductID=t2.ProductID
--2.  Podaj wszystkie produkty których cena jest mniejsza niż średnia cena produktu
SELECT ProductName, UnitPrice
FROM     dbo.Products
WHERE  (UnitPrice < (SELECT AVG(UnitPrice) AS srednia
FROM     dbo.Products))


--3. Podaj wszystkie produkty których cena jest mniejsza niż średnia cena produktu danej kategorii
select t1.ProductName ,t1.UnitPrice,t1.CategoryID, t2.srednia from
(
SELECT ProductName, UnitPrice,CategoryID
FROM     dbo.Products ) as t1,

(SELECT CategoryID, AVG(UnitPrice) AS srednia
FROM     dbo.Products
GROUP BY CategoryID) as t2
where t1.CategoryID=t2.CategoryID and t1.UnitPrice<t2.srednia

--ćwiczenie 3
--1.Dla każdego produktu podaj jego nazwę, cenę, średnią cenę wszystkich produktów oraz różnicę między ceną produktu a średnią ceną wszystkich produktów
select t1.ProductName,t1.UnitPrice,t2.srednia_cena, (t1.UnitPrice- t2.srednia_cena)as roznica from
(SELECT ProductName, UnitPrice
FROM     Products)as t1,
(SELECT AVG(UnitPrice) AS srednia_cena
FROM     Products) as t2
--2. Dla każdego produktu podaj jego nazwę kategorii, nazwę produktu, cenę, 
--średnią cenę wszystkich produktów danej kategorii oraz różnicę między ceną produktu a średnią ceną wszystkich produktów danej kategorii

select t1.ProductName,t1.CategoryName,t1.UnitPrice,t2.srednia, (t1.UnitPrice-t2.srednia)as roznica from
(SELECT Products.ProductName, Products.UnitPrice, Categories.CategoryName, Products.CategoryID
FROM     Categories INNER JOIN
                  Products ON Categories.CategoryID = Products.CategoryID)as t1,

(SELECT Categories.CategoryName, Categories.CategoryID, AVG(Products.UnitPrice) AS srednia
FROM     Categories INNER JOIN
                  Products ON Categories.CategoryID = Products.CategoryID
GROUP BY Categories.CategoryName, Categories.CategoryID)as t2
where t1.CategoryID=t2.CategoryID

--ćwiczenie 3
--1. Podaj łączną wartośd zamówienia o numerze 1025 (uwzględnij cenę za przesyłkę)
SELECT [Order Details].OrderID, 
SUM((([Order Details].UnitPrice*[Order Details].Quantity)-([Order Details].UnitPrice*[Order Details].Quantity*[Order Details].Discount))-Orders.Freight) AS łaczna_wartosc 
FROM     Orders INNER JOIN
                  [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY [Order Details].OrderID
HAVING ([Order Details].OrderID = 10250)
--2. Podaj łączną wartośd zamówieo każdego zamówienia (uwzględnij cenę za przesyłkę)

  SELECT Orders.OrderID,
	SUM((([Order Details].UnitPrice*[Order Details].Quantity)-([Order Details].UnitPrice*[Order Details].Quantity*[Order Details].Discount))-Orders.Freight) AS łaczna_wartosc 
FROM     Orders INNER JOIN
                  [Order Details] ON Orders.OrderID = [Order Details].OrderID
				  group by Orders.OrderID

 --3. Czy są jacyś klienci którzy nie złożyli żadnego zamówienia w 1997 roku, jeśli tak to pokaż ich dane adresowe

 select * from customers where CustomerID not in (SELECT CustomerID
FROM     dbo.Orders
WHERE  (YEAR(OrderDate) = 1997))

--4. Podaj produkty kupowane przez więcej niż jednego klienta

SELECT Products.ProductName, COUNT(Orders.CustomerID) AS LICZBA
FROM     Products INNER JOIN
                  [Order Details] ON Products.ProductID = [Order Details].ProductID INNER JOIN
                  Orders ON [Order Details].OrderID = Orders.OrderID
GROUP BY Products.ProductName
HAVING (COUNT(Orders.CustomerID) >= 2)

--ćwiczenie 4
--1. Dla każdego pracownika (imię i nazwisko) podaj łączną wartośd zamówieo obsłużonych przez tego pracownika 
--(przy obliczaniu wartości zamówieo uwzględnij cenę za przesyłkę_
SELECT Employees.LastName, Employees.FirstName, SUM([Order Details].UnitPrice * [Order Details].Quantity - [Order Details].UnitPrice * [Order Details].Quantity * [Order Details].Discount - Orders.Freight) 
                  AS łaczna_wartosc
FROM     Employees INNER JOIN
                  Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                  [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Employees.LastName, Employees.FirstName

--2. Który z pracowników obsłużył najaktywniejszy (obsłużył zamówienia o największej wartości) w 1997r, podaj imię i nazwisko takiego pracownika
select top 1 t1.FirstName,t1.LastName,max(t1.łaczna_wartosc) as maks from
(SELECT Employees.LastName, Employees.FirstName, SUM([Order Details].UnitPrice * [Order Details].Quantity - [Order Details].UnitPrice * [Order Details].Quantity * [Order Details].Discount - Orders.Freight) AS łaczna_wartosc, 
                  Orders.EmployeeID
FROM     Employees INNER JOIN
                  Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                  [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Employees.LastName, Employees.FirstName, Orders.EmployeeID
) AS t1 ,
(SELECT EmployeeID, OrderDate
FROM     Orders
WHERE  year(OrderDate) = 1997)as t2
                 
where t1.EmployeeID=t2.EmployeeID
group by  t1.FirstName,t1.LastName
--3. Ogranicz wynik z pkt 1 tylko do pracowników a) którzy mają podwładnych b) którzy nie mają podwładnych
--a) którzy mają podwładnych

SELECT Employees.LastName, Employees.FirstName, SUM([Order Details].UnitPrice * [Order Details].Quantity - [Order Details].UnitPrice * [Order Details].Quantity * [Order Details].Discount - Orders.Freight) 
                  AS łaczna_wartosc
FROM     Employees INNER JOIN
                  Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                  [Order Details] ON Orders.OrderID = [Order Details].OrderID
				  where Employees.ReportsTo is null
GROUP BY Employees.LastName, Employees.FirstName

--którzy nie mają podwładnych
SELECT Employees.LastName, Employees.FirstName, SUM([Order Details].UnitPrice * [Order Details].Quantity - [Order Details].UnitPrice * [Order Details].Quantity * [Order Details].Discount - Orders.Freight) 
                  AS łaczna_wartosc
FROM     Employees INNER JOIN
                  Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                  [Order Details] ON Orders.OrderID = [Order Details].OrderID
				  where Employees.ReportsTo is not  null
GROUP BY Employees.LastName, Employees.FirstName

--4.Zmodyfikuj rozwiązania z pkt 3 tak aby dla pracowników pokazad jeszcze datę ostatnio obsłużonego zamówienia
select t1.* ,t2.ostatnia_data from
(SELECT Employees.LastName, Employees.FirstName, SUM([Order Details].UnitPrice * [Order Details].Quantity - [Order Details].UnitPrice * [Order Details].Quantity * [Order Details].Discount - Orders.Freight) AS łaczna_wartosc, 
                  Employees.EmployeeID
FROM     Employees INNER JOIN
                  Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                  [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE  (Employees.ReportsTo IS NOT NULL)
GROUP BY Employees.LastName, Employees.FirstName, Employees.EmployeeID)as t1,

(SELECT EmployeeID, MAX(OrderDate) AS ostatnia_data
FROM     Orders
GROUP BY EmployeeID) as t2
where t1.EmployeeID=t2.EmployeeID