use northwind
--CW1
--1
select sum(quantity), CustomerID
from [Order Details]
inner join orders
on [Order Details].OrderID = orders.OrderID
group by CustomerID, quantity
--2 zle
select sum(quantity), CustomerID
from [Order Details]
left outer join orders
    on [Order Details].OrderID = orders.OrderID
group by CustomerID
having sum(quantity) > 250

--3
select sum(UnitPrice*quantity*discount) as suma, Orders.OrderID, ContactName
from [Order Details]
left outer join Orders
    on orders.OrderID = [Order Details].OrderID
inner join Customers
    on orders.CustomerID = customers.CustomerID
group by Orders.OrderID, ContactName

--4
select sum(UnitPrice*quantity*(1-discount)) as suma, Orders.OrderID, ContactName
from [Order Details]
left outer join Orders
    on orders.OrderID = [Order Details].OrderID
inner join Customers
    on orders.CustomerID = customers.CustomerID
group by Orders.OrderID, ContactName
having sum(Quantity) > 250

--5
select sum(UnitPrice*quantity*(1-discount)) as suma, ContactName, Employees.firstname, employees.lastname
from [Order Details]
left outer join Orders
    on orders.OrderID = [Order Details].OrderID
inner join Customers
    on orders.CustomerID = customers.CustomerID
inner join Employees
    on Employees.EmployeeID = orders.EmployeeID
group by ContactName, FirstName, LastName
having sum(Quantity) > 250

------------CWICZENIE 2

--1
select CategoryName, sum(Quantity) suma
from Categories
inner join products
    on Products.CategoryID = Categories.CategoryID
inner join [Order Details]
    on Products.ProductID = [Order Details].ProductID
group by CategoryName

--2
select CategoryName, sum([Order Details].UnitPrice*quantity*(1-Discount)) "laczna cena"
from Categories
inner join products
    on Products.CategoryID = Categories.CategoryID
inner join [Order Details]
    on Products.ProductID = [Order Details].ProductID
group by CategoryName

--3 co??

--4 ??
select (sum(UnitPrice*Quantity*(1-Discount))+Freight) as suma, orders.OrderID
from [Order Details]
inner join orders
    on orders.OrderID = [Order Details].OrderID
group by orders.OrderID, Freight

----------CWICZENIE 3
--1
select count(ShipperID) "liczba zamowien", ShipperID
from Shippers
inner join orders
    on orders.shipvia = Shippers.ShipperID
where year(ShippedDate) < 1998 and year(ShippedDate) > 1996
group by ShipperID

--2
select top 1 count(ShipperID) "liczba zamowien", CompanyName
from Shippers
inner join orders
    on orders.shipvia = Shippers.ShipperID
where year(ShippedDate) < 1998 and year(ShippedDate) > 1996
group by CompanyName
order by count(ShipperID) desc

--3
select FirstName + ' ' + LastName as "imie i nazwisko", count(OrderID) as "suma zamowien"
from Employees
inner join orders
    on orders.EmployeeID = Employees.EmployeeID
group by FirstName + ' ' + LastName

--4
select top 1 FirstName + ' ' + LastName as "imie i nazwisko", count(OrderID) as "suma zamowien"
from Employees
inner join orders
    on orders.EmployeeID = Employees.EmployeeID
where year(shippeddate)=1997
group by FirstName + ' ' + LastName
order by count(OrderID) desc

--5
select top 1 FirstName + ' ' + LastName as "imie i nazwisko", UnitPrice*quantity*(1-discount) as "wartosc zamowienia"
from Employees
inner join orders
    on orders.EmployeeID = Employees.EmployeeID
inner join [Order Details]
    on [Order Details].OrderID = orders.OrderID
where year(shippeddate)=1997
order by UnitPrice*quantity*(1-discount) desc

-----------CWICZENIE 5
--1a)
select FirstName + ' ' + LastName as "imie i nazwisko", count(OrderID) as "suma zamowien"
from Employees
inner join orders
    on orders.EmployeeID = Employees.EmployeeID
where Employees.employeeID in (select Reportsto from Employees where ReportsTo IS NOT null)
group by FirstName + ' ' + LastName

--1b)
select FirstName + ' ' + LastName as "imie i nazwisko", count(OrderID) as "suma zamowien"
from Employees
inner join orders
    on orders.EmployeeID = Employees.EmployeeID
where Employees.employeeID NOT IN (select Reportsto from Employees where ReportsTo IS NOT null)
group by FirstName + ' ' + LastName


----

select FirstName + ' ' + LastName as "imie i nazwisko", count(OrderID) as "suma zamowien"
from Employees
inner join orders
    on orders.EmployeeID = Employees.EmployeeID
where  NOT EXISTS (select 1 from Employees E where E.ReportsTo = Employees.employeeID)
group by FirstName + ' ' + LastName