use northwind
-- zad 1
select contactname, country, city, address, productname, quantity
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
order by contactname
-- zad 2
select contactname, country, city, address from customers
where country='Italy'
-- zad 3
select productID, productname, unitprice from products
order by productID desc
-- zad 4
select country, contactname from customers
order by 1,2
-- zad 5
select top 5 companyname, sum(OD.unitprice*OD.quantity)
from customers C join orders O on c.customerid=o.customerid
join [order details] OD on O.OrderID=OD.OrderID
group by companyname
order by 2 desc
-- zad 6
select orderid from [order details]
where productid='33'
-- zad 7
select top 10 productname, sum(od.unitprice*quantity) as wartsprz from products P
join [order details] OD on P.productID = OD.productid
group by productname
order by wartsprz asc
-- zad 8
select count(od.orderid) as Zamownienia_z_Londynu from [order details] OD
join orders O on OD.orderid = O.orderID
join customers C on C.customerid= O.customerid
where c.city = 'London'
-- zad 9
select categoryname, productname from categories C join products P
on C.categoryid = p.categoryid
where categoryname = 'dairy products' or categoryname = 'condiments'
order by 1,2
-- zad 10
select o.orderid, c.customerid from orders O join customers C on c.customerid = o.customerid
join [order details] OD on O.orderid=od.orderid join products P on P.productid = OD.productid
where productname in('Chai', 'Filo Mix', 'Konbu')
-- zad 11
select 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
join categories CA on CA.categoryid = P.categoryid
where country = 'Denmark' and categoryname = 'seafood'
group by companyname
-- zad 12
select companyname, od.orderid, (quantity*unitprice) as wart_zam from customers C
join orders O on C.customerid=O.customerid
join [order details] OD on O.orderid = OD.orderid
order by 1,2 asc
-- zad 13
select o.orderid, companyname, sum((quantity*unitprice)-discount*(quantity*unitprice)) as netto,
sum(((quantity*unitprice)-discount*(quantity*unitprice))*1.22) as brutto from
customers C join Orders O on C.customerid = O.customerid join [order details] OD
on OD.orderid = o.orderid
group by o.orderid, companyname
-- zad 14
create view srednia_Chai
as select avg(quantity) as srednia from [order details] OD join products P on OD.productid=p.productid
where productname = 'chai'
select companyname , quantity 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, srednia_Chai
where productname = 'Chai' and quantity > srednia
-- zad 15
select companyname from customers
where companyname like 'D%'
-- zad 16
select orderid, count(productid) as ilosc_pozycji from [order details] od
group by orderid
-- zad 17
select categoryname, productname, datepart(quarter, orderdate) as kwartał ,
sum(od.unitprice*quantity) as wartosc
from orders O join [order details] OD on O.orderid = od.orderid join products P
on od.productid = p.productid join categories CA on p.categoryid = CA.categoryid
where categoryname in('seafood', 'Beverages') and year(orderdate)= '1997'
group by categoryname, productname, datepart(quarter, orderdate)
-- zad 18
select country, count(customerid) as liczba_klientow from customers
group by country
order by 2 desc
-- zad 19
select companyname, count(orderid) as ilsoc_zamowien from customers C join orders O
on C.customerid = O.customerid
group by companyname
order by 2 desc
-- zad 20
select od.orderid, sum(od.unitprice*od.quantity) as wart_zam from [order details] OD
group by od.orderid
having sum(od.unitprice*od.quantity) > 4000
order by 2 asc
-- zad 21
select sum(od.unitprice*od.quantity) as wart_zam from [order details] OD
where orderid='10469'
-- zad 22
select avg(quantity*unitprice) as srednia_wart_zamowienia from [order details]
-- zad 23
create view avgChang
as select avg(quantity) as wart from [order details] OD join products P on OD.productid = P.productid
where productname = 'chang'
select orderid from [order details] OD join products P on OD.productid= p.productid, avgchang
where productname = 'chang' and quantity > wart
-- zad 24
create view w_fracht as
select avg(freight) as fracht from orders
select orderid from orders, w_fracht
where freight > fracht
-- zad 25
create view srednia_wart
as select avg(unitprice*quantity) as srednia from [order details]
select orderid from [order details], srednia_wart
where (unitprice*quantity) > srednia
-- zad 26
create view klient as
select top 1 companyname as cm, sum(unitprice*quantity) as suma from customers C join
orders o on c.customerid=o.customerid join [order details] od
on o.orderid=od.orderid
group by companyname
order by suma desc
select od.orderid, productname 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 , klient
where companyname = cm
-- zad 27
create view klienci as
select companyname cn, productname pn 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
group by productname, companyname
create view liczba1 as
select count(productname) as licz_prod from products
select cn [nazwa firmy] from klienci, liczba1
group by cn, licz_prod
having count(pn) = licz_prod
-- zad 28
select categoryname, max(unitprice) as [cena]
from products p join categories c
on p.categoryid= c.categoryid
group by categoryname
-- zad 29
select categoryid from products
group by categoryid
having count(discontinued) > 2
-- zad 30
create view max_cena
as select max(unitprice) as maxi from [order details]
select orderid from [order details], max_cena
where unitprice = maxi
order by orderid
-- zad 31
select productname, categoryname from products p join categories c
on p.categoryid = c.categoryid
order by 2,1
-- zad 32
select companyname, count(od.orderid) as [ilosc zamowien], sum(unitprice*quantity) as[wart zamowien]
from customers c join orders o on c.customerid = o.customerid join [order details] od on
o.orderid = od.orderid
group by companyname
-- zad 33
select firstname, lastname, count(orderid) as [ilosc zamowien] from employees e join
orders o on e.employeeid = o.employeeid
group by firstname, lastname
order by 3 desc
-- zad 34
select o.orderid, orderdate, companyname, productname, od.unitprice, quantity,
od.unitprice*quantity as wartosc_towaru, firstname, lastname from customers c join orders o
on c.customerid = o.customerid join employees e on o.employeeid = e.employeeid join
[order details] od on o.orderid = od.orderid join products p on p.productid = od.productid
where od.orderid = '10255'
-- zad 35
select categoryname, sum(od.unitprice*quantity) as [wartosc sprz] from [order details] od
join products p on od.productid = p.productid join categories c on p.categoryid = c.categoryid
group by categoryname
-- zad 36
select productname from products p join [order details] od on p.productid= od.productid
group by productname
having count(quantity) = 0
-- zad 37
select companyname from customers c join orders o on c.customerid = o.customerid
group by companyname
having count(orderid) = 0
-- zad 38
select lastname, firstname, count(orderid) as [ilosc zamowien] from employees e join
orders o on e.employeeid = o.employeeid
where lastname = 'Fuller'
group by lastname, firstname
-- zad 39
select lastname, firstname, datename(month,(orderdate)) as miesiac, count(orderid) as [ilosc zamowien]
from employees e join orders o on e.employeeid = o.employeeid
where lastname = 'Fuller' and year(orderdate) = '1997'
group by lastname, firstname, datename(month,orderdate),datepart(month, orderdate)
order by datepart(month, orderdate)
-- zad 40
select categoryname, max(productname)as [nazwa produktu], max(unitprice) as [cena]
from products p join categories c on p.categoryid= c.categoryid
group by categoryname
-- zad 41
select lastname, firstname from employees e join orders o on e.employeeid = o.employeeid
where year(orderdate) = '1998'
group by lastname, firstname
having count(orderid) > 50