--=============GRUPA C


--1 Dla każdego produktu podaj jego nazwe, nazwe kategorii,cene,średnią cene produktów
--z jego kategorii, różnice miedzy ceną jego a srednią, wartość z jego sprzedaży w marcu 1997


--2 Napisz polecenie, które wyświetla listę dzieci będących członkami
--biblioteki (baza library). Interesuje nas imię, nazwisko, data
--urodzenia dziecka, adres zamieszkania dziecka oraz imię i nazwisko
--rodzica.


--3)
-- Podaj listę człownków biblioteki (imię, nazwisko) mieszkających w Arizonie (AZ), którzy mają
-- więcej niż dwoje dzieci zapisanych do biblioteki oraz takich, którzy mieszkają w Kalifornii (CA)
-- i mają więcej niż troje dzieci zapisanych do bibliotek. Dla każdej z tych osób podaj liczbę książek
-- przeczytanych (oddanych) przez daną osobę i jej dzieci w grudniu 2001 (z union).



--4. Podaj dane pracownika oraz wartośc obsłużonych przez niego zamówień  razem z freight
--. Ogranicz sie do tych co mają podwladnych


--1
select (select c.categoryname
from categories as c
where c.categoryid=p.categoryid) as CategoryName,
p.ProductName,p.UnitPrice,
(select avg(pa.unitprice)
from products as pa
where p.CategoryID=pa.CategoryID) as CategoryAverage,
p.UnitPrice-(select avg(pa.unitprice)
from products as pa
where p.CategoryID=pa.CategoryID) as Difference,
(select isnull(sum((od.quantity*od.unitprice*(1-od.discount))),0)
from [order details] as od
inner join orders as o on o.orderid=od.orderid
and year(o.orderdate)=1997 and month(o.orderdate)=3
where od.productid=p.productid) as WartoscSprzedazy
from Products as p

--2

select jm.FirstName,jm.LastName,j.birth_date,a.state,a.city,a.street,a.zip,
am.firstname,am.lastname
from member as jm
inner join juvenile as j
on jm.member_no=j.member_no
inner join adult as a
on j.adult_member_no=a.member_no
inner join member as am
on a.member_no=am.member_no


--3

select m.FirstName,m.lastname,
(select count(*)
from loanhist as l
where (l.member_no=m.member_no
or l.member_no in
(select j.member_no
 from juvenile as j
 where j.adult_member_no=m.member_no))
 and year(l.in_date)=2001 and month(l.in_date)=12) as il_ksiazek
from member as m
inner join adult as a
on a.member_no=m.member_no
where (a.state like 'AZ' and (select count(*) from juvenile as j where j.adult_member_no=a.member_no)>2)
union
select m.FirstName,m.lastname,
(select count(*)
from loanhist as l
where (l.member_no=m.member_no
or l.member_no in
(select j.member_no
 from juvenile as j
 where j.adult_member_no=m.member_no))
 and year(l.in_date)=2001 and month(l.in_date)=12) as il_ksiazek
from member as m
inner join adult as a
on a.member_no=m.member_no
where (a.state like 'CA' and (select count(*) from juvenile as j where j.adult_member_no=a.member_no)>3)



--4


select e.FirstName,e.LastName,b.wartosc
from employees as e
inner join (select o.EmployeeID,sum(a.suma+o.Freight) as wartosc
from orders as o
inner join (select OrderID,sum(isnull(quantity*UnitPrice*(1-Discount),0)) as suma
from [Order Details]
group by orderid) as a on a.orderid=o.OrderID
group by o.EmployeeID) as b
on e.employeeid=b.EmployeeID
where b.EmployeeID in(select empl.employeeid
from employees as empl
left join Employees as emp
on emp.reportsto=empl.EmployeeID
where emp.employeeid is null)


--INNE

--1)
-- Napisz polecenie, które wyświetla listę dzieci będących członkami biblioteki. Interesuje nas imię, nazwisko,
-- data urodzenia dziecka, adres zamieszkania, imię i nazwisko rodzica oraz liczba aktualnie wypożyczonych książek.

-- 2)Dla każdego klienta znajdź wartość wszystkich złożonych zamówień (weź pod uwagę koszt przesyłki)

--3)
-- Czy są jacyś klienci, którzy nie złożyli żadnego zamówienia w 1997, jeśli tak pokaż
-- ich nazwy i dane adresowe (3 wersje - join, in, exists).

-- 4) Podaj nazwy produktów, które nie były sprzedawane w marcu 1997.

--1
select m.FirstName,m.lastname,am.firstname,am.lastname,j.Birth_Date,a.state,a.city,a.street,
(select count(*)
from loan as l
where l.member_no=j.member_no)
from juvenile as j-- W TYM ZADANIU JEST COŚ ZJEBANE ŻE CZASEM PRZYDZIELA NIE TEGO DZIEDZIAKA DO RODZICA
--                   TRZEBA PRÓBOWAĆ DO SKUTKU
inner join member as m
on j.member_no=m.member_no
inner join adult as a on
j.adult_member_no=a.member_no
inner join member as am
on am.member_no=j.adult_member_no

--2
select cu.CompanyName,cc.wartosc
from Customers as cu
inner join (select c.customerid,sum(a.suma+o.Freight) as wartosc
from orders as o
inner join (SELECT OrderID, sum(isnull(quantity*UnitPrice*(1-Discount),0)) as suma
from [Order Details]
group by orderid) as a on a.OrderID=o.OrderID
inner join customers as c
on c.CustomerID=o.CustomerID
group by c.customerid) as cc on cc.CustomerID=cu.CustomerID

--3
select c.CompanyName
from customers as c
left join (select o.CustomerID
from Orders as o
where year(o.orderdate)=1997) as a
on a.CustomerID=c.CustomerID
where a.CustomerID is null

select c.CompanyName
from customers as c
where not exists (select o.CustomerID
from Orders as o
where year(o.orderdate)=1997 and c.CustomerID=o.customerid)

select c.CompanyName
from customers as c
where c.CustomerID not in (select o.CustomerID
from Orders as o
where year(o.orderdate)=1997)

--4

select p.ProductID
from products as p
where not exists
(select distinct od.productid
from [order details] as od
inner join orders as o
on o.orderid=od.orderid and year(o.orderdate)=1997 and month(o.orderdate)=3
where od.productid=p.productid)