-- podaj klientów których srednia wartośc zamówienia jest wieksza 21.03.2007
-- niż globalna średnia wartość zamówienia
-- wyliczenie globalnej średniej wartości zamówienia
use northwind2005
-- 1 sposób
create view srednia_glob
as
select sum(OD.unitprice*OD.quantity)/count(distinct O.orderid) as sr_gl
from [order details] OD join orders O
on OD.orderid = O.orderid
-- 2 sposób
select (select sum(OD.unitprice*OD.quantity) from [order details] OD )
/
(select count(*) from orders)
-- 3 sposób
select sum(OD.unitprice*OD.quantity) / (select count(*) from orders)
from [order details] OD
select * from srednia_glob
-- srednia wart zam dla kolejnych klientow
select companyname, sum(OD.unitprice*OD.quantity)/count(distinct O.orderid) as sr_gl
from [order details] OD join orders O
on OD.orderid = O.orderid
join customers C
on O.customerid = C.customerid
group by companyname
-- i z końcowym warunkiem
select companyname, sum(OD.unitprice*OD.quantity)/count(distinct O.orderid) as sr_gl
from [order details] OD join orders O
on OD.orderid = O.orderid
join customers C
on O.customerid = C.customerid
group by companyname
having sum(OD.unitprice*OD.quantity)/count(distinct O.orderid) >
(select * from srednia_glob)
order by 2 desc
-- wyświetlić wszystkie zamówienia dla produktu CHAI w których
-- zamówiona ilośc jest większa od sredniej zamawianej ilosci tego produktu
select productname, quantity
from [order details] OD join products P
on OD.productid = P.productid
where productname = 'Chai'
order by 1
select avg(quantity*1.)
from [order details] OD join products P
on OD.productid = P.productid
where productname = 'Chai'
select productname, quantity, orderid
from [order details] OD join products P
on OD.productid = P.productid
where productname = 'Chai' and quantity>
(select avg(quantity*1.)
from [order details] OD join products P
on OD.productid = P.productid
where productname = 'Chai')
order by 1
-- przykład procedury
-- (create)
alter procedure wiosna (@nazwa_produktu varchar(30))
as
begin
select productname, quantity, orderid
from [order details] OD join products P
on OD.productid = P.productid
where productname = @nazwa_produktu and quantity>
(select avg(quantity*1.)
from [order details] OD join products P
on OD.productid = P.productid
where productname = @nazwa_produktu)
order by 1
end
-- uruchomienie procedury
exec wiosna 'Chang'
-- usuwanie procedury
drop procedure wiosna
-- którzy klienci nigdy nie kupowali 'Chang'
-- najpierw ci którzy kupowali Chang
select distinct companyname
from customers C join orders O
on C.customerid = O.customerid
join [order details] OD
on O.orderid = OD.orderid
join products P
on Od.productid = P.productid
where productname = 'Chang'
- a teraz odejmowanie
select *
from customers
where companyname not in (
select distinct companyname
from customers C join orders O
on C.customerid = O.customerid
join [order details] OD
on O.orderid = OD.orderid
join products P
on Od.productid = P.productid
where productname = 'Chang')
order by companyname
select distinct * from
(select firstname, city, customerid
from employees E join orders O
on E.employeeid = O.employeeid
where customerid = 'ALFKI') TAB1 join
(select firstname, city, customerid
from employees E join orders O
on E.employeeid = O.employeeid
where customerid = 'WOLZA') Tab2
on Tab1.firstname = tab2.firstname